Day 1: Basic SQL Queries
Topics:
- Introduction to SQL and relational databases
- Creating a database and tables
- Inserting data into tables
- Retrieving data with SELECT statement
- Filtering and sorting data
Assignments:
- Create a database named “mydb”.
CREATE DATABASE mydb;
- Create a table named “users” with columns id, name, age, city, added_at, and updated_at.
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50),
added_at DATETIME,
updated_at DATETIME
);
- Insert 5 records into the “users” table.
INSERT INTO users (id, name, age, city, added_at, updated_at)
VALUES
(1, 'John', 25, 'Mumbai', '2022-01-01', '2022-01-02'),
(2, 'Jane', 30, 'Delhi', '2022-01-03', '2022-01-04'),
(3, 'Mike', 35, 'Bangalore', '2022-01-05', '2022-01-06'),
(4, 'Lisa', 28, 'Chennai', '2022-01-07', '2022-01-08'),
(5, 'David', 32, 'Kolkata', '2022-01-09', '2022-01-10');
- Retrieve all records from the “users” table.
SELECT * FROM users;
- Retrieve the names of users whose age is greater than 30.
SELECT name FROM users WHERE age > 30;
Day 2: Data Manipulation
Topics:
- Updating records with UPDATE statement
- Deleting records with DELETE statement
- Limiting and pagination with LIMIT and OFFSET
- Aggregation functions: COUNT, SUM, AVG, MIN, MAX
- Grouping data with GROUP BY
Assignments:
- Update the age of the user with id 2 to 35.
UPDATE users SET age = 35 WHERE id = 2;
- Delete the user with id 4 from the “users” table.
DELETE FROM users WHERE id = 4;
- Retrieve the first 3 records from the “users” table.
SELECT * FROM users LIMIT 3;
- Retrieve the total number of users in the “users” table.
SELECT COUNT(*) FROM users;
- Retrieve the average age of users.
SELECT AVG(age) FROM users;
Day 3: Filtering and Sorting
Topics:
- Using WHERE clause for conditional filtering
- Using comparison operators: =, <>, <, >, <=, >=
- Using logical operators: AND, OR, NOT
- Sorting data with ORDER BY clause
- Sorting in ascending and descending order
Assignments:
- Retrieve the names of users whose city is ‘Mumbai’.
SELECT name FROM users WHERE city = 'Mumbai';
- Retrieve the names of users whose age is between 25 and 35.
SELECT name FROM users WHERE age BETWEEN 25 AND 35;
- Retrieve the names of users whose city is not ‘Delhi’.
SELECT name FROM users WHERE city <> 'Delhi';
- Retrieve all records from the “users” table sorted by age in ascending order.
SELECT * FROM users ORDER BY age ASC;
- Retrieve all records from the “users” table sorted by name in descending order.
SELECT * FROM users ORDER BY name DESC;
Day 4: Data Aggregation and Functions
Topics:
- Using aggregate functions: COUNT, SUM, AVG, MIN, MAX
- Working with NULL values: IS NULL, IS NOT NULL
- Using mathematical functions: ROUND, CEILING, FLOOR
- String functions: CONCAT, UPPER, LOWER, LENGTH
- Date functions: NOW, DATE_FORMAT, DATE_ADD, DATE_SUB
Assignments:
- Retrieve the total number of users in the “users” table.
SELECT COUNT(*) FROM users;
- Retrieve the sum of ages of all users.
SELECT SUM(age) FROM users;
- Retrieve the average age of users excluding NULL values.
SELECT AVG(age) FROM users WHERE age IS NOT NULL;
- Retrieve the concatenated names and cities of all users.
SELECT CONCAT(name, ', ', city) AS info FROM users;
- Retrieve the current date and time.
SELECT NOW();
Day 5: Grouping and Filtering with HAVING Clause
Topics:
- Grouping data with GROUP BY clause
- Filtering grouped data with HAVING clause
- Using aggregate functions with GROUP BY
- Using multiple columns in GROUP BY
- Combining GROUP BY, HAVING, and ORDER BY
Assignments:
- Retrieve the names and ages of users grouped by city.
SELECT city, GROUP_CONCAT(name) AS names, GROUP_CONCAT(age) AS ages FROM users GROUP BY city;
- Retrieve the cities with more than 2 users.
SELECT city FROM users GROUP BY city HAVING COUNT(*) > 2;
- Retrieve the average age of users in each city.
SELECT city, AVG(age) AS average_age FROM users GROUP BY city;
- Retrieve the cities with the highest and lowest average age of users.
SELECT city, AVG(age) AS average_age FROM users GROUP BY city HAVING AVG(age) = (SELECT MAX(avg_age) FROM (SELECT AVG(age) AS avg_age FROM users GROUP BY city) AS temp) OR AVG(age) = (SELECT MIN(avg_age) FROM (SELECT AVG(age) AS avg_age FROM users GROUP BY city) AS temp);
- Retrieve the cities with at least 1 user whose age is greater than 30, sorted by city name.
SELECT city FROM users WHERE age > 30 GROUP BY city ORDER BY city ASC;
