4.23sql学习

-------4.23
select * from employees

--使用CASE
SELECT EmployeeID,BirthDate,'是否成年人'=
                                 CASE
                                 WHEN BirthDate<='1950-12-30' THEN '是'
                                 WHEN BirthDate>='1951-01-01' THEN '否 '
                                 END
FROM Employees
GO

--1992-05-01 00:00:00.000

SELECT EmployeeID,BirthDate,
CASE WHEN BirthDate<='1950-12-30' THEN '是' WHEN BirthDate>='1951-01-01' THEN '否 ' END as '是否成年人'
FROM Employees

 

 

 

---建立索引
USE New
GO
CREATE TABLE emp_pay
(employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2,2) NOT NULL)
GO

CREATE UNIQUE CLUSTERED INDEX employeeID_ind
ON emp_pay(employeeID)
GO

--为管理岗位业务培训信息，建立3个表:

--S (S#,SN,SD,SA)   S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

--C (C#,CN )        C#,CN       分别代表课程编号、课程名称

--SC ( S#,C#,G )    S#,C#,G     分别代表学号、所选修的课程编号、学习成绩

USE New
GO
CREATE TABLE S
(S# varchar(20) PRIMARY KEY,
 SN varchar(30),
 SD varchar(50),
 SA int)

CREATE TABLE C
(C# varchar(20) PRIMARY KEY,
 CN varchar(50))

CREATE TABLE SC
(S# varchar(20),
 C# varchar(20),
 G int)
GO

--要求实现如下5个处理：

 -- 1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
SELECT S.S#,SN FROM S,C,SC WHERE C.CN='税收基础'AND C.C#=SC.C# AND SC.C#=S.C#

SELECT S#,SN FROM S WHERE S# IN (SELECT S# FROM C,SC WHERE C.CN='税收基础'AND C.C#=SC.C#)
 
 -- 2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位 
SELECT SN,SD FROM S,SC WHERE S.S#=SC.S# AND SC.C#='C2'

SELECT SN,SD FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#='C2')

-- 3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位 
SELECT SN,SD FROM S,SC WHERE S.S#=SC.S# AND SC.C#!='C5'

SELECT SN,SD FROM S WHERE S# NOT IN(SELECT S# FROM SC WHERE C#='C5')

 -- 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
SELECT SN,SD FROM S
WHERE NOT EXISTS(
                  SELECT * FROM C WHERE NOT EXISTS(
                                                    SELECT * FROM C,SC WHERE
                                                                     SC.S#=S.S# AND C.C#=SC.C#))

 

SELECT SN,SD FROM S
WHERE S#  IN(
SELECT S# FROM SC
RIGHT JOIN C ON SC.C#=C.C#
GROUP BY S#
HAVING COUNT(*)=COUNT(DISTINCT S#))

SELECT SN,SD FROM S
WHERE S#  IN(
SELECT S# FROM SC
RIGHT JOIN C ON SC.C#=C.C#
GROUP BY S#
HAVING COUNT(*)=(select count(*)   from c))

 

select s.sn,s.sd from s where
s# in (select s# from sc group by s# having count(*) =(select count(*)   from c))

SELECT SN,SD FROM S
WHERE S# IN(
            SELECT S# FROM SC
                             GROUP BY S#
                             HAVING COUNT(DISTINCT C#)=(select count(*)   from c))

 -- 5. 查询选修了课程的学员人数
SELECT COUNT(DISTINCT SC.S#) AS '选修了课程的学员人' FROM SC

 -- 6. 查询选修课程超过5门的学员学号和所属单位
SELECT SN,SD FROM S
WHERE S# IN(
            SELECT S# FROM SC
                             GROUP BY S#
                             HAVING COUNT(DISTINCT C#)>5)