loading..
Русский    English
05:32

Exercise #8 (tips and solutions) page 2

On the example of this simple task, a great variety of solution approaches, owing to the flexibility of SQL, can be demonstrated.

Solution 4.4.4. Outer self-join  

Console
Execute
  1. SELECT DISTINCT p.maker
  2. FROM Product p LEFT JOIN
  3. Product p1 ON p.maker = p1.maker AND
  4. p1.type = 'Laptop'
  5. WHERE p.type = 'PC' AND
  6.   p1.maker IS NULL;

The Product table is joined to itself using LEFT JOIN on the condition the maker is the same, and the product type for the second table is  laptop. The p1.maker column will be NULL if a manufacturer doesn’t have any laptop models; this is used in the WHERE clause together with the condition the product type for the record checked is PC.

Solution 4.4.5. Grouping

Console
Execute
  1. SELECT maker
  2. FROM (SELECT DISTINCT maker, type
  3. FROM Product
  4. WHERE type IN ('PC', 'Laptop')
  5. ) AS a
  6. GROUP BY maker
  7. HAVING COUNT(*) = 1 AND
  8.   MAX(type) = 'PC';

In the subquery, unique pairs (supplier, type) are selected with type being either PC or Laptop. Then, grouping by supplier is performed, whereby the rows grouped have to meet the following conditions:

  • COUNT(*) = 1 — which means, the maker has to manufacture only one product type of the selected (since printers have already been excluded by the IN predicate, it’s either PC or Laptop);
  • MAX(type) = 'PC' — this single manufactured product type is PC. Since the HAVING clause can’t contain columns not referred to by aggregate functions, it uses MAX (type); MIN(type) would work equally well.

With so many solution approaches, it’s natural to ask about their efficiency – that is, which query will be executed faster. Leading the field both in number of operations and estimated execution time is solution 4.4.5. The third solution has the weakest performance. Others are estimated to take about twice as much time to execute as the leader.

Notes:

In Management Studio (SQL Server), you can get the estimated execution time value and the query execution plan by first running the command SET SHOWPLAN_ALL ON; and then the queries to be analyzed. To return to normal mode, type SET SHOWPLAN_ALL OFF;

If  A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server isn’t installed on your computer, you can get the query execution plan directly from the site.

Return to discussion of exercise #8

Solve this task at SQL-EX.RU

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.