Untitled

SQL & PostgreSQL Flash Notes


1. Relational Database Definition

  • Definition: A database structured to recognize and manage relationships between data in tables.
  • Key Idea: Consists of tables interrelated by relationships, which include rows and columns with keys.

2. Relational vs Non-Relational Databases

  • Relational (SQL):
    • Characteristics:
    • Structured data
    • Organizes data in tables with a fixed schema
  • Non-Relational (NoSQL):
    • Characteristics:
    • Capable of storing unstructured data formats, such as JSON and documents.

3. psql Command to List Databases

  • Command: \l
  • Meaning: Lists all databases available in PostgreSQL.

4. Creating a Table Command

  • Command:
  CREATE TABLE table_name (...);  
  • Use: Creates a new table within the SQL framework.

5. Foreign Key

  • Purpose:
    • To create a relationship between two tables.
    • Links a child table to a parent table.

6. Add a Column to a Table Command

  • Command:
  ALTER TABLE table_name ADD COLUMN column_name data_type;  

7. One-to-Many Relationship (1-m)

  • Meaning:
    • Indicates that one record in Table A may be associated with multiple records in Table B.
  • Example:
    • One customer may place many orders.

8. What is a Query?

  • Definition:
    • A request for specific data from a database.
  • Example:
  SELECT * FROM employees;  

9. Delete a Column Command

  • Command:
  ALTER TABLE table_name DROP COLUMN column_name;  

10. PRIMARY KEY Constraint

  • Function:
    • Ensures that all values in a column are:
    • Unique
    • Not NULL
    • Used to uniquely identify each row within a table.

11. psql Command to Connect to Database

  • Command: \c
  • Meaning: Connects to a specified database.
  • Example:
    • \c mydatabase

12. Composite Key

  • Definition:
    • A key that is formed by multiple columns together, which uniquely identify a record.
  • Example:
    • Combination of (CustomerID + OrderID) can serve as a composite key.

13. Insert Data into a Table Command

  • Command:
  INSERT INTO table_name VALUES (...);  

14. Rename a Column Command (Standard SQL)

  • Command:
  ALTER TABLE table_name RENAME COLUMN old_name TO new_name;  

15. Delete All Records in a Table Command

  • Command:
  DELETE FROM employees;  
  • Note:
    • Deletes all rows in the specified table, while keeping the table structure intact.

16. UNIQUE Constraint

  • Purpose:
    • Ensures that all values in a specified column are unique.
    • Unlike a PRIMARY KEY, it can allow NULL values.

17. Common Relational Databases

  • Examples of Relational Databases:
    • PostgreSQL
    • MySQL
    • SQLite (Not SQLQuery)

18. One-to-One Relationship (1-1)

  • Meaning:
    • Indicates that one record in Table A corresponds to exactly one record in Table B.
  • Example:
    • One user has one profile.

19. List Tables in PostgreSQL

  • Command: \d
  • Meaning: Lists all tables or relations available in the current database.

20. Self Join

  • Definition:
    • Joining a table to itself.
  • Example Use:
    • An employee may serve as a manager for other employees, requiring a self join on the Employee table.

Quick Exam Memory Hack

  • For effective memorization and understanding of database work in IT/IS courses:
    • Structure changes: Use the ALTER TABLE command.
    • Create objects: Use the CREATE command.
    • Add data: Use the INSERT command.
    • Get data: Use the SELECT command.
    • Remove data: Use the DELETE command.
    • Establish relationships: Use PRIMARY KEY and FOREIGN KEY constraints.