SQL Interview Essentials Quiz Quiz

Test your knowledge with these 15 medium-difficulty SQL interview questions, covering SQL basics, key concepts, commands, and common scenarios designed to help you succeed in SQL-related interviews.

1. Distinction between SQL and SQL DBMS

   Which statement best describes the difference between SQL and a database management system such as MySQL?

   1. SQL is a query language, while MySQL is a database management system that implements SQL.
   2. SQL is hardware, and MySQL is software.
   3. SQL is used only for querying, while MySQL is used only for administration.
   4. SQL is owned by a corporation, whereas MySQL is open-source only.

   Explanation: SQL is the language used to query and manage relational databases, whereas MySQL is a database management system that uses SQL. SQL is not hardware, nor is it a company-owned product (the second and fourth options are incorrect). While SQL is for querying, MySQL also handles data storage and administration, so the third option is inaccurate.

2. Comparing SQL and PL/SQL

   In which way does PL/SQL differ from standard SQL when used in a database?

   1. PL/SQL replaces all DML commands in SQL.
   2. PL/SQL supports procedural programming and logic in addition to SQL queries.
   3. PL/SQL is strictly used for data visualization.
   4. PL/SQL only runs on open-source databases.

   Explanation: PL/SQL adds procedural features like loops and conditional statements on top of regular SQL, allowing for more complex logic. PL/SQL does not replace all DML commands or is not focused solely on data visualization, so the second and third choices are wrong. It is not limited to open-source databases, making the last option incorrect.

3. Understanding DDL Commands

   Which SQL command is classified as Data Definition Language (DDL) and is used to permanently remove a table from a database?

   1. MERGE
   2. DROP
   3. UPDATE
   4. DELETE

   Explanation: The DROP command is a DDL command that removes an object like a table from the database permanently. DELETE is a DML command that removes rows but does not affect the structure. UPDATE modifies data, and MERGE is used for conditional insert or update, so these do not define or remove the table structure.

4. Purpose of the BETWEEN Keyword

   What is the main function of the BETWEEN keyword in an SQL WHERE clause?

   1. To check if a value falls within a specified range, inclusive of boundary values.
   2. To combine two columns into one output.
   3. To join two tables using a condition.
   4. To remove duplicate records from a result set.

   Explanation: BETWEEN checks if a value is between two specified limits, including both endpoints. It doesn't combine columns (second option), perform table joins (third option), or remove duplicates (fourth option).

5. Usage of the IN Keyword

   How is the SQL IN keyword most commonly used when filtering query results?

   1. It inserts a row into the table.
   2. It checks if a value matches any value in a given list or subquery result.
   3. It updates all records in a table.
   4. It returns only distinct results.

   Explanation: The IN keyword is used to determine if a value exists within a specified list or the set returned by a subquery. It does not insert or update records (second and fourth options), nor is it used for deduplication (third option).

6. Understanding Views

   What is an SQL view, and one its primary uses?

   1. A view is a saved query that can simplify complex logic and improve security.
   2. A view is a trigger that enforces constraints.
   3. A view is a type of index that speeds up queries.
   4. A view is a physical copy of data stored permanently in a table.

   Explanation: A view is essentially a stored SELECT statement that can be queried like a table and used for abstraction or security. It is not an index or a trigger (second and third options), nor does it store data permanently like a table (last option).

7. Difference between View and Materialized View

   What is a key difference between a standard SQL view and a materialized view?

   1. A standard view must be indexed, but a materialized view cannot be indexed.
   2. A standard view does not store results, while a materialized view stores the query output physically.
   3. A materialized view always includes triggers.
   4. A materialized view cannot be refreshed.

   Explanation: A normal view stores only the SQL query definition, so it always runs live; a materialized view actually stores query results for faster access. Materialized views can be refreshed; they do not necessarily include triggers, and both types may use indexes depending on the system, so the other options are incorrect.

8. Identifying Primary Keys

   Which statement correctly defines a primary key in SQL?

   1. A primary key allows duplicate values in a column.
   2. A primary key uniquely identifies each row and cannot contain NULL values.
   3. A primary key is automatically created for every table.
   4. A primary key is a column used only for sorting results.

   Explanation: The primary key must be unique for every row and not null, ensuring row identity. It does not allow duplicates or nulls (second option incorrect), is not required for every table (third option), and is not just for sorting (fourth option).

9. Foreign Key Relationships

   How does a foreign key enforce relationships between two tables?

   1. It links a column in one table to the primary key of another, maintaining referential integrity.
   2. It forces both tables to always have the same number of rows.
   3. It acts as a backup of the primary keys from other tables.
   4. It only indexes both tables for faster joins.

   Explanation: A foreign key references a primary key in another table, enforcing data validity and relationships. It doesn't merely index (second option), is not a backup (third option), and doesn't force row counts to match (last option).

10. Understanding Composite Keys

    In a relational database, what best defines a composite key?

    1. A composite key duplicates only one field from another table.
    2. A composite key is a virtual key created by a view.
    3. A composite key is a primary key made up of two or more columns.
    4. A composite key is a foreign key referencing several tables at once.

    Explanation: Composite keys consist of multiple columns to guarantee each row is unique when one column alone is insufficient. They do not refer to multiple tables (second option), nor are they virtual or simply duplicated from another table (third and fourth options).

11. Identifying Surrogate Keys

    What is a surrogate key in a database table?

    1. A surrogate key is a field that automatically updates each time a row is modified.
    2. A surrogate key is a combination of two natural keys.
    3. A surrogate key is an artificially-generated column used solely as the primary key.
    4. A surrogate key is a column containing human-meaningful identification codes.

    Explanation: Surrogate keys are generic unique identifiers, usually numbers, created specifically to serve as primary keys. They do not store business-meaningful codes (second option), are not natural key combinations (third option), and do not always track row changes (fourth option).

12. DML Command Recognition

    Which of the following statements is an example of a Data Manipulation Language (DML) command?

    1. UPDATE employees SET salary = 50000 WHERE id = 10;
    2. ALTER TABLE products ADD COLUMN price INT;
    3. CREATE TABLE users (id INT);
    4. DROP TABLE orders;

    Explanation: UPDATE modifies records and belongs to DML commands. CREATE, ALTER, and DROP are DDL commands used for defining or altering the database structure, so they are not considered DML.

13. Command for Commenting

    Which DDL command allows you to add a description or comment to the database data dictionary?

    1. RENAME
    2. CHECK
    3. COMMENT
    4. REMARK

    Explanation: The COMMENT statement adds metadata to the data dictionary, typically for documentation. CHECK is a constraint, REMARK is not a valid SQL command, and RENAME is for changing object names.

14. MERGE Function in DML

    What is the main function of the MERGE statement in SQL?

    1. It combines two tables into one permanently.
    2. It only updates data in a single table.
    3. It performs an UPSERT operation, inserting or updating data as needed.
    4. It serves as a synonym for the JOIN operation.

    Explanation: MERGE is used to insert rows or update them if they already exist, handling both in one command. It does not combine tables into one object (second option), is not only for updates (third option), and is unrelated to JOIN operations (fourth option).

15. Result of TRUNCATE Command

    If you issue a TRUNCATE TABLE employees command, what happens to the data?

    1. Only the table's indexes are removed.
    2. The table is completely dropped from the database.
    3. All data in the table is deleted quickly, but the table structure remains.
    4. Only the top row is deleted from the table.

    Explanation: TRUNCATE removes all rows but keeps the table and its structure in the database. It is different from DROP (which removes the entire table), doesn't just delete one row, and does not target indexes specifically.

16. Purpose of EXPLAIN PLAN

    What does the EXPLAIN PLAN SQL command show you?

    1. It displays the execution strategy the database will use for a query.
    2. It returns all unique values in a column.
    3. It grants privileges to users.
    4. It inserts a stored procedure.

    Explanation: EXPLAIN PLAN reveals how the database intends to execute a query, helping with optimization. It does not return unique values (second option), grant privileges (third option), or interact with procedures (fourth option).