SQL Essentials for Data Engineering Interviews Quiz

Sharpen your grasp of key SQL concepts, command categories, and interview-ready syntax with this 15-question quiz designed for aspiring data engineers and SQL beginners. Tackle basic SQL classification, command usage, and simple query logic to boost your interview confidence.

1. SQL Command Categories

   Which SQL category is primarily used to define the structure of database tables?

   1. DDL (Data Definition Language)
   2. DQL (Data Query Language)
   3. TCL (Transaction Control Language)
   4. DML (Data Manipulation Language)

   Explanation: DDL (Data Definition Language) commands such as CREATE, ALTER, and DROP are used to define and modify the structure of database tables. DML is used for manipulating data, DQL for querying, and TCL for managing transactions, but only DDL changes the blueprint of the database.

2. Identifying DQL Commands

   Which SQL command is categorized under DQL (Data Query Language) and is used to retrieve data from a table?

   1. SELECT
   2. INSERT
   3. ALTER
   4. COMMIT

   Explanation: SELECT is the only major DQL command and is used to fetch data from tables. INSERT is a DML command for adding data, ALTER is a DDL command for modifying table structure, and COMMIT is a TCL command for transaction control.

3. Data Manipulation

   If you want to modify data already stored in a table, which category of SQL commands should you use?

   1. DML (Data Manipulation Language)
   2. DDL (Data Definition Language)
   3. TCL (Transaction Control Language)
   4. HTML

   Explanation: DML (Data Manipulation Language) commands like UPDATE and DELETE allow you to modify or remove data within tables. DDL deals with table structure, TCL with transactions, and HTML is not related to SQL at all.

4. Transaction Management

   Which SQL command ensures that all changes made during a transaction are saved permanently?

   1. COMMIT
   2. ROLLBACK
   3. TRUNCATE
   4. SELECT

   Explanation: COMMIT finalizes all changes in a transaction, making them permanent. ROLLBACK undoes changes, TRUNCATE removes all rows from a table but is not a transaction command, and SELECT merely queries data.

5. Simple Table Creation

   How would you create a table called Students with columns for roll number, first name, and last name, where last name cannot be empty?

   1. CREATE TABLE Students (rollno INT PRIMARY KEY, fname VARCHAR(255), lname VARCHAR(255) NOT NULL);
   2. INSERT INTO Students (rollno, fname, lname);
   3. SELECT * FROM Students WHERE lname IS NOT NULL;
   4. DELETE FROM Students WHERE lname IS NULL;

   Explanation: The CREATE TABLE command defines a new table with specified columns and constraints. INSERT adds data, SELECT is for querying, and DELETE removes rows. Only CREATE TABLE matches the described requirement directly.

6. Primary Keys

   Why is a primary key used in SQL table definitions?

   1. To uniquely identify each row in a table
   2. To allow duplicate values in a column
   3. To store all table data
   4. To represent text data only

   Explanation: A primary key ensures each row in a table is unique. It does not allow duplicates, isn't for storing all data, and isn't limited to only text data. Its core function is unique row identification.

7. Altering Tables

   Which command would you use to add a new column to an existing table?

   1. ALTER TABLE
   2. SELECT INTO
   3. COMMIT TABLE
   4. ROLLBACK TABLE

   Explanation: ALTER TABLE allows you to add, remove, or modify columns in an existing table. SELECT INTO copies data, COMMIT applies transaction changes, and ROLLBACK undoes transactions; none modifies table structure.

8. Deleting Data

   What SQL command will remove all rows from a table but keep its structure intact for future use?

   1. TRUNCATE
   2. DROP
   3. ALTER
   4. ROLLUP

   Explanation: TRUNCATE deletes all records from a table but leaves the table definition for reuse. DROP removes the entire table structure, ALTER changes metadata, and ROLLUP is used for grouping, not deleting.

9. Inserting Data

   If you want to add a new student named 'Jane Doe' with roll number 5 to the Students table, which SQL command would be correct?

   1. INSERT INTO Students VALUES (5, 'Jane', 'Doe');
   2. SELECT * FROM Students WHERE name = 'Jane Doe';
   3. COMMIT Students (5, Jane, Doe);
   4. ALTER Students ADD (5, 'Jane', 'Doe');

   Explanation: INSERT INTO is used for adding new rows. SELECT only fetches records, COMMIT finalizes transactions, and ALTER is structural and not for adding data rows. Only the INSERT statement fits the requirement.

10. Querying a Specific Column

    Which SQL query would select only the first name column from the Students table?

    1. SELECT fname FROM Students;
    2. SELECT Students FROM fname;
    3. SELECT * WHERE fname IN Students;
    4. SELECT fname WHERE Students;

    Explanation: SELECT fname FROM Students; is the correct syntax for selecting just the fname column. The other options are either syntactically incorrect or reverse the column and table placement.

11. NOT NULL Constraint

    What does the NOT NULL constraint ensure when defining a column in a table?

    1. The column must always have a value and cannot be left empty
    2. The column can contain only numbers
    3. The column cannot be used in SELECT statements
    4. The column will always be unique

    Explanation: NOT NULL ensures that every row has a value for the column, prohibiting empty fields. It does not enforce uniqueness or numeric-only entries, nor does it block SELECT statements from using the column.

12. Rolling Back Transactions

    If you mistakenly delete all records from a table but want to undo this action before finalizing, which command would you use?

    1. ROLLBACK
    2. DROP
    3. ALTER
    4. INSERT

    Explanation: ROLLBACK reverts all changes made during the current transaction, undoing deletes and updates before a COMMIT occurs. DROP removes table structures, ALTER makes structural changes, and INSERT adds data but cannot undo deletions.

13. Unique Constraints

    What does adding a UNIQUE constraint to a column enforce?

    1. All values in the column must be different
    2. The column can store only text data
    3. The column must be a primary key
    4. The column allows NULL values only

    Explanation: A UNIQUE constraint guarantees all values in a column are distinct. It is not limited to text data, does not require the column to be a primary key, and does not specify NULL requirements.

14. Updating Data

    Which command allows you to change an existing student's last name to 'Smith' in the Students table?

    1. UPDATE Students SET lname = 'Smith' WHERE ...;
    2. SELECT lname FROM Students SET = 'Smith';
    3. ALTER lname TO 'Smith' IN Students;
    4. INSERT INTO Students (lname) VALUES ('Smith');

    Explanation: UPDATE is used for modifying existing records. The correct syntax requires specifying which row(s) to update with WHERE. The other options misuse SQL keywords or describe incorrect operations for this scenario.

15. Deleting Specific Rows

    How would you remove only students named 'John' from the Students table?

    1. DELETE FROM Students WHERE fname = 'John';
    2. DROP Students WHERE fname = 'John';
    3. REMOVE Students fname = 'John';
    4. ALTER Students DELETE fname = 'John';

    Explanation: DELETE FROM Students WHERE fname = 'John'; removes specific rows with fname 'John'. DROP deletes entire tables, REMOVE is not a valid command in SQL, and ALTER changes table structures, not data.

16. Basic SELECT Usage

    What will the command SELECT * FROM Students; do?

    1. It displays all columns and rows in the Students table
    2. It deletes all rows in the Students table
    3. It creates a new table called Students
    4. It adds a new column to the Students table

    Explanation: SELECT * FROM Students; shows all data in the table with all columns. It does not delete, create tables, or alter the table's columns. The asterisk is a wildcard to select every column for every row.