2 of 10

Nested Queries (subqueries)

Queries placed inside another query
To make the program look more structured
Two types of nested queries

Independent :- Execution of the inner query is independent of the outer query

SELECT S_ID FROM STUDENT_COURSE �WHERE C_ID IN (� SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')�);

Correlated:- Inner query depends on the outer query for its execution

SELECT S_NAME FROM STUDENT S�WHERE EXISTS (� SELECT 1 FROM STUDENT_COURSE SC� WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'�);

3 of 10

JOINS

Used to combine data from multiple tables

INNER JOIN:- Selects all rows from both the tables as long as the condition is satisfied
LEFT JOIN:- Returns all the rows from the left table and the matching rows from the right table and NULL value is returned from right table if no match found
RIGHT JOIN:- Returns all the rows from the right table and the matching rows from the left table
FULL JOIN:- Creates the result set by combining both the results of LEFT JOIN and RIGHT JOIN. The result will create all the rows from both the tables.

4 of 10

SET Operations

These are used for combining/comparing the results of two or more SELECT statements

UNION:- Combines the result of two or more SELECT queries into a single result set, removing duplicate rows by default
INTERSECT:- It returns the row that appears in both the result set
EXCEPT:- It retrieves the row present in the first result set, but not in the second

These 3 operations can be extended to multiple rows set

SELECT emp_name, designation, manager_id

FROM employee_details

EXCEPT

SELECT emp_name, designation, manager_id

FROM employee_info;

5 of 10

GROUP BY & HAVING

Used to aggregate rows of your dataset
GROUP BY:- To aggregate rows of your dataset
HAVING :- Filters rows within each group defined by GROUP BY

SELECT

product_line,

AVG(unit_price) AS avg_price,

SUM(quantity) AS tot_pieces,

SUM(total) AS total_gain

FROM sales

GROUP BY product_line

HAVING SUM(total) > 40000

ORDER BY total_gain DESC

6 of 10

INTEGRITY CONSTRAINTS

To ensure our dataset remains accurate, consistent and reliable
PRIMARY KEY: Uniquely identifies each record in a table.
NOT NULL: Ensures a column cannot contain NULL values.
UNIQUE: Ensures that all values in a column or group of columns are unique.
DEFAULT: Provides a default value for a column when none is specified.
CHECK: Ensures that all values in a column satisfy a specific condition.
FOREIGN KEY: Establishes relationships between tables by referencing a primary key in another table.

7 of 10

Window Functions

Using normal aggregate function SQL groups rows together into one result

SELECT department, SUM(salary)

FROM employees

GROUP BY department;

This returns one row per department

A window function does not group rows.�It calculates something for each row, while still being able to look at other rows related to it.

8 of 10

SELECT

name,

department,

salary,

AVG(salary) OVER (PARTITION BY department) AS dept_avg

FROM employees;

Name	Department	Salary	dept_avg
Alice	HR	5000	4500
Bob	HR	4000	4500
Carol	IT	6000	5500
Dave	IT	5000	5500

9 of 10

Common Table Expressions (CTEs)

In the below table suppose, you want to find out who earns more than the average

WITH note AS ( -- write the CTE

SELECT AVG(salary) AS avg_sal

FROM employees

)

SELECT name, salary -- Step-2: Read

FROM employees, note

WHERE salary > note.avg_sal;

id	name	Salary
1	Alice	50000
2	Bob	80000
3	John	30000

10 of 10

Transactions

Do both the steps – or do nothing

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE name = 'You’;

UPDATE accounts SET balance = balance + 500 WHERE name = 'Friend’;

COMMIT;

ROLLBACK if not executed completely

1 of 10