CS6320 SQL

1
CS6320 - SQL
2
The Structured Query Language (SQL)

• Composed of two categories
• Data Definition
• create database
• create table
• drop database
• Data Manipulation
• used to manipulate the data
• select
• delete
• update

3
Data Definition

• CREATE TABLE - allows you to create a table
  definition in a database
• CREATE DATABASE - allows you to create a database
• DROP TABLE - removes a table from a database
• ALTER TABLE - modifies the definition of a table
  in a database

4
Create Table

• Example 1
• create table customer(
• last_name       varchar2(30) not null,
• state_cd          varchar(2),
• sales                 number)
• Example 2
• create table products(
• name              varchar2(30) not null,
• description    varchar2(300),
• price             number(4,2) )
• tablespace     productspace
• storage(initial 25k next 25k minextents 1)

5
Alter Table

• Add columnalter table customeradd
  tax_exempt_id       varchar2(20)
• Drop column
• alter table customer
• drop column sales

6
Data Manipulation Language

• SELECT - query the database
• select from customer where id gt 1001
• INSERT - adds new rows to a table.
• Insert into customer values (1009, John Doe)
• DELETE - removes a specified row
• delete from customer where amount 100
• UPDATE - modifies an existing row
• update customers set amount 10 where id gt 1003

7
Select

• Get all data from table
• select from table
• Get column Distribute data from table
• select Distribute from table
• Get all data where.
• select from table where Name is 'Butch Grewe'

8
Insert

• Add following entry to tableorder of data fields
  MUST be the same as when created table
• insert into table values ('James Bond', '1
  Eiffel Tower', 'Paris', 'France','Spy Software')

9
Delete

• Remove entries where.
• delete from table where age10

10
Describe

• Get meta-data
• describe  table