Chapter 27 — Comprehensive Practice System: 300 Progressive Problems

All exercises in this practice system are designed to be executed against the master sql_mastery database schema.

To practice alongside these questions, ensure you have initialized your local database by running the script [sql_mastery_schema.sql](file:///c:/antigravity/master_sql_guide/sql_mastery_schema.sql).

NOTE

Solutions and query explanations are intentionally omitted from this chapter to enable self-testing. For verified solutions and step-by-step breakdowns, refer to [Chapter 28 — The Master Answer Key](file:///c:/antigravity/master_sql_guide/28_answer_key.md).

---

Section 1: Database & Table Management (DDL, Types & Constraints)

Beginner Questions (1–10)

1. Write a SQL statement to display all tables inside the active sql_mastery database.
2. Write a statement to inspect the schema definition, datatypes, and nullability of the departments table.
3. Write a command to create a sandbox table named test_logs with a single integer column id.
4. Write a statement to drop the test_logs table only if it exists.
5. Write a query to create a table coupons with code VARCHAR(20) and discount_pct DECIMAL(4,2) that defaults to 0.05.
6. Write an ALTER TABLE statement adding a column expiry_date DATE NOT NULL to the coupons table.
7. Write an ALTER TABLE statement modifying code in coupons to VARCHAR(30) NOT NULL.
8. Write an ALTER TABLE statement dropping the expiry_date column from coupons.
9. Write a statement to rename the table coupons to promotional_codes.
10. Drop the promotional_codes table cleanly.

Intermediate Questions (11–20)

11. Write a statement creating a table project_teams with an auto-increment primary key team_id, a unique team_name VARCHAR(50), and a check constraint ensuring budget >= 1000.00.
12. Write an ALTER TABLE statement adding a foreign key to project_teams named fk_team_lead pointing team_lead_id to employees(employee_id) with ON DELETE SET NULL.
13. Write a command to create an exact structural clone of the products table named products_backup without copying any rows.
14. Write a query creating a table high_earners containing all columns and rows from employees where salary > 120000.00 using CREATE TABLE ... AS SELECT.
15. Write an ALTER TABLE statement adding a column priority_level ENUM('Low', 'Medium', 'High') DEFAULT 'Medium' positioned immediately after department_name in departments.
16. Remove the priority_level column from departments to restore the schema.
17. Write a statement to truncate the high_earners table.
18. Drop the high_earners and products_backup tables.
19. Write a statement to view the complete DDL CREATE TABLE script generated by MySQL for the order_items table.
20. Write an ALTER TABLE statement adding a composite unique constraint named uq_dept_loc on departments(department_name, location).

Advanced Questions (21–25)

21. Write a script creating a temporary table temp_sales_summary that aggregates total units sold per product from order_items. Explain when MySQL purges this table.
22. Write an ALTER TABLE statement that disables foreign key checks temporarily, adds a foreign key constraint, and re-enables foreign key checks.
23. Write a statement that attempts to add a check constraint salary > 200000 to employees. Explain why MySQL rejects the DDL if existing rows violate the condition.
24. Construct an ALTER TABLE statement that modifies customers.phone to VARCHAR(25) using MySQL 8.0 ALGORITHM=INPLACE, LOCK=NONE.
25. Demonstrate how to drop a primary key that has an active AUTO_INCREMENT attribute on an InnoDB table.

Challenge Questions (26–30)

26. Schema Migration Challenge: Write a multi-step migration script that splits customers.first_name and customers.last_name into a single full_name VARCHAR(100) column without losing any existing customer data, and drops the old columns.
27. Zero-Downtime Column Addition: Write the SQL pattern to add a non-null column with a default value to a table with 10 million rows without taking an exclusive table lock.
28. Foreign Key Integrity Audit: Write a query that queries MySQL's information_schema.table_constraints and referential_constraints to list all foreign keys and their corresponding ON DELETE rules in sql_mastery.
29. Storage Footprint Analysis: Write a query against information_schema.tables calculating the total data size and index size in Megabytes for each table in sql_mastery.
30. Composite Key Restructuring: Convert a table with an existing composite primary key (order_id, product_id) into a table with a surrogate primary key item_id INT AUTO_INCREMENT while preserving the composite uniqueness.

---

Section 2: Data Querying, Filtering & Sorting (SELECT, WHERE, ORDER BY, LIMIT)

Beginner Questions (31–40)

31. Retrieve all columns for all rows from the departments table.
32. Retrieve only first_name, last_name, and email from customers.
33. Find all products with a unit_price strictly greater than $500.00.
34. Find all customers who reside in the country 'USA'.
35. Find all orders that have a status of 'Delivered'.
36. Retrieve all products with a unit_price between $100.00 and $400.00 inclusive.
37. Find all customers who do not have a recorded state (state IS NULL).
38. Retrieve all employees sorted by hire_date ascending (earliest hires first).
39. Retrieve the top 3 highest-earning employees in the company.
40. Retrieve distinct countries from the customers table without duplicates.

Intermediate Questions (41–50)

41. Find all customers whose email address ends with '@gmail.com'.
42. Retrieve all products in category_id 1 or 2 that have stock_quantity greater than 20.
43. Find all orders placed between '2023-08-01' and '2023-08-15' where total_amount exceeds $300.00.
44. Find all employees whose salary is greater than $90,000 and whose manager_id is NOT NULL.
45. Retrieve the 5 least expensive products that are currently active (is_active = TRUE).
46. Find all customers whose first_name starts with 'M' or 'S' and whose country is NOT 'USA'.
47. Retrieve all orders with status of either 'Processing' or 'Pending', ordered by order_date descending.
48. Implement UI pagination: Retrieve rows 4 through 6 of the products table ordered by unit_price descending.
49. Find all products where the product name contains the word 'Air' or 'Pro'.
50. Retrieve all customers sorted such that customers in 'USA' appear first, with all other countries sorted alphabetically below.

Advanced Questions (51–55)

51. Write a query against customers that sorts by loyalty_points descending, placing any customer with NULL loyalty points at the very bottom.
52. Write a query retrieving all orders where shipping_fee represents more than 2% of the total_amount.
53. Construct a query that searches the products table for products whose name has exactly 15 characters.
54. Using Keyset / Cursor Pagination, write a query to fetch the next page of 3 orders following order_id = 1005 without using the OFFSET keyword.
55. Retrieve all employees who were hired in an odd-numbered month.

Challenge Questions (56–60)

56. Deterministic Pagination Challenge: Explain why ORDER BY order_date LIMIT 5 OFFSET 5 can return duplicate rows across consecutive pages if order_date contains ties. Write the corrected query.
57. Complex Pattern Matching: Write a regular expression query (REGEXP) against customers finding all phone numbers that do not strictly adhere to the pattern 555-XXXX.
58. Dynamic Threshold Filtering: Write a query retrieving products where the current inventory value (stock_quantity * unit_price) exceeds the average inventory value of all products.
59. Multi-Condition Search Filter: Write a query that models an e-commerce search bar: given search keyword 'Pro', filter across product_name, category_name, and supplier_name simultaneously.
60. Safe Range Scanning: Write a query filtering orders by order_date that is guaranteed to be SARGable and index-seekable for the entire year of 2023.

---

Section 3: Built-in SQL Functions (String, Date, Math & Flow)

Beginner Questions (61–70)

61. Concatenate first_name and last_name into a single column full_name for all employees.
62. Convert all supplier company names to uppercase.
63. Display the length in characters of every product name.
64. Round every employee's salary to the nearest thousand.
65. Return the current date and time using MySQL functions.
66. Extract the calendar year from the order_date of all orders.
67. Find the square root of 144 using a mathematical function.
68. Replace any occurrence of 'USA' with 'United States' in the customers table projection.
69. Display each customer's phone number, displaying 'No Phone Provided' if the phone is NULL using IFNULL().
70. Calculate the absolute value of $-45.50$.

Intermediate Questions (71–80)

71. Calculate the number of days elapsed between each customer's registered_at date and today.
72. Format all order_date values in orders into the human-readable format 'Month Day, Year' (e.g. 'August 01, 2023').
73. Extract the first 3 characters of every customer's country.
74. Extract the username portion (everything before the @ symbol) from customers.email.
75. Write a query calculating a 15% promotional discount on all products, truncated (not rounded) to 2 decimal places.
76. Calculate each employee's tenure in complete elapsed months using TIMESTAMPDIFF().
77. Use CONCAT_WS() to format each customer's complete address: city, state, country. Ensure missing states do not produce double commas.
78. Add a 30-day payment grace period to all order dates using DATE_ADD().
79. Use an IF() function to flag each product as 'Expensive' if price > $500, or 'Affordable' otherwise.
80. Calculate the remainder when dividing order total amounts by 10 using MOD().

Advanced Questions (81–85)

81. Use a searched CASE expression to classify customers into tiers: 'Diamond' (points $\ge 700$), 'Platinum' (points $\ge 400$), 'Silver' (points $\ge 100$), and 'Basic' otherwise.
82. Calculate the compound annual growth rate (CAGR) formula in SQL using POWER().
83. Write a query that dynamically replaces all vowels in customer first names with asterisks (*).
84. Write a query to find the last day of the month for every order in orders using LAST_DAY().
85. Write a query that computes the total payroll, average salary, minimum salary, and maximum salary for employees, ensuring NULLs are converted to 0.

Challenge Questions (86–90)

86. Working Day Calculation Challenge: Write a SQL expression that calculates the number of business days (excluding Saturdays and Sundays) between an order's order_date and today.
87. Email Obfuscation Challenge: Write a query that masks customer emails for privacy, showing only the first 2 characters and the domain (e.g., transforming emily.watson@gmail.com into em*****@gmail.com).
88. Safe Division Matrix: Write a query that calculates the ratio of loyalty_points to order counts for each customer, protecting against division by zero using NULLIF.
89. Fiscal Quarter Determination: Write an expression that maps each order date to an enterprise fiscal quarter, where Fiscal Year begins on November 1st.
90. String Parsing Challenge: Given a comma-delimited string 'alpha,beta,gamma', extract the 2nd element ('beta') using pure SQL string functions without procedural loops.

---

Section 4: Grouping & Aggregation (GROUP BY & HAVING)

Beginner Questions (91–100)

91. Count the total number of employees in the company.
92. Find the total sum of all order amounts in orders.
93. Find the average unit price across all products.
94. Count how many unique countries are represented in customers.
95. Find the maximum salary and minimum salary in the employees table.
96. Count the number of products belonging to each category_id.
97. Calculate the total payroll expenditure for each department_id.
98. Count the number of orders placed by each customer_id.
99. Calculate the total quantity of items sold in order_items.
100. Count the number of customers residing in each country.

Intermediate Questions (101–110)

101. Find all department_id groups where the average employee salary exceeds $100,000.
102. Find all customers who have placed 2 or more orders.
103. Calculate the total revenue generated by each status in the orders table.
104. List each category_id that contains more than 1 active product.
105. Use GROUP_CONCAT to produce a comma-separated list of employee first names for each department.
106. Group products by supplier_id and display the minimum price, maximum price, and price range (max - min).
107. Find all order dates where more than 1 order was placed on the same day.
108. Calculate the average discount given per order in order_items, filtering to display only orders where average discount > 0.
109. Group customers by country and state, counting how many customers reside in each combination.
110. Find the total amount of successful payments for each payment_method.

Advanced Questions (111–115)

111. Write a query grouping employees by department_id with WITH ROLLUP, calculating headcount and total salary, with a grand total row labeled 'Company Total'.
112. Explain the cause of ERROR 1055: only_full_group_by when selecting an unaggregated column in GROUP BY. Write a query demonstrating the error and fix it.
113. Find the department that has the highest average salary using GROUP BY, ORDER BY, and LIMIT 1.
114. Find all customers whose cumulative order spending exceeds the company-wide average order value.
115. Group orders by calendar month and calculate the month's total sales, shipping costs, and order count.

Challenge Questions (116–120)

116. Multi-Dimensional ROLLUP Analysis: Group products by category_id and supplier_id using WITH ROLLUP, using the GROUPING() function to identify subtotal vs grand total rows.
117. Conditional Aggregation (Pivot): Write a single query that pivots the orders table to display total revenue in 5 separate columns: Pending, Processing, Shipped, Delivered, and Cancelled using SUM(CASE ...).
118. Customer Retention Metric: Group customers by the year of their registration date and calculate how many of those customers have placed an order in 2023.
119. Pareto Principle Analysis (80/20 Rule): Write a query that identifies the top 20% of customers who generate 80% of total revenue.
120. Aggregating Pre-Calculated Line Items: Calculate the total net revenue for every order from order_items, accounting for discounts, and filter for orders where net revenue exceeds $1,000 using HAVING.

---

Section 5: Relational JOINs & Set Operations

Beginner Questions (121–130)

121. Perform an INNER JOIN between employees and departments to display each employee's name and department name.
122. Perform a LEFT JOIN between departments and employees to show all departments, including those with no employees.
123. Join orders and customers to show order IDs and customer names.
124. Join products and categories to show each product's title and category description.
125. Join products and suppliers to display product names and supplier contact emails.
126. Use UNION to combine all distinct cities from customers and suppliers.
127. Use UNION ALL to combine all cities from customers and suppliers.
128. Join orders and order_items to list all item IDs belonging to each order.
129. Join orders and payments to display order IDs and payment transaction references.
130. Perform a CROSS JOIN between categories and departments.

Intermediate Questions (131–140)

131. Write an Anti-Join to find all customers who have never placed an order.
132. Write an Anti-Join to find all products that have never been purchased in order_items.
133. Perform a Self JOIN on employees to display each employee's name alongside their direct manager's name.
134. Write a 3-table join connecting customers, orders, and order_items to list all products purchased by customer Emily Watson.
135. Write a query joining products, categories, and suppliers to list all products belonging to 'Electronics' supplied by suppliers based in 'Japan'.
136. Use UNION ALL to combine customer phone numbers and employee phone numbers, tagging each row with an 'Entity_Type' column.
137. Perform a Self JOIN to find all pairs of employees who work in the same department.
138. Join orders, order_items, and products to calculate the total retail value of items inside Order 1001.
139. Write a query to find all departments that currently employ zero staff.
140. Emulate a FULL OUTER JOIN between departments and employees using UNION.

Advanced Questions (141–145)

141. Write a 5-table join connecting customers, orders, order_items, products, and categories to calculate total revenue generated per customer per category.
142. Write a Non-Equi Join that finds all products whose unit_price is strictly greater than the average salary of employees in department 1.
143. Write a query using LEFT JOIN where the filter on the right table is placed inside the ON clause, and explain how the result differs from placing it in WHERE.
144. Find all customers who have purchased both Product 1 AND Product 3 using relational joins.
145. Combine the top 2 highest-paid employees and top 2 lowest-paid employees using UNION ALL with individual parenthesized subqueries.

Challenge Questions (146–150)

146. Full Outer Join Emulation with Nulls: Construct a complete FULL OUTER JOIN between customers and orders that accurately returns customers without orders AND orders without valid customers (if orphaned).
147. Self Join Hierarchy Tree: Write a query displaying employees, their managers, and their manager's managers (2 levels of management hierarchy) using a 3-way Self Join.
148. Relational Division Challenge: Find all customers who have purchased every single product in Category 1 (Electronics).
149. Basket Analysis (Co-Purchased Products): Write a self-join query on order_items to identify pairs of products that are most frequently purchased together in the same order.
150. Consolidated Financial Audit: Write a compound query utilizing UNION ALL to merge completed order revenue, refund deductions, and shipping costs into a chronological general ledger.

---

Section 6: Nested Queries & Common Table Expressions (CTEs)

Beginner Questions (151–160)

151. Write a scalar subquery to find all employees earning more than the company-wide average salary.
152. Write a subquery using IN to find all products that belong to categories containing the word 'Appliances'.
153. Write a subquery to find the customer who placed the single largest order by total_amount.
154. Find all products priced higher than the Quantum Pro 15 Laptop using a subquery.
155. Write a query using a derived table in the FROM clause that aliases product prices.
156. Find all customers who registered on the earliest registration date in the table.
157. Use a subquery to find all orders placed by customers residing in 'San Francisco'.
158. Write a basic CTE named ActiveProducts that selects active products, and query from it.
159. Find all employees who share a manager with employee David Kim using a subquery.
160. Use NOT IN with a subquery to find products that have never been ordered.

Intermediate Questions (161–170)

161. Write a Correlated Subquery that finds all employees earning more than the average salary of their own department.
162. Write a query using EXISTS to find all suppliers that supply at least one active product.
163. Write a query using NOT EXISTS to find all customers who have never placed an order.
164. Write a CTE that calculates total spending per customer, and query the CTE to find customers who spent over $1,000.
165. Write a query using ALL to find products whose price is greater than the price of all products in Category 3.
166. Write a query using ANY to find employees whose salary is greater than the salary of any employee in Department 4.
167. Write a subquery in the SELECT projection list that displays each employee's salary alongside the company maximum salary.
168. Write a modular query with two chained CTEs: CustomerOrders and OrderTotals, calculating average order size per customer.
169. Find all customers who have placed an order in both August 2023 and September 2023 using subqueries.
170. Write a correlated subquery that displays the most recent order date for every customer.

Advanced Questions (171–175)

171. Write a Recursive CTE that generates an integer sequence from 1 to 20.
172. Write a Recursive CTE that models the entire organizational management tree from the CEO down to individual staff members, computing the hierarchy level depth.
173. Write a correlated subquery that finds the top 1 highest-priced product within every category.
174. Write a query using a CTE that calculates the percentage of total company revenue contributed by each customer.
175. Rewrite a deeply nested subquery into a linear 3-stage Common Table Expression.

Challenge Questions (176–180)

176. Recursive Date Series Generator: Write a Recursive CTE generating all calendar dates for the month of August 2023, performing a LEFT JOIN against orders to count orders per day (showing 0 on empty days).
177. BOM (Bill of Materials) Traversal: Design a recursive CTE query that calculates the total component manufacturing cost for a hierarchical assembly.
178. Correlated Subquery Optimization: Take a slow correlated subquery calculating running balances and rewrite it into an optimized join with a derived table.
179. Detecting Circular Management References: Write a query utilizing a Recursive CTE that traverses manager hierarchies and detects if an employee accidentally reports to themselves through a loop.
180. Cumulative Tier Classification: Write a CTE calculating customer percentiles and dynamically tag the top 10% of customers as 'Key Accounts'.

---

Section 7: Database Design, Normalization & Views

Beginner Questions (181–190)

181. What normal form is violated if a column stores multiple phone numbers separated by commas?
182. What normal form requires the elimination of partial dependencies on composite keys?
183. What normal form requires the elimination of transitive dependencies?
184. Create a view named v_all_products that displays product titles, category names, and prices.
185. Query the v_all_products view for products under $300.00.
186. Create a view named v_active_employees showing only active employees.
187. Show how to inspect the definition of an existing view in MySQL.
188. Drop the view v_all_products.
189. Explain whether a view consumes physical disk storage for table rows.
190. What happens to a view if you rename one of the underlying base table columns?

Intermediate Questions (191–200)

191. Create an updatable view v_german_customers filtering for country = 'Germany'.
192. Add WITH CHECK OPTION to v_german_customers and demonstrate how it blocks inserting an Italian customer.
193. Create a security view v_employee_directory that masks employee salaries and personal phone numbers.
194. Create an analytical view v_monthly_sales_summary that aggregates revenue, order count, and average order value by month.
195. Normalize an unnormalized relation R(OrderID, CustomerName, CustomerAddress, ProductID, ProductName, Quantity) into 3NF tables.
196. Identify the functional dependencies in courses(course_id, course_code, instructor_id, instructor_office).
197. Explain why storing total_amount in the orders table is a controlled denormalization decision.
198. Demonstrate updating an employee's salary through an updatable view.
199. Explain why a view containing GROUP BY cannot be updated directly.
200. Create a view joining 4 tables to produce a customer order invoice summary.

Advanced Questions (201–205)

201. Emulate a Materialized View in MySQL using a physical summary table and a scheduled event.
202. Design a 3NF schema for a Car Rental Agency (Customers, Vehicles, Rentals, Maintenance Logs).
203. Explain Boyce-Codd Normal Form (BCNF) with a concrete schema example where 3NF is satisfied but BCNF is violated.
204. Create a view using ALGORITHM = MERGE and explain how MySQL combines the view query with the outer user query.
205. Create a view using ALGORITHM = TEMPTABLE and analyze its execution plan using EXPLAIN.

Challenge Questions (206–210)

206. Zero-Loss Normalization Decomposition: Decompose relation $R(A, B, C, D, E)$ with functional dependencies $A \rightarrow B, C$, $C \rightarrow D$, and $D \rightarrow E$ into 3NF, proving lossless join property.
207. Security View with Row-Level Tenant Isolation: Create a security view that utilizes MySQL's SESSION_USER() or CURRENT_USER() to restrict users so they can only view records belonging to their own department.
208. Materialized View Refresh Mechanism: Write a stored procedure and trigger architecture that incrementally maintains a materialized view table when new rows are inserted into order_items.
209. Denormalization Trade-off Audit: Calculate the exact byte storage difference between a normalized 3NF schema vs a denormalized Star Schema for 10 million transactions.
210. Schema Anti-Pattern Refactor: Take an existing Entity-Attribute-Value (EAV) schema anti-pattern and refactor it into a hybrid relational + JSON document design.

---

Section 8: Indexes, Transactions & Concurrency Control

Beginner Questions (211–220)

211. Write a statement to create an index named idx_cust_email on customers(email).
212. Write a statement to drop the index idx_cust_email.
213. Show all indexes currently defined on the orders table.
214. What command begins an explicit transaction in MySQL?
215. What command commits all pending transactional changes to disk?
216. What command rolls back uncommitted changes?
217. What is the default transaction isolation level in MySQL InnoDB?
218. What is a Clustered Index, and which column represents it in customers?
219. Explain what the acronym ACID stands for.
220. What is a savepoint, and how do you create one?

Intermediate Questions (221–230)

221. Create a composite index on orders(customer_id, order_date).
222. Write a query that utilizes the composite index from Question 221 obeying the Leftmost Prefix Rule.
223. Write a query that fails to utilize the composite index from Question 221 due to violating the Leftmost Prefix Rule.
224. Write a transaction that decrements stock for Product 1 and creates an order. Roll back if stock is insufficient.
225. Demonstrate how SET autocommit = 0; alters transaction persistence in a CLI session.
226. Use SELECT ... FOR UPDATE to lock a product record during an inventory verification query.
227. Use SELECT ... FOR SHARE to lock a customer record for read-only validation.
228. Explain the concurrency anomaly known as a "Dirty Read" and state which isolation level permits it.
229. Explain what a "Non-Repeatable Read" is and how REPEATABLE READ prevents it.
230. Explain what a "Phantom Read" is and how InnoDB's Next-Key Locking prevents it.

Advanced Questions (231–235)

231. Demonstrate an index-covering query on employees using EXPLAIN showing Using index in the Extra column.
232. Simulate a deadlock scenario between two concurrent connections in MySQL.
233. Write a transaction utilizing SAVEPOINT to partially roll back an order item insert while keeping the parent order.
234. Explain how InnoDB's Multi-Version Concurrency Control (MVCC) allows readers to avoid blocking writers.
235. Inspect active transaction locks using MySQL's performance_schema.data_locks table.

Challenge Questions (236–240)

236. Deadlock Resolution Routine: Write an application-level retry algorithm (in pseudocode or SQL handler) that intercepts MySQL error 1213 (Deadlock found) and retries the transaction.
237. Covering Index Optimization Challenge: Design the optimal composite index to accelerate this query to sub-millisecond speeds:

SELECT customer_id, order_date, total_amount FROM orders WHERE status = 'Delivered' ORDER BY order_date DESC LIMIT 10;

238. Index Cardinality Analysis: Write a query that computes the selectivity ratio of all indexes on customers to determine which indexes should be dropped.
239. Implicit Commit Disaster Recovery: Construct a scenario demonstrating how running an ALTER TABLE inside a transaction commits prior DML statements and breaks atomicity.
240. InnoDB Lock Escalation Mechanics: Explain why InnoDB uses row-level locking instead of table-level locking, and when row locks can escalate or lock entire ranges via Gap Locks.

---

Section 9: Programmability & Advanced Analytics (Procedures, Functions, Triggers, Windows)

Beginner Questions (241–250)

241. Write a basic stored procedure sp_list_departments that selects all departments.
242. Write the command to execute the sp_list_departments procedure.
243. Why must the DELIMITER be changed when creating stored routines in the MySQL CLI?
244. Create a deterministic function fn_add_numbers(a INT, b INT) that returns their sum.
245. Write a BEFORE INSERT trigger on employees that forces email to lowercase.
246. Use the ROW_NUMBER() window function to number all employees ordered by salary descending.
247. Extract a JSON value from '{"brand": "Sony", "model": "XM4"}' using the ->> operator.
248. Show how to drop a stored procedure named sp_my_proc.
249. Explain the difference between an IN parameter and an OUT parameter in a procedure.
250. Which pseudo-record (OLD or NEW) is available in a DELETE trigger?

Intermediate Questions (251–260)

251. Write a procedure sp_get_customer_spend taking an IN p_cust_id INT and returning their total spend as an OUT p_total DECIMAL(12,2).
252. Write a stored function fn_discounted_price(price DECIMAL(10,2), pct DECIMAL(4,2)) returning the net price.
253. Write an AFTER DELETE trigger on orders that logs the deleted order_id and total_amount to an orders_archive table.
254. Use RANK() and DENSE_RANK() side-by-side to rank products by price within their category.
255. Use LAG() to calculate the difference in salary between each employee and the next lower-paid employee in their department.
256. Use LEAD() to display the subsequent order date for each customer in orders.
257. Write a procedure with an IF-THEN-ELSE control flow that updates employee salaries based on performance ratings.
258. Write a function that counts how many orders a given customer has placed.
259. Write an AFTER UPDATE trigger that prevents modifying an order's total_amount once its status is 'Delivered'.
260. Calculate a running cumulative total of order amounts sorted by order_date using a window function.

Advanced Questions (261–265)

261. Write a stored procedure utilizing a CURSOR and a CONTINUE HANDLER FOR NOT FOUND to iterate through all active customers and award 50 bonus points.
262. Write a 3-day moving average calculation on daily order revenue using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
263. Use NTILE(4) to divide customers into 4 equal quartiles based on their lifetime spend.
264. Construct a table with a native JSON column and build an indexed virtual generated column extracting a nested string attribute.
265. Write an EXIT HANDLER FOR SQLEXCEPTION inside a stored procedure that automatically rolls back an open transaction on error.

Challenge Questions (266–270)

266. Dynamic Pivot Procedure: Write a stored procedure that dynamically constructs an SQL string using GROUP_CONCAT to pivot sales data across arbitrary years into columns and executes it via PREPARE and EXECUTE.
267. Year-over-Year (YoY) Growth Window Pipeline: Write a single SQL query using window functions that calculates monthly revenue, previous year same-month revenue (LAG 12), and the YoY percentage growth rate.
268. Strict Invariant Trigger Guard: Write a BEFORE UPDATE trigger on order_items that recalculates the parent order's total_amount in orders and rejects the update if the customer's credit limit would be exceeded.
269. Advanced JSON Array Aggregation: Query the products table and use JSON_ARRAYAGG and JSON_OBJECT to produce a single hierarchical JSON document containing each category and its nested array of products.
270. Audit Trigger with Deep Diffing: Write an AFTER UPDATE trigger on employees that compares every single column between OLD and NEW and inserts a separate log entry for each modified attribute into a normalized field_changes_audit table.

---

Section 10: Performance Optimization & Enterprise Security

Beginner Questions (271–280)

271. Write a command to generate an EXPLAIN execution plan for a query on customers.
272. What access type in EXPLAIN indicates a full table scan?
273. What access type in EXPLAIN indicates a lookup using a Primary Key?
274. Create a MySQL user account named 'intern'@'localhost' with password 'InternPass2026!'.
275. Grant read-only (SELECT) privileges on sql_mastery.* to 'intern'@'localhost'.
276. Inspect the active privileges granted to 'intern'@'localhost'.
277. Revoke SELECT privileges from 'intern'@'localhost'.
278. Drop the user 'intern'@'localhost'.
279. What does the term SARGable mean in query optimization?
280. Explain why string concatenation in web queries causes SQL Injection vulnerabilities.

Intermediate Questions (281–290)

281. Convert this non-SARGable query into a SARGable query:

SELECT * FROM employees WHERE YEAR(hire_date) = 2021;

282. Convert this non-SARGable query into a SARGable query:

SELECT * FROM customers WHERE phone LIKE '555%';

283. Use EXPLAIN ANALYZE to measure the actual execution time of a join between customers and orders.
284. Create a role named 'analyst_role', grant it SELECT on all tables in sql_mastery, and assign the role to a user.
285. Grant column-level permissions allowing a user to view only first_name, last_name, and city from customers.
286. Show how to prepare and execute a parameterized query in MySQL using PREPARE, SET, and EXECUTE.
287. Explain what Using temporary and Using filesort mean in the Extra column of an EXPLAIN plan.
288. Write the mysqldump command to back up sql_mastery using --single-transaction.
289. Identify the performance risk of comparing a VARCHAR column to an integer literal (WHERE phone = 5550100).
290. Enable the Slow Query Log in MySQL and configure it to capture queries running longer than 1.0 second.

Advanced Questions (291–295)

291. Analyze an EXPLAIN plan showing a Block Nested Loop (or Hash Join) and construct the index needed to convert it into an Index Nested Loop Join.
292. Design a multi-column covering index that completely eliminates a Filesort on a query containing both a WHERE filter and an ORDER BY clause.
293. Explain the security differences between caching_sha2_password and mysql_native_password in MySQL 8.0.
294. Write an SQL statement using sys.schema_unused_indexes to identify indexes in sql_mastery that have never been utilized by queries.
295. Configure a user account to require an encrypted SSL/TLS connection (REQUIRE SSL).

Challenge Questions (296–300)

296. Execution Plan Deconstruction: Execute EXPLAIN FORMAT=JSON on a 4-table join query and interpret the cost metrics (query_cost, read_cost, eval_cost).
297. SQL Injection Penetration Scenario: Demonstrate how an attacker bypasses authentication when a login query is written as:

SELECT * FROM users WHERE username = '$user' AND password = '$password';

Show the exact injection payload and write the secure parameterized prepared statement equivalent. 298. Buffer Pool Sizing & Hit Ratio: Write a query against information_schema and performance_schema that calculates the InnoDB Buffer Pool Read Hit Ratio percentage. 299. Granular Row-Level Access Architecture: Design a multi-tenant security architecture in MySQL where multiple client companies share a single database, but database-level roles and views ensure Tenant A can never query Tenant B's data. 300. High-Performance Query Refactoring: Take a legacy reporting query containing 4 subqueries, 2 self-joins, and a DISTINCT clause, and refactor it into an optimized pipeline using Common Table Expressions and Window Functions, reducing query cost by over 80%.