SQL数据库多表链接 问题

来源:百度知道 编辑:UC知道 时间:2024/07/02 14:22:30
use master
if exists(select * from sysdatabases where name='stu')
drop database stu
GO
create database stu --创建数据库
GO
USE stu
create table Dept--创建系别表
(
Dno int primary key,--系别编号
Dname varchar(20) not null--系别名称
)
GO
create table Student--学生信息表
(
Sno char(5) primary key,--学号
Sname varchar(10) not null,--姓名
Dno int foreign key references Dept(Dno)--所属系别编号
)
GO
create table Course--3、 课程表
(
Cno int primary key,--课程编号
Cname varchar(15) not null,--课程名称
Ccredit int not null check(Ccredit between 1 and 10)--学分
)
GO
create table SC--4、 成绩表
(
Sno char(5) foreign key references Student(Sno),--学号
Cno int foreign key references Course(Cno),--课程号
Grade int check(Grade between 0 and 100)--成绩
)
GO

insert Dept
select 1,'计算机' union
select 2,'美术'
GO

insert Student
select &

1.
select s.Sno as 学号, s.Sname as 姓名, d.Dname aS 所在系
from Student as s inner join Dept as d
on s.Dno = d.Dno

2.
select avg(Grade)
from SC as sc inner join Student as s
on sc.Sno = s.Sno
inner join Dept as d
on s.Dno = d.Dno
where d.Dname = '计算机'

........