Basic SQL syntax ~~~ Share with you ~~~~

-- 1. Find the employee ID, name, department, and date of birth. If the date of birth is null,
-- Display date is unknown and output by department. The date format is yyyy-mm-dd.
Select emp_no, emp_name, DEPT,
Isnull (convert (char (10), birthday, 120), 'date unknown ') Birthday
From employee
Order by Dept

-- 2. Search for the names, gender, department, and title of employees in the same unit as Yu Ziqiang
Select emp_no, emp_name, DEPT, title
From employee
Where emp_name <> 'yu Ziqiang 'and dept in
(Select dept from employee
Where emp_name = 'yu Ziqiang ')

-- 3. Collect the total salary of each department by department
Select Dept, sum (salary)
From employee
Group by Dept

-- 4. Search for the sales status of a 14-inch display product,
-- Display the product ID, sales quantity, unit price, and amount
Select a. prod_id, qty, unit_price, unit_price * qty totprice
From sale_item A, product B
Where a. prod_id = B. prod_id and prod_name = '14-inch dashboard'

-- 5. In the sales list, summarize the sales quantity and amount of each product by product number.
Select prod_id, sum (qty) totqty, sum (qty * unit_price) totprice
From sale_item
Group by prod_id

-- 6. Use the convert function to calculate the total order amount for each customer in 1996 by customer number
Select cust_id, sum (tot_amt) totprice
From sales
Where convert (char (4), order_date, 120) = '20140901'
Group by cust_id

-- 7. Search for customer numbers, names, and total orders with sales records
Select a. cust_id, cust_name, sum (tot_amt) totprice
From Customer A, sales B
Where a. cust_id = B. cust_id
Group by A. cust_id, cust_name

-- 8. Search for customer numbers, names, and total orders with sales records in June 1997
Select a. cust_id, cust_name, sum (tot_amt) totprice
From Customer A, sales B
Where a. cust_id = B. cust_id and convert (char (4), order_date, 120) = '123'
Group by A. cust_id, cust_name

-- 9. Search for the largest sales record at a time
Select order_no, cust_id, sale_id, tot_amt
From sales
Where tot_amt =
(Select max (tot_amt)
From sales)

-- 10. Search for the salesperson list and sales date with at least three sales attempts
Select emp_name, order_date
From Employee A, sales B
Where emp_no = sale_id and A. emp_no in
(Select sale_id
From sales
Group by sale_id
Having count (*)> = 3)
Order by emp_name

-- 11. Use an existing quantizer to find the customer name without an order record
Select cust_name
From Customer
Where not exists
(Select *
From sales B
Where a. cust_id = B. cust_id)

-- 12. Use the left outer link to find the customer ID, name, order date, and order amount of each customer.
-- Do not display the time for the order date. The date format is yyyy-mm-dd.
-- Sort by Customer ID, and output by order in descending order of the same customer
Select a. cust_id, cust_name, convert (char (10), order_date, 120), tot_amt
From customer a left Outer Join sales B on A. cust_id = B. cust_id
Order by A. cust_id, tot_amt DESC

-- 13. Search for the Sales Status of 16 m dram and display the name of the corresponding salesperson,
-- Gender, sales date, sales quantity, and amount, in which gender is represented by male and female
Select emp_name, Gender = case A. Sex when 'M' then 'male'
When 'F' then 'female'
Else 'UN'
End,
Sales date = isnull (convert (char (10), C. order_date, 120), 'date unknown '),
Qty quantity, qty * unit_price as amount
From Employee A, sales B, sale_item C, product d
Where D. prod_name = '16m DRAM 'and D. pro_id = C. prod_id and
A. emp_no = B. sale_id and B. order_no = C. order_no

-- 14. query the sales records of each person. The salesperson's ID, name, gender, and,
-- Product name, quantity, unit price, amount, and sales date
Select emp_no, emp_name, Gender = case A. Sex when 'M' then 'male'
When 'F' then 'female'
Else 'UN'
End,
Prod_name product name, sales date = isnull (convert (char (10), C. order_date, 120), 'date unknown '),
Qty quantity, qty * unit_price as amount
From employee a left Outer Join sales B on A. emp_no = B. sale_id, sale_item C, product d
Where D. pro_id = C. prod_id and B. order_no = C. order_no

-- 15. Search for the customer name and total payment with the largest sales amount
Select cust_name, D. cust_sum
From Customer,
(Select cust_id, cust_sum
From (select cust_id, sum (tot_amt) as cust_sum
From sales
Group by cust_id) B
Where B. cust_sum =
(Select max (cust_sum)
From (select cust_id, sum (tot_amt) as cust_sum
From sales
Group by cust_id) C)
) D
Where a. cust_id = D. cust_id

-- 16. Search for the number, name, and sales of the salesperson whose total sales volume is less than 1000 yuan
Select emp_no, emp_name, D. sale_sum
From Employee,
(Select sale_id, sale_sum
From (select sale_id, sum (tot_amt) as sale_sum
From sales
Group by sale_id) B
Where B. sale_sum <1000
) D
Where a. emp_no = D. sale_id

-- 17. Search for customer numbers, Customer names, product numbers, product names, quantities, and amounts that have sold at least three types of products
Select a. cust_id, cust_name, B. prod_id, prod_name, D. qty, D. qty * D. unit_price
From Customer A, product B, sales C, sale_item d
Where a. cust_id = C. cust_id and D. prod_id = B. prod_id and
C. order_no = D. order_no and A. cust_id in (
Select cust_id
From (select cust_id, count (distinct prod_id) prodid
From (select cust_id, prod_id
From sales E, sale_item F
Where E. order_no = f. order_no) g
Group by cust_id
Having count (distinct prod_id)> = 3) H)

-- 18. Search for customer numbers, names and product numbers, product names, quantities, and amounts that are at least the same as those sold by World Technology development companies.
Select a. cust_id, cust_name, D. prod_id, prod_name, qty, qty * unit_price
From Customer A, product B, sales C, sale_item d
Where a. cust_id = C. cust_id and D. prod_id = B. prod_id and
C. order_no = D. order_no and not exists
(Select F .*
From Customer X, sales E, sale_item F
Where cust_name = 'World technology developers' and X. cust_id = E. cust_id and
E. order_no = f. order_no and not exists
(Select G .*
From sale_item g, sales H
Where G. prod_id = f. prod_id and G. order_no = H. order_no and
H. cust_id = A. cust_id)
)

19. Find the employee ID, department, and salary of all employees surnamed Liu in the table.
Select emp_no, emp_name, DEPT, salary
From employee
Where emp_name like 'Liu %'

20. Search for all customer numbers with an order amount greater than 20000
Select cust_id
From sales
Where tot_amt> 20000

21. Number of employees with salaries between-in the statistical table
Select count (*) as Count
From employee
Where salesary between 40000 and 60000

22. query the average salary of employees in the same department in the table, but only query the employees whose "residential address" is "Shanghai ".
Select AVG (salary) avg_sal, Dept
From employee
Where ADDR like 'shanghai %'
Group by Dept

23. Change the employee's address in the table "Shanghai" to "Beijing"
Update employee
Set ADDR like 'beijing'
Where ADDR like 'shanghai'

24. Search for basic information about female employees in the business or accounting department.
Select emp_no, emp_name, Dept
From employee
Where sex = 'F' and dept in ('business', 'account ')

25. display the total sales amount of each product, and output the total sales amount from large to small.
Select prod_id, sum (qty * unit_price)
From sale_item
Group by prod_id
Order by sum (qty * unit_price) DESC

26. Select the customer ID, customer name, and customer address of the numbers 'c0001' and 'c0004.
Select cust_id, cust_name, ADDR
From customer
Where cust_id between 'c0001 'and 'c0004'

27. computing has sold several products in total.
Select count (distinct prod_id) as 'Total number of sold products'
From sale_item

28. Increase the salary of employees in the business department by 3%.
Update employee
Set salary = salary * 1.03
Where dept = 'business'

29. Find the employee information with the lowest salary in the employee table.
Select *
From employee
Where salary =
(Select Min (salary)
From employee)

30. Use join to query "customer name", "order amount", "Order Date", and "phone number" of the customer whose name is "customer C" purchased goods"
Select a. cust_id, B. tot_amt, B. order_date, A. tel_no
From customer a join sales B
On a. cust_id = B. cust_id and cust_name like 'customer bing'

31. In the sales table, find all orders whose order amount is greater than the amount of each order received by the salesman on the 1996/10/15 day.
Select *
From sales
Where tot_amt> All
(Select tot_amt
From sales
Where sale_id = 'e0013 'and order_date = '2017/15 ')
Order by tot_amt

32. Calculate the average unit price of the 'p0001' Product
Select AVG (unit_price)
From sale_item
Where prod_id = 'p0001'

33. Find the order received by the female employee of the company
Select sale_id, tot_amt
From sales
Where sale_id in
(Select sale_id from employee
Where sex = 'F ')

34. Find the employees who enter the company's services on the same day
Select a. emp_no, A. emp_name, A. date_hired
From Employee
Join employee B
On (A. emp_no! = B. emp_no and A. date_hired = B. date_hired)
Order by A. date_hired

35. Find the employee ID and name with the current performance exceeding 232000 RMB.
Select emp_no, emp_name
From employee
Where emp_no in
(Select sale_id
From sales
Group by sale_id
Having sum (tot_amt) <1, 232000)

36. query the average salary of all female employees and the average salary of all female employees whose addresses are in Shanghai.
Select AVG (salary)
From employee
Where sex like 'F'
Union
Select AVG (salary)
From employee
Where sex like 'F' and ADDR like 'shanghai %'

37. query the employee information whose salary exceeds the average salary in the employee table.
Select * from employee where salary> (select AVG (salary) from employee)

38. Identify the number of sales personnel whose current sales performance exceeds 40000 yuan and their sales performance, and sort them by sales performance from large to small.
Select sale_id, sum (tot_amt)
From sales
Group by sale_id
Having sum (tot_amt)> 40000
Order by sum (tot_amt) DESC

39. Find the order number and order amount received by the company's male salesman and the order amount exceeds 2000 yuan.
Select order_no, tot_amt
From sales, employee
Where sale_id = emp_no and sex = 'M' and tot_amt> 2000

40. query the order number and order amount with the highest order amount in the sales table.
Select order_no, tot_amt from sales where tot_amt = (select max (tot_amt) from sales)

41. query the customer name and address for which the order amount exceeds 24000 RMB in each order.
Select cust_name, ADDR from customer a, sales B where a. cust_id = B. cust_id and tot_amt> 24000

42. Find the total order amount of each customer, display the customer number and total order amount, and sort them in descending order according to the total order amount.
Select cust_id, sum (tot_amt) from sales
Group by cust_id
Order by sum (tot_amt) DESC

43. Calculate the total quantity and average unit price of each product ordered by each customer, and sort the number by customer number and product number from small to large.
Select cust_id, prod_id, sum (qty), sum (qty * unit_price)/sum (qty)
From sales a, sale_item B
Where a. order_no = B. order_no
Group by cust_id, prod_id
Order by cust_id, prod_id

44. query the order numbers of more than three products.
Select order_no from sale_item
Group by order_no
Having count (*)> 3

45. The products to be queried include at least the orders for the products ordered in order 10003.
Select distinct order_no
From sale_item
Where order_no <> '200' and not exists (
Select * From sale_item B where order_no = '2013' and not exists
(Select * From sale_item c Where C. order_no = A. order_no and C. prod_id = B. prod_id ))

46. In the sales table, find all the orders with the order amount greater than the "e0013 clerk's amount of each order received on", and display the clerk who undertakes the orders and the amount of the order.
Select sale_id, tot_amt from sales
Where tot_amt> All (select tot_amt from sales where sale_id = 'e0013 'and order_date = '2017/10 ')

47. query the information of the employees who undertake the business at the end.
Select *
From Employee
Where not exists
(Select * from sales B where a. emp_no = B. sale_id)

48. query the name, phone number, order number, and order amount of a customer from Shanghai.
Select cust_name, tel_no, order_no, tot_amt
From Customer A, sales B
Where a. cust_id = B. cust_id and ADDR = 'shanghai'

49. query the performance of each clerk in each month, and sort by Clerk ID and month in descending order.
Select sale_id, month (order_date), sum (tot_amt)
From sales
Group by sale_id, month (order_date)
Order by sale_id, month (order_date) DESC

50. Calculate the total sales quantity and total sales amount of each product. The product number, product name, total quantity, and total amount must be displayed, and arranged in ascending order by product number.
select. prod_id, prod_name, sum (qty), sum (qty * unit_price)
from sale_item A, product B
where. prod_id = B. prod_id
group by. prod_id, prod_name
order by. prod_id
51. query the customer number, customer name, and address of the customer whose total order amount exceeds 'c0002.
select cust_id, cust_name, ADDR
from customer
where cust_id in (select cust_id from sales
group by cust_id
having sum (tot_amt)>
(select sum (tot_amt) from sales where cust_id = 'c0002')

52. query the best-performing salesman's number, salesman's name, and total sales amount.
Select emp_no, emp_name, sum (tot_amt)
From Employee A, sales B
Where a. emp_no = B. sale_id
Group by emp_no, emp_name
Having sum (tot_amt) =
(Select max (totamt)
From (select sale_id, sum (tot_amt) totamt
From sales
Group by sale_id) C)

53. query the detailed list of each product ordered by each customer. The customer number, customer name, product number, product name, quantity, and unit price are displayed.
Select a. cust_id, cust_name, C. prod_id, prod_name, qty, unit_price
From Customer A, sales B, sale_item C, product d
Where a. cust_id = B. cust_id and B. order_no = C. order_no and C. prod_id = D. prod_id

54. Calculate the average salary of each department, and sort the average salary from small to large.
Select Dept, AVG (salary) from employee group by dept order by AVG (salary)

 

========================================================== ========================================================== ==========

1. ApplicationProgramTo minimize the number of accesses to the database.
Search for parameters to minimize the number of rows accessed to the table and minimize the result set, thus reducing the network burden.
Open operations should be processed separately as much as possible to improve the response speed each time; when using SQL in the data window, try to make
The index is placed in the first selected column;AlgorithmThe structure should be as simple as possible; do not use wildcard too much during Query
For example, select * from T1 statement, select several columns for which to use: Select col1, col2 from
T1; if possible, try to limit the number of rows in the result set as much as possible, for example: Select top 300
Col1, col2, col3 from T1, because in some cases the user does not need so much data. Do not
Using Database cursors in applications is a very useful tool, but it is more useful than using conventional, set-oriented SQL statements.
Statement requires more overhead; extract data searches in a specific order.

2. Avoid using incompatible data types. Such as float and INT, char and varchar, binary, and
Varbinary is incompatible. Incompatible data types may make the optimizer unable to execute some operations that can be imported
. For example:
Select name from employee where salary> 60000
In this statement, if the salary field is of the money type, it is difficult for the optimizer to optimize it because 60000
It is an integer. We should convert the integer type into a coin type during programming, instead of waiting for the conversion at runtime.

3. Avoid performing function or expression operations on fields in the WHERE clause whenever possible, which will cause the engine to abandon
Use indexes to scan the entire table. For example:
Select * from T1 where F1/2 = 100
Should be changed:
Select * from T1 where F1 = 100*2

Select * from record where substring (card_no, 5378) = '20140901'
Should be changed:
Select * from record where card_no like '201312'

Select member_number, first_name, last_name from Members
Where datediff (YY, datofbirth, getdate ()> 21
Should be changed:
Select member_number, first_name, last_name from Members
Where dateofbirth <dateadd (YY,-21, getdate ())
That is, any column operation will cause table scanning, including database functions, computing expressions, and so on.
If possible, move the operation to the right of the equal sign.

4. Avoid using it! = Or <>, is null, is not null, in, not in, and so on,
Because this will make the system unable to use the index, but can only directly search the data in the table. For example:
Select ID from employee where ID! = 'B %'
The optimizer cannot use indexes to determine the number of rows to be hit. Therefore, you need to search all rows in the table.
 
5. Use numeric fields as much as possible. Some developers and database administrators prefer to include value-based messages.
Information Field
Designed to be optimized, this reduces query and connection performance and increases storage overhead. This is because the engine
Process the query and connect back to compare each character in the string one by one, but for the number type, you only need to compare one
This is enough.

6. Use the exists and not exists clauses reasonably. As follows:
1. Select sum (t1.c1) from T1 where (
(Select count (*) from T2 where t2.c2 = t1.c2> 0)
2. Select sum (t1.c1) from t1where exists (
Select * From T2 where t2.c2 = t1.c2)
The two produce the same results, but the latter is obviously more efficient than the former. Because the latter will not generate a large number of locks
Fixed table or index scan.
If you want to check whether a record exists in the table, do not use count (*) as inefficient and waste the server
Server resources. It can be replaced by exists. For example:
If (select count (*) from table_name where column_name = 'xxx ')
Can be written:
If exists (select * From table_name where column_name = 'xxx ')

You often need to write a t_ SQL statement to compare a parent result set and a child result set to find whether the statement exists in the parent
Records in the result set but not in the subresult set, such:
1. Select a. hdr_key from hdr_tbl a ---- tbl a indicates that TBL is replaced by alias.
Where not exists (select * From dtl_tbl B where a. hdr_key = B. hdr_key)

2. Select a. hdr_key from hdr_tbl
Left join dtl_tbl B on A. hdr_key = B. hdr_key where B. hdr_key is null

3. Select hdr_key from hdr_tbl
Where hdr_key not in (select hdr_key from dtl_tbl)
The three writing methods can get the same correct results, but the efficiency is reduced in turn.

7. Try to avoid using non-start letters to search for indexed character data. This also makes the engine unable
Use indexes.
See the following example:
Select * from T1 where name like '% L %'
Select * from T1 where substing (name, 2, 1) = 'l'
Select * from T1 where name like 'l %'
Even if the name field has an index, the first two queries still cannot be accelerated using the index, and the engine has
Perform operations on all data in the entire table one by one. The third query can use indexes to speed up operations.

8. There may be more than one connection condition between two tables by using the connection condition.
Write the complete connection conditions in the WHERE clause, which may greatly improve the query speed.
Example:
Select sum (A. Amount) from account A, card B where a. card_no = B. card_no
Select sum (A. Amount) from account A, card B where a. card_no = B. card_no
And a. account_no = B. account_no
The second statement is much faster than the first statement.

9. Eliminates sequential access to data in large table rows
Although all check columns have indexes, some forms of where clauses force the optimizer to use
Sequential access. For example:
Select * from orders where (customer_num = 104 and order_num> 1001) or
Order_num= 1008
The solution can be to use the Union to avoid sequential access:
Select * from orders where customer_num = 104 and order_num> 1001
Union
Select * from orders where order_num = 1008
In this way, you can use the index path to process queries.

10. Avoid difficult Regular Expressions
the like keyword supports wildcard matching, technically called regular expressions. However, this matching is especially time-consuming
. For example, select * from customer where zipcode like "98 _"
even if an index is created on the zipcode field, sequential scanning is also used in this case. For example,
if you change the statement to select * from customer where zipcode> "98000", indexes are used to query the statement during query
, which obviously increases the speed.
11. Use view to accelerate query
sort a subset of a table and create a view. Sometimes, query is accelerated. It helps avoid multiple sorting
operations and simplifies the optimizer's work in other aspects. Example:
select Cust. Name, rcvbles. Balance ,...... Other columns
from Cust, rcvbles
where Cust. customer_id = rcvlbes. customer_id
and rcvblls. balance> 0
and Cust. postcode> "98000"
order by Cust. name
If the query is executed multiple times but more than once, you can find all the unpaid customers in a
View, sort by customer name:
Create view DBO. v_cust_rcvlbes
as
select Cust. name, rcvbles. balance ,...... Other columns
from Cust, rcvbles
where Cust. customer_id = rcvlbes. customer_id
and rcvblls. balance> 0
order by Cust. name

Then, query in the view in the following way:
Select * From v_cust_rcvlbes
Where postcode> 98000"
The number of rows in the view is smaller than that in the master table, and the physical order is the required order, reducing the disk size.
I/O, so the query workload can be greatly reduced.

12. If you can use between, do not use in.
Select * from T1 where ID in (10, 11, 12, 13, 14)
Changed:
Select * from T1 where ID between 10 and 14
Because in will make the system unable to use the index, but can only directly search the data in the table.

13. Distinct does not require group
Select orderid from details where unitprice> 10 group by orderid
You can change it:
Select distinct orderid from details where unitprice> 10

14. partial use of Indexes
1. Select employeeid, firstname, lastname
From names
Where dept = 'prod' or city = 'Orlando 'or division = 'food'

2. Select employeeid, firstname, lastname from names where dept =
'Prod'
Union all
Select employeeid, firstname, lastname from names where city = 'Orlando'
Union all
Select employeeid, firstname, lastname from names where division =
'Food'
If the dept column has an index, query 2 can partially use the index, and query 1 cannot.

15. Do not use Union if Union all is used.
Union all does not execute the select distinct function, which reduces unnecessary resources.

16. Do not write any queries that do not do anything.
For example, select col1 from T1 where 1 = 0
Select col1 from T1 where col1 = 1 and col1 = 2
This type of Dead Code does not return any result set, but it consumes system resources.

17. Try not to use the select into statement.
The select into statement will lock the table and prevent other users from accessing the table.

18. When necessary, force the query optimizer to use an index
Select * from T1 where nextprocess = 1 and processid in (8, 32, 45)
Changed:
Select * from T1 (Index = ix_processid) Where nextprocess = 1 and
Processid in (8, 32, 45)
The query optimizer forcibly uses the index ix_processid to execute the query.

19. Although the update and delete statements are basically fixed
Discussion:
A) Try not to modify the primary key field.
B) when modifying varchar fields, try to replace them with values of the same length.
C) Minimize the update operations on tables containing update triggers.
D) Avoid columns to be copied to other databases by update.
E) avoid updating columns with many indexes.
F) avoid updating columns in the WHERE clause condition.

The above mentioned are some basic notes for improving the query speed. However, in more cases
Different statements need to be tested and compared repeatedly to obtain the best solution. The best way to do this is test.
Which of the following SQL statements with the same function has the least execution time? However, if the database has a small amount of data, it cannot be compared.
In this case, you can view the execution plan. That is, you can score the query points for Multiple SQL statements that implement the same function.
The parser, press Ctrl + L to view the indexes used by the query, and the number of table scans (the two have the greatest impact on performance ).
Check the cost percentage.
You can use the Wizard to automatically generate a simple stored procedure: Click the run wizard icon on the Enterprise Manager toolbar and click
Click database and create Stored Procedure wizard ". Debugging complex stored procedures: on the left of the query Analyzer
Object Browser (no? Press F8) Select the stored procedure to be debugged, right-click, click debug, and enter parameters
Run. A floating toolbar is displayed, including one-step execution and breakpoint settings.