1 --sqlite3 table building code 2 CREATE TABLE Manufacturers ( 3 Code INTEGER PRIMARY KEY NOT NULL, 4 Name TEXT NOT NULL  5 ); 6  7 CREATE TABLE Products ( 8 Code INTEGER PRIMARY KEY NOT NULL, 9 Name TEXT NOT NULL ,10 Price REAL NOT NULL ,11 Manufacturer INTEGER NOT NULL 12 CONSTRAINT fk_Manufacturers_Code REFERENCES MANUFACTURERS(Code)13 );14 15 --MYSQL table building code16 -- CREATE TABLE Manufacturers (17 --   Code INTEGER,18 --   Name VARCHAR(255) NOT NULL,19 --   PRIMARY KEY (Code)   20 -- );21 22 -- CREATE TABLE Products (23 --   Code INTEGER,24 --   Name VARCHAR(255) NOT NULL ,25 --   Price DECIMAL NOT NULL ,26 --   Manufacturer INTEGER NOT NULL,27 --   PRIMARY KEY (Code), 28 --   FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code)29 -- ) ENGINE=INNODB;30 31 INSERT INTO Manufacturers(Code,Name) VALUES(1,‘Sony‘);32 INSERT INTO Manufacturers(Code,Name) VALUES(2,‘Creative Labs‘);33 INSERT INTO Manufacturers(Code,Name) VALUES(3,‘Hewlett-Packard‘);34 INSERT INTO Manufacturers(Code,Name) VALUES(4,‘Iomega‘);35 INSERT INTO Manufacturers(Code,Name) VALUES(5,‘Fujitsu‘);36 INSERT INTO Manufacturers(Code,Name) VALUES(6,‘Winchester‘);37 38 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,‘Hard drive‘,240,5);39 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,‘Memory‘,120,6);40 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,‘ZIP drive‘,150,4);41 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,‘Floppy disk‘,5,6);42 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,‘Monitor‘,240,1);43 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,‘DVD drive‘,180,2);44 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,‘CD drive‘,90,2);45 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,‘Printer‘,270,3);46 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,‘Toner cartridge‘,66,3);47 INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,‘DVD burner‘,180,2);

 1 -- 1.1 Select the names of all the products in the store. 2 select name  3 from products; 4  5 -- 1.2 Select the names and the prices of all the products in the store. 6 select name,price  7 from products; 8  9 -- 1.3 Select the name of the products with a price less than or equal to $200.10 select name 11 from products 12 where price <= 200;13 14 -- 1.4 Select all the products with a price between $60 and $120.15 select * 16 from products 17 where price >=60 and price <= 120;18 19 -- 1.5 Select the name and price in cents (i.e., the price must be multiplied by 100).20 select name,price*100 21 from products;22 23 -- 1.6 Compute the average price of all the products.24 select avg(price) 25 from products;26 27 -- 1.7 Compute the average price of all products with manufacturer code equal to 2.28 select avg(price) 29 from products 30 where manufacturer = 2;31 32 -- 1.8 Compute the number of products with a price larger than or equal to $180.33 select count(price) 34 from products 35 where price >= 180;36 37 -- 1.9 Select the name and price of all products with a price larger than or equal to $180, and sort first by price (in descending order), and then by name (in ascending order).38 select name,price 39 from products 40 where price >= 180 41 order by price desc,name asc;42 43 -- 1.10 Select all the data from the products, including all the data for each product‘s manufacturer.44 select * 45 from products,manufacturers 46 where products.manufacturer = manufacturers.code;47 48 -- 1.11 Select the product name, price, and manufacturer name of all the products.49 select products.name,price,manufacturers.name 50 from products,manufacturers 51 where products.manufacturer = manufacturers.code;52 53 -- 1.12 Select the average price of each manufacturer‘s products, showing only the manufacturer‘s code.54 select manufacturer,avg(price) 55 from products 56 group by manufacturer;57 58 -- 1.13 Select the average price of each manufacturer‘s products, showing the manufacturer‘s name.59 select manufacturers.name,avg(price) 60 from products,manufacturers 61 where products.manufacturer = manufacturers.code 62 group by manufacturers.name;63 64 -- 1.14 Select the names of manufacturer whose products have an average price larger than or equal to $150.65 select manufacturers.name 66 from products,manufacturers 67 where products.manufacturer = manufacturers.code 68 group by manufacturers.name 69 having avg(price) >= 150;70 71 -- 1.15 Select the name and price of the cheapest product.72 select name,min(price) 73 from products;74 75 -- 1.16 Select the name of each manufacturer along with the name and price of its most expensive product.76 select manufacturers.name,products.name,max(price) 77 from products,manufacturers 78 where products.manufacturer = manufacturers.code 79 group by manufacturers.name;80 81 -- 1.17 Add a new product: Loudspeakers, $70, manufacturer 2.82 insert into products (code,name,price,manufacturer)83 values (11,‘Loudspeakers‘,70,2);84 85 -- 1.18 Update the name of product 8 to "Laser Printer".86 update products 87 set name = ‘Laser Printer‘88 where code = 8;89 90 -- 1.19 Apply a 10% discount to all products.91 update products 92 set price=price*0.9;93 94 -- 1.20 Apply a 10% discount to all products with a price larger than or equal to $120.95 update products 96 set price=price*0.9 97 where price >= 120;