SQL DDL Statements

1
SQL DDL Statements

• Create Table
• create table s (sno char(5), ... )
• Drop Table
• drop table s
• Alter Table
• alter table s add phone char(13)
• Create View
• create view SV as select ....
• Drop View
• drop view SV
• Create Index
• create index X on s(sno)
• Drop Index
• drop index X

2
Suppliers-Parts Database E-R Diagram
pno (key)
sno (key)
sname
pname
SP
M
S
P
M
city
status
Color
Weight
city
qty
3
Supplier-Part Database

• Table S
  Table P
• sno sname status city pno pname
  color weight city
• ------------------------------
  ----------------------------------
• s1 Smith 20 London p1 nut red
  12 London
• s2 Jones 10 Paris p2 bolt
  green 17 Paris
• s3 Blake 30 Paris p3 screw
  blue 17 Rome
• s4 Clark 20 London p4 screw red
  14 London
• s5 Adams 30 Athens p5 cam
  blue 12 Paris
• p6 cog red
  19 London
• Table SP
• sno pno qty
• ---------------
• s1 p1 300
• s1 p2 200
• s1 p3 400
• s1 p4 200
• s1 p5 100
• s1 p6 100

4
Create Table Statements

• create table s (
• sno char(5) not null,
• sname char(20) not null,
• status smallint,
• city char(15),
• primary key (sno)
• )
• create table p (
• pno char(6) not null,
• pname char(20) not null,
• color char(6),
• weight smallint,
• city char(15),
• primary key (pno)
• )
• create table sp (
• sno char(5) not null,
• pno char(6) not null,
• qty integer not null,

5
Data Types

• integer
• The magnitude range is -2,147,484,647 ...
  2,147,484,647.
• real
• For fixed or floating-point numbers.
• The maximum precision is 38 decimal digits,
• and the magnitude range is 1.0E-129 .. 9.99E125.
• char
• For fixed length character strings upto 32,767
  bytes.
• varchar
• For variable-length character strings.
• The maximum length is 32,767 bytes.
• boolean
• For values TRUE and FALSE.
• date
• From January 1, 4712 BC to - December 31, 4712
  AD.
• timestamp
• Date and time.

6
Creating S-P-SP Database

• pe sqlplus minoura/don8key
• SQLPlus Release 3.3.2.0.0
• SQLgt start create_s_p_sp.sql
• Table created.
• Table created.
• Table created.
• SQLgt select table_name from user_tables
• TABLE_NAME
• ------------
• P
• S
• SP

7
Looking at Table Structures

• SQLgt desc s
• Name Null? Type
• ---------- -------- ----
• SNO NOT NULL CHAR(5)
• SNAME NOT NULL CHAR(20)
• STATUS NUMBER(38)
• CITY CHAR(15)
• SQLgt desc p
• Name Null? Type
• ---------- -------- ----
• PNO NOT NULL CHAR(6)
• PNAME NOT NULL CHAR(20)
• COLOR CHAR(6)
• WEIGHT NUMBER(38)
• CITY CHAR(15)

8
Adding Data to a Table

• insert into s
• values('s1', 'Smith', 20, 'London')
• insert into s
• values('s2', 'Jones', 10, 'Paris')
• insert into s
• values('s3', 'Blake', 30, 'Paris')
• insert into s
• values('s4', 'Clark', 20, 'London')
• insert into s
• values('s5', 'Adams', 30, 'Athens')
• SQLgt start insert_suppliers.sql
• 1 row created.
• 1 row created.
• 1 row created.
• 1 row created.
• 1 row created.

9
Showing Table Contents

• SQLgt select from s
• SNO SNAME STATUS CITY
• ---- ------- -------- ------
• s1 Smith 20 London
• s2 Jones 10 Paris
• s3 Blake 30 Paris
• s4 Clark 20 London
• s5 Adams 30 Athens
• SQLgt select from p
• PNO PNAME COLOR WEIGHT CITY
• ----- ------- ------- -------- -------
• p1 nut red 12 London
• p2 bolt green 17 Paris
• p3 screw blue 17 Rome
• p4 screw red 14 London
• p5 cam blue 12 Paris
• p6 cog red 19 London

10
Sailors-Boats DatabaseE-R Diagram (Schema)
sid (key)
sname
bid (key)
bname
Reserves
Sailors
Boats
M
M
date
rating
color
age
11
Create Table Statements

• create table sailors (
• sid integer not null,
• sname char(20) not null,
• rating smallint,
• age real,
• primary key (sid)
• )
• create table boats (
• bid integer not null,
• bname char(20) not null,
• color char(10),
• primary key (bid)
• )
• create table reserves (
• sid integer not null,
• bid integer not null,
• day date not null,
• primary key (sid, bid, day)
• )

12
Sailors-Boats Database Tables
Sailors
Boats
Reserves