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.