資料庫-刷題

1、題目描述
彙總各個部門當前員工的title型別的分配數目，即結果給出部門編號dept_no、dept_name、其部門下所有的當前(dept_emp.to_date = ‘9999-01-01’)員工的當前(titles.to_date = ‘9999-01-01’)title以及該型別title對應的數目count
(注：因為員工可能有離職，所有dept_emp裡面to_date不為’9999-01-01’就已經離職了，不計入統計，而且員工可能有晉升，所以如果titles.to_date 不為 ‘9999-01-01’，那麼這個可能是員工之前的職位資訊，也不計入統計)

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 dp.dept_no,dp.dept_name,t.title, count(t.title) 
from departments dp,
dept_emp de,
titles t
where dp.dept_no=de.dept_no and 
de.emp_no=t.emp_no 
and de.to_date='9999-01-01' and
t.to_date='9999-01-01'
group by dp.dept_no,dp.dept_name ,t.title

注意：就是三個表的內連線inner join 然後進行分組，先按照 dept_no 分組，然後按照 dept_name分組，再按照 title分組 即可實現。

2、題目描述

給出每個員工每年薪水漲幅超過5000的員工編號emp_no、薪水變更開始日期from_date以及薪水漲幅值salary_growth，並按照salary_growth逆序排列。
提示：在sqlite中獲取datetime時間對應的年份函式為strftime(’%Y’, to_date)
(資料保證每個員工的每條薪水記錄to_date-from_date=1年，而且同一員工的下一條薪水記錄from_data=上一條薪水記錄的to_data)

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`));
如：插入
INSERT INTO salaries VALUES(10001,52117,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');

輸出描述：

SQL程式碼實現：

select s1.emp_no,s1.from_date, s1.salary-s2.salary as salary_growth
from salaries s1 inner join salaries s2 on 
s1.emp_no=s2.emp_no and strftime('%Y',s1.from_date)=strftime('%Y',s2.to_date)
where s1.salary-s2.salary >5000
order by salary_growth DESC

注意：需要保證一年的薪水情況，需要建立兩個表格，並進行連線。

3、題目描述

查詢描述資訊(film.description)中包含robot的電影對應的分類名稱(category.name)以及電影數目(count(film.film_id))，而且還需要該分類包含電影總數量(count(film_category.category_id))>=5部

輸入為:

INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO film VALUES(4,'AFFAIR PREJUDICE','A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank');
INSERT INTO film VALUES(5,'AFRICAN EGG','A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico');
INSERT INTO film VALUES(6,'AGENT TRUMAN','A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China');
INSERT INTO film VALUES(7,'AIRPLANE SIERRA','A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat');
INSERT INTO film VALUES(8,'AIRPORT POLLOCK','A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India');
INSERT INTO film VALUES(9,'ALABAMA DEVIL','A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat');
INSERT INTO film VALUES(10,'ALADDIN CALENDAR','A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO category VALUES(12,'Music','2006-02-14 20:46:27');
INSERT INTO category VALUES(13,'New','2006-02-14 20:46:27');
INSERT INTO category VALUES(14,'Sci-Fi','2006-02-14 20:46:27');
INSERT INTO category VALUES(15,'Sports','2006-02-14 20:46:27');
INSERT INTO category VALUES(16,'Travel','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(4,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(5,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(6,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(7,5,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(8,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(9,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(10,15,'2006-02-14 21:07:09');

輸出為:

程式碼實現：

select c.name,count(f.film_id)
from film f,category c, film_category fc 
where f.film_id=fc.film_id and 
fc.category_id=c.category_id and 
f.description like '%robot%' 
and fc.category_id=(
    select category_id from film_category 
    group by category_id 
    having count(film_id) >=5 )

注意：需要找出電影分類表中，電影類別的個數大於5的表格，然後需要將三個表格進行內連線。