創(chuàng)建下列表并創(chuàng)建相關(guān)約束

問題

1：查詢出成績(jī)表，而且student_id 后面要有對(duì)應(yīng)的學(xué)生名，course_id 后面要有對(duì)應(yīng)的課程名.

1 SELECT2 score.sid,3 score.student_id,4 student.sname,5 score.course_id,6 course.cname,7 score.number8 FROM score9 LEFT JOIN student10 ON student.sid =score.student_id11 LEFT JOIN course12 ON course.cid = score.course_id

參考答案

2：查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)

1 SELECT student_id,SUM(number)/COUNT(student_id) AS avg_number FROM score GROUP BY student_id HAVING avg_number>60;

View Code

3：查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)

1 SELECT student.sid,student.sname,COUNT(score.student_id),SUM(number) FROM student,score WHERE student.sid=score.student_id GROUP BY score.student_id

View Code

4：查詢姓“李”的老師的個(gè)數(shù)

1 SELECT COUNT(*) FROM teacher WHERE tname LIKE "李%";

View Code

5：查詢學(xué)過“李平”老師課的同學(xué)的學(xué)號(hào)、姓名

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id = (SELECT tid FROM teacher WHERE tname='李平老師');

View Code

6：查詢學(xué)過“1”并且也學(xué)過編號(hào)“2”課程的同學(xué)的學(xué)號(hào)、姓名

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id IN (1,2) GROUP BY student.sname HAVING COUNT(*)=2;

View Code

7：查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名

1 SELECT2 sid,3 sname4 FROM student5 WHERE sid NOT IN(SELECT6 student.sid7 FROM score,8 student9 WHERE student.sid =score.student_id10 AND score.course_id IN(SELECT11 tid12 FROM teacher13 WHERE tname = '李平老師'));

View Code

8：查詢有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND number<60 GROUP BY student.sname;

View Code

9：查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名

1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id GROUP BY student.sid HAVING COUNT(*)

View Code

10：查詢至少有一門課與學(xué)號(hào)為“1”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名

1 SELECT *

2 FROM student,3 score4 WHERE student.sid =score.student_id5 GROUP BY student.sid6 HAVING score.course_id IN(SELECT7 score.course_id8 FROM score,9 student10 WHERE student.sid = 1

11 AND student.sid =score.student_id)12 AND student.sid != 1;

View Code

11：查詢和“2”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名

1 SELECT2 student.sid,3 student.sname4 FROM student,5 score6 WHERE student.sid =score.student_id7 GROUP BY student.sid8 HAVING COUNT(student.sid) =(SELECT9 COUNT(*)10 FROM score11 WHERE student_id = 2)12 AND SUM(course_id) =(SELECT13 SUM(course_id)14 FROM score15 WHERE student_id = 2)16 AND student.sid != 2;

View Code

12：查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程ID，最高分，最低分

1 SELECT course_id,MAX(number),MIN(number) FROM score GROUP BY course_id;

View Code

13：查詢每門課程被選修的學(xué)生數(shù)

1 SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;

View Code

14：查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名

1 SELECT student.sid,student.sname FROM score,student WHERE student.sid=score.student_id GROUP BY student_id HAVING COUNT(course_id)=1;

View Code

15：查詢男生、女生的人數(shù)

1 SELECT gender,COUNT(gender) FROM student GROUP BY gender;

View Code

16：查詢姓“張”的學(xué)生名單

1 SELECT sid,sname FROM student WHERE sname LIKE '張%';

View Code

17：查詢同名同姓學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)

1 SELECT sid,sname,COUNT(sname) FROM student GROUP BY sname HAVING COUNT(sname)>1;

View Code

18：查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列

1 SELECT course_id,AVG(number) FROM score GROUP BY course_id ORDER BY AVG(number) ASC ,course_id DESC;

View Code

19：查詢平均成績(jī)大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)

1 SELECT student.sid,student.sname,AVG(number) FROM score,student WHERE student.sid=score.student_id GROUP BY score.student_id HAVING AVG(number)>85;

View Code

20：查詢課程名稱為“生物”，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)

1 SELECT2 student.sid,3 student.sname,4 score.number5 FROM score,6 student7 WHERE student.sid =score.student_id8 AND score.course_id =(SELECT9 cid10 FROM course11 WHERE cname = "生物")12 AND score.number < 60;

View Code

21：查詢課程編號(hào)為3且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名

1 SELECT student.sid,student.sname,score.number FROM score,student WHERE student.sid=score.student_id AND score.course_id=3 AND number>80;

View Code

22：求選了課程的學(xué)生人數(shù)

1 SELECT COUNT(*) FROM (SELECT * FROM score GROUP BY student_id) AS e;

View Code

23：查詢選修“劉海燕”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)

1 SELECT student.sid,student.sname,MAX(number) FROM score,student WHERE student.sid=score.student_id AND score.course_id=(SELECT tid FROM teacher WHERE tname='劉海燕老師')

View Code

24：查詢不同課程但成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)*****

1 SELECT * FROM score AS sc WHERE EXISTS(SELECT 1 FROM score WHERE number=sc.number AND course_id<>sc.course_id) ORDER BY number DESC;

View Code

25：查詢至少選修兩門課程的學(xué)生學(xué)號(hào)

1 SELECT student_id FROM score GROUP BY student_id HAVING COUNT(student_id)>=2;

View Code

26：查詢“生物”課程比“物理”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)

1 SELECT2 a.student_id3 FROM (SELECT *

4 FROM score5 WHERE course_id =(SELECT6 cid7 FROM course8 WHERE cname = '生物')) AS a,9 (SELECT *

10 FROM score11 WHERE course_id =(SELECT12 cid13 FROM course14 WHERE cname = '物理')) AS b15 WHERE a.student_id =b.student_id16 and a.number > b.number;

View Code

創(chuàng)建表和數(shù)據(jù)

1 /*

2 Navicat Premium Data Transfer3

4 Source Server : localhost5 Source Server Type : MySQL6 Source Server Version : 50624

7 Source Host : localhost8 Source Database : sqlexam9

10 Target Server Type : MySQL11 Target Server Version : 50624

12 File Encoding : utf-8

13

14 Date: 10/21/2016 06:46:46AM15 */

16

17 SET NAMES utf8;18 SET FOREIGN_KEY_CHECKS =0;19

20 -- ----------------------------

21 -- Table structure for `class`22 -- ----------------------------

23 DROP TABLE IF EXISTS `class`;24 CREATE TABLE `class` (25 `cid` int(11) NOT NULL AUTO_INCREMENT,26 `caption` varchar(32) NOT NULL,27 PRIMARY KEY (`cid`)28 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;29

30 -- ----------------------------

31 -- Records of `class`32 -- ----------------------------

33 BEGIN;34 INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');35 COMMIT;36

37 -- ----------------------------

38 -- Table structure for`course`39 -- ----------------------------

40 DROP TABLE IF EXISTS `course`;41 CREATE TABLE `course` (42 `cid` int(11) NOT NULL AUTO_INCREMENT,43 `cname` varchar(32) NOT NULL,44 `teacher_id` int(11) NOT NULL,45 PRIMARY KEY (`cid`),46 KEY `fk_course_teacher` (`teacher_id`),47 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)48 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;49

50 -- ----------------------------

51 --Records of `course`52 -- ----------------------------

53 BEGIN;54 INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '體育', '3'), ('4', '美術(shù)', '2');55 COMMIT;56

57 -- ----------------------------

58 -- Table structure for`score`59 -- ----------------------------

60 DROP TABLE IF EXISTS `score`;61 CREATE TABLE `score` (62 `sid` int(11) NOT NULL AUTO_INCREMENT,63 `student_id` int(11) NOT NULL,64 `course_id` int(11) NOT NULL,65 `num` int(11) NOT NULL,66 PRIMARY KEY (`sid`),67 KEY `fk_score_student` (`student_id`),68 KEY `fk_score_course` (`course_id`),69 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),70 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)71 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;72

73 -- ----------------------------

74 --Records of `score`75 -- ----------------------------

76 BEGIN;77 INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');78 COMMIT;79

80 -- ----------------------------

81 -- Table structure for`student`82 -- ----------------------------

83 DROP TABLE IF EXISTS `student`;84 CREATE TABLE `student` (85 `sid` int(11) NOT NULL AUTO_INCREMENT,86 `gender` char(1) NOT NULL,87 `class_id` int(11) NOT NULL,88 `sname` varchar(32) NOT NULL,89 PRIMARY KEY (`sid`),90 KEY `fk_class` (`class_id`),91 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)92 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;93

94 -- ----------------------------

95 --Records of `student`96 -- ----------------------------

97 BEGIN;98 INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四');99 COMMIT;100

101 -- ----------------------------

102 -- Table structure for`teacher`103 -- ----------------------------

104 DROP TABLE IF EXISTS `teacher`;105 CREATE TABLE `teacher` (106 `tid` int(11) NOT NULL AUTO_INCREMENT,107 `tname` varchar(32) NOT NULL,108 PRIMARY KEY (`tid`)109 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;110

111 -- ----------------------------

112 --Records of `teacher`113 -- ----------------------------

114 BEGIN;115 INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱云海老師'), ('5', '李杰老師');116 COMMIT;117

118 SET FOREIGN_KEY_CHECKS = 1;

View Code

總結(jié)

以上是生活随笔為你收集整理的mysql题目_MySQL练习题的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。