•  It first became an official standard in 1986 as defined by the American National Standards Institute (ANSI).
•  All major database vendors conform to the SQL standard with minor variations in syntax (different dialects).
•  SQL consists of both a Data Definition Language (DDL) and a Data Manipulation Language (DML).
•  SQL is a declarative language (non-procedural). A SQL query specifies what to retrieve but not how to retrieve it.
•  SQL is not a complete programming language. It does not have control or iteration commands

SELECT A1 , A2 , … , An    <--------     attributes in result  
FROM R1 , R2 , … , Rm     <----------    tables in query
WHERE (condition)

Relations emp (eno, ename, bdate, title, salary, supereno, dno)
proj (pno, pname, budget, dno) dept (dno, dname, mgreno)
workson (eno, pno, resp, hours)

3) Return a list of all department names, the names of the projects of that department, and the name of the manager of each department. 4) Return a list of all distinct combinations of title and salary in the database.

SELECT ename FROM emp WHERE salary >= 20000 and salary <= 45000

SELECT ename FROM emp WHERE salary between 20000 and 45000

SELECT ename  FROM emp WHERE ename LIKE 'A%';
SELECT ename FROM emp WHERE ename LIKE 'J. _ _ _';

SELECT ename  FROM emp WHERE dno IN ('D1','D2','D3');  

 SELECT ename  FROM emp WHERE dno = 'D1' OR dno = 'D2' OR dno = 'D3';

SELECT ename FROM emp  WHERE dno IS NULL

SELECT dname  FROM dept WHERE mgreno IS NOT NULL

(SELECT E.ename FROM emp as E, emp as M WHERE E.supereno = M.eno and M.ename='R. Davis') UNION  (SELECT E.ename FROM emp as E, emp as M WHERE E.supereno = M.eno and M.ename='M. Smith');

SELECT E.ename INTO davisMgr FROM emp as E, emp as M
WHERE E.supereno = M.eno and M.ename='R. Davis'

SELECT E.ename INTO smithMgr FROM emp as E, emp as M WHERE E.supereno = M.eno ;and M.ename=’M. Smith'

davisMgr UNION smithMgr;

• 'ASC' sorts the data in ascending order, and 'DESC' sorts it in descending order. The default is 'ASC'. ·
• The order of sorted attributes is significant. The first attribute specified is sorted on first, then the second attribute is used to break any ties, etc.
• NULL is normally treated as less than all non-null values

SELECT COUNT(eno) AS numEmp, AVG(salary) AS avgSalary FROM emp;

SELECT title, COUNT(eno) AS numEmp FROM emp GROUP BY title HAVING COUNT(eno) >= 2;

SELECT dname, AVG(salary) AS avgSal FROM emp, dept WHERE emp.dno = dept.dno and emp.bdate > DATE ’1965-12-01' GROUP BY dname HAVING AVG(salary) > 40000;

SELECT AVG(budget) FROM proj;

SELECT proj.dno, COUNT(*) FROM proj, workson, emp WHERE emp.title = 'EE' and workson.eno=emp.eno and workson.pno = proj.pno GROUP BY proj.dno HAVING COUNT(*) >=2

SELECT pno, AVG(hours) FROM workson GROUP BY pno

SELECT W.eno, D.dno, SUM(hours) FROM workson AS W, dept AS D, proj AS P
WHERE W.pno = P.pno and P.dno = D.dno
GROUP BY W.eno, D.dno
HAVING SUM(hours) >= 10;

SELECT ename, salary, bdate FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp)

SELECT ename FROM emp WHERE dno = (SELECT dno FROM dept WHERE dname = 'Accounting')

SELECT ename FROM emp, workson WHERE workson.eno = emp.eno AND workson.hours > (SELECT AVG(hours) FROM workson);

SELECT dname FROM dept WHERE dno IN (SELECT dno FROM proj WHERE budget > 300000);

SELECT pname FROM proj WHERE pno IN (SELECT pno FROM workson WHERE eno =  (SELECT eno FROM emp WHERE ename = 'J. Doe'));

SELECT ename FROM emp as E WHERE EXISTS (SELECT * FROM emp as E2 WHERE E.name = E2.name and E.eno <> E2.eno)

SELECT ename FROM emp as E WHERE salary > ALL (SELECT salary FROM emp WHERE title = 'ME');

 SELECT dname, pname FROM (dept LEFT OUTER JOIN proj ON dept.dno = proj.dno);  

• Clauses in square brackets ([,]) are optional.

INSERT INTO emp VALUES ('E9','S. Smith',DATE ’1975-03-05’, 'SA',60000,'E8','D1');
INSERT INTO proj (pno, pname) VALUES ('P6','Programming');

INSERT INTO deptCount (SELECT dno, dname, count(*) as numEmp FROM dept, emp WHERE dept.dno = emp.dno GROUP BY dno, dname) ;

UPDATE emp SET salary = salary*1.10;