Which SQL statement is used to delete all records from a table without removing the table structure?

The correct answer is C: TRUNCATE TABLE table_name;. # SQL Statement for Deleting Records While Preserving Table Structure Understanding the differences between SQL commands for removing data versus removing entire tables is essential for database management. ## Step 1: Identify the Requirement The question asks for a command that removes ALL records

Given a table 'employees' with salary column, which query finds employees earning more than average salary?

The correct answer is B: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);. Aggregate functions cannot be used directly in WHERE clause. Must use subquery in parentheses to calculate average first.

Which SQL command is used to retrieve data from a database?

The correct answer is A: SELECT. SELECT is the standard SQL command used to retrieve or query data from one or more tables in a database.

What is the result of: SELECT CASE WHEN 1=1 THEN 'A' WHEN 2=2 THEN 'B' ELSE 'C' END;?

The correct answer is B: 'A'. CASE statement evaluates conditions sequentially and returns value for first TRUE condition. 1=1 is TRUE, so 'A' is returned.

What will be the result of: SELECT COUNT(DISTINCT department) FROM employees WHERE salary > 50000;?

The correct answer is B: Number of unique departments with at least one employee earning > 50000. COUNT(DISTINCT column) counts unique values. Query returns count of distinct departments where salary exceeds 50000.

Home

Home › Subjects › Database › SQL Basics

Database
SQL Basics

SQL queries, DBMS, normalization, transactions

57 Q 1 Topics Take Mock Test

Difficulty: All Easy Medium Hard 1–10 of 57

Topics in Database

All SQL Basics 57

Q.1 Medium SQL Basics

For a multi-million row analytics database, which data type is most storage-efficient for storing yes/no values?

A VARCHAR(5)

B CHAR(1)

C BOOLEAN/BIT

D INT

Correct Answer: C. BOOLEAN/BIT

EXPLANATION

BOOLEAN/BIT uses minimal storage (1 byte or bit). CHAR(1) uses 1 byte. VARCHAR(5) uses more. INT uses 4 bytes. For large datasets, storage matters significantly.

Test

Q.2 Easy SQL Basics

Which feature of SQL allows preventing duplicate values in a column?

A PRIMARY KEY constraint

B UNIQUE constraint

C NOT NULL constraint

D FOREIGN KEY constraint

Correct Answer: B. UNIQUE constraint

EXPLANATION

UNIQUE constraint prevents duplicate values. PRIMARY KEY is unique + NOT NULL. NOT NULL only prevents nulls. FOREIGN KEY maintains referential integrity.

Test

Q.3 Hard SQL Basics

In a manufacturing database, to find products with sales in ALL regions, which approach is correct?

A SELECT product_id FROM sales GROUP BY product_id HAVING COUNT(DISTINCT region) = (SELECT COUNT(*) FROM regions)

B SELECT product_id FROM sales WHERE region IN (SELECT DISTINCT region FROM sales)

C SELECT DISTINCT product_id FROM sales s1 WHERE region = 'North' AND EXISTS (SELECT 1 FROM sales WHERE product_id = s1.product_id)

D SELECT product_id FROM sales WHERE region = 'North' OR region = 'South'

Correct Answer: A. SELECT product_id FROM sales GROUP BY product_id HAVING COUNT(DISTINCT region) = (SELECT COUNT(*) FROM regions)

EXPLANATION

Counts distinct regions per product and compares to total regions. Option B doesn't ensure ALL regions. Option C only checks one region. Option D is incomplete.

Test

Q.4 Hard SQL Basics

For optimizing a complex query joining 5 tables with WHERE conditions, what is the recommended approach?

A Always use INNER JOINs first, then add WHERE conditions

B Use indexes on join columns and filter columns, check execution plan, simplify subqueries

C Rewrite as multiple simple queries and combine in application

D Use VIEW instead of direct query

Correct Answer: B. Use indexes on join columns and filter columns, check execution plan, simplify subqueries

EXPLANATION

Proper indexing on join and filter columns is crucial. Analyzing execution plan identifies bottlenecks. Application-level combining is inefficient. VIEWs don't inherently optimize.

Test

Q.5 Medium SQL Basics

Which anomaly in unnormalized database allows insertion of duplicate partial information without main entity?

A Update anomaly

B Insertion anomaly

C Deletion anomaly

D Referential anomaly

Correct Answer: B. Insertion anomaly

EXPLANATION

Insertion anomaly occurs when you cannot insert data without complete information. Update anomaly = data inconsistency on updates. Deletion anomaly = loss of data when deleting.

Test

Q.6 Medium SQL Basics

In a bank database with accounts table, which query correctly identifies dormant accounts (no transactions in 2 years)?

A SELECT * FROM accounts WHERE last_transaction_date > DATE_SUB(NOW(), INTERVAL 2 YEAR)

B SELECT * FROM accounts WHERE last_transaction_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)

C SELECT * FROM accounts WHERE last_transaction_date = DATE_SUB(NOW(), INTERVAL 2 YEAR)

D SELECT * FROM accounts WHERE DATEDIFF(NOW(), last_transaction_date) = 730

Correct Answer: B. SELECT * FROM accounts WHERE last_transaction_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)

EXPLANATION

Dormant accounts have last_transaction_date LESS THAN 2 years ago (older). Option A finds active accounts. Option D is too strict with exact day matching.

Test

Q.7 Hard SQL Basics

For a student result database, to calculate cumulative marks from beginning of year, which window function is appropriate?

A ROW_NUMBER()

B SUM() OVER (ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

C RANK() OVER (PARTITION BY student_id)

D LAG() function

Correct Answer: B. SUM() OVER (ORDER BY exam_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

EXPLANATION

SUM with ROWS BETWEEN clause calculates running/cumulative sum. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows up to current row.

Test

Q.8 Medium SQL Basics

Which of the following is a valid correlated subquery for finding employees earning more than their department average?

A SELECT * FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp)

B SELECT * FROM emp e WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id = e.dept_id)

C SELECT * FROM emp WHERE salary > ALL (SELECT AVG(salary) FROM emp GROUP BY dept_id)

D Both B and C

Correct Answer: D. Both B and C

EXPLANATION

Both B and C are valid. B is a correlated subquery (references outer query). C uses ALL operator with aggregated subquery. A is incorrect as it compares to global average.

Test

Q.9 Medium SQL Basics

In an e-commerce platform with orders table, which index structure is best for frequently filtering by customer_id AND order_date?

A Single index on customer_id

B Single index on order_date

C Composite index on (customer_id, order_date)

D Two separate indexes on each column

Correct Answer: C. Composite index on (customer_id, order_date)

EXPLANATION

Composite index optimizes queries filtering by multiple columns. Single indexes require separate lookups. Column order matters for optimization.

Test

Q.10 Medium SQL Basics

For a sales database, to get top 5 products by revenue in each region, which window function approach is suitable?

A ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)

B RANK() OVER (ORDER BY revenue DESC)

C DENSE_RANK() with LIMIT

D SUM() OVER (PARTITION BY region)

Correct Answer: A. ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)

EXPLANATION

ROW_NUMBER() assigns unique numbers within each region partition, enabling row filtering. RANK() and DENSE_RANK() handle ties differently.

Test

1 2 3…6

All Subjects & Topics

Govt. Exams

Entrance Exams

Teacher Exams

Subjects

Quick Links

Database SQL Basics

Database
SQL Basics