SQL Fundamentals for Data Analysts

How to retrieve all the rows and columns from a table?

Steps

Start from scratch by removing the auto generated SELECT query from workspace using “Backspace”

1. Type the query

SELECT *

FROM wineReviews

1. Click the “Run Query” button or press (Ctrl + Enter)

Explanation

The “*” communicates to the data that Select all columns from the data. Since, the query is simple without any other condition, by default, all the rows are selected as well and we fetch a complete table.

Note: It is a good practice to rename a query tab suitably for future references. To rename the tab:

• Click on “Untitled SQL query tab”

• In the “Save and share your SQL query” type the Name as “SELECT”

• Press “Save” tab

How to retrieve specific columns from a table with only 5 rows shown in the view?

Steps

1. In the new query tab, type

SELECT review_id,

country,

taster_name

FROM wineReviews

LIMIT 5

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “LIMIT”

Explanation

Remember how it took time in the previous exercise for loading the entire table view, which is an unnecessary burden on the processor. So, we LIMIT the retrieval by calling only 5 records to view the data.

What records does the data have, on the wine from Switzerland?

Steps

1. In the new query tab, type and run the following query

SELECT *

FROM wineReviews

WHERE country = "Switzerland"

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “WHERE”

Explanation

The WHERE clause restricts to pulling only the rows we are interested in. If the condition specified in the WHERE clause is true then that record is returned.

Which Swiss varieties of wines have more than 90 “Points”

A: Pinot Noir-Gamay, Pinot Noir and Chasselas

Steps

1. In the new tab, type and run the following query

SELECT variety

FROM wineReviews

WHERE country = "Switzerland"

AND points > 90

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “AND”

How to generate a list of wines having the highest points and are either moderately priced OR are highly priced?

Steps

1. In the new tab, type and run the following query

SELECT country,

winery,

variety,

price,

points

FROM wineReviews

WHERE (price <= 50 OR price >= 1000)

AND points > 98

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “OR”

Note: Always think of a criteria for evaluation prior to running a query. Here, moderately priced means <= $50 and highly priced means >= $1000

An interesting insight is that you can actually buy a cheap with great points without having to spend big.

How to generate a list of only European wines having the highest points and are either moderately priced OR are highly priced?

Steps

1. Write and execute the following query

SELECT country,

winery,

variety,

price,

points

FROM wineReviews

WHERE NOT (country = "US")

AND ((price <= 50 OR price >= 1000) AND (points > 98))

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “NOT”

How many records are in the table?

A: 129971 (including the columns headers)

Steps

1. Using * to count from all the columns, write and execute the following query

SELECT COUNT(*)

FROM wineReviews

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “AGG COUNT”

What is the sum of all the prices mentioned in the dataset?

A: 4850378.5900 (including the columns headers)

Steps

1. Select sum of data values in the “Price” column

SELECT SUM(price)

FROM wineReviews

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “AGG SUM”

What is the average price of wines?

A: 40.0941

Steps

1. Use the Average aggregate function as

SELECT AVG(price)

FROM wineReviews

Note: Do not try to divide the sum of price by the number of records to generate the average price measure as this will not account for null values in the price column. The concept of NULL will be discussed later in this module.

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “AGG AVG”

How expensive is the most expensive variety of wine?

A: White Blend, 3762.0

Steps

1. Select “Variety” and aggregate function of MAX as follows

SELECT variety, MAX(price)

FROM wineReviews

Note: You may use aggregation functions like sum, count, min and max on other measures from the dataset.

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “AGG MAX”

How to generate a view of the average price of wines in each “Country” listed in the dataset?

Steps

1. Use advanced aggregation function GROUP BY, write and execute the following query

SELECT country,

AVG(price)

FROM wineReviews

GROUP BY country

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “GROUP BY”

Explanation

In the previous exercises we were using the aggregation functions on the dataset without any conditions. The “Group By” function is used in collaboration with the “Select” command. It arranges identical records before performing aggregation.

How to select the only countries having the average price of wines greater than $40?

Steps

1. In the “Group By” query, provide a conditional argument using the “Having” clause

SELECT country,

AVG(price)

FROM wineReviews

GROUP BY country

HAVING AVG(price) > 40

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “HAVING”

How to arrange the list from the previous exercise in descending order of average price?

Steps

1. In addition to the “Group By” and “Having” functions, add an “Order By” clause

SELECT country,

AVG(price)

FROM wineReviews

GROUP BY country

HAVING AVG(price) > 40

ORDER BY AVG(price) DESC

Note: The “DESC” clause stands for descending

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “ORDER BY”

How does the “Where”, “Group By”, “Having” and “Order By” clause differ?

Steps

The WHERE clause filters records before any groupings.

The GROUP BY clause first groups the rows and then applies the specified aggregation function.

The HAVING filters the groups formed using “Group By” and then applies any additional specified filter.

1. Write and run the following query

SELECT country,

AVG(price)

FROM wineReviews

WHERE NOT (country = "Switzerland")

GROUP BY country

HAVING AVG(price) > 40

ORDER BY AVG(price) DESC

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “WHERE GROUP BY HAVING ORDER BY”

Explanation

First, the rows containing “Switzerland” as country are removed, then the remaining records are arranged in groups based on country. These groups are further filtered based on wine average price greater than $40 and lastly the summary is arranged in descending order.

How many records contain null values in the “Price” column?

A: 8996

Steps

1. Write and execute the following query

SELECT COUNT(*)

FROM wineReviews

WHERE price IS NULL

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “IS NULL”

How to calculate the average price of wines using arithmetic operation?

Steps

1. To find the average without using aggregate function, remove the null values and then execute the following query

SELECT SUM(price)/

(SELECT COUNT(*) AS CNT

FROM wineReviews

WHERE price IS NOT NULL)

FROM wineReviews

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “IS NOT NULL”

Explanation

The aforementioned query uses a simple nest of “Select” queries. SQL first solves the query written in brackets and returns the count of records where the price is not null. This value of counts is used as a denominator to calculate average. The “AS” clause is called alias and is described in the following exercise.

How to temporarily rename the columns “region_1” and “region_2” as “State” and “District” respectively?

Steps

1. Write and execute the following query

SELECT country,

region_1 AS State,

region_2 AS District

FROM wineReviews

Note: Aliases are mostly used to make column names more meaningful or to shorten a column name for a quick and temporary reference. An alias only exists for the duration of the query.

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “ALIAS”

How to specify multiple values in the “Where” clause?

Steps

1. Write and execute the following query

SELECT country,

description,

taster_name,

price,

points

FROM wineReviews

WHERE country IN ("France", "Portugal")

Note: IN operator acts as a replacement for multiple OR clauses.

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “IN”

How to select all the records between 2019 and 2020?

Steps

1. Use the “review_date” column for querying

SELECT *

FROM wineReviews

WHERE review_date BETWEEN "2019-01-01" AND "2020-12-31"

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “BETWEEN”

How to generate a view where “description” on wines contains “awesome”?

Steps

1. Write and execute the following query

SELECT *

FROM wineReviews

WHERE description LIKE "%awesome%"

Note: % operator denotes none, single or multiple characters and _ operator denotes single character

1. Press Ctrl + Enter (“Run Query”) and rename the tab to “LIKE”

Explanation

The “Like” clause looks for strings in the selected column, here the “description” column, based on the operator/s present in front, end or between the string.