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
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
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
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:
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
INSERT INTO table_name VALUES (...);
14. Rename a Column Command (Standard SQL)
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
15. Delete All Records in a Table 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.