2018-11-データベース / 2018-11 database

Transcript

1. 
   
      
   

2. 
   

3. 
   
    !
     
   
   !
    
   

   

4. 
    
   %! 
   $'" 
   #( & 

   
   
    ()

5.    
   
   
   EL C l

   X
   l C
   N D P l AQ S C I I
   I AQ

6. 
   
   

7. 
   
   
   &
   %# ( "'"$  !
     '

8. 
   
     
   l l l
   

   l l / l /

9. 
   
     
    !%0
   
    ! %0 

   l 
    75) +42 l 
    # 1-&( l ' *3 
    $,6. /   
    "

10. 
    
     

11. 
    
     
    A C
    C l

    l l D I

12.   
    
      l  
    

       

13. 23 23 23 
    
    
    
    23 23 23 
    
    
    
    51 .

    0 4

14. 0 0 0 0 0 0 2.34 1 10

15. !
    = A l ( ) A )

16.  
    
    1 B2 A B l 5 B

    l 9 0 l 9 l 9 9 5 93

17.  
     9 541 54 0 12 9 541 54

    . 13 9 = 0 6 2 3 53 54 0 1 2

18.  
    
    = l ) ) ) ) l

    ( I
    

19. -
    
       l ) ) ) l

    
        l
      l ) (
      l ( l (

20. 
    
    
    V PfdcK S
    l CD B .)

    ,B 4 .) B B l 4G 4 OR G .) aMN
    , 4D D C l eQ , 4 Rb , 4D D C aMN L
    B D C l
    ( G D B l C 4 4 GB 4 C
    4 D D B l 4

21. )( 
      3 4 1 2 
    
    

    
     - 0 7

22. )(
    2 l 0 7
    - 1 l

    0
    43 l 2  
         

23. 
    
    :- 3 0 1 l 47 2 0

    
    D
    D
    
      I

24. 
    
    
    n l o D1B l l a
    

    o 
     ( ) ( B R a R i e R B 1

25. )( (

26. 
    
    C
    ) ( ) ) l l

    L
    ) ) ) (/ ( l D
    (/ ( l

27. 
    
    E C l E C L , ( R

    l E C PS ), R l E C IN R l E C ( R
    E C D
       : A :

28. 
      
    0/. -73394 l e atQ0/.

    qL QF d
    S c i l 6 ## 9 73394 2 :# # 1 l k 0/. s Q q      
     p ! ! ! ! n o ! !  
       
        fhm 7 4l belong_to organization employee

29.  
    
    

30. -
         
     l

        
      SELECT [] FROM [ ]; SELECT name FROM employee;

31. -
    *
    l * SELECT * FROM employee;

32. -
    
    l SELECT [ 21 ] FROM [

    0 ] WHERE [
    ]; SELECT * FROM employee WHERE joined_at >= '2010/1/1';

33. -
    ] a l [ _
    d 1 ./

    5 75 .7 :/05 6 /6 / :/ 276 1 5 
     joined_at > '2007/1/1' 726/. _ ' name = '
    ' 6 5/ _ c e id <> 10001 2. _

34. -
    iNO f 
     ( rL ) rL !=

    ds ) nI K )( ( c , tvap . ul moh q eT ./ . ul moh q eT E6 < = > 5 : : 5 : : 6

35. 
    name LIKE '%
    %';
    %
    ' % l l

    l l
    l l

36. 
    ) )
    ( %
    l name LIKE

    '%¥%%'
    ( '

37. -
     l id IN (10001, 10003) l  
    
    
    
    
    
    

     

38. -
    41 l l
    l 0 2
    

    0 SELECT * FROM employee WHERE joined_at >= '2010/1/1' AND joined_at <= '2014/1/1';

39. 
    
    
    1 1
    

40. -
    D E l E ) E l AE E

    
    l C C( E D l SELECT [ ] FROM [ ] ORDER BY [  
     ] [ASC/DESC]; SELECT * FROM employee ORDER BY joined_at ASC;

41. 
    
    l l 2 SELECT [ ] FROM [

    ORDER BY [ ] [ASC/DESC] LIMIT [
    ]; SELECT * FROM employee ORDER BY joined_at ASC LIMIT 2; SELECT * FROM employee ORDER BY joined_at ASC LIMIT 1, 2;

42. 
      #$ %
      
    ) l "

    ! SELECT [&'] FROM [(] GROUP BY [
    ]; SELECT organization_id, COUNT(*) FROM belong_to GROUP BY organization_id;

43. ) * ( * ) * ( * ) *

    ( * 3 4 12 GROUP BY org_id SELECT org_id, COUNT(*) 0 0 2 3 4 1 ) *

44. 
    l 
     ) ( )

45. 
    15 0 I a D l 678 047 2

    a l 3 66 2 l 506837 d_
    9 : l _ l g d l ed l ced 
     
       grade

46. SELECT class_id, COUNT(*), AVG(result), MAX(result), MIN(result) FROM grade GROUP BY

    class_id;

47. 
    B O RO l U H E l G

    P

48. 
    
    l SELECT * FROM [ ] GROUP BY

    [ 2 ] HAVING [
    ]; SELECT organization_id, COUNT(*) AS num FROM belong_to GROUP BY organization_id HAVING num >= 2;

49.  
     
      
     
      
    

    
     GROUP BY org_id SELECT org_id, COUNT(*) 0 1 3  
        
       HAVING num >= 2 2 4

50. 
    
    
    " $ !  
    #%
      

     

51. - 
      3 4 1 2 
    
    

    
     - 0 7   - 0 3 - 0 4 7 1 2

52. - SELECT organization.name, employee.name FROM belong_to INNER JOIN organization ON

    belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;

53. -  
       
     
    
    
    
     
    

    
    
    
      
    
    
     belong_to    
        organization  
      "  
        $%"!# … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …

54. - . . . 
    
    
    
     
     
    
    
    
    

    
      
    
    
        … belong_to INNER JOIN organization ON belong_to.organization_id = organization.id …

55. - . . . 143 2 0 … belong_to INNER

    JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;

56. -   
       
       

       
      SELECT organization.name, employee.name FROM belong_to INNER JOIN organization ON belong_to.organization_id = organization.id INNER JOIN employee ON belong_to.employee_id = employee.id;

57. 
     

58. -
    ( 
     "!&%$ ' %#&   l "!&%#&

      l "!&%$ ' %#& SELECT MAX(result) FROM grade; SELECT AVG(result) FROM grade WHERE result <> [
    ];

59. -
    1 SELECT AVG(result) FROM grade WHERE result <>

    (SELECT MAX(result) FROM grade);

60. -
    > 
     l < l < l <

    SELECT class_id, MAX(result) FROM grade GROUP BY class_id; SELECT class_id, AVG(result) FROM grade WHERE (class_id = 1 AND result <> [1
     ]) OR (class_id = 2 AND result <> [2
     ]) OR … GROUP BY class_id;

61. -
    1
    1 SELECT class_id, AVG(result) FROM grade

    AS g1 WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id ) GROUP BY class_id;

62.   
    
        
     

    SELECT class_id, AVG(result) FROM grade AS g1 WHERE [
    ] GROUP BY class_id;

63.  
     
     
       6 63

    8 3 9 7 0 2 1 5 4 g1 SELECT class_id, AVG(result) FROM grade AS g1 WHERE [
    ] GROUP BY class_id;

64. 1 g1 
     
        

    
      
      
      
      
    
     
          
      WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

65.    
     
     
      

      
      
      
      
      
    
     
          
      g1 g2 
     
         
      
      
      
      
    
     
          
      WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

66. . 
     
         
    

      
      
    
        g2 g1 
     
         
      
      
      
      
    
     
          
      WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

67. MAX(result)
             

    
      
      
    
        g2 g1          
      
      
      
      
    
     
          
      WHERE g1.result <> ( SELECT MAX(result) FROM grade AS g2 WHERE g1.class_id = g2.class_id )

68. 
    
     36 4215 0     

      
    
    g2 g1        WHERE g1.result <> 100

69.   
      
     
      

    g1 1 89 B 4 3 0 2 6 753

70. 
    
    
    &
    Q &
    E C L

    &
    Q

71. 
    
     
     
    INSERT INTO [ 

    ] ([  ]) VALUES ([
    ]); INSERT INTO employee (id, name, joined_at) VALUES (10001, '', '2013/04/01'), (10002, '', '2014/04/01'), (10003, '', '2007/04/01');

72. 
    
       $#  "
      %

    
        ! 
    & UPDATE [  ] SET [
     ] = [
     ] WHERE [ ]; UPDATE employee SET joined_at = '2007/4/2' WHERE id = 10003;

73. 
    
     
    
    
     
     DELETE

    FROM [
    ] WHERE []; TRUNCATE [
    ]; DELETE FROM [
    ];

74. 
    
    
        
    

75. 
    
     !
      
    
    !
     
    

    

76.    
    
    Q l e j
    

    c i l D
    P E S IC A l PX A L N N
    N D j

77. 
    

78. 3
    L
    :
     8
    8 
    

    Q
    . 4 666 0 721 2 /

79. &
    h M
    1 0 S d L Q

    
    M
    
    
    c a
    .774 888 1 : 2 /4 4

80. 
    U B
    Ca B
    JF
    2 26

    N S
    
    3 : / / 0 4: 1: .

81.  
    
    
    9Q S
    : a L c

    
    d :
    h
    
    .55 4 0 721 2 /