Sql 作业
作业1：
答：文章来源：https://www.toymoban.com/news/detail-628326.html
create table Employee
(Id integer not null,
Name varchar(32) ,
Salary integer,
departmentId integer,
primary key (Id)
);
create table Department(
Id integer primary key,
Name varchar(30) not null
);
insert into employee values (1,"Joe",70000,1);
insert into employee values (2,"Henry",80000,2);
insert into employee values (3,"Sam",60000,2);
insert into employee values (4,"Max",90000,1);
insert into department values(1,"IT");
insert into department values(2,"Sales");
SELECT departmentId, (
SELECT Name FROM employee
WHERE departmentId = e.departmentId AND salary = MAX(e.salary)
) AS Employee,MAX(salary) AS max_salary
FROM employee e
GROUP BY departmentId
答：
create table seat(
id int primary key,
Name varchar(30) not null
);
insert into seat values(1,"Abbot");
insert into seat values(2,"Doris");
insert into seat values(3,"Emerson");
insert into seat values(4,"Green");
insert into seat values(5,"Jeames");
select * from seat;
SELECT(
CASE
WHEN MOD(id,2)=1 AND id=(SELECT COUNT(id) FROM seat) THEN id
WHEN MOD(id,2)=0 THEN id-1
ELSE id+1
END
) AS id
,name
FROM seat
ORDER BY id;
解决相关方法：
答：
create table score(
class int primary key,
score_avg int
);
insert into score values(1,93);
insert into score values(2,93);
insert into score values(3,93);
insert into score values(4,91);
select class,score_avg,
rank() over (order by score_avg desc) as rank1,
dense_rank() over (order by score_avg desc) as rank2,
row_number() over(order by score_avg desc) as rank3
from score;
答：
create table number(
id int primary key,
Num int
);
insert into number values(1,1);
insert into number values(2,1);
insert into number values(3,1);
insert into number values(4,2);
insert into number values(5,1);
insert into number values(6,2);
insert into number values(7,2);
select * from number;
select distinct num as ConsecutiveNums
from (select num,if(@prev_num = num,@count:=@count+1,@count:=1)
as consecutive_count, @prev_num:=num
from number
cross join(select @count:=0,@prev_num:=NULL) as vars
order by id
)as t
where consecutive_count>=3
答：
create table tree(
id int primary key,
p_id int
);
insert into tree values(1,null);
insert into tree values(2,1);
insert into tree values(3,1);
insert into tree values(4,2);
insert into tree values(5,2);
select * from tree;
select id , case
when p_id is null then "Root"
when id in (select p_id from tree) then "Inner"
else "Leaf"
end as Type
from tree;
答：
create table Employee2(
Id int primary key,
name varchar(50),
Department varchar(50),
ManagerId int
);
insert into employee2 values(101,"John","A",null);
insert into employee2 values(102,"Dan","A",101);
insert into employee2 values(103,"James","A",101);
insert into employee2 values(104,"Amy","A",101);
insert into employee2 values(105,"Anne","A",101);
insert into employee2 values(106,"Ron","B",101);
select * from employee2;
select name
from (select ManagerId , count(ManagerId) as M_count
from employee2
group by ManagerId
) as ManagerCount , employee2
where ManagerCount.M_count = 5 and ManagerCount.ManagerId = employee2.Id
答：
drop table if exists survey_log;
create table survey_log(
uid int ,
action varchar(20) check(action = "show" or action="answer" or action = "skip"),
question_id int,
answer_id int,
q_num int,
timestamp timestamp
);
insert into survey_log values(5,"show",285,null,1,123);
insert into survey_log values(5,"answer",285,124124,1,124);
insert into survey_log values(5,"show",369,null,2,125);
insert into survey_log values(5,"skip",369,null,2,126);
select * from survey_log;
select * from survey_log
where action = "show" or action = "answer";
select question_id, count(*) as show1 from survey_log
where action = "show"
group by question_id;
select count_answer.question_id,max(answer/show1)
from
(select question_id, count(*) as show1 from survey_log
where action = "show"
group by question_id) as count_show,
(select question_id, count(*) as answer from survey_log
where action = "answer"
group by question_id) as count_answer
where count_answer.question_id = count_show.question_id
group by count_answer.question_id
答：文章来源地址https://www.toymoban.com/news/detail-628326.html
select * from employee;
select * from department;
insert into employee value(5,"Janet",69000,1);
insert into employee value(6,"Randy",85000,1);
select department.Name as department,rank_employee.name as Employee,Salary
from (select name,salary,departmentId,
row_number() over (partition by departmentId order by salary desc) as rank1
from employee)
as rank_employee,department
where rank1 <=3 and department.id = rank_employee.departmentId
到了这里，关于wonderful-sql 作业的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
