SQL wrong questions set of Niuke brush questions

1.

Query statement select stuff('lo ina',3, 1, 've ch') The result is ？

answer ：love china

analysis ：

STUFF( Original character ,  Starting position ,  Delete length ,  Insert characters )

Delete the characters of the specified length from the specified starting point , And insert another set of characters here

2.

Write a paragraph SQL, Known shirt table SHIRTABLE, Please realize through the window function , According to different kinds of shirts shirt_type, According to the sales unit price shirt_price Create a sort table from low to high （）

answer ：

SELECT shirt_name, shirt_type, shirt_price,

RANK() OVER (PARTITION BY shirt _type ORDER BY shirt_price) AS ranking

FROM SHIRTABLE

analysis ：

RANK() Function is a Window function , It assigns a ranking to each row in the partition of the result set .

Rows with the same value in the partition get the same ranking . The level of the first row in the partition is 1. RANK() Function adds the number of bound rows to the binding level to calculate the level of the next row , therefore , Levels may not be continuous .

RANK() The syntax of the function is as follows ：

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

In this grammar ：

• First ,PARTITION BY Clause divides the rows of the result set partition to which the function is applied .
• secondly ,ORDER BY Clause specifies the logical sort order of the rows in each partition to which the function applies .

RANK() Function for solving before N And after N A report is very useful .

3.

Express information table waybillinfo(id, waybillno, zonecode, optype, update_time) All operation information of express is stored in , Please find out in ' Zhongshan Park ' Dot , Abnormal delivery （optype=' Abnormal delivery '） More than 3 Second express (waybillno), Correct sql by （）

answer ：

select waybillno from waybillinfo where zonecode=' Zhongshan Park ' and optype=' Abnormal delivery '
group by waybillno having count(*) > 3

analysis ：

1.having Only used in group by after , Yes group by Results of , You can't use it alone
2. Grouping result statistics need group by, Each express as a group

4.

Mysql Middle watch student_table(id,name,birth,sex), Insert the following record ：
('1003' , NULL , '2002-05-20' , ' male ');
('1004' , ' Zhang San ' , '2000-09-06' , ' male ');
('1005' , ' Li Si ' , '2001-12-01' , ' Woman ');

('1006' , NULL , '2001-12-02' , ' Woman ');

modify name Field is NULL The record of , It is set when boys name=' Boy's name ', Set when it is a girl name=' Girl's name ', as follows SQL The right is （）？

answer

update student_table set name  = ( case when sex = ' male ' then ' Boy's name ' when sex = ' Woman ' then ' Girl's name ' end) where name is null ;

analysis ：

【 modify name Field is NULL The record of 】 Need to put where The conditions are written at the end ;

case when The correct grammar of is : case when ... then ... when ... then ... else ... end,case and end There can be no shortage of

5.

Mysql Middle watch student_table(id,name,birth,sex), Insert the following record ：

('1004' , ' Zhang San ' ,'2000-08-06' , ' male ');
('1009' , ' Li Si ', '2000-01-01', ' male ');
('1010' , ' Li Si ', '2001-01-01', ' male ');
('1006' , ' Wang Wu ', '2000-08-06' , ' Woman ');
('1008' , ' Zhang San ', '2002-12-01', ' Woman ');
('1012' , ' Zhang San ', '2001-12-01', ' Woman ');
('1011' , ' Li Si ', '2002-08-06' , ' Woman ');

perform

select t1.*,t2.*
from (
select * from student_table where sex = ' male ' ) t1 
join 
(select * from student_table where sex = ' Woman ')t2 
on  t1.name = t2.name ; 
The number of result lines is （）？

answer ：4 strip

analysis ：

In the title join And inner join Synonymous , same key Cartesian product will be generated when matching ！ The query results are as follows ：

6. The following statement about view is wrong ？

answer ： Delete view with DELETE VIEW

analysis ：

From the question , Views are virtual tables , It only stores the definition of the view when it is stored , The failure to store the corresponding data conforms to the meaning of the question ,BC It also conforms to the syntax of the view , Deleting a view should use DROP VIEW Conduct , So choose D

7.

If the transaction T Data pair D Already added S lock , Then other transactions will affect the data D（ ）

answer ： You can add S lock , Cannot add X lock

analysis ：

Shared lock 【S lock 】
Also called read lock , If business T For data objects A add S lock , The transaction T You can read A But it can't be modified A, Other affairs can only be right again A Add S lock , Instead of X lock , until T Release A Upper S lock . This ensures that other things can be read A, But in T Release A Upper S You can't do it before you lock it A Make any changes .

Exclusive lock 【X lock 】
Also known as write lock . If business T For data objects A add X lock , Business T You can read A You can also modify A, Other affairs can no longer be right A Add any locks , until T Release A The lock on the . This ensures that the rest of the business is T Release A The lock on can't be read or modified before A.

8. In relational database design , Design relational patterns （ Two-dimensional table ） Which stage of database design ？（ ）

answer ： Logic design stage

analysis ：

9.

The query displays the employee's name and whether the name contains letters A Information about , The following conditions are met
If the characters A First place of name , Is displayed ' character A In the first place '
If the characters A At the end of the name , Is displayed ' character A At the bottom '
If the characters A Does not exist in the name , Is displayed ' No characters A'
Other situations show ' character A In the middle '
The following operation statements are correct ：（      ）

answer ：

select ename, case  charindex(‘A‘,ename)
when 1 then ‘ character A In the first place ‘
when len(ename) then
‘ character A At the bottom ‘
when 0 then ‘ No characters A‘
else ‘ character A In the middle ‘
end  Name category    from emp;

analysis ：

adopt CHARINDEX If you can find the corresponding string , Then return the string position i（ The valid location range is 1<= i <= length(input)）, Otherwise return to 0.

Be careful The location is from 1 Start .

The basic grammar is as follows ：

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

expressionToFind ： Target string , Is the string you want to find , Maximum length is 8000 .

expressionToSearch ： String to be used for lookup .

start_location： Start looking for the location , If it is empty, the default is to start searching from the first place .

10

Database design , View （View) It enables us to define a special representation for one or more data tables , Views are no different from data tables in behavior , You can use basic select,insert,update Wait for the command to modify the data , But for the update operation , There are also restrictions , The following are the reasons for limitations （）

answer ：

initial View Defined Select If the statement contains GROUP BY,DISTINCT,LIMIT or HAVING When ordered

If the data in the view comes from multiple word tables

analysis ：

The view contains the following structures that cannot be updated
1： Set operators union,union all, intersect,minus
2：distinct keyword
3：group by,order by,connect by, perhaps start with
4： Subquery
5： Group function
6： The column to be updated is not defined by the view
7： Query with connection ( You can update the key value to save the data of the table )
8： Violate the constraints of the base table ; Join view refers to the view created based on multi table join query （ Generally, it is not easy to modify , But general instead of Trigger can realize the function of modification ）

11

About ACID The following statement is correct ?

 Usability . All actions in the whole operation are to ensure high availability , The system must provide the required stability , To ensure the submission of transactions .

 Uniformity . Before and after the transaction , The constraints of the database remain unchanged .

 Isolation, . The execution of two simultaneous transactions does not affect each other , Intermediate results are not cross accessible .

 persistence . After the transaction is committed , The changes made by the transaction are persisted in the storage medium , Will not be rolled back .

answer ：BD

analysis ：

1.Atomic（ Atomicity ）： The operation contained in a transaction is treated as a logical unit , The operations in this logical unit are either all   work , All or nothing .

2.Consistency（ Uniformity ）： When the transaction completes , The data must be in a consistent state , Data integrity constraints are not broken , An error occurred during the execution of the transaction , Will be rolled back （Rollback） Status to the beginning of the transaction , Like this business never     It has been implemented .

3. Isolation（ Isolation, ）： Transactions allow multiple users to access the same data concurrently , Without destroying the correctness of the data   And integrity . meanwhile , Modifications to parallel transactions must be independent of modifications to other parallel transactions .

 4.Durability（ persistence ）： When the business is over , The result of the transaction must be able to be solidified .