求解答数据库基础SQLserver2⼀3⼀4题

SQL代码如下：

1.
CREATE TABLE Course
(
Cno	VARCHAR(3)	PRIMARY KEY,
Cname	VARCHAR(30),
Cpno	VARCHAR(3)
)

CREATE TABLE Student
(
Sno		VARCHAR(7)	PRIMARY KEY,
Sname		VARCHAR(20),
Ssex		CHAR(2),
birthday	DATE,	
Sdept		VARCHAR(15)
)

CREATE TABLE SC
(
Sno	VARCHAR(7),	
Cno	VARCHAR(3),
Grade	INT,
CONSTRAINT pk_SC_Sno_Cno PRIMARY KEY(Sno, Cno)
)

2.
SELECT (SELECT	COUNT(*)
	FROM	Student)
	AS '学生总人数',

	(SELECT	COUNT(*)
	FROM	Student
	WHERE	Sno IN (
		SELECT	DISTINCT Sno
		WHERE	SC
	))
	AS '选修总人数'

3.
SELECT	Sno, Sname, Cno, Cname, Grade
FROM	Student s
JOIN	SC
ON	s.Sno = SC.Sno
JOIN	Course c
ON	c.Cno = SC.Cno

4.
SELECT	AVG(Grade)
FROM	SC
WHERE	Cno = '001'