㈠ SQL資料庫高手來~~懸賞
--1.新建一個名為school的資料庫。
create database school
go
use school
go
create table student--學生信息表(student)
(
stu_no varchar(50) primary key,
stu_name varchar(100) ,
stu_sex varchar(2),
stu_phone varchar(200),
stu_email varchar(500)
)
go
insert into student values('001','張三','男','111','[email protected]')
insert into student values('002','李四','男','222','[email protected]')
insert into student values('003','王五','女','333','[email protected]')
insert into student values('004','趙六','女','444','[email protected]')
insert into student values('005','錢七','男','555','[email protected]')
go
create table teacher
(
tea_no varchar(50) primary key,
tea_name varchar(100),
tea_sex varchar(2),
tea_phone varchar(200),
tea_email varchar(500)
)
go
insert into teacher values('001','馮老師','女','123','[email protected]')
insert into teacher values('002','劉老師','男','321','[email protected]')
go
create table course--課程表(course)
(
cor_id int primary key,
cor_name varchar(100),
tea_no varchar(50) foreign key references teacher(tea_no)
)
go
insert into course values(991,'資料庫','001')
insert into course values(992,'操作系統','002')
go
create table choose--選課表(choose)
(
ch_id int identity primary key,
stu_no varchar(50) foreign key references student(stu_no),
cor_id int foreign key references course(cor_id)
)
go
insert into choose values('001',991)
insert into choose values('001',992)
insert into choose values('002',991)
insert into choose values('002',992)
insert into choose values('003',991)
insert into choose values('004',992)
go
--1.選出教師信息表(teacher)所有數據。（10分）
select * from teacher
--2.選出前三條的學生信息數據。（10分）
select top 3* from student order by stu_no
--3.選出張三同學選修的所有課程名稱。（10分）
select cor_name as '張三同學選修的所有課程名稱' from course where cor_id in (select cor_id from choose where stu_no=(select stu_no from student where stu_name = '張三'))
--4.選出沒有選課的學生。（10分）
select * from student where stu_no not in(select stu_no from choose)
--5.選出每個學生及他們選課的門數。（10分）
select student.stu_no,student.stu_name,student.stu_sex,student.stu_phone,student.stu_email,isnull(coun,0) from student left join (select stu_no,count(*)as coun from choose group by stu_no) as nes on student.stu_no = nes.stu_no
select stu_no,count(*) from choose group by stu_no
--6.選出馮老師所教的所有學生。（10分）
select * from student where stu_no in(select stu_no from choose where cor_id in(select cor_id from course where tea_no=(select tea_no from teacher where tea_name = '馮老師')))
可以復制到資料庫 運行