SQL基础练习2

在这里插入代码片@[TOC]

主要内容

1. SQL基础练习题

---

预备知识

SQL（Structured Query Language）是一种用于管理关系型数据库的标准化语言。它允许用户通过简单的命令来查询、插入、更新和删除数据库中的数据。

SQL查询是使用SELECT语句从数据库中检索数据的过程。它可以根据特定的条件过滤数据，排序结果，并使用聚合函数计算汇总数据。以下是SQL查询的一些常见用法：

1. SELECT语句：SELECT语句用于从数据库中选择要检索的列。例如，SELECT column1, column2 FROM table; 将返回表中指定列的数据。

2. WHERE子句：WHERE子句用于过滤数据，只返回满足特定条件的行。例如，SELECT * FROM table WHERE column = value; 将返回满足指定条件的行。

3. ORDER BY子句：ORDER BY子句用于对结果进行排序。例如，SELECT * FROM table ORDER BY column ASC; 将按升序对结果进行排序。

4. GROUP BY子句：GROUP BY子句用于将结果按照指定的列进行分组。例如，SELECT column, COUNT(*) FROM table GROUP BY column; 将按照指定列的值分组，并计算每组的行数。

5. HAVING子句：HAVING子句用于对分组后的结果进行过滤。例如，SELECT column, COUNT() FROM table GROUP BY column HAVING COUNT() > 10; 将返回满足指定条件的分组结果。

6. JOIN操作：JOIN操作用于将两个或多个表中的数据进行关联。例如，SELECT * FROM table1 JOIN table2 ON table1.column = table2.column; 将返回两个表中列值相等的行。

7. 子查询：子查询是在主查询中嵌套的查询语句。它可以作为主查询的条件、过滤或计算结果的一部分。例如，SELECT * FROM table WHERE column IN (SELECT column FROM table2); 将返回满足子查询条件的行。

SQL查询是数据库管理的核心部分，它可以帮助用户从数据库中检索需要的数据，并根据特定的需求进行处理和分析。

---

一.SQL基础练习题

1.写一个查询用首写字母大写，其它字母小写显示雇员的 last_name, 并显示姓名的长度，对所有姓名开始字母是 J、A 或 M 的雇员，给每列一个适当的标签，用 last_name 对结果排序。

代码如下（示例）:

select upper(substring(last_name,1,1)) + lower(substring(last_name,2)) as 'Last Name',
length(last_name) as 'Name Length'
from employees
where last_name like 'J%' or last_name like 'A%' or last_name like 'M%'
order by last_name;

2.对每一个雇员，显示其 last_name, 并计算从雇员受雇日期到今天的月数，列标签为 MONTHS＿WORKED，按受雇月数排序结果，四舍五入月数到最靠近的整数月。

代码如下（示例）:

select last_name, round(datediff(current_date,hire_date)/30) as 'MONTHS_WORKED'
from employees
order by MONTHS_WORKED;

3.创建一个查询显示所有雇员的 last_name 和 salary. salary 格式化为 15 个字符长度，用$左填充，列标签为 SALARY。

代码如下（示例）:

select last_name, LPAD(concat('$',salary),15,'$') as 'SALARY'
from employees;

4.显示每一个雇员的 last_name, hire_date 和雇员入职周内天数，列标签 DAY，用星期一作为每周的起始日。

代码如下（示例）:

select last_name,hire_date,(DAYOFWEEK(hire_date)+5) %7 + 1 as 'DAY'
from employees;

5.创建一个查询显示雇员的 last_names 和 commission(佣金)比率。如果雇员没有佣金显示“No Commission”，列标签为 COMM。

代码如下（示例）:

select last_name,
	case
		when commission_pct is null then 'No Commission'
		else concat(commission_pct*100,'%')
	end as 'COMM'
from employees;

6.显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：maximum、minmum、sum、average。四舍五入结果为最近的整数。

代码如下（示例）:

select MAX(salary) as 'maximum',
	MIN(salary) as 'minimu',
	SUM(salary) as 'sum',
	round(avg(salary)) as 'average'
from employees;

7.对上一问题的问题显示每种工作类型的最低、最高、合计、平均薪水。

代码如下（示例）:

select MAX(salary) as 'maximum',
	MIN(salary) as 'minimu',
	SUM(salary) as 'sum',
	round(avg(salary)) as 'average'
from employees
group by job_id;

8.显示每一个工作岗位的人数。

代码如下（示例）:

select job_id,count(*) as 'Number of Employees'
from employees
group by job_id;

9.显示所有经理人数。

代码如下（示例）:

select job_id,count(*) as 'Number of Managers'
from employees
where job_id='IT_PROG' or job_id='ST_MAN'

10.显示最高与最低薪水之间的差额。

代码如下（示例）:

select max(salary)-min(salary) as 'Salary Difference'
from employees;

11.显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

代码如下（示例）:

select d.department_name,d.location_id,count(e.employee_id) as 'Number of Employees',
round(avg(e.salary0，2）as 'Average Salary' 
from departments d
join employees e on d.department_id=e.department_id
group by d.department_name,d.location_id;

12.写一个查询显示所有雇员的 last_name, department_id and department_name。

代码如下（示例）:

select e.last_name,e.department_id,d.department_name
from employees e
join departments d on e.department_id=d.department_id;

13.创建一个在部门 80 中的所有工作岗位的唯一列表，在输出中包括部门的地点。

代码如下（示例）:

select distinct job_id,location_id
from employees
where department_id=80;

14.显示所有在其 last_name 中有一个小写 a 的雇员的 last_name 和 department_name。

代码如下（示例）:

select last_name,department_name
from employees
join departments on employees.department_id = departments.department_id
where last_name like '%a%';

15.写一个查询显示那些工作在 Toronto 的所有雇员的 last_name、job_id、department_id和 department_name。

代码如下（示例）:

select e.last_name,e.job_id,e.department_id,d.department_name
from employees e
join departments d on e.department_id = d.department_id
where d.location_id = 'Toronto';

16.显示雇员的 last_name 和 employee_id 连同他们的经理的 last_name 和 manager_id，列标签为 employee、emp_id、manager 和 mgr_id。

代码如下（示例）:

select e.last_name aas 'employee',e.employee_id as 'emp_id',m.last_name as 'manager',e.manager_id as 'mgr_id'
from employees e
join employees m on e.manager_id = m.employee_id;

17.创建一个查询 ， 显示所有与被指定雇员工作在同一部门的雇员的 last_names 、department_id。给每列一个适当的标签。

代码如下（示例）:

select e.last_name as 'last_names',e.department_id
from employees e
join employees m on e.department_id =m.department_id
where m.last_name = '指定雇员的last_name';

18.写一个查询显示与 zlotkey 在同一部门的雇员的 last name 和 hire date, 结果中不包括zlotkey。

代码如下（示例）:

select last_name,hire_date
from employees
where department_id = (select department_id from employees where last_name ='zlotkey')
and last_name !='zlotkey';

19.创建一个查询显示所有薪水高于平均薪水的雇员的雇员编号和名字，按薪水的升序排序。

代码如下（示例）:

select employee_id,last_name
from employees
where salary>(select avg(salary) from employees)
order by salary asc;

20.显示所有部门位置编号（location_id）是 1700 的雇员的 last name、departtment_id和 job_id。

代码如下（示例）:

select last_name,department_id,job_id
from employees
where department_id in (select department_id from departments where location_id = 1700);

21.显示在 Executive 部门的每个雇员的 department_id、last_name 和 job_id。

代码如下（示例）:

select department_id,last_name,job_id
from employees
where department_id=(select department_id from departments where department department_name='Executive');

---

总结

以上是今天要讲的内容，学到了SQL的基础语句以及相关练习。