17:40

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Exercise 3 (rating)

For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and printer.For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise.In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).

If the manufacturer has models of some type in the Product table, but none of them is available, then according to the formulation the result should be


yes(0)

Instead of


no

Many find a "mistake" in the test solution. Claims are reduced to the following query:

Console

Execute

SELECT COUNT(*)
   FROM Product
   WHERE Maker = 'E' AND type='PC';

which gives 3 models of PC for manufacturer Е while "right answer" gives only one model of a computer for this manufacturer. We need return to the formulation that tells us:

“… specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).”

In our case, this means that we need specify in brackets a quantity of distinct models of the personal computer of manufacturer Е in PC table. In terms of 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 it means:

Console

Execute

SELECT COUNT(DISTINCT pc.model)
   FROM Product pr
   JOIN PC ON pr.model=pc.model
   WHERE Maker = 'E';

i.e.


1

To solve the problem on SQL-EX.RU

mistakes product Computer firm exercise 3 (rating)

Previos [Exercise #13]

[Exercise #15] Next

Last added:

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 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 EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags

The book was updated
month ago

martin modern singapore

SQL Problems and solutions

S. I. Moiseenko

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Exercise 3 (rating)