What you will do
A database is good at questions about many rows at once: how many, how many of each, which have none. In this lesson you ask the shop database four of them, with COUNT, GROUP BY, JOIN and LEFT JOIN. You do not change anything.
Before you start
Note
Press Start the lab on the bar at the top of this lesson and wait until it says it is running. The first start takes a little longer, because the shop database is made then. Each Run button below types its command into your lab's terminal, one after another in the same session. To watch them, or to type your own, press Open terminal on the bar.
1. Tell the terminal how to sign in
The same two commands as SQL Basics: the first lets the client accept MySQL's own certificate, and the second makes the short name sql for signing in to the shop database and running one statement. Run them again if the terminal was restarted; it does no harm.
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
2. Count rows
COUNT(*) counts rows. AS customers names the column in the answer. The shop has 40 customers.
sql "SELECT COUNT(*) AS customers FROM customers"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
3. Count each kind
GROUP BY country makes one answer row for each country, and COUNT(*) counts the customers in each. ORDER BY customers DESC puts the biggest first. The United Kingdom has 14.
sql "SELECT country, COUNT(*) AS customers FROM customers GROUP BY country ORDER BY customers DESC LIMIT 3"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
4. Count across two tables
Products only know their category as a number. JOIN puts the category's name beside it, and then you can count the products in each. Books, Kitchen and Outdoors have five each. Two products have no category, so a JOIN leaves them out.
sql "SELECT categories.name, COUNT(*) AS products FROM products JOIN categories ON products.category_id = categories.id GROUP BY categories.name ORDER BY products DESC LIMIT 4"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
5. Find what is missing
A plain JOIN only keeps rows that match. LEFT JOIN keeps every customer, and puts NULL where there is no matching order. WHERE orders.id IS NULL keeps only those: the four customers who have never ordered.
sql "SELECT customers.first_name, customers.country FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
For teachers
Tip
This lesson is a small example of a lesson worked in a lab: the steps and the Run buttons are the lesson, and the lab is each student's own copy. This lesson uses the same MySQL lab as SQL Basics, so a student's sql shortcut and history carry over. The shop database was made to have things to find: customers who never ordered, products never sold, and missing values. Copy it into your own module and change it, or build one from scratch with the Lab menu in the lesson builder.