SQL

1
Chapter 6

• SQL

2
Agenda

• Data Definition Language (DDL)
• Access Control

3
String Data Types

• Boolean data true, false, null, unknown value
  (true gt false)
• Character data
• Fixed length CHAR(4)
• Variable length VARCHAR(30)
• Bit data binary string
• BIT(4)

4
Numeric Data Types

• NUMERIC or NUMBER precision, scale
• DECIMAL or DEC precision, scale
• INTEGER or INT
• SMALLINT (32,767)
• FLOAT precision
• REAL
• DOUBLE PRECISION

5
Datetime data

• DATE year, month, day (DD-MON-YY)
• TIME timeprecision (for second, default0)
  with time zone (control hour and minute)
  hourminutesecond a.m. or p.m.
• TIMESTAMP timeprecision (for second, default6
  with time zone (control hour and minute) date
  and times (DD-MON-YY hoursecondminute a.m. or
  p.m.)

6
Integrity Enhancement Feature

• Required data
• Domain constraint
• Entity integrity constraint
• Referential integrity constraint
• Enterprise constraint

7
Required Data

• Position VARCHAR(10) NOT NULL

8
Domain Constraint - I

• CHECK (search condition)
• sex CHAR(1) NOT NULL CHECK (VALUE IN (m, f))
• credit AS NUMBER(3)
• CHECK (VALUE BETWEEN 0 AND 999)
• major AS CHAR(3)
• DEFAULT mis
• CHECK (VALUE IN (mis, man, act,
• obe))

9
Domain Constraint - II

• CREATE DOMAIN domain name AS data type DEFAULT
  default option CHECK (VALUE IN (search
  condition))
• CREATE DOMAIN sextype AS CHAR(1) DEFAULT m
  CHECK (VALUE IN (m,f))
• sex sextype NOT NULL
• CREATE DOMAIN cnumber AS CHAR(2) CHECK (VALUE IN
  (SELECT cno FROM customer))
• cid cnumber NOT NULL
• DROP DOMAIN domain name RESTRICT l CASCADE
• DROP DOMAIN cnumber

10
Entity Integrity

• PRIMARY KEY key name or key names NOT NULL
• PRIMARY KEY sno NOT NULL
• sno INTEGER NOT NULL UNIQUE
• PRIMARY KEY (sno, classno, sdate) NOT NULL

11
Referential Integrity

• Referential action for ON UPDATE and ON DELETE
• CASCADE
• SET NULL
• SET DEFAULT
• NO ACTION
• Examples
• FOREIGN KEY hotelno REFERENCES hotel (hotelno)
• FOREIGN KEY hotelno REFERENCES hotel (hotelno) ON
  DELETE SET NULL
• FOREIGN KEY hotelno REFERENCES hotel (hotelno) ON
  UPDATE CASCADE

12
Enterprise Constraint

• Methods
• CHECK clause
• UNIQUE clause
• CREATE ASSERTION statement
• CREATE ASSERTION assertion name CHECK (assertion
  condition) for defining attribute constraint
• CREATE ASSERTION toomuch
• CHECK (NOT EXIST (SELECT sno
• FROM enroll GROUP BY sno
• HAVING COUNT () gt10))

13
SQL DDL

• CREATE
• ALTER
• DROP

14
CREATE

• SCHEMA
• DOMAIN
• TABLE
• INDEX
• VIEW

15
ALTER

• TABLE
• DOMAIN

16
DROP

• SCHEMA
• DOMAIN
• TABLE
• INDEX
• VIEW

17
CREATE Schema

• CREATE SCHEMA name AUTHORIZATION creator-id
• DROP SCHEMA name RESTRICT CASCADE
• Examples
• CREATE SCHEMA mis150 AUTHORIZATION tsai
• DROP SCHEMA mis150

18
SQL DDL For Table

• CREATE TABLE table-name (colm data-type NOT
  NULLUNIQUE DEFAULT optionCHECK
  search-cond,...,PRIMARY KEY (colm
  ,colm),FOREIGN KEY (colm ,colm) REFERENCES
  (parent-table)colms)

19
Create A New Table Example

• CREATE TABLE student
• (stuid NUMBER(5) NOT NULL CHECK
• (VALUE BETWEEN 00001 AND
  99999),
• stuname CHAR(10),
• major CHAR(10),
• credit NUMBER(3),
• CONSTRAINT pkstudent PRIMARY KEY
• (stuid))

20
Create A New Table Example

• CREATE TABLE faculty
• (facid NUMBER (5) NOT NULL,
• facname CHAR(10),
• dept CHAR(10),
• rank CHAR(3)
• CHECK (VALUE IN (F,Aso, Ast)),
• CONSTRAINT pkfaculty PRIMARY
• KEY (facid))

21
Create A New Table Example

• CREATE TABLE class
• (course NUMBER(5) NOT NULL,
• facid NUMBER(5),
• sched CHAR(10),
• room CHAR(10),
• CONSTRAINT pkclass PRIMARY KEY
• (course),
• CONSTRAINT fkclassfaculty
• FOREIGN KEY (facid)
• REFERENCES faculty
• (facid))

22
Create A New Table Example

• CREATE TABLE enrollment
• (course NUMBER(5) NOT NULL,
• stuid NUMBER(5) NOT NULL,
• grade CHAR(10),
• CONSTRAINT pkenroll PRIMARY KEY
• (course, stuid),
• CONSTRAINT fkenrollclass
• FOREIGN KEY (course)
• REFERENCES class (course),
• CONSTRAINT fkenrollstudent
• FOREIGN KEY (stuid)
• REFERENCES student (stuid))

23
Create A New Table Example

• CREATE DOMAIN PropertyNo AS SMALLINT
• CREATE DOMAIN StaffNo AS CHAR(5) CHECK (VALUE IN
  (SELECT Sno FROM Staff)
• CREATE DOMAIN Prent AS DECIMAL(6,2) CHECK (VLAUE
  BETWEEN 0 AND 9999.99)
• CREATE TABLE PropertyForRent
• (Pno PropertyNo NOT NULL,
• Sno StaffNo CONSTRAINT
  StaffNotTooMuch CHECK (NOT EXIST (SELECT Sno
• FROM PropertyForRent GROUP BY Sno
• HAVING COUNT () gt10)) NOT NULL,
• Rent Prent NOT NULL,
• CONSTRAINT pkPropertyForRent PRIMARY KEY
  (Pno),
• CONSTRAINT fkPropertyForRentStaff FOREIGN
  KEY (Sno)
• REFERENCES Staff (Sno))

24
SQL DDL For Table

• DROP TABLE table-name RESTRICT CASCADE
• ALTER TABLE table-name ADDMODIFYCOLUMN colm
  data-type NOT NULLUNIQUE DEFAULT
  optionCHECK search-cond,...
• DROP COLUMN colm RESTRICTCASCADE
• ALTER COLUMN SET DEFAULT default option
• ALTER COLUMN DROP DEFAULT

25
Example

• ALTER TABLE enrollment
• MODIFY (grade NUMBER(3))
• ALTER TABLE enrollment
• ADD (datetake DATE not null)
• DROP TABLE enrollment

26
SQL DDL For Table

• ALTER TABLE table-name ADD CONSTRAINTconstrnt-n
  ametable-constrntDROP CONSTRAINT
  constrnt-nameRESTRICT CASCADE

27

• CREATE TABLE customer
• (lastname CHAR(20) NOT NULL,
• firstname CHAR(20) NOT NULL,
• customerid NUMBER(5) NOT NULL,
• address CHAR(10))
• ALTER TABLE customer
• ADD (CONTRAINT pkcustomer PRIMARY
• KEY (lastname, firstname))
• ALTER TABLE customer
• DROP CONSTRAINT pkcustomer
• ALTER TABLE customer
• ADD (CONTRAINT pkcustomer PRIMARY
• KEY (customerid))

28
Example

• CREATE TABLE purchaseorder
• (ponumber CHAR (5) NOT NULL, podate DATE
• CONTRAINT pkpurchasorder PRIMARY KEY
• (ponumber))
• CREATE TABLE puchaseorderlineitem
• (ponumber CHAR(5) NOT NULL,
• lineitem CHAR(5) NOT NULL,
• quantity NUMBER(5) NOT NULL,
• CONTRAINT pkpuchaseorderlineitem PRIMARY KEY
• (ponumber, lineitem))
• ALTER TABLE puchaseorderlineitem
• ADD (CONSTRAINT fkpolineitem
• FOREIGN KEY (ponumber) REFERENCES
  purchaseorder (ponumber))
• ALTER TABLE puchaseorderlineitem
• DROP (CONSTRAINT fkpolineitem)

29
SQL DDL For Index

• CREATE UNIQUE INDEX index-name ON
  base-table-name (column ASC DESC , ... )
• DROP INDEX index-name

30
Example

• CREATE INDEX studentname
• ON student (stuname DESC)
• CREATE INDEX majorcredit
• ON student (major, credit)
• DROP INDEX majorcredit

31
SQL DDL For View

• CREATE VIEW view-name (view-colm , ...) AS
  SELECT ... WITH CASCADELOCAL CHECK OPTION
• DROP VIEW view-name RESTRICT CASCADE

32
Example

• CREATE VIEW substudent
• (studentid, studentname, major)
• AS
• SELECT stuid, stuname, major
• FROM student
• CREATE VIEW productprofit
• (productid, productname, profit)
• AS
• SELECT productid, productname, sale-cost
• FROM inventory
• DROP VIEW productprofit

33
Advantage of View

• Data independence
• Currency
• Improved security
• Reduced complexity
• Convenience
• Customization
• Data integrity

34
Disadvantage of View

• Update restriction
• Structure restriction (new attribute in the base
  table)
• Performance (complex views)

35
Access Control

• GRANT system-privilege ALL PRIVILEGES TO
  role-name WITH GRANT OPTION
• GRANT object-privilege ALL PRIVILEGES ON
  owner. object-name TO role-name WITH GRANT
  OPTION
• GRANT role-name TO user-name
• REVOKE privilege FROM role-name
• REVOKE role-name FROM user-name

36
System Privileges

• CREATE TABLE
• CREATE VIEW
• CREATE USER
• ALTER INDEX

37
Object Privileges

• SELECT
• INSERT
• UPDATE
• DELETE

38
Examples

• GRANT CREATE TABLE, CREATE VIEW, CREATE
  USER TO manager
• GRANT SELECT, INSERT, UPDATE, DELETE ON
  student TO manager
• GRANT manger TO tsai

39

• REVOKE DELETE ON student FROM
  manager
• REVOKE manager FROM tsai

40
Points To Remember

• Structured Query Language (SQL)
• Data Definition Language (DDL)
• Access Control

41
Assignment

• Review 5
• Read chapters 11 and 12
• Homework assignment (type question before the
  solution)
• 5.18, 6.10, 6.11, 6.12, 6.13, 6.14, 6.15, 6.16
• Due date

42

• Student (Stuid, Stuname, Major, Credits)
• Class (Course, Facid, Sched, Room)
• Faculty (Facid, Facname, Dept, Rank)
• Enrollment (Course, Stuid, Grade)

43

• Branch (Bno, Area, City)
• Staff (Sno, Name, Position, Sex, Salary, Bno)
• Property_for Rent (Pno, Area, City, Type, Rooms,
  Rent, Ono, Sno, Bno)
• Renter (Rno, Name, Max_Rent)
• Owner ( Ono, Name,)
• Viewing (Rno, Pno, Date)