学习笔记

SQL练习

SQL经典练习题50题

–创建数据表

CREATE TABLE STUDENT
   (SNO VARCHAR(3) NOT NULL, 
   SNAME VARCHAR(4) NOT NULL,
   SSEX VARCHAR(2) NOT NULL, 
   SBIRTHDAY DATETIME,
   CLASS VARCHAR(5))

  CREATE TABLE COURSE
    (CNO VARCHAR(5) NOT NULL, 
    CNAME VARCHAR(10) NOT NULL, 
    TNO VARCHAR(10) NOT NULL)

  CREATE TABLE SCORE 
    (SNO VARCHAR(3) NOT NULL, 
    CNO VARCHAR(5) NOT NULL, 
    DEGREE NUMERIC(10, 1) NOT NULL) 

    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 STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' 
    ,'男' ,1977-09-01,95033);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' 
    ,'男' ,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-20,95033);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' 
    ,'女' ,1975-02-10,95031);
    INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' 
    ,'男' ,1974-06-03,95031);

  INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
    INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);

  INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
    INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

–基础查询

  SELECT *FROM STUDENT;
    SELECT *FROM COURSE;
    SELECT *FROM SCORE;
    SELECT *FROM TEACHER;

–查询

 SELECT Sname,Ssex,Class
    FROM   STUDENT;

   SELECT Depart DISTICT 
    FROM TEACHER;
    SELECT * FROM STUDENT;

SELECT DEGREE
FROM SCORE
WHERE DEGREE=85
AND  DEGREE=86
AND DEGREE=88;

–多条件查询

   SELECT SSEX,CLASS
		FROM STUDENT
	WHERE CLASS='95031'
	AND SSEX='女';

–升序与降序查询

SELECT *
FROM SCORE
ORDER BY CNO,DEGREE DESC;

–查询STUDENT表中95301班的学生人数

SELECT COUNT(CLASS) 
	FROM STUDENT
	WHERE CLASS='95031';

–查询3-105班级课程平均分

SELECT AVG(DEGREE)
	FROM SCORE
	WHERE CNO='3-105';

–查询最高分的学生学号与课程号(嵌套查询)

select SNO,CNO
	FROM SCORE
	where DEGREE=(SELECT MAX(DEGREE) FROM SCORE); 

–查询Score 表中至少有5名学生选修的并以3开头的课程平均分数

SELECT AVG(DEGREE),CNO
	FROM SCORE
	WHERE CNO LIKE '3%'
	GROUP BY CNO
	HAVING COUNT(CNO)>=5; 

–查询最低分大于70，最高分小于90的sno列

SELECT  sno,DEGREE
	from SCORE
	where DEGREE>=70
	AND DEGREE<=90;

–查询所有学生的sname,cno,degree

SELECT SNAME,CNO,DEGREE
		FROM STUDENT,SCORE
		where STUDENT.SNO=SCORE.SNO;

–查询所有学生的sno,Cname,DEGREE(多表联结)–and 限定多个表联结

SELECT SNAME,STUDENT.SNO,CNAME,DEGREE
		FROM STUDENT,COURSE,SCORE
		WHERE STUDENT.SNO=SCORE.SNO
		AND  COURSE.CNO=SCORE.CNO; 

–查询所有学生的Sname,Cname,DEGREE

SELECT Sname,Cname,DEGREE
	FROM STUDENT,COURSE,SCORE
	WHERE STUDENT.SNO=SCORE.SNO
	AND SCORE.CNO=COURSE.CNO

–查询“95033班级所选课程的平均分”

SELECT AVG(DEGREE) AS avg_degree
	FROM STUDENT,COURSE,SCORE
	WHERE STUDENT.SNO=SCORE.SNO
	AND SCORE.CNO=COURSE.CNO
	AND CLASS='95033';

–建立表grade

create table grade(low int not null,
           upp   int  ,
		   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');

select *FROM grade;

–查询所有同学的Sno,CNO,rank,(BETWEEN-AND -条件判断，是否大于小于)

SELECT SNO,CNO,RANK
FROM SCORE,grade
WHERE DEGREE BETWEEN low AND upp;

–查询选修3-105课程的成绩高于109号同学成绩的所有同学的记录（查询无结果）

SELECT CNO,DEGREE
	FROM SCORE
	WHERE CNO='3-105'
	AND DEGREE>109;

–查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

–(无查询结果）

SELECT * FROM SCORE
WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE)
GROUP BY SNO,CNO,DEGREE
HAVING COUNT(SNO)>1
ORDER BY DEGREE ;

–查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT SNO,SNAME,Sbirthday
FROM STUDENT
WHERE SBIRTHDAY=‘1905-05-22 00:00:00.000’

–查询“张旭“教师任课的学生成绩。

SELECT  SCORE.CNO,DEGREE,TEACHER.TNAME
	FROM TEACHER,COURSE,SCORE
	WHERE TEACHER.TNO='856'
	AND TEACHER.TNO=COURSE.TNO
	AND  COURSE.CNO=SCORE.CNO;

–查询选修某课程的同学人数多于5人的教师姓名。(逻辑顺序where，对查询结果进行GROUP BY分组）
–用HAVING COUNT 聚合计数判断是否大于五，输出结果为学生人数大于5的教师姓名

SELECT TNAME
	FROM COURSE,SCORE,TEACHER
	WHERE COURSE.TNO=TEACHER.TNO
	AND  SCORE.CNO=COURSE.CNO
	GROUP BY TNAME
	HAVING COUNT(SCORE.CNO)>5;

–查询95033班和95031班全体学生的记录

SELECT * FROM STUDENT
	GROUP BY CLASS,STUDENT.SNO,STUDENT.SBIRTHDAY,SNAME,STUDENT.SSEX;

–查询存在有85分以上成绩的课程Cno

SELECT CNO
	FROM SCORE
	WHERE DEGREE>85
	GROUP BY CNO;

–查询出“计算机系“教师所教课程的成绩表。

SELECT DEGREE
	FROM COURSE,SCORE,TEACHER
	WHERE TEACHER.DEPART='计算机系'
	AND TEACHER.TNO=COURSE.TNO
	AND COURSE.CNO=SCORE.CNO;

–查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT DEPART,Tname,Prof
	FROM TEACHER
	GROUP BY DEPART,TNAME,PROF;

–查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,
–并按Degree从高到低次序排序。

SELECT CNO,SNO,DEGREE
	FROM SCORE
	WHERE CNO='3-105'
	AND   DEGREE>(SELECT MAX(DEGREE) FROM SCORE WHERE SCORE.CNO='3-245')
	ORDER BY DEGREE DESC;

–查询所有教师和同学的name、sex和birthday.(UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
–请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。)
–查询两个表中相同的数据列，输出为一个表，用union连接两个或多个表。

SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT WHERE SSEX='女'
	UNION
	SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER WHERE TSEX='女';

–查询成绩比该课程平均成绩低的同学的成绩表。
–SELECT DEGREE
–FROM SCORE
–WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE WHERE )
–A. B.的作用

SELECT A.* FROM SCORE A WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO);

–查询至少有2名男生的班号。

SELECT CLASS
	FROM STUDENT
	WHERE SSEX='男'
	GROUP BY CLASS
	HAVING COUNT(SSEX)>1;

–查询Student表中不姓“王”的同学记录。

SELECT * FROM STUDENT WHERE NOT SNAME LIKE ‘王%’;

–查询Student表中每个学生的姓名和年龄。
–获得当前日期函数为GETDATE(),YEAR返回年为int类数据，在Mysql中当前时间函数为NOW()

SELECT SNAME,(YEAR(GETDATE())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT;

–查询Student表中最大和最小的Sbirthday日期值。

SELECT MAX(SBIRTHDAY) AS SBIRTHDAY_MIN,MIN(SBIRTHDAY) AS SBIRTHDAY_MAX
	FROM STUDENT;

–以班号和年龄从大到小的顺序查询Student表中的全部记录

SELECT * FROM STUDENT
ORDER BY CLASS DESC,SBIRTHDAY;

–查询“男”教师及其所上的课程。

SELECT TNAME,CNAME
FROM TEACHER,COURSE
WHERE TSEX='男'
AND TEACHER.TNO=COURSE.TNO;

–查询最高分同学的Sno、Cno和Degree列。
–在嵌套查询时是degree=（查询条件）

SELECT SNO,CNO,DEGREE
	FROM SCORE
	WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);

–查询和“李军”同性别的所有同学的Sname.

SELECT SNAME
	FROM STUDENT
WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='李军');

–查询和“李军”同性别并同班的同学Sname.

SELECT SNAME
FROM STUDENT
WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='李军')
AND CLASS=(SELECT CLASS FROM STUDENT WHERE SNAME='李军');

–查询所有选修“计算机导论”课程的“男”同学的成绩表

SELECT DEGREE,SNAME
	FROM SCORE,COURSE,STUDENT
	WHERE COURSE.CNO=SCORE.CNO
	AND STUDENT.SNO=SCORE.SNO
	AND CNAME='计算机导论'
	AND STUDENT.SSEX='男';

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00);

SELECT * FROM employee;

–根据部门分组，显示每个部门的工资等级
–为排序后的结果进行编号

SELECT *,row_number() OVER (PARTITION BY deptid ORDER BY salary desc) rank FROM employee;