SQL--数据查询

特别提示：图片有水印，请不要直接使用 

 pcshop——Oracle版本

CREATE TABLE customers (
customer_id varchar2(10) NOT NULL,
firstname varchar2(32) default NULL,
lastname varchar2(32) default NULL,
city varchar2(32) default NULL,
address varchar2(128) default NULL,
email varchar2(128) default NULL,
PRIMARY KEY (customer_id),
UNIQUE(email)
) ;
INSERT INTO customers (customer_id, firstname, lastname, city, address, email) VALUES
('1122334455', 'Ann', 'O''Brien', 'Dublin', '1 Jervis St.', '[e-mail]');
INSERT INTO customers (customer_id, firstname, lastname, city, address, email) VALUES ('1231231231', 'John', 'Doe', 'Limerick', NULL, NULL);
INSERT INTO customers (customer_id, firstname, lastname, city, address, email) VALUES ('1234567890', 'Paul', 'Murphy', 'Cork', '20 O Connell St.', NULL);
INSERT INTO customers (customer_id, firstname, lastname, city, address, email) VALUES ('9876543210', 'Jack', 'Murphy', 'Galway', '101 O Connell St.', '[e-mail]');
CREATE TABLE laptops (
model char(4) NOT NULL ,
speed float,
ram int,
hd int,
screen float,
price float,
PRIMARY KEY (model)
) ;


INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES('2001', 2, 2048, 240, 20.1, 3673);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2002', 1.73, 1024, 80, 17, 949);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2003', 1.8, 512, 60, 15.4, 549);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2004', 2, 512, 60, 13.3, 1150);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2005', 2.16, 1024, 120, 17, 2500);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2006', 2, 2048, 80, 15.4, 1700);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2007', 1.83, 1024, 120, 13.3, 1429);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2008', 1.6, 1024, 100, 15.4, 900);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2009', 1.6, 512, 80, 14.1, 680);
INSERT INTO laptops (model, speed, ram, hd, screen, price) VALUES ('2010', 2, 2048, 160, 15.4, 2300);

CREATE TABLE pcs (
model char(4) NOT NULL,
speed float NOT NULL,
ram int NOT NULL,
hd int NOT NULL,
price float NOT NULL,
PRIMARY KEY (model)
) ;

INSERT INTO pcs (model, speed, ram, hd, price) VALUES('1001', 2.66, 1024, 250, 2114);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1002', 2.1, 512, 250, 995);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1003', 1.42, 512, 80, 478);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1004', 2.8, 1024, 250, 649);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1005', 3.2, 512, 250, 630);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1006', 3.2, 1024, 320, 1049);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1007', 2.2, 1024, 200, 510);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1008', 2.2, 2048, 250, 770);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1009', 2, 1024, 250, 650);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1010', 2.8, 2048, 300, 770);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1011', 1.86, 2048, 160, 959);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1012', 2.8, 1024, 160, 649);
INSERT INTO pcs (model, speed, ram, hd, price) VALUES ('1013', 3.06, 512, 80, 529);

CREATE TABLE printers (
model char(4) NOT NULL ,
color varchar2(5),
ptype varchar2(10) ,
price float ,
PRIMARY KEY (model)
) ;

INSERT INTO printers (model, color, ptype, price) VALUES('3001', 'TRUE', 'ink-jet', 99);
INSERT INTO printers (model, color, ptype, price) VALUES ('3002', 'FALSE', 'laser', 239);
INSERT INTO printers (model, color, ptype, price) VALUES ('3003', 'TRUE', 'laser', 899);
INSERT INTO printers (model, color, ptype, price) VALUES ('3004', 'TRUE', 'ink-jet', 120);
INSERT INTO printers (model, color, ptype, price) VALUES ('3005', 'FALSE', 'laser', 120);
INSERT INTO printers (model, color, ptype, price) VALUES ('3006', 'TRUE', 'ink-jet', 100);
INSERT INTO printers (model, color, ptype, price) VALUES ('3007', 'TRUE', 'laser', 200);


CREATE TABLE products (
maker char(1) ,
model char(4) NOT NULL ,
ptype varchar2(10) ,
PRIMARY KEY (model)
) ;
INSERT INTO products (maker, model, ptype) VALUES('A', '1001', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('A', '1002', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('A', '1003', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('B', '1004', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('B', '1005', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('B', '1006', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('C', '1007', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('D', '1008', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('D', '1009', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('D', '1010', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('E', '1011', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('E', '1012', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('E', '1013', 'pc');
INSERT INTO products (maker, model, ptype) VALUES('E', '2001', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('E', '2002', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('E', '2003', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('A', '2004', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('A', '2005', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('A', '2006', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('B', '2007', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('F', '2008', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('F', '2009', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('G', '2010', 'laptop');
INSERT INTO products (maker, model, ptype) VALUES('E', '3001', 'printer');
INSERT INTO products (maker, model, ptype) VALUES('E', '3002', 'printer');
INSERT INTO products (maker, model, ptype) VALUES('E', '3003', 'printer');
INSERT INTO products (maker, model, ptype) VALUES('D', '3004', 'printer');
INSERT INTO products (maker, model, ptype) VALUES('D', '3005', 'printer');
INSERT INTO products (maker, model, ptype) VALUES('H', '3006', 'printer');
INSERT INTO products (maker, model, ptype) VALUES('H', '3007', 'printer');

CREATE TABLE sales (
customer_id varchar2(10) NOT NULL ,
model char(4) NOT NULL ,
quantity int ,
sday date NOT NULL ,
paid float ,
type_of_payment varchar2(32),
PRIMARY KEY (customer_id,model,sday)
) ;

INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('1122334455', '2010', 1, to_date('2013-12-19','yyyy-MM-dd'), 2300, 'mastercard credit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('1122334455', '3001', 1, to_date('2013-12-18','yyyy-MM-dd'), 99, 'cash');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('1231231231', '2002', 2, to_date('2013-12-19','yyyy-MM-dd'), 1898, 'visa credit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('1231231231', '3002', 1, to_date('2013-12-18','yyyy-MM-dd'), 239, 'cash');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('1234567890', '1001', 1, to_date('2013-12-20','yyyy-MM-dd'), 1902.6, 'mastercard credit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('9876543210', '1007', 1, to_date('2013-12-17','yyyy-MM-dd'), 510, 'visa debit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('9876543210', '1007', 3, to_date('2013-12-19','yyyy-MM-dd'), 1530, 'visa debit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('9876543210', '2002', 1, to_date('2013-12-17','yyyy-MM-dd'), 949, 'visa debit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('9999999999', '1007', 1, to_date('2013-12-20','yyyy-MM-dd'), 459, 'visa credit');
INSERT INTO sales (customer_id, model, quantity, sday, paid, type_of_payment) VALUES ('9999999999', '3007', 2, to_date('2013-12-20','yyyy-MM-dd'), 360, 'visa credit');

表结构如下：

• products(maker, model, type) 供应商信息表，maker，model，type取值为PC，Laptop，Printer
• pcs(model, speed, ram, hd, price) 电脑信息表
• laptops(model, speed, ram, hd, screen, price) 笔记本电脑信息表
• printers(model, color, type, price) 打印机信息表
• customers(customer_id, firstname, lastname, city, address, email) 顾客信息表
• sales(customer_id, model, quantity, day, paid, type_of_payment)销售记录表

--单表查询，最简单的查询

1、进行单表查询
1)查询所有speed大于2.8的PC信息

2)查询购买model为1007的购买记录信息

3)统计2013-12-20购买记录的数量（count）
聚集函数

4)统计2013-12-20购买的总数量(sum)

5)查询硬盘大小出现在两种以上PC电脑上的硬盘大小

Find those hard-disk sizes that occur in two or more PCs

6) 查询速度至少3.00以上的PC models信息

--连接查询 嵌套查询

7)查询哪个供应商供应laptops硬盘至少100GB以上的供应商信息

 Which makers make laptops with a hard disk of at least 100 GB

--1、做等值连接

--2、自然连接(natural join)

--3、内连接

--4、自然连接(join using)

--5、子查询实现
--不相关子查询（子查询的SQL语句可以单独执行）

--相关子查询（子查询的SQL语句不能够单独执行，
必须嵌在父查询中，作为父查询的条件执行）

--所有子查询都可以写成相关子查询
--有些相关子查询可以用不相关子查询来代替
--所有不相关子查询都可以用相关子查询来代替

8) 查询供应商B所提供的所有产品的产品编号和产品价格。

Find the model number and price of all products (of any type) made by maker B

9)查找所有的彩色打印机的model numbers。

Find the model numbers of all color laser printers

10)查找供应laptops但不供应pc的供应商信息。

 Find those makers that sell Laptops but not PCs

 

11) 查询具有相同运行速度和内存的PC电脑编号对

每个pc models对只出现一次，即 (i, j)出现，则 (j, i)不出现

Find those pairs of PC models that have both the same speed and ram.
A pair should be listed only once; e.g., list (i, j) but not (j, i).
--典型的表的自身连接查询

12) 查询销售三种不同型号PC电脑的供应商

Find the makers who sell exactly three different models of PC

 

13) 查询至少提供3种PC运行速度的供应商

Find the makers of PCs with at least three different speeds

14) 查询提供的PC或laptop运行速度至少是2.80且有两种以上产品的供应商

Find those makers of at least two different computers (PCs or laptops)
with speeds of at least 2.80
--想让大家用视图解决问题
--from子句中应用别名方式给子查询命名名称也可以

15) 查询提供的电脑（PC or laptop）具有最高运行速度的供应商

Find the maker(s) of the computer
(PC or laptop) with the highest available speed

--想让大家用视图解决问题