Teaching数据库中有class表其属性为classno,classname,department,monitor; Course表其属性为courseno,cname,period,credit Score表其属性为studentno,courseno,usually,final Student表其属性为studentno,sname 现创建一个存储过程,查询指定学院学分未达到165的学生姓名和学号。
时间: 2024-02-28 21:56:22 浏览: 24
好的,根据您的需求,我可以为您编写一个存储过程。具体的实现如下:
```
CREATE PROCEDURE get_students_by_department
@department VARCHAR(50)
AS
BEGIN
SELECT s.studentno, s.sname
FROM Student s
INNER JOIN Score sc ON s.studentno = sc.studentno
INNER JOIN Course c ON sc.courseno = c.courseno
WHERE c.credit < 165 AND c.department = @department
GROUP BY s.studentno, s.sname
END
```
这个存储过程接受一个参数 @department,用于指定学院名称,然后查询该学院中学分未达到165的学生姓名和学号。具体的实现中,我们使用了 INNER JOIN 将 Student 表、Score 表和 Course 表连接起来,然后根据条件筛选出符合要求的学生。最后,我们使用 GROUP BY 将结果按照学生学号和姓名进行分组。
如果您需要查询其它学院的学生,请将 @department 参数替换为相应的学院名称即可。
相关问题
在视图v_score 中查找classNo为“21002”的学生的学号和成绩
若要在视图v_Score中查找classNo为“21002”的学生的学号和成绩,可以使用如下SQL语句:
```
SELECT student_id, score
FROM v_Score
WHERE classNo = '21002';
```
其中,SELECT语句用于指定要查询的列,FROM子句用于指定要查询的视图,WHERE子句用于筛选classNo为“21002”的记录。这条SQL语句将返回满足条件的学生的学号和成绩。请注意:由于v_Score是一个视图,实际上并不存储任何数据,因此这条SQL语句实际上是查询tb_score表中成绩大于等于90分的记录中,classNo为“21002”的学生的学号和成绩。
(1)创建数据库ScoreDB (2)创建表Class、Student、 Course、Term、Score,按(3)-(9)设置各表的实体完整性、参照完整性和用户自定义完整性(表结构详见教材P67) (3)设置Class, Student, Course, Term, Score五张表的主键 (4)设置Student, Course, Score三张表的外键 (5)设置Class表“ClassName”列和Course表“CourseName”列的唯一性约束 (6)设置Class表的“ClassNo”第1、2位为A-Z的英文字母,其余只能为数字字符 (7)设置Student表的 “Sex”值只能为男或女,默认值为“女”;年龄不能小于17岁 (8)设置Score表的“Score”列的值在[0,100]之间 (9)设置Course表“creditHour”列的值等于“courseHour”列的值除以16
好的,以下是创建ScoreDB数据库和五张表,以及设置完整性约束的SQL命令:
1. 创建数据库ScoreDB:
```
CREATE DATABASE ScoreDB;
```
2. 切换到ScoreDB数据库:
```
USE ScoreDB;
```
3. 创建表Class:
```
CREATE TABLE Class (
ClassNo CHAR(4) PRIMARY KEY CHECK (ClassNo LIKE '[A-Z][A-Z][0-9][0-9]'),
ClassName VARCHAR(20) UNIQUE NOT NULL,
HeadTeacher VARCHAR(10) NOT NULL
);
```
4. 创建表Student:
```
CREATE TABLE Student (
StudentNo CHAR(8) PRIMARY KEY,
StudentName VARCHAR(10) NOT NULL,
Sex CHAR(2) DEFAULT '女' CHECK (Sex IN ('男', '女')),
Birthday DATE NOT NULL,
ClassNo CHAR(4) NOT NULL,
FOREIGN KEY (ClassNo) REFERENCES Class (ClassNo) ON UPDATE CASCADE ON DELETE CASCADE
);
```
5. 创建表Course:
```
CREATE TABLE Course (
CourseNo CHAR(4) PRIMARY KEY,
CourseName VARCHAR(20) UNIQUE NOT NULL,
CreditHour INT NOT NULL,
CourseHour INT NOT NULL,
FOREIGN KEY (CourseNo) REFERENCES Score (CourseNo) ON UPDATE CASCADE ON DELETE CASCADE
);
```
6. 创建表Term:
```
CREATE TABLE Term (
TermNo CHAR(4) PRIMARY KEY,
TermName VARCHAR(20) UNIQUE NOT NULL,
BeginDate DATE NOT NULL,
EndDate DATE NOT NULL
);
```
7. 创建表Score:
```
CREATE TABLE Score (
StudentNo CHAR(8) NOT NULL,
CourseNo CHAR(4) NOT NULL,
TermNo CHAR(4) NOT NULL,
Score INT CHECK (Score BETWEEN 0 AND 100),
PRIMARY KEY (StudentNo, CourseNo, TermNo),
FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (CourseNo) REFERENCES Course (CourseNo) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (TermNo) REFERENCES Term (TermNo) ON UPDATE CASCADE ON DELETE CASCADE
);
```
8. 设置Class表“ClassName”列和Course表“CourseName”列的唯一性约束:
```
ALTER TABLE Class ADD CONSTRAINT uc_ClassName UNIQUE (ClassName);
ALTER TABLE Course ADD CONSTRAINT uc_CourseName UNIQUE (CourseName);
```
9. 设置Class表的“ClassNo”第1、2位为A-Z的英文字母,其余只能为数字字符:
```
ALTER TABLE Class ADD CONSTRAINT ck_ClassNo CHECK (ClassNo LIKE '[A-Z][A-Z][0-9][0-9]');
```
10. 设置Student表的“Sex”值只能为男或女,默认值为“女”;年龄不能小于17岁:
```
ALTER TABLE Student ADD CONSTRAINT ck_Sex CHECK (Sex IN ('男', '女'));
ALTER TABLE Student ADD CONSTRAINT df_Sex DEFAULT '女' FOR Sex;
ALTER TABLE Student ADD CONSTRAINT ck_Age CHECK (DATEDIFF(YEAR, Birthday, GETDATE()) >= 17);
```
11. 设置Score表的“Score”列的值在[0,100]之间:
```
ALTER TABLE Score ADD CONSTRAINT ck_Score CHECK (Score BETWEEN 0 AND 100);
```
12. 设置Course表“creditHour”列的值等于“courseHour”列的值除以16:
```
ALTER TABLE Course ADD CONSTRAINT df_CreditHour DEFAULT (CourseHour / 16) FOR CreditHour;
```
以上命令将创建ScoreDB数据库和五张表,并设置完整性约束。您可以根据需要修改表结构和完整性约束。