{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

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

Info iconThis preview shows pages 1–12. 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 Document Right 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 Document Right 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 Document Right 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 Document Right Arrow Icon
Jan. 2009 Yangjun Chen ACS-3902 8 SQL set default: strategy to maintain data consistency Department DNUMBER … … ... ... 1 … … 4 … … ... ... Employee ssn DNO ... ... 123456789 4 234589710 … … ... ... delete change this value to the default value 1.
Background image of page 8
Jan. 2009 Yangjun Chen ACS-3902 9 SQL DDL - modifying schemas drop schema schemaname cascade|restrict drop table tablename cascade|restrict alter table tablename add|drop attributename cascade|restrict drop constraint ...
Background image of page 9

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

View Full Document Right Arrow Icon
Jan. 2009 Yangjun Chen ACS-3902 10 SQL DDL - Examples: drop schema DROP SCHEMA CAMPANY CASCADE ; DROP SCHEMA CAMPANY RESTRICT ; drop table DROP TABLE EMPLOYEE CASCADE ; DROP TABLE EMPLOYEE RESTRICT ; alter table ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12); ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE ;
Background image of page 10
Jan. 2009 Yangjun Chen ACS-3902 11 SQL DDL - Examples: alter table (continue) ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT; ALTER TABLE COMPANY.DEPARTMENT
Background image of page 11

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

View Full Document Right Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}