Transcript Populating and Querying tables

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.
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
Revisiting Joe’s Yard…
Customer
Staff
Supplier
Customer Id
Customer Name
Customer Address
Staff no
Staff name
Staff role
Supplier Id
Supplier Name
Supplier Address
Amount Owed
Stock
Docket
DocketNo
Order Date
*Customer Id
DocketPaidSig
DocketRcvdSig
*Staff no
Order Line
*DocketNo
*Stock Code
QuantityRequired
Stock Code
Stock Description
Unit Price
*Supplier Id
UnitCostPrice
Stock level
Reorder level
SupplierOrder
SupplierOrderNo
*Supplier Id
SupplierOrderDate
DeliveredDate
SupplierOrderLine
*SupplierOrderNo
*Stock Code
StockRequired
Hierarchy of data structure
Customer
Staff
Supplier
Customer Id
Customer Name
Customer Address
Staff no
Staff name
Staff role
Supplier Id
Supplier Name
Supplier Address
Amount Owed
Stock
Docket
DocketNo
Order Date
*Customer Id
DocketPaidSig
DocketRcvdSig
*Staff no
Order Line
*DocketNo
*Stock Code
QuantityRequired
Stock Code
Stock Description
Unit Price
*Supplier Id
UnitCostPrice
Stock level
Reorder level
SupplierOrder
SupplierOrderNo
*Supplier Id
SupplierOrderDate
DeliveredDate
SupplierOrderLine
*SupplierOrderNo
*Stock Code
StockRequired
Layers of tables
• The tables Customer, Staff and Supplier
have only primary keys. They are the
foundation layer. Layer 1.
• The tables Docket, Stock and Supplier
Order have foreign keys that only
reference the foundation layer. They are
Layer 2.
• Order line and Supplier order line depend
on the tables in Layer 2. They are layer 3.
Remember the bricks…
Staff
Layer 1
The
customer
row is
added, with
key
CustomerId,
the Staff
with key
StaffNo and
the Supplier
with key
SupplierId.
Layer 2
Docket
Supplier Order
Stock
Supplier
Staff
Customer
The stock and the
supplier Order depend
on the Supplier, both
having foreign key
SupplierId.
The Docket depends
on BOTH Staff and
Customer, having
foreign keys
CustomerPdSig,
CustomerRcvdSig and
CustomerId.
Layer 3
Supplier
Order
line
Order line
Stock
Docket
Supplier
Order
Supplier
Staff
Customer
•Both the supplier
order line and the
order line depend
on the stock,
having stockcode
as a foreign key
and part of their
key.
•Order line
depends on docket.
•Supplier order
line depends on
Supplier Order
The built database
Recap
• Look back at the blocks.
– The table creates are the structure or the framework
- i.e. the architect’s drawing
– The inserts are like the bricks. You cannot insert into
a table unless there is supporting data in the table
on which it depends.
• Do
– Creates starting with the one(s) with no dependents
– Inserts starting with the one(s) with no dependents
– Deletes starting with the one(s) on which no other
depends
– Drops starting with the one(s) on which no other
depends
Revisiting database structure
External
Schema
Conceptual
Schema
Internal
Schema
Physical
Schema
The external schema
– Level visible to user
– Multiple views of the
system
• e.g. View an order see limited product
and customer
information
– Only the database
Administrator may
access the whole
database at this level
• Each external view is
defined by means of an
external schema
– Provides definitions of
each external view.
– Written in a Data
Definition Language.
– individual to the user or
user group.
– accessed through a 3GL,
a query language or a
special purpose forms or
menu-based language
SQL, DML and DDL
Data manipulation and definition
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]
Constraints
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 can appear 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));
Constraints on a single column
• [ NULL | NOT NULL ]
• { PRIMARY KEY | UNIQUE }
• FOREIGN KEY ]
REFERENCES ref_table [ (
ref_column )
– [ ON DELETE { CASCADE | NO ACTION } ]
– [ ON UPDATE { CASCADE | NO ACTION } ]
• CHECK ( logical_expression )
Table constraints.
• < table_constraint > ::= [ CONSTRAINT constraint_name
]
– { [ { PRIMARY KEY | UNIQUE }
{ ( column [ ASC | DESC ] [ ,...n ] ) }
[ WITH FILLFACTOR = fillfactor ]
[ ON { filegroup | DEFAULT } ]
]
– FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ]
( search_conditions )
Widely used constraints
•
•
•
•
•
Primary key
Foreign key
Unique
Check (logical expression)
Not null – do we always want this?
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
Create examples
• This allows certain constraints to be placed
on a column or attribute
– E.g.
CREATE TABLE CLASS
(CLASS_name char(10) CHECK (class_name in
('wmt1','wmt2','wmt3')),
class_tutor char(10) unique, not null,
max_students integer CHECK max_students
between 1 and 100 default 50,
primary key (class_name))
Table Manipulation
•
•
•
•
•
CREATE table …
ALTER table …
DROP table …
These operations work on the TABLE
This is equivalent to a file.
Alter
/* Put in a field that allows the accident to be categorised
as
Trivial, Moderate, Serious or Disastrous */
alter table Accident
add column accident_category char(1)
check (accident_category in
('T','M','S','D'));
Row manipulation
• INSERT adds new rows of data to a table.
• UPDATE modifies existing data in a table.
• DELETE removes rows of data from a
table.
UPDATE
UPDATE <table>
SET <expression>
WHERE <condition;
e.g.
UPDATE Northwind.dbo.Products
SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = 2
DELETE
• DELETE FROM <table>WHERE
<searchcondition>
Delete from BusDriver where Staff_No =
5544322;
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.