【数据库SQL实战】题目51~61答案全解析（完）

￡神魔★判官ぃ 2023-10-11 12:08 10阅读 0赞

## 51. 查找字符串'10,A,B' 中逗号','出现的次数cnt ##

查找字符串'10,A,B' 中逗号','出现的次数cnt。

select (length('10,A,B') - length(replace('10,A,B', ',', ''))) / length(',') as cnt;

## 52. 获取Employees中的first\_name 获取Employees中的first\_name，查询按照first\_name最后两个字母，按照升序进行排列 ##

# #

select first_name from employees order by substr(first_name, -2)

## 53. 按照dept\_no进行汇总 ##

按照dept\_no进行汇总，属于同一个部门的emp\_no按照逗号进行连接，结果给出dept\_no以及连接出的结果employees

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

select dept_no, group_concat(emp_no) as employees 
    from dept_emp 
    group by dept_no;

## 54. 查找排除当前最大、最小salary之后的员工的平均工资avg\_salary ##

查找排除当前最大、最小salary之后的员工的平均工资avg\_salary。

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 avg(salary) as avg_salary
    from salaries 
    where to_date='9999-01-01' 
    and salary not in (select min(salary) from salaries)
    and salary not in (select max(salary) from salaries);

## 55. 分页查询employees表，每5行一页，返回第2页的数据 ##

分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit(2-1)*5, 5;

## 56. 获取所有员工的emp\_no获取所有员工的emp\_no、部门编号dept\_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示 ##

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`));
    
    create table emp_bonus(
    emp_no int not null,
    recevied datetime not null,
    btype smallint not null);

select e.emp_no, de.dept_no, b.btype, b.recevied
    from employees e
    inner join dept_emp de on de.emp_no = e.emp_no
    left join emp_bonus as b on b.emp_no  = e.emp_no;

## 57. 使用含有关键字exists查找未分配具体部门的员工的所有信息。 ##

使用含有关键字exists查找未分配具体部门的员工的所有信息。

select *
    from employees e
    where not exists (select emp_no from dept_emp de where e.emp_no=de.emp_no);

##  58. 获取employees中的行数据，且这些行也存在于emp\_v中 ##

存在如下的视图：

create view emp_v as select * from employees where emp_no >10005;
    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`));

获取employees中的行数据，且这些行也存在于emp\_v中。注意不能使用intersect关键字。

select e.* from employees e, emp_v ev where ev.emp_no = e.emp_no;

## 59. 获取有奖金的员工相关信息。 ##

获取有奖金的员工相关信息。

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 `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 emp_bonus(
    emp_no int not null,
    recevied datetime not null,
    btype smallint not null);
    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`));

给出emp\_no、first\_name、last\_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to\_date='9999-01-01'

select e.emp_no, e.first_name, e.last_name, b.btype, s.salary, (
        case b.btype
        when 1 then s.salary * 0.1
        when 2 then s.salary * 0.2
        else s.salary * 0.3 end) as bonus
    from employees e
    inner join salaries s on s.emp_no = e.emp_no and s.to_date='9999-01-01'
    inner join emp_bonus b on b.emp_no = e.emp_no;

## 60. 统计salary的累计和running\_total ##

按照salary的累计和running\_total，其中running\_total为前两个员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

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.salary, (
        select sum(s2.salary) 
        from salaries s2
        where s2.emp_no <= s1.emp_no 
        and s2.to_date='9999-01-01'
        ) as running_total 
    from salaries as s1
    where s1.to_date='9999-01-01'
    order by s1.emp_no;

61. 对于employees表中，给出奇数行的first\_name

对于employees表中，给出奇数行的first\_name

select e1.first_name
    from (
        select e2.first_name,(
            select count(*) 
            from employees as e3
            where e3.first_name <= e2.first_name) as rowid
        from employees as e2) as e1
    where e1.rowid % 2 = 1;