pgsql数据库作业练习1

框架

建库建表语句

-- LINK: The_computer_store
-- RDBMS: postgres

create database db1;

\c db1;

CREATE TABLE Manufacturers (
  Code INTEGER,
  Name VARCHAR(255) NOT NULL,
  PRIMARY KEY (Code)   
);

CREATE TABLE Products (
  Code INTEGER,
  Name VARCHAR(255) NOT NULL ,
  Price DECIMAL NOT NULL ,
  Manufacturer INTEGER NOT NULL,
  PRIMARY KEY (Code), 
  FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code)
);

INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');
INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');
INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');
INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');
INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');
INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);

作业及练习解答

-- LINK: The_computer_store
-- 1.1 Select the names of all the products in the store.
SELECT name FROM products LIMIT 10;

Select * from products LIMIT 10;

-- 1.2 Select the names and the prices of all the products in the store.
SELECT name , price FROM products LIMIT 10;

-- 1.3 Select the name of the products with a price less than or equal to $200.
SELECT name FROM products
WHERE price <= 200;

-- 1.4 Select all the products with a price between $60 and $120.
SELECT * FROM products
WHERE price < 120 and price >60;


select * from products 
where price between 60 and 120;

-- 1.5 Select the name and price in cents (i.e., the price must be multiplied by100).
SELECT name, (price)*100 FROM products;

---以美分显示，concat连接
select name, concat(price*100, ' cents') from products;

-- 1.6 Compute the average price of all the products.
SELECT avg(price) FROM products;

-- 1.7 Compute the average price of all products with manufacturer code equal to 2.
SELECT avg(price) FROM products
WHERE manufacturer = 2;

-- 1.8 Compute the number of products with a price larger than or equal to $180.
SELECT count(*) FROM products 
WHERE price >= 180;

-- 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).
SELECT name, price FROM products
WHERE price >=180 
ORDER by price desc;

SELECT name, price FROM products
WHERE price >=180 
ORDER by name;
---同时排序
SELECT name, price FROM products
WHERE price >=180 
ORDER by price desc, name asc;

-- 1.10 Select all the data from the products, including all the data for each product's manufacturer.
------两表连接

---
select a.*, b.name from products a join Manufacturers b on(a.manufacturer = b.code);

select a.*, b.name from products a, Manufacturers b where a.manufacturer = b.code;


-- 1.11 Select the product name, price, and manufacturer name of all the products.
SELECT name, price, manufacturer FROM products;

---
select a.name, a.price, b.name 
from products a join Manufacturers b on(a.manufacturer = b.code);

SELECT Products.Name, Price, Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;

-- 1.12 Select the average price of each manufacturer's products, showing only the manufacturer's code.
SELECT avg(price), manufacturer FROM products
group by manufacturer;

-- 1.13 Select the average price of each manufacturer's products, showing the manufacturer's name.
SELECT avg(price), name FROM products
group by name;
---
select avg(a.price), b.name 
from Products a join Manufacturers b 
on a.manufacturer = b.code
group by b.name;

-- 1.14 Select the names of manufacturer whose products have an average price larger than or equal to $150.???????????????
SELECT name, avg(price) as ap FROM products
WHERE  ap >= 150 
group by name;

----
select avg(a.price), b.name 
from Manufacturers b join Products a 
on b.code = a.Manufacturer
group by b.name
having avg(a.price)>=150;
 
SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;

-- 1.15 Select the name and price of the cheapest product.
SELECT name FROM products
ORDER by price 
LIMIT 1;

SELECT name FROM products
WHERE price =(
    SELECT min(price) FROM products
);

-- 1.16 Select the name of each manufacturer along with the name and price of its most expensive product.
SELECT manufacturer, name, price FROM products
ORDER by price desc
LIMIT 1;

---
select a.Name, max(a.price), b.Name
from Manufacturers b join Products a 
on a.Manufacturer = b.code
group by b.name;
 
 
SELECT Products.Name, MAX(Price), Manufacturers.Name
 FROM Products, Manufacturers
 WHERE Manufacturer = Manufacturers.Code
 GROUP BY Manufacturers.Name;
 
select p.name,m.name,p.price from products p join manufacturers m on p.manufacturer = m.code where p.price in (
select min(a.price)
from Manufacturers b join Products a 
on a.Manufacturer = b.code
group by b.name);

-- 1.17 Add a new product: Loudspeakers, $70, manufacturer 2.
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(11,'Loudspeakers',70,2);

-- 1.18 Update the name of product 8 to "Laser Printer".
UPDATE products
set name = 'Laser Printer'
where code = 8;

-- 1.19 Apply a 10% discount to all products.
UPDATE products
set price = 0.9 * price;

-- 1.20 Apply a 10% discount to all products with a price larger than or equal to $120.
UPDATE products
set price = 0.9 * price
WHERE price >= 120;


---------判断被整除------
SELECT * FROM products
WHERE price in (SELECT price from products where price %100 = 0 );

笔记（待补）