SQL 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 code 16 -- 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);

question.sql

 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;

 

SQL 1

标签：name   store   ima   products   apply   ref   code   har   eal