Schema Design & DDL

Question 1

What does the CREATE statement do in SQL?

Answer 1

CREATE is used to define new database objects like tables, indexes, or views.

Question 2

What does the ALTER statement do in SQL?

Answer 2

ALTER modifies the structure of an existing database object, such as adding a column to a table.

Question 3

What does the DROP statement do in SQL?

Answer 3

DROP permanently deletes database objects like tables or indexes.

Question 4

What are common data types in PostgreSQL?

Answer 4

INTEGER, SERIAL, TEXT, VARCHAR, BOOLEAN, TIMESTAMP, JSONB, UUID.

Question 5

What are common data types in Oracle?

Answer 5

NUMBER, VARCHAR2, CLOB, BLOB, DATE, TIMESTAMP, RAW.

Question 6

What is a key difference between PostgreSQL and Oracle data types?

Answer 6

PostgreSQL supports JSON/JSONB natively; Oracle relies on CLOB for similar use.

Question 7

What is the syntax to create an index in SQL?

Answer 7

CREATE INDEX index_name ON table_name (column1, column2);

Question 8

What is a unique constraint?

Answer 8

A constraint that ensures all values in a column or group of columns are unique.

Question 9

What is a composite key?

Answer 9

A primary key made up of more than one column to uniquely identify a record.

Question 10

What is a SQL view?

Answer 10

A virtual table representing the result of a stored query, not storing data physically.

Question 11

What is a materialized view?

Answer 11

A view that stores the result set physically and can be refreshed periodically.

Question 12

What is the difference between a view and a materialized view?

Answer 12

Views are virtual and always reflect current data; materialized views store data and can improve performance but may become stale.

Question 13

What is a sequence in SQL?

Answer 13

A database object that generates a sequence of unique numeric values, often for primary keys.

Question 14

What is an identity column in SQL?

Answer 14

A column that auto-increments its value using a built-in mechanism, replacing explicit sequences in some RDBMS.

Question 15

What is referential integrity in databases?

Answer 15

Ensures relationships between tables remain consistent using foreign keys.

Question 16

What is the advantage of using views?

Answer 16

Views provide abstraction, security, and simplified query logic.

Question 17

What is a disadvantage of using views?

Answer 17

Views can become complex, hard to debug, and may impact performance if nested or improperly indexed.

Question 18

What are best practices for schema design?

Answer 18

Use meaningful names, normalize where needed, enforce constraints, and plan indexing based on query patterns.

Question 19

What is a use case for materialized views?

Answer 19

Caching aggregated reports or slow-running joins for faster access.

Question 20

How do schema changes impact system design?

Answer 20

They affect API behavior, data migration, and require version control in CI/CD.

Question 21

What is the impact of constraints on performance?

Answer 21

Constraints enforce data quality but add overhead during writes and updates.

Question 22

What is an architectural implication of indexes?

Answer 22

Indexes improve read performance but can slow down inserts and updates, affecting write-heavy systems.

Question 23

How do you monitor index usage?

Answer 23

Use database query plans, statistics views, and APM tools to identify index efficiency.

Question 24

What is a real-world tradeoff in schema design?

Answer 24

Normalized schemas reduce redundancy but may require complex joins; denormalization simplifies reads but can cause anomalies.

Question 25

What is a common interview question on DDL?

Answer 25

What is the difference between DELETE, DROP, and TRUNCATE?

Question 26

What’s a potential gotcha with ALTER TABLE?

Answer 26

Altering large tables can lock the table and impact availability during production.

Question 27

What’s a potential gotcha with DROP TABLE?

Answer 27

DROP removes all data and structure permanently; it's not reversible without backups.

Question 28

What’s a best practice when creating indexes?

Answer 28

Index only frequently queried columns and avoid redundant indexes.

Question 29

What’s a common use case for composite keys?

Answer 29

Linking association tables in many-to-many relationships.

Question 30

What is a tradeoff of using materialized views?

Answer 30

They improve read speed but require scheduled refreshes and can go stale.

Question 31

What is a challenge when working with sequences in distributed systems?

Answer 31

Ensuring global uniqueness and avoiding gaps or collisions when scaling horizontally.

Question 32

What happens if referential integrity is not enforced?

Answer 32

Orphaned records and inconsistent data may occur, leading to data quality issues.

Question 33

What’s the difference between SERIAL and IDENTITY in PostgreSQL?

Answer 33

SERIAL is legacy using sequences; IDENTITY is ANSI-standard and preferred in newer versions.

Question 34

What is a best practice when managing schema versions?

Answer 34

Use migrations tracked in version control and avoid direct DDL in production environments.

Question 35

How does schema design affect fault tolerance?

Answer 35

Proper use of constraints and types reduces data corruption and supports recovery.

Question 36

What tools help debug schema issues?

Answer 36

ER diagrams, pgAdmin, SQL Developer, dbSchema, and logging slow queries involving structure.

Question 37

What are examples of schema-related performance issues?

Answer 37

Unindexed foreign keys, over-indexing, missing constraints causing full scans.

Question 38

What is a composite unique constraint?

Answer 38

Ensures a combination of values in multiple columns is unique together.