Scribd
Upload
5 views3 pages

SQL Query Notes

This document provides a quick revision of SQL queries, covering basic SELECT statements, WHERE conditions, ORDER BY clauses, DISTINCT usage, functions, GROUP BY, JOINS, VIEW creation, TRIGGERS, Transaction Control Language (TCL), and INDEX creation. It includes examples for each topic to illustrate their usage. The content is structured for easy reference and understanding of SQL syntax and operations.

Uploaded by

shivamc4505
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

SQL Query Notes

This document provides a quick revision of SQL queries, covering basic SELECT statements, WHERE conditions, ORDER BY clauses, DISTINCT usage, functions, GROUP BY, JOINS, VIEW creation, TRIGGERS, Transaction Control Language (TCL), and INDEX creation. It includes examples for each topic to illustrate their usage. The content is structured for easy reference and understanding of SQL syntax and operations.

Uploaded by

shivamc4505
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL Query Notes (Quick Revision)

1. BASIC SELECT
SELECT ename, sal FROM emp;
SELECT ename, sal*12 AS annual_salary FROM emp;

2. WHERE CONDITIONS
BETWEEN:
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 5000;

IN:
SELECT * FROM emp WHERE job IN ('clerk','manager');

LIKE:
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE '%S';
SELECT * FROM emp WHERE ename LIKE '_A%';

NULL:
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;

3. ORDER BY
SELECT * FROM emp ORDER BY sal;
SELECT * FROM emp ORDER BY sal DESC;
SELECT * FROM emp ORDER BY deptno, sal DESC;

4. DISTINCT
SELECT DISTINCT job FROM emp;

5. FUNCTIONS

String:
SELECT UPPER(ename) FROM emp;
SELECT LOWER(ename) FROM emp;
SELECT SUBSTR('HELLO',1,2);
SELECT LENGTH('HELLO');

Aggregate:
SELECT SUM(sal) FROM emp;
SELECT AVG(sal) FROM emp;
SELECT MAX(sal) FROM emp;
SELECT COUNT(*) FROM emp;

6. GROUP BY
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

HAVING:
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 3000;

7. JOINS

Inner Join:
SELECT [Link], [Link]
FROM emp e, dept d
WHERE [Link] = [Link];

ANSI Join:
SELECT [Link], [Link]
FROM emp e INNER JOIN dept d
ON [Link] = [Link];

Left Join:
SELECT [Link], [Link]
FROM emp e LEFT JOIN dept d
ON [Link] = [Link];

Self Join:
SELECT [Link], [Link] AS manager
FROM emp e, emp m
WHERE [Link] = [Link];

Cross Join:
SELECT * FROM emp CROSS JOIN dept;

8. VIEW
CREATE VIEW v1 AS
SELECT ename, sal FROM emp;
SELECT * FROM v1;

9. TRIGGER (Basic)
CREATE TRIGGER trg1
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
-- logic here
END;

10. TCL
COMMIT;
ROLLBACK;
SAVEPOINT sp1;
ROLLBACK TO sp1;

11. INDEX
CREATE INDEX idx1 ON emp(ename);

You might also like