Chapter 8. SQL-99: Schema Definition, Basic Constraints, and Queries. Data Definition, Constraints, and Schema Changes. Used to CREATE, DROP, and ALTER the descriptions of the tables (relations) of a database. CREATE TABLE.
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
SQL-99: Schema Definition, Basic Constraints, and Queries
CREATE TABLE DEPT
( DNAME VARCHAR(10) NOT NULL, DNUMBER INTEGER NOT NULL, MGRSSN CHAR(9), MGRSTARTDATE CHAR(9), PRIMARY KEY (DNUMBER), UNIQUE (DNAME), FOREIGN KEY (MGRSSN) REFERENCES EMP );
CREATE TABLE EMP ( ENAME VARCHAR(30) NOT NULL, ESSN CHAR(9), BDATE DATE, DNO INTEGER DEFAULT 1, SUPERSSN CHAR(9), PRIMARY KEY (ESSN), FOREIGN KEY (DNO) REFERENCES DEPT ON DELETE SET DEFAULT ON UPDATE CASCADE, FOREIGN KEY (SUPERSSN) REFERENCES EMP ON DELETE SET NULL ON UPDATE CASCADE );
Has DATE, TIME, and TIMESTAMP data types
SELECT <attribute list>
FROM <table list>
Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith’
Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO
Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford'
Q9: SELECT SSN FROM EMPLOYEE
Q1C: SELECT * FROM EMPLOYEE WHERE DNO=5Q1D: SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research' AND DNO=DNUMBER
Q11: SELECT SALARY FROM EMPLOYEEQ11A: SELECT DISTINCT SALARY FROM EMPLOYEE
Q13: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3)