SQL — COMPLETE CGI INTERVIEW QUESTIONS + ANSWERS
1. What is SQL?
Answer
“SQL stands for Structured Query Language. It is used to manage and manipulate relational
databases.”
2. What are Uses of SQL?
Answer
Store data
Retrieve data
Update data
Delete data
Manage database structure
3. What is Database?
Answer
“Database is an organized collection of data.”
4. What is RDBMS?
Answer
“RDBMS stands for Relational Database Management System. It stores data in tables with
relationships.”
Examples:
MySQL
PostgreSQL
Oracle
5. Difference Between DBMS and RDBMS
Answer
DBMS RDBMS
Stores data Stores relational data
DBMS RDBMS
Less relationships Supports relationships
6. What is Table?
Answer
“Table stores data in rows and columns.”
7. What is Row?
Answer
“Row represents a single record.”
8. What is Column?
Answer
“Column represents an attribute/field.”
9. What is Primary Key?
Answer
“Primary key uniquely identifies each record and cannot contain null values.”
10. What is Foreign Key?
Answer
“Foreign key creates relationship between two tables.”
11. Difference Between Primary Key and Foreign Key
Answer
Primary Key Foreign Key
Uniquely identifies record References primary key
No null values Can contain null
12. What is Candidate Key?
Answer
“Candidate key is a column that can become primary key.”
13. What is Composite Key?
Answer
“Composite key contains multiple columns together as primary key.”
14. What is Unique Key?
Answer
“Unique key ensures unique values but allows one null value.”
15. What is NOT NULL Constraint?
Answer
“NOT NULL prevents null values.”
16. What is Default Constraint?
Answer
“Default constraint provides default value.”
17. What is Check Constraint?
Answer
“Check constraint validates condition before insertion.”
18. What is SQL Query?
Answer
“SQL query is command used to interact with database.”
19. Types of SQL Commands
Answer
DDL
DML
DQL
DCL
TCL
20. What is DDL?
Answer
“DDL manages database structure.”
Commands:
CREATE
ALTER
DROP
21. What is DML?
Answer
“DML manipulates data.”
Commands:
INSERT
UPDATE
DELETE
22. What is DQL?
Answer
“DQL retrieves data.”
Command:
SELECT
23. What is TCL?
Answer
“TCL manages transactions.”
Commands:
COMMIT
ROLLBACK
24. What is DCL?
Answer
“DCL controls permissions.”
Commands:
GRANT
REVOKE
25. Difference Between DELETE, DROP, and TRUNCATE
Answer
DELETE TRUNCATE DROP
Removes rows Removes all rows Removes table
Can rollback Cannot rollback mostly Deletes structure
26. What is SELECT Query?
Answer
“SELECT retrieves data from table.”
Example:
SELECT * FROM employee;
27. What is WHERE Clause?
Answer
“WHERE filters records.”
Example:
SELECT * FROM employee WHERE salary > 50000;
28. What is ORDER BY?
Answer
“ORDER BY sorts records.”
Example:
SELECT * FROM employee ORDER BY salary DESC;
29. What is GROUP BY?
Answer
“GROUP BY groups rows with same values.”
30. What is HAVING Clause?
Answer
“HAVING filters grouped data.”
31. Difference Between WHERE and HAVING
Answer
WHERE HAVING
Filters rows Filters grouped data
Before grouping After grouping
32. What is DISTINCT?
Answer
“DISTINCT removes duplicate records.”
33. What is Aggregate Function?
Answer
“Aggregate functions perform calculations on multiple rows.”
Examples:
COUNT()
SUM()
AVG()
MAX()
MIN()
34. What is COUNT()?
Answer
“COUNT() counts number of rows.”
35. What is JOIN?
Answer
“JOIN combines data from multiple tables.”
36. Types of JOINs
Answer
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
37. What is INNER JOIN?
Answer
“INNER JOIN returns matching records from both tables.”
38. What is LEFT JOIN?
Answer
“LEFT JOIN returns all left table records and matched right table records.”
39. What is RIGHT JOIN?
Answer
“RIGHT JOIN returns all right table records and matched left table records.”
40. What is FULL JOIN?
Answer
“FULL JOIN returns all records from both tables.”
41. Difference Between INNER JOIN and LEFT JOIN
Answer
INNER JOIN LEFT JOIN
Only matching rows All left rows + matching rows
42. What is Self Join?
Answer
“Self join joins table with itself.”
43. What is Cross Join?
Answer
“Cross join returns Cartesian product.”
44. What is Subquery?
Answer
“Subquery is query inside another query.”
45. What is Nested Query?
Answer
“Nested query is another name for subquery.”
46. What is Alias?
Answer
“Alias gives temporary name to table or column.”
47. What is Index?
Answer
“Index improves search performance.”
48. Why Index Used?
Answer
“To speed up data retrieval.”
49. Disadvantage of Index
Answer
“It increases storage and slows insert/update operations.”
50. What is Normalization?
Answer
“Normalization organizes database to reduce redundancy.”
51. Types of Normalization
Answer
1NF
2NF
3NF
BCNF
52. What is 1NF?
Answer
“1NF removes repeating groups and ensures atomic values.”
53. What is 2NF?
Answer
“2NF removes partial dependency.”
54. What is 3NF?
Answer
“3NF removes transitive dependency.”
55. What is Denormalization?
Answer
“Denormalization adds redundancy for performance optimization.”
56. What is ACID Property?
Answer
“ACID ensures reliable transactions.”
57. ACID Properties
Answer
Atomicity
Consistency
Isolation
Durability
58. What is Transaction?
Answer
“Transaction is group of SQL operations executed together.”
59. What is COMMIT?
Answer
“COMMIT permanently saves transaction.”
60. What is ROLLBACK?
Answer
“ROLLBACK undoes transaction.”
61. What is View?
Answer
“View is virtual table based on SQL query.”
62. Advantages of View
Answer
Security
Simplified queries
Reusability
63. What is Stored Procedure?
Answer
“Stored procedure is precompiled SQL code stored in database.”
64. Advantages of Stored Procedure
Answer
Faster execution
Reusability
Reduced network traffic
65. What is Trigger?
Answer
“Trigger automatically executes on database events.”
66. What is Union?
Answer
“UNION combines results of multiple SELECT queries and removes duplicates.”
67. Difference Between UNION and UNION ALL
Answer
UNION UNION ALL
Removes duplicates Keeps duplicates
68. What is SQL Injection?
Answer
“SQL Injection is security attack where malicious SQL is inserted.”
69. How Prevent SQL Injection?
Answer
Prepared statements
Parameterized queries
Input validation
70. What is Prepared Statement?
Answer
“Prepared statement executes parameterized SQL queries securely.”
71. Difference Between SQL and NoSQL
Answer
SQL NoSQL
Relational Non-relational
Structured schema Flexible schema
72. Why MySQL Used in Your Project?
Answer
“MySQL provides relational structure, reliability, and easy integration with Spring Boot.”
73. What are CRUD Operations?
Answer
Create → INSERT
Read → SELECT
Update → UPDATE
Delete → DELETE
74. Query to Find Second Highest Salary
\text{SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
Answer
“This query first finds highest salary and then finds maximum salary smaller than highest salary.”
75. Query to Find Duplicate Records
SELECT name, COUNT(*)
FROM employee
GROUP BY name
HAVING COUNT(*) > 1;
76. Difference Between CHAR and VARCHAR
Answer
CHAR VARCHAR
Fixed length Variable length
77. What is Auto Increment?
Answer
“Auto increment automatically generates numeric values.”
78. What is Schema?
Answer
“Schema defines database structure.”
VERY IMPORTANT CGI FOLLOW-UP QUESTIONS
79. Why Normalization Important?
Answer
“To reduce redundancy and improve data consistency.”
80. Why Index Improves Performance?
Answer
“Because database does not scan full table.”
81. Why GROUP BY Used?
Answer
“To perform aggregation on grouped data.”
82. Why HAVING Needed if WHERE Exists?
Answer
“WHERE cannot filter aggregated results.”
83. Why Inner Join Most Common?
Answer
“Because usually only matching records are needed.”
84. Difference Between DELETE and TRUNCATE Internally
Answer
“DELETE removes rows one by one while TRUNCATE removes entire data pages.”
85. Why Prepared Statements Safer?
Answer
“They prevent SQL injection attacks.”
86. Why ACID Properties Important?
Answer
“To ensure reliable transactions and data consistency.”
87. What Happens if Transaction Fails?
Answer
“ROLLBACK restores previous consistent state.”
88. Difference Between Primary Key and Unique Key
Answer
Primary Key Unique Key
One per table Multiple allowed
No null values One null allowed
MOST IMPORTANT SQL TOPICS FOR YOUR RESUME
Highest probability:
1. Joins
2. Primary vs Foreign key
3. Normalization
4. SQL vs NoSQL
5. GROUP BY and HAVING
6. CRUD operations
7. Second highest salary query
8. ACID properties
9. Indexing
10. DELETE vs TRUNCATE vs DROP