Topics:<\/p>\n\n\n\n
Assignments:<\/p>\n\n\n\n
CREATE DATABASE mydb;<\/pre>\n\n\n\n
- Create a table named “users” with columns id, name, age, city, added_at, and updated_at.<\/li><\/ol>\n\n\n\n
CREATE TABLE users (\n id INT PRIMARY KEY,\n name VARCHAR(50),\n age INT,\n city VARCHAR(50),\n added_at DATETIME,\n updated_at DATETIME\n );<\/pre>\n\n\n\n
- Insert 5 records into the “users” table.<\/li><\/ol>\n\n\n\n
INSERT INTO users (id, name, age, city, added_at, updated_at)\n VALUES\n (1, 'John', 25, 'Mumbai', '2022-01-01', '2022-01-02'),\n (2, 'Jane', 30, 'Delhi', '2022-01-03', '2022-01-04'),\n (3, 'Mike', 35, 'Bangalore', '2022-01-05', '2022-01-06'),\n (4, 'Lisa', 28, 'Chennai', '2022-01-07', '2022-01-08'),\n (5, 'David', 32, 'Kolkata', '2022-01-09', '2022-01-10');<\/pre>\n\n\n\n
- Retrieve all records from the “users” table.<\/li><\/ol>\n\n\n\n
SELECT * FROM users;<\/pre>\n\n\n\n
- Retrieve the names of users whose age is greater than 30.<\/li><\/ol>\n\n\n\n
SELECT name FROM users WHERE age > 30;<\/pre>\n\n\n\nDay 2: Data Manipulation<\/h2>\n\n\n\n
Topics:<\/p>\n\n\n\n
- Updating records with UPDATE statement<\/li>
- Deleting records with DELETE statement<\/li>
- Limiting and pagination with LIMIT and OFFSET<\/li>
- Aggregation functions: COUNT, SUM, AVG, MIN, MAX<\/li>
- Grouping data with GROUP BY<\/li><\/ol>\n\n\n\n
Assignments:<\/p>\n\n\n\n
- Update the age of the user with id 2 to 35.<\/li><\/ol>\n\n\n\n
UPDATE users SET age = 35 WHERE id = 2;<\/pre>\n\n\n\n
- Delete the user with id 4 from the “users” table.<\/li><\/ol>\n\n\n\n
DELETE FROM users WHERE id = 4;<\/pre>\n\n\n\n
- Retrieve the first 3 records from the “users” table.<\/li><\/ol>\n\n\n\n
SELECT * FROM users LIMIT 3;<\/pre>\n\n\n\n
- Retrieve the total number of users in the “users” table.<\/li><\/ol>\n\n\n\n
SELECT COUNT(*) FROM users;<\/pre>\n\n\n\n
- Retrieve the average age of users.<\/li><\/ol>\n\n\n\n
SELECT AVG(age) FROM users;<\/pre>\n\n\n\nDay 3: Filtering and Sorting<\/h2>\n\n\n\n
Topics:<\/p>\n\n\n\n
- Using WHERE clause for conditional filtering<\/li>
- Using comparison operators: =, <>, <, >, <=, >=<\/li>
- Using logical operators: AND, OR, NOT<\/li>
- Sorting data with ORDER BY clause<\/li>
- Sorting in ascending and descending order<\/li><\/ol>\n\n\n\n
Assignments:<\/p>\n\n\n\n
- Retrieve the names of users whose city is ‘Mumbai’.<\/li><\/ol>\n\n\n\n
SELECT name FROM users WHERE city = 'Mumbai';<\/pre>\n\n\n\n
- Retrieve the names of users whose age is between 25 and 35.<\/li><\/ol>\n\n\n\n
SELECT name FROM users WHERE age BETWEEN 25 AND 35;<\/pre>\n\n\n\n
- Retrieve the names of users whose city is not ‘Delhi’.<\/li><\/ol>\n\n\n\n
SELECT name FROM users WHERE city <> 'Delhi';<\/pre>\n\n\n\n
- Retrieve all records from the “users” table sorted by age in ascending order.<\/li><\/ol>\n\n\n\n
SELECT * FROM users ORDER BY age ASC;<\/pre>\n\n\n\n
- Retrieve all records from the “users” table sorted by name in descending order.<\/li><\/ol>\n\n\n\n
SELECT * FROM users ORDER BY name DESC;<\/pre>\n\n\n\nDay 4: Data Aggregation and Functions<\/h2>\n\n\n\n
Topics:<\/p>\n\n\n\n
- Using aggregate functions: COUNT, SUM, AVG, MIN, MAX<\/li>
- Working with NULL values: IS NULL, IS NOT NULL<\/li>
- Using mathematical functions: ROUND, CEILING, FLOOR<\/li>
- String functions: CONCAT, UPPER, LOWER, LENGTH<\/li>
- Date functions: NOW, DATE_FORMAT, DATE_ADD, DATE_SUB<\/li><\/ol>\n\n\n\n
Assignments:<\/p>\n\n\n\n
- Retrieve the total number of users in the “users” table.<\/li><\/ol>\n\n\n\n
SELECT COUNT(*) FROM users;<\/pre>\n\n\n\n
- Retrieve the sum of ages of all users.<\/li><\/ol>\n\n\n\n
SELECT SUM(age) FROM users;<\/pre>\n\n\n\n
- Retrieve the average age of users excluding NULL values.<\/li><\/ol>\n\n\n\n
SELECT AVG(age) FROM users WHERE age IS NOT NULL;<\/pre>\n\n\n\n
- Retrieve the concatenated names and cities of all users.<\/li><\/ol>\n\n\n\n
SELECT CONCAT(name, ', ', city) AS info FROM users;<\/pre>\n\n\n\n
- Retrieve the current date and time.<\/li><\/ol>\n\n\n\n
SELECT NOW();<\/pre>\n\n\n\nDay 5: Grouping and Filtering with HAVING Clause<\/h2>\n\n\n\n
Topics:<\/p>\n\n\n\n
- Grouping data with GROUP BY clause<\/li>
- Filtering grouped data with HAVING clause<\/li>
- Using aggregate functions with GROUP BY<\/li>
- Using multiple columns in GROUP BY<\/li>
- Combining GROUP BY, HAVING, and ORDER BY<\/li><\/ol>\n\n\n\n
Assignments:<\/p>\n\n\n\n
- Retrieve the names and ages of users grouped by city.<\/li><\/ol>\n\n\n\n
SELECT city, GROUP_CONCAT(name) AS names, GROUP_CONCAT(age) AS ages FROM users GROUP BY city;<\/pre>\n\n\n\n
- Retrieve the cities with more than 2 users.<\/li><\/ol>\n\n\n\n
SELECT city FROM users GROUP BY city HAVING COUNT(*) > 2;<\/pre>\n\n\n\n
- Retrieve the average age of users in each city.<\/li><\/ol>\n\n\n\n
SELECT city, AVG(age) AS average_age FROM users GROUP BY city;<\/pre>\n\n\n\n
- Retrieve the cities with the highest and lowest average age of users.<\/li><\/ol>\n\n\n\n
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);<\/pre>\n\n\n\n
- Retrieve the cities with at least 1 user whose age is greater than 30, sorted by city name.<\/li><\/ol>\n\n\n\n
SELECT city FROM users WHERE age > 30 GROUP BY city ORDER BY city ASC;<\/pre>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
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 a table named “users” with columns id, name, age, city, added_at, and updated_at. Insert 5 records into the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2546"}],"collection":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/comments?post=2546"}],"version-history":[{"count":1,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2546\/revisions"}],"predecessor-version":[{"id":2547,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/posts\/2546\/revisions\/2547"}],"wp:attachment":[{"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/media?parent=2546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/categories?post=2546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codeinsightacademy.com\/blog\/wp-json\/wp\/v2\/tags?post=2546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}