Copy Code code as follows:
--Execution order from Where Select
SELECT * FROM
(select Sal as salary,comm as commission from EMP) x where salary<5000
--Draw Name Work as a Job
Select Ename + ' Work as a ' +job as msg from EMP where deptno=10
--If the employee pay less than 2000 returns underpaid greater than or equal to 4k return OK between overpaid
Select Ename,sal,
Case when sal<2000 then ' underpaid '
When sal>=4000 then ' overpaid '
Else
' OK '
End
From EMP
--Random return of N records from a table newid ()
When specifying a numeric constant in the--order by clause, it requires that the column be sorted according to the corresponding position in the select list
--order a function in the By clause, the result is sorted by function
Select Top 5 ename from emp Order by NEWID ()
--NULL for null value
SELECT * FROM EMP where comm is null
--Converts a null value to an actual value
--Explanation: Returns the first non-empty expression in its argument
--coalesce Unite, combine, combine. English: [, kəuə ' Les] American: [, koə ' lɛs]
Select COALESCE (comm, 1), EmpNo from EMP
--Search by mode
--Returns rows that match a specific substring or pattern
Select Ename,job
From EMP
where Deptno in (10,20)
--Sort by substring by the last two characters of the position field
Select ename, job from emp ORDER by substring (Job,len (Job) -2,2)
--select Top 2 len (Job)-2 from EMP
--select top 2 job from EMP
--☆☆☆☆☆ processing sort null value ☆☆☆☆☆[can only be greater than 0]
Select Ename, Sal,comm
From EMP
ORDER BY 1 Desc
--Sort non-null values in descending or ascending order, putting null values at the end, you can use the case
Select Ename,sal,comm from
(
Select Ename, Sal,comm,
Case when comm are null then 0 else 1 end as A
From EMP
) x
ORDER BY A DESC, Comm Desc
