單表查詢面試題
準(zhǔn)備數(shù)據(jù)
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', '數(shù)據(jù)庫(kù)'),
('2', 'qtp'),
('3', 'linux'),
('4', '系統(tǒng)測(cè)試'),
('5', '單元測(cè)試'),
('6', '測(cè)試過(guò)程');
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划提、查詢學(xué)生"百里守約"的基本信息
2、查詢學(xué)生"百里守約"或”百里玄策”的基本信息
3、查詢姓"張"學(xué)生的姓名唉堪，年齡槽袄，班級(jí)
4芹关、查詢姓名中含有"約"字的學(xué)生的基本信息
5皿桑、查詢姓名長(zhǎng)度為三個(gè)字撒会，姓“孫”的學(xué)生的學(xué)號(hào)嘹朗，姓名，年齡诵肛，班級(jí)屹培，身份證號(hào)
6、查詢姓"百"或者姓”孫”的學(xué)生的基本信息
7、查詢姓"百"并且家鄉(xiāng)是"山西"的學(xué)生信息
8褪秀、查詢家鄉(xiāng)是"北京"蓄诽、”新疆”、”山東”或者"上海"的學(xué)生的信息
9媒吗、查詢姓"孫"若专，但是家鄉(xiāng)不是"河北"的學(xué)生信息
10、查詢家鄉(xiāng)不是"北京"蝴猪、"新疆"、"山東"膊爪、"上海"的學(xué)生的信息
11自阱、查詢?nèi)繉W(xué)生信息，并按照“性別”排序
('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米酬、查詢現(xiàn)有學(xué)生都來(lái)自于哪些不同的省份
13沛豌、查詢所有男生，并按年齡升序排序
14赃额、統(tǒng)計(jì)共有多少個(gè)學(xué)生
15加派、統(tǒng)計(jì)年齡大于20歲的學(xué)生有多少個(gè)
16、統(tǒng)計(jì)男生的平均年齡
17跳芳、查詢1班學(xué)生中的最大年齡是多少
18芍锦、統(tǒng)計(jì)2班男女生各有多少人
19、統(tǒng)計(jì)每個(gè)班級(jí)中每種性別的學(xué)生人數(shù)飞盆，并按照班級(jí)升序排序
多表查詢面試題
準(zhǔn)備數(shù)據(jù)
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', '市場(chǎng)部');
insert into departments values ('1002', '測(cè)試部');
insert into departments values ('1003', '開發(fā)部');
create table employees (題目
1.列出總?cè)藬?shù)大于4的部門號(hào)和總?cè)藬?shù)
2.列出開發(fā)部和測(cè)試部的職工號(hào)娄琉，姓名
empid int(10) primary key,
empname varchar(20) not null, -- 姓名
sex varchar(4) default null, -- 性別
deptid int(20) default null, -- 部門編號(hào)
jobs varchar(20) default null, -- 崗位
politicalstatus varchar(20) default null, -- 政治面貌
leader int(10) default null
);
insert into employees values ('1', '王昭君', '女', '1003', '開發(fā)', '群眾', '9');
insert into employees values ('2', '諸葛亮', '男', '1003', '開發(fā)經(jīng)理', '群眾',
null);
insert into employees values ('3', '張飛', '男', '1002', '測(cè)試', '團(tuán)員', '4');
insert into employees values ('4', '白起', '男', '1002', '測(cè)試經(jīng)理', '黨員', null);
insert into employees values ('5', '大喬', '女', '1002', '測(cè)試', '黨員', '4');
insert into employees values ('6', '孫尚香', '女', '1001', '市場(chǎng)', '黨員', '12');
insert into employees values ('7', '百里玄策', '男', '1001', '市場(chǎng)', '團(tuán)員', '12');
insert into employees values ('8', '小喬', '女', '1002', '測(cè)試', '群眾', '4');
insert into employees values ('9', '百里守約', '男', '1003', '開發(fā)', '黨員', '9');
insert into employees values ('10', '妲己', '女', '1003', '開發(fā)', '團(tuán)員', '9');
insert into employees values ('11', '李白', '男', '1002', '測(cè)試', '團(tuán)員', '4');
insert into employees values ('12', '孫臏', '男', '1001', '市場(chǎng)經(jīng)理', '黨員', 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 ('開發(fā)部','測(cè)試部')3.求出各部門黨員的人數(shù)，要求顯示部門名稱
select d.deptname,count(*)
from employees e inner join departments d on e.deptid=d.deptid
where e.politicalstatus ="黨員" group by e.deptid
4.列出市場(chǎng)部的所有女職工的姓名和政治面貌
select e.empname,e.politicalstatus
from employees e inner join departments d on e.deptid = d.deptid
where e.sex= '女' and d.deptname = '市場(chǎng)部'
5.顯示所有職工的姓名吓歇，部門名和工資數(shù)
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名職工的職工號(hào)和姓名
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='王昭君'
)
