AWS & SQL/PostgreSQL Interview Flashcards

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

1/104

flashcard set

Earn XP

Description and Tags

Vocabulary flashcard deck covering key topics in AWS IAM, Security Groups, RDS, SQL syntax, PostgreSQL features, database theory, transactions, and Docker fundamentals.

105 Terms

1
New cards

IAM

Identity and Access Management — a global AWS service used to manage users, groups, and permissions. It is NOT region-specific.

2
New cards

IAM Root Account

The account created automatically when you set up AWS. Best practice is to NOT use or share it, but to create admin users instead.

3
New cards

IAM User

A person within your AWS organization that has a set of credentials. Users can belong to zero or more groups.

4
New cards

IAM Group

A collection of IAM Users used to assign permissions to multiple users at once. Groups can only contain users — NOT other groups.

5
New cards

IAM Policy

A JSON document that defines permissions (allow/deny) for users, groups, or roles based on the least privilege principle.

6
New cards

Least Privilege Principle

A security concept: only grant users the minimum permissions they need to do their job — nothing more.

7
New cards

IAM Role

Permissions assigned to AWS services (not people) so those services can perform actions on your behalf (e.g., EC2 Instance Role, Lambda Function Role, CloudFormation Role).

8
New cards

Three Ways to Access AWS

  1. AWS Management Console (protected by MFA + password). 2. AWS CLI (protected by access keys). 3. AWS SDK (programmatic access from code protected by access keys).
9
New cards

AWS Access Keys

Credentials used to authenticate with the AWS CLI or SDK, where Access Key ID acts like a username and Secret Access Key acts like a password.

10
New cards

AWS CLI

An open-source tool to interact with AWS services via shell commands, providing direct access to public AWS APIs and allowing scripting for resource management.

11
New cards

AWS SDK

Software Development Kit consisting of language-specific libraries (Java, Python, JS, .NET, etc.) that let you access and manage AWS services programmatically from code.

12
New cards

IAM Security Audit Tools

  1. IAM Credentials Report (account-level report of all users and credential status). 2. IAM Last Accessed / Access Advisor (shows when a user last accessed which services).
13
New cards

IAM Best Practices

  1. Don't use root after setup. 2. One physical user = one AWS user. 3. Assign users to groups and permissions to groups. 4. Strong password policies. 5. Enforce MFA. 6. Roles for services. 7. Access keys for CLI/SDK. 8. Never share credentials. 9. Audit with Credentials Report & Last Accessed.
14
New cards

IAM Policy Inheritance

Policies assigned at the group level are inherited by all group members. Group-level assignment is best practice over inline user policies.

15
New cards

AWS Security Groups

Virtual firewalls that control inbound and outbound traffic for EC2 instances and RDS databases. They contain ONLY ALLOW rules (no explicit deny).

16
New cards

Security Group Regulations

Regulates: 1. Access to specific ports. 2. Authorized IP ranges (IPv4/IPv6). 3. Inbound traffic (external → instance). 4. Outbound traffic (instance → external).

17
New cards

Default Security Group Rules

All inbound traffic is BLOCKED by default. All outbound traffic is ALLOWED by default.

18
New cards

Timeout vs Connection Refused (Security Group)

Timeout indicates traffic is being blocked before reaching the instance (Security Group issue). Connection Refused indicates application error or non-running app (traffic reached instance but was rejected).

19
New cards

Security Group Multi-Instance Scope

One security group can be attached to multiple instances, and one instance can have multiple security groups. They are scoped to a specific region/VPC combination.

20
New cards

Security Group Location relative to EC2

Lives OUTSIDE the EC2 instance. If traffic is blocked by a security group, the EC2 instance never sees it.

21
New cards

Port 22

SSH (Secure Shell) — log into a Linux instance. Also used by SFTP (Secure File Transfer over SSH).

22
New cards

Port 21

FTP (File Transfer Protocol) — upload files to a file share.

23
New cards

Port 80

HTTP — access unsecured websites.

24
New cards

Port 443

HTTPS — access secured/encrypted websites.

25
New cards

Port 3389

RDP (Remote Desktop Protocol) — log into a Windows instance.

26
New cards

Port 5432

PostgreSQL default port, used when connecting to an RDS PostgreSQL instance.

27
New cards

AWS RDS

Relational Database Service — a managed relational database service (MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, Aurora) handling patching, backups, and failover.

28
New cards

Connecting to RDS

Requires server endpoint, master username, master password, port number, and the RDS security group allowing inbound TCP traffic on the database port.

29
New cards

Relational vs NoSQL Database

Relational: structured data in tables (rows & columns) using SQL (e.g., MySQL, PostgreSQL, Oracle). NoSQL: unstructured or semi-structured data with flexible schema (e.g., MongoDB, Cassandra, Redis).

30
New cards

Database Schema

A namespace inside a database that organizes and groups related tables, views, functions, and other objects (acts like a folder).

31
New cards

PostgreSQL Hierarchy

Database → Schema → Table → Columns → Rows. Schemas also contain Constraints, Indexes, Views, Functions, and Stored Procedures.

32
New cards

SET search_path

An ordered list of schemas PostgreSQL searches when an object is referenced without qualification (e.g., SET search_path TO billing, public;).

33
New cards

SQL Sublanguages

DDL (Data Definition: CREATE, ALTER, DROP, TRUNCATE), DML (Data Manipulation: INSERT, UPDATE, DELETE), DQL (Data Query: SELECT), DCL (Data Control: GRANT, REVOKE), TCL (Transaction Control: BEGIN, COMMIT, ROLLBACK, SAVEPOINT).

34
New cards

NOT NULL Constraint

Prevents a column from storing a NULL value; every row must contain a value for that column.

35
New cards

UNIQUE Constraint

Ensures all values in a column (or combination of columns) are distinct across all rows.

36
New cards

PRIMARY KEY Constraint

A combination of NOT NULL + UNIQUE that uniquely identifies every row in a table. A table can only have one primary key.

37
New cards

FOREIGN KEY Constraint

Links a column in one table to the PRIMARY KEY of another table, ensuring referential integrity.

38
New cards

ON DELETE CASCADE

A foreign key behavior where deleting a parent row automatically deletes all related child rows.

39
New cards

ON DELETE SET NULL

A foreign key behavior where deleting a parent row sets foreign key columns in child rows to NULL instead of deleting them.

40
New cards

CHECK Constraint

Ensures column values satisfy a specific condition, such as CHECK (age >= 18) or CHECK (UnitPrice > 0).

41
New cards

DEFAULT Constraint

Assigns a default value to a column when no value is specified during insertion (e.g., created_at TIMESTAMP DEFAULT NOW()).

42
New cards

CREATE INDEX

Creates an index on a table column, significantly speeding up data retrieval queries at the cost of slower writes and added storage.

43
New cards

First Normal Form (1NF)

Requires each column to contain only atomic (indivisible) values with no lists or sets in a single cell.

44
New cards

Second Normal Form (2NF)

Must be in 1NF AND every non-key column must be fully dependent on the entire primary key (no partial dependencies on a composite key).

45
New cards

Third Normal Form (3NF)

Must be in 2NF AND no non-key column should be transitively dependent on the primary key (non-key columns depend ONLY on the primary key).

46
New cards

Entity Relationship Diagram (ERD)

A visual blueprint of a database displaying entities (rectangles), attributes (ovals), relationships (diamonds), and keys (underlined attributes).

47
New cards

Cardinality Ratios (ERD)

Defines the maximum number of relationship instances an entity can participate in: 1:1, 1:N, N:1, or M:N.

48
New cards

Total vs Partial Participation (ERD)

Total Participation: every entity MUST participate in a relationship (double line). Partial Participation: some entities may not participate (single line).

49
New cards

Weak Entity Type

An entity lacking its own key attribute that depends on a strong owner entity for identification.

50
New cards

ACID Properties

Atomicity, Consistency, Isolation, Durability — guarantee reliable database transactions.

51
New cards

Atomicity (ACID)

A transaction is an indivisible unit: either ALL operations succeed, or NONE do (no partial updates).

52
New cards

Consistency (ACID)

A transaction moves the database from one valid state to another while adhering to all rules, constraints, and triggers.

53
New cards

Isolation (ACID)

Concurrent transactions do not interfere with each other, executing as if they ran sequentially.

54
New cards

Durability (ACID)

Once committed, transaction changes are permanent even during system failures or power outages.

55
New cards

PostgreSQL Numeric Types

SMALLINT (2 bytes), INTEGER/INT (4 bytes), BIGINT (8 bytes), NUMERIC(p,s)/DECIMAL (exact, used for money), REAL (4 bytes, non-exact), DOUBLE PRECISION (8 bytes, non-exact).

56
New cards

PostgreSQL String Types

TEXT (variable length, no length limit, preferred), VARCHAR(n) (variable length up to n characters), CHAR(n) (fixed length, space-padded, legacy).

57
New cards

PostgreSQL Date/Time Types

DATE (calendar date), TIME (time of day), TIMESTAMP (date + time), TIMESTAMPTZ (date + time + timezone in UTC, preferred).

58
New cards

UUID (PostgreSQL)

Universally Unique Identifier — a 128-bit value optimal for distributed systems, generated via gen_random_uuid() or uuid_generate_v4().

59
New cards

SERIAL vs UUID

SERIAL/BIGSERIAL: 4/8 bytes, auto-incrementing, fast indexing, NOT for distributed systems. UUID: 16 bytes, optional auto-gen, slower index, YES for distributed systems.

60
New cards

NULL in SQL

Represents UNKNOWN (not zero, empty string, or false). Checked using IS NULL or IS NOT NULL ('WHERE col = NULL' is incorrect).

61
New cards

JSONB vs JSON (PostgreSQL)

JSON stores raw text. JSONB stores binary JSON in a decomposed format, which is much faster to query and preferred.

62
New cards

DROP vs TRUNCATE

DROP removes structure + data. TRUNCATE removes all rows quickly while retaining structure. Neither supports a WHERE clause.

63
New cards

SELECT DISTINCT

Removes duplicate rows from a result set and can apply across single or multiple columns.

64
New cards

UNION vs UNION ALL

UNION combines SELECT results and removes duplicates. UNION ALL combines results while retaining duplicates (faster). Both require matching column counts and compatible types.

65
New cards

LIMIT and OFFSET

LIMIT caps the maximum returned rows; OFFSET skips a specified number of rows before returning results (used for pagination).

66
New cards

LIKE Operator

Pattern matching operator (% = 0+ chars, _ = exactly 1 char). Case-sensitive in PostgreSQL (use ILIKE for case-insensitive).

67
New cards

Aggregate Functions

Functions operating across rows returning a single value: COUNT(*), SUM(), AVG(), MIN(), MAX(). Used with GROUP BY and filtered with HAVING.

68
New cards

WHERE vs HAVING

WHERE filters rows BEFORE grouping/aggregation occurs. HAVING filters groups AFTER aggregation occurs.

69
New cards

Scalar Functions

Functions operating on each individual row returning one output per row (String: UPPER, LOWER, LENGTH, TRIM; Date: NOW, CURRENT_DATE, AGE, EXTRACT).

70
New cards

EXTRACT() Function

Retrieves specific fields (year, month, day, hour) from a timestamp (e.g., EXTRACT(YEAR FROM invoice_date)).

71
New cards

INTERVAL Type

Represents a duration of time supporting arithmetic (e.g., WHERE last_login < NOW() - INTERVAL '2 years').

72
New cards

INNER JOIN

Returns only rows that match in BOTH tables, excluding non-matching rows.

73
New cards

LEFT JOIN

Returns ALL rows from the left table plus matching rows from the right table (unmatched right columns return NULL).

74
New cards

RIGHT JOIN

Returns ALL rows from the right table plus matching rows from the left table.

75
New cards

FULL OUTER JOIN

Returns all rows from BOTH tables, filling in NULLs where no match exists on either side.

76
New cards

CROSS JOIN

Returns the Cartesian product of two tables (result size = rowsleft×rowsright\text{rows}_{\text{left}} \times \text{rows}_{\text{right}}) without join conditions.

77
New cards

JOINs vs UNIONs

JOINs combine COLUMNS (make tables wider); UNIONs combine ROWS (stack same-shaped results taller).

78
New cards

Subquery

A query nested inside another query to filter results based on the output of the inner query.

79
New cards

Common Table Expression (CTE)

A named temporary result set defined using WITH…AS that improves readability and reusability over subqueries.

80
New cards

User-Defined Function (UDF)

A reusable SQL/PL/pgSQL block that MUST return a value (even VOID), called via SELECT, and CANNOT perform TCL operations.

81
New cards

Stored Procedure (PostgreSQL)

A reusable block of SQL/PL/pgSQL logic called via CALL, where return values are OPTIONAL and TCL commands (BEGIN, COMMIT, ROLLBACK) ARE allowed.

82
New cards

Functions vs Stored Procedures

Functions: must return a value, no TCL allowed, called with SELECT. Stored Procedures: optional return value, TCL allowed, called with CALL.

83
New cards

PL/pgSQL

Procedural Language for PostgreSQL used to write in-database logic including IF/ELSE control flow, variables, loops, and error handling.

84
New cards

Database Index

A structure that speeds up query retrieval at the expense of extra storage and slower write operations.

85
New cards

B-Tree Index

The DEFAULT index type in PostgreSQL; a balanced tree efficient for =,

86
New cards

GIN Index

Generalized Inverted Index — optimal for JSONB data, arrays, and full-text search using operators like @> (contains).

87
New cards

GiST Index

Generalized Search Tree — optimal for ranges, overlapping data, and geospatial coordinates (e.g., PostGIS).

88
New cards

Partial Index

An index covering only a SUBSET of rows specified by a WHERE clause, saving space and improving speed.

89
New cards

Database View

A saved SELECT query acting as a virtual table that re-runs its underlying query each time it is accessed.

90
New cards

Database Trigger

An object that automatically fires a trigger function upon specified events (INSERT, UPDATE, DELETE, TRUNCATE) on a table.

91
New cards

BEFORE vs AFTER Triggers

BEFORE triggers execute pre-write (can modify NEW record, used for validation); AFTER triggers execute post-write (cannot modify record, used for auditing).

92
New cards

Trigger Variables (NEW vs OLD)

INSERT provides NEW only; UPDATE provides both OLD and NEW; DELETE provides OLD only.

93
New cards

Transaction Control Language (TCL)

Commands managing transaction boundaries: BEGIN (start), COMMIT (persist changes), ROLLBACK (undo changes), SAVEPOINT (checkpoint).

94
New cards

PostgreSQL Autocommit Mode

The default transaction mode where every individual DML statement is automatically wrapped in an implicit BEGIN/COMMIT.

95
New cards

SAVEPOINT

A named checkpoint in a transaction enabling selective rollback (ROLLBACK TO SAVEPOINT) without discarding the entire transaction.

96
New cards

MVCC

Multi-Version Concurrency Control — PostgreSQL's mechanism allowing concurrent reads and writes without blocking by maintaining row versions.

97
New cards

How MVCC Works

Updates create new row versions rather than overwriting existing ones. Transactions receive snapshots displaying only committed data prior to their start.

98
New cards

EXPLAIN Command

Displays PostgreSQL's estimated query execution plan (estimated cost, scan method, estimated row count) without running the query.

99
New cards

EXPLAIN ANALYZE Command

Actually EXECUTES a query and prints both the execution plan and real execution timing stats.

100
New cards

PostgreSQL Query Planner

Internal database engine component that calculates the optimal execution strategy (join type, index usage, filter order) for a query.