DBMS Basics Interview Questions

A DBMS (database management system) is software that stores data and lets many users define, query, update and protect it through one interface, like MySQL or MongoDB. Over plain files it adds a query language, enforced constraints, safe concurrent access, crash recovery and permissions. The database is the data; the DBMS is the software that manages it (what a database is).

Files leave every rule to the application: copies of the same data drift out of sync, each new report needs new code, two writers at once can corrupt a file, and a crash mid-write leaves half an update. A DBMS fixes these with normalized tables, SQL, constraints, locking and transaction logs. Files still suit logs, media and bulk data written once.

An RDBMS is a DBMS built on the relational model: tables linked by primary and foreign keys, queried with SQL, with constraints and normalization. DBMS is the wider term that also covers hierarchical (IBM IMS), document (MongoDB) and key-value (Redis) systems. Every RDBMS is a DBMS. Avoid the myth that a DBMS serves one user and an RDBMS many.

The ANSI/SPARC architecture has three levels: internal (how data sits on disk: files, pages, indexes), conceptual (the whole database as tables, keys and constraints) and external (what each user or app sees, usually through views). The mappings between levels give data independence: adding an index changes no query.

Data independence is changing the schema at one level without rewriting the level above. Physical: change storage, such as adding an index, without touching the logical schema or any query. Logical: change tables, such as splitting one, without breaking applications, usually by keeping a view with the old shape. Physical independence is easier to achieve than logical.

A schema is the design of the database (tables, columns, types, constraints) and rarely changes; an instance is the data in it at one moment and changes with every write, like a class versus its current objects. "Schema" also means a namespace inside a database (PostgreSQL's public), so say which meaning you are answering.

SQL commands are grouped by what they act on:

• DDL (structure): CREATE, ALTER, DROP, TRUNCATE
• DML (data): INSERT, UPDATE, DELETE, plus SELECT, which some books call DQL
• DCL (permissions): GRANT, REVOKE
• TCL (transactions): COMMIT, ROLLBACK, SAVEPOINT

CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);      -- DDL
INSERT INTO courses (title) VALUES ('DBMS'), ('Operating Systems');      -- DML
UPDATE courses SET title = 'Database Systems' WHERE id = 1;              -- DML
SELECT id, title FROM courses;                                           -- DQL

The SQL cheat sheet lists the syntax.

DELETE removes chosen rows (DML: WHERE, triggers, logs each row). TRUNCATE removes all rows by deallocating pages (DDL: no WHERE, no DELETE triggers). DROP removes the table itself with its indexes and constraints. TRUNCATE and DROP roll back in PostgreSQL and SQL Server but commit implicitly in MySQL and Oracle. SQLite has no TRUNCATE. A DELETE inside a transaction can be undone; here all 3 rows come back:

CREATE TABLE logs (id INTEGER PRIMARY KEY, msg TEXT);
BEGIN;
INSERT INTO logs (msg) VALUES ('boot'), ('login'), ('logout');
COMMIT;

BEGIN;
DELETE FROM logs;
ROLLBACK;

SELECT count(*) AS rows_left FROM logs;

It prints 2. A view stores the query, not the rows, so each SELECT from it reads current data, and after Ravi's raise both earn over 45000. A materialized view stores the result and stays stale until refreshed (PostgreSQL, Oracle); SQLite and MySQL have none. See views.

It prints NULL | 1 | NULL. Any comparison with NULL is unknown, even NULL = NULL; only IS NULL and IS NOT NULL return 1 or 0. Since WHERE keeps a row only when the condition is TRUE, WHERE phone = NULL returns nothing and WHERE city <> 'Pune' silently drops NULL cities. Use IS NULL, and IS DISTINCT FROM for a NULL-safe <>, which SQLite also spells IS NOT.

They are the relational model's names for the parts of a table: a relation is a table, a tuple a row, an attribute a column, a domain the set of values a column allows, the degree the number of columns and the cardinality the number of rows. A relation is a set, so in theory it has no duplicate rows and no order; SQL tables allow both, hence DISTINCT and ORDER BY.

A join combines rows of two tables on a condition, usually a foreign key matching a primary key. INNER keeps only matches; LEFT and RIGHT keep every row of one side, with NULLs where nothing matches; FULL keeps both sides; CROSS pairs every row with every row; a self join joins a table to itself (employee and manager).

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (name TEXT, dept_id INTEGER);
INSERT INTO departments VALUES (1, 'Sales'), (2, 'HR');
INSERT INTO employees VALUES ('Asha', 1), ('Ravi', 1), ('Meera', NULL);

SELECT e.name, d.name AS dept
FROM employees e LEFT JOIN departments d ON d.id = e.dept_id
ORDER BY e.name;

Meera has no department, so an inner join would drop her. MySQL has no FULL JOIN; use a UNION of a left and a right join. See left joins.

It prints 12. FROM a, b with no condition is the Cartesian product, every row of a with every row of b: 3 × 4. CROSS JOIN does the same. An inner join is this product filtered, though engines never build it in full. Forget the join condition on two 100,000-row tables and you get 10 billion rows.

The six basic operations and their SQL: selection σ (WHERE), projection π (SELECT DISTINCT columns), union ∪ (UNION), set difference − (EXCEPT, MINUS in Oracle), Cartesian product × (CROSS JOIN) and rename ρ (AS). Join, intersection and division are built from them. SQL has no division operator ("students who took every course"), so you write a double NOT EXISTS.