loading..
Русский    English
15:00
go

Exercise #17 (tips and solutions)

So, get rid of the Cartesian product. To do this, we remove the PC table from FROM clause, and join Product and Laptop tables on ‘model’ column:

Console
Execute
  1. SELECT DISTINCT type, Laptop.model, speed
  2. FROM Laptop, Product
  3. WHERE Product.model = Laptop.model AND
  4.       Laptop.speed < (SELECT MIN(speed) FROM PC);

Condition p.type = 'laptop' is unnecessary, because the internal joining will contain only model of this type. It turns out that join is no needed also, because Product table is used only for product type output in result set. But the product type is known in advance that a laptop computer, therefore we can simply use the expression (a constant) to specify the type, removing the join:

Console
Execute
  1. SELECT DISTINCT 'Laptop', model, speed
  2. FROM Laptop
  3. WHERE speed < (SELECT MIN(speed) FROM PC);

Note that this decision will be valid only if the products with ‘laptop’ type will be located in Laptop table. For our database this condition is satisfied, because there are only three types of products and, accordingly, three tables. Therefore, a violation of this condition may be associated only with the restructuring, which, however, should also be borne in mind when developing applications with built-in  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 queries.

To return to discussion of exercise #17

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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
website
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100