mysql 基本 SQL 练习1

最新推荐文章于 2023-07-10 20:36:18 发布

StellaY

最新推荐文章于 2023-07-10 20:36:18 发布

阅读量940

点赞数 3

分类专栏： sql 文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/yan_zhao123/article/details/124839716

版权

sql 专栏收录该内容

5 篇文章 2 订阅

订阅专栏

查询全体职工的职工号和姓名。

select staffNo, fName, lName from staff;

查询全体职工的记录。

select * from staff;

查询全体职工的姓名和出生日期。

select fName, lName, DOB from staff;

在职工表中查询有哪些职工担任了职务，并列出职工的职工号。

Select staffNo from staff where position is not null;

查询全体主管(supervisor & manager)的姓名。

select fName, lName from staff where position = "supervisor" or position = "manager";

查询全体助理的职工号。

select staffNo from staff where position = "assistant";

查询所有工资在 20000 以下的职工的姓名和出生日期。

select fName, lName, DOB from staff where salary < 20000;

查询工资在 10000-20000 之间的职工的姓名、所担任职务和出生日期。

select fName, lName, position, DOB from staff where salary between 10000 and 20000;

查询工资不在 10000-20000 之间的职工的姓名、职工号和性别。

select fName, lName, staffNo, sex from staff where salary not between 10000 and 20000;

查询担任经理或助理职务员工的姓名和性别。

select fName, lName, sex from staff 
where position = "manager" or position = "assistant";

查询不担任经理或助理职务员工的姓名和性别。

select fName, lName, sex from staff 
where position not in ("manager", "assistant");

查询 lname 为 white 的职员的详细信息。

select * from staff where lName = "white";

查询 fname 中第二个字母为“o”或“n”的职员的姓名和职工号。

select fName, lName, staffNo from staff where fname like "_o%" or fname like "_n%";

查询所有 lname 不是“white”的职工姓名。

select fName, lName from staff where not lName = "white";

查询职工号的最后一位不是 2、 3、 5 的职工的所有信息。

select * from staff 
where (staffNo not like "%2") and (staffNo not like "%3") and (staffNo not like "%5");

查询有电话号码的 owner（业主），并对负责该业主的经纪人按收入多少进行降序排序，输出经纪人编号、姓名以及收入。

select distinct staffNo, staff.fName, staff.lName, staff.salary 
from privateowner join propertyforrent using (ownerNo) join staff using (staffNo) 
where privateowner.telNo is not null 
order by staff.salary desc;

查询所有担任助理职务员工的员工号和收入，查询结果按收入降序排列。

select staffNo, salary from staff where position = "assistant" order by salary desc;

统计员工总人数。

select count(*) as total_staff from staff;

统计担任经理职务员工的人数。

select count(*) as total_manager from staff where position = "manager";

计算所有担任助理职务员工的总收入之和。

select sum(salary) as total_salary_assistant from staff where position = "assistant";

统计每个职务的员工人数，列出职务名称和人数。

select position, count(*) as total_staff from staff group by position;

查询每种职务的员工人数和平均收入。

select position, count(*) as total_staff, avg(salary) as avg_salary from staff group by position;

查询与 Ann beech 担任同一个职务的职工的职工号。

select staffNo from staff where position = (
select position from staff where fname = "Ann" and lname = "beech"
);

将职工号为“SA9”的职工的收入改为 15000。

update staff set salary = 15000 where staffNo = "SA9";

删除所有收入低于 10000 的员工的记录。

SET FOREIGN_KEY_CHECKS = 0; 
delete from staff where salary < 10000;

插入一条员工记录， staffNo：你的学号， fName：是你的名， lName：你的姓，其他字段自行设计。

insert into staff value('2019210902', "Yan", "Zhao", "Assistant", "F", "2001-05-29", 12000, null);

按分支机构编号排列，列出每个分支机构每天出租房产的全部租金收入
（假设全部租出，每月按 30 天计）。

with branch_rent(branchNo, daily_rent) as
(SELECT branchNo, sum(rent)/30 FROM propertyforrent group by branchNo) 

select branch.branchNo, 
(case 
when daily_rent is not null then daily_rent 
else 0 
end) as daily_rent 
from branch natural left outer join branch_rent
order by branchNo;

给出每个分支机构持有的每一类房产的总数。

select branchNo, type, count(*) as total_property 
from propertyforrent 
group by branchNo, type;

给出看过两类（含）房产以上且由两个（含）分公司的员工带看过的客
户的名字和电话号码。

select fName, lName, telNo from client 
where 2 <= (
select count(distinct type) from viewing natural join propertyforrent 
where client.clientNo = viewing.clientNo
);

给出为提供多处房产的业主提供服务的员工的名字、薪水和所在分支机
构

with ownerwithmultiproperty(ownerNo) 
as (select ownerNo from propertyforrent 
group by ownerNo 
having count(propertyNo) > 1),

staffsatisfied(staffNo) 
as (select staffNo from propertyforrent 
where ownerNo in (select ownerNo from ownerwithmultiproperty))

select fName, lName, salary, branchNo from staff 
where staffNo in (select staffNo from staffsatisfied);