/*学生表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、
年龄(Sage)、所在系(Sdept)五个属性组成，
记为： Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 为主键。 
课程表Course由课程号(Cno)、课程名(Cname)、选修课号(Cpno)、学分(Ccredit)
四个属性组成，记为：Course(Cno,Cname,Cpno,Ccredit) Cno为主键。  
成绩表SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，
记为： SG(Sno,Cno,Grade) (SNO, CNO)为主键。 */
CREATE TABLE Student
(Sno CHAR(4) NOT NULL PRIMARY KEY,
Sname VARCHAR(20),
Ssex CHAR(2) DEFAULT '男',
Sage INT ,
Sdept VARCHAR(20))
CREATE TABLE Course
(Cno CHAR(4) NOT NULL PRIMARY KEY,
Cname VARCHAR(20),
Cpno CHAR(4),
Ccredit INT
)
CREATE TABLE SG(
Sno CHAR(4) NOT NULL,
Cno CHAR(4) NOT NULL 
PRIMARY KEY (Sno,Cno),
Grade INT )

INSERT INTO dbo.Student( Sno, Sname, Ssex, Sage, Sdept )
VALUES  ( '0001','李四','男',18, '计算机'),
    ('0002','张三','女',18,'计算机'),
    ('0003','王五','男',19,'物理');
INSERT INTO dbo.Course( Cno, Cname, Cpno, Ccredit )
VALUES  ( '0134','高等数学',NULL,5);
INSERT INTO dbo.SG( Sno, Cno, Grade )
VALUES  ('0001','0134',89),
    ('0002','0134',97),
    ('0003','0134',88);
--查询各院系选修课程为"电子竞技"
--(课程号为‘0134’)的男女比例是多少？
SELECT Sdept AS '院系名称',
COUNT(CASE WHEN S.Ssex='男' THEN 1 ELSE NULL END) AS '男生人数',
COUNT(CASE WHEN S.Ssex='女' THEN 1 ELSE NULL END) AS '女生人数'
FROM dbo.Student S
WHERE Sno IN 
(SELECT Sno FROM dbo.SG 
JOIN dbo.Course c ON c.Cno = SG.Cno
WHERE c.Cno='0134')
GROUP BY S.Sdept;

--方法二
SELECT Sdept AS '院系名称',
SUM(CASE WHEN S.Ssex='男' THEN 1 ELSE 0 END) AS '男生人数',
SUM(CASE WHEN S.Ssex='女' THEN 1 ELSE 0 END) AS '女生人数'
FROM dbo.Student S
WHERE Sno IN 
(SELECT Sno FROM dbo.SG 
JOIN dbo.Course c ON c.Cno = SG.Cno
WHERE c.Cno='0134')
GROUP BY S.Sdept;

DROP TABLE dbo.Student
DROP TABLE dbo.Course
DROP TABLE dbo.SG

--查询各院系选修课程为"电子竞技"
--(课程号为‘0134’)的男女比例是多少？
SELECT Sdept AS '院系名称',
COUNT(CASE WHEN S.Ssex='男' THEN 1 ELSE 0 END) AS '男生人数',
COUNT(CASE WHEN S.Ssex='女' THEN 1 ELSE 0 END) AS '女生人数'
FROM dbo.Student S
WHERE Sno IN 
(SELECT Sno FROM dbo.SG 
JOIN dbo.Course c ON c.Cno = SG.Cno
WHERE c.Cno='0134')
GROUP BY S.Sdept;