SQL题目

[b][size=medium]SQL[/size][/b]

Given 3 tables:

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

[b]1. find all students who select a course's number 'c2'.[/b]

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

[b]2. find all students who select a course named "Java".[/b]

        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'
        )

[b]3. find all students who didn't select the course's number 'c5'.[/b]

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

[b]4. find all the student's who selected at least one course.[/b]

        select count(distinct _student_id)
        from student_course

[b]5. find all the student's who selected at least 5 courses.[/b]

        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.

[b]6. find students whose seleted course score all great than 80[/b]

        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.

[b]7. delete redundant student records which are only different in _id.[/b]

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

[b]8. list the student name and the number of their total selected courses.[/b]

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

[b]9. How to query table "sale"[/b]
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

[b] To the following result:[/b]
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


-