SQL题目

SQL

Given 3 tables:

    student(_id, _name, _age, _class)
    course(_id, _name)
    student_course(_student_id, _course_id, _score)

1. find all students who select a course's number 'c2'.

        select _id, _name
        from student_course, student
        where _course_id = 'c2'

2. find all students who select a course named "Java".

        select _id, _name 
        from student 
        where _id in (
            select _student_id
            from student_course sc, course c
            where sc._course_id = c._id 
                  and  c._name = 'Java'
        )

3. find all students who didn't select the course's number 'c5'.

        select _id, _name
        from student
        where _id not in (
            select _student_id
            from student_course
            where _course_id = 'c5'
        )

4. find all the student's who selected at least one course.

        select count(distinct _student_id)
        from student_course

5. find all the student's who selected at least 5 courses.

        select _id, _name
        from student
        where _id in (
            select _student_id
            from student_course
            group by _student_id
            having count(_course_id) > 5
        )

    -- The HAVING clause was added to SQL, because the WHERE keyword could not be used with aggregate functions.

6. find students whose seleted course score all great than 80

        select _id, _name
        from student
        where _id not in (
            select distinct _student_id 
            from student_course
            where _score < 80
        )

    -- the "in/not in" operator will make the inner table left join the outer table.

7. delete redundant student records which are only different in  _id.

        delete
        from student
        where _id no in (
            select min(_id)
            from student
            group by _name, _age, _class
        )

8. list the student name and the number of their total selected courses.

        select _name , count(*)
        from (
            select _name
            from student s
                 right join student_course sc
                 on s._id = sc.student_id
        )
        group by _name

9. How to query table "sale"
    year  month amount
    1991   1     1.1
    1991   2     1.2
    1991   3     1.3
    1991   4     1.4
    1992   1     2.1
    1992   2     2.2
    1992   3     2.3
    1992   4     2.4
   
   To the following result:
    year  m1   m2   m3   m4
    1991  1.1  1.2  1.3  1.4
    1992  2.1  2.2  2.3  2.4 

    select
            year,  
            ( 
                select 
                     amount 
                from sale m 
                where 
                     m.year = sale.year  
                     and  month = 1
            ) as m1, 
            ( 
                select 
                     amount 
                from sale m 
                where 
                     m.year = sale.year  
                     and  month = 2
            ) as m2, 
            ( 
                select 
                     amount 
                from sale m 
                where 
                     m.year = sale.year  
                     and  month = 3
            ) as m3,
            ( 
                select 
                     amount 
                from sale m 
                where 
                     m.year = sale.year  
                     and  month = 4
            ) as m4
    from sale  
    group by year 

-
转载请注明：
原文出处：http://lixh1986.iteye.com/blog/2334132










-