Complete Guide to SQL Queries, Joins, and Aggregation

Creating and Populating Tables in SQL

Creating Tables

Tables are created using the CREATE TABLE command to define their structure:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

Example:

CREATE TABLE Dept (
    deptNo INTEGER PRIMARY KEY,
    dName VARCHAR(20) UNIQUE NOT NULL,
    dLoc VARCHAR(30) NOT NULL,
    createDate DATE
);

Populating Tables

Data is inserted into tables using the INSERT INTO statement:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example:

INSERT INTO Dept (deptNo, dName, dLoc, createDate
VALUES (10, 'Database Management', 'New Cav', '2021-03-21');

Querying Existing Tables in SQL

Retrieving Data

• Select all columns:

  SELECT * FROM table_name;

• Select specific columns:

  SELECT column1, column2 FROM table_name;

Arithmetic Expressions and Concatenation

Perform arithmetic on numeric columns and concatenate strings:

• Arithmetic:

  SELECT salary, salary + 500 AS updated_salary FROM Emp;

• Concatenation:

  SELECT CONCAT(fName, ' ', lName) AS full_name FROM Emp;

Filtering Data

• Using WHERE:

  SELECT * FROM Emp 
  WHERE salary > 4000 
  AND deptNo = 10;

• Operators:

  • Comparison: =, <, >, <=, >=, <>
  • Logical: AND, OR, NOT

Retrieve Records from Multiple Tables

Join Statements

Join (Default: Inner Join)

Retrieves records with matching values in both tables:

SELECT D.dName, E.fName
FROM Dept D JOIN Emp E 
ON D.deptNo = E.deptNo;

Left-Outer Join

Retrieves matching records plus records from the left table with no match in the right table (NULL for unmatched values):

SELECT D.dName, E.fName
FROM Dept D LEFT OUTER JOIN Emp E 
ON D.deptNo = E.deptNo;

Right-Outer Join

Retrieves matching records plus records from the right table with no match in the left table:

SELECT D.dName, E.fName
FROM Dept D RIGHT OUTER JOIN Emp E 
ON D.deptNo = E.deptNo;

Full-Outer Join (Simulated with UNION)

Combines left and right outer joins:

SELECT D.dName, E.fName
FROM Dept D LEFT OUTER JOIN Emp E 
ON D.deptNo = E.deptNo
UNION
SELECT D.dName, E.fName
FROM Dept D RIGHT OUTER JOIN Emp E 
ON D.deptNo = E.deptNo;

Aggregate Data with Group Functions and Subqueries

Group Functions in SQL

Aggregate functions operate on a group of rows:

SELECT MIN(salary), MAX(salary), AVG(salary), SUM(salary) 
FROM Emp;

Grouping Data

Use GROUP BY to group rows:

SELECT deptNo, AVG(salary)
FROM Emp
GROUP BY deptNo;

Restrict grouped results using HAVING:

SELECT deptNo, AVG(salary)
FROM Emp
GROUP BY deptNo
HAVING AVG(salary) > 4500;

Subqueries

Subqueries can return a single value or multiple values to use in a main query.

Single-Row Subqueries

Compare with a single value:

SELECT lName, fName, salary
FROM Emp
WHERE salary > 
                (SELECT AVG(salary) 
                FROM Emp);

Multiple-Row Subqueries

Compare with multiple values using IN, ANY, or ALL:

• ANY: Compare to any value in a subquery result:

  SELECT lName 
  FROM Emp 
  WHERE salary > ANY 
                      (SELECT salary 
                      FROM Emp 
                      WHERE deptNo = 10);

• ALL: Compare to all values in a subquery result:

  SELECT lName 
  FROM Emp 
  WHERE salary > ALL 
                      (SELECT salary 
                      FROM Emp 
                      WHERE deptNo = 10);