Transcript Nov1

Table Creation
CIT 381
2000 Nov 1
First: Create a Database
• Dependent on software product
• In Access, click “New”
• SQL standard used by Oracle, Informix,
Sybase…
• CREATE DATABASE dbname
• DROP DATABASE dbname
Creating a Table
CREATE TABLE tabname(
column-name
type,
column-name
type,
…
column-name
type,
primary-key-constraint,
foreign-key-constraint);
The constraints are optional (but common).
Create Example
CREATE TABLE offices(
office
integer NOT NULL,
city
varchar(15) NOT NULL,
mgr
integer,
target
money,
sales
money NOT NULL,
PRIMARY KEY(office),
CONSTRAINT hasmgr
FOREIGN KEY(mgr) REFERENCES
salesreps(empl_num),
UNIQUE(city))
Constraints
•
•
•
•
•
NOT NULL
PRIMARY KEY
FOREIGN KEY
UNIQUE (alternate key)
Constraints can be named. This gives a
handle with which to refer to them. (One
might wish to drop or defer them.)
User Defined Types
The SQL2 standard allows the user to define types:
CREATE DOMAIN good_office_num INTEGER
CHECK (VALUE BETWEEN 11 AND 99)
CREATE TABLE offices(
office
good_office_num NOT NULL,
city
TEXT(15),
…)
Check Constraints
A constraint which is enforced each time table
is altered (by insert, update, delete).
CREATE TABLE salesreps(
empl_num
INTEGER,
name
TEXT(15),
…
PRIMARY KEY(empl_num),
CHECK( (hire_date < ‘01-JAN-98’ OR
(quota <= 30000) )
);
Dropping a Table
Easy
DROP TABLE salesreps;
Has big consequences. Generally most users
would not be allowed to do this. Only the DBA
should have this permission.
Changing the Schema
ALTER TABLE customers
ADD contact_name TEXT(15);
ALTER TABLE products
ADD min_qty NOT NULL WITH DEFAULT 0;
ALTER TABLE products
ALTER min_qty SET DEFAULT 1;
% Change primary key of offices to city (and then go back).
ALTER TABLE offices
DROP PRIMARY KEY (office);
ALTER TABLE offices
ADD CONSTRAINT newkey
PRIMARY KEY (city);
ALTER TABLE offices
DROP CONSTRAINT newkey;
ALTER TABLE offices
ADD PRIMARY KEY (office);
Creating an Index
An index is a very important structure which can
help speed up queries.
CREATE INDEX indxName
ON salesreps(name);
Good for queries on name field of salesreps.
Buzzword Time
DDL
Data Definition Language
CREATE TABLE
ALTER TABLE
CREATE INDEX
DROP TABLE
CREATE VIEW
etc.
Part of SQL which defines the schema.
Back to Subqueries
% List all items ordered by companies with 3 or
less orders.
First find how much a company (say 2117) has ordered.
SELECT COUNT(o2.order_num)
FROM orders o2
WHERE o2.cust=2117;
Expr1000
1
% Now use that value for each company.
SELECT c.company, p.description
FROM customers c, orders o, products p
WHERE c.cust_num=o.cust
AND o.mfr=p.mfr_id
AND o.product=p.product_id
AND 3>= (SELECT COUNT(o2.order_num)
FROM orders o2
WHERE o2.cust=o.cust);
description
Ratchet Link
Widget Installer
500-lb Brace
Size 4 Widget
Ratchet Link
Reducer
900-lb Brace
Widget Adjuster
Housing
Right Hinge
Left Hinge
Size 3 Widget
Widget Adjuster
Hinge Pin
Motor Mount
Widget Installer
Reducer
Motor Mount
Handle
900-lb Brace
300-lb Brace
Right Hinge
company
Ace International
Ace International
Chen Associates
First Corp.
Fred Lewis Corp.
Fred Lewis Corp.
Holm & Landis
Holm & Landis
Holm & Landis
Ian & Schmidt
J.P. Sinclair
JCP Inc.
JCP Inc.
JCP Inc.
Jones Mfg.
Orion Corp.
Orion Corp.
Peter Brothers
Peter Brothers
Rico Enterprises
Zetacorp
Zetacorp