SQL and PostgreSQL Lecture Review

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/25

flashcard set

Earn XP

Description and Tags

Flashcards covering database types, Docker setup, PostgreSQL commands, schema design, normalization, joins, indexing, procedural logic, and transaction controls.

26 Terms

1
New cards

What are the primary structural differences between Relational and NoSQL databases?

Relational databases organize data into tables with rows and columns and use SQL (e.g., MySQL, PostgreSQL, Oracle). NoSQL databases are designed for handling unstructured, semi-structured, or rapidly changing data (e.g., MongoDB, Cassandra, Redis).

2
New cards

What PowerShell command serves as the 'nuclear option' to clean up Docker Desktop directory data if reinstallation fails?

Remove-Item -Path "C:\ProgramData\DockerDesktop" -Recurse -Force

3
New cards

How are Docker Hub, Docker Images, and Docker Containers defined?

Docker Hub is a registry similar to GitHub for Docker Images; Docker Images are bundled snapshots and instructions used to create containers; and a Docker Container is a running instance of a Docker Image.

4
New cards

What are the five sublanguages of SQL and an example command for each?

  1. DDL (Data Definition Language): CREATE, DROP, ALTER, TRUNCATE
  2. DML (Data Manipulation Language): INSERT, UPDATE, DELETE, CALL, EXPLAIN CALL, LOCK
  3. TCL (Transaction Control Language): COMMIT, SAVEPOINT, ROLLBACK, SET Transaction, SET Constraint
  4. DQL (Data Query Language): SELECT
  5. DCL (Data Control Language): GRANT, REVOKE
5
New cards

What are the three levels of the Three-Schema Approach in database design?

  1. External Level: Describes parts of the database relevant to specific user groups.
  2. Conceptual Level: Describes the structure of the entire database including entities, relationships, data types, operations, and constraints.
  3. Physical Level: Describes the physical storage structure, medium, and implementation details.
6
New cards

How are entities, relationships, attributes, and keys visually represented in an Entity Relationship Diagram (ERD)?

Entities are represented as rectangles, relationships as diamonds, attributes as ovals, and keys as underlined attributes within an oval.

7
New cards

What are the core requirements for First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF)?

1NF requires attribute domains to contain only atomic (indivisible) values and single values per tuple. 2NF requires every non-key column to be fully dependent on the primary key. 3NF requires every non-key column to not be transitively dependent on the primary key.

8
New cards

What are the four ACID properties of database transactions?

  1. Atomicity: Treats a transaction as a single indivisible unit (all operations succeed or none do).
  2. Consistency: Ensures the database transitions from one valid state to another adhering to rules and constraints.
  3. Isolation: Guarantees concurrent transactions do not interfere with each other.
  4. Durability: Guarantees committed changes are permanent and survive system failures.
9
New cards

How do OLTP and OLAP systems differ in architecture and use cases?

OLTP (Online Transaction Processing) is used for APIs and transactional systems with highly normalized tables. OLAP (Online Analytical Processing) uses fewer, wider, and typically denormalized tables optimized for analytics.

10
New cards

How does a Star Schema differ from a Snowflake Schema in data warehousing?

A Star Schema places one central fact table surrounded directly by simple dimension tables. A Snowflake Schema normalizes dimension tables further into sub-dimensional tables.

11
New cards

Why should NUMERIC/DECIMAL be used for financial data in PostgreSQL instead of REAL or DOUBLE PRECISION?

NUMERIC and DECIMAL provide exact numeric precision required for monetary calculations, whereas REAL and DOUBLE PRECISION are floating-point types subject to rounding errors.

12
New cards

Which extension and function are used by default in modern PostgreSQL to automatically generate UUID primary keys?

The extension "pgcrypto" using the DEFAULT gen_random_uuid() function.

13
New cards

What are the four regular expression matching operators in PostgreSQL?

~ (case-sensitive match), ~* (case-insensitive match), !~ (case-sensitive non-match), and !~* (case-insensitive non-match).

14
New cards

What is the purpose of the RETURNING clause in PostgreSQL DML statements?

It allows INSERT, UPDATE, or DELETE statements to directly return modified or generated values (such as newly generated IDs or timestamps) without requiring a separate SELECT query.

15
New cards

How is an UPSERT constructed in PostgreSQL?

By appending ON CONFLICT (column_name) DO UPDATE SET column = EXCLUDED.column to an INSERT statement, where EXCLUDED refers to the record that caused the conflict.

16
New cards

What are the main functional differences between PostgreSQL UDFs (User-Defined Functions) and Stored Procedures?

Functions MUST return a value (or void) and CANNOT perform Transaction Control Language (TCL) operations. Stored Procedures have optional return values, are executed with CALL, and CAN perform TCL operations.

17
New cards

What is the difference between an INNER JOIN and a CROSS JOIN?

An INNER JOIN returns combined rows that satisfy an explicit join condition. A CROSS JOIN combines every row from the left table with every row from the right table without a match filter, producing a Cartesian product.

18
New cards

What default index type is created in PostgreSQL and which operations is it suited for?

B-Tree index. It is suitable for =,

19
New cards

What do the PostgreSQL JSONB operators @>, <@, and && evaluate?

@> tests if the left JSONB value contains the right structure; <@ tests if the left JSONB value is contained within the right structure; && tests if there is any overlap between the two structures.

20
New cards

When should a Generalized Inverted Index (GIN) be used instead of a B-Tree index in PostgreSQL?

GIN indexes should be used when querying composite or multi-valued data types that B-Trees cannot handle effectively, such as JSONB, array data types (e.g., TEXT[]), and full-text search.

21
New cards

What is a Partial Index in PostgreSQL?

An index built over a subset of a table specified by a WHERE clause (e.g., CREATE INDEX idx_users_active ON users (created_at) WHERE is_active = True;).

22
New cards

What is the functional and performance difference between UNION and UNION ALL?

UNION combines result sets and eliminates duplicate rows. UNION ALL combines result sets while retaining all duplicates, making UNION ALL faster because it avoids duplicate removal.

23
New cards

What is the fundamental difference between JOINS and UNIONS?

JOINS combine columns from different tables side-by-side to make the result set wider. UNIONS combine rows from queries with matching structures to stack them vertically, making the result set longer.

24
New cards

What two components are required to implement a database trigger in PostgreSQL?

  1. Trigger Function: A function written in PL/pgSQL that returns type TRIGGER containing the execution logic.
  2. Trigger: An object attached to a specific table that defines the triggering event (e.g., BEFORE UPDATE) and calls the trigger function.
25
New cards

How does Multi-Version Concurrency Control (MVCC) work during row updates in PostgreSQL?

When a row is updated, PostgreSQL creates a new version of the row rather than overwriting it. It tracks row visibility for concurrent transactions using hidden system fields (xmin and xmax) and transaction snapshots.

26
New cards

What is the difference between EXPLAIN and EXPLAIN ANALYZE in PostgreSQL?

EXPLAIN displays the execution plan and estimated costs without running the query. EXPLAIN ANALYZE actually executes the query and returns real execution times and actual row counts.