SQL Day 6: Advanced Queries, Joins, and Database Operations

Day 6;

Subquery vs Join

Subquery

• A query inside another query.
• Used when you need a result from one query to use in another.
• Can be used in SELECT, WHERE, and HAVING clauses.
• Example:

SELECT name FROM employees WHERE dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'HR');

Join

• Combines rows from multiple tables based on a common column.
• More efficient than subqueries in most cases.
• Example:

SELECT employees.name, departments.dept_name
  FROM employees
  INNER JOIN departments ON employees.dept_id = departments.dept_id;

When to Use What?

• Use Joins when fetching data from multiple tables efficiently.
• Use Subqueries when performing nested queries, like filtering results dynamically.
• You can replace subqueries with joins, but you can't always replace joins with subqueries.

IN vs =

• IN: Used when checking multiple values.

SELECT * FROM students WHERE dept_id IN (1, 2, 3);

• =: Used when checking a single value.

SELECT * FROM students WHERE dept_id = 1;

Advanced Joins

• Cross Join: Combines all rows from both tables (Cartesian Product).

SELECT * FROM table1 CROSS JOIN table2;

• Self Join: A table joins itself.

SELECT e1.name, e2.name FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.emp_id;

Subqueries in Different Scenarios

• Subquery in SELECT:

SELECT name, (SELECT dept_name FROM departments WHERE dept_id = employees.dept_id) AS department FROM employees;

• Subquery in FROM:

SELECT avg_salary FROM (SELECT AVG(salary) AS avg_salary FROM employees) AS temp;

UNION vs INTERSECT vs EXCEPT

• UNION: Combines results from two queries (removes duplicates).

SELECT name FROM table1 UNION SELECT name FROM table2;

• INTERSECT: Returns common records between two queries.

SELECT name FROM table1 INTERSECT SELECT name FROM table2;

• EXCEPT: Returns records from one query that do not exist in another.

SELECT name FROM table1 EXCEPT SELECT name FROM table2;

CHAR vs VARCHAR

Example:

CREATE TABLE test (
    col1 CHAR(10),
    col2 VARCHAR(10)
);

SQL Commands Overview

DDL (Data Definition Language) - Defines database structure

• CREATE, ALTER, DROP

DML (Data Manipulation Language) - Modifies data

• INSERT, UPDATE, DELETE

DQL (Data Query Language) - Retrieves data

• SELECT

TCL (Transaction Control Language) - Manages transactions

• COMMIT, ROLLBACK, SAVEPOINT

DCL (Data Control Language) - Manages permissions

• GRANT, REVOKE

PL/SQL - Procedural SQL (used for stored procedures, triggers)

Basic SQL Operations

Create a Database & Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

Modify Table Structure

Change vs Rename

• Change Column Type

ALTER TABLE employees MODIFY salary FLOAT;

• Rename Column

ALTER TABLE employees RENAME COLUMN name TO full_name;

• Rename Table

RENAME TABLE employees TO staff;

• Rename Database (Not supported in all SQL versions)

ALTER DATABASE old_db_name MODIFY NAME = new_db_name;

Add a Column

ALTER TABLE employees ADD department VARCHAR(50);

Primary Key Rules

• Must be unique.
• Cannot be NULL.
• One table can have only one primary key.
• Example:

ALTER TABLE employees ADD CONSTRAINT pk_emp PRIMARY KEY (id);

Comments in SQL

• Single-line Comment: -- This is a comment
• Multi-line Comment:

/*
   This is a
   multi-line comment
  */

DROP vs DELETE vs TRUNCATE

Example:

DROP TABLE employees; -- Removes table permanently
DELETE FROM employees WHERE id = 5; -- Removes one row
TRUNCATE TABLE employees; -- Deletes all rows