copyright © 2007 ramez elmasri and shamkant b. navathe chapter 8 sql-99: advanced queries

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe

Chapter 8

SQL-99: Advanced Queries

Copyright © 2007 Ramez Elmasri and Shamkant B. Navathe Slide 8- 2

NESTING OF QUERIES

A complete SELECT query, called a nested query, can be specified within the WHERE-clause of another query, called the outer query

Many of the previous queries can be specified in an alternative form using nesting

Query 1: Retrieve the name and address of all employees who work for the 'Research' department.

Q1: SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEEWHERE DNO IN (SELECT DNUMBERFROM DEPARTMENTWHERE DNAME='Research' )


NESTING OF QUERIES Query 1: Retrieve the name and address of all employees

who work for the 'Research' department.

Q1: SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEEWHERE DNO IN (SELECT DNUMBERFROM DEPARTMENTWHERE DNAME='Research' )

Can we write this query as follows? Q2: SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER AND

DNAME='Research' )


NESTING OF QUERIES (contd.)

The nested query selects the number of the 'Research' department

The outer query select an EMPLOYEE tuple if its DNO value is in the result of either nested query

The comparison operator IN compares a value v with a set (or multi-set) of values V, and evaluates to TRUE if v is one of the elements in V

In general, we can have several levels of nested queries A reference to an unqualified attribute refers to the

relation declared in the innermost nested query In this example, the nested query is not correlated with

the outer query


THE EXISTS FUNCTION

EXISTS is used to check whether the result of a correlated nested query is empty (contains no tuples) or not We can formulate Query 12 in an alternative form that

uses EXISTS as Q12B EXISTS will take a value of ‘true’ if the subquery returns

an intermediate results table which contains one or more values, and ‘false’ if no rows are returned (opposite in NOT EXISTS).

For NOT EXISTS, if the inner query does not match at least one condition, the result set will return an empty set.


THE EXISTS FUNCTION (contd.)

Query 12: Retrieve the name of each employee who has a dependent with the same first name as the employee.

Q12B: SELECT FNAME, LNAMEFROM EMPLOYEEWHERE EXISTS (SELECT *

FROMDEPENDENT

WHERESSN=ESSN AND

FNAME=DEPENDENT_NAME)


THE EXISTS FUNCTION (contd.)

Query 6: Retrieve the names of employees who have no dependents.

Q6: SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT EXISTS (SELECT *

FROM DEPENDENT

WHERE SSN=ESSN) In Q6, the correlated nested query retrieves all DEPENDENT

tuples related to an EMPLOYEE tuple. If none exist, the EMPLOYEE tuple is selected

EXISTS is necessary for the expressive power of SQL


SALES

SALENO VOLUME ITEM DEPT1001 2 Boots-snakeproof Clothes1002 1 Pith helmet Clothes1003 1 Sextant Navigation1004 3 Hat-polar explorer Navigation1005 5 Pith helmet Equipment1006 1 Pocket knife-Nile Clothes1007 1 Pocket knife-Nile Recreation1016 1 Sextant Clothes1017 1 Sextant Equipment1018 1 Sextant Recreation

DEPT

DNAME FLOORManagement 5Books 1Clothes 2Equipment 3Furniture 4Navigation 1Recreation 2Accounting 5Purchasing 5Personnel 5Marketing 5

ITEM

INAME TYPE COLORBoots-snakeproof C GreenCamel saddle R BrownPith helmet C KhakiPocket knife-Avon E BrownPocket knife-Nile E BrownSafari chair F KhakiSafari cooking kit F --Sextant N --Hat-polar explorer C White

SUPPLIERSNO SNAME

101Global Books & Maps

102 Nepalese Corp.

103All Sports Manufacturing

104Sweatshops Unlimited

105 All Points, Inc.

106São Paulo Manufacturing

EMPLOYEEENO ENAME SALARY DEPT MGR

1 Alice 75000 Management2 Ned 45000 Marketing 13 Andrew 25000 Marketing 24 Clare 22000 Marketing 25 Todd 38000 Accounting 16 Nancy 22000 Accounting 57 Brier 43000 Purchasing 18 Sarah 56000 Purchasing 79 Sophie 35000 Personnel 1

10 Sanjay 15000 Navigation 311 Rita 15000 Books 412 Gigi 16000 Clothes 413 Maggie 16000 Clothes 414 Paul 11000 Equipment 315 James 15000 Equipment 316 Pat 15000 Furniture 317 Mark 15000 Recreation 3

SUPPLYDELNO DELQTY ITEMNAME DEPTNAME SPLNO

51 50 Hat-polar explorer Navigation 10552 10 Pocket knife-Nile Books 10553 10 Pocket knife-Nile Clothes 10554 10 Pocket knife-Nile Equipment 10555 10 Pocket knife-Nile Furniture 10556 10 Pocket knife-Nile Recreation 10561 2 Sextant Navigation 10162 1 Sextant Equipment 10566 1 Sextant Books 10367 5 Sextant Recreation 10268 3 Sextant Navigation 10469 5 Boots-snakeproof Clothes 10570 15 Pith helmet Clothes 10571 1 Pith helmet Clothes 10172 1 Pith helmet Clothes 10273 1 Pith helmet Clothes 10374 1 Sextant Clothes 10475 5 Pith helmet Books 10576 5 Pith helmet Books 10577 5 Pith helmet Equipment 10578 5 Pith helmet Furniture 10579 5 Pith helmet Recreation 10580 10 Pocket knife-Nile Books 10286 5 Pocket knife-Avon Recreation 10296 2 Boots-snakeproof Equipment 10597 20 Pith helmet Equipment 10698 20 Pocket knife-Nile Equipment 10699 1 Sextant Equipment 106


Non-correlated subqueries

Data is not dependent on the outer query. The query is executed only once for all tuples

processed in the outer query. For example:

Find the floors of all the departments which sell sextant.

select floor from dept where dname in (select dept from sales where item=sextant)

Slide 8- 9


Non-correlated subqueries

For example:Find the floors of all the departments which sell sextant.

select floor from dept where dname in (select dept from sales where item=“sextant”)

- First the inner query will be executed. The result will contain the departments which sell “sextant”. It wil be executed only once.

- Second, the outer query will be executed based on the result of the inner query. It will find all the departments which match the inner query and return the floor numbers.

Slide 8- 10


Correlated subqueries

Uses the result of the inner query to determine the processing of the outer query.

The inner query processes different for each time the outer query executes a row.

For example let us revisit the previous example and try to solve it by using correlated subquery: Find the floors of all the departments which sell

sextant.

Slide 8- 11



Example: Find the floors of all the departments which sell sextant.

select floor from dept d where exists (select * from sales s where s.item="sextant" and d.dname=s.dept)

Slide 8- 12

SALES


DEPT


Get first row. Run the inner query for d.dname=“Management”. If query returns a null set, where clause will become false, so floor info will not be returned. Here, management department does not sell sextant hence floor 5 will not be returned.





Slide 8- 13

SALES


DEPT


Get second row. Run the inner query for d.dname=“Books”. If query returns a null set, where clause will become false, so floor info will not be returned. Here, books department does not sell sextant hence floor 1 will not be returned.





Slide 8- 14

SALES


DEPT


Get third row. Run the inner query for d.dname=“Clothes”. If query returns a null set, where clause will become false, so floor info will not be returned. Here, clothes department sells sextant hence floor 2 will be returned.





Slide 8- 15

SALES


DEPT


Get fourth row. Run the inner query for d.dname=“Equipment”. If query returns a null set, where clause will become false, so floor info will not be returned. Here, equipment department sells sextant hence floor 3 will be returned.





Slide 8- 16

SALES


DEPT


When all rows are processed, the result will be:Floor 2 -> Clothes Floor 3 -> Equipment Floor 1 -> Navigation Floor 2 -> Recreation



Example: Find the items sold by all departments on the second floor.

The result will be : pocket-knife Nile, sextantSALES


DEPT



Correlated subqueries Example: Find the items sold by all departments on the second floor. The result would be : pocket-knife Nile, sextant

SALES


DEPT


select INAMEfrom ITEM Iwhere not exists (select *

from DEPT Dwhere FLOOR=2 and not exists (select * from SALES

where ITEM=I.INAMEand

DEPT=D.DNAME));

ITEM


Get first item. Then, find departments on the second floor: clothes, recreation.The middle query is also a correlated query.



SALES


DEPT





DEPT=D.DNAME));

ITEM



Clothes&Boots-snakeproof exists Recreation&Boots-snakeproof do not existNot all the items exist so the query will be empty (null). Not exists return true. So we select the department.

select * from DEPT D where FLOOR=2 and not exists (select * from SALES where ITEM=“Boots-snakeproof” and DEPT=D.DNAME));



SALES


DEPT





DEPT=D.DNAME));

ITEM



Clothes&Boots-snakeproof exists Recreation&Boots-snakeproof do not existNot all the items exist so the query will be empty (null).

The outer query will return a null set since NOT EXISTS will be non empty set.



SALES


DEPT



from DEPT Dwhere FLOOR=2and not exists

(select * from SALESwhere ITEM=I.INAME

and DEPT=D.DNAME));

ITEM


Get second item. Then, find departments on the second floor: clothes, recreation. The middle query is also a correlated query.

No camel saddle&ClothesNo camel saddle&Recreation in Sales tableNo matching hence both middle and outer query will have empty set.



SALES


DEPT





and DEPT=D.DNAME));

ITEM


Get second item. Then, find departments on the second floor: clothes, recreation. The middle query is also a correlated query.

No camel saddle&ClothesNo camel saddle&Recreation in Sales tableNo matching hence outer query will have empty set.

select * from DEPT D where FLOOR=2 and not exists (select * from SALES where ITEM=“Camel saddle” and DEPT=D.DNAME));


Correlated subqueries For example: Find the items sold by all departments on the second floor. The result would be : pocket-knife Nile, sextant

SALES


DEPT





and DEPT=D.DNAME));

ITEM


After running third, fourth items, get fifth item. Then, find departments on the second floor: clothes, recreation. The middle query is also a correlated query.

Pocket knife-Nile&ClothesPocket knife-Nile&RecreationBoth exist and so outer query will have non empty result set.

So select Pocket knife-Nill.


Correlated subqueries For example: Find the items sold by all departments on the second floor. The result would be : pocket-knife Nile, sextant

SALES


DEPT





and DEPT=D.DNAME));

ITEM


After running third, fourth items, get fifth item. Then, find departments on the second floor: clothes, recreation. The middle query is also a correlated query.

Pocket knife-Nile&ClothesPocket knife-Nile&RecreationBoth exist and so outer query will have non empty result set.

So select Pocket knife-Nill.

select * from DEPT D where FLOOR=2 and not exists (select * from SALES where ITEM=“Pocket knife-Nile” and DEPT=D.DNAME));


Correlated subqueries List the items sold only by departments that sell all the items they are

supplied. The correct items are highlighted on the Item table.

Slide 8- 25

DEPT

DNAME FLOORManagement 5Books 1Clothes 2Equipment 3Furniture 4Navigation 1Recreation 2Accounting 5Purchasing 5Personnel 5Marketing 5Help-desk 2

ITEM


SALES







Slide 8- 26

DEPT


ITEM


SALES




An item X will be sold by N departments.All departments sell the items that are in the supply. For example, “Boots-snakeproof” is sold by “Clothes”.Look at all the items of “Clothes” department in the “Supply”.The department sells “Pocket knife-Nile”, “Boots-snakeproof”, “Pith Helmet”, “Sextant”.Check whether all these items are in the “Sales”. Indeed they are. Likewise items in “Navigation”department.Books and Furniture-> did not sell anyRecreation->did not sell Pocket knife-Avon, pitt helmetEquipment->did not sell everything except sextant and pitt helmet“Boots-snakeproof” is only sold by Clothes but not by any other one.“Hat-polar explorer” is only sold by Navigation but not by any other one.




Slide 8- 27

DEPT


ITEM


SALES




In the second step, consider “Navigation” and “Clothes” department.Identify an item which is only sold by one of these departments.Result:Boots-snakeproof by “Clothes” departmentHat-polar explorer by “Navigation” department.




Slide 8- 28

DEPT


ITEM


SALES




Step 1: Take each item from “Item” table. Call it as X.Step 2: Find all the departments which sell X in the “Sales” table. Call it as Y.

Step 3: Ensure that each entry in Y will sell all the items in “Supply” provided by themselves .Even an entry in Y does not satisfy the condition, do not count X anymore.




Slide 8- 29

DEPT


ITEM


SALES




We need to check every item iteratively.Let us check “sextant”.We need to find all the departments which sell it.Select InameFrom Item IWhere …. { select *

from Sales S1where I.Iname=S1.Item….

Aim to have “Clothes, Navigation, Equipment, Recreation” on hand.




Slide 8- 30

DEPT


ITEM


SALES




We will have “Clothes, Navigation, Equipment, Recreation” on hand. Then, for each department, we need to check whether all items are found in both “sales” and “supply”.Each department should be checked iteratively. Correlation should be based on “S1.dept”.Consider “Navigation” for example. But we also need to ensure that each item in “Supply” should be in “Sales” as well.Otherwise, we will not be sure about whether the query satisfies “all” criteria. So, items in “Supply” should be correlated.This should be checked via “Sales” table. But this will be a new copy of table. (no relation with the previous one).Select InameFrom Item IWhere …. exists { select *

from Sales S1, Supply SUwhere I.Iname=S1.Itemand S1.dept=SU.deptnameWhere ..exists { select *

from Sales S2where S2.item= SU.itemnameand S2.dept=S1.dept)




Slide 8- 31

DEPT


ITEM


SALES




“Exists” returns true even one of the departments is found as selling all items in “Supply”. But we want all. So we need to use “not exists”.Select InameFrom Item IWhere not exists { select * (below will be non-empty incorrect set, so it will be “not exists”. False will return.)

from Sales S1, Supply SUwhere I.Iname=S1.Itemand S1.dept=SU.deptnameWhere not exists { select * (true when the criteria is not met)

from Sales S2where S2.item= SU.itemname “Not exists” will return true if the set is null.

If one of the departments does not follow the criteria, then t the result will be empty set.

In order not to return that item, the first part will be also “not exists”.

and S2.dept=S1.dept)




Slide 8- 32

DEPT


ITEM


SALES




Select InameFrom Item IWhere not exists { select *

from Sales S1, Supply SUwhere I.Iname=S1.Itemand S1.dept=SU.deptnameWhere not exists { select *

from Sales S2where S2.item= SU.itemname and S2.dept=S1.dept)

1. Select I.name = Books-snakeproof”.

2. The middle query returns this tuple.

3. For each item from “Clothes” department should be found in Sales S3 table.Check each row iteratively. If at least one does not satisfy, it will be null set.



List the items supplied to all departments on the second floor by all suppliers.

Answer: sextant. Do not forget to select

which fields should be checked iteratively and in what order.

Slide 8- 33

ITEM


SUPPLIERSNO SNAME


102 Nepalese Corp.



51 50 Hat-polar explorer Navigation 10552 10 Pocket knife-Nile Books 10553 10 Pocket knife-Nile Clothes 10554 10 Pocket knife-Nile Equipment 10555 10 Pocket knife-Nile Furniture 10556 10 Sextant Recreation 10261 2 Sextant Clothes 10162 1 Sextant Recreation 10266 1 Sextant Clothes 10267 5 Sextant Recreation 10168 3 Sextant Navigation 10569 5 Boots-snakeproof Clothes 10570 15 Pith helmet Clothes 10571 1 Pith helmet Clothes 10172 1 Pith helmet Clothes 10273 1 Pith helmet Clothes 10574 1 Sextant Clothes 10575 5 Pith helmet Recreatıon 10576 5 Pith helmet Books 10577 5 Pith helmet Equipment 10578 5 Pith helmet Furniture 10579 5 Pith helmet Recreation 10180 10 Pocket knife-Nile Books 10286 5 Pocket knife-Avon Recreation 10296 2 Boots-snakeproof Equipment 10597 20 Pith helmet Equipment 10598 20 Pocket knife-Nile Equipment 10599 1 Sextant Recreation 105

DEPT


Table values are revised in order to demonstrate the query effects.




Answer: sextant. Item will be the top

dependent. As all dept and suppliers

should be checked on “Supply” table, they will be the second dependent items.

Slide 8- 34

ITEM


SUPPLIERSNO SNAME


102 Nepalese Corp.




DEPT





Answer: sextant.Select Iname

From Item I

Where not exists (select *

from Dept D, Supplier S

where d.floor=2

and not exists (select *

from Supply

where Deptname=D.Dname

and Splno=S.Sno

and Itemname=I.Iname ));

Slide 8- 35

ITEM


SUPPLIERSNO SNAME


102 Nepalese Corp.




DEPT



CORRELATED NESTED QUERIES

If a condition in the WHERE-clause of a nested query references an attribute of a relation declared in the outer query, the two queries are said to be correlated

The result of a correlated nested query is different for each tuple (or combination of tuples) of the relation(s) the outer query

Query 12: Retrieve the name of each employee who has a dependent with the same first name as the employee.

Q12: SELECT E.FNAME, E.LNAMEFROM EMPLOYEE AS EWHERE E.SSN IN

(SELECT ESSNFROM DEPENDENTWHERE ESSN=E.SSN AND

E.FNAME=DEPENDENT_NAME)


CORRELATED NESTED QUERIES (contd.)

In Q12, the nested query has a different result in the outer query

A query written with nested SELECT... FROM... WHERE... blocks and using the = or IN comparison operators can always be expressed as a single block query. For example, Q12 may be written as in Q12A

Q12A: SELECT E.FNAME, E.LNAMEFROM EMPLOYEE E, DEPENDENT DWHERE E.SSN=D.ESSN AND

E.FNAME=D.DEPENDENT_NAME


EXPLICIT SETS

It is also possible to use an explicit (enumerated) set of values in the WHERE-clause rather than a nested query

Query 13: Retrieve the social security numbers of all employees who work on project number 1, 2, or 3.Q13: SELECT DISTINCT ESSN

FROM WORKS_ONWHERE PNO IN (1, 2, 3)


NULLS IN SQL QUERIES

SQL allows queries that check if a value is NULL (missing or undefined or not applicable)

SQL uses IS or IS NOT to compare NULLs because it considers each NULL value distinct from other NULL values, so equality comparison is not appropriate.

Query 14: Retrieve the names of all employees who do not have supervisors.Q14: SELECT FNAME, LNAME

FROM EMPLOYEEWHERE SUPERSSN IS NULL

Note: If a join condition is specified, tuples with NULL values for the join attributes are not included in the result


Joined Relations Feature in SQL2

Can specify a "joined relation" in the FROM-clause Looks like any other relation but is the result of a

join Allows the user to specify different types of joins

(regular "theta" JOIN, NATURAL JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN, etc)


Joined Relations Feature in SQL2 (contd.)

Examples:Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE E SWHERE E.SUPERSSN=S.SSN

can be written as:Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S ON E.SUPERSSN=S.SSN)


Joined Relations Feature in SQL2 (contd.) Examples:

Q1: SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHERE DNAME='Research' AND DNUMBER=DNO

could be written as:Q1: SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO)WHERE DNAME='Research’

or as:Q1: SELECT FNAME, LNAME, ADDRESS

FROM (EMPLOYEE NATURAL JOIN DEPARTMENT AS DEPT(DNAME, DNO, MSSN, MSDATE)WHERE DNAME='Research’


Joined Relations Feature in SQL2 (contd.)

Another Example: Q2 could be written as follows; this illustrates multiple joins in the joined tablesQ2: SELECT PNUMBER, DNUM, LNAME,

BDATE, ADDRESSFROM (PROJECT JOIN

DEPARTMENT ON DNUM=DNUMBER)

JOIN EMPLOYEE ON MGRSSN=SSN) )

WHERE PLOCATION='Stafford’


AGGREGATE FUNCTIONS

Include COUNT, SUM, MAX, MIN, and AVG Query 15: Find the maximum salary, the

minimum salary, and the average salary among all employees.Q15: SELECT MAX(SALARY),

MIN(SALARY), AVG(SALARY)FROM EMPLOYEE

Some SQL implementations may not allow more than one function in the SELECT-clause


AGGREGATE FUNCTIONS (contd.)

Query 16: Find the maximum salary, the minimum salary, and the average salary among employees who work for the 'Research' department.Q16: SELECT MAX(SALARY),

MIN(SALARY), AVG(SALARY)FROM EMPLOYEE,

DEPARTMENTWHERE DNO=DNUMBER AND

DNAME='Research'


AGGREGATE FUNCTIONS (contd.)

Queries 17 and 18: Retrieve the total number of employees in the company (Q17), and the number of employees in the 'Research' department (Q18).Q17: SELECT COUNT (*)

FROM EMPLOYEE

Q18: SELECT COUNT (*)FROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER AND

DNAME='Research’


GROUPING

In many cases, we want to apply the aggregate functions to subgroups of tuples in a relation

Each subgroup of tuples consists of the set of tuples that have the same value for the grouping attribute(s)

The function is applied to each subgroup independently

SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-clause


GROUPING (contd.)

Query 20: For each department, retrieve the department number, the number of employees in the department, and their average salary.Q20: SELECT DNO, COUNT (*), AVG (SALARY)

FROM EMPLOYEEGROUP BY DNO

In Q20, the EMPLOYEE tuples are divided into groups- Each group having the same value for the grouping attribute

DNO The COUNT and AVG functions are applied to each such

group of tuples separately The SELECT-clause includes only the grouping attribute

and the functions to be applied on each group of tuples A join condition can be used in conjunction with grouping


GROUPING (contd.)

Query 21: For each project, retrieve the project number, project name, and the number of employees who work on that project.

Q21: SELECT PNUMBER, PNAME, COUNT (*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAME

In this case, the grouping and functions are applied after the joining of the two relations


THE HAVING-CLAUSE

Sometimes we want to retrieve the values of these functions for only those groups that satisfy certain conditions

The HAVING-clause is used for specifying a selection condition on groups (rather than on individual tuples)


THE HAVING-CLAUSE (contd.)

Query 22: For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project.Q22: SELECT PNUMBER, PNAME,

COUNT(*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAMEHAVING COUNT (*) > 2


SUBSTRING COMPARISON

The LIKE comparison operator is used to compare partial strings

Two reserved characters are used: '%' (or '*' in some implementations) replaces an arbitrary number of characters, and '_' replaces a single arbitrary character


SUBSTRING COMPARISON (contd.)

Query 25: Retrieve all employees whose address is in Houston, Texas. Here, the value of the ADDRESS attribute must contain the substring 'Houston,TX‘ in it.Q25: SELECT FNAME, LNAME

FROM EMPLOYEEWHERE ADDRESS LIKE

'%Houston,TX%'


SUBSTRING COMPARISON (contd.)

Query 26: Retrieve all employees who were born during the 1950s.

Here, '5' must be the 8th character of the string (according to our format for date), so the BDATE value is '_______5_', with each underscore as a place holder for a single arbitrary character.

Q26: SELECT FNAME, LNAMEFROM EMPLOYEEWHERE BDATE LIKE '_______5_’

The LIKE operator allows us to get around the fact that each value is considered atomic and indivisible

Hence, in SQL, character string attribute values are not atomic


ARITHMETIC OPERATIONS

The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction, multiplication, and division, respectively) can be applied to numeric values in an SQL query result

Query 27: Show the effect of giving all employees who work on the 'ProductX' project a 10% raise.Q27: SELECT FNAME, LNAME, 1.1*SALARY

FROM EMPLOYEE, WORKS_ON, PROJECT

WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’


ORDER BY

The ORDER BY clause is used to sort the tuples in a query result based on the values of some attribute(s)

Query 28: Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name.Q28: SELECT DNAME, LNAME, FNAME, PNAME

FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT

WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER

ORDER BY DNAME, LNAME


ORDER BY (contd.)

The default order is in ascending order of values We can specify the keyword DESC if we want a

descending order; the keyword ASC can be used to explicitly specify ascending order, even though it is the default


Summary of SQL Queries

A query in SQL can consist of up to six clauses, but only the first two, SELECT and FROM, are mandatory. The clauses are specified in the following order:

SELECT <attribute list>FROM <table list>[WHERE <condition>][GROUP BY <grouping attribute(s)>][HAVING <group condition>][ORDER BY <attribute list>]


Assignment

The SQL database consists of the following relations: SALES(DEPT,ITEM,VOLUME) DEPT(DNAME,FLOOR) ITEM(INAME,TYPE,COLOR) EMPLOYEE(ENO,ENAME,MGR,DEPT,SALARY,JOB-

STATUS) SUPPLIER(SNO,SNAME) SUPPLY(SUPPLIER,DEPT,ITEM,VOLUME)

Note: Keys are given bold. For the queries you do not need them.

Slide 8- 59


Query 1: Find the items sold by no department on the second floor. Query 2: Find the items sold by all departments on the second floor. Query 3: Find the items sold by at least two departments on the second floor. Query 4: Find the departments where all the employees earn less than their

manager. Query 5: Find the names of the employees who are in the same department

as their manager (as an employee) Query 6: List the name and salary of the managers who earn more than ten

employees. Query 7:List the items supplied by all suppliers that supply all items of type A. Query 8: Find the suppliers that supply pens. Query 9: Find the suppliers that do no supply pens. Query 10: List the departments that do not sell all the items of type A.

Slide 8- 60

copyright © 2007 ramez elmasri and shamkant b. navathe chapter 8 sql-99: advanced queries

Documents