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

SQL26 determine Products The price in the table does not exceed 10 The price of the most expensive product in dollars

describe

Products surface

problem

To write SQL sentence , determine Products The price in the table does not exceed 10 The price of the most expensive product in dollars （prod_price）. Name the calculated field max_price.

Sample results

return max_price

Example analysis

Return the highest price below ten yuan max_price.

Example

answer

Investigate knowledge points ：

• Conditions of the query ： Use keywords WHERE, The price of the product shall not exceed 10 Dollar products are screened out .
• MAX( Name )： Find the maximum value in the column .
• Take the alias ： By keyword AS Will not exceed 10 The record with the highest price among the products in USD will be renamed after screening .

SQL27 Returns the number of lines for each order number

describe

OrderItems The table contains each product for each order

problem

To write SQL sentence , Return each order number （order_num） How many lines are there （order_lines）, And press order_lines Ascending the results Sort .

Sample results

Return order No order_num And the number of lines corresponding to the order number order_lines

Example analysis

The order number a002 Yes 3 Line order record is also the largest order number, so it is returned in the last place , Orders with the same number of order lines do not need to be processed too much .

Example

answer

Investigate knowledge points ：

• COUNT( Name )： Returns the number of values for the specified column .
• AS： Take the alias .
• GROUP BY： Groups rows according to the value of the specified column or expression .
• ORDER BY： According to the following column names Sort ,ASC Indicate positive order , Also by default. Sort ,DESC Reverse order .

SQL28 The lowest cost product per supplier

describe

Yes Products surface , Containing fields prod_price Represents the product price ,vend_id On behalf of the supplier id

problem

To write SQL sentence , Return a file named cheapest_item Field of , This field contains the products with the lowest cost per supplier （ Use Products In the table prod_price）, Then the results are sorted in ascending order from the lowest cost to the highest cost Sort .

Sample results

Return to supplier id vend_id And the lowest cost products of corresponding suppliers cheapest_item.

Example analysis

for example b0019 The lowest cost price is 20, And finally according to *** grid Sort The return sequence is a0019、b0019、a0011.

Example

answer

To find the lowest cost products among various suppliers , You need to pass the keyword GROUP BY To group , Then with the help of the function MIN() find prod_price The smallest value in , Then alias it cheapest_item, Finally, according to the lowest cost products among the suppliers cheapest_item Using keywords ORDER BY Ascending order Sort .

SQL29 The total number of returned orders shall not be less than 100 The order number of all orders for

describe

OrderItems Represents the order item table , Include ： The order number order_num And order quantity quantity.

problem

Please write SQL sentence , The total number of returned orders shall not be less than 100 All order numbers for , The final result is in ascending order according to the order number Sort .

Sample results

return order_num The order number .

Example analysis

The order number a1、a2、a4 Of quantity The sum is greater than or equal to 100, In order a1、a2、a4.

Example

answer

Conditions of the query , Only at this time, it is no longer filtering the specified line , Instead, you need to filter groups , So you can't use keywords at this time WHERE, Instead, you need to use keywords HAVING, It's usually with keywords GROUP BY Continuous use . In addition, you should pay attention to the order of keywords , First, GROUP BY, Followed by the HAVING, The last is ORDER BY.

SQL30 Calculate the sum

describe

OrderItems Table represents order information , Including field ： The order number order_num and item_price The selling price of the goods 、quantity The number .

problem

To write SQL sentence , Aggregate by order number , The total price of the returned order shall not be less than 1000 All order numbers for , The final results are in ascending order according to the order number Sort .

Tips ： The total price = item_price multiply quantity

Sample results

Example

answer

The above questions have already covered the knowledge points , This question is just a comprehensive application , The main contents are as follows ：

• SUM()： Sum the total price of the same product .
• AS： Take the alias .
• GROUP BY： Group by column .
• HAVING： And GROUP BY Combined use to achieve conditional filtering .
• ORDER BY： Proceed in columns Sort .