Basic SQL Queries
1. Select All Data
Task: Retrieve all columns from the employees table.
SELECT * FROM employees;2. Select Specific Columns
Task: Retrieve only the name and position columns from the employees table.
SELECT name, position FROM employees;3. Filter Data
Task: List all employees with a salary greater than 50,000.
SELECT * FROM employees WHERE salary > 50000;4. Sort Data
Task: Sort employees by salary in descending order.
SELECT * FROM employees ORDER BY salary DESC;5. Select Unique Values
Task: Find all distinct position values in the employees table.
SELECT DISTINCT position FROM employees;Data Manipulation
6. Insert New Data
Task: Add a new employee to the employees table.
INSERT INTO employees (name, position, salary)
VALUES ('John Doe', 'Developer', 60000);7. Update Existing Data
Task: Update the salary of John Doe to 70,000.
UPDATE employees
SET salary = 70000
WHERE name = 'John Doe';8. Delete Data
Task: Remove employees with a salary less than 30,000.
DELETE FROM employees
WHERE salary < 30000;Relational Queries (JOIN)
9. Inner Join
Task: Join employees and departments tables to list employee names and their department names.
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;10. Left Join
Task: List all employees and their departments, including those without a department.
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;Grouping and Functions
11. Calculate Total Salary
Task: Calculate the total salary of all employees.
SELECT SUM(salary) AS total_salary FROM employees;12. Average Salary by Position
Task: Calculate the average salary for each position.
SELECT position, AVG(salary) AS average_salary
FROM employees
GROUP BY position;13. Employee Count Per Department
Task: Count the number of employees in each department.
SELECT departments.department_name, COUNT(employees.id) AS employee_count
FROM departments
LEFT JOIN employees
ON departments.id = employees.department_id
GROUP BY departments.department_name;14. Find the Highest Salary
Task: Retrieve the highest salary and the employee who earns it.
SELECT name, MAX(salary) AS highest_salary
FROM employees;Advanced SQL
15. Subqueries
Task: Find employees who earn more than the average salary.
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);16. Range Filtering (BETWEEN)
Task: List employees whose salaries are between 40,000 and 80,000.
SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 80000;17. Pattern Matching (LIKE)
Task: Find employees whose names start with “John.”
SELECT * FROM employees
WHERE name LIKE 'John%';Practice Tips
- Free tools for practicing SQL:
Example Dataset:
CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), position VARCHAR(50), salary DECIMAL(10, 2), department_id INT );
INSERT INTO employees (name, position, salary, department_id) VALUES ('Alice', 'Manager', 80000, 1), ('Bob', 'Developer', 60000, 2), ('Charlie', 'Intern', 30000, NULL);…………
Thank you for your time; sharing is caring! 🌍
…………
