Common SQL statements

The company organized the SQL proficiency test, read the teaching materials, and wrote a summary to share with you.
Our company uses Sybase ase12.5, so some of the following features are for Sybase ASE.
I. SQL bisic
1: Structured quary language features:
A: Standardization
B: non-procedural
C: optimizable
D: set-oriented

2: Data Type in ase
A: numberic
B: character
C: date/time
D: lobs

3: Convert (varchar, textcolumn). If varchar (n) N is not specified, the default value is 30.

4: Roles of where in SQL
A: filter data.
B: table join (before sql92)
C: select an index.

5: differences between Whare and having
The where statement inserts filtered data into the work table.
The having statement filters data from the work table to obtain the final result.

6: execution sequence of a SELECT statement
A: From clause
B: Where clause
C: group by clause
D: Select clause
E: Having clause
F: Order by clause

7: Union vs Union all
A: Union sorts two result sets and removes duplicate elements (efficiency is poor, so it is easy not to use)
B: Union all only sets two results without sorting or removing duplicate elements (high efficiency)

2. Index and query parameters
1: There are three access data methods in ase
A: clustered Index
B: nonclustered Index
C: Table Scan

2: covered Query
A covered query only obtains data from the index and does not need to scan the database table. This is the fastest data query method.
Limit 1: only valid in selece
Limit 2: all referenced columns must be in the same nonclustered index.

3: Functional Index
It is supported only after ase15.0. That is to say, in versions earlier than ase15.0, the following statements may not use indexes.

SQL code

1. Select column1
2. From Table1
3. Where upper (column2) = 'ivanl'

4: How to view the execution plan SQL code

1. Set showplan on
2. Go
3. Your SQL
4. Go
5. Set showplan off
6. Go

5. View iosql code

1. Set statistics Io on
2. Set statistics time on
3. Go
4. You SQL
5. Go
6. Set statistics Io off
7. Set statistics time off
8. Go

6: advice on Using Index
A: Use the fields frequently used in the where statement for index.
B: Make the index contain fewer fields, the better.
C: Drop useless Indexes

Iii. Table join
1: What is table join?
A table connection queries data from multiple tables or retrieves data from a table multiple times.
(A join is a transanct-SQL operation than access rows from multi-tables or from a single talbe multi-times)

2: table join category
A: inner join
B: Outer Join
C: cross join (full join)

3: Full join is not supported in ase, but full joinsql code can be simulated through Union.

1. Select t1.colu1, t2.column2
2. From T1, T2
3. Where t1.id * = t2.id
4. Union
5. Select t1.colu1, t2.column2
6. From T1, T2
7. Where t1.id = * t2.id

(Not recommended, inefficient)

4: ASE supports up to 50 tables for table join. The ASE query optimizer does not do well. Sybase recommends that you join no more than 4 tables (-_-~!)

5. There are three ways to connect tables in the database.
A: nested loop join
B: Merge join
C: Hash join
(You can use show plan to check which join statement is used in the database to implement the join statement)

6. Suggestions for table connection:
A: The join method is used in showplan.
B: add an index to the join column.
C: Multi-table join is divided into several small tables.
D: avoid generating the flute product.

4. Use Case statements
1: two forms of SQL code for case statements

1. A:
2. Case
3. When search_condition then expression
4. [When search_condition then expression]
5. [Else exproestion]
6. End
7. B:
8. Case expression
9. When expression then expression
10. [When exproession then expression]
11. [Else expression]
12. End

2: Use Case
A: Decoding columnsql code

1. Select cust_id, cust_name
2. Case cust_type
3. When 'r 'then' relation'
4. When 'I 'then' International'
5. When's 'then' small'
6. Else 'other'
7. End as customer_type

B: conditionally displaying columns or valuessql code

1. Select title_id, total_sales,
2. Case
3. When total_sales> 5000 then 'higint'
4. When total_sales <100 then 'low'
5. Else''
6. End as 'column'

C: horizontal frequency table and summary calculationsql code

1. Select sum (Case type when 'adv 'Then 1 else 0 end) as adv
2. , Sum (Case type when 'cues' then 1 else 0 end) as cuz
3. From customer

D: updating on variable conditionssql code

1. Update customer
2. Set cust_charge = cust_charte + case cust_type
3. When 'd 'then 1
4. When 'C' then 2
5. When 'E' then 3
6. Else 0
7. End
8. [/Code]
9. E: Rules and check Constraints
10. [Code]
11. Create Table cust_order_info
12. (
13. Order_num int,
14. Order_taker int,
15. Order_date char (7) Default
16. Case
17. When datepart (DW, getdate () between 2 and 6 then 'weekday'
18. Else 'weekend'
19. End
20. )

V. Transactions and locks
1: There are two transaction modes in ase
A: chained Mode
B: unchained mode (Sybase default)
Unchained mode indicates the start of a transaction, and chained implicitly starts a transaction.
Unchained mode uses 'commint Tran ', 'rollback Tran'
Chained mode uses 'commint work' and 'rollback work'
Unchained mode supports nested transactions, but chained mode does not.

2: locking Schema
A: All pages table, will lock data and index as they are accessed (you can have clustered index)
B: A datapages table will lock datpages as they are accessed, index will not be locked (no clustered index)
C: A datarow table will lock datpages as they are accessed, index will not be locked (no clustered index)

3: locking type
The three most important lock types in ASE are
A: Shared locks (select, fetch)
B: Update locks (fetch, update, delete)
C: exclusive locks (insert, update, delete)

4: isolation level
There are four isolation levels in ase
A: isolation level 0 (read uncommited), allowing bloat read
B: isolation level 1 (read comminted) (ASE default), cannot be expanded
C: isolation level 2 (Repeatable read), Repeatable read
D: isolation level 3 (serializable). The Phantom is not allowed to read SQL code.

1. SET transaction isolation level {0 | 1 | 2 | 3}
2. Or
3. Select...
4. At isolation {0 | 1 | 2 | 3}

5: How to Write efficient transaction
For OLTP transaction
A: Make transaction as short as possible
B: use index to randomly access data
C: Use transaction only when necessary
D: select the appropriate lock type and isolation level.
E: Use optimistic locks

Vi. Data Processing
1: divided by 0
Use coalesce () and nullif ()
First, use nullif () to convert 0 to null, and use coalesce () to process the null SQL code.

1. Select coalesce (total_sales/nullif (sales, 0), 0)
   
2. -- Coalesce (ex1, ex2, ex3. ..) returns the first non-null expression.
   -- Nullif (expre, value) If expre = value, null is returned.

2: Find the duplicate data SQL code

1. Select Type, count (*)
2. From table
3. Where ..
4. Group by type
5. Having count (*)> 1

3: Find the SQL code that contains the most repeated data

1. Select Type, count (*)
2. From table
3. Where ..
4. Group by type
5. Having count (*) = max (count (*))

4: data accumulation Java code

1. Select t1.title _ id, t1.advice, sum (t2.advice) as cumulative_total
2. From Title T1, title T2
3. Where t1.title _ id> = t2.title _ id
4. Group by t1.title _ id, t1.advice

5: Ranking datasql code

1. Select Rank = identity (10), title_id, total_sales
2. Into # top from titles
3. Where ..
4. Order by total_sales DESC
5. Go
6. Select * from # top
7. Go
8. Drop table # top
9. Go

6: conver between Julian Date and Gregorian datesql code

1. Select datepart (YY, @ date) * 1000 + datepart (dy, @ date) as julina_date
2. Select dateadd (DD, juliandate % 1000, '2014/1/'+ convert (char (4), juliandate/12/31-1) as gregorian_date

7: calculate the number of days of SQL code this month

1. Datepart (DD,
2. Dateadd (DD,-1 -- last day of this month
3. Datead (mm, 1 -- add a month
4. Dateadd (DD --
5. ,
6. 1-datepart (DD, getdate () -- 1-today
7. Getdate () -- get today

8: whether the SQL code is a leap year

1. Select datepart (dy, '2014/1/'| convert (char (4), datepart (YY, getdate ())))
2. -- = 61 is a leap year
3. -- = 60 is not a leap year