22,300
社区成员




--创建一个学生管理数据库STUDENTDB
CREATE DATABASE STUDENTDB
--创建以上各表并添加相应数据
CREATE TABLE STUDENT
(
SNO VARCHAR(20),
SNAME VARCHAR(50),
SSEX VARCHAR(20),
SAGE INT,
SDEPT VARCHAR(15)
)
CREATE TABLE COURSE
(
CNO VARCHAR(20),
CNAME VARCHAR(50),
CPNO VARCHAR(10),
CCREIDET VARCHAR(15)
)
CREATE TABLE SC
(
SNO VARCHAR(20),
CNO VARCHAR(20),
GRADE VARCHAR(20)
)
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT)VALUES('0215121','李勇','男','20','CS')
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT)VALUES('0215122','刘晨','女','19','IS')
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT)VALUES('0215123','王敏','女','18','MA')
INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT)VALUES('0215125','张立','男','18','IS')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('1','数据库','5','4')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('2','数学','','2')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('3','信息系统','1','4')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('4','操作系统','6','3')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('5','数据结构','7','4')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('6','数据处理','','2')
INSERT INTO COURSE (CNO,CNAME,CPNO,CCREIDET)VALUES('7','C语言','6','4')
INSERT INTO SC (SNO,CNO,GRADE)VALUES('0215121','1','92')
INSERT INTO SC (SNO,CNO,GRADE)VALUES('0215121','2','85')
INSERT INTO SC (SNO,CNO,GRADE)VALUES('0215121','3','88')
INSERT INTO SC (SNO,CNO,GRADE)VALUES('0215122','2','90')
INSERT INTO SC (SNO,CNO,GRADE)VALUES('0215122','3','80')
--在SC表中,查询学号为0215121学生的课程号与成绩
SELECT CNO,GRADE FROM SC WHERE SNO = '0215121'
--在COURSE表中,查询哪些课程没有预选课程号
SELECT * FROM COURSE WHERE CPNO = ''
--在STUDENT表中,查询IS系的学生姓名
SELECT SNAME FROM STUDENT WHERE SDEPT = 'IS'
--在STUDENT表中,查找姓“王”学生的基本信息
SELECT * FROM STUDENT WHERE SNAME LIKE '%王%'
--在COURSE表中,哪些课程的学分在3与4之间
SELECT * FROM COURSE WHERE CCREDIT BETWEEN '3' AND '4'