100 SQL Coding Questions & Answers
Beginner Level
Q1. What is SQL?
SQL stands for Structured Query Language.
Q2. Select all records.
SELECT * FROM students;
Q3. Select specific columns.
SELECT name, age FROM students;
Q4. Use WHERE clause.
SELECT * FROM students WHERE age > 18;
Q5. Sort records.
SELECT * FROM students ORDER BY name;
Q6. Insert data.
INSERT INTO students VALUES (1,'Vijay',20);
Q7. Update record.
UPDATE students SET age=21 WHERE id=1;
Q8. Delete record.
DELETE FROM students WHERE id=1;
Q9. COUNT records.
SELECT COUNT(*) FROM students;
Q10. DISTINCT values.
SELECT DISTINCT city FROM students;
Intermediate Level
Q11. Create table.
CREATE TABLE students( id INT, name VARCHAR(50), age INT );
Q12. Add column.
ALTER TABLE students ADD city VARCHAR(30);
Q13. Drop column.
ALTER TABLE students DROP city;
Q14. BETWEEN operator.
SELECT * FROM students WHERE age BETWEEN 18 AND 25;
Q15. IN operator.
SELECT * FROM students WHERE city IN ('Delhi','Mumbai');Q16. LIKE operator.
SELECT * FROM students WHERE name LIKE 'A%';
Q17. LIMIT results.
SELECT * FROM students LIMIT 5;
Q18. Alias.
SELECT name AS student_name FROM students;
Q19. GROUP BY.
SELECT city,COUNT(*) FROM students GROUP BY city;
Q20. HAVING.
HAVING COUNT(*) > 1;
Advanced Level
Q21. INNER JOIN.
SELECT * FROM students s INNER JOIN marks m ON s.id=m.sid;
Q22. LEFT JOIN.
SELECT * FROM students LEFT JOIN marks ON students.id=marks.sid;
Q23. RIGHT JOIN.
SELECT * FROM students RIGHT JOIN marks ON students.id=marks.sid;
Q24. Subquery.
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);
Q25. Create view.
CREATE VIEW student_view AS SELECT name,age FROM students;
Q26. Drop view.
DROP VIEW student_view;
Q27. PRIMARY KEY.
id INT PRIMARY KEY
Q28. FOREIGN KEY.
FOREIGN KEY (sid) REFERENCES students(id);
Q29. UNIQUE constraint.
email VARCHAR(50) UNIQUE
Q30. INDEX.
CREATE INDEX idx_name ON students(name);
Advanced & Interview Level
Q31. 2nd highest salary.
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
Q32. Find duplicate records.
SELECT name,COUNT(*) FROM students GROUP BY name HAVING COUNT(*)>1;
Q33. Delete duplicates.
DELETE FROM students WHERE id NOT IN ( SELECT MIN(id) FROM students GROUP BY name );
Q34. DELETE vs TRUNCATE.
DELETE supports rollback, TRUNCATE does not.
Q35. ACID properties.
Atomicity, Consistency, Isolation, Durability.
Q36. Normalization.
Process to reduce redundancy.
Q37. Denormalization.
Improves performance by redundancy.
Q38. Stored Procedure.
Reusable SQL block.
Q39. Function.
Returns a value.
Q40. Transaction.
BEGIN; COMMIT; ROLLBACK;
Q41. EXISTS.
WHERE EXISTS(SELECT 1 FROM orders);
Q42. UNION.
SELECT name FROM a UNION SELECT name FROM b;
Q43. UNION ALL.
UNION ALL
Q44. CASE statement.
CASE WHEN age>18 THEN 'Adult' ELSE 'Minor' END
Q45. NULL check.
WHERE city IS NULL;
Q46. NOT NULL.
name VARCHAR(50) NOT NULL
Q47. CHAR vs VARCHAR.
CHAR fixed, VARCHAR variable.
Q48. WHERE vs HAVING.
WHERE filters rows, HAVING filters groups.
Q49. DROP table.
DROP TABLE students;
Q50. SQL Best Practices.
Avoid SELECT *, use indexes, optimized joins.
Q51. Fetch first 3 records.
SELECT * FROM students LIMIT 3;
Q52. Fetch last record.
SELECT * FROM students ORDER BY id DESC LIMIT 1;
Q53. Rename table.
ALTER TABLE students RENAME TO learners;
Q54. Change column datatype.
ALTER TABLE students MODIFY age SMALLINT;
Q55. Check current database.
SELECT DATABASE();
Q56. Show all tables.
SHOW TABLES;
Q57. Show table structure.
DESC students;
Q58. Add default value.
age INT DEFAULT 18;
Q59. Check constraint.
age INT CHECK (age >= 18);
Q60. AUTO_INCREMENT.
id INT AUTO_INCREMENT PRIMARY KEY;
Q61. Fetch records with NULL.
SELECT * FROM students WHERE city IS NULL;
Q62. Fetch records NOT NULL.
SELECT * FROM students WHERE city IS NOT NULL;
Q63. String length.
SELECT LENGTH(name) FROM students;
Q64. Convert to uppercase.
SELECT UPPER(name) FROM students;
Q65. Convert to lowercase.
SELECT LOWER(name) FROM students;
Q66. Trim spaces.
SELECT TRIM(name) FROM students;
Q67. Concatenate columns.
SELECT CONCAT(firstname,' ',lastname) FROM users;
Q68. Current date.
SELECT CURRENT_DATE;
Q69. Current time.
SELECT CURRENT_TIME;
Q70. Current timestamp.
SELECT CURRENT_TIMESTAMP;
Q71. Date difference.
SELECT DATEDIFF(end_date,start_date);
Q72. Extract year.
SELECT YEAR(dob) FROM students;
Q73. Extract month.
SELECT MONTH(dob) FROM students;
Q74. Round number.
SELECT ROUND(123.456,2);
Q75. Absolute value.
SELECT ABS(-10);
Q76. Average value.
SELECT AVG(salary) FROM emp;
Q77. Sum values.
SELECT SUM(salary) FROM emp;
Q78. Minimum value.
SELECT MIN(salary) FROM emp;
Q79. Maximum value.
SELECT MAX(salary) FROM emp;
Q80. IFNULL usage.
SELECT IFNULL(city,'NA') FROM students;
Q81. COALESCE.
SELECT COALESCE(phone,email,'N/A') FROM users;
Q82. Self join.
SELECT a.name,b.name FROM emp a, emp b WHERE a.manager_id=b.id;
Q83. Cross join.
SELECT * FROM a CROSS JOIN b;
Q84. Full join (concept).
Combines LEFT and RIGHT JOIN results.
Q85. Create temporary table.
CREATE TEMPORARY TABLE temp_students;
Q86. Drop temporary table.
DROP TEMPORARY TABLE temp_students;
Q87. Lock table.
LOCK TABLE students WRITE;
Q88. Unlock table.
UNLOCK TABLES;
Q89. Explain query.
EXPLAIN SELECT * FROM students;
Q90. Index usage benefit.
Improves query performance.
Q91. Composite key.
PRIMARY KEY (order_id,product_id);
Q92. Cascade delete.
ON DELETE CASCADE
Q93. Grant permission.
GRANT SELECT ON db.* TO user;
Q94. Revoke permission.
REVOKE SELECT ON db.* FROM user;
Q95. SQL Injection.
Security vulnerability due to unsafe queries.
Q96. Prepared statement.
Precompiled SQL for security & performance.
Q97. View vs Table.
View is virtual, table stores data.
Q98. Index vs Primary Key.
Primary key is unique, index may allow duplicates.
Q99. OLTP vs OLAP.
OLTP = transactions, OLAP = analytics.
Q100. SQL Best Interview Tip.
Practice joins, subqueries, indexing & optimization.
No comments:
Post a Comment