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
( DNAMEVARCHAR(10)NOT NULL,DNUMBERINTEGERNOT NULL,MGRSSNCHAR(9),MGRSTARTDATECHAR(9),PRIMARY KEY (DNUMBER),UNIQUE (DNAME),FOREIGN KEY (MGRSSN) REFERENCES EMP );
CREATE TABLE EMP(ENAMEVARCHAR(30)NOT NULL,ESSNCHAR(9),BDATEDATE,DNOINTEGER DEFAULT 1,SUPERSSNCHAR(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, ADDRESSFROM EMPLOYEEWHEREFNAME='John' AND MINIT='B’ AND LNAME='Smith’
Q1:SELECTFNAME, LNAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHEREDNAME='Research' AND DNUMBER=DNO
Q2:SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROMPROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN ANDPLOCATION='Stafford'
Q1C:SELECT *FROMEMPLOYEEWHEREDNO=5Q1D:SELECT*FROMEMPLOYEE, DEPARTMENTWHEREDNAME='Research' AND DNO=DNUMBER
Q11:SELECT SALARYFROMEMPLOYEEQ11A: SELECT DISTINCT SALARYFROMEMPLOYEE
Q13:SELECT DISTINCT ESSNFROMWORKS_ONWHEREPNO IN (1, 2, 3)