Question

帮帮忙"1.创建数据库aa,并分别使用SQL语句创建student表、course表、Achievement表"

Answer 1

CREATE DATEBASE aa
CREATE TABLE Student(
sno CHAR(10) NOT NULL,
sname CHAR(10) NOT NULL,
sage INT,
ssex CHAR(1),
PRIMARY KEY sno
)CREATE TABLE course(
cno CHAR(10) NOT NULL,
cname CHAR(10) NOT NULL,
ccredit CHAR(8),
PRIMARY KEY cno
)CREATE TABLE Achievement(
cno CHAR(10) REFERENCES course(cno),
sno CHAR(10) REFERENCES student(sno),
sach int
)
4.使用SQL语句实现以下查询
(1)查询学生基本信息
(1)SELECT *
FROM student.
(2)查询年龄不小于20岁的学生姓名，并显示出是哪一年出生的。
(2)SELECT sname,sage
FROM student
WHERE sage>=20
(3)查询学生的课表
(3)SELECT *
FROM course
(4)查询某门课程的考试成绩情况，成绩按降序进行排序
(4)SELECT sach
FROM Achievement
WHERE cno IN(SELECT cno FROM course WHERE cname='某课程名称')5.使用SQL语句创建以下视图
(1)创建所有女生选课信息，并指定别名。
CREATE VIEW 视图名
AS
SELECT c.cname,s.sname,s.ssex
FROM student AS s INNER JOIN Achievement AS a
ON s.sno=a.sno
INNER JOIN course AS c
ON a.cno=c.cno
WHERE s.ssex='女'(2)创建所有课程所有不及格同学的相关信息视图。
CREATE VIEW 视图名
AS
SELECT s.sname,a.sach
FROM student AS s INNER JOIN Achievement AS a
ON s.sno=a.sno
WHERE a.sach<60