选课系统SQL语句练习含数据信息.txt
- 文档编号:1317610
- 上传时间:2023-04-30
- 格式:TXT
- 页数:5
- 大小:5.45KB
选课系统SQL语句练习含数据信息.txt
《选课系统SQL语句练习含数据信息.txt》由会员分享,可在线阅读,更多相关《选课系统SQL语句练习含数据信息.txt(5页珍藏版)》请在冰点文库上搜索。
--ѧ�����
createtableStudent(
Snovarchar(11)primarykey,
Snamevarchar(20),
Ssexvarchar
(2),
Sageint,
Sdeptvarchar(20),
constraintche_sexcheck(Ssexin('��','Ů')),
constraintche_agecheck(Sagebetween18and22)
);
--�γ̱��
createtableCourse(
Cnovarchar(4)primarykey,
Cnamevarchar(20),
Ctimeint,
Ccreditint(10)
);
--ѧ��ѡ�α��
createtableSC(
Snovarchar(11),
Cnovarchar(4),
Gradeint,
constraintforeignkey(Sno)referencesStudent(Sno),
constraintforeignkey(Cno)referencesCourse(Cno)
);
-----------data
--Student
insertintoStudentvalues('001','Zhangxin','��',18,'computerscience');
insertintoStudentvalues('002','Wangpeng','��',19,'electronic');
insertintoStudentvalues('003','Lina','Ů',20,'Informationscience');
insertintoStudentvalues('004','Mafei','Ů',18,'computerscience');
insertintoStudentvalues('005','Zuoli','��',19,'Informationscience');
insertintoStudentvalues('006','Zhanghai','Ů',21,'Informationscience');
insertintoStudentvalues('007','Lijian','��',20,'electronic');
insertintoStudentvalues('008','Wangpeng','��',20,'computerscience');
insertintoStudentvalues('009','Liqiang','��',18,'electronic');
--Course
insertintoCoursevalues('C01','DatabaseSystem',48,1);
insertintoCoursevalues('C02','Internet',64,2);
insertintoCoursevalues('C03','ProgramminginJava',72,1);
insertintoCoursevalues('C04','ITIM',36,2);
insertintoCoursevalues('C05','Bibliometrics',54,3);
insertintoCoursevalues('C06','FPE',48,1);
insertintoCoursevalues('C07','LARS',36,2);
--SC
insertintoSCvalues('001','C01',78);
insertintoSCvalues('001','C02',64);
insertintoSCvalues('002','C03',57);
insertintoSCvalues('002','C04',89);
insertintoSCvalues('002','C05',97);
insertintoSCvalues('003','C04',null);
insertintoSCvalues('004','C06',26);
insertintoSCvalues('004','C07',58);
insertintoSCvalues('005','C02',85);
insertintoSCvalues('006','C03',85);
insertintoSCvalues('006','C06',75);
insertintoSCvalues('007','C07',null);
insertintoSCvalues('007','C02',88);
insertintoSCvalues('008','C03',52);
insertintoSCvalues('009','C03',93);
insertintoSCvalues('009','C01',63);
insertintoSCvalues('009','C02',73);
----------
1����ѯ����ѡ����Bibliometrics���ε�ѧ���������ͳɼ���
selectsname,grade
fromstudent,sc,course
wherestudent.sno=sc.sno
andcname='Bibliometrics'
ando=o;
2����ѯ���Գɼ��������ѧ���ĸ�����
selectcount(sno)fromsc
wheregrade<60;
3����ѯ���������ٺ���һ����z���ַ���ѧ����������ѧ�ź��Ա�
select*fromstudent
wheresnamelike'%z%';
4����ѯѡ���ˡ�IntroductiontotheInternet���γ̵�ѧ����ѧ�ż���ɼ�����ѯ����������Ľ������У�
selectsc.sno,gradefromsc
joincourseono=o
andcname='Internet'
orderbygradedesc;
5����ѯ��Zuoli��ͬѧѡ�γ̵���ѧʱ(time)��
selectsum(ctime)fromcourse
wherecnoin
(selectcnofromstudent
joinsconstudent.sno=sc.snoandsname='Zuoli');
6����ѯ���䲻����20���ѧ����ƽ�����Գɼ���
selectavg(grade)fromsc
wheresnoin(selectsnofromstudentwheresage<=20);
7����ѯ��computerscience��רҵѧ��ѡ����DatabaseSystem����������
selectcount(sno)fromscwheresnoin
(selectsnofromstudentwheresdept='computerscience');
8����ѯͬʱѡ�γ̡�DatabaseSystem���͡�IntroductiontotheInternet����ѧ��������
(selectsnamefromstudent
joinsconstudent.sno=sc.sno
joincourseono=o
andcname='DatabaseSystem')
intersect
(selectsnamefromstudent
joinsconstudent.sno=sc.sno
joincourseono=o
andcname='Internet'
);
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
wherestudent.sno=sc.sno
andsc.gradeisnull;
11����ѯ��computerscience��רҵѧ��ÿ���˵�ѡ����ѧ�֡�
selectst1.sname,sum(grade)fromscsc1,studentst1
wherest1.sno=sc1.sno
andst1.snoin
(selectstudent.sno
fromstudentwheresdept='computerscience')
groupbyst1.sno;
12����ѯ���˿���ƽ���ɼ�����רҵƽ���ɼ���ѧ������
selectsnamefrom
(selectsname,sdept,avg(grade)a_gfromstudent,sc
wherestudent.sno=sc.sno
groupbysname)asS,
(selectsdept,avg(grade)a_gfromstudent,sc
wherestudent.sno=sc.sno
groupbysdept)asD
whereS.sdept=D.sdept
andS.a_g>D.a_g;
13����ѯ���˿���ƽ���ɼ�����Ů��ƽ���ɼ�����������
selectsnamefrom
--��ѯ����������ƽ���ɼ�,�������S��ʾ
(selectsname,avg(grade)a_gfromstudentst1,scsc1
wherest1.sno=sc1.sno
andssex='��')asS
whereS.a_g>
--��ѯ����Ů��ƽ���ɼ�
(selectavg(grade)fromstudent,sc
wherestudent.sno=sc.sno
andssex='Ů');
14����ѯ�ȡ�computerscience��רҵ����ѧ�����䶼���ѧ��������
selectsnamefromstudent
wheresage>
(selectmax(sage)fromstudent
wheresdept='computerscience');
15����ѯ���Գɼ�����һ�Ʋ�����ѧ������
selectsnamefromstudent,sc
wherestudent.sno=sc.sno
andgrade<60
groupbystudent.sno
havingcount(*)=1;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 选课 系统 SQL 语句 练习 数据 信息