Copy codeThe Code is as follows:
-- Execution sequence From Where Select
Select * from
(Select sal as salary, comm as commission from emp) x where salary <5000
-- Get Name Work as a Job
Select ename + 'work as a' + job as msg from emp where deptno = 10
-- If the employee's salary is less than 2000, the system returns "OK" between the OverPaid values when the UnderPaid is greater than or equal to 4 k.
Select ename, sal,
Case when sal <2000 then 'underpaid'
When sal> = 4000 then 'overpaid'
Else
'OK'
End
From emp
-- Returns N records newid () randomly from the table ()
-- When the order by clause specifies a numerical constant, it must be sorted according to the column at the corresponding position in the select list.
-- When a function is used in the order by clause, the result is sorted by the function in the calculation result of no row.
Select top 5 ename from emp order by newid ()
-- Null value is null
Select * from emp where comm is null
-- Convert null values to actual values
-- Explanation: return the first non-null expression in the parameter.
-- Coalesce combination, merge, and combine. English: [, k} u records 'Les] American: [, ko records 'l records s]
Select coalesce (comm, 1), empNo from emp
-- Search by Mode
-- Returns the row that matches the specified substring or pattern.
Select ename, job
From emp
Where deptno in (10, 20)
-- Sort by substrings by the last two characters in 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
-- ☆☆☆☆☆☆Process sorting null values ☆☆☆☆☆[ 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. Put the null values to the end. You can use case
Select ename, sal, comm from
(
Select ename, sal, comm,
Case when comm is null then 0 else 1 end as
From emp
) X
Order by A desc, comm desc