SQL：第四课

第五章：



练习1：
USE t103;

CREATE TABLE t_bbc(
pk_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
b_name VARCHAR(50) NOT NULL,
b_region VARCHAR(60),
b_area DECIMAL(10), -- 单位:平方公里
b_population DECIMAL(11),
b_gdp DECIMAL(14)   --  单位:美元
);

DROP TABLE t_bbc;

INSERT INTO t_bbc
VALUES  (NULL,'china','asia',12600000,1300000000,8300000000000),
(NULL,'india','south asia',2980000,1210193422,1848000000000),
(NULL,'italy','europe',301338,60813326,2198000000000),
(NULL,'germany','europe',357021,81799600,3366000000000),
(NULL,'United states of America','north america',9629091,308750000,15000000000000),
(NULL,'The Republic of France','europe',551602,63860000,2550000000000);

DELETE FROM t_bbc;
TRUNCATE TABLE t_bbc;

SELECT *FROM t_bbc;

-- 人口不小于2亿的国家名称
SELECT b_name FROM t_bbc WHERE b_population>=200000000;

-- 人口不小2亿的国家的名称和人均GDP
SELECT b_name,b_gdp/b_population AS 人均GDP FROM t_bbc
WHERE b_population>=200000000;

-- 给出地区属于south asia的国家的名称和人口数(以百万为单位)
SELECT b_name,ROUND(b_population/1000000) AS 人口数
FROM t_bbc WHERE b_region='south asia';

-- 给出法国,德国和意大利的国家名称和人口
SELECT pk_id,b_name,b_population FROM t_bbc
WHERE b_name IN ('The Republic of France','germany','italy');

-- 给出包含'United'字符国家名称
SELECT pk_id,b_name FROM t_bbc WHERE b_name LIKE '%United%';

-- 显示t_bbc中一共有哪些地区
SELECT DISTINCT b_region FROM t_bbc;

-- 显示第个人口超过1亿国家的名称和人口,这些国家以人口数作降序排列
SELECT pk_id,b_name,b_population FROM t_bbc
WHERE b_population>100000000 ORDER BY b_population DESC;

练习2：
USE t103;

CREATE TABLE t_nobel(
pk_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
n_yr INT,
n_subject VARCHAR(15),
n_winner VARCHAR(50)
);

INSERT INTO t_nobel
VALUES  (NULL,1950,'和平奖','Johnson Bunche'),
(NULL,1950,'化学奖','Kurt Alder'),
(NULL,1950,'化学奖','Otto Paul Hermann Diels'),
(NULL,1950,'医学奖','Tadeus Reichstein'),
(NULL,1950,'医学奖','Edward Calvin Kendall'),
(NULL,1962,'文学奖','John Perse'),
(NULL,1921,'物理学奖','Albert Einstein'),
(NULL,1906,'和平奖','Theodore Roosevelt'),
(NULL,1919,'和平奖','Woodrow Wilson'),
(NULL,1950,'经济学奖','Jed Bartlet'),
(NULL,2002,'和平奖','Jimmy Carter'),
(NULL,2000,'和平奖','金大中'),
(NULL,2001,'和平奖','安南'),
(NULL,2000,'化学奖','Hideki Shirakawa'),
(NULL,1980,'文学奖','米沃什'),
(NULL,1981,'文学奖','卡内蒂'),
(NULL,1982,'文学奖','马尔克斯');

SELECT *FROM t_nobel;

SELECT n_winner FROM t_nobel WHERE n_yr=1950;

SELECT n_winner FROM t_nobel
WHERE n_yr=1962 AND n_subject='文学奖';

SELECT n_yr,n_subject FROM t_nobel WHERE n_winner='Albert Einstein';

SELECT n_winner FROM t_nobel
WHERE n_yr>=2000 AND n_subject='和平奖';

SELECT * FROM t_nobel
WHERE  n_subject='文学奖' AND n_yr BETWEEN 1980 AND 1989;

SELECT * FROM t_nobel
WHERE  n_winner IN ('Theodore Roosevelt','Woodrow Wilson','Jed Bartlet','Jimmy Carter');

SELECT n_winner FROM t_nobel WHERE n_winner LIKE 'John%';

SELECT DISTINCT n_subject FROM t_nobel;

练习3：
USE t103;

CREATE TABLE t_film(
pk_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
f_name CHAR(50),
f_daoyan VARCHAR(50),
f_time YEAR,
f_money FLOAT
);

INSERT INTO t_film
VALUES (NULL,'人在囧途','叶伟明',2010,8000000),
(NULL,'黑客帝国','安迪·沃卓斯基',1999,63000000),
(NULL,'冰河时代','卡洛斯·沙尔丹哈/克里斯·韦奇',2002,59000000),
(NULL,'暮光之城','凯瑟琳·哈德威克',2008,37000000),
(NULL,'盗梦空间','克里斯托弗·诺兰',2010,19400000),
(NULL,NULL,NULL,NULL,NULL),
(NULL,'t103的故事','周周',2013,300000);

/* 1、查询电影表的所有信息
2、查询有哪些导演拍了电影
-- 3、查询这张表的前五行数据
4、查询电影名称叫《人在囧途》的电影信息
5、查询耗资成本超过一千万的电影有哪些
6、查询年份在2000年 以后耗资成本低于一百万的电影有哪些
7、查询电影在2000年到2005间出过哪些电影
8、查询电影名称中包含有人的电影名称有哪些
9、查询电影名称为（null） 的电影有哪些
10、查询电影名称为《黑客帝国》、《冰河时代》、《暮光之城》的信息
*/

SELECT *FROM t_film;

SELECT DISTINCT f_daoyan FROM t_film;

SELECT *FROM t_film WHERE pk_id >=1 AND pk_id<=5;

SELECT *FROM t_film WHERE f_name='人在囧途';

SELECT *FROM t_film WHERE f_money>10000000;

SELECT *FROM t_film WHERE f_time>2000 AND f_money<1000000;

SELECT *FROM t_film WHERE f_time BETWEEN 2000 AND 2005;

SELECT *FROM t_film WHERE f_name LIKE '%人%';

SELECT *FROM t_film WHERE f_name IS NULL;

SELECT *FROM t_film WHERE f_name IN ('黑客帝国','冰河时代','暮光之城');