攀孜簦客數(shù)據(jù)庫SQL實(shí)戰(zhàn)題(21-30題)
21寡喝、查找所有員工自入職以來的薪水漲幅情況，給出員工編號(hào)emp_no以及其對(duì)應(yīng)的薪水漲幅growth沙廉，并按照growth進(jìn)行升序
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_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));
答案
select now.emp_no, (now.salary-pre.salary) as growth
from (select em.emp_no, sa.salary
from employees as em
join salaries as sa
on em.emp_no=sa.emp_no
where sa.to_date='9999-01-01') as now
join (select em.emp_no, sa.salary
from employees as em
join salaries as sa
on em.emp_no=sa.emp_no
where em.hire_date=sa.from_date) as pre
on now.emp_no=pre.emp_no
order by growth;
分別建兩張表拘荡，一張表是emp_no和對(duì)應(yīng)的開始工資，一張表是emp_no和對(duì)應(yīng)的當(dāng)前工資撬陵。然后將兩張表連接起來，做減法网缝。
22巨税、統(tǒng)計(jì)各個(gè)部門對(duì)應(yīng)員工漲幅的次數(shù)總和，給出部門編碼dept_no粉臊、部門名稱dept_name以及次數(shù)sum
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 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));
答案
select de.dept_no, dename.dept_name, count(sa.salary) as sum
from (dept_emp as de
join departments as dename on dename.dept_no=de.dept_no)
join salaries as sa on de.emp_no=sa.emp_no
group by de.dept_no;
23草添、對(duì)所有員工的當(dāng)前(to_date='9999-01-01')薪水按照salary進(jìn)行按照1-N的排名，相同salary并列且按照emp_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));
答案
select sa1.emp_no, sa1.salary, count(distinct sa2.salary) as rank
from salaries as sa1
left join salaries as sa2
on sa2.salary>=sa1.salary
where sa1.to_date='9999-01-01' and sa2.to_date='9999-01-01'
group by sa1.emp_no
order by sa1.salary desc, sa1.emp_no;
排序這個(gè)字段需要造出一個(gè)字段并count扼仲，為了構(gòu)造出rank远寸，我們可以復(fù)用salaries表抄淑，對(duì)salaries1表中的每一個(gè)salary，對(duì)大于或等于s1.salary的sa.salary進(jìn)行count驰后。
參考潘磷剩客網(wǎng)，在支持ROW_NUMBER灶芝、RANK郑原、DENSE_RANK等函數(shù)的SQL Server數(shù)據(jù)庫中，有以下參考代碼夜涕，可惜在本題的SQLite數(shù)據(jù)庫中不支持犯犁。
SELECT emp_no, salaries, DENSE_RANK() OVER(ORDER BY salary DESC) AS rank
WHERE to_date ='9999-01-01'
ORDER BY salary DESC, emp_no ASC
24、獲取所有非manager員工當(dāng)前的薪水情況女器，給出dept_no酸役、emp_no以及salary ，當(dāng)前表示to_date='9999-01-01'
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 employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_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));
答案
select dema.dept_no, em.emp_no, sa.salary
from employees as em
join dept_emp as de
on em.emp_no=de.emp_no
join dept_manager as dema
on de.dept_no=dema.dept_no
join salaries as sa
on em.emp_no=sa.emp_no
where sa.to_date='9999-01-01' and em.emp_no<>dema.emp_no
25驾胆、獲取員工其當(dāng)前的薪水比其manager當(dāng)前薪水還高的相關(guān)信息簇捍，當(dāng)前表示to_date='9999-01-01'
結(jié)果第一列給出員工的emp_no，
第二列給出其manager的manager_no俏拱，
第三列給出該員工當(dāng)前的薪水emp_salary,
第四列給該員工對(duì)應(yīng)的manager當(dāng)前的薪水manager_salary
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));
答案
select em.emp_no, ma.emp_no as manager_no, sa1.salary as emp_salary, sa2.salary as manager_salary
from dept_emp as em
join dept_manager as ma
on em.dept_no=ma.dept_no
and em.to_date='9999-01-01'
and ma.to_date='9999-01-01'
and em.emp_no<>ma.emp_no
join salaries as sa1
on em.emp_no=sa1.emp_no and sa1.to_date='9999-01-01'
join salaries as sa2
on ma.emp_no=sa2.emp_no and sa2.to_date='9999-01-01'
where sa1.salary>sa2.salary
重復(fù)利用salary暑塑。
26、匯總各個(gè)部門當(dāng)前員工的title類型的分配數(shù)目
結(jié)果給出部門編號(hào)dept_no锅必、dept_name事格、其當(dāng)前員工所有的title以及該類型title對(duì)應(yīng)的數(shù)目count
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);
答案
select de.dept_no, name.dept_name, ti.title, count(ti.title)
from dept_emp as de
join departments as name
on de.dept_no=name.dept_no and de.to_date='9999-01-01'
join titles as ti
on de.emp_no=ti.emp_no and ti.to_date='9999-01-01'
group by de.dept_no, ti.title
27、給出每個(gè)員工每年薪水漲幅超過5000的員工編號(hào)emp_no
薪水變更開始日期from_date以及薪水漲幅值salary_growth搞隐，并按照salary_growth逆序排列驹愚。
提示：在sqlite中獲取datetime時(shí)間對(duì)應(yīng)的年份函數(shù)為strftime('%Y', to_date)
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));
答案
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
每年薪水漲幅的定義并不是非常明確。
28劣纲、查找描述信息中包括robot的電影對(duì)應(yīng)的分類名稱以及電影數(shù)目逢捺，而且還需要該分類對(duì)應(yīng)電影數(shù)量>=5部
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL,
last_update timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL,
last_update timestamp);
答案
select c.name, count(fc.film_id)
from (select category_id, count(film_id) as amount
from film_category
group by category_id
having amount>=5) as cc,
film as f, category as c, film_category as fc
where f.description like '%robot%'
and f.film_id=fc.film_id
and fc.category_id=c.category_id
and c.category_id=cc.category_id;
首先，此題所說的該分類對(duì)應(yīng)電影數(shù)量>=5癞季，是該分類下所有電影的數(shù)量大>=5劫瞳，而不是篩選了描述中有robot字段之后電影數(shù)量依然>=5的分類。
其次绷柒，這題的代碼還是讓人摸不著頭腦志于，第一行的select c.name, count(fc.film_id)中的count(fc.film_id)很奇怪，還沒有g(shù)roup by废睦。用join各表的方法寫的代碼無法通過伺绽。作者才疏學(xué)淺，如果你有好的想法請(qǐng)聯(lián)系我。
29奈应、使用join查詢方式找出沒有分類的電影id以及名稱
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL,
last_update timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL,
last_update timestamp);
答案
select f.film_id, f.title
from film as f
left join film_category as fc
on f.film_id=fc.film_id
where fc.category_id is null;
30澜掩、使用子查詢的方式找出屬于Action分類的所有電影對(duì)應(yīng)的title,description
CREATE TABLE IF NOT EXISTS film (
film_id smallint(5) NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id));
CREATE TABLE category (
category_id tinyint(3) NOT NULL ,
name varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id ));
CREATE TABLE film_category (
film_id smallint(5) NOT NULL,
category_id tinyint(3) NOT NULL, `last_update` timestamp);
答案
子查詢方式：
select f.title, f.description
from film as f
where f.film_id
in (select fc.film_id
from film_category as fc
where fc.category_id
in (select c.category_id
from category as c
where name='Action'))
非子查詢方式：
select f.title,f.description
from film as f
inner join film_category as fc
on f.film_id = fc.film_id
inner join category as c
on c.category_id = fc.category_id
where c.name = 'Action';
結(jié)尾
如果您發(fā)現(xiàn)我的文章有任何錯(cuò)誤，或?qū)ξ业奈恼掠惺裁春玫慕ㄗh杖挣，請(qǐng)聯(lián)系我肩榕！如果您喜歡我的文章，請(qǐng)點(diǎn)喜歡~*我是藍(lán)白絳程梦，感謝你的閱讀点把！
