单表查询面试题
准备数据
CREATE TABLE students (
studentNo int(10) primary key auto_increment,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age int(4),
class varchar(10),
card varchar(20)
);
INSERT INTO students (name, sex, hometown, age, class, card)
VALUES ('王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
( '张飞', '男', '南京', '24', '3班', '340322199003247654'),
( '白起', '男', '安徽', '22', '4班', '3403221 99005247654'),
( '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
( '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
( '小乔', '女', '河南', '15', '3班', NULL),
( '百里守约', '男', '湖南', '21', '1班', ''),
( '妲己', '女', '广东', '26', '2班', '340322199607247654'),
( '李白', '男', '北京', '30', '4班', '340322199005267754'),
( '孙膑', '男', '新疆', '26', '3班', '340322199000297655');
CREATE TABLE courses (
courseNo int(10) PRIMARY KEY AUTO_INCREMENT,
name varchar(10)
);
INSERT INTO courses
VALUES ('1', '数据库'),
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试'),
('5', '单元测试'),
('6', '测试过程');
CREATE TABLE scores (
id int(10) PRIMARY KEY AUTO_INCREMENT,
courseNo int(10),
studentNo int(10),
score int(4)
);
INSERT INTO scores
VALUES ('1', '1', 1, '90'),
('2', '1', 2, '75'),
('3', '2', 2, '98'),
('4', '3', 1, '86'),题目
1、查询学生"百里守约"的基本信息
2、查询学生"百里守约"或”百里玄策”的基本信息
3、查询姓"张"学生的姓名，年龄，班级
4、查询姓名中含有"约"字的学生的基本信息
5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号
6、查询姓"百"或者姓”孙”的学生的基本信息
7、查询姓"百"并且家乡是"山西"的学生信息
8、查询家乡是"北京"、”新疆”、”山东”或者"上海"的学生的信息
9、查询姓"孙"，但是家乡不是"河北"的学生信息
10、查询家乡不是"北京"、"新疆"、"山东"、"上海"的学生的信息
11、查询全部学生信息，并按照“性别”排序
('5', '3', 3, '80'),
('6', '4', 4, '79'),
('7', '5', 5, '96'),
('8', '6', 6, '80');
select * from students where name='百里守约'
select * from students where name='百里守约' or name='百里玄策'
select name,age,class from students where name like '张%'
select * from students where name like '%约%'
select studentNo,name,age,class,card from students where name like '孙__'
select * from students where name like '百%' or name like '孙%'
select * from students where name like '百%' and hometown='山西'
select * from students where hometown in('北京','新疆','山东','上海')
select * from students where name like '孙%' and hometown!='河北'
select * from students where hometown not in('北京','新疆','山东','上海')12、查询现有学生都来自于哪些不同的省份
13、查询所有男生，并按年龄升序排序
14、统计共有多少个学生
15、统计年龄大于20岁的学生有多少个
16、统计男生的平均年龄
17、查询1班学生中的最大年龄是多少
18、统计2班男女生各有多少人
19、统计每个班级中每种性别的学生人数，并按照班级升序排序
多表查询面试题
准备数据
select * from students order by sex
select hometown from students group by hometown
select * from students where sex='男' order by age
select count(*) from students
select count(*) from students where age>20
select avg(age) from students where sex='男'
select max(age) from students where class='1班'
select sex,count(*) from students where class='2班' group by sex
select class,sex,count(*) from students group by class,sex order by class
create table departments (
deptid int(10) primary key,
deptname varchar(20) not null -- 部门名称
);
insert into departments values ('1001', '市场部');
insert into departments values ('1002', '测试部');
insert into departments values ('1003', '开发部');
create table employees (题目
1.列出总人数大于4的部门号和总人数
2.列出开发部和测试部的职工号，姓名
empid int(10) primary key,
empname varchar(20) not null, -- 姓名
sex varchar(4) default null, -- 性别
deptid int(20) default null, -- 部门编号
jobs varchar(20) default null, -- 岗位
politicalstatus varchar(20) default null, -- 政治面貌
leader int(10) default null
);
insert into employees values ('1', '王昭君', '女', '1003', '开发', '群众', '9');
insert into employees values ('2', '诸葛亮', '男', '1003', '开发经理', '群众',
null);
insert into employees values ('3', '张飞', '男', '1002', '测试', '团员', '4');
insert into employees values ('4', '白起', '男', '1002', '测试经理', '党员', null);
insert into employees values ('5', '大乔', '女', '1002', '测试', '党员', '4');
insert into employees values ('6', '孙尚香', '女', '1001', '市场', '党员', '12');
insert into employees values ('7', '百里玄策', '男', '1001', '市场', '团员', '12');
insert into employees values ('8', '小乔', '女', '1002', '测试', '群众', '4');
insert into employees values ('9', '百里守约', '男', '1003', '开发', '党员', '9');
insert into employees values ('10', '妲己', '女', '1003', '开发', '团员', '9');
insert into employees values ('11', '李白', '男', '1002', '测试', '团员', '4');
insert into employees values ('12', '孙膑', '男', '1001', '市场经理', '党员', null);
create table salary (
sid int(10) primary key,
empid int(10) not null,
salary int(10) not null -- 工资
);
insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');
select deptid, count(*) from employees e group by deptid having count(*)>4
select e.empid,d.empname
from employees e inner join departments d on e.deptid = d.deptid
where d.deptname in ('开发部','测试部')3.求出各部门党员的人数，要求显示部门名称
select d.deptname,count(*)
from employees e inner join departments d on e.deptid=d.deptid
where e.politicalstatus ="党员" group by e.deptid
4.列出市场部的所有女职工的姓名和政治面貌
select e.empname,e.politicalstatus
from employees e inner join departments d on e.deptid = d.deptid
where e.sex= '女' and d.deptname = '市场部'
5.显示所有职工的姓名，部门名和工资数
select e.empname,d.deptname,s.salary
from employees e
inner join departments d on e .deptid = d.deptid
inner join salary s on e.empid = s.empid
6.显示各部门名和该部门的职工平均工资
select d.deptname, avg(s.salary)
from departments d
inner join employees e on d.deptid = e.deptid
inner join salary s on e.empid = s.empid
group by d.deptname
7.显示工资最高的前3名职工的职工号和姓名
select e.empid, e.empname,s.salary
from salary s inner join employees e on s.empid = e.empid
order by s.salary desc
limit 3
8.列出工资在1000-2000之间的所有职工姓名
select e.empname,s.salary
from salary s
inner join employees e on s.empid = e.empid
where s.salary between 1000 and 2000
9.列出工资比王昭君高的员工
select *
from employees e
inner join salary s on e.empid=s.empid
where s.salary > (
select s.salary from employees e inner join salary s on e.empid=s.empid where
e.empna me='王昭君'
)
