3)创建上述数据库,并插入数据。 (4)用T-SQL语句为部门表创建一个DELETE类型的触发器DEL_COUNT,删除数据时,显示删除元组的个数。 (5)如果删除员工表中的某一元组,因为涉及与部门表的外键联系,需要创建员工表的INSTEAD OF触发器,使得在删除员工表元组前,必须先删除表中外键联系的元组。 (6)把实习生的姓名和所在部门名称定义为一个视图。 (7)查询“教务系统”项目组负责人姓名。 (8)查询每个部门的实习生的人数。 (9)查询各个实习生所参加的项目名称,并按照项目名称排序。
时间: 2024-03-19 21:43:21 浏览: 129
由于没有提供数据,我只能给出创建表和视图的 SQL 语句以及查询语句的大致思路,具体实现可能需要根据实际数据稍作修改。
1. 创建表的 SQL 语句:
```sql
CREATE TABLE Department (
dno CHAR(9) PRIMARY KEY,
dname CHAR(9),
daddress CHAR(50),
mno CHAR(9),
CONSTRAINT c1 FOREIGN KEY (mno) REFERENCES Employee (eno)
);
CREATE TABLE Employee (
eno CHAR(9) PRIMARY KEY,
ename CHAR(9),
elevel INT,
dno CHAR(9),
FOREIGN KEY (dno) REFERENCES Department (dno)
);
CREATE TABLE Trainee (
tno CHAR(9) PRIMARY KEY,
tname CHAR(9),
tage INT,
dno CHAR(9),
FOREIGN KEY (dno) REFERENCES Department (dno)
);
CREATE TABLE Project (
pno CHAR(9) PRIMARY KEY,
pname CHAR(50),
start_time DATETIME,
terminal_time DATETIME,
rno CHAR(9),
FOREIGN KEY (rno) REFERENCES Employee (eno)
);
CREATE TABLE Trainee_participate (
tno CHAR(9),
pno CHAR(9),
FOREIGN KEY (tno) REFERENCES Trainee (tno),
FOREIGN KEY (pno) REFERENCES Project (pno)
);
CREATE TABLE Employee_participate (
eno CHAR(9),
pno CHAR(9),
time_rate FLOAT,
FOREIGN KEY (eno) REFERENCES Employee (eno),
FOREIGN KEY (pno) REFERENCES Project (pno)
);
```
2. 插入数据的 SQL 语句:
```sql
-- 待插入数据
```
3. 创建触发器的 SQL 语句:
```sql
-- 创建部门表的 DELETE 类型触发器 DEL_COUNT
CREATE TRIGGER DEL_COUNT
ON Department
FOR DELETE
AS
BEGIN
DECLARE @count INT
SELECT @count = COUNT(*) FROM deleted
PRINT CONCAT('Deleted ', CAST(@count AS VARCHAR), ' rows.')
END
```
4. 创建视图的 SQL 语句:
```sql
CREATE VIEW TraineeDepartmentView AS
SELECT Trainee.tname, Department.dname
FROM Trainee
INNER JOIN Department ON Trainee.dno = Department.dno;
```
5. 查询语句:
- 查询“教务系统”项目组负责人姓名
```sql
SELECT Employee.ename
FROM Employee
INNER JOIN Project ON Employee.eno = Project.rno
WHERE Project.pname = '教务系统';
```
- 查询每个部门的实习生的人数
```sql
SELECT Department.dname, COUNT(Trainee.tno) AS trainee_count
FROM Department
LEFT JOIN Trainee ON Department.dno = Trainee.dno
GROUP BY Department.dname;
```
- 查询各个实习生所参加的项目名称,并按照项目名称排序
```sql
SELECT Trainee.tname, Project.pname
FROM Trainee_participate
INNER JOIN Trainee ON Trainee_participate.tno = Trainee.tno
INNER JOIN Project ON Trainee_participate.pno = Project.pno
ORDER BY Project.pname;
```
阅读全文