Basic SQL Practice: Blog & Traffic Data

Table of contents

1. 1. Selecting from one table

   0/34

   Review the fundamentals of SQL. Practice using SELECT and WHERE to filter data.

   List of exercises Start now
   1. Introduction
      1. Introduction
      2. Get to know the blog database
   2. SELECT
      1. Selecting from a table - a review
      2. All blog articles
      3. Names of article categories
      4. All first names without duplicates
      5. Product URLs and titles
      6. All product categories
   3. WHERE – numbers
      1. Filtering with WHERE - a review
      2. High traffic articles
      3. Product pages with high traffic
      4. Author with ID 7
      5. Articles in a specific category
      6. Product categories with the BETWEEN operator
   4. WHERE – text
      1. Filtering text values - a review
      2. The "Are Wild Mushrooms Toxic to Dogs?" article
      3. Traffic to /blog/dog-wild-mushrooms/
      4. Product traffic generated by the article /blog/dog-wild-mushrooms/
      5. All articles about dogs
      6. All last names with 5 letters
      7. All last names with at least 5 letters
      8. "a" as the second letter
   5. WHERE – date
      1. Filtering dates - a review
      2. Publications on March 25, 2020
      3. The article traffic in March 2020
   6. WHERE – NULL
      1. NULLs in SQL - a review
      2. Known first names
   7. WHERE – Logical Operators
      1. Logical operators - a review
      2. Articles published in March 2020
      3. Traffic to article /blog/puppy-training-techniques/ in June 2020
      4. Articles generating traffic to /bundle/dog-from-a-to-z
      5. Traffic on Dec. 1, 2020
      6. Articles by Author 16 and Category 3 or 6
      7. Congratulations

2. 2. JOIN

   0/22

   Practice using JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Check your skills of working with multiple tables.

   List of exercises Start now
   1. INNER JOIN
      1. INNER JOIN - a review
      2. Authors and articles
      3. Products and categories
      4. Articles written by Dana Cohen
      5. Articles and traffic on March 20
      6. Product traffic from the COVID article
   2. Multiple JOINs
      1. Multiple JOINs - a review
      2. Articles with their authors and categories
      3. Categories of Geraldine Wood's articles
      4. Articles with high traffic to product pages
      5. Traffic to product pages
   3. Other JOIN types
      1. Other JOIN types - a review
      2. Authors and their articles
      3. Product pages without any traffic
      4. G-surnamed authors and their articles
   4. Multiple JOINs of different types
      1. Multiple JOINs of different types - a review
      2. Article traffic for each author
      3. Articles and traffic to product pages
      4. The product traffic on May 1, 2020
      5. Articles and product pages without any traffic
      6. Traffic for April 2020 articles in May 2020
      7. Congratulations

3. 3. ORDER BY

   0/21

   Practice sorting the rows by single and multiple columns.

   List of exercises Start now
   1. ORDER BY one column
      1. ORDER BY - a review
      2. Sorted articles
      3. Article categories sorted in descending order
      4. Articles from the most recent to the oldest
   2. ORDER BY multiple columns
      1. ORDER BY with multiple columns - a review
      2. Authors sorted by their names
      3. Traffic from articles to product pages in chronological order
      4. Articles from the most recent to the oldest
   3. ORDER BY + WHERE
      1. ORDER BY and WHERE - a review
      2. The article traffic on Christmas Day 2020
      3. The traffic to the sign-up page
      4. Articles on dog tips and dog gift ideas from the most recent to the oldest
      5. Articles in category 'Puppies'
   4. ORDER BY + WHERE + JOIN
      1. ORDER BY with WHERE and JOIN - a review
      2. Traffic to bundle pages in March 2020
      3. Articles by Diana Gordon from the most recent to the oldest
      4. Traffic to Dog Gift Ideas articles in May 2020
      5. The article traffic on June 1, 2020
      6. Traffic from a specific article to a specific product
      7. Dog health articles without any traffic to product pages
      8. Congratulations

4. 4. GROUP BY

   0/29

   Practice grouping rows in SQL. Use GROUP BY and aggregate functions.

   List of exercises Start now
   1. Aggregate functions
      1. Aggregate functions - a review
      2. The number of authors
      3. The number of articles about small dogs and puppies
      4. All October traffic for the "October Monthly Surprise Box"
      5. Traffic statistics in December 2020
      6. The number of authors without any articles
   2. GROUP BY one column
      1. GROUP BY - a review
      2. The number of articles in each category
      3. Article views in May 2020
      4. Traffic from articles to products in categories Dog Health and Dog Products in May 2020
      5. The number of articles by author
   3. GROUP BY multiple columns
      1. GROUP BY multiple columns - a review
      2. Traffic generated by each author in May 2020
      3. Traffic from articles to product pages in May 2020
      4. The number of articles by author and category
      5. Traffic in May 2020 by article category
   4. HAVING
      1. HAVING - a review
      2. Authors with exactly one article
      3. Articles with more than 1000 views in April 2020
      4. Articles with traffic to at least 10 different product pages
      5. Article-product pairs with traffic over 100 in March 2020
   5. GROUP BY exercises
      1. GROUP BY practice
      2. Authors with articles in only one category
      3. Authors with total article views of 50,000 or more
      4. Total traffic from each article category to each product category in May 2020
      5. Count of the articles without any traffic to product pages, by article category
      6. Articles with 20 or more views but no traffic to product pages
      7. Articles written in June 2020 with 1000 or more total views
      8. Congratulations

5. 5. Other SQL Features

   0/20

   Practice using COUNT(), SUM(), MAX(), MIN() and SQL's mathematical operators. Test your knowledge of NULLs and subqueries.

   List of exercises Start now
   1. NULL
      1. NULLs – a review
      2. Authors without a first name
      3. The total article traffic by author
      4. March 2020 traffic by product page
   2. Calculations
      1. Mathematical operations - a review
      2. The range of article view counts
      3. Average views per article for each author
      4. Traffic in article categories in May 2020
   3. Subqueries
      1. Subqueries - a review
      2. Articles of a specific category
      3. Articles with less traffic than the average
      4. The percentage of authors without any articles
      5. The date of the highest traffic
      6. The product with the highest number of views
   4. Correlated subqueries
      1. Correlated subqueries - a review
      2. The date of the highest traffic for each article
      3. Articles with and without traffic
      4. Articles with more traffic than their category average
      5. The most viewed product in each category
      6. Congratulations

6. 6. Advanced queries

   0/15

   Challenge yourself and try writing some more advanced queries! Use all of the skills you practiced in the previous parts: selecting from a table, joining tables, using ORDER BY, GROUP BY, aggregate functions and mathematical operations, and writing subqueries.

   List of exercises Start now
   1. Advanced queries
      1. Let's review
      2. Diana Gordon articles in 2021
      3. Traffic from articles to product pages in May 2020
      4. Authors who first published articles after 2020
      5. Average views per article category in May 2020
   2. Advanced queries II
      1. The percentage of the traffic that went to product pages
      2. Who has more articles than Pat Stephens?
      3. The article with the highest traffic for each product page
      4. The author with the most views on Dog Health
   3. Advanced queries III
      1. Authors who have written for at least 5 categories
      2. Product pages with at least 2000 views
      3. Authors who did not publish in 2020
      4. The first article by each author
      5. The article with the most traffic by category
      6. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more