Using SQL to create tables

1
Using SQL to create tables

• Ways of using Databases

2
Revisiting database structure
External Schema
Conceptual Schema
Internal Schema
Physical Schema
3
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)

4
DLL - Add a table

• CREATE TABLE table_name
• (column_name data_type
• NOT NULL UNIQUEDEFAULT 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_ke
  y_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.

5
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))

6
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)

7
DDL - Add a table

• Syntax is as follows-
• CREATE TABLE TableName
• (ColumnDefinition,
• ,Multi-ColumnConstraint,)
• Column definition is
• ColumnDefinition ColumnName
  DataType(size)Single-ColumnConstraint

8
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

9
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,)
  

10
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))

11
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

12
DDL - Syntax for Alter and Drop

• ALTER TABLE TableName
• ADD ColName ColType(size) uniquenot null
  
• DROP COLUMN ColName
• DROP TABLE TableName

13
Populating and Querying tables

• Insert, Update, Delete and View (DML)

14
Inserting data into a table

• To add a row of data to a table
• INSERT INTO lttablegt 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.

15
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.

16
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.

17
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

18
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.