SQL Complete Notes with Examples
1. DDL (Data Definition Language)
---------------------------------
CREATE:
CREATE TABLE emp (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary DECIMAL(10,2),
dept VARCHAR(20)
);
ALTER:
ALTER TABLE emp ADD age INT;
DROP:
DROP TABLE emp;
TRUNCATE:
TRUNCATE TABLE emp;
RENAME:
RENAME emp TO employee;
2. DML (Data Manipulation Language)
-----------------------------------
INSERT:
INSERT INTO emp (emp_id, emp_name, salary, dept)
VALUES (101, 'Rahul', 50000, 'HR');
SELECT:
SELECT emp_name, salary FROM emp WHERE dept = 'HR';
UPDATE:
UPDATE emp SET salary = 55000 WHERE emp_id = 101;
DELETE:
DELETE FROM emp WHERE emp_id = 101;
3. DCL (Data Control Language)
-------------------------------
GRANT:
GRANT SELECT, INSERT ON emp TO user1;
REVOKE:
REVOKE INSERT ON emp FROM user1;
4. TCL (Transaction Control Language)
-------------------------------------
COMMIT:
INSERT INTO emp VALUES (102, 'Amit', 40000, 'IT');
COMMIT;
ROLLBACK:
DELETE FROM emp WHERE emp_id = 102;
ROLLBACK;
SAVEPOINT:
SAVEPOINT sp1;
ROLLBACK TO:
ROLLBACK TO sp1;
5. DQL (Data Query Language)
-----------------------------
SELECT:
SELECT * FROM emp;
6. JOINS
---------
INNER JOIN:
SELECT e.emp_name, d.dept_name
FROM emp e INNER JOIN dept d
ON [Link] = d.dept_id;
LEFT JOIN:
SELECT e.emp_name, d.dept_name
FROM emp e LEFT JOIN dept d
ON [Link] = d.dept_id;
RIGHT JOIN:
SELECT e.emp_name, d.dept_name
FROM emp e RIGHT JOIN dept d
ON [Link] = d.dept_id;
FULL OUTER JOIN (using UNION):
SELECT e.emp_name, d.dept_name
FROM emp e LEFT JOIN dept d ON [Link] = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM emp e RIGHT JOIN dept d ON [Link] = d.dept_id;
7. Subquery
------------
SELECT emp_name
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp);
8. Set Operators
-----------------
SELECT emp_name FROM emp1
UNION
SELECT emp_name FROM emp2;
9. Clauses
-----------
SELECT dept, COUNT(*)
FROM emp
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY dept;
10. Aggregate Functions
------------------------
SELECT MAX(salary) AS Highest,
MIN(salary) AS Lowest,
AVG(salary) AS Average,
COUNT(*) AS TotalEmployees
FROM emp;