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;
  1. 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
   );
  1. 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');
  1. Retrieve all records from the “users” table.
   SELECT * FROM users;
  1. 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;
  1. Delete the user with id 4 from the “users” table.
   DELETE FROM users WHERE id = 4;
  1. Retrieve the first 3 records from the “users” table.
   SELECT * FROM users LIMIT 3;
  1. Retrieve the total number of users in the “users” table.
   SELECT COUNT(*) FROM users;
  1. 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';
  1. Retrieve the names of users whose age is between 25 and 35.
   SELECT name FROM users WHERE age BETWEEN 25 AND 35;
  1. Retrieve the names of users whose city is not ‘Delhi’.
   SELECT name FROM users WHERE city <> 'Delhi';
  1. Retrieve all records from the “users” table sorted by age in ascending order.
   SELECT * FROM users ORDER BY age ASC;
  1. 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;
  1. Retrieve the sum of ages of all users.
   SELECT SUM(age) FROM users;
  1. Retrieve the average age of users excluding NULL values.
   SELECT AVG(age) FROM users WHERE age IS NOT NULL;
  1. Retrieve the concatenated names and cities of all users.
   SELECT CONCAT(name, ', ', city) AS info FROM users;
  1. 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;
  1. Retrieve the cities with more than 2 users.
   SELECT city FROM users GROUP BY city HAVING COUNT(*) > 2;
  1. Retrieve the average age of users in each city.
   SELECT city, AVG(age) AS average_age FROM users GROUP BY city;
  1. 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);
  1. 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;

SQL DQL Test 2

  1. list all selling in 2019
  2. count all selling year wise order by selling
  3. select all selling in march month of year 2019
  4. count selling in 22 week of year 2020
  5. select all selling from 1st Feb 2019 to 31st Mar 2019
  6. select all customers who place orders in 2019
  7. select all customers from USA who placed order in 2019
  8. select all customers whose order is on hold
  9. select all customers who placed order in march month only
  10. select top 5 customers from USA (who has maximum orders from country USA)

SQL DQL Test 1

  1. select employees in descending order – salary
  2. select all employees from Mumbai
  3. select all employees having salary more than average salary
  4. select sum of salary from table
  5. select all unique address
  6. select details, salary from table
    (details should be concatenation of name and address)
  7. select names of employees having max salary
  8. select employees having 2nd max salary
  9. count employees by address, order by employee count
    e.g. select count(name,address) from employee
  10. show count of employees from nagpur only
  11. select all employees whose names starts or ends with vowels
  12. find employees having max salary in particular city
  13. select top 5 salaried employees
  14. select 2nd highest salaried employees
  15. show cities having total salary more than 200000
  16. Show all students who appeared for exam
  17. show student, subject and total marks of students whose total marks are more than 80