How to solve this EXERCISE

Transact-SQL
, , , ,
1

The exercise consist on :
Find the PC makers with all personal computer models produced by them being present in the PC table.

Product(maker(varchar), model(varchar), type(varchar))
PC(code(int), model(varchar), speed(smallint), ram(smallint), hd(real), cd(varchar), price(money))
Laptop(code(int), model(varchar), speed(smallint), ram(smallint), hd(real), screen(varchar), price(money))
Printer(code(int), model(varchar), color(char), type(varchar), price(money)).

The result of correct query:

maker
A
B

2

If you provide some sample data, we can help with a solution

3

@mike01 this is an IN preidcate , my query just showed me E,D.
It wasn't correct so i tried several times with others queries and didnt get the result A.B

I think in the sample data they would be some a b c d ecc on the part of producing laptop , pc either both of them

4

I'm not going to guess or do the work to create data for you. If you want help, then take a little time to create usable DDL and sample data

5

PROMPT DATA pc

INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (1, '1232', 500, 64, 5, '12x', 600.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (2, '1121', 750, 128, 14, '40x', 850.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (3, '1233', 500, 64, 5, '12x', 600.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (4, '1121', 600, 128, 14, '40x', 850.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (5, '1121', 600, 128, 8, '40x', 850.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (6, '1233', 750, 128, 20, '50x', 950.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (7, '1232', 500, 32, 10, '12x', 400.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (8, '1232', 450, 64, 8, '24x', 350.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (9, '1232', 450, 32, 10, '24x', 350.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (10, '1260', 500, 32, 10, '12x', 350.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (11, '1233', 900, 128, 40, '40x', 980.00);
INSERT INTO pc (code, model, speed, ram, hd, cd, price) VALUES (12, '1233', 800, 128, 20, '50x', 970.00);

6

INSERT INTO product (maker, model, type) VALUES ('B', '1121', 'PC');
INSERT INTO product (maker, model, type) VALUES ('A', '1232', 'PC');
INSERT INTO product (maker, model, type) VALUES ('A', '1233', 'PC');
INSERT INTO product (maker, model, type) VALUES ('E', '1260', 'PC');
INSERT INTO product (maker, model, type) VALUES ('A', '1276', 'Printer');
INSERT INTO product (maker, model, type) VALUES ('D', '1288', 'Printer');
INSERT INTO product (maker, model, type) VALUES ('A', '1298', 'Laptop');
INSERT INTO product (maker, model, type) VALUES ('C', '1321', 'Laptop');
INSERT INTO product (maker, model, type) VALUES ('A', '1401', 'Printer');
INSERT INTO product (maker, model, type) VALUES ('A', '1408', 'Printer');
INSERT INTO product (maker, model, type) VALUES ('D', '1433', 'Printer');
INSERT INTO product (maker, model, type) VALUES ('E', '1434', 'Printer');
INSERT INTO product (maker, model, type) VALUES ('B', '1750', 'Laptop');
INSERT INTO product (maker, model, type) VALUES ('A', '1752', 'Laptop');
INSERT INTO product (maker, model, type) VALUES ('E', '2113', 'PC');
INSERT INTO product (maker, model, type) VALUES ('E', '2112', 'PC');

7

PROMPT DATA printer

INSERT INTO printer (code, model, color, type, price) VALUES (1, '1276', 'n', 'Laser', 400.00);
INSERT INTO printer (code, model, color, type, price) VALUES (2, '1433', 'y', 'Jet', 270.00);
INSERT INTO printer (code, model, color, type, price) VALUES (3, '1434', 'y', 'Jet', 290.00);
INSERT INTO printer (code, model, color, type, price) VALUES (4, '1401', 'n', 'Matrix', 150.00);
INSERT INTO printer (code, model, color, type, price) VALUES (5, '1408', 'n', 'Matrix', 270.00);
INSERT INTO printer (code, model, color, type, price) VALUES (6, '1288', 'n', 'Laser', 400.00);

8

PROMPT DATA laptop

INSERT INTO laptop (code, model, speed, ram, hd, price, screen) VALUES (1, '1298', 350, 32, 4, 700.00, 11);
INSERT INTO laptop (code, model, speed, ram, hd, price, screen) VALUES (2, '1321', 500, 64, 8, 970.00, 12);
INSERT INTO laptop (code, model, speed, ram, hd, price, screen) VALUES (3, '1750', 750, 128, 12, 1200.00, 14);
INSERT INTO laptop (code, model, speed, ram, hd, price, screen) VALUES (4, '1298', 600, 64, 10, 1050.00, 15);
INSERT INTO laptop (code, model, speed, ram, hd, price, screen) VALUES (5, '1752', 750, 128, 10, 1150.00, 14);
INSERT INTO laptop (code, model, speed, ram, hd, price, screen) VALUES (6, '1298', 450, 64, 10, 950.00, 12);

@mike01

9

Doesn't look like Printer and Laptop tables are needed for this. Assuming the Type column in Product is the PC type you are looking for and your original request to find all PC Makers with personal computer models produced by them in the PC table, then I get A, B and E since model 1260 us in the PC Table too.

DDL

Summary

Create table #Product(maker varchar(20), model varchar(20), type varchar(20))
Create table #PC(code int, model varchar(20), speed smallint, ram smallint, hd real, cd varchar(20), price money)

INSERT INTO #pc (code, model, speed, ram, hd, cd, price) VALUES
(1, '1232', 500, 64, 5, '12x', 600.00),
(2, '1121', 750, 128, 14, '40x', 850.00),
(3, '1233', 500, 64, 5, '12x', 600.00),
(4, '1121', 600, 128, 14, '40x', 850.00),
(5, '1121', 600, 128, 8, '40x', 850.00),
(6, '1233', 750, 128, 20, '50x', 950.00),
(7, '1232', 500, 32, 10, '12x', 400.00),
(8, '1232', 450, 64, 8, '24x', 350.00),
(9, '1232', 450, 32, 10, '24x', 350.00),
(10, '1260', 500, 32, 10, '12x', 350.00),
(11, '1233', 900, 128, 40, '40x', 980.00),
(12, '1233', 800, 128, 20, '50x', 970.00)

INSERT INTO #product (maker, model, type) VALUES
('B', '1121', 'PC'),
('A', '1232', 'PC'),
('A', '1233', 'PC'),
('E', '1260', 'PC'),
('A', '1276', 'Printer'),
('D', '1288', 'Printer'),
('A', '1298', 'Laptop'),
('C', '1321', 'Laptop'),
('A', '1401', 'Printer'),
('A', '1408', 'Printer'),
('D', '1433', 'Printer'),
('E', '1434', 'Printer'),
('B', '1750', 'Laptop'),
('A', '1752', 'Laptop'),
('E', '2113', 'PC'),
('E', '2112', 'PC')

Query

Summary

Select distinct prod.maker
from #product prod
join #PC pc
on prod.model = pc.model
where prod.Type ='PC'

10

SELECT DISTINCT p.maker
FROM Product p INNER JOIN
PC ON p.model = PC.model
WHERE p.maker IN (SELECT ip.maker
FROM Laptop il INNER JOIN
Product ip ON il.model = ip.model
)

This is my query , and i get A and B
But is not correct , distmatching data...

I think its my fault for not builiding a correct query.

Otherwise i gave u full dml because i just got it from sql-exe

11

why are you joining to the laptop table that way? That will return all makers regardless as to whether they are in the PC Table. You asked for all PC Makers with personal computer models produced by them in the PC table. What does the actual question say?

12

This is the part i've stuck on mike " models produced by them in the PC table "
Can u help me witth that ?

13

I gave you that already. I used temp tables instead of real tables, so just remove the # sign

Select distinct prod.maker
from #product prod
join #PC pc
on prod.model = pc.model
where prod.Type ='PC'

14

Thank u mike01 for making it easy to me , im really sorry for taking ur time.
Hope u help me other time like u ve done alerady several times
Thank u

15

np, just need to understand what you were asking :grinning: Like I said earlier; providing DDL, sampleData and expected outcome make it alot easier to help.

1 Like
16

The information you share is broad knowledge and should be read by people to open their eyes. I am very grateful for that.

17

Kinda hard solutions anna , we should think out of the box and try several queries to get the correct one.

18

@mike01
Look at this
select maker from product p where type = 'PC'
and exists(select 1 from PC where model = p.model)
except
Select maker from Product WHERE type = 'PC'
and model not in ( select model from PC)

1 Like