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);