mysql crash course

Day 1: Basic SQL Queries

Topics:

1. Introduction to SQL and relational databases
2. Creating a database and tables
3. Inserting data into tables
4. Retrieving data with SELECT statement
5. Filtering and sorting data

Assignments:

1. Create a database named “mydb”.

   CREATE DATABASE mydb;

2. 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
   );

3. 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');

4. Retrieve all records from the “users” table.

   SELECT * FROM users;

5. Retrieve the names of users whose age is greater than 30.

   SELECT name FROM users WHERE age > 30;

Day 2: Data Manipulation

Topics:

1. Updating records with UPDATE statement
2. Deleting records with DELETE statement
3. Limiting and pagination with LIMIT and OFFSET
4. Aggregation functions: COUNT, SUM, AVG, MIN, MAX
5. Grouping data with GROUP BY

Assignments:

1. Update the age of the user with id 2 to 35.

   UPDATE users SET age = 35 WHERE id = 2;

2. Delete the user with id 4 from the “users” table.

   DELETE FROM users WHERE id = 4;

3. Retrieve the first 3 records from the “users” table.

   SELECT * FROM users LIMIT 3;

4. Retrieve the total number of users in the “users” table.

   SELECT COUNT(*) FROM users;

5. Retrieve the average age of users.

   SELECT AVG(age) FROM users;

Day 3: Filtering and Sorting

Topics:

1. Using WHERE clause for conditional filtering
2. Using comparison operators: =, <>, <, >, <=, >=
3. Using logical operators: AND, OR, NOT
4. Sorting data with ORDER BY clause
5. Sorting in ascending and descending order

Assignments:

1. Retrieve the names of users whose city is ‘Mumbai’.

 SELECT name FROM users WHERE city = 'Mumbai';

2. Retrieve the names of users whose age is between 25 and 35.

   SELECT name FROM users WHERE age BETWEEN 25 AND 35;

3. Retrieve the names of users whose city is not ‘Delhi’.

   SELECT name FROM users WHERE city <> 'Delhi';

4. Retrieve all records from the “users” table sorted by age in ascending order.

   SELECT * FROM users ORDER BY age ASC;

5. 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:

1. Using aggregate functions: COUNT, SUM, AVG, MIN, MAX
2. Working with NULL values: IS NULL, IS NOT NULL
3. Using mathematical functions: ROUND, CEILING, FLOOR
4. String functions: CONCAT, UPPER, LOWER, LENGTH
5. Date functions: NOW, DATE_FORMAT, DATE_ADD, DATE_SUB

Assignments:

1. Retrieve the total number of users in the “users” table.

   SELECT COUNT(*) FROM users;

2. Retrieve the sum of ages of all users.

   SELECT SUM(age) FROM users;

3. Retrieve the average age of users excluding NULL values.

   SELECT AVG(age) FROM users WHERE age IS NOT NULL;

4. Retrieve the concatenated names and cities of all users.

   SELECT CONCAT(name, ', ', city) AS info FROM users;

5. Retrieve the current date and time.

   SELECT NOW();

Day 5: Grouping and Filtering with HAVING Clause

Topics:

1. Grouping data with GROUP BY clause
2. Filtering grouped data with HAVING clause
3. Using aggregate functions with GROUP BY
4. Using multiple columns in GROUP BY
5. Combining GROUP BY, HAVING, and ORDER BY

Assignments:

1. 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;

2. Retrieve the cities with more than 2 users.

   SELECT city FROM users GROUP BY city HAVING COUNT(*) > 2;

3. Retrieve the average age of users in each city.

   SELECT city, AVG(age) AS average_age FROM users GROUP BY city;

4. 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);

5. 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;