ASSIGNMENT 06
Student ID: 1312311
Full name: Hùynh Văn Lâm
ASSIGNMENT 06
1).
Exercises 6.5.1: Write the following database modifications, based on the database schema
Product (maker , model , type)
PC (model , speed , ram , hd , rd , price)
Laptop (model , speed , ram , hd , screen , price)
Printer (model , color , type , price) of Exercise 5.2.1. Describe the effect of the modifications on the data of that exercise.
a)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 PC
VALUES (1100, 1800, 256, 80, ‘20x DVD’, 2499)
c) Delete all PC 's with less than 20 gigabytes of hard disk.
DELETE FROM PC
WHERE HD < 20
d) Delete all Laptops made by a manufacturer that doesn 't make printers.
DELETE FROM Product
WHERE maker in (SELECT DISTINCT maker
FROM Product
EXCEPT
SELECT DISTINCT maker
FROM Product
WHERE type = ‘printer’)
e) Manufacturer A buys manufacturer B. Change all products made by B so they are now made by A.
UPDATE Product
SET maker = ‘A’ WHERE maker = ‘B’
f) For each PC, double the amount of RAM and add 20 gigabytes to the amount of hard disk. (Remember that several attributes can be changed by one UPDATE statement.) !
UPDATE PC
SET ram = ram * 2, hd = hd + 20 WHERE model IN (SELECT model FROM PC)
g) 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 Laptop LAP join Product PROD on LAP.model = PROD.model)
Exercises 6.5.2: Write the following database modifications, based on the database schema
Classes(class , type , country , numGuns , bore , displacement)
Ships (name , class , launched)
Battles (name , date) Outcomes (ship , battle , result ) of Exercise 5.2.4. Describe the effect of the modifications on the data of that exercise.
a) The two British battleships of the
Full name: Hùynh Văn Lâm
ASSIGNMENT 06
1).
Exercises 6.5.1: Write the following database modifications, based on the database schema
Product (maker , model , type)
PC (model , speed , ram , hd , rd , price)
Laptop (model , speed , ram , hd , screen , price)
Printer (model , color , type , price) of Exercise 5.2.1. Describe the effect of the modifications on the data of that exercise.
a)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 PC
VALUES (1100, 1800, 256, 80, ‘20x DVD’, 2499)
c) Delete all PC 's with less than 20 gigabytes of hard disk.
DELETE FROM PC
WHERE HD < 20
d) Delete all Laptops made by a manufacturer that doesn 't make printers.
DELETE FROM Product
WHERE maker in (SELECT DISTINCT maker
FROM Product
EXCEPT
SELECT DISTINCT maker
FROM Product
WHERE type = ‘printer’)
e) Manufacturer A buys manufacturer B. Change all products made by B so they are now made by A.
UPDATE Product
SET maker = ‘A’ WHERE maker = ‘B’
f) For each PC, double the amount of RAM and add 20 gigabytes to the amount of hard disk. (Remember that several attributes can be changed by one UPDATE statement.) !
UPDATE PC
SET ram = ram * 2, hd = hd + 20 WHERE model IN (SELECT model FROM PC)
g) 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 Laptop LAP join Product PROD on LAP.model = PROD.model)
Exercises 6.5.2: Write the following database modifications, based on the database schema
Classes(class , type , country , numGuns , bore , displacement)
Ships (name , class , launched)
Battles (name , date) Outcomes (ship , battle , result ) of Exercise 5.2.4. Describe the effect of the modifications on the data of that exercise.
a) The two British battleships of the