SQL - SQL Outline: SQL Chapter 8 3rd ed. (Chapter 8 4th,...

Info iconThis preview shows pages 1–9. Sign up to view the full content.

View Full Document Right Arrow Icon
Jan. 2009 Yangjun Chen ACS-3902 1 SQL Outline: SQL Chapter 8 – 3rd ed. (Chapter 8 – 4th, 5th ed.) DDL - creating schemas - modifying schemas DML - select-from-where clause - group by, having, order by - update - view
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2009 Yangjun Chen ACS-3902 2 SQL Structured Query Language declarative or non-procedural DDL for data definition DML for query, update, view facility for security, integrity constraints, transactions, embedding into other 3GLs such as Cobol, C, … SQL89, SQL92, SQL2000? Also referred to as SQL2
Background image of page 2
Jan. 2009 Yangjun Chen ACS-3902 3 SQL DDL - creating schemas Create schema schemaname authorization user ; Create table tablename constraints: primary keys foreign keys on delete set null|cascade|set default on update set null|cascade|set default on insert set null|cascade|set default uniqueness for secondary keys Create domain domainname
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2009 Yangjun Chen ACS-3902 4 SQL DDL - Examples: Create schema: Create schema COMPANY authorization JSMITH; Create table: Create table EMPLOYEE (FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL(10, 2), SUPERSSN CHAR(9), DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER));
Background image of page 4
Jan. 2009 Yangjun Chen ACS-3902 5 SQL DDL - Examples: Specifying constraints: Create table EMPLOYEE (…, DNO INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (SSN), CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ON DELETE SET DEFAULT ON UPDATE CASCADE); Create domain: CREATE DOMAIN SSN_TYPE AS CHAR(9);
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Jan. 2009 Yangjun Chen ACS-3902 6 SQL set null or cascade: strategies to maintain data consistency Employee delete ssn supervisor ... ... 123456789 234589710 234589710 null ... ... Employee delete ssn supervisor ... ... 123456789 234589710 234589710 null ... ... delete not reasonable cascade
Background image of page 6
Jan. 2009 Yangjun Chen ACS-3902 7 SQL set null or cascade: strategies to maintain data consistency Employee set null ssn supervisor ... ... 123456789 null 234589710 null ... ... Employee delete ssn supervisor ... ... 123456789 234589710 234589710 null ... ... delete reasonable
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Yangjun Chen ACS-3902 8 SQL set default: strategy to maintain data consistency Department DNUMBER … … ... ... 1 … … 4 … … ... ... Employee ssn DNO ... ... 123456789 4 234589710 … … ... ... delete
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/17/2010 for the course ACS ACS-3911 taught by Professor Chen during the Spring '10 term at Windsor.

Page1 / 64

SQL - SQL Outline: SQL Chapter 8 3rd ed. (Chapter 8 4th,...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online