Exercise #27 (computer firm) | Interactive tutorial on SQL

16:06

	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 #27
	Define the average size of the PC hard drive for each maker that also produces printers.Result set: maker, average capacity of HD. Track the steps of the solution to the exercise and discover mistakes. 1. Determining all manufacturers who produce printers. Console Execute SELECT Product.maker FROM Product INNER JOIN Printer ON Product.model = Printer.model GROUP BY Product.maker; SELECT Product.maker FROM Product INNER JOIN Printer ON Product.model = Printer.model GROUP BY Product.maker; Scheme 2. Deducing the size of a hard disk and its manufacturer for each personal computer. Console Execute SELECT PC.hd, Product.maker FROM PC INNER JOIN Product ON PC.model = Product.model; SELECT PC.hd, Product.maker FROM PC INNER JOIN Product ON PC.model = Product.model; Scheme 3. Choosing only the rows from item 2 which include the manufacturer found in item 1. Console Execute SELECT PC.hd, Product.maker FROM PC INNER JOIN Product ON PC.model = Product.model WHERE Product.maker IN (SELECT Product1.maker FROM Product Product1 INNER JOIN Printer ON Product1.model = Printer.model GROUP BY Product1.maker ); SELECT PC.hd, Product.maker FROM PC INNER JOIN Product ON PC.model = Product.model WHERE Product.maker IN (SELECT Product1.maker FROM Product Product1 INNER JOIN Printer ON Product1.model = Printer.model GROUP BY Product1.maker ); Scheme 4. In the final solution we receive average values on the basis of query from item 3. Console Execute SELECT Result.maker, AVG(result.hd) FROM (SELECT PC.hd, Product.maker FROM PC INNER JOIN Product ON PC.model = Product.model WHERE Product.maker IN (SELECT Product1.maker FROM Product Product1 INNER JOIN Printer ON Product1.model = Printer.model GROUP BY Product1.maker ) ) AS result GROUP BY result.maker; SELECT Result.maker, AVG(result.hd) FROM (SELECT PC.hd, Product.maker FROM PC INNER JOIN Product ON PC.model = Product.model WHERE Product.maker IN (SELECT Product1.maker FROM Product Product1 INNER JOIN Printer ON Product1.model = Printer.model GROUP BY Product1.maker ) ) AS result GROUP BY result.maker; Scheme T&S To solve the problem on SQL-EX.RU mistakes PC GROUP BY AVG grouping average value maker exercise 27

Previos [Exercise #26]

[Database «Recycled materials company»] Next

Last added:

Tags

aggregate functions ALL ASCII AVG Battles Bezhaev Bismarck C.J.Date Cartesian product CASE cast CHAR CHARINDEX classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CTE data type conversion database schema DATEDIFF DATEPART DATETIME DELETE DISTINCT duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 exercise 37 exercise 39 exercise 46 exercise 54 exercise 55 exercise 56 exercise 57 exercise 7 exercise 70 exercise 8 exercises EXISTS FROM FULL JOIN GROUP BY More tags

The book was updated
several days ago

SQL Problems and solutions

S. I. Moiseenko

Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

Exercise #27