美文网首页mysql优化技术MySQL
[Practice] MySQL查询几题

[Practice] MySQL查询几题

作者: 敲代码的密斯想 | 来源:发表于2017-12-12 14:56 被阅读28次

    题目一

    设教学数据库Education有三个关系:
    学生关系Student (SNO, SNAME, AGE, SEX, SDEPT);
    学习关系SC (SNO, CNO, GRADE);
    课程关系 Curriculum (CNO, CNAME, CDEPT, TNAME)。
    
    
    查询问题:  
    (1) 检索计算机系的所有学生的学号、姓名和性别;  
    (2) 检索学习课程号为2的学生学号和姓名;  
    (3) 检索选修课程名为”Digital”的学生学号和姓名;(三表联合)  
    (4) 检索选修课程号为2或4的学生学号;  
    (5) 检索年龄在17-19岁间的学生信息;  
    (6) 检索每个院系有多少个学生;  
    (7) 检索选修课程号为2的年纪最大的学生;  
    (8) 检索选修每个课程学生的最大年纪;  
    (9) 检索每个学生的总成绩,并从高到低排序;  
    (10) 检索每门课程的平均成绩;  
    (11) 检索java成绩低于2级的学生信息;  
    (12) 检索同时选修课程1和课程2的学生信息;  
    (13) 检索以”j”开头的学生的信息以及所选课程  
    
    首先创建Student、SC以及Curriculum表:
    CREATE table Student (  
    SNO int NOT NULL AUTO_INCREMENT,  
    SNAME VARCHAR(10) NOT NULL,  
    AGE int NOT NULL,  
    SEX VARCHAR(6) NOT NULL,  
    SDEPT VARCHAR(20) NOT NULL,  
    PRIMARY KEY (SNO)
         );  
    
    CREATE table SC (
    SNO int NOT NULL,
    CNO int NOT NULL,
    GRADE int NOT NULL
    );
    
    CREATE table Curriculum(
    CNO int NOT NULL AUTO_INCREMENT,
    CNAME VARCHAR(20) NOT NULL,
    CDEPT VARCHAR(20) NOT NULL,
    TNAME VARCHAR(20) NOT NULL,
    PRIMARY KEY (CNO)
    );
    
    解答(按序号)
    (1) SELECT Student.SNO, Student.SNAME,Student.SEX
    FROM Student
    WHERE SDEPT="CS";
    
    (2) SELECT Student.SNO, Student.SNAME
    FROM Student INNER JOIN SC
    ON Student.SNO=SC.SNO
    WHERE SC.CNO=2;
    
    (3) SELECT Student.SNAME, Student.AGE 
    FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO) 
    INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
    WHERE Curriculum.CNAME="digital";
    
    (4) SELECT Student.SNAME, Student.AGE 
    FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO) 
    INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
    WHERE SC.CNO IN (4,5);
    
    (5) SELECT * FROM Student
    WHERE Student.AGE BETWEEN 17 AND 19;
     
    (6) SELECT SDEPT, COUNT(SNO)
    FROM Student
    GROUP BY SDEPT;
    
    (7) SELECT Student.SNAME, MAX(Student.AGE)
    FROM Student INNER JOIN SC
    ON Student.SNO=SC.SNO
    WHERE SC.CNO=2;
    
    (8) SELECT CNAME, MAX(Student.AGE)
    FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO)
    INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
    GROUP BY CNAME;
    
    (9) SELECT Student.SNO, Student.SNAME, SUM(SC.GRADE)
    FROM Student INNER JOIN SC
    ON Student.SNO=SC.SNO
    GROUP BY SNAME
    ORDER BY GRADE DESC;
    
    (10)    SELECT Curriculum.CNO, Curriculum.CNAME, AVG(SC.GRADE)
    FROM SC INNER JOIN Curriculum
    ON SC.CNO=Curriculum.CNO
    GROUP BY CNAME
    ORDER BY SC.CNO;
    
    (11)    SELECT Student.*
    FROM (Student INNER JOIN SC ON Student.SNO=SC.SNO)
    INNER JOIN Curriculum ON SC.CNO=Curriculum.CNO
    WHERE (CNAME="java" AND GRADE<2);
    
    (12)    SELECT a.SNO
    FROM SC a, SC b
    WHERE a.CNO = 1 and b.CNO = 2;
    
    SELECT Distinct Student.*
    FROM Student INNER JOIN SC
    ON Student.SNO = SC.SNO
    WHERE SC.SNO = (
    SELECT a.SNO from SC a, SC b where a.CNO=1 and b.CNO=2 and a.SNO=b.SNO);
    
    (13)    SELECT Student.*, SC.* 
    FROM Student INNER JOIN SC
    ON Student.SNO=SC.SNO
    WHERE SNAME LIKE "j%";
    

    题目二

    设教学数据库Education_2有四个关系:
    学生关系Student (S, SNAME, SAGE, SSEX);
    学习关系SC (S, C, SCORE);
    课程关系 Course (C, CNAME, T);
    老师关系 Teacher (T, TNAME)。
    
    问题:
    (1) 创建数据库并插入响应数据;
    (2) 检索01课程比02课程成绩高的学生的信息及课程分数;
    (3) 检索平均成绩大于等于60分的同学编号和学生姓名和平均成绩;
    (4) 检索所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩;
    (5) 检索“李”姓老师数量;
    (6) 检索上过“Zhang”老师课的同学信息;
    (7) 检索没上过“Zhang”老师课的同学;
    (8) 检索学过编号1课程并且也学过编号2课程的学生信息
    
    首先创建表
    (1) CREATE table Student (
    S Int NOT NULL AUTO_INCREMENT,
    SNAME Varchar(10) NOT NULL,
    SAGE Int NOT NULL,
    SSEX Varchar(10) NOT NULL,
    PRIMARY KEY (S)
    );
    
    CREATE table Course (
    C Int NOT NULL AUTO_INCREMENT,
    CNAME Varchar(10) NOT NULL,
    T Varchar(10) NOT NULL,
    PRIMARY KEY (C)
    );
    
    CREATE table Teacher (
    T Int NOT NULL AUTO_INCREMENT,
    TNAME Varchar(10) NOT NULL,
    PRIMARY KEY (T)
    );
    
    CREATE table SC (
    S Int NOT NULL,
    C Int NOT NULL,
    SCORE Int NOT NULL
    );
    
    解答正式开始
    (2) SELECT a.*, b.SCORE as “score1”, c.SCORE as “score2”
    FROM Student a
    INNER JOIN SC b
    ON a.S = b.S and b.C=1
    INNER JOIN SC b
    ON a.S = c.S and c.C=2
    WHERE b.SCORE > c.SCORE;
    
    (3) SELECT a.S, a.SNAME, AVG(b.SCORE)
    FROM Student a
    INNER JOIN SC b
    ON a.S = b.S
    GROUP BY a.S
    HAVING AVG (b.SCORE)>60;
    
    (4) SELECT Student.S, Student.SNAME, COUNT(SC.C), AVG(SC.SCORE)
    FROM Student
    INNER JOIN SC
    ON Student.S = SC.S
    GROUP BY SC.S;
    
    (5) SELECT COUNT(T) 
    FROM Teacher
    WHERE Teacher.TNAME LIKE ‘李%’;
    
    (6) SELECT Student.* 
    FROM Student
    INNER JOIN SC
    ON Student.S = SC.S
    INNER JOIN Course
    ON SC.C = Course.C
    INNER JOIN Teacher
    ON Course.T = Teacher.T
    WHERE Teacher.TNAME=”Zhang”;
    
    (7) SELECT Student.*
    FROM Student
    INNER JOIN SC 
    ON Student.S = SC.S
    WHERE NOT EXISTS (
      SELECT * 
      FROM Course
      INNER JOIN Teacher
      ON Course.T = Teacher.T
      INNER JOIN SC
      ON Course.C = SC.C
      WHERE Teacher.TNAME=’Zhang’ and SC.S = Student.S
    );
    
    (8) SELECT DISTINCT Student.*
    FROM Student
    INNER JOIN SC
    ON Student.S = SC.S
    WHERE SC.S = any (
       SELECT a.S FROM SC a, SC b WHERE a.C=1 AND b.C=2 AND a.S=b.S
    );
    
    (9) SELECT DISTINCT Student.*
    FROM Student 
    INNER JOIN SC
    ON Student.S = SC.S
    WHERE SC.S = any (
       SELECT S FROM SC WHERE C = 1 and S NOT IN (
    SELECT S FROM SC WHERE C =2
    )
    );
    

    相关文章

      网友评论

        本文标题:[Practice] MySQL查询几题

        本文链接:https://www.haomeiwen.com/subject/jycnixtx.html