-- Format

USE     <database schema>
SELECT  <attribute list, project operation>
FROM    <table list, cross product operation>
WHERE   <condition, select operation>
GROUP BY<grouping attributes>
HAVING  <condition after grouping>
ORDER BY<attributes>

CREATE TABLE EMPLOYEE (
  DNAME VARCHAR(10) NOT NULL,
  DNUMBER INTEGER NOT NULL,
  SALARY FLOAT
  MGRSSN CHAR(9) NOT NULL DEFAULT '888665555',
  MGRSTARTDATE CHAR(10),
  PRIMARY KEY (DNUMBER),
  UNIQUE (DNAME),
  FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
  ON DELETE SET DEFAULT
  ON UPDATE CASCADE
);

DROP TABLE EMPLOYEE RESTRICT -- Cannot drop table with foreign key
DROP TABLE EMPLOYEE CASCADE -- Drop table with foreign key

ALTER TABLE EMPLOYEE ADD JOB VARCHAR(12) SET DEFAULT "Engineer";

SELECT FNAME, LNAME FROM EMPLOYEE WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN)

-- Retrieve department no, no of employee, avg salary of employee on which number of employee is more than one
SELECT DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO

-- For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000
SELECT DNO, COUNT(*) 
FROM EMPLOYEE 
WHERE SALARY > 40000 AND 
      DNO IN (SELECT DNO 
              FROM EMPLOYEE 
              GROUP BY DNO 
              HAVING COUNT(*) > 5 )
GROUP BY DNO

INSERT INTO <table> (<attr1>, <attr2>, ...) VALUES ('char', 0, ...)

DELETE FROM <table> WHERE ...

UPDATE <table>  SET <attribute1>=<value1>, attribute2>=<value2>, .. WHERE ...