SQL notes (compact version)

Database structure diagram

1. Retrieve data

1.1 select database

USE sql5th;

1.2 select a single column

select prod_name from Products;

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+
9 rows in set
Time: 0.010s

1.3 selecting multiple columns

select prod_id, prod_name, prod_price from Products;

+---------+---------------------+------------+
| prod_id | prod_name           | prod_price |
+---------+---------------------+------------+
| BNBG01  | Fish bean bag toy   | 3.49       |
| BNBG02  | Bird bean bag toy   | 3.49       |
| BNBG03  | Rabbit bean bag toy | 3.49       |
| BR01    | 8 inch teddy bear   | 5.99       |
| BR02    | 12 inch teddy bear  | 8.99       |
| BR03    | 18 inch teddy bear  | 11.99      |
| RGAN01  | Raggedy Ann         | 4.99       |
| RYL01   | King doll           | 9.49       |
| RYL02   | Queen doll          | 9.49       |
+---------+---------------------+------------+
9 rows in set
Time: 0.006s

1.4 select all columns

select * from Products;

1.5 retrieving different values

As can be seen from the figure below, there are three product suppliers in total, and the six results of our query contain duplicate values.

select vend_id from products;

+---------+
| vend_id |
+---------+
| BRS01   |
| BRS01   |
| BRS01   |
| DLL01   |
| DLL01   |
| DLL01   |
| DLL01   |
| FNG01   |
| FNG01   |
+---------+
9 rows in set
Time: 0.006s

If we only want to query the total number of suppliers, how should we write the query statement?

select distinct vend_id from Products;

+---------+
| vend_id |
+---------+
| BRS01   |
| DLL01   |
| FNG01   |
+---------+
3 rows in set
Time: 0.006s

select distinct vend_id, prod_price from Products;

As can be seen from the query results in the figure below, when two columns filter unique values together, they can be filtered out as long as each row of data is unique.

+---------+------------+
| vend_id | prod_price |
+---------+------------+
| DLL01   | 3.49       |
| BRS01   | 5.99       |
| BRS01   | 8.99       |
| BRS01   | 11.99      |
| DLL01   | 4.99       |
| FNG01   | 9.49       |
+---------+------------+
6 rows in set
Time: 0.006s

select vend_id, prod_price from Products;

+---------+------------+
| vend_id | prod_price |
+---------+------------+
| DLL01   | 3.49       |
| DLL01   | 3.49       |
| DLL01   | 3.49       |
| BRS01   | 5.99       |
| BRS01   | 8.99       |
| BRS01   | 11.99      |
| DLL01   | 4.99       |
| FNG01   | 9.49       |
| FNG01   | 9.49       |
+---------+------------+
9 rows in set
Time: 0.006s

1.6 limiting results

select prod_name from Products limit 5;

Return 1-5 rows of data

+---------------------+
| prod_name           |
+---------------------+
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| 8 inch teddy bear   |
| 12 inch teddy bear  |
+---------------------+
5 rows in set
Time: 0.005s

Return 5-10 rows of data

select prod_name from Products limit 5 offset 5;

+--------------------+
| prod_name          |
+--------------------+
| 18 inch teddy bear |
| Raggedy Ann        |
| King doll          |
| Queen doll         |
+--------------------+
4 rows in set
Time: 0.006s

1.7 notes

--Single-Line Comments 
/*
multiline comment 
*/

Chapter one exercises

First question

Write an SQL statement to retrieve all the cust_id s from the Customers table.

select cust_id from Customers;

+------------+
| cust_id    |
+------------+
| 1000000001 |
| 1000000002 |
| 1000000003 |
| 1000000004 |
| 1000000005 |
+------------+
5 rows in set
Time: 0.006s

Second question

The OrderItems table contains all ordered products (some have been ordered multiple times). Write SQL statements to retrieve and list the list of ordered products (prod_id) (instead of listing each order, only the list of different products). Tip: finally, 7 lines should be displayed.

select distinct prod_id from OrderItems;

+---------+
| prod_id |
+---------+
| BNBG01  |
| BNBG02  |
| BNBG03  |
| BR01    |
| BR02    |
| BR03    |
| RGAN01  |
+---------+
7 rows in set
Time: 0.006s

Question 3

Write an SQL statement to retrieve all the columns in the Customers table, and then write another SELECT statement to retrieve only the customer ID. Using comments, comment out a SELECT statement to run another SELECT statement. (of course, test these two statements.)

select * from Customers;

select cust_id from Customers;

+------------+
| cust_id    |
+------------+
| 1000000001 |
| 1000000002 |
| 1000000003 |
| 1000000004 |
| 1000000005 |
+------------+
5 rows in set
Time: 0.005s

2. Sort and retrieve data

2.1 sorting data

select prod_name from Products order by prod_name;

For prod_ This statement is the same as the previous statement except for the ORDER BY clause in which the name column sorts the data alphabetically.

+---------------------+
| prod_name           |
+---------------------+
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| 8 inch teddy bear   |
| Bird bean bag toy   |
| Fish bean bag toy   |
| King doll           |
| Queen doll          |
| Rabbit bean bag toy |
| Raggedy Ann         |
+---------------------+
9 rows in set
Time: 0.006s

2.2 sorting by multiple columns

select prod_id, prod_price, prod_name from Products order by prod_price,prod_name;

prod_price is the first order, prod_name is the second order.

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
| RGAN01  | 4.99       | Raggedy Ann         |
| BR01    | 5.99       | 8 inch teddy bear   |
| BR02    | 8.99       | 12 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR03    | 11.99      | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set
Time: 0.006s

2.3 sorting by arrangement position

select prod_id, prod_price, prod_name from Products order by 2,3;

ORDER BY 2 means to press prod, the second column in the SELECT list_ Price. ORDER BY 2, 3 means to press prod first_ Price, and then press prod_name to sort.

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
| RGAN01  | 4.99       | Raggedy Ann         |
| BR01    | 5.99       | 8 inch teddy bear   |
| BR02    | 8.99       | 12 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR03    | 11.99      | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set
Time: 0.005s

2.4 specify sorting direction

select prod_id, prod_price, prod_name from Products order by prod_price desc;

In descending order of price

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    | 11.99      | 18 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR02    | 8.99       | 12 inch teddy bear  |
| BR01    | 5.99       | 8 inch teddy bear   |
| RGAN01  | 4.99       | Raggedy Ann         |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set
Time: 0.005s

select prod_id, prod_price, prod_name from Products order by prod_price desc, prod_name;

In descending order of price, product names are still in ascending order of standard.

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    | 11.99      | 18 inch teddy bear  |
| RYL01   | 9.49       | King doll           |
| RYL02   | 9.49       | Queen doll          |
| BR02    | 8.99       | 12 inch teddy bear  |
| BR01    | 5.99       | 8 inch teddy bear   |
| RGAN01  | 4.99       | Raggedy Ann         |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set
Time: 0.006s

Chapter II exercises

First question

Write an SQL statement to retrieve all customer names from Customers and display the results in the order from Z to A.

select cust_name from Customers order by cust_name desc;

+---------------+
| cust_name     |
+---------------+
| Village Toys  |
| The Toy Store |
| Kids Place    |
| Fun4All       |
| Fun4All       |
+---------------+
5 rows in set
Time: 0.006s

Second question

Write an SQL statement to retrieve the customer ID (cust_id) and order_num from the Orders table, sort the results according to the customer ID, and then arrange them in reverse order according to the order date.

select cust_id, order_num from orders order by 1,2 desc;
-- perhaps
select cust_id, order_num from orders order by cust_id, order_num desc;

+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 | 20009     |
| 1000000001 | 20005     |
| 1000000003 | 20006     |
| 1000000004 | 20007     |
| 1000000005 | 20008     |
+------------+-----------+
5 rows in set
Time: 0.005s

Question 3

Obviously, our virtual store prefers to sell more expensive items, and there are many such items. Write an SQL statement to display the quantity and price (item_price) in the OrderItems table, and sort by quantity from more to less and price from high to low.

select quantity, item_price from OrderItems order by quantity desc;

+----------+------------+
| quantity | item_price |
+----------+------------+
| 250      | 2.49       |
| 250      | 2.49       |
| 250      | 2.49       |
| 100      | 5.49       |
| 100      | 10.99      |
| 100      | 2.99       |
| 100      | 2.99       |
| 100      | 2.99       |
| 50       | 11.49      |
| 50       | 4.49       |
| 20       | 5.99       |
| 10       | 8.99       |
| 10       | 11.99      |
| 10       | 3.49       |
| 10       | 3.49       |
| 10       | 3.49       |
| 5        | 4.99       |
| 5        | 11.99      |
+----------+------------+
18 rows in set
Time: 0.005s

Question 4

Is there a problem with the following SQL statement? (try pointing out without running.)

select vend_name from Vendors order vend_name desc;
-- Correct statement
-- select vend_name from Vendors order by vend_name desc;

3. Filter data

3.1 where clause

select prod_name, prod_price from Products where prod_price = 3.49;

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
+---------------------+------------+
3 rows in set
Time: 0.005s

Operator	explain	Operator	explain
=	be equal to	>	greater than
<>	Not equal to	>=	Greater than or equal to
!=	Not equal to	!>	Not greater than
<	less than	BETWEEN	Between two values specified
<=	Less than or equal to	IS NULL	NULL value
!<	Not less than

3.1.1 check individual values

select prod_name, prod_price from Products where prod_price < 10;

Inquire about products with a price of less than $10

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| 8 inch teddy bear   | 5.99       |
| 12 inch teddy bear  | 8.99       |
| Raggedy Ann         | 4.99       |
| King doll           | 9.49       |
| Queen doll          | 9.49       |
+---------------------+------------+
8 rows in set
Time: 0.005s

3.1.2 mismatch check

select vend_id, prod_name from Products where vend_id <> "DLL01";

List all products not manufactured by supplier DLL01.

+---------+--------------------+
| vend_id | prod_name          |
+---------+--------------------+
| BRS01   | 8 inch teddy bear  |
| BRS01   | 12 inch teddy bear |
| BRS01   | 18 inch teddy bear |
| FNG01   | King doll          |
| FNG01   | Queen doll         |
+---------+--------------------+
5 rows in set
Time: 0.006s

3.1.3 range value check

select prod_name, prod_price from Products where prod_price between 5 and 10;

+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 8 inch teddy bear  | 5.99       |
| 12 inch teddy bear | 8.99       |
| King doll          | 9.49       |
| Queen doll         | 9.49       |
+--------------------+------------+
4 rows in set
Time: 0.005s

3.1.4 null value check

select cust_name from Customers where cust_email is null;

+---------------+
| cust_name     |
+---------------+
| Kids Place    |
| The Toy Store |
+---------------+
2 rows in set
Time: 0.006s

Chapter III exercises

First question

Write an SQL statement to retrieve the product ID (prod_id) and product name (prod_name) from the Products table, and only return Products with a price of $9.49.

select prod_id, prod_name from Products where prod_price = 9.49;

+---------+------------+
| prod_id | prod_name  |
+---------+------------+
| RYL01   | King doll  |
| RYL02   | Queen doll |
+---------+------------+
2 rows in set
Time: 0.006s

Second question

Write an SQL statement to retrieve the product ID (prod_id) and product name (prod_name) from the Products table, and only return Products with a price of $9 or higher.

select prod_id, prod_name from Products where prod_price >= 9;

+---------+--------------------+
| prod_id | prod_name          |
+---------+--------------------+
| BR03    | 18 inch teddy bear |
| RYL01   | King doll          |
| RYL02   | Queen doll         |
+---------+--------------------+
3 rows in set
Time: 0.006s

Question 3

Write SQL statements in combination with lessons 3 and 4 to retrieve all different order numbers (order_num) from the OrderItems table, including 100 or more products.

select distinct order_num from OrderItems where order_num >=100;

+-----------+
| order_num |
+-----------+
| 20005     |
| 20006     |
| 20007     |
| 20008     |
| 20009     |
+-----------+
5 rows in set
Time: 0.006s

Question 4

Write an SQL statement to return the names (prod_name) and prices (prod_price) of all Products with prices between $3 and $6 in the Products table, and then sort the results by price. There are many solutions to this problem, which we will discuss in the next lesson, but you can use the knowledge you have learned to solve it

select prod_name, prod_price from Products where prod_price between 3 and 6 order by prod_price;

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
| 8 inch teddy bear   | 5.99       |
+---------------------+------------+
5 rows in set
Time: 0.005s

4. Advanced data filtering

4.1 combining where clauses

4.1.1 and operator

Multiple conditions are used for filtering. The keyword used in the WHERE clause is used to indicate the retrieval of rows that meet all given conditions.

select prod_id, prod_price, prod_name from Products where vend_id = "DLL01" and prod_price <= 4;

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
+---------+------------+---------------------+
3 rows in set
Time: 0.006s

4.1.2 OR operator

A keyword used in the WHERE clause to retrieve rows that match any given condition.

select prod_id, prod_price, prod_name from Products where vend_id = "DLL01" or vend_id ="BRS01";

+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR01    | 5.99       | 8 inch teddy bear   |
| BR02    | 8.99       | 12 inch teddy bear  |
| BR03    | 11.99      | 18 inch teddy bear  |
| BNBG01  | 3.49       | Fish bean bag toy   |
| BNBG02  | 3.49       | Bird bean bag toy   |
| BNBG03  | 3.49       | Rabbit bean bag toy |
| RGAN01  | 4.99       | Raggedy Ann         |
+---------+------------+---------------------+
7 rows in set
Time: 0.006s

4.1.3 evaluation sequence

select prod_name, prod_price from Products where vend_id = "DLL01" or vend_id = "BRS01" and prod_price >= 10;

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 18 inch teddy bear  | 11.99      |
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+
5 rows in set
Time: 0.006s

select prod_name, prod_price from Products where (vend_id = "DLL01" or vend_id = "BRS01") and prod_price >= 10;

+--------------------+------------+
| prod_name          | prod_price |
+--------------------+------------+
| 18 inch teddy bear | 11.99      |
+--------------------+------------+
1 row in set
Time: 0.005s

Computer logic operator priority not > and > or

4.2 in operator

select prod_name, prod_price from Products where vend_id in("DLL01","BRS01") order by prod_name;
-- perhaps
select prod_name, prod_price from Products where vend_id = "DLL01" or vend_id = "BRS01" order by prod_name;

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 12 inch teddy bear  | 8.99       |
| 18 inch teddy bear  | 11.99      |
| 8 inch teddy bear   | 5.99       |
| Bird bean bag toy   | 3.49       |
| Fish bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+
7 rows in set
Time: 0.006s

4.3 not operator

The keyword in the WHERE clause used to negate the subsequent condition.

select prod_name from Products where not vend_id = "DLL01" order by prod_name;
-- perhaps
select prod_name from Products where vend_id <> "DLL01" order by prod_name;

+--------------------+
| prod_name          |
+--------------------+
| 12 inch teddy bear |
| 18 inch teddy bear |
| 8 inch teddy bear  |
| King doll          |
| Queen doll         |
+--------------------+
5 rows in set
Time: 0.006s

Chapter IV exercises

First question

Write an SQL statement to retrieve the vendor name (vend_name) from the Vendors table and return only the suppliers in California (this needs to be filtered by country [USA] and state [CA], and there may be a California in other countries). Prompt: string matching required.

select vend_name from Vendors where vend_state = "CA" and vend_country = "USA";

+-----------------+
| vend_name       |
+-----------------+
| Doll House Inc. |
+-----------------+
1 row in set
Time: 0.006s

Second question

Write SQL statements to find all orders with at least 100 BR01, BR02 or BR03 ordered. You need to return the order number (order_num), product ID (prod_id) and quantity in the OrderItems table, and filter by product ID and quantity. Tip: depending on how you write filters, you may need to pay special attention to the evaluation order.

select order_num, prod_id,quantity from OrderItems where quantity >= 100;

+-----------+---------+----------+
| order_num | prod_id | quantity |
+-----------+---------+----------+
| 20005     | BR01    | 100      |
| 20005     | BR03    | 100      |
| 20007     | BNBG01  | 100      |
| 20007     | BNBG02  | 100      |
| 20007     | BNBG03  | 100      |
| 20009     | BNBG01  | 250      |
| 20009     | BNBG02  | 250      |
| 20009     | BNBG03  | 250      |
+-----------+---------+----------+
8 rows in set
Time: 0.005s

Question 3

Now, let's review the challenge in the previous lesson. Write an SQL statement to return the name (prod_name) AND price (prod_price) of all products with a price between $3 AND $6. Use AND and then sort the results by price.

select prod_name, prod_price from Products where prod_price between 3 and 6;

+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   | 3.49       |
| Bird bean bag toy   | 3.49       |
| Rabbit bean bag toy | 3.49       |
| 8 inch teddy bear   | 5.99       |
| Raggedy Ann         | 4.99       |
+---------------------+------------+
5 rows in set
Time: 0.007s

Question 4

Is there a problem with the following SQL statement? (try pointing out without running.)

select vend_name from Vendors order by vend_name where vend_country = "USA" and vend_state = "CA";
-- modify
select vend_name from Vendors where vend_country = "USA" and vend_state = "CA" order by vend_name;

5. Filter with wildcards

**wildcard) * * a special character used to match part of a value.

**search pattern) * * a search condition consisting of literals, wildcards, or a combination of both.

5.1 like operator

5.1.1 percent sign (%) wildcard

Symbol	effect
%str	Matches a string ending in str
str%	Matches a string beginning with str
%str%	Match string containing str

str is case sensitive

select  prod_id, prod_name from Products where prod_name like "Fish%";

+---------+-------------------+
| prod_id | prod_name         |
+---------+-------------------+
| BNBG01  | Fish bean bag toy |
+---------+-------------------+
1 row in set
Time: 0.006s

select  prod_id, prod_name from Products where prod_name like "%bean bag%";

+---------+---------------------+
| prod_id | prod_name           |
+---------+---------------------+
| BNBG01  | Fish bean bag toy   |
| BNBG02  | Bird bean bag toy   |
| BNBG03  | Rabbit bean bag toy |
+---------+---------------------+
3 rows in set
Time: 0.005s

select  prod_id, prod_name from Products where prod_name like "F%y";

+---------+-------------------+
| prod_id | prod_name         |
+---------+-------------------+
| BNBG01  | Fish bean bag toy |
+---------+-------------------+
1 row in set
Time: 0.005s

If the text field of a column has 20 characters and the stored text is Fish beanbag toy (17 characters), three spaces need to be added after the text to fill the column. This generally has no impact on the data and its use, but may have a negative impact on the above SQL statements. Clause WHERE prod_name LIKE 'F%y' only matches prods that start with F and end with y_ name. If the value is followed by a space, it does not end with y, so Fish beanbag toy will not be retrieved. The simple solution is to add a% sign to the search pattern: 'F%y%' also matches the characters (or spaces) after y. Wildcard% does not match NULL value.

5.1.2 underline () wildcard

Underline () The purpose of% is the same as%, but it only matches a single character, not multiple characters.

 select  prod_id, prod_name from Products where prod_name like "__ inch teddy bear";

+---------+--------------------+
| prod_id | prod_name          |
+---------+--------------------+
| BR02    | 12 inch teddy bear |
| BR03    | 18 inch teddy bear |
+---------+--------------------+
2 rows in set
Time: 0.005s

5.1.3 square brackets ([]) wildcards

The square bracket ([]) wildcard is used to specify a character set. It must match a character at the specified position (the position of the wildcard), [^ STR] represents a string without str.

support	I won't support it
SQL Server	MySQL,Oracle,DB2,SQLite

Chapter V exercises

First question

Write an SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, and only return the Products with the word toy in the description.

select prod_name, prod_desc from Products where  prod_desc like "%toy%";

+---------------------+-----------------------------------------------------------------------+
| prod_name           | prod_desc                                                             |
+---------------------+-----------------------------------------------------------------------+
| Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
+---------------------+-----------------------------------------------------------------------+
3 rows in set
Time: 0.006s

Second question

Turn it around and do it again. Write an SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, and only return the Products without the word toy in the description. This time, the results are sorted by product name.

--Demo database is MySQL,I won't support it`[]`Wildcard, so another way to demonstrate.
select prod_name, prod_desc from Products where  prod_desc not like "%toy%";

+--------------------+--------------------------------------------------+
| prod_name          | prod_desc                                        |
+--------------------+--------------------------------------------------+
| 8 inch teddy bear  | 8 inch teddy bear, comes with cap and jacket     |
| 12 inch teddy bear | 12 inch teddy bear, comes with cap and jacket    |
| 18 inch teddy bear | 18 inch teddy bear, comes with cap and jacket    |
| Raggedy Ann        | 18 inch Raggedy Ann doll                         |
| King doll          | 12 inch king doll with royal garments and crown  |
| Queen doll         | 12 inch queen doll with royal garments and crown |
+--------------------+--------------------------------------------------+
6 rows in set
Time: 0.005s

Question 3

Write an SQL statement to retrieve the product name (prod_name) AND description (prod_desc) from the Products table, AND only return the Products with toy AND carrots in the description at the same time. There are several ways to do this, but for this challenge, please use AND and two LIKE comparisons.

select prod_name, prod_desc from Products where  prod_desc like "%toy%" and prod_desc like "%carrots%";

+---------------------+--------------------------------------------------+
| prod_name           | prod_desc                                        |
+---------------------+--------------------------------------------------+
| Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots |
+---------------------+--------------------------------------------------+
1 row in set
Time: 0.006s

Question 4

A tricky one. I didn't specifically show you this grammar, but wanted to see if you can find the answer based on what you have learned so far. Write an SQL statement to retrieve the product name (prod_name) and description (prod_desc) from the Products table, and only return the Products with toy and carrots appearing in the description at the same time. Tip: just use LIKE with three% symbols.

select prod_name, prod_desc from Products where  prod_desc like "%toy%carrots%";

+---------------------+--------------------------------------------------+
| prod_name           | prod_desc                                        |
+---------------------+--------------------------------------------------+
| Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots |
+---------------------+--------------------------------------------------+
1 row in set
Time: 0.005s

6. Calculation field

6.1 arithmetic fields

Retrieve all items in order number 20008.

select prod_id, quantity, item_price from orderitems where order_num = 20008;

+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| RGAN01  | 5        | 4.99       |
| BR03    | 5        | 11.99      |
| BNBG01  | 10       | 3.49       |
| BNBG02  | 10       | 3.49       |
| BNBG03  | 10       | 3.49       |
+---------+----------+------------+
5 rows in set
Time: 0.006s

Summarize item prices.

select prod_id, quantity, item_price, quantity * item_price as expanded_price from orderitems where order_num = 20008;

+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| RGAN01  | 5        | 4.99       | 24.95          |
| BR03    | 5        | 11.99      | 59.95          |
| BNBG01  | 10       | 3.49       | 34.90          |
| BNBG02  | 10       | 3.49       | 34.90          |
| BNBG03  | 10       | 3.49       | 34.90          |
+---------+----------+------------+----------------+
5 rows in set
Time: 0.005

6.2 splicing field

select concat(vend_name, "(",vend_country, ")") from vendors order by vend_name;

+------------------------------------------+
| concat(vend_name, "(",vend_country, ")") |
+------------------------------------------+
| Bear Emporium(USA)                       |
| Bears R Us(USA)                          |
| Doll House Inc.(USA)                     |
| Fun and Games(England)                   |
| Furball Inc.(USA)                        |
| Jouets et ours(France)                   |
+------------------------------------------+
6 rows in set
Time: 0.006s

Chapter VI exercises

First question

A common use of aliases is to rename the column fields of the table in the retrieved results (to meet specific report requirements or customer needs). Write an SQL statement to retrieve vend from the Vendors table_ id,vend_name,vend_address and vend_city, vend_name is renamed vname, and vend_ Rename city to vcity and turn vend_ Rename address to vaaddress. Sort the results by vendor name (either original or new).

select vend_id, vend_name as vname, vend_address as vaddress, vend_city as vcity from vendors order by vname;

+---------+-----------------+-----------------+------------+
| vend_id | vname           | vaddress        | vcity      |
+---------+-----------------+-----------------+------------+
| BRE02   | Bear Emporium   | 500 Park Street | Anytown    |
| BRS01   | Bears R Us      | 123 Main Street | Bear Town  |
| DLL01   | Doll House Inc. | 555 High Street | Dollsville |
| FNG01   | Fun and Games   | 42 Galaxy Road  | London     |
| FRB01   | Furball Inc.    | 1000 5th Avenue | New York   |
| JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      |
+---------+-----------------+-----------------+------------+
6 rows in set
Time: 0.006s

Second question

Our sample store is on sale, and all Products are reduced by 10%. Write an SQL statement and return prod from the Products table_ id,prod_price and sale_price. sale_price is a calculation field containing promotion price. Tip: you can multiply by 0.9 to get 90% of the original price (i.e. 10% discount).

select prod_id, prod_price, prod_price * 0.9 as sale_price from products;

+---------+------------+------------+
| prod_id | prod_price | sale_price |
+---------+------------+------------+
| BNBG01  | 3.49       | 3.141      |
| BNBG02  | 3.49       | 3.141      |
| BNBG03  | 3.49       | 3.141      |
| BR01    | 5.99       | 5.391      |
| BR02    | 8.99       | 8.091      |
| BR03    | 11.99      | 10.791     |
| RGAN01  | 4.99       | 4.491      |
| RYL01   | 9.49       | 8.541      |
| RYL02   | 9.49       | 8.541      |
+---------+------------+------------+
9 rows in set
Time: 0.005s

7. Use functions to process data

7.1 text processing function

function	explain
left()	Returns the string to the left of the string
length(),datalength(),len()	Returns the length of the string
lower()	Convert string to lowercase
ltrim()	Remove the space to the left of the string
right()	Returns the character to the right of the string
rtrim()	Remove the space to the right of the string
substr(),substring()	Extract the components of a string
sounddex()	Returns the soundex value of a string
upper()	Convert string to uppercase

7.1.1 left() function

select left(vend_name,3) from vendors;

+-------------------+
| left(vend_name,3) |
+-------------------+
| Bea               |
| Bea               |
| Dol               |
| Fun               |
| Fur               |
| Jou               |
+-------------------+
6 rows in set
Time: 0.006s

7.1.2 lenth() function

select length(vend_name) from vendors;

+-------------------+
| left(vend_name,3) |
+-------------------+
| Bea               |
| Bea               |
| Dol               |
| Fun               |
| Fur               |
| Jou               |
+-------------------+
6 rows in set
Time: 0.005s

7.1.3 lower() function

select lower(vend_name) from vendors;

+------------------+
| lower(vend_name) |
+------------------+
| bear emporium    |
| bears r us       |
| doll house inc.  |
| fun and games    |
| furball inc.     |
| jouets et ours   |
+------------------+
6 rows in set
Time: 0.006s

7.1.4 ltrim() function

select ltrim(vend_name) from vendors;

+------------------+
| ltrim(vend_name) |
+------------------+
| Bear Emporium    |
| Bears R Us       |
| Doll House Inc.  |
| Fun and Games    |
| Furball Inc.     |
| Jouets et ours   |
+------------------+
6 rows in set
Time: 0.005s

7.1.5 right() function

select right(vend_name,3) from vendors;

+--------------------+
| right(vend_name,3) |
+--------------------+
| ium                |
|  Us                |
| nc.                |
| mes                |
| nc.                |
| urs                |
+--------------------+
6 rows in set
Time: 0.005s

7.1.6 rtrim() function

select rtrim(vend_name) from vendors;

+------------------+
| rtrim(vend_name) |
+------------------+
| Bear Emporium    |
| Bears R Us       |
| Doll House Inc.  |
| Fun and Games    |
| Furball Inc.     |
| Jouets et ours   |
+------------------+
6 rows in set
Time: 0.005s

7.1.7 substr() function

-- Extract the third character (inclusive) and subsequent parts of the string
select substr(vend_name,3) from vendors;

+---------------------+
| substr(vend_name,3) |
+---------------------+
| ar Emporium         |
| ars R Us            |
| ll House Inc.       |
| n and Games         |
| rball Inc.          |
| uets et ours        |
+---------------------+
6 rows in set
Time: 0.006s

7.1.8 soundex() function

-- Find strings with similar pronunciation
select cust_name, cust_contact from customers where soundex(cust_contact) = soundex("Michael Green");

+------------+----------------+
| cust_name  | cust_contact   |
+------------+----------------+
| Kids Place | Michelle Green |
+------------+----------------+
1 row in set
Time: 0.006s

7.1.9 upper() function

select vend_name, upper(vend_name) as vend_name_name_upcase from vendors order by vend_name;

+-----------------+-----------------------+
| vend_name       | vend_name_name_upcase |
+-----------------+-----------------------+
| Bear Emporium   | BEAR EMPORIUM         |
| Bears R Us      | BEARS R US            |
| Doll House Inc. | DOLL HOUSE INC.       |
| Fun and Games   | FUN AND GAMES         |
| Furball Inc.    | FURBALL INC.          |
| Jouets et ours  | JOUETS ET OURS        |
+-----------------+-----------------------+
6 rows in set
Time: 0.006s

7.2 date processing function

select order_num from orders where year(order_date) = 2020;

+-----------+
| order_num |
+-----------+
| 20005     |
| 20006     |
| 20007     |
| 20008     |
| 20009     |
+-----------+
5 rows in set
Time: 0.006s

7.3 numerical processing function

function	explain
abs()	Returns the absolute value of a number
cos()	Returns the cosine of an angle
exp()	Returns the exponent of a number
pi()	Return pi π \pi Value of π
sin()	Returns the sine of an angle
sqrt()	Returns the square root of a number
tan()	Returns the tangent of an angle

Chapter VII exercises

First question

All users need a login name. The default login name is the combination of their name and city. Write an SQL statement to return customer ID, customer name and user login. The login name is all in uppercase and consists of the first two characters of the customer contact and the first three characters of the city where it is located. For example, my login name is BEOAK (Ben Forta, living in Oak Park). Tip: you need to use functions, splices, and aliases.

select cust_id, cust_name as customer_name, upper(concat(left(cust_contact,2),left(cust_city,3))) as user_login from customers;

+------------+---------------+------------+
| cust_id    | customer_name | user_login |
+------------+---------------+------------+
| 1000000001 | Village Toys  | JODET      |
| 1000000002 | Kids Place    | MICOL      |
| 1000000003 | Fun4All       | JIMUN      |
| 1000000004 | Fun4All       | DEPHO      |
| 1000000005 | The Toy Store | KICHI      |
+------------+---------------+------------+
5 rows in set
Time: 0.007s

Second question

Write an SQL statement to return the order number (order_num) and order date (order_date) of all orders in January 2020, and sort by order date. You should be able to solve this problem based on what you have learned so far, but you can also open the volume and consult the DBMS document.

select order_num, order_date from orders where year(order_date) = 2020 and month(order_date) = 01;

+-----------+---------------------+
| order_num | order_date          |
+-----------+---------------------+
| 20006     | 2020-01-12 00:00:00 |
| 20007     | 2020-01-30 00:00:00 |
+-----------+---------------------+
2 rows in set
Time: 0.006s

8. Summary data

8.1 aggregation function

function	explain
avg()	Returns the average value of a column
count()	Returns the number of rows in a column
max()	Returns the maximum value of a column
min()	Returns the minimum value of a column
sum	Return value of a sum column

8.1.1 avg() function

-- avg()Function ignore null value
select avg(prod_price) as avg_price from products;

+-----------+
| avg_price |
+-----------+
| 6.823333  |
+-----------+
1 row in set
Time: 0.005s

8.1.2 count() function

count(*) contains all rows.
count(colnm) ignores null containing values

select count(*) as num_cust from customers;

+----------+
| num_cust |
+----------+
| 5        |
+----------+
1 row in set
Time: 0.009s

select count(cust_email) as num_cust from customers;

+----------+
| num_cust |
+----------+
| 3        |
+----------+
1 row in set
Time: 0.006s

8.1.3 max() function

-- max()ignore null value
select max(prod_price) as max_price from products;

+-----------+
| max_price |
+-----------+
| 11.99     |
+-----------+
1 row in set
Time: 0.006s

8.1.4 min() function

Same as max()

8.1.5 sum() function

 -- sum()ignore null value
select sum(quantity) as items_ordered from orderitems where order_num = 20005;

+---------------+
| items_ordered |
+---------------+
| 200           |
+---------------+
1 row in set
Time: 0.006s

8.2 combined aggregate function

select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;

+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 9         | 3.49      | 11.99     | 6.823333  |
+-----------+-----------+-----------+-----------+
1 row in set
Time: 0.006s

Chapter 8 exercises

First question

Write an SQL statement to determine the total number of products sold (using the quantity column in OrderItems).

select sum(quantity) from orderitems;

+---------------+
| sum(quantity) |
+---------------+
| 1430          |
+---------------+
1 row in set
Time: 0.006s

Second question

Modify the statement just created to determine the total number of prod_item BR01 sold.

 select sum(order_item) from orderitems where prod_id = "BR01";

+-----------------+
| sum(order_item) |
+-----------------+
| 2               |
+-----------------+
1 row in set
Time: 0.006s

Question 3

Write an SQL statement to determine the price of the most expensive product (prod_price) with a price of no more than $10 in the Products table. Name the calculated field max_price.

select max(prod_price) as max_price from products where prod_price <= 10;

+-----------+
| max_price |
+-----------+
| 9.49      |
+-----------+
1 row in set
Time: 0.006s

9. Grouped data

9.1 creating groups

select vend_id, count(*) as num_prods from products group by vend_id;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   | 3         |
| DLL01   | 4         |
| FNG01   | 2         |
+---------+-----------+
3 rows in set
Time: 0.005s

9.2 filter grouping

Note: there is another way to understand the difference between HAVING and WHERE. WHERE filters before data grouping and HAVING filters after data grouping. This is an important difference. WHERE excluded rows are not included in the grouping. This may change the calculated values, affecting the grouping filtered based on these values in the HAVING clause.

select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;

+------------+--------+
| cust_id    | orders |
+------------+--------+
| 1000000001 | 2      |
+------------+--------+
1 row in set
Time: 0.005s

select vend_id, count(*) as num_prods from products where prod_price >=4 group by vend_id having count(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   | 3         |
| FNG01   | 2         |
+---------+-----------+
2 rows in set
Time: 0.007s

select vend_id, count(*) as num_prods from products where prod_price >= 4 group by vend_id having count(*) >= 2;

+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   | 3         |
| FNG01   | 2         |
+---------+-----------+
2 rows in set
Time: 0.006s

9.3 grouping and sorting

select order_num, count(*) as items from orderitems group by order_num having count(*) >= 3 order by items, order_num;

+-----------+-------+
| order_num | items |
+-----------+-------+
| 20006     | 3     |
| 20009     | 3     |
| 20007     | 5     |
| 20008     | 5     |
+-----------+-------+
4 rows in set
Time: 0.006s

Chapter IX exercises

First question

The OrderItems table contains each product for each order. Write an SQL statement, return the number of order_lines of each order_num, and press order_ Lines sorts the results.

select order_num, count(*) as order_lines from orderitems group by order_num order by order_lines;

+-----------+-------------+
| order_num | order_lines |
+-----------+-------------+
| 20005     | 2           |
| 20006     | 3           |
| 20009     | 3           |
| 20007     | 5           |
| 20008     | 5           |
+-----------+-------------+
5 rows in set
Time: 0.006s

Second question

Write the SQL statement and return the name as cheapest_ The field of item, which contains the Products with the lowest cost for each supplier (using prod_price in the Products table), and then sort the results from the lowest cost to the highest cost.

select min(prod_price) as cheapest_item from products group by vend_id;

+---------------+
| cheapest_item |
+---------------+
| 5.99          |
| 3.49          |
| 9.49          |
+---------------+
3 rows in set
Time: 0.006s

Question 3

It is very important to determine the best customer. Please write an SQL statement to return the order number of all orders with at least 100 items (order_num in the OrderItems table).

select order_num, sum(quantity) as sum_quantity from orderitems group by order_num having sum_quantity >= 100 ;

+-----------+--------------+
| order_num | sum_quantity |
+-----------+--------------+
| 20005     | 200          |
| 20007     | 400          |
| 20009     | 750          |
+-----------+--------------+
3 rows in set
Time: 0.006s

Question 4

Another way to determine the best customer is to see how much they spend. The total price of all orders in the SQL statement is at least u# 1000, and the total price of all orders is returned. Tip: you need to calculate the sum (item_price multiplied by quantity). Sort the results by order number.

select order_num, sum(item_price * quantity) as total from orderitems group by order_num having total >= 1000 order by order_num;

+-----------+---------+
| order_num | total   |
+-----------+---------+
| 20005     | 1648.00 |
| 20007     | 1696.00 |
| 20009     | 1867.50 |
+-----------+---------+
3 rows in set
Time: 0.006s

Question 5

Is there a problem with the following SQL statement? (try pointing out without running.)

-- count(*)Can't pass group by Group
select order_num, count(*) as items from orderitems group by items having count(*) >= 3 order by items. order_num;

10. Use sub query

10.1 filtering by sub query

List all customers who ordered the item RGAN01.

The first step is to retrieve the numbers of all orders containing article RGAN01.
The second step is to retrieve the ID s of all customers with the order numbers listed in the previous step.
The third step is to retrieve the customer information of all customer ID S returned in the previous step.

-- First step
select order_num from orderitems where prod_id = "RGAN01";

+-----------+
| order_num |
+-----------+
| 20007     |
| 20008     |
+-----------+
2 rows in set
Time: 0.006s

-- Step two
select cust_id from orders where order_num in (20007,20008);

+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set
Time: 0.005s

-- Step 3
select cust_name, cust_contact from customers where cust_id in (1000000004,1000000005);

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set
Time: 0.006s

Merge the above three statements into one through nested subquery.

-- nested subqueries 
select cust_name, cust_contact from customers where cust_id in(
    select cust_id from orders where order_num in(
        select order_num from orderitems where prod_id = "RGAN01"
    ));

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set
Time: 0.006s

10.2 use subquery as calculation field

Query the total number of orders for each customer in the Customers table.

The first step is to retrieve the customer list from the Customers table;
The second step is to count the number of Orders in the Orders table for each customer retrieved.

-- First step
select count(*) as orders from orders where cust_id = 1000000001;

+--------+
| orders |
+--------+
| 2      |
+--------+
1 row in set
Time: 0.006s

-- Step two
select cust_name, cust_state, (
    select count(*) from orders where orders.cust_id = customers.cust_id
) as orders from customers order by cust_name;

+---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         | 1      |
| Fun4All       | AZ         | 1      |
| Kids Place    | OH         | 0      |
| The Toy Store | IL         | 1      |
| Village Toys  | MI         | 2      |
+---------------+------------+--------+
5 rows in set
Time: 0.007s

Chapter 10 exercises

First question

Use the subquery to return the list of customers who buy products with a purchase price of $10 or more. You need to use the OrderItems table to find the matching Order number (order_num), and then use the Order table to retrieve the customer ID (cust_id) of these matching orders.

select cust_name, cust_contact from customers where cust_id in (
    select cust_id from orders where orders.order_num in (
        select order_num from orderitems where item_price>=10));

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Village Toys  | John Smith         |
| Fun4All       | Jim Jones          |
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
4 rows in set
Time: 0.006s

Second question

You want to know the date of ordering BR01 products. Write an SQL statement and use a subquery to determine which Orders (in OrderItems) purchased prod_ For products with ID BR01, the customer ID (cust_id) and order_date corresponding to each product are returned from the Orders table. Sort the results by order date.

select cust_id, order_date from orders where order_num in (
    select order_num from orderitems where prod_id = "BR01"
) order by order_date;

+------------+---------------------+
| cust_id    | order_date          |
+------------+---------------------+
| 1000000003 | 2020-01-12 00:00:00 |
| 1000000001 | 2020-05-01 00:00:00 |
+------------+---------------------+
2 rows in set
Time: 0.006s

Question 3

Now let's make it more challenging. In the previous challenge, go back to purchasing prod_ Email of all Customers of the product with ID BR01 (cust_email in the Customers table). Tip: this involves a SELECT statement. The innermost layer returns order from the OrderItems table_ Num, the middle one returns cust from the Customers table_ id.

select cust_id, cust_email from customers where cust_id in (
    select cust_id from orders where order_num in (
        select order_num from orderitems where prod_id = "BR01"))

+------------+-----------------------+
| cust_id    | cust_email            |
+------------+-----------------------+
| 1000000001 | sales@villagetoys.com |
| 1000000003 | jjones@fun4all.com    |
+------------+-----------------------+
2 rows in set
Time: 0.006s

Question 4

We need a list of customer IDS containing the total amount they have ordered. Write an SQL statement, return the customer ID (cust_id in the Orders table), and use the sub query to return total_ordered to return the total number of Orders per customer. Sort the results by amount from large to small. Tip: you have previously used SUM() to calculate the total number of Orders.

select cust_id, (
    select sum(item_price * quantity) from orderitems where orders.order_num = orderitems.order_num) as total_ordered 
    from orders order by total_ordered desc;

+------------+---------------+
| cust_id    | total_ordered |
+------------+---------------+
| 1000000001 | 1867.50       |
| 1000000004 | 1696.00       |
| 1000000001 | 1648.00       |
| 1000000003 | 329.60        |
| 1000000005 | 189.60        |
+------------+---------------+
5 rows in set
Time: 0.007s

Question 5

Again. Write an SQL statement to retrieve all product names (prod_name s) and quant from the Products table_ The calculated column of solve, which contains the total number of Products sold (retrieved using subquery and SUM(quantity) on the OrderItems table)

select prod_name, (
    select sum(quantity) from orderitems where orderitems.prod_id = products.prod_id
) as quant_sold from products;

+---------------------+------------+
| prod_name           | quant_sold |
+---------------------+------------+
| Fish bean bag toy   | 360        |
| Bird bean bag toy   | 360        |
| Rabbit bean bag toy | 360        |
| 8 inch teddy bear   | 120        |
| 12 inch teddy bear  | 10         |
| 18 inch teddy bear  | 165        |
| Raggedy Ann         | 55         |
| King doll           | <null>     |
| Queen doll          | <null>     |
+---------------------+------------+
9 rows in set
Time: 0.005s

11. Connection table

11.1 creating connections

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id;

+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Doll House Inc. | Fish bean bag toy   | 3.49       |
| Doll House Inc. | Bird bean bag toy   | 3.49       |
| Doll House Inc. | Rabbit bean bag toy | 3.49       |
| Bears R Us      | 8 inch teddy bear   | 5.99       |
| Bears R Us      | 12 inch teddy bear  | 8.99       |
| Bears R Us      | 18 inch teddy bear  | 11.99      |
| Doll House Inc. | Raggedy Ann         | 4.99       |
| Fun and Games   | King doll           | 9.49       |
| Fun and Games   | Queen doll          | 9.49       |
+-----------------+---------------------+------------+
9 rows in set
Time: 0.006s

11.2 internal connection

-- The query result is the same as above
select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Doll House Inc. | Fish bean bag toy   | 3.49       |
| Doll House Inc. | Bird bean bag toy   | 3.49       |
| Doll House Inc. | Rabbit bean bag toy | 3.49       |
| Bears R Us      | 8 inch teddy bear   | 5.99       |
| Bears R Us      | 12 inch teddy bear  | 8.99       |
| Bears R Us      | 18 inch teddy bear  | 11.99      |
| Doll House Inc. | Raggedy Ann         | 4.99       |
| Fun and Games   | King doll           | 9.49       |
| Fun and Games   | Queen doll          | 9.49       |
+-----------------+---------------------+------------+
9 rows in set
Time: 0.006s

11.3 Cartesian product

cartesian product is the result returned from the table relationship without connection conditions. The number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table.

11.4 connecting multiple tables

select prod_name, vend_name, prod_price, quantity from orderitems, products, vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20007;

+---------------------+-----------------+------------+----------+
| prod_name           | vend_name       | prod_price | quantity |
+---------------------+-----------------+------------+----------+
| 18 inch teddy bear  | Bears R Us      | 11.99      | 50       |
| Fish bean bag toy   | Doll House Inc. | 3.49       | 100      |
| Bird bean bag toy   | Doll House Inc. | 3.49       | 100      |
| Rabbit bean bag toy | Doll House Inc. | 3.49       | 100      |
| Raggedy Ann         | Doll House Inc. | 4.99       | 50       |
+---------------------+-----------------+------------+----------+
5 rows in set
Time: 0.014s

Let's solve the problem of 10.1 through the connection table.

select cust_name, cust_contact from customers, orders, orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = "RGAN01";

+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set
Time: 0.006s

data source

-- Ceate
-- Sams Teach Yourself SQL in 10 Minutes, 5th Edition
-- http://forta.com/books/0135182794/
-- Example table creation scripts for MySQL & MariaDB


-- ----------------------
-- Create Customers table
-- ----------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

-- -----------------------
-- Create OrderItems table
-- -----------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);


-- -------------------
-- Create Orders table
-- -------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);

-- ---------------------
-- Create Products table
-- ---------------------
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL 
);

-- --------------------
-- Create Vendors table
-- --------------------
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL 
);


-- -------------------
-- Define primary keys
-- -------------------
ALTER TABLE Customers ADD PRIMARY KEY (cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);
ALTER TABLE Orders ADD PRIMARY KEY (order_num);
ALTER TABLE Products ADD PRIMARY KEY (prod_id);
ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);


-- -------------------
-- Define foreign keys
-- -------------------
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);
ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

--Populate
-- Sams Teach Yourself SQL in 10 Minutes, 5th Edition
-- http://forta.com/books/0135182794/
-- Example table population scripts for MySQL & MariaDB
-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2020-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2020-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2020-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2020-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2020-02-08', '1000000001');

-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

Keywords: MySQL

Added by sc00tz on Sun, 20 Feb 2022 09:13:52 +0200

Programming VIP

SQL notes (compact version)

Database structure diagram

1. Retrieve data

1.1 select database

1.2 select a single column

1.3 selecting multiple columns

1.4 select all columns

1.5 retrieving different values

1.6 limiting results

1.7 notes

Chapter one exercises

2. Sort and retrieve data

2.1 sorting data

2.2 sorting by multiple columns

2.3 sorting by arrangement position

2.4 specify sorting direction

Chapter II exercises

3. Filter data

3.1 where clause

3.1.1 check individual values

3.1.2 mismatch check

3.1.3 range value check

3.1.4 null value check

Chapter III exercises

4. Advanced data filtering

4.1 combining where clauses

4.1.1 and operator

4.1.2 OR operator

4.1.3 evaluation sequence

4.2 in operator

4.3 not operator

Chapter IV exercises

5. Filter with wildcards

5.1 like operator

5.1.1 percent sign (%) wildcard

5.1.2 underline () wildcard

5.1.3 square brackets ([]) wildcards

Chapter V exercises

6. Calculation field

6.1 arithmetic fields

6.2 splicing field

Chapter VI exercises

7. Use functions to process data

7.1 text processing function

7.1.1 left() function

7.1.2 lenth() function

7.1.3 lower() function

7.1.4 ltrim() function

7.1.5 right() function

7.1.6 rtrim() function

7.1.7 substr() function

7.1.8 soundex() function

7.1.9 upper() function

7.2 date processing function

7.3 numerical processing function

Chapter VII exercises

8. Summary data

8.1 aggregation function

8.1.1 avg() function

8.1.2 count() function

8.1.3 max() function

8.1.4 min() function

8.1.5 sum() function

8.2 combined aggregate function

Chapter 8 exercises

9. Grouped data

9.1 creating groups

9.2 filter grouping

9.3 grouping and sorting

Chapter IX exercises

10. Use sub query

10.1 filtering by sub query

10.2 use subquery as calculation field

Chapter 10 exercises

11. Connection table

11.1 creating connections

11.2 internal connection

11.3 Cartesian product

11.4 connecting multiple tables