SQL练习题(1)-牛客网

牛客网在线编程网址：https://www.nowcoder.com/activity/oj

(默认使用SQLite)

题目1：

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

select EMP.emp_no, MGR.emp_no manager_no, EMP.salary emp_salary, MGR.salary manager_salary
FROM 
(
    Select dept_emp.emp_no, salary, dept_no from dept_emp INNER JOIN salaries 
    ON dept_emp.emp_no = salaries.emp_no
    AND dept_emp.to_date = '9999-01-01'
    AND salaries.to_date = '9999-01-01'
) EMP 
INNER JOIN 
(
    Select dept_manager.emp_no, salary, dept_no from dept_manager INNER JOIN salaries 
    ON dept_manager.emp_no = salaries.emp_no
    AND dept_manager.to_date = '9999-01-01'
    AND salaries.to_date = '9999-01-01'
) MGR
ON EMP.dept_no = MGR.dept_no AND EMP.salary > MGR.salary;

题目2：

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

select dept_no, dept_name, title, count(*) count
FROM 
(
    Select departments.dept_no, departments.dept_name, dept_emp.emp_no, titles.title from 
    departments INNER JOIN dept_emp
    ON departments.dept_no = dept_emp.dept_no AND dept_emp.to_date = '9999-01-01'
    INNER JOIN titles
    ON dept_emp.emp_no = titles.emp_no AND titles.to_date = '9999-01-01'
) temp
GROUP BY dept_no, dept_name, title

题目3：

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

有同学（wasrehpic）给出了如下答案运行可通过：

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC；

在mysql中可以用以下语句运行出结果，但在牛客网的测试用例上就有问题（另外在牛客网上YEAR函数不可用，应用上述提示的sqlite的函数）。

把年份那两句改成了一样的x-y=1的格式就可以通过了。逻辑上感觉没有差别，在本地上运行出来的结果也一样，不明白是为什么。

select s1.emp_no, s2.from_date, (s2.salary - s1.salary) salary_growth 
from salaries s1, salaries s2
WHERE s1.emp_no = s2.emp_no
AND (YEAR(s1.from_date) = YEAR(s2.from_date) - 1 
     OR YEAR(s1.to_date) = YEAR(s2.to_date) - 1)
AND (s2.salary - s1.salary) > 5000 
order by salary_growth desc;

从答案示例来看，每年涨幅超过5000的定义通过上面两个年份的等式可以限定，不过实际定义感觉和这个还是应该不太一样，如果是说在某一年里调薪幅度超过5000，应该调薪后的'from_date'和之前某次薪水的'to_date'相差一年以内就可以计为一年内的涨幅。但其实也不知道这里的每年是什么意思，答案只是能符合测试用例的逻辑而已。

秒客网

SQL练习题(1)-牛客网

相关文章