数据库Company基本查询

椰汁甜糯米糍

已于 2023-06-18 17:49:42 修改

阅读量330

点赞数 3

分类专栏： MySQL刷题文章标签： mysql sql 数据库

于 2023-05-09 15:09:06 首次发布

本文链接：https://blog.csdn.net/m0_64761777/article/details/130495809

版权

MySQL刷题专栏收录该内容

7 篇文章 2 订阅

订阅专栏

设有一个company数据库，包含regions，countries，locations，departments，jobs，employees，job_history7个关系模式。

创建regions表：

CREATE TABLE regions (
  region_id INT NOT NULL,
  region_name VARCHAR(25) DEFAULT NULL,
  PRIMARY KEY (region_id)
) ;

创建countries表：

CREATE TABLE countries (
  country_id CHAR(2) NOT NULL,
  country_name VARCHAR(40) DEFAULT NULL,
  region_id INT DEFAULT NULL,
  PRIMARY KEY (country_id),
  FOREIGN KEY (region_id) REFERENCES regions(region_id)
) ;

创建locations表：

CREATE TABLE locations (
  location_id INT NOT NULL DEFAULT '0',
  street_address VARCHAR(40) DEFAULT NULL,
  postal_code VARCHAR(12) DEFAULT NULL,
  city VARCHAR(30) NOT NULL,
  state_province VARCHAR(25) DEFAULT NULL,
  country_id CHAR(2) DEFAULT NULL,
  PRIMARY KEY (location_id),
  FOREIGN KEY (country_id) REFERENCES countries (country_id)
) ;

创建departments表：

CREATE TABLE departments (
  department_id INT NOT NULL DEFAULT '0',
  department_name VARCHAR(30) NOT NULL,
  manager_id INT DEFAULT NULL,
  location_id INT DEFAULT NULL,
  PRIMARY KEY (department_id),
  FOREIGN KEY (location_id) REFERENCES locations (location_id)
) ;

创建jobs表：

CREATE TABLE jobs (
  job_id VARCHAR(10) NOT NULL DEFAULT '',
  job_title VARCHAR(35) NOT NULL,
  min_salary INT DEFAULT NULL,
  max_salary INT DEFAULT NULL,
  PRIMARY KEY (job_id)
) ;

创建employees表：

CREATE TABLE employees (
  employee_id INT NOT NULL DEFAULT '0',
  first_name VARCHAR(20) DEFAULT NULL,
  last_name VARCHAR(25) NOT NULL,
  email VARCHAR(25) NOT NULL,
  phone_number VARCHAR(20) DEFAULT NULL,
  hire_date DATE NOT NULL,
  job_id VARCHAR(10) NOT NULL,
  salary DECIMAL(8,2) DEFAULT NULL,
  commission_pct DECIMAL(2,2) DEFAULT NULL,
  manager_id INT DEFAULT NULL,
  department_id INT DEFAULT NULL,
  PRIMARY KEY (employee_id),
  FOREIGN KEY (department_id) REFERENCES departments (department_id),
  FOREIGN KEY (job_id) REFERENCES jobs (job_id),
  FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
) ;

创建job_history表：

CREATE TABLE job_history (
  employee_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  job_id VARCHAR(10) NOT NULL,
  department_id INT DEFAULT NULL,
  PRIMARY KEY (employee_id,start_date),
  FOREIGN KEY (department_id) REFERENCES departments (department_id),
  FOREIGN KEY (employee_id) REFERENCES employees (employee_id),
  FOREIGN KEY (job_id) REFERENCES jobs (job_id)
) ;

基本SELECT语句

# 1.1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY

SELECT employee_id,first_name,last_name,salary * 12 "ANNUAL SALARY"
FROM employees;

# 1.2.查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id
FROM employees;

# 1.3.查询工资大于12000的员工姓名和工资

SELECT first_name,last_name,salary
FROM employees
where salary>12000;

# 1.4.查询员工号为176的员工的姓名和部门号

SELECT first_name,last_name,department_id
FROM employees
where employee_id=176;

# 1.5.显示表 departments 的结构，并查询其中的全部数据
  
DESCRIBE departments;//显示表的结构

SELECT * FROM departments;//查询全部数据

运算符

# 2.1.选择工资不在5000到12000的员工的姓名和工资

select first_name,last_name,salary
from employees
where salary<5000 or salary>12000;

# 2.2.选择在20或50号部门工作的员工姓名和部门号

select first_name,last_name,department_id
from employees
where department_id=20 or department_id=50;

# 2.3.选择公司中没有管理者的员工姓名及job_id

select first_name,last_name,job_id
from employees
where manager_id is null;

# 2.4.选择公司中有奖金的员工姓名，工资和奖金级别

select first_name,last_name,salary,commission_pct
from employees
where commission_pct is not null;

# 2.5.选择员工姓名的第三个字母是a的员工姓名

select first_name,last_name
from employees
where first_name like '__a%';

# 2.6.选择last_name中有字母a和k的员工姓名

select first_name,last_name
from employees
where last_name like '%a%k%' or last_name like '%k%a%';

# 2.7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

select first_name,last_name
from employees
where first_name like '%e';

# 2.8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

select firname,last_name,job_id
from employeest_s
where department_id between 80 and 100;

# 2.9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

select first_name,last_name,salary,manager_id
from employees
where manager_id in (100,101,110);

排序和分页

#3.1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

select first_name,last_name,department_id,salary*12 annual_salary
from employees
order by annual_salary desc,first_name;

#3.2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

select first_name,last_name,salary
from employees
where salary<8000 or salary>17000
order by salary desc
limit 20,20;

#3.3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

select *
from employees
where email like '%e%'
order by length(email) desc,department_id;

多表查询

# 4.1.显示所有员工的姓名，部门号和部门名称。

select first_name,last_name,employees.department_id,department_name
from employees left outer join departments
on employees.department_id=departments.department_id;

# 4.2.查询90号部门员工的job_id和90号部门的location_id

select job_id,location_id
from employees,departments
where employees.department_id=90 and employees.department_id=departments.department_id;

# 4.3.选择所有有奖金的员工的 last_name , department_name , location_id , city

select last_name,department_name,departments.location_id,city
from employees,departments,locations
where commission_pct is not null and employees.department_id=departments.department_id
and departments.location_id=locations.location_id;

# 4.4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

select last_name,job_id,departments.department_id,department_name
from employees,departments,locations
where city='Toronto' and employees.department_id=departments.department_id
and departments.location_id=locations.location_id;

# 4.5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为'Executive'

select department_name,street_address,first_name,last_name,job_id,salary
from employees,departments,locations
where department_name='Executive' and employees.department_id=departments.department_id
and departments.location_id=locations.location_id;

# 4.6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号

select e1.last_name employee,e1.employee_id,e2.last_name employer,e2.employee_id employer_id
from employees e1 left outer join employees e2 
on e1.manager_id=e2.employee_id; 

# 4.7.查询哪些部门没有员工

select departments.department_id
from departments left outer join employees
on departments.department_id=employees.department_id
where employees.employee_id is null;

# 4.8. 查询哪个城市没有部门

select city
from locations left outer join departments
on departments.location_id=locations.location_id
where departments.department_id is null;

# 4.9. 查询部门名为 Sales 或 IT 的员工信息

select e.employee_id,e.first_name,e.last_name,e.job_id,e.salary,e.department_id
from employees e left outer join departments
on e.department_id=departments.department_id 
where departments.department_name='Sales'or departments.department_name='IT';

单行函数

# 5.1.显示系统时间(注：日期+时间)

select current_timestamp() '系统时间'
from dual;

# 5.2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

select employee_id,first_name,last_name,salary,salary*1.2 as 'new salary'
from employees;

# 5.3.将员工的姓名按首字母排序，并写出姓名的长度（length）

select first_name,last_name,length(first_name)+length(last_name) as length
from employees
order by first_name;

# 5.4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees

# 5.5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT employee_id, ROUND(DATEDIFF(CURDATE(),hire_date)/365) "worked_years", DATEDIFF(CURDATE(),hire_date) "worked_days"
FROM employees
ORDER BY worked_years DESC

# 5.6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空

SELECT last_name, hire_date, department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
AND hire_date >= '1997-01-01'

# 5.7.查询公司中入职超过10000天的员工姓名、入职时间

SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;

# 5.8.做一个查询，产生下面的结果<last_name> earns <salary> monthly but wants <salary*3>

SELECT CONCAT(last_name,' earns ',salary, ' monthly but wants ',salary * 3) "Dream Salary"
FROM employees;

# 5.9.使用case-when，按照下面的条件：
/*  job grade
   AD_PRES A
   ST_MAN B
   IT_PROG C
   SA_REP D
   ST_CLERK E
*/

SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							 WHEN 'ST_MAN' THEN 'B'
							 WHEN 'IT_PROG' THEN 'C'
							 WHEN 'SA_REP' THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 END "Grade"
FROM employees;

聚合函数

# 6.1.查询公司员工工资的最大值，最小值，平均值，总和

select max(salary) maxSalary,min(salary) minSalary,avg(salary)avgSalary,sum(salary)sumSalary
from employees;

# 6.2.查询各job_id的员工工资的最大值，最小值，平均值，总和

select job_id, max(salary) maxSalary,min(salary) minSalary,avg(salary)avgSalary,sum(salary)sumSalary
from employees
group by job_id;

# 6.3.选择具有各个job_id的员工人数

select job_id,count(*)
from employees
group by job_id;

# 6.4.查询员工最高工资和最低工资的差距（DIFFERENCE）

select max(salary)-min(salary) DIFFERENCE
from employees;

# 6.5.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select min(salary) minSalary,manager_id
from employees
group by manager_id
having manager_id is not null
and  minSalary>=6000;

# 6.6.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

select  department_name,location_id,count(employee_id),avg(salary)
FROM departments LEFT OUTER JOIN employees
on departments.department_id=employees.department_id
GROUP BY department_name,location_id 
order by avg(salary) desc;

# 6.7.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_id,MIN(salary)
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name,job_id;

子查询

#7.1.查询和Zlotkey相同部门的员工姓名和工资

select first_name,last_name,salary
from employees
where department_id in(
select  department_id from employees
where last_name='zlotkey'
); 

#7.2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id,first_name,last_name,salary
from employees
where salary>(
select avg(salary) 
from employees);

#7.3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

select last_name,job_id,salary
from employees
where salary>ALL(
select salary
from employees
where job_id='SA_MAN');

#7.4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select employee_id,first_name,last_name
from employees
where department_id in(
select distinct department_id
from employees
where first_name like '%u%' or last_name like '%u%');

#7.5.查询在部门的location_id为1700的部门工作的员工的员工号

select employee_id
from employees
where department_id in(
select distinct department_id
from departments
where location_id=1700);

#7.6.查询管理者是King的员工姓名和工资

select first_name,last_name,salary
from employees
where manager_id in(
select distinct manager_id
from employees
where last_name='King');

#7.7.查询工资最低的员工信息: last_name, salary

select last_name,salary
from employees
where salary =(
select min(salary)
from employees
);

#7.8.查询平均工资最低的部门信息

SELECT d.*
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#7.9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_sal
FROM departments d
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING AVG(salary ) <= ALL(
						SELECT AVG(salary)
						FROM employees
						GROUP BY department_id
						)
			);

#7.10.查询平均工资最高的 job 信息

SELECT *
FROM jobs
WHERE job_id = (
		SELECT job_id
		FROM employees
		GROUP BY job_id
		HAVING AVG(salary) = (
					SELECT MAX(avg_sal)
					FROM (
						SELECT AVG(salary) avg_sal
						FROM employees
						GROUP BY job_id
						) t_job_avg_sal
					)
		);


#7.11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) > (
			SELECT AVG(salary)
			FROM employees
			);

#7.12.查询出公司中所有 manager 的详细信息

SELECT DISTINCT m.employee_id,m.first_name,m.last_name,m.job_id,m.department_id,m.salary,m.email,m.phone_number,m.hire_date,m.commission_pct
FROM employees e,employees m
where e.manager_id = m.employee_id;

#7.13.各个部门中最高工资中最低的那个部门的最低工资是多少?

SELECT MIN(salary)
FROM employees
WHERE department_id = (
			SELECT department_id
			FROM employees
			GROUP BY department_id
			HAVING MAX(salary) <= ALL (
						SELECT MAX(salary)
						FROM employees
						GROUP BY department_id
						)
			);


#7.14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = ANY (
			SELECT DISTINCT manager_id
			FROM employees
			WHERE department_id = (
						SELECT department_id
						FROM employees
						GROUP BY department_id
						HAVING AVG(salary) >= ALL (
								SELECT AVG(salary) avg_sal
								FROM employees
								GROUP BY department_id
								)
						)
			);


#7.15.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

select department_id
from employees
where department_id not in (
select department_id
from employees
where job_id='ST_CLERK');

#7.16.选择所有没有管理者的员工的last_name
select last_name
from employees e1
where not exists(
select * from employees e2
where e1.manager_id = e2.employee_id);

#7.17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

select employee_id,first_name,last_name,hire_date,salary
from employees
where manager_id in(
select employee_id
from employees
where last_name='De Haan');

#7.18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

select employee_id,first_name,last_name,salary
from employees e
where salary >(
select avg(salary)
from employees m
group by department_id
having e.department_id=m.department_id);

#7.19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

select department_name
from departments d
where 5 <(
select count(*)
from employees e
group by department_id
having e.department_id=d.department_id);

#7.20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

select country_id
from locations l
where 2 <(
select count(*)
from departments d
group by location_id
having l.location_id=d.location_id);