Explanation and Examples of SQL Keywords and Functions.

Case Distinct Group By Having IN Join Like Null Union Where Count Avg Sum Max Min

Here's an explanation of each keyword and a sample SQL query that demonstrates how to use them:

Assuming you have a table named "patients" with columns named "first_name", "last_name", and "province_id", as well as a table named "provinces" with columns named "id" and "name"|

1. Case: The CASE statement in SQL is used to conditionally execute a set of SQL statements. It is typically used in the SELECT statement to calculate or transform data based on certain conditions. Here's a sample query that uses the CASE statement:

SELECT 
   first_name, 
   last_name, 
   CASE 
      WHEN gender = 'M' THEN 'Male' 
      WHEN gender = 'F' THEN 'Female' 
      ELSE 'Unknown' 
   END AS gender_category
FROM patients;

This query selects the first_name, last_name, and a new calculated column called gender_category. The CASE statement checks the gender column, and if it is 'M', the gender_category is set to 'Male'. If it is 'F', the gender_category is set to 'Female'. Otherwise, the gender_category is set to 'Unknown'.

1. Distinct: The DISTINCT keyword in SQL is used to remove duplicate values from a result set. Here's a sample query that uses DISTINCT:

SELECT DISTINCT city FROM patients;

This query selects the unique values from the city column of the patients table. Any duplicates are removed from the result set.

1. Group By: The GROUP BY keyword in SQL is used to group rows that have the same values in one or more columns. Here's a sample query that uses GROUP BY:

SELECT city, COUNT(*) as num_patients 
FROM patients
GROUP BY city;

This query groups the patients by city and returns the count of patients in each city.

1. Having: The HAVING keyword in SQL is used to filter results based on a condition that uses an aggregate function, such as COUNT or SUM. Here's a sample query that uses HAVING:

SELECT city, COUNT(*) as num_patients 
FROM patients
GROUP BY city
HAVING COUNT(*) > 100;

This query groups the patients by city and returns the count of patients in each city, but only for cities that have more than 100 patients.

1. In: The IN keyword in SQL is used to match a value against a list of values. Here's a sample query that uses IN:

SELECT first_name, last_name 
FROM patients
WHERE city IN ('Toronto', 'Vancouver', 'Calgary');

This query selects the first_name and last_name columns from the patients table for patients who live in Toronto, Vancouver, or Calgary.

1. Join: The JOIN keyword in SQL is used to combine rows from two or more tables based on a related column between them. Here's a sample query that uses JOIN:

SELECT orders.order_id, customers.first_name, customers.last_name 
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

This query selects the order_id, first_name, and last_name columns from the orders and customers tables, but only for orders that match a customer in the customers table based on their customer_id.

1. Like: The LIKE keyword in SQL is used to match a string against a pattern. Here's a sample query that uses LIKE:

SELECT first_name, last_name 
FROM patients
WHERE last_name LIKE 'Sm%';

This query selects the first_name and last_name columns from the patients table for patients whose last name starts with 'Sm'.

1. Null: The NULL keyword in SQL is used to represent a missing or unknown value. Here's a sample query that uses NULL:

SELECT first_name, last_name 
FROM patients
WHERE city IS NULL;

This query selects the first_name and last_name columns from the patients table for patients whose city value

1. Union: The UNION keyword in SQL is used to combine the results of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns and data types. Here's a sample query that uses UNION:

SELECT first_name, last_name, city 
FROM patients
WHERE city = 'Toronto'
UNION
SELECT first_name, last_name, city
FROM patients
WHERE city = 'Vancouver';

This query selects the first_name, last_name, and city columns from the patients table for patients who live in Toronto or Vancouver, and then combines the results into a single result set.

1. Where: The WHERE keyword in SQL is used to filter rows based on a condition. Here's a sample query that uses WHERE:

sqlCopy codeSELECT first_name, last_name 
FROM patients
WHERE birth_date BETWEEN '1990-01-01' AND '2000-12-31';

This query selects the first_name and last_name columns from the patients table for patients whose birth_date is between January 1, 1990 and December 31, 2000.

1. Count: The COUNT function in SQL is used to count the number of rows that match a condition. Here's a sample query that uses COUNT:

SELECT COUNT(*) as num_patients
FROM patients
WHERE city = 'Toronto';

This query returns the number of patients who live in Toronto.

1. Avg: The AVG function in SQL is used to calculate the average value of a numeric column. Here's a sample query that uses AVG:

SELECT AVG(weight) as avg_weight
FROM patients;

This query returns the average weight of all patients in the patients table.

1. Sum: The SUM function in SQL is used to calculate the sum of a numeric column. Here's a sample query that uses SUM:

SELECT SUM(weight) as total_weight
FROM patients;

This query returns the total weight of all patients in the patients table.

1. Max: The MAX function in SQL is used to find the maximum value in a column. Here's a sample query that uses MAX:

SELECT MAX(birth_date) as latest_birth_date
FROM patients;

This query returns the latest birth date in the patients table.

1. Min: The MIN function in SQL is used to find the minimum value in a column. Here's a sample query that uses MIN:

SELECT MIN(birth_date) as earliest_birth_date
FROM patients;

This query returns the earliest birth date in the patients table.