1.
UPDATE:
Explanation: The UPDATE statement is used to modify existing records in a
table.
Usage:
UPDATE employees
SET salary = 50000
WHERE employee_id = 1;
Scenario: If you want to give a raise to an employee with employee_id 1, you
would use the UPDATE statement to change their salary.
2. SET:
Explanation: The SET clause specifies which columns to update and their new
values.
Usage:
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';
Scenario: This would increase the price of all products in the 'Electronics'
category by 10%.
3. WHERE:
Explanation: The WHERE clause filters records based on specified conditions.
Usage:
SELECT * FROM orders
WHERE order_date >= '2023-01-01';
Scenario: This retrieves all orders placed on or after January 1, 2023.
4. DISTINCT:
Explanation: The DISTINCT keyword is used to return unique values from a
column.
Usage:
SELECT DISTINCT country FROM customers;
Scenario: This retrieves a list of unique countries from the customers table,
eliminating duplicates.
5. TRIM:
Explanation: The TRIM function removes leading and trailing spaces from a
string.
Usage:
SELECT TRIM(name) FROM employees;
Scenario: This cleans up the names of employees by removing any extra spaces.
6. CAST:
Explanation: The CAST function converts a value from one data type to
another.
Usage:
SELECT CAST(salary AS VARCHAR) FROM employees;
Scenario: This converts the salary from a numeric type to a string type for
display purposes.
7. DATE BETWEEN:
Explanation: This clause filters records based on a date range.
Usage:
SELECT * FROM events
WHERE event_date BETWEEN '2023-01-01' AND '2023-12-31';
Scenario: This retrieves all events scheduled for the year 2023.
8. AS:
Explanation: The AS keyword is used to rename a column or table with an
alias.
Usage:
SELECT first_name AS "First Name", last_name AS "Last Name" FROM
employees;
Scenario: This makes the output more readable by renaming the columns in the
result set.
9. CONCAT:
Explanation: The CONCAT function combines two or more strings into one.
Usage:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
Scenario: This creates a full name by combining the first and last names of
employees.
10. COALESCE:
Explanation: The COALESCE function returns the first non-null value in a list
of arguments.
Usage:
SELECT COALESCE(phone_number, 'No phone number provided') FROM
customers;
Scenario: This checks if a customer has a phone number; if not, it provides a
default message.
11. Use CASE statements to correct misspellings in SQL.
1. The SQL table used in this example is not available for download, but if
you were performing a similar query you’d first make sure to load the
data in BigQuery.
2. Start your SQL query with the basic structure:
3. SELECT
4. FROM
5. WHERE
6. 3. In the FROM clause, specify the table you're pulling data from after
FROM. For example, project-id.customer_data.customer_name
7. 4. In the SELECT clause, specify the columns you want to return. In this
example, you want customer_id and first_name.
8. 5. However, there is a misspelling in a customer’s first name.
i. To correct the misspelled name "Tnoy" to "Tony", use a
CASE statement.
ii. Enter CASE. On the next line, enter WHEN first_name =
'Tnoy'THEN 'Tony'. This tells SQL to replace any instances
of Tnoy in the first_name column with Tony.
iii. On the next line, add the statement ELSE first_name to
keep other names as they are.
iv. End the statement with END AS cleaned_name.This creates
a new column called cleaned_name that will contain the data
cleaned with the CASE statement.
9. 6. Delete the WHERE clause because you don’t want to filter the query.
10. 7. The final statement should be:
11.