Case nested query and connection Query

1,CaseSubquery connection Query
Copy codeThe Code is as follows:
Select * from score
Create database demo
Use demo
Create table [user]
(
[UId] int identity (1, 1) primary key,
[Name] varchar (50 ),
[Level] int -- 1 ashes prawn cainiao
)
Insert into [user] (name, level) values ('sirge', 1)
Insert into [user] (name, level) values ('month', 2)
Insert into [user] (name, level) values ('Sister Furong ', 3)
-- Case end Single-value judgment is equivalent to switch case
-- The return value types after then must be consistent
Select [name],
Case [level]
When 1 then 'creden'
When 2 then 'prawns'
When 3 then 'cainiao'
End as 'level'
From [user]
Use MySchool
Select * from score
-- Case end: the second usage is equivalent to multiple if statements.
Select studentId,
Case
When english> = 90 then 'out'
When english> = 80 and english <90 then 'liang'
When english> = 70 and english <80 then '中'
When english> = 60 and english <70 then 'to'
Else 'bad'
End as 'score'
From score
Order by english

-- The table has three columns a B c, which are implemented using SQL statements: If Column A is greater than Column B, select column A; otherwise, select Column B, if column B is greater than column C, column B is selected; otherwise, column C is selected.
Select
Case
When a> B then
Else B
End,
Case
When B> c then B
Else c
End
From T

-- Exercise
Create table test
(
Number varchar (10 ),
Amount int
)
Insert into test (number, amount) values ('rk1 ', 10)
Insert into test (number, amount) values ('rk2', 20)
Insert into test (number, amount) values ('rk3 ',-30)
Insert into test (number, amount) values ('rk4 ',-10)
Select number,
Case
When amount> 0 then amount
Else 0
End as 'revenue ',
Case
When amount <0 then abs (amount)
Else 0
End as 'output'
From test
-- The result is as follows:


Copy codeThe Code is as follows:
-- There is a table student0, which records students' scores
Use demo
Create table student0 (name nvarchar (10), subject nvarchar (10), result int)
Insert into student0 VALUES ('zhang san', 'China', 80)
Insert into student0 VALUES ('zhang san', 'mat', 90)
Insert into student0 VALUES ('zhang san', 'Physical ', 85)
Insert into student0 VALUES ('Li si', 'China', 85)
Insert into student0 VALUES ('Li si', 'mat', 92)
Insert into student0 VALUES ('lily', 'Physical ', null)
Select * from student0
Select [name],
Isnull (sum (case subject
When 'China' then result
End), 0) as 'China ',
Isnull (sum (case subject
When 'mate' then result
End), 0) as 'mat ',
Isnull (sum (case subject
When 'physical 'then result
End), 0) as 'physical'
From student0
Group by [name]


Copy codeThe Code is as follows:
-- A subquery uses a query statement as a result set for other SQL statements, just like using a common table,
-- The query statement used as the result set is called a subquery. Almost all tables that can be used can be replaced by subqueries.
Use myschool
Select sName from (select * from student) as t
Select 1, (select sum (english) from score) as 'and', (select avg (sAge) from student) as 'average age'
-- Query all students in the first class
Select * from student where sClassId =
(Select cId from class where cName = 'higher than one class ')
-- Query all the students in the first class and second class.
-- More than one value is returned by the subquery. When the subquery follows in = ,! =, <, <=,>,> =
-- After a subquery is followed by a comparison operator, the subquery must return only one value.
-- If a subquery is a multi-row, single-column subquery, The result set of this subquery is actually a set. You can use the in keyword instead of the = sign.
Select * from student where sClassId =
(Select cId from class where cName in ('higher than one class', 'higher than two class '))
Select * from student where sClassId in
(Select cId from class where cName in ('higher than one class', 'higher than two class '))
-- Query Liu guanzhang's score
Select * from score where studentId in
(Select sId from student where sName in ('Liu bei ', 'guan Yu', 'zhang fei '))
-- Delete Liu guanzhang
Delete from score where studentId in
(Select sId from student where sName in ('Liu bei ', 'guan Yu', 'zhang fei '))

-- Implement Paging
-- Recently enrolled students
Select top 3 * from student
Order by sId desc
-- Query the first student
Select top 3 * from student
Where sId not in (select top 3 sId from student order by sId desc)
Order by sId desc
-- The queried student
Select top 3 * from student
Where sId not in (select top 6 sId from student order by sId desc)
Order by sId desc
-- The above is the implementation method before SQL 2000. The simplified implementation of the Row_Number function is added after SQLServer2005.
-- Paging in SQL 2005
Select * from
(Select row_number () over (order by sId desc) as num, * from student) as t
Where num between 1 and 3
Select * from
(Select row_number () over (order by sId desc) as num, * from student) as t
Where num between 4 and 6
Select * from
(Select row_number () over (order by sId desc) as num, * from student) as t
Where num between 7 and 9
Select * from
(Select row_number () over (order by sId desc) as num, * from student) as t
Where num between 3 * (3-1) + 1 and 3*3
-- Table join
-- Cross join
Select * from student
Cross join class
-- Inner join... on...
Select * from student
Inner join class on sClassId = cId
Select * from class
-- Query the names, ages, and classes of all students.
Select sName, sAge, cName, sSex from student
Inner join class on sClassId = cId
Where sSex = 'female'
-- Query the names, ages, and classes of students over the age.
Select sName, sAge, cName from class
Inner join student on sClassId = cId
Where sAge> 20
-- External Connection
-- Left join... on...
Select sName, sAge, cName from class