Dev.to

DAY6 PSQL Using Difference types of keys

Difference types of PSQL keys. Primary Key A unique identifier for each row in a table. Enforces uniqueness and not-null constraints. A table can have only one primary key. Example: CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL ); Foreign Key A field (or set of fields) in one table that references the primary key in another table. Enforces referential integrity (ensures valid relationships). Example: CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id) ON DELETE CASCADE ); Unique Key Ensures that values in a column (or group of columns) are unique, but allows NULL values. A table can have multiple unique keys. Example: CREATE TABLE employees ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE ); Composite Key A primary or unique key that consists of multiple columns. Useful when a single column is not sufficient for uniqueness. Example: CREATE TABLE enrollments ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id) ); Candidate Key Any column (or set of columns) that can serve as a unique identifier for a row. A table can have multiple candidate keys, but only one is chosen as the primary key. Example: In a table with id (PRIMARY KEY) and email (UNIQUE), both could be candidate keys. Super Key A set of one or more columns that uniquely identify a row. Every primary key and candidate key is a super key. Example: {id}, {email}, and {id, email} can all be super keys if they uniquely identify records. Alternate Key A candidate key that is not chosen as the primary key. Example: If both id and email can be unique, but id is chosen as the primary key, then email becomes an alternate key. When to Use Which Key? Use Primary Key for the main row identifier. Use Foreign Key to link tables. Use Unique Key for columns like email, username. Use Composite Key when a single column isn’t enough for uniqueness. PostgreSQL(PSQL),subqueries 1. Scalar Subquery Returns a single value (one row, one column). Used in SELECT, WHERE, or HAVING clauses. Example: SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); The subquery calculates the average salary, and the outer query selects employees earning above that amount. 2. Row Subquery Returns a single row but multiple columns. Typically used with operators like =, , IN, etc. Example: SELECT * FROM employees WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id LIMIT 1); This finds the highest-paid employee in a department. Table Subquery (Derived Table) Returns multiple rows and columns, acting like a temporary table. Used in the FROM clause. Example: SELECT department, avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg; The subquery calculates average salaries per department, and the outer query selects from it. Correlated Subquery Depends on the outer query for execution. Evaluated once per row in the outer query. Example: SELECT name, salary FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id); For each employee, the subquery calculates the department’s average salary. Exists Subquery Uses EXISTS to check if a subquery returns any rows. Example: SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.employee_id); The outer query returns employees who are department managers. IN Subquery Used with IN to check if a value exists in a set returned by the subquery. Example: SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York'); The outer query retrieves employees in New York-based departments. NOT IN Subquery Opposite of IN, returns records that do not match the subquery. Example: SELECT name FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location = 'New York'); Retrieves employees not in New York-based departments. ANY and ALL Subquery ANY: Compares a value against any value in a subquery. ALL: Compares a value against all values in a subquery. Example (ANY): SELECT name, salary FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2); Example (ALL): SELECT name, salary FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2);

Mar 26, 2025 - 17:02
 0
DAY6 PSQL Using Difference types of keys

Difference types of PSQL keys.

  1. Primary Key

    A unique identifier for each row in a table.

    Enforces uniqueness and not-null constraints.

    A table can have only one primary key.

    Example:

    CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
    );

  2. Foreign Key

    A field (or set of fields) in one table that references the primary key in another table.

    Enforces referential integrity (ensures valid relationships).

    Example:

    CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE
    );

  3. Unique Key

    Ensures that values in a column (or group of columns) are unique, but allows NULL values.

    A table can have multiple unique keys.

    Example:

    CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE
    );

  4. Composite Key

    A primary or unique key that consists of multiple columns.

    Useful when a single column is not sufficient for uniqueness.

    Example:

    CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
    );

  5. Candidate Key

    Any column (or set of columns) that can serve as a unique identifier for a row.

    A table can have multiple candidate keys, but only one is chosen as the primary key.

    Example: 

    In a table with id (PRIMARY KEY) and email (UNIQUE), both could be candidate keys.

  6. Super Key

    A set of one or more columns that uniquely identify a row.

    Every primary key and candidate key is a super key.

    Example: 

    {id}, {email}, and {id, email} can all be super keys if they uniquely identify records.

  7. Alternate Key

    A candidate key that is not chosen as the primary key.

    Example: 

    If both id and email can be unique, but id is chosen as the primary key, then email becomes an alternate key.

Image description

When to Use Which Key? 

Use Primary Key for the main row identifier.

Use Foreign Key to link tables.

Use Unique Key for columns like email, username.

Use Composite Key when a single column isn’t enough for uniqueness.

PostgreSQL(PSQL),subqueries

Image description

1. Scalar Subquery 

Returns a single value (one row, one column).

Used in SELECT, WHERE, or HAVING clauses.

Example:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The subquery calculates the average salary, and the outer query selects employees earning above that amount.

2. Row Subquery 

Returns a single row but multiple columns.

Typically used with operators like =, <>, IN, etc.

Example:

SELECT *
FROM employees
WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id LIMIT 1);

This finds the highest-paid employee in a department.

  1. Table Subquery (Derived Table)

    Returns multiple rows and columns, acting like a temporary table.

    Used in the FROM clause.

    Example:

    SELECT department, avg_salary
    FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg;

    The subquery calculates average salaries per department, and the outer query selects from it.

  2. Correlated Subquery

    Depends on the outer query for execution.

    Evaluated once per row in the outer query.

    Example:

    SELECT name, salary
    FROM employees e1
    WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

    For each employee, the subquery calculates the department’s average salary.

  3. Exists Subquery

    Uses EXISTS to check if a subquery returns any rows.

    Example:

    SELECT name
    FROM employees e
    WHERE EXISTS (SELECT 1 FROM departments d WHERE d.manager_id = e.employee_id);

    The outer query returns employees who are department managers.

  4. IN Subquery

    Used with IN to check if a value exists in a set returned by the subquery.

    Example:

    SELECT name
    FROM employees
    WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

    The outer query retrieves employees in New York-based departments.

  5. NOT IN Subquery

    Opposite of IN, returns records that do not match the subquery.

    Example:

    SELECT name
    FROM employees
    WHERE department_id NOT IN (SELECT department_id FROM departments WHERE location = 'New York');

    Retrieves employees not in New York-based departments.

  6. ANY and ALL Subquery

    ANY: Compares a value against any value in a subquery.

    ALL: Compares a value against all values in a subquery.

    Example (ANY):

SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 2);

Example (ALL):

SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 2);

Read More

Tags:

Previous Article

Letting the power of Agentic AI: How Autonomous Agents are Revolutionizing Cyber...

Next Article

Why I recommend Optimizing File and Folder Composition for Server Components First!

Related Posts

Memoization in React

Memoization in React

Feb 16, 2025  0

Code Craft # 4 - Into the github basics

Code Craft # 4 - Into the github basics

Mar 17, 2025  0

Navigating the Funding Maze: Open Source Software's Financial Challenges

Navigating the Funding Maze: Open Source Software's Fin...

Feb 19, 2025  0