SQL Questions and Answers : PART 1

1. What is denormalization?

A. Removing tables

B. Combining tables to improve performance

C. Deleting duplicate data

D. Creating indexes only

Ans : B

2. What is the main advantage of denormalization?

A. Less storage

B. Better normalization

C. No redundancy

D. Faster query performance

ANS : D

3. What is a disadvantage of denormalization?

A. Increased redundancy

B. Better reporting

C. Reduced JOINs

D. Faster queries

Ans : A

4. Which systems commonly use denormalization?

A. Banking transaction systems

B. Reporting and dashboard systems

C. Authentication systems

D. Compiler systems

Ans : B

5. Which statement is TRUE?

A. Normalization improves performance only

B. Denormalization reduces redundancy

C. Normalization reduces redundancy

D. Denormalization removes duplicate data

Ans : C

6. What is the purpose of constraints in SQL?

A. Increase redundancy

B. Maintain data integrity

C. Speed up internet

D. Delete tables

Ans : B

7. Which constraint prevents NULL values?

A. DEFAULT

B. UNIQUE

C. NOT NULL

D. CHECK

Ans : C

8. Which constraint ensures all values are unique?

A. PRIMARY KEY

B. FOREIGN KEY

C. DEFAULT

D. UNIQUE

Ans : D

9. What is the main purpose of a FOREIGN KEY?

A. Create duplicate values

B. Improve UI design

C. Create relationship between table

D. Delete records automatically

Ans : C

10. Which constraint is used to restrict values based on condition?

A. UNIQUE

B. CHECK

C. DEFAULT

D. NOT NULL

Ans : B

11. What is the purpose of UNION in SQL?

A. Delete duplicate rows

B. Combine results of multiple SELECT queries

C. Create relationships

D. Sort tables

Ans : B

12. What does UNION do with duplicate rows?

A. Keeps duplicates

B. Deletes table

C. Removes duplicates

D. Converts to NULL

Ans : C

13. Which operator keeps duplicate rows?

A. UNION

B. UNION DISTINCT

C. UNION ALL

D. JOIN

Ans : C

14. Which statement is TRUE about UNION?

A. Number of columns can be different

B. Column data types should be compatible

C. ORDER BY cannot be used

D. UNION works only on one table

Ans : B

15. Which is faster for large datasets?

A. GROUP BY

B. JOIN

C. UNION

D. UNION ALL

Ans : A

16. What is the purpose of CASE statement in SQL?

A. Apply conditional logic

B. Join tables

C. Delete rows

D. Create tables

Ans : A

17. CASE statement in SQL is similar to which programming concept?

A. Loop

B. Function

C. if-else

D. Array

Ans : C

18. Which keyword is mandatory in CASE statement?

A. BREAK

B. END

C. STOP

D. EXIT

Ans : B

19. CASE statement is commonly used in:

A. Reporting and dashboards

B. Game development only

C. Hardware design

D. Network routing

Ans : A

20. What does NULL represent in SQL?

A. Zero

B. Empty string

C. Missing or unknown value

D. False value

Ans : C

21. What is the purpose of COALESCE function?

A. Delete NULL values

B. Replace NULL with another value

C. Sort NULL values

D. Count NULL values

Ans : B

22. What will this query return if bonus is NULL?

SELECT COALESCE(bonus, 0) FROM employees;

A. NULL

B. 0

C. bonus

D. Empty string

Ans : B

23. What does COALESCE(phone, email, 'No Contact') return?

A. Last value always

B. First non-NULL value

C. Only NULL values

D. Nothing

Ans : B

24. Which function converts text to uppercase?

A. LOWER()

B. UPPER()

C. CAPITAL()

D. CASE()

Ans : B

25. What does CONCAT() do in SQL?

A. Removes spaces

B. Counts characters

C. Combines strings

D. Extracts substring

Ans : C

26. Which function removes extra spaces from text?

A. REPLACE()

B. LENGTH()

C. CONCAT()

D. TRIM()

Ans : D

27. What will this query return?

SELECT REPLACE('I love Java', 'Java', 'SQL');

A. I love Java

B. I love SQL

C. SQL and Java Love

D. SQL

Ans : B

28. Which function returns the current date and time?

A. CURDATE()

B. CURTIME()

C. NOW()

D. DATE()

Ans : C

29. Which function returns only the current date?

A. NOW()

B. CURDATE()

C. TODAY()

D. CURRENT_DATE()

Ans : B

30. Which function is used to calculate difference between two dates?

A. DATE_FORMAT()

B. MONTH()

C. DATEDIFF()

D. DAY()

Ans : C

31. What will this query return?

SELECT DATE_FORMAT('2026-06-28', '%d-%m-%Y');

A. 28-06-2026

B. 06-28-2026

C. 06-28-26

D. 2026-06-28

Ans : A

32. What is a View in SQL?

A. A physical table storing data

B. A virtual table based on a query

C. An index

D. A trigger

Ans : B

33. What is the main difference between a View and a Materialized View?

A. Both store data physically

B. View stores data, Materialized View stores query

C. View stores query, Materialized View stores data

D. There is no difference

Ans : C

34. Which object generally provides faster query performance for large reports?

A. Table Scan

B. View

C. Materialized View

D. Trigger

Ans : C

35. What must be done to see the latest data in a Materialized View after underlying tables change?

A. Recreate the table

B. Refresh the Materialized View

C. Restart the database

D. Create an index

Ans : B

36. Which statement is TRUE?

A. Materialized Views cannot contain aggregate

B. Views always consume more storage than Materialized Views

C. Materialized Views always show real-time data automatically

D. Views always display current data from underlying tables

Ans : D

37. What is a User Defined Function (UDF)?

A. A built-in SQL command

B. A custom function created by users

C. A database table

D. An index

Ans : B

38. What is mandatory for a SQL Function?

A. Must contain JOIN

B. Must return a value

C. Must create a table

D. Must use GROUP BY

Ans : B

39. What is the main difference between a Function and a Procedure?

A. Functions return a value, Procedures may not

B. Procedures return a value, Functions may not

C. Both are identical

D. Functions cannot accept parameters

Ans : A

40. Where can a SQL Function be used?

A. Inside SELECT statement

B. Inside WHERE clause

C. Inside ORDER BY clause

D. All of the above

Ans : D

41. What is a Recursive CTE?

A. A permanent table

B. A CTE that references itself

C. A stored procedure

D. A trigger

Ans : B

42. Which keyword is commonly used to create a Recursive CTE?

A. REPEAT

B. LOOP

C. RECURSIVE

D. FUNCTION

Ans : C

43. What are the two main parts of a Recursive CTE?

A. SELECT and UPDATE

B. Anchor Query and Recursive Query

C. JOIN and GROUP BY

D. WHERE and HAVING

Ans : B

44. What will happen if a Recursive CTE does not have a stopping condition?

A. It will return NULL

B. It will stop automatically after 10 rows

C. It may enter an infinite loop or hit recursion limits

D. It will behave like a normal CTE

Ans : C

45.Which of the following is a common use case for Recursive CTEs?

A. Creating indexes

B. Building employee-manager hierarchies

C. Dropping tables

Ans : B

46. What is Dynamic SQL?

A. SQL stored permanently in a table

B. SQL query built and executed at runtime

C. SQL used only for databases

D. SQL without SELECT statement

Ans : B

47. What is the main advantage of Dynamic SQL?

A. Less flexibility

B. Faster than all queries

C. Flexible query generation at runtime

D. Removes need for indexes

Ans : C

48. Which MySQL statement is used to execute a prepared Dynamic SQL query?

A. RUN

B. EXECUTE

C. CALL

D. START

Ans : B

49. What is a major risk associated with Dynamic SQL?

A. Data normalization

B. Foreign keys

C. Index creation

D. SQL Injection

Ans : D

50. Which statement is TRUE about Static SQL and Dynamic SQL?

A. Static SQL is created at runtime

B. Dynamic SQL is always faster

C. Static SQL is fixed, Dynamic SQL is generated at runtime

D. Both are exactly the same

Ans : C

✅ SQL Date & Time Functions ✅

 ✅ SQL Date & Time Functions ✅

1. Why Date Functions Matter?  

Almost every real-world database contains dates 👇  

• ✔️ Orders  
• ✔️ Employee joining dates  
• ✔️ Transactions  
• ✔️ Login activity  

SQL date functions help analyze time-based data 💯  

⚡ 2. Common Date Functions  

Function : Purpose  

NOW() : Current date & time  

CURDATE() : Current date  

CURTIME() : Current time  

YEAR() : Extract year  

MONTH() : Extract month  

DAY() : Extract day  

DATEDIFF() : Difference between dates  

DATE_FORMAT() : Format dates    

🔥 3. NOW(), CURDATE(), CURTIME()

SELECT NOW();

Output : ✔️ Current date + time

SELECT CURDATE();

Output : ✔️ Current date only

SELECT CURTIME();

Output : ✔️ Current time only  

🔥 4. YEAR(), MONTH(), DAY()

SELECT YEAR(joining_date) FROM employees;

SELECT MONTH(joining_date) FROM employees;

SELECT DAY(joining_date) FROM employees;

🔥 5. DATEDIFF()  

👉 Find difference between dates

SELECT DATEDIFF('2026-06-10', '2026-06-01');

✔️ Result → 9 days  

🔥 6. DATE_FORMAT()  

👉 Format dates professionally

SELECT DATE_FORMAT(NOW(), '%d-%m-%Y');

✔️ Example Output → 06-06-2026  

🎯 7. Real Example  

👉 Employees joined in 2025

SELECT * FROM employees WHERE YEAR(joining_date) = 2025;

🎯 8. Practice Tasks  

1. Show current date  

2. Show current time  

3. Extract joining year from employee table  

4. Find employees joined in specific month  

5. Calculate days between two dates  

⚡ Mini Challenge 🔥  

👉 Find employees who joined in the last 30 days    

✅ Solution Using DATEDIFF()

SELECT * FROM employees WHERE DATEDIFF(CURDATE(), joining_date) <= 30;

✅ Alternative Solution Using DATE_SUB()

SELECT * FROM employees WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

🔥 Pro Tip

Date functions are heavily used in:  

👉 Sales reports  

👉 Monthly dashboards  

👉 Retention analysis  

👉 Time-series analytics 💯

✅ SQL String Functions

 

✅ SQL String Functions

  

🧠 1. What are String Functions?  

String functions are used to  

• 👉 manipulate text data  
• 👉 clean messy data  
• 👉 format outputs  

Used heavily in:  

✔️ Data Analytics  

✔️ Reporting  

✔️ ETL processes  

⚡ 2. Common String Functions  

Function  : Purpose  

UPPER() : Convert to uppercase  

LOWER() : Convert to lowercase  

LENGTH() : Count characters  

CONCAT() : Join strings  

SUBSTRING() : Extract part of string  

TRIM() : Remove spaces  

REPLACE() : Replace text  

🔥 3. UPPER() & LOWER()

SELECT UPPER(name) AS upper_name FROM employees;

SELECT LOWER(name) AS lower_name FROM employees;

🔥 4. LENGTH()  

👉 Count number of characters

SELECT name, LENGTH(name) AS total_chars FROM employees;

🔥 5. CONCAT()  

👉 Combine strings

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

🔥 6. SUBSTRING()  

👉 Extract part of string

SELECT SUBSTRING(name, 1, 3) FROM employees;

✔️ Extracts first 3 characters

🔥 7. TRIM()  

👉 Remove extra spaces

SELECT TRIM('   SQL   ');

✔️ Result → SQL

🔥 8. REPLACE()  

👉 Replace text inside string

SELECT REPLACE('I love Java', 'Java', 'SQL');

✔️ Result → I love SQL

🎯 9. Practice Tasks  

1. Convert names to uppercase  

2. Convert emails to lowercase  

3. Combine first & last names  

4. Extract first 4 letters of names  

5. Remove extra spaces from city names  

⚡ Mini Challenge 🔥  

👉 Create employee usernames using:  

first 3 letters of name + employee ID  

Example:  

Amit + 101 → Ami101  

🔥 Mini Challenge Solution 💯

👉 Requirement:  

Create username using:  

- First 3 letters of name  

- Employee ID  

Example:  

Amit + 101 → Ami101  

✅ SQL Solution

SELECT name,

       emp_id,

       CONCAT(SUBSTRING(name, 1, 3), emp_id) AS username

FROM employees;

✅ Example Output  

name : emp_id : username  

Amit : 101 : Ami101  

Neha : 102 : Neh102  

Ravi : 103 : Rav103  

🧠 How It Works  

👉 SUBSTRING(name, 1, 3)  

Extracts first 3 letters  

👉 CONCAT()  

Combines extracted text with employee ID  

🔥 Real-World Usage:  

String functions are commonly used for:  

👉 Username generation  

👉 Email formatting  

👉 Data cleaning  

👉 Customer IDs 💯

COALESCE & NULL Handling in SQL

 

🧠 1. What is NULL in SQL?  

NULL means  : 

• 👉 missing value  
• 👉 unknown value
• 👉 no data available  

⚠️ NULL is NOT:  

❌ 0  

❌ Empty string  

⚡ 2. Problems with NULL Values  

NULL can affect:  

• ❌ Calculations  
• ❌ Comparisons
• ❌ Reports  

Example 👇

SELECT salary + bonus

FROM employees;

If bonus is NULL → result becomes NULL ❌  

========================================================

🔥 3. COALESCE Function  

👉 Replaces NULL with another value  

✅ Syntax

COALESCE(column, value)

⚡ 4. Basic Example

SELECT name,

       COALESCE(bonus, 0) AS bonus

FROM employees;

✔️ If bonus is NULL → shows 0 instead  

⚡ 5. Multiple Values with COALESCE

SELECT name,

       COALESCE(phone, email, 'No Contact') AS contact

FROM employees;

✔️ Returns first non-NULL value  

🔥 6. IS NULL & IS NOT NULL  

👉 Find employees without bonus

SELECT * FROM employees

WHERE bonus IS NULL;

👉 Find employees with bonus

SELECT * FROM employees

WHERE bonus IS NOT NULL;

⚡ 7. NULL Handling in Aggregation

SELECT AVG(COALESCE(bonus, 0))

FROM employees;

✔️ Prevents NULL issues in calculations  

🎯 8. Practice Tasks  

1. Replace NULL bonus with 0  

2. Find rows with NULL values  

3. Find rows without NULL values  

4. Use COALESCE with multiple columns  

5. Calculate total salary safely using COALESCE  

⚡ Mini Challenge 🔥  

👉 Show employee total income:  

salary + bonus  

(If bonus is NULL → treat as 0)  

🔥 Mini Challenge Solution 💯

👉 Requirement:  

total_income = salary + bonus  

If bonus is NULL → treat it as 0  

✅ SQL Solution Using COALESCE

SELECT name,

       salary,

       COALESCE(bonus, 0) AS bonus,

       salary + COALESCE(bonus, 0) AS total_income

FROM employees;

✅ Example Output  

name : Amit, salary : 70000, bonus : 5000, total_income : 75000  

name : Neha, salary : 50000, bonus : 0, total_income : 50000  

name : Ravi, salary : 60000, bonus : 3000, total_income : 63000  

🧠 How It Works  

👉 COALESCE(bonus, 0)  

means:  

- If bonus exists → use bonus  

- If bonus is NULL → use 0  

  

✅ CASE Statement in SQL

 

✅ CASE Statement in SQL  

🧠 1. What is CASE Statement?

• CASE is used for conditional logic in SQL
• It's like if-else in programming
• Helps categorize data
• Create custom labels
• Build smart reports  

⚡ 2. Basic Syntax

SELECT column_name,

       CASE

           WHEN condition THEN result

           ELSE result

       END AS alias_name

FROM table_name;

📊 Example Table  

name : Ajit, Neha, Sujit  

salary : 70000, 40000, 55000  

🔥 3. Basic CASE Example  

👉 Categorize employees by salary

SELECT name, salary,

       CASE

           WHEN salary >= 60000 THEN 'High Salary'

           WHEN salary >= 50000 THEN 'Medium Salary'

           ELSE 'Low Salary'

       END AS salary_category

FROM employees;

✅ Output  

name : Ajit, Sujit, Neha  

salary : 70000, 55000, 40000  

salary_category : High Salary, Medium Salary, Low Salary  

⚡ 4. CASE with Aggregation  

👉 Count employees department-wise

SELECT department,

       SUM(

           CASE

               WHEN salary > 50000 THEN 1

               ELSE 0

           END

       ) AS high_salary_count

FROM employees

GROUP BY department;

⚡ 5. CASE in ORDER BY

SELECT name, department

FROM employees

ORDER BY

CASE

    WHEN department = 'IT' THEN 1

    WHEN department = 'HR' THEN 2

    ELSE 3

END;

🎯 6. Practice Tasks

1. Categorize salary as High/Low

2. Create age groups using CASE

3. Use CASE with GROUP BY

4. Count employees based on salary condition

5. Use CASE in ORDER BY

What will this query return?

SELECT name, salary,

       CASE

           WHEN salary >= 50000 THEN 'High'

           ELSE 'Low'

       END AS category

FROM employees;

A. Deletes salaries

B. Sorts employees

C. Add employees

D. Categorizes salary as High or Low

Ans : D

✅ UNION & UNION ALL in SQL

 ✅ UNION & UNION ALL in SQL  

🧠 1. What is UNION?  

UNION is used to combine results from multiple SELECT queries  

"Merge data from two tables into one result”  

⚡ 2. Rules for UNION  

•  Same number of columns  
•  Same datatype/order of columns  

📊 Example Tables  

👨‍💼 employees_2025

Name  

- Ajit

- Neha  

👨‍💼 employees_2026  

name  

- Sujit 

- Neha  

🔥 3. UNION Example

SELECT name FROM employees_2025

UNION

SELECT name FROM employees_2025;

✔️ Removes duplicates automatically  

✅ Result  

name  

- Ajit

- Neha  

- Sujit  

⚡ 4. UNION ALL

SELECT name FROM employees_2025

UNION ALL

SELECT name FROM employees_2026;

• Keeps duplicates  
• Faster than UNION  

✅ Result  

name  

- Ajit

- Neha  

- Sujit  

- Neha  

🔥 5. UNION vs UNION ALL  

UNION  

•  Removes duplicates
•  Slower  
• Doesn't keep all rows  

UNION ALL  

• Doesn't remove duplicates  
• Faster  
• Keeps all rows  

⚡ 6. ORDER BY with UNION

SELECT name FROM employees_2025

UNION

SELECT name FROM employees_2026

ORDER BY name;

🎯 7. Practice Tasks  

1. Combine employee names using UNION  

2. Combine employee names using UNION ALL  

3. Identify duplicate removal  

4. Sort UNION result using ORDER BY  

5. Compare UNION vs UNION ALL output  

🧠 Why UNION ALL?  

👉 UNION → removes duplicates  

👉 UNION ALL → keeps duplicates + faster

#union #union_alll