SQL TUTORIALS

How to Use the "AS" Keyword

• The AS keyword is used to assign an alias to column or table. To use this keyword, use the following syntax:
  SELECT [column_name] AS [new_name]
FROM [table_name];
  OR
  SELECT [column_name]
FROM [table_name] AS [new_name];
• If you want to include a space in an alias, use the following syntax:
  SELECT [column_name] AS "[new_name]"
FROM [table_name];
• NOTE: Although the AS keyword is not required when giving a column or table an alias, it's a best practice to use this keyword to make it more obvious that the column or table is being temporarily renamed.

How to Use the "DISTINCT" Keyword"

• The DISTINCT keyword is used to remove all duplicate records from a result set. To use this keyword, use the following syntax:
  SELECT DISTINCT [column_name], [column_name]
FROM [table_name];
• NOTE: A commonly asked question during interviews for Data-related roles is, "How do you get only unique values from a query?". This keyword is the easiest way to answer that question.

How to Use the "LIKE" Keyword

• The LIKE keyword is used to filter a string-based column on a substring. To use this keyword, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] LIKE '[substring]';
• If you want to filter a column based on a substring the string starts with, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] LIKE '[substring]%';
• If you want to filter a column based on a substring the string ends with, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] LIKE '%[substring]';
• If you want to filter a column based on a substring the column contains, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] LIKE '%[substring]%';
• If you want to filter a column based on a substring it DOES NOT match, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] NOT LIKE '%[substring]%';
• NOTE: Although you have the option to use the underscore (_) character to match on a single character, it's rarely used as some strings may have leading or trailing spaces that are invisible in the result set.

How to Use the "IN" Function

• The IN keyword is used to filter a column based on a list of values. To use this keyword, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] IN([value1], [value2], [value3]);
• If you want to filter out a column's values based on a list of values, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] NOT IN([value1], [value2], [value3]);
• NOTE: In interviews where thay ask you SQL-related questions, it's important to know that the IN keyword can slow down your queries as it has to find multiple exact matches. We'll disucss a more efficient approach in a later tutorial.

How to Use the "IS NULL" Keyword

• A NULL value means there is no value in the particular cell. This is different from a zero or blank string, as those represent actual values with measurable meaning.
• The IS NULL keyword is used to filter a column based on if the value is NULL. To use this keyword, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] IS NULL;
• If you want to filter out any NULL values in a particular column, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] IS NOT NULL;

How to Use the "BETWEEN" Keyword

• The BETWEEN keyword is used to filter a column based on if the column value falls within a specified range. To use this keyword, use the following syntax:
  SELECT *
FROM [table_name]
WHERE [column_name] BETWEEN [min_number] AND [max_number];

Exercise

Congratulations! You just completed the Common Keywords Tutorial! To help test your knowledge, let's use the database you populated in the previous tutorial to run some more readable queries. This exercise is based on the exercise of the previous tutorial.
**It's highly recommended that you complete the exercise outlined in the previous tutorial before beginning this exercise.**

Instructions:

1. Open your RDBMS Client (e.g., MySQL, PostgreSQL, Microsoft SQL Server).
2. Using the "earthquake" table, answer the following questions:
1. How Many UNIQUE Earthquakes Occurred in Northern California with a Magnitude Between 6.0 and 7.0?.
2. What was the Deepest Earthquake that Took Place in Either Utah, Oregon, Wyoming, or Egypt?
3. How Many NULL Values are in the "place" column?
4. How Many Places are there that Start with an "A" or End with an "r"?
5. What is the Average Depth of All Earthquakes that Occurred Between 1/1/1970 and 1/1/1990, Had a Magnitude of AT LEAST 6.0, and were Caused by an Earthquake?
3. Exercise Completed! Click here to view the answers.

Have any issues with the above exercise? Post your question on Discord!