SQl server 练习题6

课后作业

作业 1：查询 2022 年毕业用户的刷题记录

建表语句
CREATE TABLE questions_pass_record (
user_id int NOT NULL,
question_type varchar(32) NOT NULL,
device varchar(14) NOT NULL,
pass_count int NOT NULL,
date date NOT NULL);
CREATE TABLE user_info (
user_id int NOT NULL,
graduation_year int NOT NULL,
register_time datetime NOT NULL,
gender varchar(14),
age int,
university varchar(32) NOT NULL );
数据插入语句
INSERT INTO questions_pass_record VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record VALUES(102, 'sql', 'pc', 15,'2021-07-07');

INSERT INTO questions_pass_record VALUES(102, 'python', 'pc', 9,'2021-04-09');
INSERT INTO questions_pass_record VALUES(104, 'python', 'app',3,'2022-03-17');
INSERT INTO questions_pass_record VALUES(105, 'sql', 'pc', 60, '2018-08-15');
INSERT INTO questions_pass_record VALUES(104, 'sql', 'pc', 20, '2019-05-15');
I

NSERT INTO user_info VALUES(101, 2022, '2021-03-01 11:22:33', 'male', 27, '北
京大学');
INSERT INTO user_info VALUES(102, 2023, '2022-05-09 09:50:34', 'female', 31,'
清华大学');
INSERT INTO user_info VALUES(103, 2021, '2022-03-09 15:10:50', 'male', null, '
复旦大学');
INSERT INTO user_info VALUES(104, 2020, '2018-08-12 10:00:00', null, 23,'墨尔
本大学');
INSERT INTO user_info VALUES(105, 2022, '2020-11-09 22:01:03', 'female', 26, '
北京大学');
INSERT INTO user_info VALUES(210, 2022, '2022-03-09 01:07:09', 'male', 20, '上
海交通大学');

查询语句：

select user_info.user_id,question_type,device,pass_count,date 
from user_info inner join questions_pass_record 
on user_info.user_id=questions_pass_record.user_id 
where graduation_year=2022;

结果应是：

作业 2：查询 2022 年以来刷题用户的用 user_id 和毕业院校表数据使用练习一的即可

questions_pass_recorduser_info
查询语句:

select user_info.user_id,university 
from user_info full join questions_pass_record 
on user_info.user_id=questions_pass_record.user_id 
where year(date)>=2022;

结果应是

---

作业 3：查询被投递过的职位信息

建表语句
CREATE TABLE deliver_record (
user_id int NOT NULL, -- 投递用户 id
job_id int NOT NULL, -- 投递职位 ID
platform varchar(10) NOT NULL, -- 投递平台
resume_id int NOT NULL, -- 投递的简历 ID
resume_if_checked int NOT NULL, -- 简历是否被查看 1 被查看 0 未被查看

deliver_date date NOT NULL); -- 投递日期
CREATE TABLE job_info (
job_id int NOT NULL, -- 职位 id
boss_id int NOT NULL, -- hr id
company_id int NOT NULL, -- 公司 id
post_time datetime NOT NULL, -- 职位发布时间
salary int, -- 职位工资
job_city varchar(32) NOT NULL ); -- 职位城市
数据插入语句
INSERT INTO deliver_record VALUES(101, 18903, 'app', 308897, 1, '2021-03-01');
INSERT INTO deliver_record VALUES(102, 21089, 'pc', 154906, 0, '2022-07-07');
INSERT INTO deliver_record VALUES(102, 22869, 'pc', 967389, 1, '2022-04-09');
INSERT INTO deliver_record VALUES(104, 16739, 'app', 327368, 0, '2018-09-17');
INSERT INTO deliver_record VALUES(105, 34992, 'pc', 600367, 0, '2020-11-15');
INSERT INTO deliver_record VALUES(104, 22889, 'pc', 202819, 1, '2022-05-15');
INSERT INTO job_info VALUES(18903, 202, 3, '2021-03-01 11:22:33', 112000, '北京');
INSERT INTO job_info VALUES(21089, 203, 6, '2022-05-09 09:50:34', 78000, '西安');
INSERT INTO job_info VALUES(22869, 204, 2, '2022-03-09 15:10:50', 92000, '上海');
INSERT INTO job_info VALUES(16739, 204, 6, '2018-08-12 10:00:00', 62000, '杭州');
INSERT INTO job_info VALUES(34992, 205, 9, '2020-11-09 22:01:03', 123000, '北京');

INSERT INTO job_info VALUES(22889, 206, 16, '2022-03-09 01:07:09', 150000, '上海');
查询语句：

select job_info.job_id,boss_id,company_id,post_time,salary,job_city 
from job_info inner join deliver_record on job_info.job_id=deliver_record.job_id;

结果应是：

---

作业 4：查询每个公司查看过的投递用户数

建表语句使用练习三的即可
deliver_record

job_info
查询语句：

select company_id,sum(resume_if_checked) 
from job_info inner join deliver_record on job_info.job_id=deliver_record.job_id 
group by company_id;

结果应是：

---

作业 5： 判断其是否有过购买记录

建表语句：
CREATE TABLE customers_info (
customer_id int NOT NULL, -- 客户 id
gender varchar(10) NOT NULL, -- 客户性别
city varchar(32) NOT NULL, -- 客户所在城市
country varchar(32) NOT NULL, -- 客户所在国家
age int, -- 客户年龄

latest_place_order_date date ); -- 客户最近购买日期
数据插入语句：
INSERT INTO customers_info VALUES(18903, 'male', '北京', '中国', 23,
'2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66,
'2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null,
'2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);
INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19,
'2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);
查询语句：

select customer_id,
iif(latest_place_order_date is null,'没有购买记录','有购买记录') as 是否有购买记录  
from customers_info;

结果应是：

---

作业 6：请按城市对客户进行排序,如果城市为空，则
按国家排序

建表语句
DROP TABLE customers_info;
CREATE TABLE customers_info (
customer_id int NOT NULL, -- 客户 id
gender varchar(10) NOT NULL, -- 客户性别
city varchar(32) , -- 客户所在城市
country varchar(32) NOT NULL, -- 客户所在国家
age int, -- 客户年龄
latest_place_order_date date ); -- 客户最近购买日期
数据插入语句

INSERT INTO customers_info VALUES(18903, 'male', NULL, '中国', 23,
'2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66,
'2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null,
'2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);
INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19,
'2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);
查询语句：

select * from customers_info order by iif(city is null,country,city);

结果应是：