create database lovo80;
use lovo80;
--全球国家信息表
create table BBC(
Name Varchar(50) primary key,
Region Varchar(60),
Area Decimal(10),
Population Decimal(11),
Gdp Decimal(14),
)
insert into BBC(Name,Region,Area,Population,Gdp) values('中国','亚洲',9600000, 1300000000,1000);
insert into BBC(Name,Region,Area,Population,Gdp) values('美国','美洲',11000000, 300000000,1500);
insert into BBC(Name,Region,Area,Population,Gdp) values('法国','欧洲',3300000, 100000000,900);
insert into BBC(Name,Region,Area,Population,Gdp) values('德国','欧洲',2300000, 30000000,800);
insert into BBC(Name,Region,Area,Population,Gdp) values('意大利','欧洲',1300000, 90000000,1100);
insert into BBC(Name,Region,Area,Population,Gdp) values('俄罗斯','欧洲',13000000,300000000,500);
insert into BBC(Name,Region,Area,Population,Gdp) values('印度','非洲',5600000, 700000000,300);
insert into BBC(Name,Region,Area,Population,Gdp) values('日本','亚洲',600000, 60000000,800);
insert into BBC(Name,Region,Area,Population,Gdp) values('韩国','亚洲',500000, 70000000,1200);
select Name,Population from BBC where Population > 200000000; --人口超过2亿的国家
select Name,Gdp from BBC where Population > 200000000; --人口不小于2亿的国家人均Gdp
select Name,floor(Population/1000000) as Population from BBC where Region = '亚洲'; --属于亚洲国家人口除以1000000取整
select Name,Population from BBC where Name in ('法国','德国','意大利'); --法国 德国 意大利国家名称人口
select Name from BBC where Name like '美%'; --名字中包含美字的国家
select distinct Region from BBC; --全球有那些地区去掉重复的
select Name,Population from BBC where Population > 100000000 order by Population desc ; --人口大于1亿降序排序
create table nobel(
id int primary key,
yr int,
Subject varchar(15),
winner varchar(50),
z_id int foreign key(z_id) references zt(z_id),
)
insert into nobel(id,yr,Subject,winner,z_id) values(1,1950,'诺贝尔奖','罗素',3);
insert into nobel(id,yr,Subject,winner,z_id) values(2,1962,'文学奖','莱辛',3);
insert into nobel(id,yr,Subject,winner,z_id) values(3,1921,'诺贝尔物理学奖','Albert Einstein',1);
insert into nobel(id,yr,Subject,winner,z_id) values(4,1966,'平奖','马利肯',3);
insert into nobel(id,yr,Subject,winner,z_id) values(5,1970,'平奖','莱洛伊尔',3);
insert into nobel(id,yr,Subject,winner,z_id) values(6,2000,'平奖','白川秀树',4);
insert into nobel(id,yr,Subject,winner) values(7,2001,'平奖','威廉·诺尔斯');
insert into nobel(id,yr,Subject,winner,z_id) values(8,1980,'文学奖','米洛什',4);
insert into nobel(id,yr,Subject,winner,z_id) values(9,1987,'文学奖','Joseph Brodsky',4)
insert into nobel(id,yr,Subject,winner,z_id) values(10,1989,'文学奖','塞拉',4);
insert into nobel(id,yr,Subject,winner,z_id) values(11,1908,'诺贝尔化学奖','ErnestRutherford',1);
select winner from nobel where yr = 1950 and Subject = '诺贝尔奖'; --1950诺贝尔奖得主
select winner from nobel where yr = 1962 and Subject = '文学奖'; --1962文学奖得主
select yr,Subject from nobel where winner = 'Albert Einstein'; --显示Albert Einstein获奖年份和奖项
select winner from nobel where Subject = '平奖' and yr <= 2000; --2000年以来包含2000年评奖得主
select * from nobel where Subject = '文学奖' and yr between 1980 and 1989; --1980-1989文学奖得主所以信息
select winner from nobel where winner like 'Joseph%'; --名字第一个字符为Joseph获奖者名称
select distinct Subject from nobel; --所有奖项只出现一次
select yr,Subject,winner from nobel where z_id in (1,2,3,4); --获奖者为下列总统的所以信息 Theodore Roosevelt Woodrow Wilson Jed Bartlet immy Carter
create table zt(
z_id int primary key,
z_oldyz int,
z_newyz int,
z_name char(30),
)
insert into zt(z_id,z_oldyz,z_newyz,z_name) values(1,1900,1925,'Theodore Roosevelt');
insert into zt(z_id,z_oldyz,z_newyz,z_name) values(2,1926,1945,'Woodrow Wilson');
insert into zt(z_id,z_oldyz,z_newyz,z_name) values(3,1946,1973,'Jed Bartlet');
insert into zt(z_id,z_oldyz,z_newyz,z_name) values(4,1974,2000,'Jimmy Carter');
alter table nobel add z_id int;
alter table nobel drop column z_id;
