MyException - 我的异常网
当前位置:我的异常网» MySQL » mysql 练习题

mysql 练习题

www.MyException.Cn  网友分享于:2013-12-25  浏览:180次
mysql 练习
USE gg;
/*
CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5)) COLLATE='utf8_bin' ENGINE=InnoDB;

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL) COLLATE='utf8_bin' ENGINE=InnoDB;

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL) COLLATE='utf8_bin' ENGINE=InnoDB;

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) COLLATE='utf8_bin' ENGINE=InnoDB;

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','助教','电子工程系');
*/

/*
-- 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT st.Sname,st.Ssex,st.Class
FROM STUDENT st;
--  查询教师所有的单位即不重复的Depart列。
SELECT  DISTINCT tt.DEPART
FROM TEACHER tt ;
-- 查询Student表的所有记录。
SELECT st.*
FROM STUDENT st;
-- 查询Score表中成绩在60到80之间的所有记录。
SELECT sc.*
FROM SCORE sc
WHERE sc.DEGREE >= 60 AND sc.DEGREE <=80 ORDER BY sc.SNO desc;

--  查询Score表中成绩为85,86或88的记录。
SELECT sc.*
FROM SCORE sc
WHERE sc.DEGREE in (85,86,88) ORDER BY sc.SNO desc;

-- 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT st.*
FROM STUDENT st
WHERE st.CLASS = '95031' OR st.SSEX ='女'
ORDER BY st.SNO;

--  以Class降序查询Student表的所有记录。
SELECT st.*
FROM STUDENT st
ORDER BY st.CLASS DESC


-- 以Cno升序、Degree降序查询Score表的所有记录。
SELECT sc.*
FROM SCORE sc
ORDER BY sc.CNO ,sc.DEGREE DESC;


-- 查询“95031”班的学生人数。
SELECT COUNT(*)
FROM STUDENT st
WHERE st.CLASS='95031';


-- 查询Score表中的最高分的学生学号和课程号。
SELECT sc.SNO,sc.CNO
FROM SCORE sc
WHERE sc.DEGREE =
(
SELECT MAX(sc.DEGREE)
FROM SCORE sc
)
ORDER BY sc.SNO;


*/

-- 查询‘3-105’号课程的平均分。
SELECT AVG(sc.DEGREE)
FROM SCORE sc
WHERE sc.CNO='3_105'

*/


/*
-- 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT AVG(sc.DEGREE)
FROM SCORE sc
WHERE sc.CNO LIKE '3%' AND sc.CNO IN (
SELECT T.CNO
FROM (
SELECT COUNT(*) AS bb, sc.CNO
FROM SCORE sc
GROUP BY sc.CNO) T
WHERE T.bb >= 5)
GROUP BY sc.CNO
ORDER BY sc.CNO;



SELECT AVG(sc.DEGREE)
FROM SCORE sc
WHERE sc.CNO LIKE '3%' AND sc.CNO = (
SELECT T.CNO
FROM (
SELECT COUNT(*) AS BB,sc.CNO AS CNO
FROM SCORE sc
GROUP BY sc.CNO) T
WHERE T.BB >=5);



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

*/

/*
-- 查询最低分大于70,最高分小于90的Sno列。
SELECT sc.SNO,sc.DEGREE
FROM SCORE sc
WHERE sc.DEGREE >70 AND sc.DEGREE < 90;


-- 查询所有学生的Sname、Cno和Degree列。
SELECT st.SNAME,sc.CNO,sc.DEGREE
FROM STUDENT st,SCORE sc
WHERE st.SNO=sc.SNO;

-- 查询所有学生的Sno、Cname和Degree列。
SELECT DISTINCT st.SNO,co.CNAME,sc.DEGREE
FROM STUDENT st,SCORE sc,COURSE co
WHERE st.SNO=sc.SNO AND sc.CNO= co.CNO
ORDER BY st.SNO

-- 查询所有学生的Sname、Cname和Degree列。
SELECT DISTINCT st.SNAME,co.CNAME,sc.DEGREE
FROM STUDENT st,COURSE co,SCORE sc
WHERE st.SNO=sc.SNO AND sc.CNO=co.CNO
ORDER BY st.SNAME;


-- 查询“95033”班所选课程的平均分。
SELECT AVG(sc.DEGREE)
FROM STUDENT st,SCORE sc
WHERE st.SNO=sc.SNO AND st.CLASS='95033';

-- 假设使用如下命令建立了一个grade表
create table grade(low   NUMERIC(10,0),upp   NUMERIC(10,0) ,rank   varchar(1) ) COLLATE='utf8_bin' ENGINE=INNODB;
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;


-- 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT DISTINCT st.*
FROM STUDENT st,SCORE sc
WHERE sc.CNO='3_105' AND sc.DEGREE > (
SELECT sc.DEGREE
FROM SCORE sc
WHERE sc.SNO='109' AND sc.CNO='3_105'
);
*/

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

SELECT sc.*
FROM SCORE sc
WHERE sc.DEGREE < (SELECT MAX(sc.DEGREE) FROM SCORE sc)
GROUP by sc.CNO
HAVING COUNT(sc.SNO) >1;

-- 好像不对
*/

/*
-- 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT sc.*
FROM SCORE sc
WHERE sc.CNO='3_105' AND sc.DEGREE >(
SELECT sc.DEGREE
FROM SCORE sc
WHERE sc.CNO='3_105' AND sc.SNO='109');

*/

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

set @birthday=(SELECT st.SBIRTHDAY FROM STUDENT st WHERE st.SNO='108');
SELECT st.SNO,st.SNAME,st.SBIRTHDAY
FROM STUDENT st
WHERE YEAR(st.SBIRTHDAY) = YEAR(@birthday);


-- 写法2
SELECT st.*
FROM STUDENT st
WHERE YEAR(st.SBIRTHDAY)=(SELECT YEAR(t.SBIRTHDAY) FROM STUDENT t WHERE t.SNO='108');
*/

/*
-- 查询“张旭“教师任课的学生成绩。

SELECT sc.*
FROM TEACHER te,SCORE sc,COURSE co
WHERE te.TNAME='张旭' AND te.TNO=co.TNO AND co.CNO=sc.CNO;
*/

/*
-- 查询选修某课程的同学人数多于5人的教师姓名。
SELECT DISTINCT te.TNAME
FROM TEACHER te,SCORE sc,COURSE co
WHERE te.TNO=co.TNO AND co.CNO=sc.CNO AND sc.CNO IN (
SELECT t.CNO
FROM (
SELECT COUNT(*) bb, sc.CNO
FROM SCORE sc
GROUP BY sc.CNO) T
WHERE T.bb >5);


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

*/
/*
-- 查询95033班和95031班全体学生的记录。
SELECT DISTINCT st.*
FROM STUDENT st
WHERE  st.CLASS='95033' OR st.CLASS='95031'
ORDER BY st.SNO;
*/

/*
-- 查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT sc.CNO
FROM SCORE sc
WHERE sc.DEGREE >85
ORDER BY sc.CNO;


SELECT sc.CNO
FROM SCORE sc
GROUP BY sc.CNO
HAVING MAX(sc.DEGREE)>85;

*/
/*
-- 查询出“计算机系“教师所教课程的成绩表。
SELECT sc.*
FROM SCORE sc,COURSE co,TEACHER te
WHERE te.DEPART='计算机系' AND te.TNO=co.TNO AND sc.CNO=co.CNO
ORDER BY sc.SNO;

SELECT sc.*
FROM SCORE sc JOIN (COURSE co,TEACHER te) ON co.TNO=te.TNO AND sc.CNO=co.CNO AND te.DEPART='计算机系'
ORDER BY sc.SNO;
*/

/*
-- 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT te.TNAME,te.PROF
FROM TEACHER te
WHERE te.DEPART='计算机系' AND te.PROF NOT IN (SELECT t.PROF FROM TEACHER t WHERE t.DEPART='电子工程系')
GROUP BY te.PROF;

*/

/*
-- 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT sc.CNO,sc.SNO,sc.DEGREE
FROM SCORE sc
WHERE sc.CNO='3_105' AND sc.DEGREE > (SELECT MAX(sc.DEGREE) FROM SCORE sc WHERE sc.CNO='3_245')
ORDER BY sc.DEGREE DESC;


-- 查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT sc.CNO,sc.SNO,sc.DEGREE
FROM SCORE sc
WHERE sc.CNO='3_105' AND sc.DEGREE > (SELECT MAX(sc.DEGREE) FROM SCORE sc WHERE sc.CNO='3_245');

-- 查询所有教师和同学的name、sex和birthday.
SELECT st.SNAME name,st.SSEX,st.SBIRTHDAY
FROM STUDENT st UNION ALL
SELECT te.TNAME,te.TSEX,te.TBIRTHDAY
FROM TEACHER te;
*/

/*
-- 查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT st.SNAME name,st.SSEX,st.SBIRTHDAY
FROM STUDENT st
WHERE st.SSEX='女' UNION ALL
SELECT te.TNAME,te.TSEX,te.TBIRTHDAY
FROM TEACHER te
WHERE te.TSEX='女';

*/

/*
-- 查询成绩比该课程平均成绩低的同学的成绩表。
--  写法1,查询每一科的平均分,再 根据要查询科目去取平局分比较
SELECT DISTINCT sc.*
FROM SCORE sc
WHERE sc.DEGREE < (SELECT T.bb FROM (SELECT AVG(DEGREE) bb,CNO FROM SCORE GROUP BY CNO) T WHERE T.CNO=sc.CNO)
ORDER BY CNO;

-- 写法2
SELECT DISTINCT sc.*
FROM SCORE sc
WHERE sc.DEGREE <( SELECT AVG(ss.DEGREE) FROM SCORE ss WHERE ss.CNO=sc.CNO )
ORDER BY sc.CNO;

*/
/*
-- 查询所有任课教师的Tname和Depart.
SELECT te.TNAME,te.DEPART
FROM TEACHER te,COURSE co
WHERE te.TNO=co.TNO;

SELECT te.TNAME,te.DEPART
FROM TEACHER te join COURSE USING(TNO);
*/

/*
-- 查询所有未讲课的教师的Tname和Depart.
SELECT te.TNAME,te.DEPART
FROM TEACHER te
WHERE  NOT EXISTS (SELECT sc.TNO FROM COURSE sc WHERE sc.TNO=te.TNO);
*/



/*
-- 查询至少有2名男生的班号。
-- 写法1
SELECT T.CLASS
FROM (
SELECT COUNT(*) BB,st.CLASS
FROM STUDENT st
WHERE st.SSEX='男'
GROUP BY st.CLASS) T
WHERE T.BB>=2;

-- 写法2
SELECT st.CLASS
FROM STUDENT st
WHERE st.SSEX='男'
GROUP BY st.CLASS
HAVING COUNT(st.SSEX) >1;
*/

/*
-- 查询Student表中不姓“王”的同学记录。
SELECT st.*
FROM STUDENT st
WHERE st.SNAME NOT LIKE '王%';

*/

/*
-- 查询Student表中每个学生的姓名和年龄。
-- 这条计算的准些
SELECT st.SNAME, st.SBIRTHDAY, ROUND(DATEDIFF(NOW(),st.SBIRTHDAY)/365) AGE
FROM STUDENT st;

-- 这条只做年的校验
SELECT SNAME,(YEAR(NOW())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT;

*/

/*
-- 查询Student表中最大和最小的Sbirthday日期值。
SELECT MIN(st.SBIRTHDAY) a, MAX(st.SBIRTHDAY) b
FROM STUDENT st;

-- 以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT st.*
FROM STUDENT st
ORDER BY st.CLASS DESC,st.SBIRTHDAY ASC;
*/

/*
-- 查询“男”教师及其所上的课程。
SELECT co.CNAME,te.TNAME
FROM COURSE co,TEACHER te
WHERE te.TSEX='男' AND te.TNO=co.TNO;
-- 写法2
SELECT A.TNAME,B.CNAME FROM TEACHER A JOIN COURSE B USING(TNO) WHERE A.TSEX='男';
*/

/*
-- 查询最高分同学的Sno、Cno和Degree列。
SELECT sc.SNO,sc.CNO,sc.DEGREE
FROM SCORE sc
WHERE sc.DEGREE = (SELECT MAX(sc.DEGREE) FROM SCORE sc);

-- 查询和“李军”同性别的所有同学的Sname
SELECT st.SNAME
FROM STUDENT st
WHERE st.SSEX =(SELECT st.SSEX FROM STUDENT st WHERE st.SNAME='李军');

-- 查询和“李军”同性别并同班的同学Sname.
SELECT st.SNAME
FROM STUDENT st
WHERE st.SSEX =(SELECT st.SSEX FROM STUDENT st WHERE st.SNAME='李军') AND st.CLASS=(SELECT st.CLASS FROM STUDENT st WHERE st.SNAME='李军');

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

SELECT sc.*
FROM SCORE sc,STUDENT st,COURSE co
WHERE sc.SNO=st.SNO AND st.SSEX='男' AND co.CNO=sc.CNO AND co.CNAME='计算机导论'
ORDER BY sc.SNO;

-- 写法2
SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) USING(sno,CNO) WHERE B.SSEX='男' AND C.CNAME='计算机导论';
*/

文章评论

旅行,写作,编程
旅行,写作,编程
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
我的丈夫是个程序员
我的丈夫是个程序员
一个程序员的时间管理
一个程序员的时间管理
10个调试和排错的小建议
10个调试和排错的小建议
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
为什么程序员都是夜猫子
为什么程序员都是夜猫子
每天工作4小时的程序员
每天工作4小时的程序员
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
如何成为一名黑客
如何成为一名黑客
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员都该阅读的书
程序员都该阅读的书
代码女神横空出世
代码女神横空出世
鲜为人知的编程真相
鲜为人知的编程真相
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
中美印日四国程序员比较
中美印日四国程序员比较
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
老程序员的下场
老程序员的下场
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员的鄙视链
程序员的鄙视链
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
总结2014中国互联网十大段子
总结2014中国互联网十大段子
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
那些争议最大的编程观点
那些争议最大的编程观点
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
程序员必看的十大电影
程序员必看的十大电影
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
漫画:程序员的工作
漫画:程序员的工作
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
程序员应该关注的一些事儿
程序员应该关注的一些事儿
我是如何打败拖延症的
我是如何打败拖延症的
 程序员的样子
程序员的样子
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
程序员和编码员之间的区别
程序员和编码员之间的区别
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有