《sql数据库表的创建和使用命令.docx》由会员分享，可在线阅读，更多相关《sql数据库表的创建和使用命令.docx（7页珍藏版）》请在三一办公上搜索。
1、1. 创建学生表student create table student (sno char (9) primary key ,sname char (20) not null ,ssex char (2) null ,sage smallint null ,sdept char (20) null)2. 创建课程表course create table course (cno char (4) primary key not null ,cname char (40) null ,cpno char (4) null ,ccredit smallint null ,foreign key (
2、cpno) references course (cno) )3. 创建学生选课表 create table sc (sno char (9) not null ,cno char (4) not null ,grade smallint null ,primary key (sno,cno),foreign key (cno) references course (cno), foreign key (sno) references student (sno) )4. 查询所有姓王的学生的姓名、学号和性别5. 查询所有姓张且全名为三个字的学生ZXYQLEXPRE.wc. - SQLQu er
3、y 1. sq select Bname from student w：here snane like 弓长_；trrrQ结果瓦消息 sname1 rsii i .6. 查询缺少成绩的学生的学号、课号表-dbo.sc 表-dbo.student ZXY&QLEXPRE.wc. - SQLQuer select snQt eno from scwnere grade IS njll;4H匚结果看消息sno eno/StiZ7. 查询平均成绩大于等于90的学生的学号和和平均成绩表-dbo.sc 表-dbo.student ZXY&QLEXPRES.wc. -QLQueryl.si select s
4、no,avc gradei from sc grcup by snonavi ng avc gr ade) = 90;|结果_J消息snol无列名）1 | 2DD215101 :如2 市罗曲曲 -如8. 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名表-dbo.sc 表-dbo.student ZXYSQLEXPRES,wc. -SQLQuery select st udent, sno, snane from studentr scwhere st adent sno sc sno and |sc eno R 2 F and bc grade 90;* I. m结果_J消息snos
5、name1200215101崔深梁22002151OE梁树得32flO215W7罗开是42flO215WB隹K污5200215110贺声代6200215112魏沉因72flO21511B胡睛音9. 找出每个学生超过他自己选修课程和平均成绩的课程号表-dbo.sc 表-dbo.student ZXYQLEXPRES.wc. - SQLQueryl.sql select, sna, eno from sc k where grade=(select avc(grade) from sc y where y.sno x.sno);/rrrE3结果匾消息 snoeno1 r21510l| 12 藏诵砂
6、W3 2W21510314 2W2151D415 2W21510516 2W21511927 2W215120110. 查询既选修1号课又选修2号课的学生学号表-dbo.sc 表-dbo.student ZXYQLEXPRES.wc. SQLQii&ry 1.sq select bho from bc where cno = F L1 intersect select sno from sc where cno= r 2r;rrr11.将张三的信息添加到student表-dbo.student 表-dbo.sr ZXY&QLEXPRE.wc. - EQLQu&ry 1.sqF|g| inser
7、t intc studentvalues. R 弓长三 1 ;男,12 .二司：1 ;表-dbo.student 表-dbo.scF ZXVSQLEXPRESS.wc.-SQLQueryl.sql*擒要snosnamessexsagesdeptInn uuiM张三11IS200215101崔深梁女17MA200215102潘声引女ISMA200215103冯睛拓女20IS200215104陈钟戛女20IS12.修改张三的年龄为22 -.T*表-dbo.student 表-dbo.sr ZXYSQLEXPRES.wc. SQLQueryl.sq,ipdat e b fade nt set sage=22where 皿已=弓长三；表-dbo.Student ZXYSQLEXPRESS.wc. - SQLQueryl.sql* 熊snosnamessexsagesdept111111111张三幽IS200215101崔深梁女17MA200215102藩声引女18MA200215103冯睛拓女20IS200215104陈钟裳女20IS
