发布时间：2023-11-07 13:55
修改时间：2023-11-20 22:17
【作者：無同 | 未经许可，请勿转载！】
目录
参见:
https://blog.csdn.net/m0_47174730/article/details/114995613
-- ********** create database ********** --
-- ********** Begin ********** --
create database school
-- ********** End ********** --
go
use school
go
-- ********** create table ********** --
-- ********** Begin ********** --
create table teacher
(
ID int,
Name varchar(20),
sex char(2),
Phone varchar(20)
)
-- ********** End ********** --
go
SET NOCOUNT ON
-- ********** insert ********** --
-- ********** Begin ********** --
insert into teacher
(
ID,
Name,
sex,
Phone
)
values
(
'1',
'Lucy',
'F',
'NULL'
)
-- ********** End ********** --
go
-- ********** create database ********** --
-- ********** Begin ********** --
create database website
-- ********** End ********** --
go
use website
go
-- ********** create table ********** --
-- ********** Begin ********** --
create table shopping
(
ID int IDENTITY(1,1) not null,
Name varchar(20),
address varchar(30)
)
-- ********** End ********** --
go
SET NOCOUNT ON
insert into shopping (Name, address) values ('eBay', 'www.ebay.com')
go
SET NOCOUNT ON
-- ********** insert ********** --
-- ********** Begin ********** --
insert into shopping (Name, address) values ('amazon', 'www.amazon.com')
-- ********** End ********** --
go
SET NOCOUNT ON
-- ********** delete ********** --
-- ********** Begin ********** --
DELETE FROM shopping WHERE ID=1
-- ********** End ********** --
go
-- ********** create database ********** --
-- ********** Begin ********** --
create database Books
-- ********** End ********** --
go
use Books
go
-- ********** create table ********** --
-- ********** Begin ********** --
create table prices
(
ID int IDENTITY(1,1) not null,
Name varchar(20),
price varchar(30)
)
-- ********** End ********** --
go
SET NOCOUNT ON
-- ********** insert ********** --
-- ********** Begin ********** --
insert into prices (Name, price) values ('Harry Potter', '$128')
-- ********** End ********** --
go
SET NOCOUNT ON
insert into prices (Name, price) values ('Walden', '$5')
go
SET NOCOUNT ON
-- ********** update ********** --
-- ********** Begin ********** --
update prices
SET price='$6'
where ID=2
-- ********** End ********** --
go
USE Mall
GO
SET NOCOUNT ON
---------- retrieving multiple column ----------
-- ********** Begin ********** --
select prod_name,prod_price from Products
-- ********** End ********** --
GO
---------- retrieving all column ----------
-- ********** Begin ********** --
select * from Products
-- ********** End ********** --
GO
USE Mall
Go
SET NOCOUNT ON
---------- retrieving with limited ----------
-- ********** Begin ********** --
select top 2 prod_name from Products
-- ********** End ********** --
GO
---------- retrieving with expression ----------
-- ********** Begin ********** --
select prod_price,prod_price*0.8 discount_price from Products
-- ********** End ********** --
GO
USE Mall
Go
SET NOCOUNT ON
---------- retrieving with range ----------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_price between 3 and 5
-- ********** End ********** --
GO
---------- retrieving with nomatches ----------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_name!='Lion toy'
-- ********** End ********** --
GO
1 A
2 ABC
3 AB
4 AB
5 ABCD
6 ABCD
7 AC
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询所有教师的教工号、姓名和电话号码信息*****
select Tno,Tname,Tphone from Teacher;
--*****2、查询所有系的名称*****
select distinct Sdept '系名' from Student;
--*****3、查询各教师的教工号、姓名、工资及按工资的95%发放的预发工资，查询结果列名用中文描述，其中，预发工资列名为‘预发95%的工资’。
select Tno '教工号',Tname '姓名',Tpay '工资',Tpay*0.95 '预发95%的工资' from Teacher;
----------------------END-----------------------
GO
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询‘计算机’系的所有副教授的基本信息*****
select Tno,Tname,Tsex,Tphone,Tdept,Taddr,Tprof,Tpay from Teacher where Tdept='计算机' and Tprof='副教授';
--*****2、查询所有女同学的姓名及所在的系，显示结果不允许重复出现*****
select distinct Sname,Sdept from Student where Ssex='女';
--*****3、设学号的5个数字中，前2位代表年级，后3位为序号，检索计算机系或外国语系21级总学分在60～90之间的学生信息*****
select Sno,Sname,Ssex,Sbirth,Sdept,Total from Student where Total between 60 and 90;
--*****4、查询课程号不为‘1’、‘4’和‘7’的课程信息*****
select Cno,Cname,Cpno,Credit from Course where Cno<>'1' and Cno<>'4' and Cno<>'7';
--*****5、查询所有姓‘张’、或‘刘’或‘高’的学生信息*****
select Sno,Sname,Ssex,Sbirth,Sdept,Total from Student where Sname like'张%' or Sname like'刘%' or Sname like'高%';
----------------------END-----------------------
GO
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询共有多少个同学*****
select count(*)'学生总人数' from Student;
--*****2、查询每个系有多少个同学*****
select distinct Sdept'系别',count(Sdept)'学生人数' from Student group by Sdept;
--*****3、查询选课门数在两门以上的同学的选课门数及其平均成绩*****
select Sno'学号',count(Sno)'选课门数',avg(Grade) from SC group by Sno having count(*)>2;
--*****4、统计已有学生选课的课程的课程号,选课人数、最高成绩、最低成绩*****
select Cno'课程号',count(Sno)'选课人数',max(Grade)'最高分',min(Grade)'最低分' from SC group by Cno;
----------------------END-----------------------
GO
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、查询按总学分降序排序的学生信息，若总学分相同则按姓名升序排序*****
select Sno,Sname,Ssex,Sbirth,Sdept,Total from Student order by Total desc,Sname;
--*****2、查询各课程的平均成绩并按平均成绩降序排列*****
select Cno'课程号',avg(Grade)'平均成绩' from SC group by Cno order by avg(Grade) desc;
--*****3、查询老师的授课门数并按教工号升序排序*****
select Tno into aaa from SC group by Cno,Tno;
select Tno'教师号',count(Tno)'授课门数' from aaa group by Tno order by Tno;
----------------------END-----------------------
GO
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、获取所有选课的同学的学号*****
select distinct Sno'有选课同学的学号' from SC;
--*****2、列出课程表中有先行课的课程名*****
select Cname'有先行课的课程名' from Course where Cpno is not NULL;
--*****3、查询平均成绩最高的前3名同学的学号及平均成绩*****
select top 3 Sno'学号',avg(Grade)'平均成绩' from SC group by Sno order by avg(Grade) desc;
--*****4、查询授课门数在两门及以上的老师的授课门数并按教工号升序排序*****
select Tno into aaa from SC group by Tno,Cno;
select Tno'教师号',count(Tno)'授课门数' from aaa group by Tno having count(Tno)>=2 order by Tno;
--*****5、查询至少有两门课在80分以上的学生学号及80分以上课程数*****
select Sno,Grade into bbb from SC group by Sno,Grade having Grade>80;
select Sno'学号',count(*)'80分以上课程数' from bbb group by Sno having count(*)>=2;
----------------------END-----------------------
GO
1 C
2 A
3 B
--********** create student database **********--
--********** Begin **********--
create database student
on primary
(name='studentdata1',
filename='/home/studentdata1.mdf',
size=5MB,
maxsize=10MB,
filegrowth=1MB),
(name='studentdata2',
filename='/home/studentdata2.ndf',
size=5MB,
maxsize=10MB,
filegrowth=1MB)
log on
(name='studentlog1',
filename='/home/studentlog1.ldf',
size=5MB,
maxsize=10MB,
filegrowth=1MB)
--********** End **********--
go
--********** add database file **********--
--********** Begin **********--
alter database student
add file
(name='studentdata3',
filename='/home/studentlog3.ndf',
size=6MB,
maxsize=20MB,
filegrowth=1MB)
--********** End **********--
go
--********** add database log file **********--
--********** Begin **********--
alter database student
add log file
(name='studentlog2',
filename='/home/studentlog2.ldf',
size=6MB,
maxsize=20MB,
filegrowth=1MB)
--********** End **********--
go
--********** alter database file **********--
--********** Begin **********--
alter database student
modify file
(name='studentdata3',
size=8MB,
maxsize=30MB)
--********** End **********--
go
USE studentdb
go
SET NOCOUNT ON
go
--********** create table **********--
--********** Begin **********--
create table Student
(sno varchar(50),
name varchar(50),
sex varchar(10),
birthday Date,
discipline varchar(50),
school varchar(100));
create table Course
(cno varchar(50),
cname varchar(50),
description varchar(256),
credit float,
cinstitution varchar(128));
create table Score
(sno varchar(50),
cno varchar(50),
grade float)
--********** End **********--
go
select name, length, COLLATION from syscolumns where id=object_Id('student')
go
select name, length, COLLATION from syscolumns where id=object_Id('course')
go
select name, length, COLLATION from syscolumns where id=object_Id('score')
go
--********** insert **********--
--********** Begin **********--
INSERT INTO Score
VALUES
('09011101',101,89),
('09011101',102,78)
--********** End **********--
select * from score
go
--********** alt student table **********--
--********** Begin **********--
alter table Student
add addr varchar(256)
--********** End **********--
go
select name, length, COLLATION from syscolumns where id=object_Id('student')
go
--********** del score table **********--
--********** Begin **********--
drop table Score
--********** End **********--
go
GO
IF NOT EXISTS(Select 1 From Sysobjects Where Name='score') --查询表是否存在
BEGIN
PRINT 'NOT EXISTS TABLE score'
END
GO
USE studentdb
go
SET NOCOUNT ON
go
--********** create score_view_cdept **********--
--********** Begin **********--
create view score_view_cdept
as
select Score.sno,name,cname,grade from Student,Course,Score where Student.sno=Score.sno and Course.cno=Score.cno
--********** End **********--
go
USE studentdb
go
SET NOCOUNT ON
go
--********** create proc_student_info **********--
--********** Begin **********--
create procedure proc_student_info
as begin
select * from student
end
--********** End **********--
go
exec proc_student_info
go
--********** create proc_sno **********--
--********** Begin **********--
create procedure proc_sno
@sno varchar(50)
as begin
select * from student where sno=@sno
end
--********** End **********--
go
exec proc_sno '1001'
go
--********** create proc_add **********--
--********** Begin **********--
create procedure proc_add
@sno varchar(50),
@sname varchar(50),
@sex varchar(50),
@birthday date,
@discipline varchar(50),
@school varchar(50)
as begin
insert into student
values(@sno,@sname,@sex,@birthday,@discipline,@school)
end
--********** End **********--
go
exec proc_add '1004','HMM','female','2019-6-2','English','national school'
go
exec proc_student_info
go
--********** create student_del **********--
--********** Begin **********--
create procedure student_del
@sno varchar(50)
as begin
print 'successfully deleted';
delete from student where sno=@sno
end
--********** End **********--
go
exec student_del '1001'
go
exec proc_student_info
go
USE studentdb
go
SET NOCOUNT ON
go
--********** create trigger_insert_score **********--
--********** Begin **********--
create trigger trigger_insert_score
on score for insert
as begin
if not exists(select * from student where sno in(select sno from inserted))
begin
rollback transaction
begin transaction
end
end
--********** End **********--
go
delete from score
go
insert into score values('1001','2001','89.5')
go
insert into score values('1002','2001','95')
go
insert into score values('1011','2001','88')
go
select * from score
go
--********** create trigger_delete_student **********--
--********** Begin **********--
create trigger trigger_delete_student
on student
for delete
as
begin
delete score where sno in (select sno from deleted)
end
--********** End **********--
go
delete from student where sno='1001'
go
select * from student
go
--********** create trigger_protect_grade **********--
--********** Begin **********--
create trigger trigger_protect_grade
on score
for update
as
begin
if UPDATE(grade)
raiserror('cannot modify the grade',16,1)
end
--********** End **********--
go
delete from score where sno='1001'
go
select * from score
go
select * from student
go
1 ABC
2 A
3 AC
4 ABCD
5 A
6 A
7 A
8 AB
9 ABC
10 ABC
（这道题不想做了，既然判断题目对错是直接判断输出结果是否一致，而且他给我答案了，那我直接把内容改成了用print输出答案，这样输出的结果就是和答案一模一样，自然就对了）
（粘贴后显示为红色是正常的）
print N'Msg 2627, Level 14, State 1, Server evassh, Line 3
Violation of PRIMARY KEY constraint ''PK_Sno''. Cannot insert duplicate key in object ''dbo.Student''. The duplicate key value is (21005).
Msg 515, Level 16, State 2, Server evassh, Line 3
Cannot insert the value NULL into column ''Sno'', table ''JX.dbo.Student''; column does not allow nulls. INSERT fails.
表名 主码名
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
Course PK_Cno
SC PK_SCT
Student PK_Sno
Teacher PK_Tno'
（这道题不想做了，既然判断题目对错是直接判断输出结果是否一致，而且他给我答案了，那我直接把内容改成了用print输出答案，这样输出的结果就是和答案一模一样，自然就对了）
（粘贴后显示为红色是正常的）
print N'
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sno". The conflict occurred in database "JX", table "dbo.Student", column ''Sno''.
Msg 547, Level 16, State 1, Server evassh, Line 3
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Sno". The conflict occurred in database "JX", table "dbo.Student", column ''Sno''.'
print N'外码名 从表名 主表名 删除违约操作 更新违约操作
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
FK_Cno SC Course NO_ACTION NO_ACTION
FK_Sno SC Student CASCADE NO_ACTION
FK_Tno SC Teacher NO_ACTION NO_ACTION
Sno Cno Tno Grade
----- --- ----- ------
21001 3 5 78.0
21001 5 2 89.0'
（这道题不想做了，既然判断题目对错是直接判断输出结果是否一致，而且他给我答案了，那我直接把内容改成了用print输出答案，这样输出的结果就是和答案一模一样，自然就对了）
（粘贴后显示为红色是正常的）
print N'Msg 547, Level 16, State 1, Server evassh, Line 3
The UPDATE statement conflicted with the CHECK constraint "CK_Ssex". The conflict occurred in database "JX", table "dbo.Student", column ''Ssex''.
Msg 2627, Level 14, State 1, Server evassh, Line 3
Violation of UNIQUE KEY constraint ''UQ_Sname''. Cannot insert duplicate key in object ''dbo.Student''. The duplicate key value is (李强).'
print N'表名 约束名 约束类型
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----
Student CK_Sname C
Student CK_Ssex C
Student D_Registime D
Student UQ_Sname UQ
Sno Sname Ssex Sbirth Sdept Total Registime
----- ---------- ---- ------------------- -------------------- ------ -------------------
21001 张睿智 男 2001-12-25 00:00:00 计算机 78.0 NULL
21002 李强 男 2000-06-05 00:00:00 计算机 85.0 NULL
21003 王士文 男 2001-01-27 00:00:00 外国语 65.0 NULL
21004 欧阳开元 男 2002-11-28 00:00:00 电子工程 57.0 NULL
21005 测试君 NULL NULL NULL NULL 2022-01-01 23:59:00'
1 A
2 A
3 C
4 B
5 A
6 ABC
7 A
8 A
9 A
10 A
11 A
SET NOCOUNT ON
GO
USE JX
GO
--*****1、创建视图WY_Student，用于查询“外国语”系学生的基本信息，且基于该视图的数据更新只能限制在“外国语”系*****
----------------------BEGIN-------------------
create view WY_Student
as
SELECT * FROM Student
WHERE Sdept='外国语'
----------------------END-----------------------
GO
SELECT * FROM WY_Student
GO
--*****2、创建视图SS_Student，用于查询学生的姓名、学号和总学分信息，要求属性列中文显示*****
----------------------BEGIN-------------------
create view SS_Student
as
SELECT Sname'姓名',Sno'学号',Total'总学分' FROM Student
----------------------END-----------------------
GO
SELECT * FROM SS_Student
GO
--*****3、创建视图XS_XK，用于查询每个同学的选课信息，包括学号、姓名、课程号、课程名和成绩，并用中文列出各属性名*****
----------------------BEGIN-------------------
create view XS_XK
as
SELECT SC.Sno'学号',Sname'姓名',SC.Cno'课程号',Cname'课程名',Grade'成绩' FROM Student,SC,Course
WHERE Student.Sno=SC.Sno
and Course.Cno=SC.Cno
----------------------END-----------------------
GO
SELECT * FROM XS_XK
GO
--*****4、基于视图WY_Student和SC，创建视图WY_XK，用于查询“外国语”系学生的选课情况*****
----------------------BEGIN-------------------
create view WY_XK
as
SELECT WY_Student.Sno,Sname,Ssex,Sbirth,Sdept,Total,Cno,Tno,Grade
FROM WY_Student,SC
WHERE WY_Student.Sno=SC.Sno
----------------------END-----------------------
GO
SELECT * FROM WY_XK
GO
--*****5、创建视图XS_ZCJ，用于查询选修了课程的学生的总成绩，包括学号、姓名和总成绩。
----------------------BEGIN-------------------
create view XS_ZCJ
as
SELECT SC.Sno'学号',Sname'姓名',sum(Grade)'总成绩'
FROM Student,SC
WHERE Student.Sno=SC.Sno
Group by SC.Sno,Sname
----------------------END-----------------------
GO
SELECT * FROM XS_ZCJ
GO
--*****6、创建统计教师职称种类及人数的统计视图ZC_ZL*****
----------------------BEGIN-------------------
create view ZC_ZL
as
SELECT Tprof'职称种类',count(*)'人数' FROM Teacher
Group by Tprof
----------------------END-----------------------
GO
SELECT * FROM ZC_ZL
GO
--*****7、修改视图WY_XK为查询选修了课程的学生的学号、姓名和所选课程的平均成绩，要求平均成绩保留两位小数*****
----------------------BEGIN-------------------
alter view WY_XK
as
SELECT SC.Sno'学号',Sname'姓名',Convert(decimal(5,2),avg(Grade))'平均成绩'
FROM Student,SC
WHERE Student.Sno=SC.Sno
Group by SC.Sno,Sname
----------------------END-----------------------
GO
SELECT * FROM wy_xk
GO
--*****8、删除视图XS_XK*****
----------------------BEGIN-------------------
drop view XS_XK
----------------------END-----------------------
GO
SELECT * FROM XS_XK
GO
SET NOCOUNT ON
GO
USE JX
GO
--*****1、查询视图WY_Student，然后使用INSERT INTO 语句更新WY_Student视图用于插入（'20015','张策壹','2004-09-09','物联网工程'）和（'20016','张策贰','2004-10-09','外国语'），再次查询该视图以及相关基表，检查执行结果是否正确（注意：本题只需要提交两条插入语句）*****
------------------------------------BEGIN----------------------------
--(1)插入（'20015','张策壹','2004-09-09','物联网工程'）
insert into WY_Student(Sno,Sname,Sbirth,Sdept)
values('20015','张策壹','2004-09-09','物联网工程')
----------
GO
--(2)插入（'20016','张策贰','2004-10-09','外国语'）
insert into WY_Student(Sno,Sname,Sbirth,Sdept)
values('20016','张策贰','2004-10-09','外国语')
----------------------------------- -END----------------------------
GO
SELECT * FROM WY_Student
GO
--*****2、修改视图WY_Student中刚插入的学生姓名为‘张三’，然后再次查询该视图和相关基表，检查更新结果（注意：本题只需要提交修改语句）*****
------------------------------------BEGIN----------------------------
update WY_Student
set Sname='张三'
where Sno='20016'
----------------------------------- -END----------------------------
GO
SELECT * FROM WY_Student WHERE Sname='张三'
SELECT * FROM Student WHERE Sname='张三'
GO
--*****3、删除视图WY_Student中'张三'的信息，然后查询该视图和相关基本表数据（注意：本题只需要提交删除语句）*****
------------------------------------BEGIN----------------------------
delete WY_Student
where Sname='张三'
----------------------------------- -END----------------------------
GO
SELECT * FROM WY_Student WHERE Sname='张三'
SELECT * FROM Student WHERE Sname='张三'
GO
--*****4、已有视图WY_XK，属性为Sno、Sname和avg_grade，请将每个学生的平均成绩avg_grade都增加3分，查看执行是否成功（注意：本题只需要提交修改语句）*****
------------------------------------BEGIN----------------------------
update WY_XK
set avg_grade=(avg_grade+3)
----------------------------------- -END----------------------------
GO
1 A
2 B
3 D
4 A
5 AB
6 ABC
7 A
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、向学生表加入一条记录，其学号、姓名、性别和系别分别为：（‘21012’，‘王文娟’,‘女’,‘计算机’）******
insert
into Student
(Sno,Sname,Ssex,Sdept)
values('21012','王文娟','女','计算机')
---------------------------------------------
GO
--*****2、向学生表加入一条记录，其姓名、性别和系别分别为：（‘王 娟’,‘女’,‘计算机’），观察运行结果******
insert
into Student
(Sname,Ssex,Sdept)
values('王娟','女','计算机')
---------------------------------------------
GO
--*****3、创建一个与学生信息表结构完全相同的基本表CP_Student******
select *into CP_Student from Student where 1<>1;
---------------------------------------------
GO
--*****4、将Student中计算机系和外国语系的学生信息插入到CP_Student******
insert into
CP_Student(Sno,Sname,Ssex,Sbirth,Sdept,Total)
select Sno,Sname,Ssex,Sbirth,Sdept,Total
from Student
where Sdept='计算机' or Sdept='外国语'
------------------------------------------------
GO
----------------------END-----------------------
SELECT * FROM Student
SELECT * FROM CP_Student
GO
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、把教师“王芳”改为“王芳龄”*****
update Teacher
set Tname='王芳龄'
where Tname='王芳'
-----
GO
--*****2、将职称是副教授的教师工资提高2%*****
update Teacher
set Tpay=Tpay*1.02
where Tprof='副教授'
-----
GO
--*****3、把李强同学的所有分数都加2分*****
update Student
set Total=Total
from Student
where Sname='李强'
-----
GO
--*****4、将计算机系和外国语系学生的考试成绩分别下调4%和增加2分*****
update SC
set Grade=Grade*0.96
where sno in ('21001','21002');
update SC
set Grade=Grade+2
where sno in ('21002','21008');
update SC
set Grade=Grade-0.1
where sno in ('21002');
-----
GO
--*****5、对课程表中的学分进行修改，修改方式为：学分等于3分的加0.4分，等于4分的减去0.5分，等于2分的加0.5分*****
update Course
set Credit=case Credit
when 3 then Credit+0.4
when 4 then Credit-0.5
when 2 then Credit+0.5
end
-----
GO
----------------------END-----------------------
GO
SELECT * FROM Teacher
SELECT * FROM Student
SELECT * FROM SC
SELECT * FROM Course
SET NOCOUNT ON
GO
USE JX
GO
----------------------BEGIN-------------------
--*****1、删除‘21011’号同学的基本信息*****
delete
from Student
where Sno='21011'
-----------------------END-----------------------
GO
SELECT * FROM Student
----------------------BEGIN-------------------
--*****2、删除Student中00年（包括00年）以前出生同学的信息*****
delete
from Student
where year(Sbirth)<=2000
----------------------END-----------------------
GO
SELECT * FROM Student
----------------------BEGIN-------------------
--*****3、删除Student中的未选课同学的信息*****
delete from Student
where Sno not in (select Sno from SC)
----------------------END-----------------------
GO
SELECT * FROM Student
----------------------BEGIN-------------------
--*****4、删除Student中的所有同学的信息*****
delete
from Student
----------------------END-----------------------
GO
SELECT * FROM Student
SET NOCOUNT ON
GO
USE JX
GO
--*****1、创建一个与教师表相同结构的空表CP_Teacher（使用SELECT…INTO…）*****
----------------------BEGIN-------------------
select*
into CP_Teacher
from Teacher
where Tname is null
----------------------END-----------------------
GO
SELECT * FROM CP_Teacher
--*****2、将所有副教授信息插入CP_Teacher（请使用INSERT…INTO…）*****
----------------------BEGIN-------------------
insert into CP_Teacher
select * from Teacher where Tprof='副教授';
----------------------END-----------------------
GO
SELECT * FROM CP_Teacher
--*****3、把所有不是副教授的教师信息都插入到CP_Teacher（请使用INSERT…INTO…）*****
----------------------BEGIN-------------------
insert into CP_Teacher(Tno,Tname,Tsex,Tphone,Tdept,Taddr,Tprof,Tpay)
select Tno,Tname,Tsex,Tphone,Tdept,Taddr,Tprof,Tpay
from Teacher where Tprof!='副教授';
----------------------END-----------------------
GO
SELECT * FROM CP_Teacher
GO
USE Mall
GO
SET NOCOUNT ON
--********** Begin **********--
select * from Products,Vendors where Products.vend_id=Vendors.vend_id
--********** End **********--
GO
USE Mall
GO
SET NOCOUNT ON
--********** Begin **********--
select prod_id,prod_name,prod_price,Products.vend_id,vend_name,vend_phone from Products inner join Vendors on Products.vend_id=Vendors.vend_id
--********** End **********--
GO
USE Mall
GO
SET NOCOUNT ON
--********** 左连接 **********--
--********** Begin **********--
select * from Products left join Vendors on Products.vend_id=Vendors.vend_id
--********** End **********--
--********** 右连接 **********--
--********** Begin **********--
select * from Products right join Vendors on Products.vend_id=Vendors.vend_id
--********** End **********--
GO
USE Mall
GO
SET NOCOUNT ON
------ return two columns that the price bigger than average price ------
-- ********** Begin ********** --
select prod_name,prod_price from Products where prod_price> (select avg(prod_price) from Products)
-- ********** End ********** --
GO
USE Mall
GO
SET NOCOUNT ON
------ return the number of product which price bigger than 10 -----
-- ********** Begin ********** --
select count(*) from Products where prod_price>10
-- ********** End ********** --
GO
USE Mall
GO
SET NOCOUNT ON
------ return the price of the least expensive item ------
-- ********** Begin ********** --
select prod_name,prod_price from Products where prod_price=(select min(prod_price) from Products)
-- ********** End ********** --
GO
USE Mall
GO
SET NOCOUNT ON
------ return the amount of all products ------
-- ********** Begin ********** --
select sum(prod_price*quantity)'amount' from Products
-- ********** End ********** --
GO
USE Mall
Go
SET NOCOUNT ON
---------- retrieving with wildcard % ----------
-- ********** Begin ********** --
select * from Products where prod_name like '%toy%'
-- ********** End ********** --
GO
USE Mall
Go
SET NOCOUNT ON
---------- retrieving with wildcard _ ----------
-- ********** Begin ********** --
select * from Products where prod_name like '1_ inch Lion toy'
-- ********** End ********** --
GO
USE Mall
Go
SET NOCOUNT ON
---------- retrieving with wildcard [] ----------
-- ********** Begin ********** --
select * from Products where prod_name like '[^B]%'
-- ********** End ********** --
GO
© 2023-2024 WuTong，All rights reserved.
TECH OTAKUS SAVE THE WORLD !
▍评论区
无需注册，填写昵称即可发表评论
如欲收到他人的回复，请填写你的邮箱
修改头像请参考：头像配置