--创建一个学生信息表，包含字段：学号，姓名，班级，性别，年龄，所属省份，入学时间等；
--创建一个学生成绩表，包含字段：学号（关联学生信息表），数学成绩，语文成绩，英语成绩，选修课，选修课成绩等；
if exists (select 1
from sysindexes
where id = object_id('tStu')
and name = 'Index_1'
and indid > 0
and indid < 255)
drop index tStu.Index_1
go
if exists (select 1
from sysobjects
where id = object_id('tStu')
and type = 'U')
drop table tStu
go
/*==============================================================*/
/* Table: tStu */
/*==============================================================*/
create table tStu (
fId int not null,
fName varchar(20) not null,
fClass varchar(20) null,
fSex varchar(10) null,
fAge bigint null,
fAddress varchar(250) null,
fDate datetime null,
constraint PK_TSTU primary key (fid)
)
go
/*==============================================================*/
/* Index: Index_1 */
/*==============================================================*/
create unique index Index_1 on tStu (
fName ASC
)
go
DROP TABLE IF EXISTS tAch;
create table tAch (
fId int not null,
fMath int not null,
fChinese int null,
fEnglish int null,
fElectives varchar(50) null,
fElectivesGrade int null
)
--2. 新增学生
insert into tStu values (1,'张三','初一','男',15,'合肥','2021-04-21');
insert into tStu values (2,'*四','初一','男',15,'合肥','2020-01-02');
insert into tStu values (3,'王二麻','初二','女',16,'上海','2018-07-02');
insert into tStu values (4,'***','初三','女',17,'北京','2017-07-02');
insert into tStu values (5,'张六','初三','男',18,'北京','2017-07-02');
insert into tAch values (1,80,70,60,'体育',60);
insert into tAch values (2,90,77,63,'美术',80);
insert into tAch values (3,65,98,87,'体育',56);
insert into tAch values (4,80,78,77,'音乐',63);
insert into tAch values (5,70,77,89,'数据库技术',90);
--3. 统计学生信息表，查询学生“张三”的全部基本信息；
select * from tStu where fName = '张三';
--4. 统计学生信息表，查询学生“张三”和‘*四’的基本信息；
select * from tStu where fName = '张三' or fName = '*四';
--5. 统计学生信息表，查询姓“张”学生的基本信息；
select * from tStu where fName like '张%';
--6. 统计学生信息表，查询姓名中含有“四”字的学生的基本信息；
select * from tStu where fName like '%四%';
--7. 统计学生信息表，查询姓名长度为三个字，姓“*”，且最后一个字是“*”的全部学生信息；
select * from tStu where len(fName) = 3 and fName like '*%*';
--8. 统计学生信息表，查询姓“张”或者姓“*”的学生的基本信息；
select * from tStu where fName like '张%' or fName like '*%';
--9. 统计学生信息表，查询姓“张”并且所属省份是“北京”的学生信息；
select * from tStu where fName like '张%' and fAddress = '北京';
--10. 统计学生信息表，查询所属省份是“北京”、“上海”或者“山东”的学生的信息；
select * from tStu where fAddress in ( '北京','上海','山东');
--11. 统计学生信息表，查询姓“张”，但所属身份不是“北京”的学生信息；
select * from tStu where fAddress != '北京' AND fName like '张%';
--12. 统计学生信息表，查询全部学生信息，并按照“性别”排序，性别相同的情况下按照“所属省份”排序，所属省份相同的情况下再按照“班级”排序；
select * from tStu order by fSex,fAddress,fClass ;
--13. 统计学生信息表，查询现有学生都来自于哪些不同的省份；
select distinct fAddress from tStu;
--（2） 聚合函数练习
--1. 统计学生信息表，年龄大于15岁的学生有多少个；
select COUNT(*) from tStu where fAge > 15;
--2. 统计学生信息表，入学时间在2017年至2018年的学生人数；
select COUNT(*) from tStu where fDate between '2017-01-01' and '2018-12-31';
--3. 统计学生成绩表，学号为“1”的学生的平均成绩；
select (fEnglish+fChinese+fElectivesGrade+fMath)/4 as '平均成绩'
from tAch where fId = 1;
--4. 统计学生成绩表，学号为“1”的学生的总成绩；
select fEnglish+fChinese+fElectivesGrade+fMath as [Sum] from tAch where fId = 1;
select isnull(fMath,0)+isnull(fChinese,0)+isnull(fEnglish,0)
+isnull(fElectivesGrade,0) as [Sum] from tAch where fId = 1;
--5. 查询学生信息表，所有学生中的最大年龄是多少；
select MAX(fAge) as '最大年龄' from tStu;
--（3） 分组查询练习
--1、 统计学生成绩表，每个学生的总成绩；
select s.fId,s.fName,(isnull(fMath,0)+isnull(fChinese,0)+isnull(fEnglish,0)
+isnull(fElectivesGrade,0)) as '总成绩' from tStu s,tAch a where s.fId = a.fId;
--2、 统计学生信息表，每个班级中每种性别的学生人数，并按照班级排序；
select fClass,fSex,COUNT(*) as '人数' from tStu group by fSex,fClass order by fClass;
--3、 统计学生成绩表，每个学生的平均成绩，并按照成绩降序排序；
select s.fId,s.fName,(fEnglish+fChinese+fElectivesGrade+fMath)/4 as '平均成绩'
from tStu s,tAch a where s.fId = a.fId
order by (fEnglish+fChinese+fElectivesGrade+fMath)/4 desc;
--4、 统计学生成绩表，查询有一门以上课程不及格的学生的学号；
select a.fId from tAch a where
a.fChinese < 60 or a.fElectivesGrade<60 or a.fEnglish<60 or a.fMath<60 ;
--（4） 嵌套查询练习
--1、 用子查询实现，查询选修“数据库技术”的全部学生 的总成绩；
select SUM(fElectivesGrade)+SUM(fChinese)+SUM(fMath)+SUM(fEnglish)
as '选修“数据库技术”的全部学生的总成绩' from tAch
where fElectives = '数据库技术' ;
--2、 统计学生成绩表，查询学号为“1”的学生在其各科成绩中，最高分成绩所对应的课程和成绩；
select 学科,成绩 from (select fmath AS 成绩,'数学' as 学科 from tAch where fId=1
union all
select fChinese AS 成绩,'语文'as 学科 from tAch where fId=1
union all
select fEnglish AS 成绩,'英语'as 学科 from tAch where fId=1
union all
select fElectivesGrade AS 成绩,fElectives as 学科 from tAch where fId=1)a where a.成绩=(select max(成绩) from (select fmath AS 成绩,'数学' as 学科 from tAch where fId=1
union all
select fChinese AS 成绩,'语文'as 学科 from tAch where fId=1
union all
select fEnglish AS 成绩,'英语'as 学科 from tAch where fId=1
union all
select fElectivesGrade AS 成绩,fElectives as 学科 from tAch where fId=1)a);
--3、 查询1班选修“数据库技术”的所有学生的成绩之和；
select SUM(a.fElectivesGrade)+SUM(a.fChinese)+SUM(a.fMath)+SUM(a.fEnglish)
as '选修“数据库技术”的全部学生的总成绩' from tAch a,tStu s
where a.fId = s.fId and a.fElectives = '数据库技术' and s.fClass='初三';
--（5） 外联接查询
--1、 查询1班“张三”的“数学成绩”，显示姓名、班级和成绩；
select s.fName,a.fMath,s.fClass from tStu s,tAch a where s.fId = a.fId and s.fName = '张三';
--2、 查询张三所有课程的成绩，并显示学号、姓名、数学成绩、语文成绩和英语成绩，没有成绩记录的显示为0；
select s.fId,fName,isnull(fMath,0) as '数学',isnull(fChinese,0) as '语文',
isnull(fEnglish,0) as '英语' from tStu s,tAch a
where s.fId = a.fId and s.fName = '张三';
--3、 查询入学时间为去年的学生的所有课程总成绩，并显示学号、姓名、班级、课程总成绩；
select s.fId,s.fName,s.fClass,a.fEnglish+a.fChinese+a.fElectivesGrade+a.fMath as '课程总成绩'
from tStu s,tAch a where s.fId = a.fId and year(s.fDate) = year(GETDATE())-1;