160 likes | 263 Views
Structured Query Language (SQL). BOOK RELATION. CREATE TABLE SYNTAX. CREATE TABLE <RELATION NAME> ( <ATTRIBUTE> <TYPE> [NOT NULL], <ATTRIBUTE> <TYPE> [NOT NULL], ... CONSTRAINT PRIMARY KEY (<ATTRIBUTE(S)>) [,] [ FOREIGN KEY (<ATTRIBUTE(S)>)
E N D
CREATE TABLE SYNTAX CREATE TABLE <RELATION NAME> ( <ATTRIBUTE> <TYPE> [NOT NULL], <ATTRIBUTE> <TYPE> [NOT NULL], ... CONSTRAINT PRIMARY KEY (<ATTRIBUTE(S)>) [,] [FOREIGN KEY (<ATTRIBUTE(S)>) REFERENCES <RELATION> (<ATTRIBUTE(S)>)] [,] [FOREIGN KEY (<ATTRIBUTE(S)>) REFERENCES <RELATION> (<ATTRIBUTE(S)>)] ... )
EMPLOYEE & DEPARTMENT RELATIONS EMPLOYEE DEPARTMENT
SQL FOR GET NAMES OF DEPARMENT MANAGERS SELECT NAME FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.SSN = DEPARTMENT.MGRSSN
People The PEOPLE Relation
Result SELECT Name, Age FROM People WHERE Dependents = 0 ORDER BY Age DESC, Name ASC Ordering Query Results
People SELECT Age, COUNT(*) FROM People GROUP BY Age The PEOPLE Relation Partitioned Into Groups
Intermediary table for LEFT OUTER JOIN QUERY: SELECT SNAME, P# FROM S, (SELECT * FROM SPJ WHERE J#=‘J1’) SPJ1 WHERE S.S#=SPJ.S# (+)
TRIGGERS: LEDGER and LEDGER_AUDIT TABLES LEDGER LEDGER_AUDIT
TRIGGERS: EXAMPLE create trigger ledger_update before update on LEDGER for each row when ((new.Amount/old.Amount)>1.1) begin insert into LEDGER_AUDIT values (:old.ActionDate, :old.Action, :old.Item, :old.Quantity, :old.QuantityType, :old.Cost, :old.Total, :old.Person); end;