文章出處
文章列表
USE master GO IF EXISTS(SELECT * FROM sysdatabases WHERE name='DB_StudentInfoManagement') DROP DATABASE DB_StudentInfoManagement GO CREATE DATABASE DB_StudentInfoManagement GO USE DB_StudentInfoManagement GO IF EXISTS(SELECT * FROM sysobjects WHERE name='Student') DROP TABLE Student GO CREATE TABLE Student ( S# INT PRIMARY KEY ,--學號 Sname NVARCHAR(100) NOT NULL,--姓名 Sage INT NOT NULL,--年齡 Ssex NVARCHAR(10) NOT NULL--性別 ) GO IF EXISTS(SELECT * FROM sysobjects WHERE name='Teacher') DROP TABLE Teacher GO CREATE TABLE Teacher ( T# INT PRIMARY KEY ,--教師編號 Tname NVARCHAR(50) NOT NULL,--教師名 ) GO IF EXISTS(SELECT * FROM sysobjects WHERE name='Course') DROP TABLE Course GO CREATE TABLE Course ( C# INT PRIMARY KEY ,--課程號 Cname NVARCHAR(50) NOT NULL,--課程名 T# INT NOT NULL REFERENCES Teacher(T#)--教師編號 ) GO IF EXISTS(SELECT * FROM sysobjects WHERE name='SC') DROP TABLE SC GO CREATE TABLE SC ( C# INT NOT NULL REFERENCES dbo.Course(C#),--課程號 S# INT NOT NULL REFERENCES dbo.Student(S#),--學號 Score NVARCHAR(50) NOT NULL,--分數 CONSTRAINT PK_SC PRIMARY KEY(C#,S#) )
--插入測試數據
INSERT INTO dbo.Student SELECT '1' , N'劉一' , '18' , N'男' UNION ALL --不會去除重復數據，Union會自動去除重復數據 SELECT '2' , N'錢二' , '19' , '女' UNION ALL SELECT '3' , N'張三' , '17' , N'男' UNION ALL SELECT '4' , N'李四' , '18' , N'女' UNION ALL SELECT '5' , N'王五' , '17' , N'男' UNION ALL SELECT '6' , N'趙六' , '19' , N'女'; INSERT INTO Teacher SELECT '1' , N'葉平' UNION ALL SELECT '2' , N'賀高' UNION ALL SELECT '3' , N'楊艷' UNION ALL SELECT '4' , N'周磊'; INSERT INTO Course SELECT '1' , N'語文' , '1' UNION ALL SELECT '2' , N'數學' , '2' UNION ALL SELECT '3' , N'英語' , '3' UNION ALL SELECT '4' , N'物理' , '4'; INSERT INTO SC SELECT 1 , 1 , 56 UNION ALL SELECT 1 , 2 , 78 UNION ALL SELECT 1 , 3 , 67 UNION ALL SELECT 1 , 4 , 58 UNION ALL SELECT 2 , 1 , 79 UNION ALL SELECT 2 , 2 , 81 UNION ALL SELECT 2 , 3 , 92 UNION ALL SELECT 2 , 4 , 68 UNION ALL SELECT 3 , 1 , 91 UNION ALL SELECT 3 , 2 , 47 UNION ALL SELECT 3 , 3 , 88 UNION ALL SELECT 3 , 4 , 56 UNION ALL SELECT 4 , 2 , 88 UNION ALL SELECT 4 , 3 , 90 UNION ALL SELECT 4 , 4 , 93 UNION ALL SELECT 5 , 1 , 46 UNION ALL SELECT 5 , 3 , 78 UNION ALL SELECT 5 , 4 , 53 UNION ALL SELECT 6 , 1 , 35 UNION ALL SELECT 6 , 2 , 68 UNION ALL SELECT 6 , 4 , 71;
--1、查詢“001”課程比“002”課程成績高的所有學生的學號； --先查出001號，002號課程的學號和成績 --在使用子查詢連表查詢 SELECT a.S# FROM ( SELECT S# , Score FROM dbo.SC WHERE C# = '001' ) AS a , --001號課程的學號和成績 ( SELECT S# , Score FROM dbo.SC WHERE C# = '002' ) AS b ----002號課程的學號和成績 WHERE a.S# = b.S# AND a.Score > b.Score;
驗證：
SELECT S# ,
Score
FROM dbo.SC
WHERE C# = '001'
SELECT S# ,
Score
FROM dbo.SC
WHERE C# = '002'
--2、查詢平均成績大于60分的同學的學號和平均成績； --Having子句中，不能使用聚合函數的別名【錯誤答案：列名 'AvgScore' 無效。】 SELECT S#,AVG(Score)AS AvgScore FROM dbo.SC GROUP BY S# HAVING AvgScore>60
--正確答案： SELECT S#,AVG(Score)AS AvgScore FROM dbo.SC GROUP BY S# HAVING AVG(Score)>60
--3、查詢所有同學的學號、姓名、選課數、總成績； --這里要注意的是，并不是所有學生都選課了，所以要用到外連接， --因為是要查詢所有學生的學號，姓名，，也就是不管該學生，選沒選課，都要查出來，所以左表是Student，用左連接 SELECT S.S# , S.Sname , COUNT(SC.C#) AS SelectCourseCount , SUM(SC.Score) AS TotalScore FROM dbo.Student AS S LEFT JOIN dbo.SC AS SC ON SC.S# = S.S# GROUP BY S.S#,S.Sname;
--4、查詢姓“李”的老師的個數； --分析，用到模糊查詢,只要姓李就行，后面幾個字的名，不管 --'%代表任意字符的字符串，_代表單個字符' --同名的也算，這是明顯的。網上的答案是錯誤的。 --自己的答案 SELECT COUNT(T#) FROM dbo.Teacher WHERE Tname LIKE '李%'; --網上的答案：同名的也算，網上的答案是錯誤的了 SELECT COUNT(DISTINCT ( Tname )) FROM Teacher WHERE Tname LIKE '李%';
驗證：
--測試
--SELECT * FROM dbo.Teacher WHERE Tname LIKE '李%';
--5、查詢沒學過“葉平”老師課的同學的學號、姓名； --分析：先查出學過葉平老師課的學生學號，再求出Student表中學號不在這其中的就是了 SELECT S#,Sname FROM dbo.Student WHERE S# NOT IN ( --學過葉平老師課的學生學號 SELECT DISTINCT( SC.S#) FROM dbo.Course AS C INNER JOIN dbo.Teacher AS T ON T.T# = C.T# INNER JOIN dbo.SC AS SC ON SC.C# = C.C# WHERE T.Tname='葉平' )
--6、查詢學過“001”并且也學過編號“002”課程的同學的學號、姓名； --查出學過001號課程的 SELECT s.S# , Sname FROM dbo.Student AS s RIGHT JOIN dbo.SC ON SC.S# = s.S# WHERE C# = '001' AND EXISTS ( SELECT * FROM SC AS SC2 LEFT JOIN dbo.Student AS s ON s.S# = SC2.S# WHERE SC2.C# = '002' );
未完待整理...........
文章列表
全站熱搜
留言列表
