SQL Concepts in MySQL (ANSI SQL)
1. SELECT Statement
The SELECT statement is used to retrieve data from a database.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SELECT first_name, last_name
FROM employees
WHERE department = 'HR';
2. SQL Operators
- Arithmetic: +, -, *, /, %
Example: SELECT salary, salary+500 AS new_salary FROM employees;
- Comparison: =, !=, <, >, <=, >=
Example: SELECT * FROM employees WHERE salary > 50000;
- Logical: AND, OR, NOT
Example: SELECT * FROM employees WHERE department='HR' AND salary > 30000;
- IN, BETWEEN, LIKE, IS NULL
Example:
SELECT * FROM employees WHERE department IN ('HR','Finance');
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;
SELECT * FROM employees WHERE first_name LIKE 'A%';
SELECT * FROM employees WHERE manager_id IS NULL;
3. SQL Functions
- Aggregate: COUNT(), AVG(), MAX(), MIN(), SUM()
Example: SELECT COUNT(*), AVG(salary) FROM employees;
- String: UPPER(), LOWER(), LENGTH()
Example: SELECT UPPER(first_name) FROM employees;
- Date: NOW(), CURDATE(), YEAR(), MONTH()
Example: SELECT YEAR(hire_date) FROM employees;
- Numeric: ROUND(), CEIL(), FLOOR()
Example: SELECT ROUND(salary,0) FROM employees;
4. Clauses in SQL
- WHERE → Filters rows
- GROUP BY → Groups rows for aggregate functions
- HAVING → Filters groups
- ORDER BY → Sorts results
- LIMIT → Restricts rows returned
Example:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 40000
ORDER BY AVG(salary) DESC
LIMIT 5;
5. Joins and Types
- INNER JOIN → Matching rows in both tables
- LEFT JOIN → All from left + matched from right
- RIGHT JOIN → All from right + matched from left
- FULL OUTER JOIN → UNION of LEFT and RIGHT JOIN (not direct in MySQL)
- CROSS JOIN → Cartesian product
Example:
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
6. Sub-queries
A query inside another query.
- In WHERE:
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
- In FROM:
SELECT dept, avg_sal
FROM (SELECT department AS dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY department) t;
- In SELECT:
SELECT first_name,
(SELECT department_name FROM departments d WHERE d.department_id = e.department_id)
AS dept
FROM employees e;
7. Views
A view is a stored query that behaves like a virtual table.
CREATE VIEW high_salary_employees AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;
SELECT * FROM high_salary_employees;
8. Indexes
Indexes improve query performance by speeding up lookups.
- Create Index:
CREATE INDEX idx_lastname ON employees(last_name);
- Unique Index:
CREATE UNIQUE INDEX idx_empid ON employees(employee_id);
- Drop Index:
DROP INDEX idx_lastname ON employees;
