Orcal_ SQL writing (we have known each other in Series 5)

We were familiar with Series 5

We continue with the series that started eight months ago.

Many of my friends will find it difficult to write a simple SQL statement during the interview. It's probably all about Orm. I am no exception.

Website with basic knowledge: w3school

Subject:

1. UseDecodeThe function can avoid scanning the same record repeatedly or connecting the same table repeatedly.

Decode (value, if1, then1, if2, then2, if3, then3,..., else)

Decode (if this is the object to be compared, the result is this; otherwise, the result is this ...)

The benefit of this function is that it can implement the if logic. As a query statement, this capability can be added to handle many problems.

Example:

SelectDecode (Sign(Salary- 8000),1, Salary*1.15,-1, Salary*1.2, Salary*1.15)FromEmployee;

2,SignFunction

Purpose: Take the number n. If it is greater than 0, 1 is returned. If it is less than 0,-1 is returned. If it is equal to 0, 0 is returned.

Select Sign(100),Sign(-100),Sign(0)FromDual;

3,NvlFunction

Nvl (string1, replace_with)

Purpose: If string1 is null, The nvl function returns the value of replace_with; otherwise, the value of string1 is returned. If both parameters are null, null is returned.

The processing of null can be done in the place where computing is needed:

 



SelectEname Sal+Nvl (coum,0)FromEMP;

Note that the values of string1 and replace_with must be of the same data type. For example: nvl (to_char (name), 'string1 ')

In addition:Oracle extends the nvl function and provides the nvl2 function.
Nvl2 (E1, E2, E3) function: If E1 is null, the function returns E3; otherwise, the function returns E2.

4,CountFunction

Count (column_name)

The function returns the number of values in a specified column (null is not counted)

5,Like

A. employee information with three characters including all

 



SelectEnameFromEMPWhereEnameLike '% All %';

B. The second letter of the name is the employee information of.

SelectEnameFromEMPWhereEnameLike '_ A %';

C. Match nalt and .

 



SelectEnameFromEMPWhereFirstnameLike '[Nc] alt';

D. If the wildcard is in a matching field, escape

SelectEnameFromEMPWhereEnameLike '% \ %' Escape '\';

6,CaseBenefits

  select   case   when  C. claim_status =  '  fin   '  and  C. check_out_flag =  0   then   '  1   '  else   '  0   '  end   as  isfinishe 

SelectClassCount(Case WhenSex='' Then 1 End)FromTesttableGroup ByClass

7,To_charFunction

Function: converts a date or number to a string.

To_char (Number,'Format') To_char (salary, '$99,999.99')

Baidu encyclopedia

8,RoundFunction

Number.
The number of digits specified by num_digits.

Note:
If num_digits is greater than 0, it is rounded to the specified decimal place.
If num_digits is equal to 0, it is rounded to the nearest integer.
If num_digits is less than 0, it is rounded to the left of the decimal point.

9,Exists and in

Exists:Verify whether data is returned.

In:Determines whether the given value matches the value in the subquery or list.

The above is the real difference between the two.

Let's take a look at the following two sentences:

  select   *   from  EMP e  where   exists  ( select   *   from   depo d   where  D. empno = E. empno  and  D. empno    0005 ); 

Select * FromEMP EWhereE. empnoIn(SelectD. empnoFromDepo dWhereD. empno< 0005);

10,And and or

Note that, when we use and or after where or having at the same time, we need to pay attention to the priority problem. And is prior to or, so we generally need to write it like this, to achieve the effect you want:

Select * From Table WhereFlag='1'And(X=1 OrY=2 OrZ=3)

Yes. use parentheses to change the order and turn it into the logic you want.

11,Question 1:

Student table (Sn, name, SAX) -- Student table (Sn indicates student ID)

Score Table (Sn, CN, score) -- score table

Scourse table (CN, cname) -- course schedule (CN indicates course number)

Question: query the information of students whose Chinese scores are higher than French scores in the China and French courses selected.

 Select Student. Sn, student. Name, student. Sax  From  Student  Where Student. Sn In  ( Select Sc1.sn From  Score SC1 Right   Join  Score SC2  On Sc1.sn = Sc2.sn  Where Sc1.cn = ( Select S1.cn From  Scourse  Where S1.cname =  '  French  ' )  And S2.cn = (Select S2.cn From Scourse S2 Where S2.cname =   '  China  ' )  And Sc1.score < Sc2.score)

 

Write your SQL.

 

 

------------------------------

bow your head and look up your head.