Basic skills of SQL -- complex query

Previously reviewed:

SQL knowledge points -- insert records

SQL knowledge points -- update records and delete records

Basic skills of SQL (I) -- SQL and database

Basic skills of SQL (II) -- basic query

Basic skills of SQL (III) -- aggregation and sorting



Advantages of view

Delete view


scalar subquery

Associated subquery

Knowledge test



From the perspective of SQL, views and tables are the same. The difference between the two is that the actual data is saved in the table, while the SELECT statement is saved in the view (the view itself does not store data). Using views, you can easily complete complex operations such as querying data across multiple tables, The view contains two restrictions: "ORDER BY cannot be used" and "it can be updated with restrictions".

Advantages of view

  • The first point is that since the view does not need to save data, it can save the capacity of the storage device.
  • The second advantage is that frequently used SELECT statements can be saved as views, so there is no need to rewrite them every time. Frequently used SELECT statements should be made into views.

The CREATE VIEW statement is required to create a view

CREATE VIEW View name (< View column name 1>, < View column name 2>, ...... )
<SELECT sentence >
CREATE VIEW ProductSum (product_type, cnt_product)--Alias of the view
--there AS Alias used when defining AS Not the same. It belongs to fixed grammar and cannot be omitted
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;--The body of the view
---Use view
SELECT product_type, cnt_product
FROM ProductSum;

Although the view supports creating views on top of views, multiple views will reduce the performance of SQL. Try to avoid creating views on the basis of views. Precautions for using Views:

  • The ORDER BY clause cannot be used
  • Conditions under which the view can be updated:
    • DISTINCT is not used in the SELECT clause
    • There is only one table in the FROM clause
    • The GROUP BY clause is not used
    • HAVING clause not used

Delete view

To delete a view, you need to use the DROP VIEW statement and use the CASCADE option to delete the associated view

DROP VIEW View name (< View column name 1>, < View column name 2>, ...... )


A subquery is a one-time view (SELECT statement). Unlike views, subqueries disappear after the SELECT statement is executed. The sub query is to directly use the SELECT statement used to define the view in the FROM clause. As an inner query, the sub query will be executed first.

-- stay FROM Clause to define the view SELECT sentence
SELECT product_type, cnt_product
FROM ( SELECT product_type, COUNT(*) AS cnt_product
FROM Product
GROUP BY product_type ) AS ProductSum;--Subquery

scalar subquery

Scalar subquery has a special limitation, that is, it must and can only return the results of one row and one column, that is, it returns the value of a column of a row in the table. Scalar subquery is a subquery that returns a single value. The writing position of scalar subquery is not limited to WHERE clause. Generally, any position that can use a single value can be used. In other words, constants or column names can be used in almost all places, whether in the SELECT clause, GROUP BY clause, HAVING clause, or ORDER BY clause. Scalar subqueries must not return multi row results.

Associated subquery

Like the GROUP BY clause, the associated subquery can also segment the data in the table.

SELECT product _type , product_name, sale_price
FROM Product AS P1 ①
WHERE sale_price > (SELECT AVG(sale_price)
FROM Product AS P2 ②
WHERE P1.product_type = P2.product_type
GROUP BY product_type);

What plays a key role here is the condition of the WHERE clause added in the subquery. This condition means to compare the sales unit price and average unit price of each commodity in the same commodity category. When comparing within a subdivided group, you need to use an associated subquery.

Knowledge test

1. Create a view that meets the following three conditions (the view name is ViewPractice5_1). Using the Product table as the reference table, it is assumed that the table contains 8 rows of data in the initial state.
Condition 1: the sales unit price is greater than or equal to 1000 yen.
Condition 2: the registration date is September 20, 2009.
Condition 3: it includes three columns: commodity name, sales unit price and registration date.
The result of executing the SELECT statement on the view is as follows.

SELECT * FROM ViewPractice5_1;

results of enforcement

CREATE VIEW ViewPractice5_1 
	sale_price >= 1000 
	AND regist_date > "2009-09-20"

2. Add viewpractice5 to the view created in 1_ What results will be obtained by inserting the following data into 1?

INSERT INTO ViewPractice5_1 VALUES (' knife ', 300, '2009-11-02');

Error: field of view 'shop viewpractice5_ 1' underlying table doesn't have a default value

When inserting data into the view, it will also insert data into the original table. However, multiple fields in the original table (employees) are not allowed to be empty, so they cannot be inserted. Modify these fields that are not allowed to be empty to allow them to be empty.

3. Write a SELECT statement according to the following results, where sale_price_all is the average selling unit price of all goods

	( SELECT sum( sale_price ) FROM product ) AS sale_price_all 

4. Write an SQL statement according to the condition} in 1 and create a view (named AvgPriceByType) containing the following data.

Tip: the key is AVG_ sale_ The price column. Unlike 3, what needs to be calculated here is the average sales unit price of each commodity category. This column can be created using an associated sub query. The question is where to use this associated subquery.

SELECT product_id,
       (SELECT AVG(sale_price)
          FROM Product P2
         WHERE P1.product_type = P2.product_type
         GROUP BY P1.product_type) AS avg_sale_price
 FROM Product P1;


Keywords: Database SQL

Added by Jude on Mon, 07 Feb 2022 02:13:36 +0200