建表語句
CREATE TABLE students
(sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5));
CREATE TABLE courses
(cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL);
CREATE TABLE scores
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL);
CREATE TABLE teachers
(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);
插入數(shù)據(jù)
INSERT INTO students (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華' ,'男' ,'1977-09-01',95033);
INSERT INTO students (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO students (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗' ,'女' ,'1976-01-23',95033);
INSERT INTO students (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍' ,'男' ,'1976-02-20',95033);
INSERT INTO students (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO students (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君' ,'男' ,'1974-06-03',95031);
INSERT INTO courses(CNO,CNAME,TNO)VALUES ('3-105' ,'計算機(jī)導(dǎo)論',825);
INSERT INTO courses(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系統(tǒng)' ,804);
INSERT INTO courses(CNO,CNAME,TNO)VALUES ('6-166' ,'數(shù)據(jù)電路' ,856);
INSERT INTO courses(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數(shù)學(xué)' ,100);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO scores(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
INSERT INTO teachers(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李誠','男','1958-12-02','副教授','計算機(jī)系');
INSERT INTO teachers(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO teachers(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','計算機(jī)系');
INSERT INTO teachers(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系');
題目
1、 查詢Student表中的所有記錄的Sname、Ssex和Class列。
2、 查詢教師所有的單位即不重復(fù)的Depart列。
3、 查詢Student表的所有記錄。
4、 查詢Score表中成績在60到80之間的所有記錄。
5、 查詢Score表中成績?yōu)?5，86或88的記錄。
6、 查詢Student表中“95031”班或性別為“女”的同學(xué)記錄。
7、 以Class降序查詢Student表的所有記錄。
8、 以Cno升序、Degree降序查詢Score表的所有記錄。
9、 查詢“95031”班的學(xué)生人數(shù)。
10、查詢Score表中的最高分的學(xué)生學(xué)號和課程號。
11、查詢‘3-105’號課程的平均分。
12、查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。
13、查詢最低分大于70，最高分小于90的Sno列。
14、查詢所有學(xué)生的Sname、Cno和Degree列。
15、查詢所有學(xué)生的Sno、Cname和Degree列。
16、查詢所有學(xué)生的Sname、Cname和Degree列。
17、查詢“95033”班所選課程的平均分。
18、假設(shè)使用如下命令建立了一個grade表：
create table grade(low? number(3,0),upp? number(3),rank? char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
commit;
現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列。
19、查詢選修“3-105”課程的成績高于“109”號同學(xué)成績的所有同學(xué)的記錄。
20、查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄。
21、查詢成績高于學(xué)號為“109”、課程號為“3-105”的成績的所有記錄。
22、查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。
23、查詢“張旭“教師任課的學(xué)生成績。
24、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名。
25、查詢95033班和95031班全體學(xué)生的記錄。
26、查詢存在有85分以上成績的課程Cno.
27、查詢出“計算機(jī)系“教師所教課程的成績表。
28、查詢“計算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof。
29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學(xué)的Cno、Sno和Degree,并按Degree從高到低次序排序。
30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學(xué)的Cno、Sno和Degree.
31、查詢所有教師和同學(xué)的name、sex和birthday.
32、查詢所有“女”教師和“女”同學(xué)的name、sex和birthday.
33、查詢成績比該課程平均成績低的同學(xué)的成績表。
34、查詢所有任課教師的Tname和Depart.
35? 查詢所有未講課的教師的Tname和Depart.
36、查詢至少有2名男生的班號。
37、查詢Student表中不姓“王”的同學(xué)記錄。
38、查詢Student表中每個學(xué)生的姓名和年齡。
39、查詢Student表中最大和最小的Sbirthday日期值。
40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。
41、查詢“男”教師及其所上的課程。
42、查詢最高分同學(xué)的Sno、Cno和Degree列。
43、查詢和“李軍”同性別的所有同學(xué)的Sname.
44、查詢和“李軍”同性別并同班的同學(xué)Sname.
45、查詢所有選修“計算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表
1.查詢Student表中的所有記錄的Sname、Ssex和Class列。
SELECT Sname,Ssex,Class
FROM Student;
2.查詢教師所有的單位即不重復(fù)的Depart列。
SELECT DISTINCT Depart
FROM teachers;
3.查詢Student表的所有記錄。
SELECT *
FROM students;
4.查詢Score表中成績在60到80之間的所有記錄。
SELECT *
FROM scores
WHERE Degree BETWEEN 60 AND 80;
5.查詢Score表中成績?yōu)?5，86或88的記錄。
SELECT *
FROM scores
WHERE Degree IN (85,86,88);
6.查詢Student表中“95031”班或性別為“女”的同學(xué)記錄。
SELECT *
FROM students
WHERE Class='95031' OR Ssex='女';
7.以Class降序查詢Student表的所有記錄。
SELECT *
FROM students
ORDER BY Class DESC;
8.以Cno升序、Degree降序查詢Score表的所有記錄。
SELECT *
FROM scores
ORDER BY Cno,Degree DESC;
9.查詢“95031”班的學(xué)生人數(shù)。
SELECT COUNT(1) AS StuNum
FROM students
WHERE Class='95031';
10.查詢Score表中的最高分的學(xué)生學(xué)號和課程號。
SELECT Sno,Cno
FROM scores
ORDER BY Degree DESC
LIMIT 1;
11.查詢‘3-105’號課程的平均分。
SELECT AVG(Degree)
FROM scores
WHERE Cno='3-105';
12.查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。
SELECT Cno,AVG(Degree)
FROM scores
WHERE Cno LIKE '3%'
GROUP BY Cno
HAVING COUNT(Sno) >= 5;
13.查詢最低分大于70，最高分小于90的Sno列。
SELECT Sno
FROM scores
GROUP BY Sno
HAVING MAX(Degree)<90 AND MIN(Degree)>70;
14.查詢所有學(xué)生的Sname、Cno和Degree列。
SELECT Sname,Cno,Degree
FROM students INNER JOIN scores
ON(students.Sno=scores.Sno)
ORDER BY Sname;
15.查詢所有學(xué)生的Sno、Cname和Degree列。
SELECT Sno,Cname,Degree
FROM scores INNER JOIN courses
ON(scores.Cno=courses.Cno)
ORDER BY Sno;
16.查詢所有學(xué)生的Sname、Cname和Degree列。
SELECT Sname,Cname,Degree
FROM students INNER JOIN scores
ON(students.Sno=scores.Sno) INNER JOIN courses
ON(scores.Cno=courses.Cno)
ORDER BY Sname;
17.查詢“95033”班所選課程的平均分。
SELECT Cname,AVG(Degree)
FROM students INNER JOIN scores
ON(students.Sno=scores.Sno) INNER JOIN courses
ON(scores.Cno=courses.Cno)
WHERE Class='95033'
GROUP BY courses.Cno
ORDER BY Cname;
18.假設(shè)使用如下命令建立了一個grade表：
CREATE TABLE grade(low TINYINT,upp TINYINT,rank CHAR(1));
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');
現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列。
SELECT Sno,Cno,rank
FROM scores INNER JOIN grade
ON(scores.Degree>=grade.low AND scores.Degree<=grade.upp)
ORDER BY Sno;
19.查詢選修“3-105”課程的成績高于“109”號同學(xué)成績的所有同學(xué)的記錄。
SELECT s1.Sno,s1.Degree
FROM scores AS s1 INNER JOIN scores AS s2
ON(s1.Cno=s2.Cno AND s1.Degree>s2.Degree)
WHERE s1.Cno='3-105' AND s2.Sno='109'
ORDER BY s1.Sno;
20.查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄。
SELECT *
FROM scores
GROUP BY Sno
HAVING COUNT(cno)>1 AND Degree!=MAX(Degree);
21.查詢成績高于學(xué)號為“109”、課程號為“3-105”的成績的所有記錄。
SELECT s1.Sno,s1.Degree
FROM scores AS s1 INNER JOIN scores AS s2
ON(s1.Cno=s2.Cno AND s1.Degree>s2.Degree)
WHERE s1.Cno='3-105' AND s2.Sno='109'
ORDER BY s1.Sno;
22.查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。
SELECT s1.Sno,s1.Sname,s1.Sbirthday
FROM students AS s1 INNER JOIN students AS s2
ON(YEAR(s1.Sbirthday)=YEAR(s2.Sbirthday))
WHERE s2.Sno='108';
23.查詢“張旭“教師任課的學(xué)生成績。
SELECT Sno,Degree
FROM scores INNER JOIN courses
ON(scores.Cno=courses.Cno) INNER JOIN teachers
ON(courses.Tno=teachers.Tno)
WHERE teachers.Tname='張旭';
24.查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名。
SELECT DISTINCT Tname
FROM scores INNER JOIN courses
ON(scores.Cno=courses.Cno) INNER JOIN teachers
ON(courses.Tno=teachers.Tno)
WHERE courses.Cno IN(SELECT Cno FROM scores GROUP BY(Cno) HAVING COUNT(Sno)>5);
25.查詢95033班和95031班全體學(xué)生的記錄。
SELECT *
FROM students
WHERE Class IN ('95033','95031')
ORDER BY Class;
26.查詢存在有85分以上成績的課程Cno.
SELECT DISTINCT Cno
FROM scores
WHERE Degree>85;
27.查詢出“計算機(jī)系“教師所教課程的成績表。
SELECT Tname,Cname,SName,Degree
FROM teachers INNER JOIN courses
ON(teachers.Tno=courses.Tno) INNER JOIN scores
ON(courses.Cno=scores.Cno) INNER JOIN students
ON(scores.Sno=students.Sno)
WHERE teachers.Depart='計算機(jī)系'
ORDER BY Tname,Cname,Degree DESC;
28.查詢“計算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof。
SELECT Tname,Prof
FROM teachers
WHERE Depart='計算機(jī)系' AND Prof NOT IN(
? ? SELECT DISTINCT Prof
? ? FROM teachers
? ? WHERE Depart='電子工程系');
29.查詢選修編號為“3-105“課程且成績至少高于任意選修編號為“3-245”的同學(xué)的成績的Cno、Sno和Degree,并按Degree從高到低次序排序。
SELECT Cno,Sno,Degree
FROM scores
WHERE Cno='3-105' AND Degree > ANY(
? ? SELECT Degree
? ? FROM scores
? ? WHERE Cno='3-245')
ORDER BY Degree DESC;
30.查詢選修編號為“3-105”且成績高于所有選修編號為“3-245”課程的同學(xué)的Cno、Sno和Degree.
SELECT Cno,Sno,Degree
FROM scores
WHERE Cno='3-105' AND Degree > ALL(
? ? SELECT Degree
? ? FROM scores
? ? WHERE Cno='3-245')
ORDER BY Degree DESC;
31.查詢所有教師和同學(xué)的name、sex和birthday.
SELECT Sname,Ssex,Sbirthday
FROM students
UNION
SELECT Tname,Tsex,Tbirthday
FROM teachers;
32.查詢所有“女”教師和“女”同學(xué)的name、sex和birthday.
SELECT Sname,Ssex,Sbirthday
FROM students
WHERE Ssex='女'
UNION
SELECT Tname,Tsex,Tbirthday
FROM teachers
WHERE Tsex='女';
33.查詢成績比該課程平均成績低的同學(xué)的成績表。
SELECT s1.*
FROM scores AS s1 INNER JOIN (
? ? SELECT Cno,AVG(Degree) AS aDegree
? ? FROM scores
? ? GROUP BY Cno) s2
ON(s1.Cno=s2.Cno AND s1.Degree<s2.aDegree);
34.查詢所有任課教師的Tname和Depart.
SELECT Tname,Depart
FROM teachers
WHERE Tno IN(
? ? SELECT Tno
? ? FROM courses
);
35.查詢所有未講課的教師的Tname和Depart.
SELECT Tname,Depart
FROM teachers
WHERE Tno NOT IN(
? ? SELECT Tno
? ? FROM courses
);
36.查詢至少有2名男生的班號。
SELECT Class,COUNT(1) AS boyCount
FROM students
WHERE Ssex='男'
GROUP BY Class
HAVING boyCount>=2;
37.查詢Student表中不姓“王”的同學(xué)記錄。
SELECT *
FROM students
WHERE Sname NOT LIKE '王%';
38.查詢Student表中每個學(xué)生的姓名和年齡。
SELECT Sname,YEAR(NOW())-YEAR(Sbirthday) AS Sage
FROM students;
39.查詢Student表中最大和最小的Sbirthday日期值。
SELECT MIN(Sbirthday),MAX(Sbirthday)
FROM students;
40.以班號和年齡從大到小的順序查詢Student表中的全部記錄。
SELECT *
FROM students
ORDER BY Class DESC,Sbirthday ASC;
41.查詢“男”教師及其所上的課程。
SELECT teachers.Tname,courses.Cname
FROM teachers INNER JOIN courses
ON(teachers.Tno=courses.Tno)
WHERE teachers.Tsex='男';
42.查詢最高分同學(xué)的Sno、Cno和Degree列。
SELECT *
FROM scores
GROUP BY Cno
HAVING Degree=Max(Degree);
43.查詢和“李軍”同性別的所有同學(xué)的Sname.
SELECT s1.Sname
FROM students AS s1 INNER JOIN students AS s2
ON(s1.Ssex=s2.Ssex)
WHERE s2.Sname='李軍';
44.查詢和“李軍”同性別并同班的同學(xué)Sname.
SELECT s1.Sname
FROM students AS s1 INNER JOIN students AS s2
ON(s1.Ssex=s2.Ssex AND s1.Class=s2.Class)
WHERE s2.Sname='李軍';
45.查詢所有選修“計算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表
SELECT *
FROM scores
WHERE Sno IN (
? ? SELECT Sno
? ? FROM students
? ? WHERE Ssex='男') AND
? ? Cno IN (
? ? SELECT Cno
? ? FROM courses
? ? WHERE Cname='計算機(jī)導(dǎo)論');
查詢“3-105”課程比“3-245”課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
SELECT * from
(SELECT sno,cno,degree dd FROM scores WHERE cno='3-105') t1
left join
(SELECT sno,cno,degree dx FROM scores WHERE cno='3-245') t2
ON
t1.sno=t2.sno
WHERE dd>dx
查詢平均成績大于等于60分的同學(xué)的學(xué)生編號和平均成績
SELECT avg(degree),sno from scores GROUP BY sno having avg(degree)>60
查詢和“103”號同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)生編號
用hive實(shí)現(xiàn)，先列轉(zhuǎn)行，將學(xué)生所學(xué)的所有課程按順序拼接起來，然后跟103的比較
查詢沒學(xué)過“張旭”老師講授的任一門課程的學(xué)生編號
SELECT DISTINCT sno from scores where sno not in(
SELECT? sno from scores
where cno in (
SELECT cno from courses C LEFT JOIN teachers T ON C.tno=T.tno WHERE T.tname='張旭'))
查詢所有同學(xué)的學(xué)生編號、選課總數(shù)、所有課程的總成績（沒成績的顯示為null）
SELECT sno,count(cno),sum(degree) from scores GROUP BY sno
查詢各科成績最高分、最低分和平均分
SELECT max(degree),min(degree),avg(degree),cno from scores GROUP BY cno
統(tǒng)計各科成績各分?jǐn)?shù)段人數(shù)：課程編號，【100-85】，【85-70】，【70-60】，【60-0】及所占百分比
SELECT SC.cno as '課程ID'
,sum(CASE WHEN degree BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100 - 85]'
,sum(CASE WHEN degree BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85 - 70]'
,sum(CASE WHEN degree BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70 - 60]'
,sum(CASE WHEN degree < 60 THEN 1 ELSE 0 END) AS '[60 -0]'
FROM scores SC
GROUP BY SC.cno;
查詢各科成績前三名的記錄
用hive窗口函數(shù)實(shí)現(xiàn)
成績有重復(fù)的情況下，查詢“3-105”課程的學(xué)生中，成績最高的學(xué)生信息及其成績
select sno,cno,degree from scores where degree= (select max(degree) from scores where cno='3-105')
