You’re the Expert!

pynfinity

sql

Sql

Your complete recipe for mastering Sql — course modules & quick-reference guide in one place.

DB Recipe Module 3 sections
Back to DB Quick Reference

BASICS

Create a Table and Insert Data

Explore the concepts and examples below to master this topic.

Code Example 
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES (1, 'Santosh K', 30, 'Engineering'),
       (2, 'T V Santosh', 25, 'Marketing'),
       (3, 'Dhruv', 35, 'HR');

SELECT * FROM employees;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Basic SELECT Statement

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, age FROM employees;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Filtering Data with WHERE Clause

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, department FROM employees
WHERE age > 30;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Sorting Results with ORDER BY

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, age FROM employees
ORDER BY age DESC;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Updating Data with UPDATE Statement

Explore the concepts and examples below to master this topic.

Code Example 
UPDATE employees
SET age = 32
WHERE name = 'John Doe';

SELECT * FROM employees;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Deleting Data with DELETE Statement

Explore the concepts and examples below to master this topic.

Code Example 
DELETE FROM employees
WHERE name = 'Dhruv K';

SELECT * FROM employees;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

INTERMEDIATE

JOIN Operations (INNER JOIN)

Explore the concepts and examples below to master this topic.

Code Example 
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name)
VALUES (1, 'Engineering'),
       (2, 'Marketing'),
       (3, 'HR');

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department = departments.department_name;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Aggregate Functions (COUNT, AVG, MAX, MIN, SUM)

Explore the concepts and examples below to master this topic.

Code Example 
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

SELECT AVG(age) AS average_age FROM employees;

SELECT MAX(age) AS oldest_employee_age FROM employees;

SELECT MIN(age) AS youngest_employee_age FROM employees;

SELECT SUM(age) AS total_age FROM employees;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Using GROUP BY with HAVING

Explore the concepts and examples below to master this topic.

Code Example 
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Using LIKE Operator for Pattern Matching

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name FROM employees
WHERE name LIKE 'J%';
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Working with NULL Values

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, age FROM employees
WHERE age IS NULL;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Subqueries (Nested Queries)

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, age
FROM employees
WHERE age > (SELECT AVG(age) FROM employees);
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

ADVANCED

UNION and UNION ALL

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, age FROM employees WHERE department = 'Engineering'
UNION
SELECT name, age FROM employees WHERE department = 'Marketing';

SELECT name, age FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT name, age FROM employees WHERE department = 'Marketing';
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Creating and Using Views

Explore the concepts and examples below to master this topic.

Code Example 
CREATE VIEW employee_summary AS
SELECT name, department, age
FROM employees;

SELECT * FROM employee_summary;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Creating Indexes to Improve Query Performance

Explore the concepts and examples below to master this topic.

Code Example 
CREATE INDEX idx_department ON employees(department);

SELECT * FROM employees WHERE department = 'Engineering';
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Using Transactions (BEGIN, COMMIT, ROLLBACK)

Explore the concepts and examples below to master this topic.

Code Example 
BEGIN;

UPDATE employees SET age = 40 WHERE name = 'Santosh K';

COMMIT;

-- ROLLBACK example:
-- BEGIN;
-- DELETE FROM employees WHERE name = 'Santosh K';
-- ROLLBACK;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Advanced JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN)

Explore the concepts and examples below to master this topic.

Code Example 
-- Left Join
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department = departments.department_name;

-- Right Join
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department = departments.department_name;

-- Full Join (if supported by your DBMS)
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department = departments.department_name;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Using CASE Statements for Conditional Logic

Explore the concepts and examples below to master this topic.

Code Example 
SELECT name, age,
       CASE
         WHEN age >= 30 THEN 'Senior'
         WHEN age < 30 THEN 'Junior'
         ELSE 'Unknown'
       END AS experience_level
FROM employees;
Video Walkthrough
Coming soon — in-depth video explanation for this topic.

Using Stored Procedures

Explore the concepts and examples below to master this topic.

Code Example 
DELIMITER //

CREATE PROCEDURE get_employee_by_department(dept VARCHAR(50))
BEGIN
  SELECT name, age
  FROM employees
  WHERE department = dept;
END //

DELIMITER ;

-- Call the stored procedure:
CALL get_employee_by_department('Engineering');
Video Walkthrough
Coming soon — in-depth video explanation for this topic.
⚡ Quick Reference — Recipe

Use the reference cards below for fast lookup. Perfect for brushing up on syntax while you cook your code. 🍳

Create a Table and Insert Data 
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50)
);

INSERT INTO employees (id, name, age, department)
VALUES (1, 'Santosh K', 30, 'Engineering'),
       (2, 'T V Santosh', 25, 'Marketing'),
       (3, 'Dhruv', 35, 'HR');

SELECT * FROM employees;
Basic SELECT Statement 
SELECT name, age FROM employees;
Filtering Data with WHERE Clause 
SELECT name, department FROM employees
WHERE age > 30;
Sorting Results with ORDER BY 
SELECT name, age FROM employees
ORDER BY age DESC;
Updating Data with UPDATE Statement 
UPDATE employees
SET age = 32
WHERE name = 'John Doe';

SELECT * FROM employees;
Deleting Data with DELETE Statement 
DELETE FROM employees
WHERE name = 'Dhruv K';

SELECT * FROM employees;

JOIN Operations (INNER JOIN) 
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name)
VALUES (1, 'Engineering'),
       (2, 'Marketing'),
       (3, 'HR');

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department = departments.department_name;
Aggregate Functions (COUNT, AVG, MAX, MIN, SUM) 
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

SELECT AVG(age) AS average_age FROM employees;

SELECT MAX(age) AS oldest_employee_age FROM employees;

SELECT MIN(age) AS youngest_employee_age FROM employees;

SELECT SUM(age) AS total_age FROM employees;
Using GROUP BY with HAVING 
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Using LIKE Operator for Pattern Matching 
SELECT name FROM employees
WHERE name LIKE 'J%';
Working with NULL Values 
SELECT name, age FROM employees
WHERE age IS NULL;
Subqueries (Nested Queries) 
SELECT name, age
FROM employees
WHERE age > (SELECT AVG(age) FROM employees);

UNION and UNION ALL 
SELECT name, age FROM employees WHERE department = 'Engineering'
UNION
SELECT name, age FROM employees WHERE department = 'Marketing';

SELECT name, age FROM employees WHERE department = 'Engineering'
UNION ALL
SELECT name, age FROM employees WHERE department = 'Marketing';
Creating and Using Views 
CREATE VIEW employee_summary AS
SELECT name, department, age
FROM employees;

SELECT * FROM employee_summary;
Creating Indexes to Improve Query Performance 
CREATE INDEX idx_department ON employees(department);

SELECT * FROM employees WHERE department = 'Engineering';
Using Transactions (BEGIN, COMMIT, ROLLBACK) 
BEGIN;

UPDATE employees SET age = 40 WHERE name = 'Santosh K';

COMMIT;

-- ROLLBACK example:
-- BEGIN;
-- DELETE FROM employees WHERE name = 'Santosh K';
-- ROLLBACK;
Advanced JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN) 
-- Left Join
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department = departments.department_name;

-- Right Join
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department = departments.department_name;

-- Full Join (if supported by your DBMS)
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department = departments.department_name;
Using CASE Statements for Conditional Logic 
SELECT name, age,
       CASE
         WHEN age >= 30 THEN 'Senior'
         WHEN age < 30 THEN 'Junior'
         ELSE 'Unknown'
       END AS experience_level
FROM employees;
Using Stored Procedures 
DELIMITER //

CREATE PROCEDURE get_employee_by_department(dept VARCHAR(50))
BEGIN
  SELECT name, age
  FROM employees
  WHERE department = dept;
END //

DELIMITER ;

-- Call the stored procedure:
CALL get_employee_by_department('Engineering');

Recipe Complete! 🎉

You've explored the full Sql recipe.
Keep practising — a language a day keeps AI away! 🤖

Back to DB Courses

Pynfinity
Install Pynfinity Add to home screen for the best experience