SQL Relationships, Joins, and Logic Operators Summary

1. SQL Relationships (Modeling)

Relationships define how data in one table is linked to data in another.

A. One-to-One (1:1)

• Definition: Each record in Table A links to exactly one record in Table B.
• Implementation:
  • Create a Foreign Key (FK) in the child table referencing the parent's Primary Key.
  • Crucial Step: The Foreign Key column must have a UNIQUE constraint.
• Example (Person & Passport):
  • Person Table: person_id (PK).
  • Passport Table: passport_id (PK), person_id (FK, UNIQUE).
• Example (Employee & Payroll):
  • EMPLOYEE Table: EMPLOYEE_id (PK).
  • PAYROLL Table: PAYROLL_ID (PK), EMPLOYEE_ID (FK, UNIQUE).

B. One-to-Many (1:M)

• Definition: A single record in Table A links to multiple records in Table B.
• Implementation:
  • Create a Foreign Key (FK) in the "Many" side (Child table).
  • The FK does not have a UNIQUE constraint.
• Example (Customer & Orders):
  • CUSTOMERS Table: CUSTOMER_ID (PK).
  • ORDERS Table: ORDER_ID (PK), CUSTOMER_ID (FK).
  • One Customer can have many Orders.

---

2. SQL Clauses & Operators

A. Sorting Data (ORDER BY)

Used to sort the result set of a query.

• ASC (Ascending): Default sort order (Lowest to Highest, A-Z).
  • SELECT * FROM table ORDER BY column ASC;
• DESC (Descending): Sorts from Highest to Lowest (Z-A).
  • SELECT * FROM table ORDER BY column DESC;

B. Filtering Data (WHERE)

Used to extract only those records that fulfill a specified condition.

• Comparison Operators: =, >, <, >=, <=, <> (not equal).
• Logical Operators:
  • AND: Records must satisfy both conditions.
  • OR: Records must satisfy at least one condition.
  • NOT: Negates a condition.
  • BETWEEN: Selects values within a given range.
• Example:

  SELECT * FROM EMPLOYEE
  WHERE ADDRESS = 'Cairo' AND Salary >= 5000;

---

3. SQL JOINS

Joins are used to combine rows from two or more tables based on a related column between them.

A. INNER JOIN

• Function: Returns records that have matching values in both tables.
• Venn Diagram: The intersection of the two circles.
• Syntax:

  SELECT * FROM Table1
  INNER JOIN Table2 ON Table1.ID = Table2.ID;

B. LEFT (OUTER) JOIN

• Function: Returns all records from the left table, and the matched records from the right table.
• Result: If there is no match in the right table, the result is NULL for the right side columns.
• Syntax:

  SELECT * FROM Customers
  LEFT JOIN Orders ON Customers.ID = Orders.CustomerID;

C. RIGHT (OUTER) JOIN

• Function: Returns all records from the right table, and the matched records from the left table.
• Result: If there is no match in the left table, the result is NULL for the left side columns.

D. FULL (OUTER) JOIN

• Function: Returns all records when there is a match in either the left or right table.