What makers produce at least a PC or Laptop(model and at least 2 Printer models)

Practice for SQL and Constraints (Product-PC-Laptop-Printer)

Exercise – PC/Laptop/Printer Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) Find those manufacturers that sell Laptops, but not PC's Find those hard-disk sizes that occur in two or more PC's. Find those manufacturers of at least two different computers (PC or Laptops) with speed of at least 700. Find the manufacturers who sell exactly three different models of PC. Using two INSERT statements, store in the database the fact that PC model 1100 is made by manufacturer C, has speed 1800, RAM 256, hard disk 80, a 20x DVD, and sells for $2499. Insert the facts that for every PC there is a laptop with the same manufacturer, speed, RAM and hard disk, a 15-inch screen, a model number 1000 greater, and a price $500 more. Delete all PC’s with less than 20 GB of hard disk. Delete all laptops made a manufacturer that doesn’t make printers. Manufacturer A buys manufacturer B. Change all products made by B so they are now made by A. For each PC, double the amount of RAM and add 20 GB to the amount of hard disk. For each laptop made by manufacturer B, add one inch to the screen size and subtract $100 from the price.

Creation of Tables CREATE TABLE Product ( maker CHAR(10), model INT, type CHAR(5) ); CREATE TABLE PC ( speed INT, ram INT, hd INT, rd INT, price INT CREATE TABLE Laptop ( model INT, speed INT, ram INT, hd INT, screen INT, price INT ); CREATE TABLE Printer ( color CHAR(1), type CHAR(5),

a) Find those manufacturers that sell Laptops, but not PC's. Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) a) Find those manufacturers that sell Laptops, but not PC's. (SELECT maker FROM Laptop NATURAL JOIN Product) MINUS FROM PC NATURAL JOIN Product);

b) Find those hard-disk sizes that occur in two or more PC's. Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) b) Find those hard-disk sizes that occur in two or more PC's. SELECT hd FROM PC GROUP BY hd HAVING COUNT(model) >= 2;

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) c) Find those manufacturers of at least two different computers (PC or Laptops) with speed of at least 700. SELECT maker FROM ( (SELECT model, speed FROM PC) UNION FROM Laptop) ) NATURAL JOIN Product WHERE speed>=700 GROUP BY maker HAVING COUNT(model) >= 2; Or: SELECT maker FROM ( (SELECT model, speed FROM PC) UNION FROM Laptop) ) C JOIN Product ON C.model=Product.model WHERE C.speed>=700 GROUP BY Product.maker HAVING COUNT(C.model) >= 2;

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) d) Find the manufacturers who sell exactly three different models of PC. SELECT Product.maker FROM PC, Product WHERE PC.model=Product.model GROUP BY Product.maker HAVING COUNT(PC.model)=3; Or: SELECT maker FROM PC NATURAL JOIN Product GROUP BY maker HAVING COUNT(model)=3;

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) e) Using two INSERT statements, store in the database the fact that PC model 1100 is made by manufacturer C, has speed 1800, RAM 256, hard disk 80, a 20x DVD, and sells for $2499. INSERT INTO Product(maker,model,type) VALUES('C',1100,'PC'); INSERT INTO PC(model,speed,ram,hd,rd,price) VALUES(1100,1800,256,80,20,2499);

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) f) Insert the facts that for every PC there is a laptop with the same manufacturer, speed, RAM and hard disk, a 15-inch screen, a model number 1000 greater, and a price $500 more. INSERT INTO Product(maker,model,type) (SELECT maker,model+1000,'Laptop' FROM Product WHERE type='PC' ); INSERT INTO Laptop(model,speed,ram,hd,screen,price) (SELECT model+1000, speed, ram, hd, 15, price+500 FROM PC

g) Delete all PC’s with less than 20 GB of hard disk. Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) g) Delete all PC’s with less than 20 GB of hard disk. DELETE FROM PC WHERE hd<20;

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) h) Delete all laptops made by a manufacturer that doesn’t make printers. DELETE FROM Laptop WHERE model IN (SELECT model FROM Product WHERE maker IN ( (SELECT maker FROM Product NATURAL JOIN Laptop) MINUS FROM Product NATURAL JOIN Printer) ) );

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) i) Manufacturer A buys manufacturer B. Change all products made by B so they are now made by A. UPDATE Product SET maker='B' WHERE maker='C';

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) j) For each PC, double the amount of RAM and add 20 GB to the amount of hard disk. UPDATE PC SET ram=ram*2, hd=hd+20;

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) k) For each laptop made by manufacturer B, add one inch to the screen size and subtract $100 from the price. UPDATE Laptop SET screen=screen+1, price=price-100 WHERE model IN (SELECT model FROM Product WHERE maker='B' );

Constraints – PCs, Laptops, Printers Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) First create keys and foreign key references. Then create the following constraints. The speed of a laptop must be at least 800. The only types of printers are laser, ink-jet, and bubble. A model of a product must also be the model of a PC, a laptop, or a printer.

First create keys and foreign key references. Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) First create keys and foreign key references. CREATE TABLE Product ( maker VARCHAR(10), model INT PRIMARY KEY, type VARCHAR(10) ); CREATE TABLE PC ( speed INT, ram INT, hd INT, rd INT, price FLOAT, CONSTRAINT fk_pc FOREIGN KEY(model) REFERENCES Product(model) ON DELETE CASCADE CREATE TABLE Laptop ( model INT PRIMARY KEY, speed INT, ram INT, hd INT, screen INT, price FLOAT, CONSTRAINT fk_lap FOREIGN KEY(model) REFERENCES Product(model) ON DELETE CASCADE );

The speed of a laptop must be at least 800. Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) The speed of a laptop must be at least 800. CREATE TABLE Laptop ( model INT PRIMARY KEY, speed INT CHECK(speed >= 800), ram INT, hd INT, screen INT, price FLOAT, CONSTRAINT fk_lap FOREIGN KEY(model) REFERENCES Product(model) ON DELETE CASCADE );

b) The only types of printers are laser, ink-jet, and bubble. Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) b) The only types of printers are laser, ink-jet, and bubble. CREATE TABLE Printer ( model INT PRIMARY KEY, color VARCHAR(10), type VARCHAR(10) CHECK(type IN ('laser', 'ink-jet', 'bubble')), price FLOAT, CONSTRAINT fk_printer FOREIGN KEY(model) REFERENCES Product(model) ON DELETE CASCADE );

Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) c) A model of a product must also be the model of a PC, a laptop, or a printer. CREATE VIEW ProductSafe(maker,model,type) AS SELECT maker, model, type FROM Product WHERE model IN ( (SELECT model FROM PC) UNION (SELECT model FROM Laptop) UNION (SELECT model FROM Printer) ) WITH CHECK OPTION; Then, we insert into this view as opposed to directly into Product. Also, make the FOREIGN KEY constraints in PC, Laptop, and Printer deferrable initially deferred.

TASK: for each maker who has models at least in one of the tables PC, Laptop, or Printer, determine the maximum price for his products. Output: maker; if there are NULL values among the prices for the products of a given maker, display NULL for this maker, otherwise, the maximum price.

This is task 41 from this website: http://www.sql-ex.ru/

Database description:

The database scheme consists of four tables:

• Product (maker, model, type)
• PC (code, model, speed, ram, hd, cd, price)
• Laptop (code, model, speed, ram, hd, screen, price)
• Printer (code, model, color, type, price)

The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price.

The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen. For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field, printing technology ('Laser', 'Jet', or 'Matrix') – type, and price are specified.

I've tried several queries, most of them worked on the first database but not on the second one

Here's my last tried query:

Your query returned the correct dataset on the first (available) database, but it returned incorrect dataset on the second checking database.

• Wrong number of records (more by 3)