SQL & PostgreSQL Flash Notes

These flashcards are designed to cover key vocabulary and concepts related to SQL and PostgreSQL to aid in exam preparation.

Relational Database

A database structured to recognize and manage relationships between data in tables.

Relational vs Non-Relational Databases

Relational databases (SQL) use structured data with tables and fixed schema, while Non-Relational databases (NoSQL) can store unstructured data like JSON and documents.

psql Command: \l

Lists all databases in PostgreSQL.

Creating a Table Command

CREATE TABLE table_name (…); Used to create a new table in SQL.

Foreign Key

A key that creates a relationship between two tables, linking a child table to a parent table.

Add a Column to a Table Command

ALTER TABLE tablename ADD COLUMN columnname data_type; Used to add a new column to an existing table.

One-to-Many Relationship (1-m)

A relationship where one record in Table A corresponds to multiple records in Table B.

What is a Query?

A request for specific data from a database, such as SELECT * FROM employees;.

Delete a Column Command

ALTER TABLE tablename DROP COLUMN columnname; Used to remove a column from a table.

PRIMARY KEY Constraint

Ensures that column values are unique, not NULL, and used to identify each row.

psql Command: \c

Connects to a specified database, such as \c mydatabase.

Composite Key

A key made of multiple columns that together uniquely identify a record, such as (CustomerID + OrderID).

Insert Data into a Table Command

INSERT INTO table_name VALUES (…); Used to add new data into a table.

Rename a Column Command

ALTER TABLE tablename RENAME COLUMN oldname TO new_name; Used to change the name of a column.

Delete All Records in a Table Command

DELETE FROM employees; Removes all rows from a table while keeping the table itself.

UNIQUE Constraint

Ensures all values in a column are unique, allowing NULLs unlike PRIMARY KEY.

Common Relational Databases

PostgreSQL, MySQL, SQLite.

One-to-One Relationship (1-1)

A relationship where one record in Table A corresponds to exactly one record in Table B.

List Tables in PostgreSQL Command

\d; Lists tables/relations in the current database.

Self Join

Joining a table with itself, such as relationships within an employee table.