A taxonomy of SQL queries – Learning Plan
a. Simple queries: selection, projection, sorting on a simple table
i. Small-large number of attributes
ii. Distinct output values
iii. Renaming attributes
iv. Computed attributes
v. Simple-complex conditions (AND, OR, NOT)
vi. Partial Matching (LIKE % _ * ?)
vii. ASC-DESC ordering combinations
viii. Checking for NULLs
ix. DECODE, CASE … , WHEN operators
b. Multi-table queries
i. Simple joins (no INNER JOIN)
ii. Aliasing tables – Full/Partial name qualification
iii. Inner-joins (two and more (different) tables)
iv. Inner-recursive-joins (joining to itself)
v. Outer-joins (restrictions as part of the WHERE and ON clauses)
c. Nested queries
i. In, Not In
ii. Exists, Not Exists
iii. Dynamic relations (as part of SELECT, FROM, and WHERE clauses)
d. Set Oriented Operations
i. Union
ii. Difference
iii. Intersection
iv. Division
e. Summary queries
i. On a simple table
ii. On more than one table
iii. Using nested/dynamic expressions
iv. Using where & having clauses
f. Maintenance Operations
i. Simple insertion (insert into table … values (…) )
ii. Inserting rows from existing tables (create/append to table … as select …)
iii. Delete rows
iv. Update rows
v. Complex conditions given in the WHERE clause as select stmt.
vi. Drop table command
g. Administration
i. Creating objects: tables, views, users, sequences, triggers, etc.
ii. Privilege management through the Grant/Revoke commands
iii. Transaction processing using Commit/Rollback
iv. Backups, Legacy data, migration
Examples
a. Simple Queries
Small-large number of attributes
select * from employee;
select fname, lname from employee;
Distinct output values
select distinct salary from employee;
Renaming attributes
select distinct salary as "Monthly Payment" from employee;
Computed attributes
select salary as "USD", (salary*0.78) as "Euros" from employee
Simple-complex conditions (AND, OR, NOT)
select Fname, Lname, superssn from employee
where dno = 4
and sex = 'F'
and not (superssn= 123456789)
Partial Matching (LIKE % _ * ?)
select Fname, Lname from employee where Lname like 'W%'
select Fname, Lname, address from employee
where address like '%TX%' and Fname like '_a%'
ASC-DESC ordering combinations
select fname, salary, dno from employee order by salary
select fname, salary, dno from employee order by salary DESC
select fname, salary, dno from employee order by dno ASC, salary DESC
Checking for NULLs
select fname, superssn from employee where (superssn is null)
select fname, superssn from employee where NOT (superssn is null)
DECODE, CASE … , WHEN operators
select Fname, Lname, DECODE(superssn, null, 'N.A.', superssn)
from employee
SELECT
CASE sex
WHEN 'F' THEN 'Ms. '
WHEN 'M' THEN 'Mr. '
ELSE ' '
END, fname, lname, salary
FROM employee
b. Multi-table queries
Simple joins (no INNER JOIN)
select Fname, Lname, Dname
from employee, department
where dno = dnumber
Aliasing tables – Full/Partial name qualification
select e.Fname, e.Lname, d.Dname
from employee e, department d
where e.dno = d.dnumber
Inner-joins (two and more (different) tables)
select e.Fname, e.Lname, d.Dname
from employee e INNER JOIN department d ON (e.dno = d.dnumber)
where e.sex = 'F';
Inner-recursive-joins (joining to itself)
select e.Fname as "Employee", b.fname as "Boss"
from employee e, employee b
where e.superssn = b.ssn
Outer-joins (restrictions as part of the WHERE and ON clauses)
***INCORRECT VERSION***
select e.Fname, e.Lname, d.dependent_Name as "Spouse"
from employee e LEFT JOIN dependent d ON (e.ssn = d.essn)
where d.relationship = 'Spouse'
***CORRECT VERSION***
select e.Fname, e.Lname, d.dependent_Name as "Spouse"
from employee e LEFT JOIN dependent d
ON (e.ssn = d.essn) AND d.relationship = 'Spouse'
c. Nested queries
In, Not In
select fname from employee where dno IN (1,4,5);
select Fname, Lname from employee
where ssn IN (select essn from dependent where relationship='Spouse')
select Fname, Lname from employee
where ssn NOT IN (select essn from dependent where relationship='Spouse')
Exists, Not Exists
select e.Fname, e.Lname from employee e
where exists (select * from dependent d
where d.relationship='Spouse' and e.ssn = d.essn)
Dynamic relations (as part of SELECT, FROM, and WHERE clauses)
select e.Fname as "The Employee", d.Dependent_Name as "Spouse"
from employee e, (select * from dependent where relationship = 'Spouse') d
where e.ssn = d.essn
d. Set Oriented Operations
Union
select Fname, dno from employee where sex = 'M'
UNION
select Fname, dno from employee where sex = 'F'
Difference
select ssn from employee
MINUS
select essn from dependent where relationship = 'Spouse'
Intersection
select essn from dependent where relationship = 'Spouse'
INTERSECT
select mgrssn from department
Division
select e.SSN from employee ewhere not exists
(select * from project p
where not exists
(select * from works_on w
where w.essn = e.ssn and w.pno=p.pnumber))
e. Summary queries
On a simple table
select dno, avg(salary) as "AVG SALARY"
from employee
group by dno
On more than one table
select d.dname, avg(e.salary) as "AVG SALARY"
from employee e, department d
group by d.dname
Using nested/dynamic expressions
Using where & having clauses
select dno, avg(salary) as "AVG SALARY"
from employee
group by dno
having count(*) >= 2
f. Maintenance Operations
Simple insertion (insert into table … values (…) )
Insert into WORKS_ON values (123456789, ‘P77’, 40)
Insert into PROJECT values (‘P77’, ‘Automation’, null, ‘Cleveland’)
Inserting rows from existing tables (create/append to table … as select …)
MS-ACCESS
Select * INTO Ladies from employee where sex =’F’
ORACLE
Create table Ladies as select * from employee where sex =’F’
Delete rows
Delete employee where ssn = 123456789
Delete works_on where pno = ‘P77’
Update rows
Update employee set superssn=55555555, dno=4 where ssn=123456789
Update employee set salary = 1000000 where ssn = 123456789
Update employee set salary = salary + 1000 where dno =7
Complex conditions given in the WHERE clause as select stmt.
Update employee set salary=salary*1.15
where ssn IN
(select essn from works_on group by essn where count(pno) > 7)
Drop table command
Drop table employee
compare with
Delete from employee
g. Administration
Creating objects: tables, views, users, sequences, triggers, etc.
Create table customer (custName Text, custBalance Number)
Create user Maria identified as Macarena
Create Index, Cluster, Tablespace, Sequence, Segment, Profile, Trigger, …
Privilege management through the Grant/Revoke commands
Grant select, insert, update, delete on employee to maria
Revoke update on Employee from maria
Transaction processing using Commit/Rollback
Commit
Rollback
Company Database
Reference:
Elmasri / Navathe. "Fundamentals of Database Systems". Ed. Addison/Wesley Pub Co. 7th Edition, (2016). ISBN-13:978-0133970777
Guide-Learning-SQL-Examples. V. Matos. 8