day07数据库操作练习

数据准备

在这里插入代码片
drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
);
insert into students values
('001 ','王昭君','女','北京','20','1班','340322199001247654'),
('002 ','诸葛亮','男','上海','18','2班','340322199002242354'),
('003 ','张飞','男','南京','24','3班','340322199003247654 '),
('004','白起','男','安徽','22','4班','340322199005247654 '),
('005','大乔','女','天津','19','3班','340322199004247654'),
('006', '孙尚香','女','河北', '18','1班', '340322199006247654'),
('007','百里玄策','男','山西','20','2班','340322199007247654'),
('008 ','小乔','女','河南','15','3班',null ),
('009 ','百里守约','男','湖南','21', '1班',''),
('010', '妲己','女','广东','26', '2班','340322199607247654'),
('011 ','李白','男','北京','30','4班','340322199005267754'),
('012','孙膑','男','新疆','26','3班','340322199000297655');

drop table if exists courses;
create table courses (
courseNo int(10) unsigned primary key auto_increment,
name varchar(10)
);
insert into courses values ('1', '数据库'), ('2', 'qtp'), ('3', 'linux'),
('4', '系统测试'), ('5', '单元测试'), ('6', '测试过程');
drop table if exists scores;
create table scores (
id int(10) unsigned primary key auto_increment,
courseNo int(10),
studentno varchar(10),
score tinyint(4)
);
insert into scores values ('1', '1', '001', '90'), ('2', '1', '002', '75'),
('3', '2', '002', '98'),('4', '3', '001', '86'),('5', '3', '003', '80'),
('6', '4', '004', '79'),('7', '5', '005', '96'),('8', '6', '006', '80');

练习加答案 （空格代表两种答案）

– 1、列出总人数大于4的部门号和总人数。(要统计所有部门的人数，需要使用分组, 同时也要使用聚合函数)
SELECT deptid,count() from employees GROUP BY deptid
HAVING count()>4;
– 2、列出开发部和和测试部的职工号、姓名
SELECT empid, empname,jobs FROM employees
WHERE jobs BETWEEN"开发"and"测试";

SELECT empid,empname,jobs FROM employees WHERE deptid in(SELECT deptid from departments WHERE
deptname in (“开发部”,“测试部”))
– 3、求出各部门党员的人数，要求显示部门名称。
SELECT deptname,count(*) FROM employees emp INNER JOIN
departments ds on ds.deptid=emp.deptid
WHERE emp.politicalstatus=“党员”
GROUP BY deptname;

select dep.deptname,count(*) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.politicalstatus=‘党员’
group by emp.deptid;

– 4、列出市场部的所有女职工的姓名和政治面貌。
select emp.empname, emp.politicalstatus from employees emp WHERE deptid=(
SELECT deptid FROM departments WHERE deptname = “市场部”)
and sex=“女”;

select emp.empname, emp.politicalstatus from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
where emp.sex=‘女’ and dep.deptname=‘市场部’;

– 5、显示所有职工的姓名、部门名和工资数。
SELECT emp.empname,ds.deptname,sa.salary FROM employees emp
INNER JOIN salary sa on sa.empid=emp.empid
INNER JOIN departments ds on emp.deptid=ds.deptid;

select emp.empname, dep.deptname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid;

– 6、显示各部门名和该部门的职工平均工资
SELECT ds.deptname,avg(salary) FROM employees emp
INNER JOIN salary sa on sa.empid=emp.empid
INNER JOIN departments ds on emp.deptid=ds.deptid
GROUP BY deptname;

select dep.deptname,avg(sa.salary) from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid;

– 7、显示工资最高的前3名职工的职工号和姓名
SELECT emp.empid,emp.empname,sa.salary from employees emp INNER JOIN salary sa
on emp.empid=sa.empid
ORDER BY sa.salary desc LIMIT 0,3;

select emp.empid,emp.empname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
order by sa.salary desc limit 3

– 8、列出工资在1000－2000之间的所有职工姓名。
SELECT emp.empname,sa.salary from employees emp INNER JOIN salary sa
on emp.empid=sa.empid WHERE sa.salary BETWEEN 1000 and 2000;

select emp.empname, sa.salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
where sa.salary between 1000 and 2000;

– 9、列出工资比王昭君高的员工。(首先查询王昭君的工资)
– 查询王昭君的工资
SELECT empname,sa.salary FROM employees emp INNER JOIN salary sa
on sa.empid = emp.empid
WHERE sa.salary>(SELECT sa.salary FROM employees emp INNER JOIN
salary sa on sa.empid = emp.empid WHERE empname =“王昭君”);

select emp.empname, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where sa.salary > (
select sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
where emp.empname=‘王昭君’
);

– 10、列出每个部门中工资小于本部门平均工资的员工信息。(首先查询出每个部门的平均工资)
SELECT emp.deptid,empname,sa.salary from employees emp INNER JOIN salary sa
on sa.empid=emp.empid INNER JOIN
(SELECT ds.deptid ,avg(sa.salary) avgs from employees emp INNER JOIN departments ds
on ds.deptid=emp.deptid INNER JOIN salary sa
on sa.empid=emp.empid
GROUP BY deptname ) as c
on emp.deptid =c.deptid
WHERE sa.salary < c.avgs;

select emp.deptid, emp.empname, sa.salary from employees as emp
inner join salary as sa on sa.empid=emp.empid
inner join (
select emp.deptid, avg(sa.salary) as avg_salary from employees as emp
inner join departments as dep on emp.deptid=dep.deptid
inner join salary as sa on sa.empid=emp.empid
group by emp.deptid
) as c on emp.deptid=c.deptid
where sa.salary < c.avg_salary;

SELECT emp.deptid,empname,sa.salary,avgs from employees emp INNER JOIN salary sa
on sa.empid=emp.empid
INNER JOIN
(SELECT ds.deptid ,avg(sa.salary) avgs from employees emp INNER JOIN departments ds
on ds.deptid=emp.deptid INNER JOIN salary sa
on sa.empid=emp.empid
GROUP BY deptname ) as c
on emp.deptid =c.deptid
WHERE sa.salary < avgs;