loading..
Ðóññêèé    English
03:48
go

Exercise #23 (tips and solutions)

To solve this exercise is generally used two approaches: first, the join and selecting the required row and second, check for hit maker in the two lists. The next incorrect solution implements the first approach:

Solution 4.11.1

Console
Execute
  1. SELECT DISTINCT a.maker 
  2. FROM Product a LEFT JOIN 
  3.      PC b ON a.model = b.model AND 
  4.              b.speed> =750 LEFT JOIN 
  5.      Laptop c ON a.model = c.model  AND c.speed> =750 
  6. WHERE NOT (b.model IS NULL AND 
  7.            c.model IS NULL
  8.            );

This is another variant on the theme of "something one". Indeed, the model is unique, ie it is either a PC or laptop. Next rows are obtained as result of external joins:

  1. maker model (PC) NULL

or

  1. maker NULL model (laptop)

However, there can be no rows such as:

  1. maker model (PC) model (laptop)
because the join is performed by model number.

As a result, the list contains only makers who make only one product type with the desired characteristics. However, the correct solution is simply adding the grouping by maker and model counting. We suggest you do it yourself.

Solution 4.11.2

To demonstrate the second approach, consider the following variant:

Console
Execute
  1. SELECT DISTINCT t.maker 
  2. FROM Product t 
  3. WHERE (t.model IN (SELECT model 
  4.                    FROM PC 
  5.                    WHERE speed >= 750
  6.                    ) OR 
  7.        t.model IN (SELECT model 
  8.                    FROM Laptop 
  9.                    WHERE speed >= 750
  10.                    )
  11.        ) AND 
  12.        EXISTS (SELECT * 
  13.                FROM Product 
  14.                WHERE Product.maker = t.maker AND 
  15.                      Product.type='PC'
  16.                ) AND 
  17.        EXISTS (SELECT * 
  18.                FROM Product 
  19.                WHERE Product.maker = t.maker AND 
  20.                Product.type='Laptop'
  21.                );
which can be read as follows: find a manufacturer that produces the PC with at least 750 MHz or a laptop computer with at least 750 MHz, with a given maker must produce both PCs and laptops. Undeniable progress of this solution compared with the decision 1.15.1 is that the findings of makers, such as PC and laptop computers. However, this solution allows for an option when a maker produces only a PC at speeds over 750 MHz, while all of its laptop computers have a speed of less than 750 MHz, and vice versa.

To return to discussion of exercise #23

To solve a problem on SQL-EX.RU
Bookmark and Share
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.