SQL基础练习题二
1.根据要求使用SQL创建数据库
CREATE TABLE Student(
Sno VARCHAR(20) NOT NULL,-- 学号
Sname VARCHAR(20) NOT NULL,-- 学生姓名
Sex VARCHAR(20) NOT NULL,-- 学生性别
Sbirthday DATETIME NULL , -- 学生出生年月
Class VARCHAR(20) NULL,
PRIMARY KEY(Sno)
);
CREATE TABLE Course(
Cno VARCHAR(20) NOT NULL,-- 课程号
Cname VARCHAR(20) NOT NULL,-- 课程名称
Tno VARCHAR(20) NOT NULL,-- 教工编号(外码)
PRIMARY KEY(Cno)
);
CREATE TABLE Score(
Sno VARCHAR(20) NOT NULL,-- 学号(外码)
Cno VARCHAR(20) NOT NULL,-- 课程号(外码)
Degree DECIMAL(20) NOT NULL,-- 成绩
FOREIGN KEY (Cno) REFERENCES Course(Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno)
);
1.根据要求使用SQL创建数据库
CREATE TABLE Teacher(
Tno VARCHAR(20) NOT NULL,-- 教工编号(主码)
Tname VARCHAR(20) NOT NULL,-- 教工姓名
Tsex VARCHAR(20) NOT NULL,-- 教工性别
Tbirthday DATETIME NULL ,-- 教工出生年月
Prof VARCHAR(20) NULL,-- 职称
Depart VARCHAR(20) NOT NULL,-- 职工所在部门
PRIMARY KEY(Tno)
);
2.使用sql添加表中数据
INSERT INTO student (Sno,Sname, Sex,Sbirthday,class) VALUES
('108','曾华','男','1977-09-01','95033'),
('105','匡明','男','1975-10-02','95031'),
('107','王丽','女','1976-01-23','95033'),
('101','李军','男','1976-01-23','95033'),
('109','王芳','女','1976-01-23','95031'),
('103','陆君','男','1976-01-23','95031');
INSERT INTO score (Sno,Cno,Degree) VALUES
('103','3-245','86'),
('105','3-245','75'),
('109','3-245','68'),
('103','3-105','92'),
('105','3-105','88'),
('109','3-105','76'),
('101','3-105','64'),
('107','3-105','91'),
('108','3-105','78'),
('101','6-166','85'),
('107','6-166','79'),
('108','6-166','81');
INSERT INTO Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart)
VALUES
('804', '张诚', '男', '1980-01-01', '副教授', '计算机系'),
('856', '张旭', '男', '1985-03-15', '讲师', '电子工程系'),
('825', '王萍', '女', '1978-06-30', '助教', '计算机系'),
('831', '刘冰', '女', '1990-11-20', '助教', '电子工程系');
3.将course表中高等数学修改为JAVA高级编程
UPDATE course SET Cname = 'java高级编程' WHERE Cno = '9-888';
4.删除学生陆君的信息(学生信息及学生考试成绩)
DELETE FROM score WHERE Sno = (SELECT Sno FROM student WHERE Sname = '陆君');
5.查询95031班级所有男生信息,返回学号,姓名,性别,出生日期,班级编号
SELECT * FROM student WHERE Class = '95031';
6.查询Score表中成绩为85,86或88的记录
SELECT * FROM score WHERE Degree BETWEEN 85 AND 88;
原文地址:https://blog.csdn.net/qq_62859013/article/details/142964496
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!