选课系统SQL语句练习含数据信息.txt资料文档下载

选课系统SQL语句练习含数据信息.txt资料文档下载

《选课系统SQL语句练习含数据信息.txt资料文档下载》由会员分享，可在线阅读，更多相关《选课系统SQL语句练习含数据信息.txt资料文档下载（5页珍藏版）》请在冰豆网上搜索。

Ctimeint,

Ccreditint（10）

--ѧ��ѡ�α��

createtableSC（

Snovarchar（11）,

Cnovarchar（4）,

Gradeint,

constraintforeignkey（Sno）referencesStudent（Sno）,

constraintforeignkey（Cno）referencesCourse（Cno）

-----------data

--Student

insertintoStudentvalues（'

001'

Zhangxin'

18,'

computerscience'

002'

Wangpeng'

19,'

electronic'

003'

Lina'

20,'

Informationscience'

004'

Mafei'

005'

Zuoli'

006'

Zhanghai'

21,'

007'

Lijian'

008'

009'

Liqiang'

--Course

insertintoCoursevalues（'

C01'

DatabaseSystem'

48,1）;

C02'

Internet'

64,2）;

C03'

ProgramminginJava'

72,1）;

C04'

ITIM'

36,2）;

C05'

Bibliometrics'

54,3）;

C06'

FPE'

C07'

LARS'

--SC

insertintoSCvalues（'

78）;

64）;

57）;

89）;

97）;

null）;

26）;

58）;

85）;

75）;

88）;

52）;

93）;

63）;

73）;

----------

1����ѯ����ѡ�޹���Bibliometrics���ε�ѧ���������ͳɼ���

selectsname,grade

fromstudent,sc,course

wherestudent.sno=sc.sno

andcname='

ando=o;

2����ѯ���Գɼ��������ѧ���ĸ�����

selectcount（sno）fromsc

wheregrade<

60;

3����ѯ���������ٺ���һ����z���ַ���ѧ����������ѧ�ź��Ա�

select*fromstudent

wheresnamelike'

%z%'

;

4����ѯѡ���ˡ�IntroductiontotheInternet���γ̵�ѧ����ѧ�ż���ɼ�����ѯ����������Ľ������У�

selectsc.sno,gradefromsc

joincourseono=o

orderbygradedesc;

5����ѯ��Zuoli��ͬѧѡ�޿γ̵���ѧʱ（time）��

selectsum（ctime）fromcourse

wherecnoin

（selectcnofromstudent

joinsconstudent.sno=sc.snoandsname='

6����ѯ���䲻����20���ѧ����ƽ�����Գɼ���

selectavg（grade）fromsc

wheresnoin（selectsnofromstudentwheresage<

=20）;

7����ѯ��computerscience��רҵѧ��ѡ����DatabaseSystem����������

selectcount（sno）fromscwheresnoin

（selectsnofromstudentwheresdept='

）;

8����ѯͬʱѡ�޿γ̡�DatabaseSystem���͡�IntroductiontotheInternet����ѧ��������

（selectsnamefromstudent

joinsconstudent.sno=sc.sno

）

intersect

9����ѯѡ�޵Ŀγ��к��С�Wanggang��ͬѧ����ѡ�޿γ̵�ѧ��������wanggang-����ͬѧnotexist

selectsno,snamefromstudentaseachS

wherenotexist

（（selectcnofromsc

joinstudentonsc.sno=student.sno

andsname='

Wanggang'

except

（selectcnofromstudent

joinsconsonstudent.sno=student.sno

andsc.sno=eachS.sno

））;

10����ѯ��InformationTechnologyforInformationManagement�����Գɼ�Ϊ�յ�ѧ��������רҵ���ơ�

selectsname,sdept

fromstudent,sc

andsc.gradeisnull;

11����ѯ��computerscience��רҵѧ��ÿ

���˵�ѡ�޿���ѧ�֡�

selectst1.sname,sum（grade）fromscsc1,studentst1

wherest1.sno=sc1.sno

andst1.snoin

（selectstudent.sno

fromstudentwheresdept='

groupbyst1.sno;

12����ѯ���˿���ƽ���ɼ�����רҵƽ���ɼ���ѧ������

selectsnamefrom

（selectsname,sdept,avg（grade）a_gfromstudent,sc

wherestudent.sno=sc.sno

groupbysname）asS,

（selectsdept,avg（grade）a_gfromstudent,sc

groupbysdept）asD

whereS.sdept=D.sdept

andS.a_g>

D.a_g;

13����ѯ���˿���ƽ���ɼ�����Ů��ƽ���ɼ�����������

--��ѯ����������ƽ���ɼ�,�������S��ʾ

（selectsname,avg（grade）a_gfromstudentst1,scsc1

wherest1.sno=sc1.sno

andssex='

）asS

whereS.a_g>

--��ѯ����Ů��ƽ���ɼ�

（selectavg（grade）fromstudent,sc

14����ѯ�ȡ�computerscience��רҵ����ѧ�����䶼���ѧ��������

selectsnamefromstudent

wheresage>

（selectmax（sage）fromstudent

wheresdept='

15����ѯ���Գɼ�����һ�Ʋ�����ѧ������

selectsnamefromstudent,sc

andgrade<

60

groupbystudent.sno

havingcount（*）=1;