Database (DB) Interview questions and answers: SQL, Normalization, and Architecture

Database (DB) Interview Challenge: SQL, Normalization, and Architecture

Test your knowledge of **Database Fundamentals, Data Modeling, SQL Optimization, and Advanced Concepts**. **Pass with 80% to proceed to the next difficulty level.**

Level 1: Core SQL & Basics: 0/10 (0%)

Level 2: Normalization & Transactions: 0/10 (0%)

Level 3: Advanced Optimization & Features: 0/10 (0%)

Level 1: Core SQL & Basics

1.1 Which subset of SQL is responsible for defining the database schema, such as `CREATE`, `ALTER`, and `DROP`?

A. DML (Data Manipulation Language) B. DDL (Data Definition Language) C. DCL (Data Control Language)

**Correct Answer: B. DDL (Data Definition Language)**. DDL commands define the database structure or schema.

1.2 What is the purpose of a Primary Key constraint?

A. To create a link between two tables. B. To uniquely identify each record in a table and ensure the value is NOT NULL. C. To speed up data retrieval operations.

**Correct Answer: B. To uniquely identify each record...**. A Primary Key enforces uniqueness and non-nullability, which are fundamental for data integrity.

1.3 Which SQL operator is used for pattern matching? (e.g., finding all names starting with 'S')

A. MATCHES B. LIKE C. CONTAINS

**Correct Answer: B. LIKE**. The `LIKE` operator is used in the `WHERE` clause to search for a specified pattern in a column, typically with wildcards (`%` or `_`).

1.4 The value `NULL` in a database field signifies:

A. The boolean value False. B. The number zero (0). C. Missing or unknown data.

**Correct Answer: C. Missing or unknown data**. `NULL` is a marker, not a value. It indicates the absence of data. Comparisons involving `NULL` require `IS NULL` or `IS NOT NULL`.

1.5 Which command is used to add new rows of data into an existing table?

A. ADD INTO B. INSERT INTO C. UPDATE

**Correct Answer: B. INSERT INTO**. This DML command adds one or more records to a table. `UPDATE` modifies existing records.

1.6 A Foreign Key is primarily used to achieve what in a relational database?

A. Create faster lookup tables. B. Enforce referential integrity and maintain relationships between tables. C. Prevent duplicate records in the primary table.

**Correct Answer: B. Enforce referential integrity...**. The Foreign Key links a child table back to the Primary Key of a parent table, ensuring consistency.

1.7 Which aggregate function determines the number of rows in a table that meet a specific criteria?

A. SUM() B. MAX() C. COUNT()

**Correct Answer: C. COUNT()**. `COUNT(*)` counts all rows, while `COUNT(column)` counts only non-null values in that column.

1.8 Which SQL clause is required to arrange the result set in ascending or descending order?

A. GROUP BY B. ORDER BY C. SORT BY

**Correct Answer: B. ORDER BY**. `ORDER BY` sorts the final result set. The default order is `ASC` (Ascending).

1.9 What is a Database Schema?

A. The graphical user interface (GUI) for the database. B. The physical files on the server where data is stored. C. The overall structure or design of the database (tables, views, indexes, relationships).

**Correct Answer: C. The overall structure...**. The schema defines the logical organization of the entire database.

1.10 Which SQL command is used to permanently remove an entire table and all its data from the database?

A. DELETE TABLE B. DROP TABLE C. TRUNCATE TABLE

**Correct Answer: B. DROP TABLE**. `DROP TABLE` removes the table structure itself. `TRUNCATE TABLE` removes all rows but keeps the table structure.

**End of Core SQL & Basics. Click "Next Level" to continue.**

Level 2: Normalization & Transactions

2.1 What is the goal of Database Normalization?

A. To speed up all data retrieval queries. B. To eliminate redundant data and improve data integrity. C. To encrypt all data stored in the database.

**Correct Answer: B. To eliminate redundant data...**. Normalization reduces data anomalies (insertion, deletion, update) by breaking tables into smaller, related tables.

2.2 In the ACID properties of a database transaction, what does the 'I' stand for?

A. Interoperability B. Isolation C. Integrity

**Correct Answer: B. Isolation**. Isolation ensures that concurrent transactions do not interfere with each other, meaning each transaction appears to execute in sequence.

2.3 A table is in 3rd Normal Form (3NF) if it is in 2NF and:

A. It has no composite primary keys. B. It has no transitive dependencies (non-key attributes dependent on other non-key attributes). C. All attributes are dependent on the full primary key.

**Correct Answer: B. It has no transitive dependencies...**. 1NF removes repeating groups, 2NF removes partial dependencies, and 3NF removes transitive dependencies.

2.4 Which type of SQL Join returns only the rows that have matching values in both tables?

A. FULL JOIN B. INNER JOIN C. LEFT JOIN

**Correct Answer: B. INNER JOIN**. The INNER JOIN keyword selects records that have matching values in both tables, discarding non-matches.

2.5 The SQL clause HAVING is used to filter what kind of data?

A. Individual records before grouping. B. Groups created by the GROUP BY clause. C. Columns in the SELECT statement.

**Correct Answer: B. Groups created by the GROUP BY clause**. `WHERE` filters individual rows; `HAVING` filters aggregate groups, often based on aggregate functions (e.g., `HAVING COUNT(id) > 5`).

2.6 Which command is used to make all changes within an active transaction permanent?

A. SAVEPOINT B. COMMIT C. END TRANSACTION

**Correct Answer: B. COMMIT**. `COMMIT` permanently saves the transactional changes to the database. `ROLLBACK` undoes them.

2.7 What is a common example of a many-to-many relationship in a database?

A. One `Employee` and one `Department`. B. `Students` and `Courses` (a student takes many courses, and a course has many students). C. A `Country` and its `Capital City`.

**Correct Answer: B. `Students` and `Courses`**. Many-to-many relationships require an intermediate, or **junction table**, to resolve the link (e.g., `Enrollment` table).

2.8 What is a Subquery (or Inner Query) in SQL?

A. A query used to update only a portion of a record. B. A query nested inside another SQL query (SELECT, INSERT, UPDATE, or DELETE). C. A query that only retrieves data from a single table.

**Correct Answer: B. A query nested inside another SQL query...**. Subqueries often return a single value or a list of values to be used by the Outer Query's WHERE or HAVING clause.

2.9 What is an Integrity Constraint in a database?

A. A user access permission level. B. A rule that prevents invalid data entry into tables (e.g., CHECK, UNIQUE, NOT NULL). C. An internal system log of data modifications.

**Correct Answer: B. A rule that prevents invalid data entry...**. Constraints ensure the accuracy and reliability of data in the database.

2.10 What is the main problem addressed by the 2nd Normal Form (2NF)?

A. Multiple non-key columns depending on each other. B. Partial dependency (a non-key attribute depends on only part of a composite primary key). C. Repeating groups of data.

**Correct Answer: B. Partial dependency...**. 2NF applies when a table has a composite (multi-column) primary key and removes data that depends only on a subset of that key.

**End of Normalization & Transactions. Click "Next Level" to continue.**

Level 3: Advanced Optimization & Features

3.1 What is the primary operational difference between a Clustered Index and a Non-Clustered Index?

A. A Clustered Index is used only for numeric data. B. A Clustered Index dictates the physical, on-disk order of the data rows itself. C. A Non-Clustered Index is always faster for retrieval operations.

**Correct Answer: B. A Clustered Index dictates the physical...**. A table can only have one Clustered Index (since data can only be sorted one way), but many Non-Clustered Indexes.

3.2 Which object automatically executes in response to certain events (INSERT, UPDATE, DELETE) on a table?

A. Stored Procedure B. View C. Trigger

**Correct Answer: C. Trigger**. Triggers are used to enforce business rules, audit changes, or manage related data automatically.

3.3 What is the primary security benefit of using Prepared Statements for database interaction?

A. They encrypt the data automatically. B. They prevent SQL Injection attacks by separating query logic from data. C. They restrict user access to sensitive tables.

**Correct Answer: B. They prevent SQL Injection attacks...**. Prepared statements (or parameter binding) ensure user input is treated only as data, not as executable code.

3.4 What is the purpose of the SQL command `EXPLAIN` (or `EXPLAIN PLAN`)?

A. To show the query execution plan (how the database will retrieve the data). B. To describe the columns and data types of a table. C. To execute a query in a debug mode.

**Correct Answer: A. To show the query execution plan...**. This is the primary tool for query optimization, helping developers see if indexes are being used correctly or if full table scans are occurring.

3.5 What is Denormalization?

A. The process of completely removing foreign keys. B. The intentional introduction of redundancy to improve read performance. C. Simplifying the table structure by removing complex joins.

**Correct Answer: B. The intentional introduction of redundancy...**. Denormalization is a performance trade-off, accepting slightly higher data redundancy to reduce the need for expensive JOINs during read operations.

3.6 What is a Stored Procedure primarily used for in terms of application architecture?

A. Storing large binary objects (BLOBs). B. Executing complex business logic on the database server itself. C. Defining relationships between tables.

**Correct Answer: B. Executing complex business logic...**. Stored Procedures are pre-compiled and stored, reducing network traffic and improving performance by executing multiple SQL statements efficiently.

3.7 Which transaction isolation level prevents 'dirty reads' but allows 'non-repeatable reads'?

A. SERIALIZABLE B. READ COMMITTED C. READ UNCOMMITTED

**Correct Answer: B. READ COMMITTED**. This level ensures a transaction only sees data that has been committed. Non-repeatable reads occur when a row is read twice, and another transaction modifies or deletes it between the reads.

3.8 What is the key functional difference between a View and a Materialized View?

A. A View can be updated, but a Materialized View cannot. B. A View can be created without using a JOIN statement. C. A Materialized View stores the query result physically; a standard View does not.

**Correct Answer: C. A Materialized View stores the query result physically...**. Standard Views are virtual and execute the underlying query every time they are accessed. Materialized Views are fast because they are pre-computed tables.

3.9 In database administration, what is a Deadlock?

A. Two or more transactions permanently waiting for each other to release a lock. B. A table that has run out of physical disk space. C. A transaction that is cancelled by the user.

**Correct Answer: A. Two or more transactions permanently waiting...**. Deadlocks are typically resolved by the database management system (DBMS) automatically killing one of the transactions (the 'victim').

3.10 What is Sharding in the context of database architecture?

A. Storing large binary files in the database. B. Horizontal partitioning of a large database across multiple separate servers. C. Breaking up a single table into two smaller, related tables.

**Correct Answer: B. Horizontal partitioning...**. Sharding is a scaling technique that distributes data across different physical machines (nodes) to handle massive loads that a single database server cannot manage.

**DB Mastered! Click "Finish Quiz" to see your final summary.**

Final Quiz Summary 📋

Related Topics & Tags

SQL DDL DML Normalization Indexing ACID Optimization Transactions Interview Database Design

Awk command with simple examples

Now, we are going to learn very interesting and Most important in Linux Why late lets know about Awk command,one of the mostly used command in scripting. awk command used to manipulate data | using this AWK command we can generate specified reports as per our requirement. Syntax : awk ‘BEGIN {start_action} {action} END {stop_action}’ filename let me explain about awk command with simple examples Example 1: How to print entire data of the file cat filename | awk '{print}' cat filename | awk '{print$0}' both " awk '{print}' and awk '{print$0}' " used to print entire date Example 2: How to print very first column cat filename| awk '{print$1}' awk '{print$1}' used to print very first column awk '{print$1}' used to print second column Example 3: How to print p attern Matching Technique in Awk. free -m | awk '{/Mem/print}' here, we are matching Mem using awk '{...

Learn Linux in Telugu | Linux complete Free Course in Telugu by 7Hills

Hello, Here we are going to learn complete Linux course freely & Simply way. Why Linux? Linux is an heart the operating system, Based on the Linux Kernel their are different types of Linux flavors available i.e.. RedHat, Centos, Fedora, Mint Debian, Kali, OpenSUSE...etc Features of Linux Open source. Multi-user. Multitasking. Hierarchical file system. Portable. Wide hardware support. No need to worry, we explained each and every topic clearly just click on the below links for watch on Youtube. 1. Linux outline in Telugu 2. Linux introduction In Telugu 3. Linux architecture In Telugu 4. Linux Directory Structure In Telugu 5. Linux Basic Commands In Telugu 6. ...

Hey! Well come back! Here, we are going to learn very useful command rsync. basically rsync is used to sync file system with same structure. It also used to data backup/copy. rsync is free command line utility & In most of the system it available defiantly. We can install rsync utility in debian based system using below command apt-get install rsync We can install rsync utility in redhat based system using below command yum install rsync Below command used to copy files without using option rsync /root/Desktop/original/* /root/Desktop/backup/ Below command used for recursive copy, it means with sub folders. rsync -avhr /root/Desktop/original/* /root/Desktop/backup/ Here -a is used for archive data. -v is used for show output. -h is used for show copied data in MB's,it means human readable format. Below command used for include or exclude rsync -avz --in...

Now we are going to learn very useful & interesting topic "How to reduce/extend LVM size" with real time example.hope this article very helpful for you. For example scenario, I have added some storage space to linux machine,we need to scan hosts to display in linux machine, we can scan all hots/LUN's by using below commands at the time. ------------------------------------------------------ Use below command to scan hosts/LUN's. ls /sys/class/scsi_host/ | while read host ; do echo "- - -" > /sys/class/scsi_host/$host/scan ; done Use below command to verify newly added device/stroage #lsblk Use below command to format /dev/sdb #fdisk /dev/sdb Below commands helpful for complete format. #n #p enter enter #t #8e p #wq Below command used to update partition info to kernal. partprobe /dev/sdb1 try below command to create pv(Physical volume) #pvcreate /dec/sdb1 Try below command to create volume group #vgcreate VG0...