超有用的sql查詢語句。項目、面試都有用

create database cstp;
use cstp;
/*1:建立學生表*/
create table student (
?? 學號 char(3) primary key,
?? 姓名 char(4),
?? 性別 char(2),
?? 年齡 int,
?? 班級 char(5)
);
insert into student values('108','曾華','男',19,'95033');
insert into student values('105','匡明','男',20,'95031');
insert into student values('107','王麗','女',20,'95033');
insert into student values('101','李軍','男',19,'95033');
insert into student values('109','王芳','女',22,'95031');
insert into student values('103','陸君','男',20,'95031');
/*2:建立教師表*/
create table teacher(
?? 教師號 char(3) primary key,
?? 姓名 char(4),
?? 性別 char(2),
?? 年齡 int ,
?? 級別 char(6),
?? 專業 char(8)
);
insert into teacher values('804','李成','男',42,'副教授','計算機系');
insert into teacher values('856','張旭','男',35,'講師','電子工程');
insert into teacher values('825','王萍','女',28,'助教','計算機系');
insert into teacher values('831','劉冰','女',25,'助教','電子工程');
/*3:建立課程表*/
create table course (
?? 課程號 char(5) primary key,
?? 課程名 char(10),
?? 教師號 char(3),
?? foreign key(教師號) references teacher(教師號)
);
insert into course values('3-105','計算機導論','825');
insert into course values('3-245','操作系統','804');
insert into course values('6-166','數字電路','856');
insert into course values('9-888','高等數學','831');
/*4:建立選課表*/
create table sc(
?? 學號 char(3),
?? 課程號 char(5),
?? primary key(學號,課程號),
?? 成績 int,
?? foreign key(學號) references student(學號),
?? foreign key(課程號) references course(課程號)
);
insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);
/*5:所有表內容*/
select * from student;
select * from course;
select * from teacher;
select * from sc;

所有表信息如下:

student(學生)表 ?	course(課程)表 ?
teacher(教師)表 ?	sc(選課)表 ?

作業題:
1.查詢選修課程'3-105'且成績在60到80之間的所有記錄。
注釋:用于指定某個范圍使用between and,也可以使用and連接符;
答案:
法1:select * from sc where 課程號='3-105' and 成績 between 60 and 80;
法2:select * from sc where 課程號='3-105' and 成績 > 60 and 成績 < 80;
2.查詢成績為85、86或88的記錄。
注釋:用于制定某個集合使用 in 關鍵字,也可以使用 or 連接符;
答案:
法1:select * from sc where 成績=85 or 成績=86 or 成績=88;
法2:select * from sc where 成績 in(85,86,88);
3.查詢'95031'班的學生人數。
注釋:count(*)用于計算結果總數;
答案:
select count(班級) as 學生人數 from student where 班級='95031';
4.查詢最低分大于70,且最高分小于90的學號列。
注釋:having后面跟聚合函數:avg,min,max,count;having語句只能跟在:group by語句后面;
答案:
select 學號,min(成績)as 最低分,max(成績)as 最高分 from sc group by 學號 having min(成績)>70 and max(成績) < 90;
5.查詢至少有5名學生選修并以3開頭的課程的平均成績。
注釋:group by 語句置于where語句后面表示根據什么條件來分組;
答案:
select avg(成績) as 平均成績 from sc where 課程號 like '3%' group by 課程號 having count(課程號) >= 5;
6.查詢平均分大于80分的學生的成績表
注釋:having后面跟聚合函數avg();
答案:
select * from sc group by 學號 having avg(成績) > 80;
7.查詢'95033'班每個學生所選課程的平均分。
注釋:此題是根據 '95033'班學生的學號進行的分組,使用where語句限制group by語句的分組條件;
答案:
select 學號,avg(成績) as 平均成績 from sc where 學號 in (select 學號 from student where 班級 = '95033') group by 學號;
8.以選修 '3-105'為例,查詢成績高于'109'號同學的所有同學的記錄。
注釋:此題使用典型的嵌套查詢,層層深入;
答案:
select * from student where 學號 in (select 學號 from sc where 課程號='3-105' and 成績 >
(select 成績 from sc where 學號='109' and 課程號='3-105'));
9.查詢與學號為'108'的同學同歲的所有學生的學號、姓名和年齡。
注釋:當查詢的結果集返回只有一個時關鍵字in的作用等價于'='的作用,但是注意'='只能用與返回結果集只有一個,而 in 可以有多個結果;
答案:
法1:select 學號,姓名,年齡 from student where 年齡 = (select 年齡 from student where 學號='108');
法2:select 學號,姓名,年齡 from student where 年齡?in (select 年齡 from student where 學號='108');
10.查詢'張旭'教師任課的課程號，選修其課程學生的學號和成績。
注釋:此題使用了表的連接:inner join,將多個表連接在一起組成一個新的表,標的連接的條件是必須存在相同的列;
答案:
法1:select 課程號 from course where 教師號=(select 教師號 from teacher where 姓名='張旭'); select 學號,成績 from sc where 課程號 in(select 課程號 from course where 教師號 in
(select 教師號 from teacher where 姓名='張旭'));
法2:select teacher.姓名 as 教師姓名,course.課程號,student.姓名 as 學生姓名,student.學號,成績 from? teacher inner join (course inner join (sc inner join student on student.學號=sc.學號) on course.課程號=sc.課程號) on course.教師號=teacher.教師號 where? teacher.姓名='張旭';
11.查詢選修其課程的學生人數多于5人的教師姓名。
注冊:此題采用嵌套查詢和連接表兩種方法,表現出了同樣的效果;
答案:
法1:select 姓名 as 教師姓名 from teacher where 教師號 in (select 教師號 from course where 課程號 in (select 課程號 from sc group by 課程號 having count(*) > 5));
法2:select 姓名 as 教師姓名 from teacher inner join (course inner join sc on course.課程號=sc.課程號) on teacher.教師號=course.教師號 group by sc.課程號 having count(*) > 5;
12.查詢'計算機系'與'電子工程系'不同職稱的教師的姓名和職稱。
注釋:不同職稱意味著分組后的返回結果集只有一個;
答案:
select 姓名 as 教師姓名,級別 as 職稱 from teacher group by 級別 having count(*) = 1;
13.查詢選修編號為'3-105'課程且成績至少高于選修編號為'3-245'課程的同學的課程號、學號 、成績并按成績從高到低次序排列。
注釋:any 表示任意一個;
答案:
select * from sc where 課程號='3-105' and 成績 > any (select 成績 from sc where 課程號='3-245') order by 成績 desc;
14.查詢選修編號為'3-105'課程且成績高于選修編號為'3-245'課程的同學的課程號、學號 、成績。
注釋:all 表示所有;
答案:
select * from sc where 課程號='3-105' and 成績 > all (select 成績 from sc where 課程號='3-245') order by 成績 desc;
15.列出所有教師和同學的姓名 、性別 、年齡。
注釋:此題將所有表連接在一起:
答案:
select student.姓名 as 學生姓名,student.性別 as 學生性別,student.年齡 as 學生年齡,
teacher.姓名 as 教師姓名,teacher.性別 as 教師性別,teacher.年齡 as 教師年齡 from
student inner join (sc inner join (course inner join teacher on course.教師號=teacher.教師號)
on sc.課程號=course.課程號) on student.學號=sc.學號;
16.查詢成績比'3-105'課程的平均成績低的學生的成績表。
答案:
select * from sc where 成績 < (select avg(成績) from sc where 課程號='3-105') and 課程號='3-105';
17.查詢成績比該課程平均成績低的學生的成績表。
注釋:此題是所有題中最難的,下面的方法很經典,請認真琢磨,先按照課程進行分組,查出小于各門課程平均成績的所有成績;
答案:
select * from sc where 成績 < any (select avg(成績) from sc group by 課程號) order by 課程號 asc;
18.列出所有任課教師的姓名和專業。
注釋:姓名和專業涉及兩個表course表和teacher表,只需連接這兩個表就能解決此題了;
答案:
select teacher.姓名 as 教師姓名,專業 from teacher where 教師號 in (select 教師號 from course where 課程號 in (select 課程號 from sc group by 課程號));
19.列出所有未講課教師的姓名和專業。
注釋:解答同上題;
答案:
select teacher.姓名 as 教師姓名,專業 from teacher where 教師號 not in
(select 教師號 from course where 課程號 in (select 課程號 from sc group by 課程號));
20.列出至少有2名男生的班號。
注釋:根據性別='男'這個條件來對student表進行分組就可以了;
答案:
select 班級 as 班號 from student where 性別='男' group by 班級 having count(*) >= 2;
21.查詢不姓'王'的學生記錄。
答案:
法1:select *? from student where 姓名 not like '王%';
法2:select * from student where 姓名 not in (select 姓名 from student where 姓名like '王%');
22.查詢每門課最高分的學生的學號、課程號、成績。
注釋:此題的技巧在于根據最高分來查成績表;
答案:
select * from sc where 成績 in (select max(成績) from sc group by 課程號);
23.查詢與'李軍'同性別并同班的同學名字。
注釋:嵌套查詢;
答案:
select 姓名 from student where 性別=(select 性別 from student where 姓名='李軍') and
班級=(select 班級 from student where 姓名='李軍');
24.查詢'男'教師及其所上的課程。
注釋:先分析此題涉及的表;
答案:
select 姓名 as 教師名,課程名 as 課程 from teacher inner join course on teacher.教師號=course.教師號 where 性別='男';
25.查

超有用的sql查詢語句。項目、面試都有用