student表

 

 teacher表

 

 course表

 

 score表

 

 對(duì)這四個(gè)表進(jìn)行一些練習(xí)。

1：查詢student表中所有記錄。

      select *from student;

2：查詢student表中name/sex/classid這些列。

      select  name,sex,classid from student;

3：查詢教師中不重復(fù)的單位department。

      select distinct department from teacher;

      distinct 列(一個(gè)或多個(gè)) 獲取不重復(fù)的記錄

4：查詢score表中成績(jī)?cè)?0至80之間的記錄。

      select *from score where degree between 60 and 80;

5：查詢score表中成績(jī)?yōu)?5，86，88的記錄。

     select *from score where degree in (86,85,88);

6：查詢student表中為95031班的或性別為女生的。

      select *from student where classid=95031 or sex='女';

7：以classid列降序查詢student表的所有記錄。

     select *from student order by desc classid ;

8：以degree降序courseid升序輸出score表中所有記錄。

    select *from score order by courseid asc, degree desc;

9：查詢95031班級(jí)的人數(shù)。

     select count(id) from student where classid=95031;

10：查詢score表中成績(jī)最高者的記錄。

       select *from score where degree=(select max(degree) from score);

      或： select id,courseid,degree order by degree desc limit 0,1;

11：查詢每門課的平均數(shù)。

    select courseid,avg(degree) from score group by courseid;

12：查詢score表中至少2名學(xué)生選修以3開頭的課程的平均分?jǐn)?shù)。

      select courseid, avg(degree) as average from score group by courseid having count(courseid)>1 and courseid like '3%';

13：查詢score中分?jǐn)?shù)大于70小于90的id列。

        select  *from score where degree between 70 and 90;

14：查詢學(xué)生的name列和degree列和classid列(多表查詢)。

        select name,degree,classid from student,score where student.id=score.id

15：查詢學(xué)生的課程id課程名和degree列(多表查詢)。

         select coursename,degree from score,course where course.courseid=score.courseid;

16：查詢學(xué)生的姓名成績(jī)和對(duì)應(yīng)課程名。(三表查詢)。

       select  name,degree,coursename from student,course,score where score.id=student.id and score.courseid=course.courseid;

17：查詢95031班學(xué)生每門課的平均成績(jī)(子查詢)。

        select avg(degree) from score where id in(select id from student where classid=95031) group by courseid;

18：查詢選修3105課程高于109好同學(xué)3105課程成績(jī)的同學(xué)記錄。

         select *from score where courseid=3105 and degree>(select degree from score where id=109 and courseid=3105);

19：查詢成績(jī)高于學(xué)號(hào)109課程號(hào)為3105的同學(xué)的記錄。

       select *from score where  degree>(select degree from score where id=109 and courseid=3105);

20：查詢學(xué)號(hào)與101和108的同學(xué)同年出生的學(xué)生的記錄。

       select name, brithday,classid from student where year(brithday) in (select year(brithday) from student where id=108 or id=101);

21：查詢張旭老師教的課程學(xué)生的成績(jī)。

        select *from score where courseid in (select courseid from course where teacherid=(select id from teacher where name='張旭'));

22：查詢選修課多余5人的老師的記錄。

       select *from teacher where id in (select teacherid from course where courseid in (select courseid from score group by courseid having count(courseid)>5));

23：查詢95033和95031班級(jí)同學(xué)的記錄

       select *from student where classid in (95033,95031);

24：查詢88分以上成績(jī)的課程name。

       select coursename from course where courseid in(select courseid from score where degree>88);

25：查詢計(jì)算機(jī)系老師所教課程同學(xué)的成績(jī)。

       select *from score where courseid in(select courseid from course where teacherid in(select id from teacher where department='計(jì)算機(jī)系'));

26：查詢計(jì)算機(jī)系與電子工程系不同職稱的教師的記錄。

        select *from teacher where department='計(jì)算機(jī)系'and professional  not in (select professional from  teacher where department='電子工程系' )
    -> union
    -> select *from teacher where department='電子工程系'and professional  not in (select professional from  teacher where department='計(jì)算機(jī)系' );

27：查詢選修編號(hào)為3105的課程成績(jī)至少高于部分選修3245課程學(xué)生的成績(jī)的記錄。

        select *from score where courseid=3105  and degree> any(select degree from score where courseid=3245);

28：查詢選修編號(hào)為3105的課程成績(jī)高于所有選修3245課程學(xué)生的成績(jī)的記錄。

        select *from score where courseid=3105  and degree> all(select degree from score where courseid=3245);

29：查詢所有教師和學(xué)生的name ,sex ,brithday。

         select name,sex,brithday from student
    -> union
    -> select name,sex,brithday from teacher;

30：查詢女教師和女同學(xué)的name sex brithday。

      select name,sex,brithday from student where sex='女'
    -> union
    ->  select name,sex,brithday from teacher where sex='女';

31：查詢成績(jī)比該課程平均分低的同學(xué)的成績(jī)。

        select *from score a where degree<(select avg(degree) from score b where a.courseid=b.courseid);

32：查詢?nèi)握n老師的name和department。

         select *from teacher where id in(select teacherid from course where courseid in(select courseid from score group by courseid));

33：查詢班級(jí)中至少有2名男生的班級(jí)。

       select classid from student where sex='男' group by classid having count(classid)>1;

34：查詢班級(jí)中不姓王的同學(xué)。

         select *from student where name not like '王%';

35：查詢所有學(xué)生的姓名和年齡。

        select name,year(now())-year(brithday) as age from student;

36：查詢學(xué)生中年齡最大和年齡最小的數(shù)據(jù)。

        select max(year(now())-year(brithday)),min(year(now())-year(brithday)) from student;

37：以班號(hào)和年齡從大到小順序查詢student表中所有記錄。

       select *from student order by classid,year(now())-year(brithday);

38：查詢男教師所上的課程。

       select coursename from course where teacherid in (select  id from teacher where sex='男')；

39：查詢最高分同學(xué)的信息。

      select *from score where degree =(select max(degree) from score);

40：查詢和季軍同性別的所有同學(xué)。

      select *from student where sex=(select sex from student where name='季軍');

41：查詢和季軍同性別并同班的同學(xué)的信息。

        select *from student where sex=(select sex from student where name='季軍') and classid=(select classid from student where name='季軍');

42：查詢所有選修計(jì)算機(jī)導(dǎo)論課程的男同學(xué)的成績(jī)。

       select *from score where courseid=(select courseid from course where coursename='計(jì)算機(jī)導(dǎo)論') and id in(select id from student where sex='男' );