Transcript Using SQL to create tables

Using SQL to create tables
Ways of using Databases
Revisiting database structure
External
Schema
Conceptual
Schema
Internal
Schema
Physical
Schema
SQL
• Standard Query Language is used widely to
access databases.
• Implemented SQL (Oracle, MS SQL Server,
Access) varies a little from the standard, but
is reasonably standard
• It consists of a data definition language DDL (Create, Alter, Drop)
• And a Data Manipulation Language - DML
(Select, Union, Update, Delete, Insert Into,
Select Into, Transform, Parameter)
DLL - Add a table
CREATE TABLE table_name
{(column_name data_type
[NOT NULL] [UNIQUE][DEFAULT default_option]
[CHECK (search_condition)][,…]}
[PRIMARY KEY (list_of_columns),]
{[UNIQUE (list_of_columns),][,…]}
{[FOREIGN KEY {(list_of_foreign_key_columns)
REFERENCES parent_table_name[(list_of_candidate_key_columns)],
[MATCH {PARTIAL | FULL}
{[CHECK (search_condition)][,…]})
• [] optional, {} one or more, lower case – user supplied names, upper
case reserved words, [|] either or, () are part of the syntax.
Basic create
• DDL - Create a table
Create table Publisher
( PubID VARCHAR(10) NOT NULL,
PubName VARCHAR(100),
PubPhone VARCHAR(20))
Create table Books
(ISBN VARCHAR(13) NOT NULL,
Title VARCHAR(100),
Price Decimal(7,2),
PubID VARCHAR(10))
With keys
Create table Publisher
(PubID VARCHAR(10) UNIQUE NOT NULL,
PubName VARCHAR(100),
PubPhone VARCHAR(20),
primary key (PubID))
Create table Books
(ISBN VARCHAR(13),
Title VARCHAR(100),
Price Decimal(7,2),
PubID VARCHAR(10),
PRIMARY KEY (ISBN),
FOREIGN KEY (PubID) REFERENCES PUBLISHER)
DDL - Add a table
• Syntax is as follows:CREATE TABLE TableName
(ColumnDefinition,…
[,Multi-ColumnConstraint,…]);
• Column definition is
ColumnDefinition ::= ColumnName
DataType[(size)][Single-ColumnConstraint]
DDL - Single column constraint
• Single-ColumnConstraint:==
CONSTRAINT
IndexName
[PRIMARY KEY | UNIQUE |
REFERENCES ReferencedTable
[(ReferencedColumn,…)]
]
• The ReferencedColumn is only necessary if the field being
referenced is not the primary key of the referenced table
DDL - Multi-column constraints
• This constraint clause appears after all column
definitions:
CONSTRAINT
IndexName
[PRIMARY KEY (ColumnName,…) |
UNIQUE (ColumnName,…) |
FOREIGN KEY (ReferencingColumn,…)
REFERENCES ReferencedTable[(ReferencedColumn,…)]
]
DDL - Create a table
Create table Publisher
(PubID TEXT(10) CONSTRAINT PrimaryKeyName PRIMARY
KEY,
PubName TEXT(100),
PubPhone TEXT(20));
Create table Books
(ISBN TEXT(13) CONSTRAINT PrimaryKeyName PRIMARY KEY,
Title TEXT(100),
Price MONEY,
PubID TEXT(10) CONSTRAINT FOREIGN KEY PubID
REFERENCES PUBLISHER(PubID));
DDL - ALTER or DROP TABLE
• Alter is used to
– Add a new column to the table
– Delete a column from the table
• Drop is used to delete a table
DDL - Syntax for Alter and Drop
ALTER TABLE TableName
ADD ColName ColType[(size)]
[unique][not null] |
DROP COLUMN ColName
DROP TABLE TableName
Populating and Querying tables
Insert, Update, Delete and View
(DML)
Inserting data into a table
• To add a row of data to a table
INSERT INTO <table> VALUES
(value1, value2, …valueN)
• If the value list matches the column list
exactly, there is no need to list the column
names.
• If the value list does not match the column
list exactly, there is a need to list the column
names.
Example
• insert into Expert values
(2222221,'Dr.
O''Meara','Orthopaedic');
• Where the expert table was set up as:CREATE TABLE Expert (
Expert_Id numeric(7,0),
Expert_Name varchar(50),
Expertise_area varchar(15),
PRIMARY KEY (Expert_Id))
Note also, if you want to put an apostrophe in a string, put in two
apostrophes.
Delete
Delete from expert where
expert_id = 2222221;
This deletes all rows from the expert table that
have an expert_id of 2222221.
Please note that you can only delete a row if
no other row depends on it.
Referential integrity
• The above tables have no foreign keys and a
straightforward insert does not compromise
referential integrity.
• If a table is constrained by foreign key values,
then there is a need to see what is in the other
table.
• Retrieving data from a database
– Data can be retrieved from a table or set of tables using
the SELECT statement
Exercise
• The following tables form part of a database held in a
relational DBMS:
–
–
–
–
Hotel (Hotel_No, Name, Address)
Room (Room_No, Hotel_No, Type, Price)
Booking (Hotel_No,Guest_No, Date_from, Date_To, Room_No)
Guest (Guest_No, Name, Address)
where
–
–
Hotel contains hotel details and Hotel_No is the primary key,
Room contains room details for each hotel and (Hotel_No,
Room_No) forms the primary key,
– Booking contains details of the bookings and the primary key
comprises (Hotel_No, Guest_No and Date_From)
– Guest contains guest details and Guest_No is the primary key.
• Create the database.