IS 333 Information Systems Development

Tutorial No. 2based on Advanced SQL

SUBQUERIES NON-CORRELATED QUERIES

The use of JOINS in a relational database allows us to get information from two or more tables at a time i.e. the data is processed at the same time form all the tables.

Sometimes it is often necessary to process data based on other processed data. e.g. display a list of vendors who provide products.

Or display a list of all products with a price greater than or equal to the average product price.

In both cases, we use subquery to generate the required information that could be used as input for the originating query.

  • A subquery is a query (SELECT query) inside a query.
  • A subquery is normally expressed inside parentheses.
  • The first query in the SQL statement is known as the outer query.
  • The query inside the SQL statement is known as the inner query.
  • The inner query is executed first.
  • The output of an inner query is used as the output for the outer query.
  • The entire SQL statement is sometimes referred to as a nested query.

WHERE SUBQUERIES/IN SUB QUERIES

The most common type of sub-query uses an inner SELECT sub-query on the right side of a WHERE comparison expression.

If the sub-query returns only one value , use = comparison operator.

If the sub-query returns only more than one value , useIN comparison operator.

  1. List Product code,product price for all the products with a price greater than or equal to the average product price.
  2. List vendor code and vendor name of all the vendors who have not supplied any products.

3. Delete all products that have been supplied by the vendors having areacode 615.

4. List all the customers who ordered the Product “Claw Hammer”. Use a sub query.

5. List all customers who have purchased hammers, saw or saw blades. Use a sub-query.

HAVING SUBQUERY

We can use a sub-query with a HAVING clause. Remember the HAVING clause is used to restrict the output of a GROUP BY query by applying a conditional criteria to the grouped rows.

6. List all products with the total quantity sold greater than the average quantity sold.

MultirowSubqueryOperators : ANY and ALL

The IN sub-query is used when we need to compare a value to a list of values. It uses an equality operator i.e. it selects only those rows that matches (are equal to ) at least one of the values in the list.

But if we need to do an inequality comparison ( > or <) of one value to a list of values, then we use ANY or ALL operators.

ANY operator is equivalent to IN operators

7. List the products that have a product cost that is greater than all individual product costs for products which are not supplied by any vendor.

8. List the products that have a product cost that is greater than all individual product

Note: A sub-query in the attribute list must return one single value otherwise an error code is raised.

The use of this type of query is limited to certain instances where you need to include data from other tables that are not directly related to a main table or tables in a query.

11. List the product code, product price, average product price and the difference between each product’s price and the average product price.

12. List the product code, the total sales by product, total number of employees and the contribution by employee of each product sales.

13. Correlated Subqueries

In the non correlated sub queries, each subquery in a command ssequence executes in a serial fashion,one after another. The inner subquery executes first; itsoutput is used buy the outer query, which then executes until the last outer query executes (the first SQL statement in the code)

In contrast, a correlated subquery is a subquery that executes once for each row in the outer query.

This process is similar to the typical nested lopin a programming language.

FOR X = 1 TO 2

FOR Y =1 TO 3

PRINT “X= “ X,” Y=”Y

END

END

will yield the output

X=1Y=1

X=1Y=2

X=1Y=3

X=2Y=1

X=2Y=2

X=2Y=3

13.List all product sales in which the units sold value is greater than the average units sold value for that product.( Hint: First compute the average units sold value for a product, than compare the average computed to the units sold in each sale row, then select only those rows in which the number of units sold is greater.)

14. Repeat Q. 12 by adding a correlated inline sub-query in the attributes of the outer query showing the average units sold column for each product.

15. List the vendor code, vendor name of all of the vendors who have not given any discount.

Correlated Queries with EXISTS special operator.

16. List all customers who have placed an order lately.

17. List the vendor code and vendor name for products having a quantity on hand that is less than double the minimum quantity.

18. List the product code, product description, product discount for those products purchased where the total sales (LINE_TOTAL) line price is greater than $200.

19. List p_code and p_descript for those products which are supplied by the vendors belonging to area code 615.