MySQL練習(学生表、カリキュラム、選択カリキュラム)超詳細

25851 ワード

mysql練習
  • 一、テーマ:
  • 二、学生、課程を作成し、授業表を選択し、データ
  • を挿入する.
  • 三、練習問題の答え
  • 環境:win 10システム、MySQLデータベース
    一、テーマ:
        :
    S(Sno,Sname,Sage,Ssex,Sdept)
    C(cno,cname,cpno,ccredit)
    SC(sno,cno,grade)
    (  sno:  ;sname:  ;Ssex:  ;Sdept:  ;Sage :  ;cno:   ;cname:   ;
    cpno:      ;ccredit:  ;grade:  )1、  “CS”        ;
    
    2、  “CS”       19 21        、  ;
    
    3、          ;
    
    4、  “   ”        ,     、  ;
    
    5、         ,       ;
    
    6、             ,       ;
    
    7、     “1” “2”           ;
    
    8、         “   ”    60         、     ;
    
    93108011801280        
    
    13、           ,     、  ;
    

    二、学生の作成、カリキュラム、選択表、データの挿入
    #     :    ,  ,  ,  ,  
    CREATE TABLE S
    (
    Sno VARCHAR(7)PRIMARY KEY,
    Sname VARCHAR(10)NOT NULL,
    Sage INT,
    Ssex VARCHAR(2),
    Sdept VARCHAR(20)DEFAULT('    ')
    );
    
    #     :     ,   ,      ,  
    CREATE TABLE C
    (
    Cno VARCHAR(10)PRIMARY KEY,
    Cname VARCHAR (20)NOT NULL,
    Cpno VARCHAR(10),
    Ccredit INT
    );
    
    #     
    CREATE TABLE SC
    (
    Sno VARCHAR(7),
    Cno VARCHAR(10),
    grade INT,
    FOREIGN KEY (sno) REFERENCES S(Sno),
    FOREIGN KEY (cno) REFERENCES C(cno)
    );
    
    #      S     
    INSERT INTO s
       (Sno,Sname,Sage,Ssex,Sdept)
    VALUE
       ("10001","  ",20,' ','   '),
       ("10002","  ",19,' ','   '),
       ("10003","  ",18,' ','CS'),
       ("10004","  ",21,' ','   '),
       ("10006","  ",18,' ','    '),
       ("10008","  ",21,' ','   '),
       ("10005","  ",22,' ','CS');
    
    #      C        
    INSERT INTO c
       (Cno,Cname,Cpno,Ccredit)
    VALUE
       ("1","    ",NULL,5),
       ("2","    ",'3',6),
       ("3","    ",NULL,4),
       ("4","    ",'3',6),
       ("5","    ",'1',4),
       ("6","   ",'4',5);
    DELETE FROM c; 
    
    #      SC       
    INSERT INTO sc
      (Sno,Cno,grade)
    VALUE
      ("10001","1",70),
      ("10001","6",56),
      ("10003","4",90),
      ("10003","5",83),
      ("10004","1",75),
      ("10004","3",90),
      ("10008","1",70),
      ("10008","5",70),
      ("10008","6",88),
      ("10002","1",85),
      ("10002","6",89);
    

    三、練習問題の答え
    # 1、  “CS”        ;
    SELECT * FROM s WHERE Sdept="CS";
    
    # 2、  “CS”       19 21        、  ;
    SELECT Sno,Sname,Sage,Sdept 
    FROM s 
    WHERE (Sage<19 OR Sage>21) AND Sdept = "CS";
    
    # 3、          ;
    SELECT MAX(Sage) FROM s;
    
    # 4、  “   ”        ,     、  ;
    SELECT Sno, Sname 
    FROM s 
    WHERE Sdept="   " 
    ORDER BY Sage DESC 
    LIMIT 1;
    
    # 5、         ,       ;
    SELECT Sdept,COUNT(*) '  ' 
    FROM s 
    GROUP BY Sdept 
    ORDER BY '  ';
    
    # 6、             ,       ;
    SELECT Sdept,AVG(Sage) '    '
    FROM s 
    GROUP BY Sdept 
    ORDER BY '    'DESC;
    
    # 7、     “1” “2”           ;
    SELECT s.Sno,Sname 
    FROM s JOIN sc ON s.`Sno`=sc.`Sno` 
    WHERE Cno IN ("1","2");
    
    # 8、         “   ”    60         、     ;
    SELECT s.Sno,Sname 
    FROM s JOIN sc ON s.`Sno`=sc.`Sno` 
    JOIN c ON sc.`Cno`=c.`Cno`
    WHERE Cname ="   " AND grade<60;
    
    # 9、     3          ;
    SELECT sno 
    FROM sc 
    GROUP BY sc.Sno 
    HAVING COUNT(*)>=3;
    
    # 10、              80        ;
    SELECT sno
    FROM sc 
    GROUP BY sc.Sno 
    HAVING MAX(grade)>80;
    
    # 11、          80        ;
    SELECT sno 
    FROM sc 
    GROUP BY sc.Sno 
    HAVING MIN(grade)>80;
    
    # 12、           80        
    SELECT sno
    FROM sc 
    GROUP BY sc.Sno 
    HAVING AVG(grade)>80;
    
    # 13、           ,     、  ;
    SELECT sno,Sname
    FROM s JOIN 
    	(SELECT Sdept,MAX(Sage) Sage
    	FROM s
    	GROUP BY Sdept) a
    ON (s.`Sage`=a.Sage AND s.`Sdept` = a.Sdept) ;
    
    , 。