loading..
Русский    English
21:19

Exercise #8 (tips and solutions) page 2

For this simple task as an example, we can demonstrate a variety of solutions, which is due to flexibility of SQL.

Solution 4.4.4. External 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;

Left self-join the Product table is provided if the manufacturer is the same, and the type of production from the second table is a portable computer. Then the column p1.maker will be NULL, if the vendor have no models of portable computers and is used in a WHERE clause predicate, along with the condition that in the same row the type of production is the 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 if the type is PC or portable computer. Then, you group by the supplier, with the grouped rows must meet the following conditions:

COUNT(*) = 1 — that is, the supplier should produce only one type of product from the remaining (because we have already cut off the printers, it is either a PC or portable computer);

MAX(type) = 'PC' — this type of product is a PC. Since the HAVING clause can not contain links to columns without aggregate functions, it uses MAX (type), although with the same success could write and MIN (type).

With so many approaches the question of the effectiveness are raised, which of the submitted requests will be faster. The leader here, by the number of operations and by execution time is the solution 4.4.5. The worst performance is in the third variant. Others are inferior to the leader about twice the time.

Notes:

Estimation time as well as query execution plan in textual representation can be obtained from the Query Analyzer (SQL Server), running the following command: SET SHOWPLAN_ALL ON; and then performing queries. To return to normal query, you need in the same connection to do following command: SET SHOWPLAN_ALL OFF;

If you have not  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 installed, you can get the query execution plan directly from the site.

To return to discussion of exercise #8

To solve a problem on 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.
Rambler's Top100