學(xué)習(xí)有一段時(shí)間了躺枕，再次練習(xí)題目驾荣；
#創(chuàng)建表 student
create table student
(sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5));
insert into student (sno,sname,ssex,sbirthday,class)
values(108,"曾華","男","1997-09-01",95033);
insert into student (sno,sname,ssex,sbirthday,class)
values(104,"匡明","男","1975-10-02",95031);
insert into student (sno,sname,ssex,sbirthday,class)
values(107,"王麗","女","1976-01-23",95033);
insert into student (sno,sname,ssex,sbirthday,class)
values(101,"李軍","男","1976-02-10",95033);
insert into student (sno,sname,ssex,sbirthday,class)
values(109,"王芳","女","1975-02-01",95031);
insert into student (sno,sname,ssex,sbirthday,class)
values(103,"陸君","男","1974-06-03",95031);
select * from student;
#創(chuàng)建表 course
create table course
(cno VARCHAR(5) not NULL,
cname VARCHAR(10) not NULL,
tno VARCHAR(10) NOT NULL);
INSERT INTO course
values("3-105","計(jì)算機(jī)導(dǎo)論",825);
INSERT INTO course
VALUES("3-245","操作系統(tǒng)",804);
INSERT INTO course
VALUES("6-166","數(shù)據(jù)電路",856);
INSERT INTO course
VALUES("9-888","高等數(shù)學(xué)",100);
#創(chuàng)建表 score
drop table score;
CREATE TABLE score
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree numeric(10,1) NOT NULL);
INSERT INTO score
VALUES(103,"3-245",86);
INSERT INTO score
VALUES(105,"3-245",75);
INSERT INTO score
VALUES(109,"3-245",82);
INSERT INTO score
VALUES(103,"3-105",92);
INSERT INTO score
VALUES(105,"3-105",88);
INSERT INTO score
VALUES(109,"3-105",76);
INSERT INTO score
VALUES(101,"3-105",64);
INSERT INTO score
VALUES(107,"3-105",91);
INSERT INTO score
VALUES(108,"3-105",78);
INSERT INTO score
VALUES(101,"6-166",85);
INSERT INTO score
VALUES(107,"6-106",79);
INSERT INTO score
VALUES(108,"6-166",81);
select * from score;
#建表teacher
CREATE TABLE teacher
(tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL,
tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL,
prof VARCHAR(6),
depart VARCHAR(10) NOT NULL);
INSERT INTO teacher
VALUES(856,"李誠","男","1958-12-02","副教授","計(jì)算機(jī)系");
INSERT INTO teacher
VALUES(856,"張旭","男","1969-03-12","講師","電子工程系");
INSERT INTO teacher
VALUES(825,"王萍","女","1972-05-05","助教","計(jì)算機(jī)系");
INSERT INTO teacher
VALUES(831,"劉冰","女","1977-08-14","助教","電子工程系");
select * from teacher;
#題目
#1 查詢student中的所有記錄的sname,ssex,class的列
select sname,ssex,class from student;
#2查詢教師所有的單位即不重復(fù)的列萄传；
select distinct * from teacher;
#3 查詢student 表的所有記錄
select * from student;
#4 查詢score中成績在60到80之間的所有記錄
select * from score where degree between 60 and 80;
#5 查詢score中成績?yōu)?5,86进胯，或80之間的記錄
select * from score where degree in (85,86,80);
#6 查詢student 表中“95031”班或性別為女的所有記錄送漠；
select * from student where class = "95031" or ssex ="女";
#7 以class降序查詢studnt表中的所有記錄喧兄；
select * from student order by class desc;
#8 以cno升序逆皮、degree降序查詢score的所有記錄宅粥；
select * from score order by cno asc, degree desc;
#9 查詢"95031"班的學(xué)生人數(shù)；
select count(*) from student group by class having class = "95031";
#10 查詢score表中的最高分的學(xué)生學(xué)號和課程號电谣；
select? sno,cno from score order by degree desc limit 1;
#11 查詢“3-105”課程的平均分秽梅；
select avg(degree) from score group by cno having cno ="3-105";
#12 查詢score中至少有5學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)
select avg(degree) from score where cno like "3%"group by cno having count(sno)>=5 ;
#13 最低分>70，最高分<90的sno列剿牺；
select sno,degree from score where degree between 70 and 90;
#14 查詢所有學(xué)生的sname,cno 和degree列企垦；
select b.sname, a.cno,a.degree from
(select sno,cno,degree from score) as a
inner join
(select sno,sname from student) as b
on a.sno = b.sno;
#15 查詢所有學(xué)生的Sno、cname和Degree列
select c.cname, a.sno,a.degree from
(select sno,cno,degree from score) as a
inner join
(select cno,cname from course) as c
on a.cno = c.cno;
#16晒来、查詢所有學(xué)生的Sname-studet-c钞诡、Cname-course-a和Degree-score-b列
select b.sname, c.cname, a.degree from
(select cname,cno from course) as c
inner join
(select cno,sno,degree from score)? as a
on c.cno = a.cno
inner join
(select sname,sno from student) as b
on b.sno=a.sno;
#17、查詢“95033”班所選課程的平均分: 95033班-學(xué)生- 選的課程-得分
select avg(a.degree) from
(select sno,degree from score) as a
inner join
(select sno,class from student where class ="95033") as d
on a.sno=d.sno;
#18湃崩、查詢選修“3-105”課程的成績高于“109”號同學(xué)成績的所有同學(xué)的記錄
select * from score
where cno="3-105" and degree>(select degree from score where sno = "109" and cno="3-105");
#19 查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄荧降。
select distinct a1.sno,a1.cno,a1.degree from
(select sno,cno,degree from score where degree< (select max(degree) from score)) as a1
inner join
(select sno from score group by cno having count(cno)>1) as b1
on a1.sno = b1.sno;
#20 查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列
select b.sno,b.sname,b.sbirthday from student as b
where year(b.sbirthday) = (select year(c.sbirthday) from student as c where sno=108);
select * from student;
#21 查詢“張旭“教師任課的學(xué)生成績: teacher-tname,tno,course-tno,cno,score-cno,degree
#表連接法
select b.sno,b.degree from
(select tno,cno from course) as a
inner join (select tno from teacher where tname="張旭") as c
on a.tno = c.tno
inner join (select cno,sno,degree from score) as b
on a.cno= b.cno;
#子查詢法
select sno,cno,degree from score c
where c.cno in (select cno from course b
where b.tno = (select tno from teacher a where tname="張旭"));
#22攒读、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名
select tname from teacher c
where c.tno = (select tno from course b
where b.cno = (select cno from score a group by cno having count(cno)>5));
#23朵诫、查詢95033班和95031班全體學(xué)生的記錄
select * from student where class="95033" or class="95031";
#24、查詢存在有85分以上成績的課程Cno.
select cno from score where degree> 85;
#25薄扁、查詢出“計(jì)算機(jī)系“教師所教課程的成績表剪返。
select cno,degree from score b
where b.cno in (select cno from course a where a.tno in (select tno from teacher where depart="計(jì)算機(jī)系"));
#26废累、查詢“計(jì)算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof
select tname, prof from teacher where depart="計(jì)算機(jī)系" or depart="電子工程系";
#27、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學(xué)的Cno脱盲、Sno和Degree,并按Degree從高到低次序排序
select a.cno,a.sno,a.degree,b.cno,b.degree from
(select sno,degree,cno from score where cno="3-105") as a
inner join (select sno,degree,cno from score where cno="3-245") as b
on a.sno=b.sno
where a.degree > b.degree order by a.degree desc;
#28邑滨、查詢所有教師和同學(xué)的name、sex和birthday.
select sname,ssex,date(sbirthday) from student
union all
select tname,tsex,date(tbirthday) from teacher;
#29宾毒、查詢所有“女”教師和“女”同學(xué)的name驼修、sex和birthday.
select sname,ssex,date(sbirthday) from student where ssex="女"
union all
select tname,tsex,date(tbirthday) from teacher where tsex="女";
#30 查詢成績比該課程平均成績低的同學(xué)的成績表
select * from score a
where a.degree< all(select avg(degree) from score b group by cno);
#31 查詢所有任課教師的Tname和Depart.
select tname,depart from teacher a where
a.tno in (select tno from course);
#32 查詢所有未講課的教師的Tname和Depart.
select tname,depart from teacher a where
a.tno not in (select tno from course);
#33、查詢至少有2名男生的班號诈铛。
select class from student where ssex="男" group by ssex having count(ssex)>1;
#34、查詢Student表中不姓“王”的同學(xué)記錄墨礁。
select * from student where sname not like "王%";
#35幢竹、查詢Student表中每個(gè)學(xué)生的姓名和年齡。
select sname, datediff(curdate(),date(sbirthday)) from student;
#36恩静、查詢Student表中最大和最小的Sbirthday日期值
select date(max(sbirthday)),date(min(sbirthday)) from student;
#37焕毫、以班號和年齡從大到小的順序查詢Student表中的全部記錄
select * from student order by class desc, sbirthday asc;
#38、查詢“男”教師及其所上的課程
select b.tname, a.cname from
(select cname,cno,tno from course) a
inner join
(select tname,tno from teacher where tsex="男") b
on a.tno = b.tno;
#39驶乾、查詢最高分同學(xué)的Sno邑飒、Cno和Degree列
select * from score a where a.degree = (select max(degree) from score);
#40、查詢和“李軍”同性別的所有同學(xué)的Sname
select sname from student a where a.ssex=(select ssex from student where sname="李軍");
#41级乐、查詢和“李軍”同性別并同班的同學(xué)Sname
select sname from student a where a.ssex=(select ssex from student where sname="李軍")
and a.class=(select class from student where sname="李軍");
#42疙咸、查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表
select c.degree from
(select sno,sname,ssex from student where ssex="男") a
inner join
(select cno,cname from course where cname="計(jì)算機(jī)導(dǎo)論") b
inner join
(select sno,cno,degree from score ) c
on a.sno = c.sno and b.cno=c.cno ;
