Table Creation

1
Table Creation

• CIT 381

2
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

3
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).
4
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), UNI
QUE(city))
5
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.)

6
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), )
7
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 lt 01-JAN-98 OR (quota lt 30000)
) )
8
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.
9
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
10
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)
11
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.
12
Buzzword Time
DDL Data Definition Language CREATE TABLE ALTER
TABLE CREATE INDEX DROP TABLE CREATE
VIEW etc. Part of SQL which defines the schema.
13
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.cust2117
14
Now use that value for each company. SELECT
c.company, p.description FROM customers c, orders
o, products p WHERE c.cust_numo.cust AND
o.mfrp.mfr_id AND o.productp.product_id AND
3gt (SELECT COUNT(o2.order_num) FROM orders
o2 WHERE o2.custo.cust)
15
(No Transcript)