sql练习题归纳总结中（6）

26.汇总各个部门的当前员工title类型的分配数目

题目描述：

汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目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);

Cue：

①这是一个三表连接加上分组的问题

②先将titles和dept_emp连接，然后将departments 填进去

③讲dept_no和title分组统计

 

select de.dept_no,dp.dept_name,t.title,count(t.title) as count 
from titles as t inner join dept_emp as de on t.emp_no=de.emp_no and de.to_date ='9999-01-01' and t.to_date = '9999-01-01'
inner join departments as dp
on de.dept_no=dp.dept_no
group by de.dept_no,t.title
 

27.给出每个员工每年薪水涨幅超过5000的员工

题目描述：

 

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

提示：在sqlite中获取datetime时间对应的年份函数为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 s1.emp_no,s1.from_data,(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",s2.to_date))=1

OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )

order by salaary_growth desc

关于strftime函数的相关内容

strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。
strftime()的语法是strftime(格式, 日期/时间, 修正符, 修正符, ...)

它可以用以下的符号对日期和时间进行格式化：
%d 日期, 01-31
%f 小数形式的秒，SS.SSS
%H 小时, 00-23
%j 算出某一天是该年的第几天，001-366
%m 月份，00-12
%M 分钟, 00-59
%s 从1970年1月1日到现在的秒数
%S 秒, 00-59
%w 星期, 0-6 (0是星期天)
%W 算出某一天属于该年的第几周, 01-53
%Y 年, YYYY
%% 百分号

strftime()的用法举例如下：

select strftime('%Y.%m.%d %H:%M:%S','now','localtime');
结果：2006.10.17 21:41:09
其实更好的用法是这样的,比如统,本月或本年的支出:
select strftime('%Y-%m',日期) as 月份,sum(支出) as 月支出 from 流水帐 group by 月份;

28.将employees表的所有员工的last_name和first_name拼接起来作为Name

题目描述：

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

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));

select 

from employees

select last_name||" "||first_name as name 
from employees

29.对employees表的first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

 

CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);
CREATE INDEX 名称 ON 表名(字段名)和唯一索引的关键字UNIQUE

30.针对employees表创建视图employees_view

create view employees_view as

select birth_date as birth_date_v,first_name as first_name_v,last_name as last_name_v.gender as gender_v,hire_date as hire_date_v

from employees