Lesson 2

Question 1

Why do we need to order data in SQL?

Sometimes we have to ___

Answer 1

It can be really hard to see any trends or insights with a large amount of data, so sometimes we have to summarize, rearrange, or reshape data in order to learn more.

Sometimes we need to:

• see what the range of values looks like
• what the distribution looks like
• helps us see our max and min

Question 2

How can you sort data in SQL?

Answer 2

The ORDER BY clause helps us sort data

• Can sort in descending or ascending order

Question 3

Sorting an entire dataset can have negative impacts to optimization since ____.

• What can it do?

Caution (best practice in real world)

Answer 3

Sorting an entire dataset can have negative impacts to optimization since it can take a lot of time and power to sort a very large dataset.

• So, it can really hurt your query performance

Question 4

By default, ORDER BY sorts in ascending / descending order

Answer 4

By default, ORDER BY sorts in ascending order

Question 5

How can you sort by descending order?

For example, how can you pull everything from the orders table and sort by order_date, in descending order?

(Write code)

Answer 5

To sort by descending order, you can add desc after the column name:

SELECT
   *
FROM
   orders
ORDER BY
   order_date desc

Question 6

SQL clauses do / do not have to go in a certain order, or you can get an error and they won’t work.

Answer 6

SQL clauses DO have to go in a certain order, or you can get an error and they won’t work.

Question 7

How can you pull everything from the orders table and sort by the order_date?

(Write code & pay attention to order of SQL clauses)

Answer 7

SELECT
   *
FROM
   orders
ORDER BY
   order_date

• There are other SQL clauses that sometimes go between FROM and ORDER BY, but we’ll learn those later.

Question 8

What will the following query return?

SELECT
    order_id,
    customer_id,
    order_date
FROM
    orders
ORDER BY
    customer_id, order_date desc

Answer 8

• In SQL, you can order by multiple columns.
  
  • It’ll order by the first column and then by the second column, etc.

This will sort our entire dataset by:

• customer_id, ascending
  
  • (because that’s the default)
• and THEN within each customer_id, it’ll sort by order_date, descending

Question 9

In some SQL dialects (especially in newer cloud platforms like Snowflake for example), you can use a shorthand notation to represent the column name.

How can you rewrite the following code?

SELECT
    order_id,
    customer_id,
    order_date
FROM
    orders
ORDER BY
    customer_id, order_date desc

This will make you NOT look like a newb! :p

Answer 9

SELECT
    order_id,
    customer_id,
    order_date
FROM
    orders
ORDER BY
    2, 3 desc

• We can replace the column names in the ORDER BY statement [wc?] with numbers.
• The shorthand notation is based on the order of the columns in the SELECT statement.
  
  • customer_id is the 2nd column in the SELECT statement
  • order_date is the 3rd column in the SELECT statement

Makes query look cleaner - a lot of people use it in the real world