1. What is SQL?
Structured Query Language, used to manage relational databases, e.g., `SELECT * FROM table;`.
2. What is a relational database?
A database storing data in tables with relationships, e.g., linked by keys.
3. What is a table in SQL?
A collection of rows and columns, e.g., `CREATE TABLE users (id INT, name VARCHAR(50));`.
4. What is a row in SQL?
A single record in a table, e.g., `(1, \'Alice\')` in `users`.
5. What is a column in SQL?
A field storing data of a specific type, e.g., `name` in `users`.
6. What is DDL in SQL?
Data Definition Language, e.g., `CREATE`, `ALTER`, `DROP`.
7. What is DML in SQL?
Data Manipulation Language, e.g., `SELECT`, `INSERT`, `UPDATE`, `DELETE`.
8. What is DCL in SQL?
Data Control Language, e.g., `GRANT`, `REVOKE`.
9. What is TCL in SQL?
Transaction Control Language, e.g., `COMMIT`, `ROLLBACK`.
10. How do you create a table in SQL?
Use `CREATE TABLE`, e.g., `CREATE TABLE users (id INT, name VARCHAR(50));`.
11. What are common SQL data types?
`INT`, `VARCHAR`, `FLOAT`, `DATE`, `BOOLEAN`, e.g., `age INT`.
12. What is the `VARCHAR` data type?
Variable-length string, e.g., `VARCHAR(50)` for names.
13. What is the `CHAR` data type?
Fixed-length string, e.g., `CHAR(10)` for codes.
14. What is the `INT` data type?
Integer, e.g., `id INT` for identifiers.
15. What is the `FLOAT` data type?
Floating-point number, e.g., `salary FLOAT`.
16. What is the `DATE` data type?
Stores dates, e.g., `birth_date DATE`.
17. What is a primary key?
Uniquely identifies rows, e.g., `id INT PRIMARY KEY`.
18. What is a foreign key?
Links tables, e.g., `user_id INT REFERENCES users(id)`.
19. What is a unique constraint?
Ensures unique values, e.g., `email VARCHAR(50) UNIQUE`.
20. What is a `NOT NULL` constraint?
Requires non-null values, e.g., `name VARCHAR(50) NOT NULL`.
21. What is a default constraint?
Sets default value, e.g., `status VARCHAR(10) DEFAULT \'active\'`.
22. What is a check constraint?
Enforces condition, e.g., `age INT CHECK (age >
23. How do you drop a table?
Use `DROP TABLE`, e.g., `DROP TABLE users;`.
24. How do you alter a table to add a column?
Use `ALTER TABLE`, e.g., `ALTER TABLE users ADD email VARCHAR(50);`.
25. How do you modify a column in a table?
Use `ALTER TABLE`, e.g., `ALTER TABLE users MODIFY COLUMN email VARCHAR(100);`.
26. How do you drop a column from a table?
Use `ALTER TABLE`, e.g., `ALTER TABLE users DROP COLUMN email;`.
27. What is the `INSERT` statement?
Adds rows, e.g., `INSERT INTO users (id, name) VALUES (1, \'Alice\');`.
28. How do you insert multiple rows?
Use multiple `VALUES`, e.g., `INSERT INTO users (id, name) VALUES (1, \'Alice\'), (2, \'Bob\');`.
29. What is the `UPDATE` statement?
Modifies rows, e.g., `UPDATE users SET name
30. What is the `DELETE` statement?
Removes rows, e.g., `DELETE FROM users WHERE id
31. How do you delete all rows from a table?
Use `DELETE` or `TRUNCATE`, e.g., `DELETE FROM users;` or `TRUNCATE TABLE users;`.
32. What is the difference between `DELETE` and `TRUNCATE`?
`DELETE` removes specific rows; `TRUNCATE` resets table.
33. What is the `SELECT` statement?
Retrieves data, e.g., `SELECT * FROM users;`.
34. How do you select specific columns?
List columns, e.g., `SELECT id, name FROM users;`.
35. What is the `WHERE` clause?
Filters rows, e.g., `SELECT * FROM users WHERE age > 18;`.
36. What are comparison operators in SQL?
`
37. What are logical operators in SQL?
`AND`, `OR`, `NOT`, e.g., `WHERE age > 18 AND city
38. What is the `IN` operator?
Matches list, e.g., `WHERE id IN (1, 2, 3)`.
39. What is the `BETWEEN` operator?
Matches range, e.g., `WHERE age BETWEEN 18 AND 30`.
40. What is the `LIKE` operator?
Pattern matching, e.g., `WHERE name LIKE \'A%\'`.
41. What is the `%` wildcard in `LIKE`?
Matches any characters, e.g., `\'A%\'` for names starting with \'A\'.
42. What is the `_` wildcard in `LIKE`?
Matches single character, e.g., `\'A_e\'` for \'Ace\'.
43. What is the `ORDER BY` clause?
Sorts results, e.g., `SELECT * FROM users ORDER BY name ASC;`.
44. What is the difference between `ASC` and `DESC`?
`ASC` sorts ascending; `DESC` sorts descending.
45. What is the `LIMIT` clause?
Restricts rows, e.g., `SELECT * FROM users LIMIT 10;`.
46. What is the `OFFSET` clause?
Skips rows, e.g., `SELECT * FROM users LIMIT 10 OFFSET 20;`.
47. What is the `DISTINCT` keyword?
Removes duplicates, e.g., `SELECT DISTINCT city FROM users;`.
48. What is an alias in SQL?
Renames column/table, e.g., `SELECT name AS full_name FROM users;`.
49. How do you use a table alias?
Assign alias, e.g., `SELECT u.name FROM users AS u;`.
50. What is the `GRANT` statement?
Gives permissions, e.g., `GRANT SELECT ON users TO \'user1\';`.
51. What is the `REVOKE` statement?
Removes permissions, e.g., `REVOKE SELECT ON users FROM \'user1\';`.
52. What is the `COMMIT` statement?
Saves transaction, e.g., `COMMIT;`.
53. What is the `ROLLBACK` statement?
Undoes transaction, e.g., `ROLLBACK;`.
54. What is the `SAVEPOINT` statement?
Sets rollback point, e.g., `SAVEPOINT sp1;`.
55. What is the `SET TRANSACTION` statement?
Configures transaction, e.g., `SET TRANSACTION READ ONLY;`.
56. What is a comment in SQL?
Explains code, e.g., `-- This is a comment` or `/* Multi-line */`.
57. What is the `NULL` value in SQL?
Represents missing data, e.g., `WHERE column IS NULL`.
58. What is the `IS NULL` operator?
Checks for `NULL`, e.g., `WHERE name IS NULL`.
59. What is the `IS NOT NULL` operator?
Checks for non-`NULL`, e.g., `WHERE name IS NOT NULL`.
60. What is the `COALESCE` function?
Returns first non-`NULL`, e.g., `COALESCE(column, \'default\')`.
61. What is the `NULLIF` function?
Returns `NULL` if equal, e.g., `NULLIF(column, \'value\')`.
62. What is a schema in SQL?
Logical container, e.g., `CREATE SCHEMA myschema;`.
63. How do you create an index?
Use `CREATE INDEX`, e.g., `CREATE INDEX idx_name ON users(name);`.
64. What is a clustered index?
Determines physical order, e.g., primary key in SQL Server.
65. What is a non-clustered index?
Separate structure, e.g., `CREATE NONCLUSTERED INDEX idx ON users(name);`.
66. What is the `DESCRIBE` statement?
Shows table structure, e.g., `DESCRIBE users;` (MySQL).
67. What is the `EXPLAIN` statement?
Shows query plan, e.g., `EXPLAIN SELECT * FROM users;`.
68. What is the `TRUNCATE` statement?
Removes all rows, e.g., `TRUNCATE TABLE users;`.
69. What is the `MERGE` statement?
Upserts data, e.g., `MERGE INTO users USING source ON condition WHEN MATCHED THEN UPDATE;`.
70. What is a sequence in SQL?
Generates numbers, e.g., `CREATE SEQUENCE seq_id START WITH 1;`.
71. How do you use a sequence?
Use `NEXTVAL`, e.g., `INSERT INTO users (id) VALUES (seq_id.NEXTVAL);`.
72. // Queries and Joins: What is a `JOIN` in SQL?
Combines tables, e.g., `SELECT * FROM users JOIN orders ON users.id
73. What is an `INNER JOIN`?
Matching rows, e.g., `SELECT * FROM users INNER JOIN orders ON users.id
74. What is a `LEFT JOIN`?
All left table rows, e.g., `SELECT * FROM users LEFT JOIN orders ON users.id
75. What is a `RIGHT JOIN`?
All right table rows, e.g., `SELECT * FROM users RIGHT JOIN orders ON users.id
76. What is a `FULL JOIN`?
All rows from both, e.g., `SELECT * FROM users FULL JOIN orders ON users.id
77. What is a `CROSS JOIN`?
Cartesian product, e.g., `SELECT * FROM users CROSS JOIN orders;`.
78. What is a self-join?
Joins table to itself, e.g., `SELECT a.name, b.name FROM users a JOIN users b ON a.manager_id
79. How do you join multiple tables?
Chain joins, e.g., `SELECT * FROM users JOIN orders ON users.id
80. What is the `ON` clause in a join?
Specifies join condition, e.g., `ON users.id
81. What is the `USING` clause in a join?
Simplifies join, e.g., `SELECT * FROM users JOIN orders USING (id);`.
82. What is a natural join?
Joins on same-named columns, e.g., `SELECT * FROM users NATURAL JOIN orders;`.
83. What is a subquery?
Query within query, e.g., `SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);`.
84. What is a correlated subquery?
References outer query, e.g., `SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id
85. What is a scalar subquery?
Returns single value, e.g., `SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id
86. What is a multi-row subquery?
Returns multiple rows, e.g., `SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);`.
87. What is the `EXISTS` operator?
Checks subquery results, e.g., `SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id
88. What is the `NOT EXISTS` operator?
Opposite of `EXISTS`, e.g., `SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id
89. What is the `ANY` operator?
Compares to any value, e.g., `SELECT * FROM users WHERE age > ANY (SELECT age FROM employees);`.
90. What is the `ALL` operator?
Compares to all values, e.g., `SELECT * FROM users WHERE age > ALL (SELECT age FROM employees);`.
91. What is a nested query?
Subquery in `WHERE` or `SELECT`, e.g., `SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);`.
92. What is a derived table?
Subquery in `FROM`, e.g., `SELECT * FROM (SELECT id, name FROM users) AS temp;`.
93. What is a join with a subquery?
Joins subquery result, e.g., `SELECT u.name FROM users u JOIN (SELECT user_id FROM orders) o ON u.id
94. What is the `GROUP BY` clause?
Groups rows, e.g., `SELECT city, COUNT(*) FROM users GROUP BY city;`.
95. What is the `HAVING` clause?
Filters groups, e.g., `SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 10;`.
96. What is the difference between `WHERE` and `HAVING`?
`WHERE` filters rows; `HAVING` filters groups.
97. How do you combine multiple conditions in `WHERE`?
Use `AND`, `OR`, e.g., `WHERE age > 18 AND city
98. What is a case-sensitive query?
Depends on collation, e.g., `WHERE name
99. How do you select top N rows?
Use `LIMIT` or `TOP`, e.g., `SELECT * FROM users LIMIT 5;` or `SELECT TOP 5 * FROM users;`.
100. How do you skip rows in a query?
Use `OFFSET`, e.g., `SELECT * FROM users LIMIT 5 OFFSET 10;`.
101. What is a union in SQL?
Combines results, e.g., `SELECT name FROM users UNION SELECT name FROM employees;`.
102. What is `UNION ALL`?
Combines with duplicates, e.g., `SELECT name FROM users UNION ALL SELECT name FROM employees;`.
103. What is the difference between `UNION` and `UNION ALL`?
`UNION` removes duplicates; `UNION ALL` keeps them.
104. What is `INTERSECT`?
Common rows, e.g., `SELECT id FROM users INTERSECT SELECT user_id FROM orders;`.
105. What is `EXCEPT`?
Rows in first not in second, e.g., `SELECT id FROM users EXCEPT SELECT user_id FROM orders;`.
106. How do you handle NULLs in joins?
Use `LEFT` or `RIGHT` join, e.g., `SELECT * FROM users LEFT JOIN orders ON users.id
107. What is a three-way join?
Joins three tables, e.g., `SELECT * FROM users JOIN orders ON users.id
108. What is a join with multiple conditions?
Uses `AND`, e.g., `SELECT * FROM users JOIN orders ON users.id
109. What is a non-equi join?
Non-equality condition, e.g., `SELECT * FROM users JOIN orders ON users.id !
110. What is a theta join?
General condition, e.g., `SELECT * FROM users JOIN orders ON users.age > orders.quantity;`.
111. What is a semi-join?
Uses `EXISTS` or `IN`, e.g., `SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);`.
112. What is an anti-join?
Uses `NOT EXISTS`, e.g., `SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id
113. What is a join with aggregation?
Groups joined data, e.g., `SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id
114. What is a join with sorting?
Uses `ORDER BY`, e.g., `SELECT u.name FROM users u JOIN orders o ON u.id
115. What is a join with filtering?
Uses `WHERE`, e.g., `SELECT u.name FROM users u JOIN orders o ON u.id
116. What is a join with limiting?
Uses `LIMIT`, e.g., `SELECT u.name FROM users u JOIN orders o ON u.id
117. What is a join with aliases?
Renames tables, e.g., `SELECT u.name, o.id FROM users AS u JOIN orders AS o ON u.id
118. What is a join with subquery in condition?
Uses subquery, e.g., `SELECT * FROM users u JOIN orders o ON u.id
119. What is a join with derived table?
Joins subquery, e.g., `SELECT u.name FROM users u JOIN (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) o ON u.id
120. What is a join with multiple keys?
Uses multiple columns, e.g., `SELECT * FROM users u JOIN orders o ON u.id
121. What is a join with NULL handling?
Uses `COALESCE`, e.g., `SELECT u.name, COALESCE(o.id, 0) FROM users u LEFT JOIN orders o ON u.id
122. What is a join with aggregation and filtering?
Combines `GROUP BY` and `HAVING`, e.g., `SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id
123. What is a join with sorting and limiting?
Uses `ORDER BY` and `LIMIT`, e.g., `SELECT u.name FROM users u JOIN orders o ON u.id
124. What is a join with complex conditions?
Uses multiple operators, e.g., `SELECT * FROM users u JOIN orders o ON u.id
125. What is a join with case statement?
Uses `CASE`, e.g., `SELECT u.name, CASE WHEN o.id IS NULL THEN \'No Order\' ELSE \'Ordered\' END FROM users u LEFT JOIN orders o ON u.id
126. What is a join with window function?
Uses window function, e.g., `SELECT u.name, ROW_NUMBER() OVER (PARTITION BY u.id) FROM users u JOIN orders o ON u.id
127. What is a join with CTE?
Uses CTE, e.g., `WITH cte AS (SELECT user_id FROM orders) SELECT u.name FROM users u JOIN cte ON u.id
128. What is a join with recursive CTE?
Handles hierarchy, e.g., `WITH RECURSIVE cte AS (...) SELECT * FROM employees e JOIN cte ON e.manager_id
129. What is a join with indexed columns?
Improves performance, e.g., `SELECT * FROM users u JOIN orders o ON u.id
130. What is a join with filtered subquery?
Uses subquery in join, e.g., `SELECT u.name FROM users u JOIN (SELECT user_id FROM orders WHERE date > \'2023-01-01\') o ON u.id
131. What is a join with aggregated subquery?
Joins aggregated data, e.g., `SELECT u.name FROM users u JOIN (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) o ON u.id
132. What is a join with multiple subqueries?
Uses multiple subqueries, e.g., `SELECT u.name FROM users u JOIN (SELECT user_id FROM orders WHERE price > 100) o1 ON u.id
133. What is a join with outer apply?
Applies subquery, e.g., `SELECT u.name, o.data FROM users u OUTER APPLY (SELECT COUNT(*) AS data FROM orders WHERE user_id
134. What is a join with cross apply?
Similar to `INNER JOIN`, e.g., `SELECT u.name, o.data FROM users u CROSS APPLY (SELECT COUNT(*) AS data FROM orders WHERE user_id
135. What is a join with partition?
Uses window function, e.g., `SELECT u.name, COUNT(o.id) OVER (PARTITION BY u.id) FROM users u JOIN orders o ON u.id
136. What is a join with ranking?
Uses `RANK`, e.g., `SELECT u.name, RANK() OVER (PARTITION BY u.city ORDER BY o.price) FROM users u JOIN orders o ON u.id
137. What is a join with conditional aggregation?
Uses `CASE`, e.g., `SELECT u.name, SUM(CASE WHEN o.price > 100 THEN 1 ELSE 0 END) FROM users u JOIN orders o ON u.id
138. What is a join with pivot?
Transforms rows to columns, e.g., `SELECT * FROM (SELECT u.name, o.category FROM users u JOIN orders o ON u.id
139. What is a join with unpivot?
Transforms columns to rows, e.g., `SELECT name, category, value FROM table UNPIVOT (value FOR category IN (col1, col2));`.
140. // Aggregations and Functions: What is an aggregate function?
Computes single value, e.g., `COUNT`, `SUM`, `AVG`.
141. What is the `COUNT` function?
Counts rows, e.g., `SELECT COUNT(*) FROM users;`.
142. What is the `SUM` function?
Adds values, e.g., `SELECT SUM(salary) FROM employees;`.
143. What is the `AVG` function?
Computes average, e.g., `SELECT AVG(salary) FROM employees;`.
144. What is the `MIN` function?
Finds minimum, e.g., `SELECT MIN(salary) FROM employees;`.
145. What is the `MAX` function?
Finds maximum, e.g., `SELECT MAX(salary) FROM employees;`.
146. How do you count distinct values?
Use `COUNT(DISTINCT)`, e.g., `SELECT COUNT(DISTINCT city) FROM users;`.
147. What is the `GROUP_CONCAT` function (MySQL)?
Concatenates group values, e.g., `SELECT GROUP_CONCAT(name) FROM users GROUP BY city;`.
148. What is the `STRING_AGG` function (SQL Server)?
Concatenates strings, e.g., `SELECT STRING_AGG(name, \',\') FROM users GROUP BY city;`.
149. What is the `CONCAT` function?
Joins strings, e.g., `SELECT CONCAT(first_name, \' \', last_name) FROM users;`.
150. What is the `SUBSTRING` function?
Extracts substring, e.g., `SELECT SUBSTRING(name, 1, 3) FROM users;`.
151. What is the `LENGTH` function?
Returns string length, e.g., `SELECT LENGTH(name) FROM users;`.
152. What is the `UPPER` function?
Converts to uppercase, e.g., `SELECT UPPER(name) FROM users;`.
153. What is the `LOWER` function?
Converts to lowercase, e.g., `SELECT LOWER(name) FROM users;`.
154. What is the `TRIM` function?
Removes spaces, e.g., `SELECT TRIM(name) FROM users;`.
155. What is the `REPLACE` function?
Replaces substring, e.g., `SELECT REPLACE(name, \'a\', \'b\') FROM users;`.
156. What is the `CAST` function?
Converts types, e.g., `SELECT CAST(age AS FLOAT) FROM users;`.
157. What is the `CONVERT` function?
Converts types, e.g., `SELECT CONVERT(VARCHAR, age) FROM users;`.
158. What is the `ROUND` function?
Rounds number, e.g., `SELECT ROUND(salary, 2) FROM employees;`.
159. What is the `FLOOR` function?
Rounds down, e.g., `SELECT FLOOR(salary) FROM employees;`.
160. What is the `CEIL` function?
Rounds up, e.g., `SELECT CEIL(salary) FROM employees;`.
161. What is the `ABS` function?
Returns absolute value, e.g., `SELECT ABS(-10);`.
162. What is the `POWER` function?
Raises to power, e.g., `SELECT POWER(2, 3);`.
163. What is the `SQRT` function?
Returns square root, e.g., `SELECT SQRT(16);`.
164. What is the `CURRENT_DATE` function?
Returns current date, e.g., `SELECT CURRENT_DATE;`.
165. What is the `CURRENT_TIME` function?
Returns current time, e.g., `SELECT CURRENT_TIME;`.
166. What is the `NOW` function?
Returns current timestamp, e.g., `SELECT NOW();`.
167. What is the `DATEDIFF` function?
Calculates date difference, e.g., `SELECT DATEDIFF(end_date, start_date) FROM projects;`.
168. What is the `DATEADD` function?
Adds to date, e.g., `SELECT DATEADD(day, 10, start_date) FROM projects;`.
169. What is the `EXTRACT` function?
Extracts date part, e.g., `SELECT EXTRACT(YEAR FROM birth_date) FROM users;`.
170. What is the `TO_DATE` function?
Converts string to date, e.g., `SELECT TO_DATE(\'2023-01-01\', \'YYYY-MM-DD\');`.
171. What is the `IFNULL` function (MySQL)?
Returns alternative for `NULL`, e.g., `SELECT IFNULL(name, \'Unknown\') FROM users;`.
172. What is the `NVL` function (Oracle)?
Replaces `NULL`, e.g., `SELECT NVL(name, \'Unknown\') FROM users;`.
173. What is the `CASE` statement?
Conditional logic, e.g., `SELECT CASE WHEN age > 18 THEN \'Adult\' ELSE \'Minor\' END FROM users;`.
174. What is a searched `CASE` statement?
Multiple conditions, e.g., `SELECT CASE WHEN age > 18 THEN \'Adult\' WHEN age > 12 THEN \'Teen\' ELSE \'Child\' END FROM users;`.
175. What is a simple `CASE` statement?
Matches value, e.g., `SELECT CASE status WHEN \'A\' THEN \'Active\' ELSE \'Inactive\' END FROM users;`.
176. What is the `GREATEST` function?
Returns maximum, e.g., `SELECT GREATEST(col1, col2) FROM table;`.
177. What is the `LEAST` function?
Returns minimum, e.g., `SELECT LEAST(col1, col2) FROM table;`.
178. What is the `SIGN` function?
Returns -1, 0, or 1, e.g., `SELECT SIGN(-10);`.
179. What is the `MOD` function?
Returns remainder, e.g., `SELECT MOD(10, 3);`.
180. What is the `TRUNC` function?
Truncates number, e.g., `SELECT TRUNC(3.14159, 2);`.
181. What is the `TO_CHAR` function?
Formats value, e.g., `SELECT TO_CHAR(birth_date, \'YYYY-MM-DD\') FROM users;`.
182. What is the `POSITION` function?
Finds substring position, e.g., `SELECT POSITION(\'a\' IN name) FROM users;`.
183. What is the `INSTR` function?
Finds substring, e.g., `SELECT INSTR(name, \'a\') FROM users;`.
184. What is the `REGEXP_LIKE` function?
Matches pattern, e.g., `SELECT * FROM users WHERE REGEXP_LIKE(name, \'^A\');`.
185. What is the `RANK` aggregate function?
Not aggregate; use window function, e.g., `RANK() OVER (ORDER BY salary);`.
186. What is the `COUNTIF` equivalent in SQL?
Use `SUM(CASE)`, e.g., `SELECT SUM(CASE WHEN age > 18 THEN 1 ELSE 0 END) FROM users;`.
187. What is the `LAG` function?
Not aggregate; window function, e.g., `LAG(salary) OVER (ORDER BY id);`.
188. What is the `LEAD` function?
Not aggregate; window function, e.g., `LEAD(salary) OVER (ORDER BY id);`.
189. What is the `STDDEV` function?
Standard deviation, e.g., `SELECT STDDEV(salary) FROM employees;`.
190. What is the `VARIANCE` function?
Variance of values, e.g., `SELECT VARIANCE(salary) FROM employees;`.
191. What is the `CORR` function?
Correlation coefficient, e.g., `SELECT CORR(salary, age) FROM employees;`.
192. What is the `COVAR_POP` function?
Population covariance, e.g., `SELECT COVAR_POP(salary, age) FROM employees;`.
193. What is the `CUME_DIST` function?
Not aggregate; window function, e.g., `CUME_DIST() OVER (ORDER BY salary);`.
194. What is the `PERCENTILE_CONT` function?
Continuous percentile, e.g., `SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;`.
195. What is the `PERCENTILE_DISC` function?
Discrete percentile, e.g., `SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;`.
196. What is the `MEDIAN` function?
Median value, e.g., `SELECT MEDIAN(salary) FROM employees;`.
197. What is the `MODE` function?
Most frequent value, e.g., `SELECT MODE() WITHIN GROUP (ORDER BY city) FROM users;`.
198. What is the `FIRST_VALUE` function?
Not aggregate; window function, e.g., `FIRST_VALUE(salary) OVER (PARTITION BY dept);`.
199. What is the `LAST_VALUE` function?
Not aggregate; window function, e.g., `LAST_VALUE(salary) OVER (PARTITION BY dept);`.
200. What is the `NTH_VALUE` function?
Not aggregate; window function, e.g., `NTH_VALUE(salary, 2) OVER (PARTITION BY dept);`.
201. What is the `NTILE` function?
Not aggregate; window function, e.g., `NTILE(4) OVER (ORDER BY salary);`.
202. What is the `LISTAGG` function (Oracle)?
Concatenates values, e.g., `SELECT LISTAGG(name, \',\') WITHIN GROUP (ORDER BY name) FROM users;`.
203. What is the `JSON_ARRAYAGG` function?
Aggregates as JSON array, e.g., `SELECT JSON_ARRAYAGG(name) FROM users;`.
204. What is the `JSON_OBJECTAGG` function?
Aggregates as JSON object, e.g., `SELECT JSON_OBJECTAGG(id, name) FROM users;`.
205. What is the `COLLECT` function (Oracle)?
Collects values into array, e.g., `SELECT COLLECT(name) FROM users GROUP BY city;`.
206. What is the `APPROX_COUNT_DISTINCT` function?
Approximate distinct count, e.g., `SELECT APPROX_COUNT_DISTINCT(city) FROM users;`.
207. What is the `APPROX_PERCENTILE` function?
Approximate percentile, e.g., `SELECT APPROX_PERCENTILE(0.5, salary) FROM employees;`.
208. What is the `BIT_AND` function?
Bitwise AND, e.g., `SELECT BIT_AND(flags) FROM table;`.
209. What is the `BIT_OR` function?
Bitwise OR, e.g., `SELECT BIT_OR(flags) FROM table;`.
210. What is the `BIT_XOR` function?
Bitwise XOR, e.g., `SELECT BIT_XOR(flags) FROM table;`.
211. // Database Design and Normalization: What is database normalization?
Organizes data to reduce redundancy, e.g., splitting into tables.
212. What is the first normal form (1NF)?
Atomic values, no repeating groups, e.g., one value per cell.
213. What is the second normal form (2NF)?
1NF + no partial dependency, e.g., non-key attributes depend on full key.
214. What is the third normal form (3NF)?
2NF + no transitive dependency, e.g., non-key attributes depend only on key.
215. What is Boyce-Codd Normal Form (BCNF)?
3NF + every determinant is candidate key, e.g., stricter 3NF.
216. What is denormalization?
Adds redundancy for performance, e.g., combining tables.
217. What is a candidate key?
Uniquely identifies rows, e.g., `id` or `email`.
218. What is a composite key?
Multiple columns as key, e.g., `(user_id, order_date)`.
219. What is a surrogate key?
Artificial key, e.g., auto-incremented `id`.
220. What is a natural key?
Existing data as key, e.g., `email`.
221. What is an index in SQL?
Improves query speed, e.g., `CREATE INDEX idx_name ON users(name);`.
222. What is a unique index?
Ensures unique values, e.g., `CREATE UNIQUE INDEX idx_email ON users(email);`.
223. What is a composite index?
Indexes multiple columns, e.g., `CREATE INDEX idx_comp ON users(city, age);`.
224. What is a covering index?
Contains all query columns, e.g., index on `(name, age)` for `SELECT name, age`.
225. What is a clustered index?
Determines physical order, e.g., one per table in SQL Server.
226. What is a non-clustered index?
Separate structure, e.g., multiple per table.
227. What is a bitmap index?
Uses bitmaps, e.g., for low-cardinality columns.
228. What is a full-text index?
Supports text search, e.g., `CREATE FULLTEXT INDEX idx_text ON users(description);`.
229. What is a spatial index?
Supports geometric data, e.g., for GIS applications.
230. What is a partitioned table?
Splits data, e.g., `CREATE TABLE orders PARTITION BY RANGE (order_date);`.
231. What is a partitioned index?
Indexes partitioned table, e.g., `CREATE INDEX idx ON orders(order_date) PARTITIONED;`.
232. What is a view in SQL?
Virtual table, e.g., `CREATE VIEW vw_users AS SELECT id, name FROM users;`.
233. What is a materialized view?
Stores query result, e.g., `CREATE MATERIALIZED VIEW mv_users AS SELECT * FROM users;`.
234. What is a temporary table?
Session-specific table, e.g., `CREATE TEMPORARY TABLE temp_users (id INT);`.
235. What is a global temporary table?
Schema-shared, e.g., `CREATE GLOBAL TEMPORARY TABLE temp_users (id INT);`.
236. What is a table variable?
Variable table, e.g., `DECLARE @temp TABLE (id INT);` (SQL Server).
237. What is a trigger in SQL?
Executes on event, e.g., `CREATE TRIGGER tr_ins AFTER INSERT ON users FOR EACH ROW ...;`.
238. What is an `INSTEAD OF` trigger?
Replaces action, e.g., `CREATE TRIGGER tr_ins INSTEAD OF INSERT ON vw_users ...;`.
239. What is a `BEFORE` trigger?
Executes before event, e.g., `CREATE TRIGGER tr_bef BEFORE INSERT ON users ...;`.
240. What is an `AFTER` trigger?
Executes after event, e.g., `CREATE TRIGGER tr_aft AFTER INSERT ON users ...;`.
241. What is a stored procedure?
Reusable SQL code, e.g., `CREATE PROCEDURE sp_get_users AS SELECT * FROM users;`.
242. What is a user-defined function (UDF)?
Custom function, e.g., `CREATE FUNCTION fn_age() RETURNS INT AS ...;`.
243. What is a scalar UDF?
Returns single value, e.g., `CREATE FUNCTION fn_total() RETURNS INT ...;`.
244. What is a table-valued UDF?
Returns table, e.g., `CREATE FUNCTION fn_users() RETURNS TABLE ...;`.
245. What is a database schema?
Organizes objects, e.g., `CREATE SCHEMA sales;`.
246. What is a star schema?
Data warehouse design, e.g., fact table with dimension tables.
247. What is a snowflake schema?
Normalized star schema, e.g., dimension tables normalized.
248. What is a fact table?
Stores measures, e.g., `sales` with `quantity`, `amount`.
249. What is a dimension table?
Stores attributes, e.g., `products` with `name`, `category`.
250. What is a one-to-one relationship?
Single row links, e.g., `users` to `profiles` via unique `id`.
251. What is a one-to-many relationship?
One row to multiple, e.g., `users` to `orders` via `user_id`.
252. What is a many-to-many relationship?
Multiple rows link, e.g., `students` to `courses` via junction table.
253. What is a junction table?
Links many-to-many, e.g., `student_courses` with `student_id`, `course_id`.
254. What is referential integrity?
Ensures valid foreign keys, e.g., `ON DELETE CASCADE`.
255. What is `ON DELETE CASCADE`?
Deletes child rows, e.g., `FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;`.
256. What is `ON UPDATE CASCADE`?
Updates child keys, e.g., `FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE;`.
257. What is `ON DELETE SET NULL`?
Sets foreign key to `NULL`, e.g., `FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;`.
258. What is a self-referencing table?
Foreign key to itself, e.g., `employees` with `manager_id`.
259. What is a composite primary key?
Multiple columns, e.g., `PRIMARY KEY (user_id, order_date)`.
260. What is a check constraint with multiple columns?
Enforces rule, e.g., `CHECK (start_date < end_date)`.
261. What is a domain constraint?
Restricts values, e.g., `CHECK (status IN (\'A\', \'I\'))`.
262. What is a functional dependency?
Column determines another, e.g., `id -> name`.
263. What is a transitive dependency?
Indirect dependency, e.g., `A -> B`, `B -> C` implies `A -> C`.
264. What is a database diagram?
Visualizes schema, e.g., ER diagram with tables, keys.
265. What is an ER diagram?
Entity-Relationship diagram, e.g., shows entities, relationships.
266. What is a weak entity?
Depends on another, e.g., `order_items` relies on `orders`.
267. What is a strong entity?
Independent, e.g., `users` with unique `id`.
268. What is cardinality in relationships?
Number of instances, e.g., one-to-many.
269. What is a database constraint?
Enforces rules, e.g., `PRIMARY KEY`, `FOREIGN KEY`.
270. What is a database integrity?
Ensures data accuracy, e.g., via constraints.
271. What is a database catalog?
Stores metadata, e.g., table definitions.
272. What is a database instance?
Running database, e.g., specific MySQL server.
273. What is a database system?
DBMS software, e.g., PostgreSQL, Oracle.
274. What is a relational model?
Based on tables, e.g., uses keys for relationships.
275. What is a hierarchical model?
Tree structure, e.g., parent-child relationships.
276. What is a network model?
Graph structure, e.g., many-to-many links.
277. // Advanced Queries: What is a window function?
Computes over window, e.g., `ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary);`.
278. What is the `ROW_NUMBER` function?
Assigns unique number, e.g., `SELECT ROW_NUMBER() OVER (ORDER BY id) FROM users;`.
279. What is the `RANK` function?
Assigns rank with gaps, e.g., `SELECT RANK() OVER (ORDER BY salary) FROM employees;`.
280. What is the `DENSE_RANK` function?
Assigns rank without gaps, e.g., `SELECT DENSE_RANK() OVER (ORDER BY salary) FROM employees;`.
281. What is the `NTILE` function?
Divides into buckets, e.g., `SELECT NTILE(4) OVER (ORDER BY salary) FROM employees;`.
282. What is the `LAG` function?
Accesses previous row, e.g., `SELECT LAG(salary) OVER (ORDER BY id) FROM employees;`.
283. What is the `LEAD` function?
Accesses next row, e.g., `SELECT LEAD(salary) OVER (ORDER BY id) FROM employees;`.
284. What is the `FIRST_VALUE` function?
First value in window, e.g., `SELECT FIRST_VALUE(salary) OVER (PARTITION BY dept) FROM employees;`.
285. What is the `LAST_VALUE` function?
Last value in window, e.g., `SELECT LAST_VALUE(salary) OVER (PARTITION BY dept) FROM employees;`.
286. What is the `CUME_DIST` function?
Cumulative distribution, e.g., `SELECT CUME_DIST() OVER (ORDER BY salary) FROM employees;`.
287. What is the `PERCENT_RANK` function?
Relative rank, e.g., `SELECT PERCENT_RANK() OVER (ORDER BY salary) FROM employees;`.
288. What is the `PARTITION BY` clause?
Groups window, e.g., `OVER (PARTITION BY dept ORDER BY salary);`.
289. What is the `ORDER BY` clause in window functions?
Sorts window, e.g., `OVER (PARTITION BY dept ORDER BY salary);`.
290. What is a frame specification in window functions?
Defines rows, e.g., `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.
291. What is `ROWS UNBOUNDED PRECEDING`?
All rows before current, e.g., `SUM(salary) OVER (ROWS UNBOUNDED PRECEDING);`.
292. What is `RANGE BETWEEN` in window functions?
Uses value range, e.g., `SUM(salary) OVER (RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING);`.
293. What is a running total with window functions?
Cumulative sum, e.g., `SELECT SUM(salary) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) FROM employees;`.
294. What is a moving average with window functions?
Average over window, e.g., `SELECT AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM employees;`.
295. What is a Common Table Expression (CTE)?
Temporary result, e.g., `WITH cte AS (SELECT * FROM users) SELECT * FROM cte;`.
296. What is a recursive CTE?
Self-referencing, e.g., `WITH RECURSIVE cte AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n < 5) SELECT * FROM cte;`.
297. What is a hierarchical query?
Handles tree data, e.g., `SELECT * FROM employees CONNECT BY PRIOR id
298. What is a pivot query?
Rows to columns, e.g., `SELECT * FROM (SELECT name, category FROM users) PIVOT (COUNT(*) FOR category IN (\'A\', \'B\'));`.
299. What is an unpivot query?
Columns to rows, e.g., `SELECT name, category, value FROM table UNPIVOT (value FOR category IN (col1, col2));`.
300. What is a rollup query?
Subtotals, e.g., `SELECT dept, city, SUM(salary) FROM employees GROUP BY ROLLUP(dept, city);`.
301. What is a cube query?
All combinations, e.g., `SELECT dept, city, SUM(salary) FROM employees GROUP BY CUBE(dept, city);`.
302. What is a grouping sets query?
Custom aggregations, e.g., `SELECT dept, city, SUM(salary) FROM employees GROUP BY GROUPING SETS ((dept), (city), ());`.
303. What is the `GROUPING` function?
Identifies subtotals, e.g., `SELECT dept, GROUPING(dept) FROM employees GROUP BY ROLLUP(dept);`.
304. What is a lateral subquery?
Correlated in `FROM`, e.g., `SELECT u.name, l.cnt FROM users u, LATERAL (SELECT COUNT(*) AS cnt FROM orders o WHERE o.user_id
305. What is a cross apply in SQL?
Correlated subquery, e.g., `SELECT u.name, o.cnt FROM users u CROSS APPLY (SELECT COUNT(*) AS cnt FROM orders WHERE user_id
306. What is an outer apply?
Left-joined subquery, e.g., `SELECT u.name, o.cnt FROM users u OUTER APPLY (SELECT COUNT(*) AS cnt FROM orders WHERE user_id
307. What is a top-N query with ties?
Includes tied ranks, e.g., `SELECT * FROM (SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) WHERE rnk <
308. What is a gap analysis query?
Finds missing ranges, e.g., `SELECT id + 1 AS start, LEAD(id) - 1 AS end FROM users WHERE LEAD(id) - id > 1;`.
309. What is an island query?
Groups consecutive rows, e.g., `SELECT MIN(id), MAX(id) FROM (SELECT id, id - ROW_NUMBER() OVER (ORDER BY id) AS grp FROM users) GROUP BY grp;`.
310. What is a deduplication query?
Removes duplicates, e.g., `SELECT DISTINCT ON (name) * FROM users;` (PostgreSQL).
311. What is a query to find Nth highest value?
Uses `DENSE_RANK`, e.g., `SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) WHERE rnk
312. What is a query to find duplicates?
Uses `GROUP BY`, e.g., `SELECT name, COUNT(*) FROM users GROUP BY name HAVING COUNT(*) > 1;`.
313. What is a query to rank within groups?
Uses `PARTITION BY`, e.g., `SELECT name, RANK() OVER (PARTITION BY dept ORDER BY salary) FROM employees;`.
314. What is a query to compare rows?
Uses `LAG`, e.g., `SELECT name, salary, LAG(salary) OVER (ORDER BY id) AS prev FROM employees;`.
315. What is a query to calculate running total?
Uses `SUM`, e.g., `SELECT name, SUM(salary) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) FROM employees;`.
316. What is a query to calculate percentage?
Uses window function, e.g., `SELECT name, salary / SUM(salary) OVER () * 100 AS pct FROM employees;`.
317. What is a query to find top N per group?
Uses `RANK`, e.g., `SELECT * FROM (SELECT name, dept, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees) WHERE rnk <
318. What is a query to handle hierarchical data?
Uses recursive CTE, e.g., `WITH RECURSIVE cte AS (SELECT id, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id FROM employees e JOIN cte c ON e.manager_id
319. What is a query to pivot dynamically?
Uses dynamic SQL, e.g., generate `PIVOT` columns based on data.
320. What is a query to unpivot dynamically?
Uses dynamic SQL, e.g., generate `UNPIVOT` columns based on data.
321. What is a query to find missing dates?
Uses sequence, e.g., `SELECT d.date FROM generate_series(\'2023-01-01\'::date, \'2023-12-31\'::date, \'1 day\') d LEFT JOIN orders o ON d.date
322. What is a query to calculate session duration?
Uses `LEAD`, e.g., `SELECT user_id, start_time, LEAD(end_time) OVER (PARTITION BY user_id ORDER BY start_time) AS end_time FROM sessions;`.
323. What is a query to find overlapping ranges?
Uses conditions, e.g., `SELECT * FROM ranges r1 JOIN ranges r2 ON r1.id !
324. What is a query to aggregate by time intervals?
Uses `DATE_TRUNC`, e.g., `SELECT DATE_TRUNC(\'hour\', order_date), COUNT(*) FROM orders GROUP BY DATE_TRUNC(\'hour\', order_date);`.
325. What is a query to calculate cumulative count?
Uses `COUNT`, e.g., `SELECT order_date, COUNT(*) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) FROM orders;`.
326. What is a query to find first occurrence?
Uses `MIN`, e.g., `SELECT user_id, MIN(order_date) FROM orders GROUP BY user_id;`.
327. What is a query to find last occurrence?
Uses `MAX`, e.g., `SELECT user_id, MAX(order_date) FROM orders GROUP BY user_id;`.
328. What is a query to find consecutive days?
Uses `ROW_NUMBER`, e.g., `SELECT user_id, order_date FROM (SELECT user_id, order_date, order_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS grp FROM orders) GROUP BY user_id, grp HAVING COUNT(*) >
329. What is a query to find median?
Uses `PERCENTILE_CONT`, e.g., `SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;`.
330. What is a query to find mode?
Uses `MODE`, e.g., `SELECT MODE() WITHIN GROUP (ORDER BY city) FROM users;`.
331. What is a query to handle JSON data?
Uses JSON functions, e.g., `SELECT JSON_EXTRACT(data, \'$.name\') FROM table;`.
332. What is a query to handle XML data?
Uses XML functions, e.g., `SELECT EXTRACTVALUE(xml_col, \'//name\') FROM table;`.
333. What is a query to calculate growth rate?
Uses `LAG`, e.g., `SELECT order_date, (sales - LAG(sales) OVER (ORDER BY order_date)) / LAG(sales) OVER (ORDER BY order_date) * 100 AS growth FROM sales;`.
334. What is a query to find outliers?
Uses standard deviation, e.g., `SELECT * FROM employees WHERE ABS(salary - (SELECT AVG(salary) FROM employees)) > 2 * (SELECT STDDEV(salary) FROM employees);`.
335. What is a query to partition data manually?
Uses `NTILE`, e.g., `SELECT name, NTILE(5) OVER (ORDER BY salary) AS partition FROM employees;`.
336. // Performance and Optimization: What is query optimization?
Improves query efficiency, e.g., using indexes, rewriting queries.
337. What is an execution plan?
Shows query steps, e.g., `EXPLAIN SELECT * FROM users;`.
338. What is a query cost?
Estimated resource usage, e.g., from execution plan.
339. What is a table scan?
Reads all rows, e.g., no index used.
340. What is an index scan?
Uses index, e.g., `SELECT * FROM users WHERE id
341. What is a full table scan?
Reads entire table, e.g., `SELECT * FROM users WHERE name LIKE \'%a%\';`.
342. What is a seek operation?
Directly accesses rows, e.g., `SELECT * FROM users WHERE id
343. What is a clustered index scan?
Reads clustered index, e.g., primary key in SQL Server.
344. What is a non-clustered index scan?
Reads non-clustered index, e.g., secondary index.
345. What is a covering index?
Contains all columns, e.g., index on `(name, age)` for `SELECT name, age`.
346. How do you optimize a query with `WHERE`?
Use indexes, e.g., `CREATE INDEX idx_age ON users(age);` for `WHERE age > 18`.
347. How do you optimize a query with `JOIN`?
Index join columns, e.g., `CREATE INDEX idx_user_id ON orders(user_id);`.
348. How do you optimize a query with `GROUP BY`?
Index grouped columns, e.g., `CREATE INDEX idx_city ON users(city);`.
349. How do you optimize a query with `ORDER BY`?
Index sorted columns, e.g., `CREATE INDEX idx_name ON users(name);`.
350. What is a composite index for optimization?
351. What is index selectivity?
Uniqueness of values, e.g., high for `id`, low for `gender`.
352. What is a low-cardinality column?
Few distinct values, e.g., `status` with \'A\', \'I\'.
353. What is a high-cardinality column?
Many distinct values, e.g., `email`.
354. What is a filtered index?
Indexes subset, e.g., `CREATE INDEX idx_active ON users(name) WHERE status
355. What is a partial index?
Indexes condition, e.g., `CREATE INDEX idx_active ON users(name) WHERE status
356. What is index fragmentation?
Scattered index data, e.g., reduces performance.
357. How do you rebuild an index?
Use `REBUILD`, e.g., `ALTER INDEX idx_name ON users REBUILD;`.
358. How do you reorganize an index?
Use `REORGANIZE`, e.g., `ALTER INDEX idx_name ON users REORGANIZE;`.
359. What is a statistics object?
Data distribution, e.g., used by optimizer.
360. How do you update statistics?
Use `UPDATE STATISTICS`, e.g., `UPDATE STATISTICS users;`.
361. What is a histogram in statistics?
Shows value distribution, e.g., for query planning.
362. What is a query hint?
Guides optimizer, e.g., `SELECT * FROM users WITH (INDEX(idx_name));`.
363. What is a table hint?
Specifies table behavior, e.g., `SELECT * FROM users WITH (NOLOCK);`.
364. What is a join hint?
Forces join type, e.g., `SELECT * FROM users INNER LOOP JOIN orders;`.
365. What is a parameterized query?
Uses placeholders, e.g., `SELECT * FROM users WHERE id
366. What is query caching?
Stores results, e.g., improves repeated query performance.
367. What is a prepared statement?
Precompiled query, e.g., `PREPARE stmt AS SELECT * FROM users WHERE id
368. What is a stored procedure for optimization?
Reduces network calls, e.g., `CREATE PROCEDURE sp_get_users AS SELECT * FROM users;`.
369. What is partitioning for optimization?
Splits table, e.g., `CREATE TABLE orders PARTITION BY RANGE (order_date);`.
370. What is sharding?
Distributes data, e.g., across multiple servers.
371. What is a materialized view for optimization?
Stores results, e.g., `CREATE MATERIALIZED VIEW mv_users AS SELECT * FROM users;`.
372. What is a temporary table for optimization?
Reduces contention, e.g., `CREATE TEMPORARY TABLE temp_users (id INT);`.
373. What is a common table expression for optimization?
Simplifies queries, e.g., `WITH cte AS (SELECT * FROM users) SELECT * FROM cte;`.
374. What is a covering query?
Uses covering index, e.g., `SELECT name FROM users` with index on `name`.
375. What is a query rewrite?
Simplifies query, e.g., replace subquery with join.
376. What is a correlated subquery optimization?
Replace with join, e.g., `SELECT * FROM users u JOIN orders o ON u.id
377. What is a query to avoid full table scan?
Add index, e.g., `CREATE INDEX idx_age ON users(age);` for `WHERE age > 18`.
378. What is a query to reduce I/O?
Select fewer columns, e.g., `SELECT id, name FROM users` instead of `SELECT *`.
379. What is a query to minimize locks?
Use `READ UNCOMMITTED`, e.g., `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`.
380. What is a query to avoid sorting?
Use index, e.g., `CREATE INDEX idx_name ON users(name);` for `ORDER BY name`.
381. What is a query to optimize aggregation?
Index grouped columns, e.g., `CREATE INDEX idx_dept ON employees(dept);` for `GROUP BY dept`.
382. What is a query to optimize window functions?
Limit rows, e.g., `SELECT ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) FROM employees WHERE dept
383. What is a query to optimize joins?
Index foreign keys, e.g., `CREATE INDEX idx_user_id ON orders(user_id);`.
384. What is a query to optimize subqueries?
Use CTE or join, e.g., `WITH cte AS (SELECT user_id FROM orders) SELECT * FROM users JOIN cte ON users.id
385. What is a query to optimize large datasets?
Partition table, e.g., `CREATE TABLE orders PARTITION BY RANGE (order_date);`.
386. What is a query to optimize recursive CTEs?
Limit recursion, e.g., `WITH RECURSIVE cte AS (... WHERE depth < 10)`.
387. What is a query to optimize pivoting?
Use conditional aggregation, e.g., `SELECT name, SUM(CASE WHEN category
388. What is a query to optimize XML/JSON?
Use indexes, e.g., `CREATE INDEX idx_json ON table USING GIN (data);`.
389. What is a query to optimize text search?
Use full-text index, e.g., `CREATE INDEX idx_text ON users USING GIN (description);`.
390. What is a query to optimize geospatial data?
Use spatial index, e.g., `CREATE INDEX idx_geo ON locations USING GIST (point);`.
391. What is a query to optimize time-series data?
Partition by time, e.g., `CREATE TABLE events PARTITION BY RANGE (event_time);`.
392. What is a query to optimize high-concurrency?
Use connection pooling, e.g., configure DBMS for multiple connections.
393. What is a query to optimize read-heavy systems?
Use read replicas, e.g., configure database replication.
394. What is a query to optimize write-heavy systems?
Batch inserts, e.g., `INSERT INTO users (id, name) VALUES (...), (...);`.
395. What is a query to optimize locking?
Use row-level locks, e.g., `SELECT * FROM users FOR UPDATE;`.
396. What is a query to optimize transaction performance?
Minimize transaction scope, e.g., `BEGIN; INSERT ...; COMMIT;`.
397. What is a query to optimize index maintenance?
Rebuild periodically, e.g., `ALTER INDEX idx_name REBUILD;`.
398. What is a query to optimize statistics?
Update frequently, e.g., `ANALYZE users;`.
399. What is a query to optimize temporary tables?
Use for intermediate results, e.g., `CREATE TEMPORARY TABLE temp AS SELECT * FROM users;`.
400. What is a query to optimize materialized views?
Refresh incrementally, e.g., `REFRESH MATERIALIZED VIEW CONCURRENTLY mv_users;`.
401. What is a query to optimize database size?
Vacuum database, e.g., `VACUUM FULL users;` (PostgreSQL).
402. What is a query to optimize query cache?
Enable caching, e.g., configure MySQL query cache.
403. // Transactions and Security: What is a transaction in SQL?
Unit of work, e.g., `BEGIN; INSERT ...; COMMIT;`.
404. What are ACID properties?
Atomicity, Consistency, Isolation, Durability, e.g., ensures reliable transactions.
405. What is atomicity in ACID?
All or nothing, e.g., `INSERT` succeeds or rolls back.
406. What is consistency in ACID?
Valid state, e.g., constraints enforced.
407. What is isolation in ACID?
Transaction independence, e.g., no interference.
408. What is durability in ACID?
Persists after commit, e.g., data saved to disk.
409. What is a transaction isolation level?
Controls visibility, e.g., `READ COMMITTED`, `SERIALIZABLE`.
410. What is `READ UNCOMMITTED` isolation?
Sees uncommitted changes, e.g., risks dirty reads.
Share this protal to share friends and complete unlimited tests here. You can also make friends on our protal also start mutual competition tests with your firends easily.