This content is divided into ten chapters , Five questions each time , Other chapters are on the homepage Mysql Column

Preface

Are you still worrying SQL Where to start , Or learned SQL I want to find a place to practice my hand ？ What a coincidence , Recently, after work, I boarded Cattle guest , Found out Cattle guest I don't know when it went online SQL Will know The exercises of .

《SQL Will know 》 As MIT 、 Erie Reference textbooks of many universities such as neu University , From simple to deep SQL The basic concepts and grammar of . Involving data Sort 、 Filtering and grouping , And the watch 、 View 、 coupling 、 Subquery 、 The cursor 、 Stored procedures and triggers . Examples abound , Convenient access , It can be said that as a CRUD BOY/GIRL A must read list .

Just think about brushing it again , And then put yourself Brush problem To sum up , So here's today's article , I hope I can give a little help to my little friend in need

SQL41 Another way to determine the best customer （ Two ）

describe

OrderItems Table represents order information , Another way to determine the best customer is to see how much they spend ,OrderItems The table has the order number order_num and item_price The selling price of the goods 、quantity The number

Orders Table contains fields order_num The order number 、cust_id customer id

Customer watch Customers There are fields cust_id Customer id、cust_name Name of customer

problem

To write SQL sentence , The total price of the returned order shall not be less than 1000 Customer name and total amount of （OrderItems In the table order_num）.

Tips ： You need to calculate the sum （item_price multiply quantity）. The result is calculated according to the total amount Sort , Please use INNER JOIN grammar .

Sample results

Example analysis

Total （item_price multiply quantity） Greater than or equal to 1000 Order number , for example a2 Corresponding customers id by cust1,cust1 Name of the customer cust_name yes ben, Finally back to ben As order_num a2 Of quantity * item_price The result of the sum 1319.

Example

answer

The main knowledge points to be investigated ：

• Take the alias ：AS keyword
• Sum up ：SUM()
• GROUP BY and HAVING At the same time, it is used to filter the results
• Sort ：ORDER BY keyword

Mainly investigate multiple SQL Use of keywords , At the same time, we need to pay attention to SQL The sequence of keywords in .

SQL42 Retrieve the name of each customer and all order numbers （ One ）

describe

Customers The table represents customer information and contains customers id cust_id And customer name cust_name

Orders The table represents the order information, including the order number order_num And customers id cust_id

problem

Use INNER JOIN To write SQL sentence , Retrieve the name of each customer （Customers In the table cust_name） And all order numbers （Orders In the table order_num）, Finally, according to the customer's name cust_name Returns... In ascending order .

Sample results

Return customer name cust_name And order number order_num

Example

answer

Main investigation INNER JOIN Usage of , Its usage syntax is as follows ：

To realize this problem , Use the above syntax to fill in the corresponding field name and table name , The last is the keyword ORDER BY Use .

SQL43 Retrieve the name of each customer and all order numbers （ Two ）

describe

Orders The table represents the order information, including the order number order_num And customers id cust_id

Customers The table represents customer information and contains customers id cust_id and Customer name cust_name

problem

Retrieve the name of each customer （Customers In the table cust_name） And all order numbers （Orders In the table order_num）, List all customers , Even if they haven't placed an order . Finally, according to the customer's name cust_name Returns... In ascending order .

Sample results

Return customer name cust_name And order number order_num

Example analysis

Based on two tables , Return order No a1 Name of the customer andy wait forsomeone , Customers who don't place orders ace Also counted in .

Example

Copy code

answer

According to the meaning , Mainly with Customers The columns in the table are primary , And then take Customers and Orders The intersection of . about Orders If the column does not exist in the table, the value is null. So you can use... In the extranet LEFT JION, Its usage syntax is as follows ：

Apply the above Syntax , Fill in the corresponding table and column name to achieve , Finally, add simple Sort that will do .

SQL44 Returns the product name and its associated order number

describe

Products The table is a product information table with fields prod_id product id、prod_name The product name

OrderItems The table is the order information table, which contains fields order_num Order number and product id prod_id

problem

Use OUTER JOIN coupling Products Table and OrderItems surface , Return the product name （prod_name） And the order number associated with it （order_num） A list of , And in ascending order according to the product name Sort .

Sample results

Return the product name prod_name And order number order_num

Example analysis

Return the order number of the product and the corresponding actual payment order , But products without actual orders soda Also returned , Finally, according to the product name ascending Sort .

Example

answer

The solution of this problem is similar to 43 topic , The main use of OUTER JOIN Left join in LEFT JOIN, Mainly clear the relevant syntax , Then apply and fill in the table name and column name .

SQL45 Return the product name and the total number of orders for each product

describe

Products The table is a product information table with fields prod_id product id、prod_name The product name

OrderItems The table is the order information table, which contains fields order_num Order number and product id prod_id

problem

Use OUTER JOIN coupling Products Table and OrderItems surface , Return the product name （prod_name） And the total number of orders for each product （ Not the order number ）, And in ascending order by product name Sort .

Sample results

Return the product name prod_name Order number and order number orders

Example analysis

Returns the number of orders actually paid for the product and the corresponding product , But products without actual orders soda Also returned , Finally, according to the product name ascending Sort .

Example

answer

The knowledge point of investigation is still external connection , Just joined in COUNT() The function is used for grouping statistics , Finally, it is also simple Sort .