MySQL Test 3 – Advanced SQL और Case Study (50 Marks)
MySQL Test 3 – Advanced SQL and Case Study (50 Marks) Hindi Instructions
MySQL Test 3 – Advanced SQL and Case Study (50 Marks)
Case study tables ([Case study] वाले सवालों के लिए)
customers (id INT PRIMARY KEY, name, city)
| id | name | city |
|---|---|---|
| 1 | Asha | Delhi |
| 2 | Ravi | Pune |
| 3 | Meena | Delhi |
| 4 | John | Jaipur |
orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), amount INT, order_date DATE)
| id | customer_id | amount | order_date |
|---|---|---|---|
| 101 | 1 | 500 | 2026-01-05 |
| 102 | 1 | 1200 | 2026-01-20 |
| 103 | 2 | 300 | 2026-02-02 |
| 104 | 3 | 1500 | 2026-02-10 |
| 105 | 3 | 700 | 2026-03-01 |
हर case-study उत्तर असली MySQL-compatible server पर query चलाकर जाँचा गया है।
Question Palette
Green = answered, Purple = review, White = not answered.
उत्तर कुंजी और व्याख्या
पहले test पूरा करें और submit करें, फिर अपने उत्तर यहाँ मिलाएँ। हर उत्तर के साथ छोटा explanation है ताकि ग़लती का कारण समझ आए।
सभी 50 उत्तर दिखाएँ
- Which join returns only rows that have matching values in both tables?
उत्तर: B. INNER JOIN – Rows without a match in either table are dropped.
- Which join returns all rows of the left table and matching rows of the right table?
उत्तर: A. LEFT JOIN – Unmatched right-side columns appear as NULL.
- A CROSS JOIN of a 4-row table with a 5-row table returns:
उत्तर: D. 20 rows – A cross join is the Cartesian product: 4 × 5.
- Joining a table with itself (e.g. employee and manager in one table) is called:
उत्तर: B. Self join – Use aliases: FROM emp e JOIN emp m ON e.manager_id = m.id.
- MySQL does not support FULL OUTER JOIN directly. It can be produced with:
उत्तर: A. LEFT JOIN UNION RIGHT JOIN – UNION removes duplicates of the matched rows.
- UNION and UNION ALL differ because:
उत्तर: B. UNION removes duplicate rows, UNION ALL keeps them – UNION ALL is faster when duplicates are acceptable.
- A subquery is:
उत्तर: B. A query written inside another query – Example: SELECT name FROM students WHERE marks > (SELECT AVG(marks) FROM students);
- A correlated subquery:
उत्तर: B. Refers to a column of the outer query and runs once per outer row – Example: find students scoring above their own class average.
- Which operator checks whether a subquery returns any row?
उत्तर: D. EXISTS – WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cust_id = c.id).
- marks > ALL (SELECT marks FROM students WHERE class = 9) means:
उत्तर: C. Greater than the highest class-9 mark – > ANY means greater than at least one value (i.e. the minimum).
- Which window function gives 1, 2, 2, 4 for marks 95, 90, 90, 85?
उत्तर: A. RANK() – RANK leaves gaps after ties; DENSE_RANK would give 1, 2, 2, 3.
- Which window function gives 1, 2, 2, 3 for marks 95, 90, 90, 85?
उत्तर: A. DENSE_RANK() – DENSE_RANK does not skip numbers after ties (MySQL 8+).
- SUM(amount) OVER (ORDER BY order_date) calculates:
उत्तर: B. A running total – Window functions keep every row while adding an aggregate.
- A CTE (Common Table Expression) starts with the keyword:
उत्तर: D. WITH – WITH toppers AS (SELECT …) SELECT * FROM toppers; (MySQL 8+).
- Which statement shows how MySQL will execute a query (index usage etc.)?
उत्तर: A. EXPLAIN SELECT … – EXPLAIN shows the access type, keys used and rows estimated.
- An index on (last_name, first_name) helps most for:
उत्तर: A. WHERE last_name = 'Sharma' AND first_name = 'Ravi' – Composite indexes are used from the leftmost column (leftmost-prefix rule).
- A stored procedure is called using:
उत्तर: A. CALL proc_name(); – Procedures can take IN, OUT and INOUT parameters.
- Why is DELIMITER $$ used before CREATE PROCEDURE in the MySQL client?
उत्तर: B. So the semicolons inside the body do not end the statement early – Reset with DELIMITER ; afterwards.
- A trigger runs:
उत्तर: B. Automatically before or after INSERT, UPDATE or DELETE on a table – Example: a BEFORE INSERT trigger that converts names to upper case.
- Inside an UPDATE trigger, the new value of a column is accessed as:
उत्तर: B. NEW.column – OLD.column gives the value before the update.
- A SAVEPOINT allows you to:
उत्तर: A. Roll back part of a transaction – ROLLBACK TO sp1; undoes changes made after the savepoint.
- Which isolation level is the InnoDB default?
उत्तर: C. REPEATABLE READ – It prevents dirty and non-repeatable reads.
- Reading another transaction's uncommitted change is called a:
उत्तर: A. Dirty read – Allowed only at READ UNCOMMITTED.
- Two transactions each waiting for a lock held by the other is a:
उत्तर: D. Deadlock – InnoDB detects deadlocks and rolls back one transaction.
- Which tool creates a logical backup (SQL file) of a MySQL database?
उत्तर: C. mysqldump – mysqldump -u root -p school > school.sql; restore with mysql school < school.sql.
- Which clause prevents SQL injection in application code?
उत्तर: A. Prepared statements with parameters – Never build SQL by joining user input into the query string.
- CASE in SQL is used to:
उत्तर: D. Return values based on conditions inside a query – CASE WHEN marks >= 90 THEN 'A1' WHEN marks >= 80 THEN 'A2' ELSE 'B' END.
- ON DELETE CASCADE on a foreign key means:
उत्तर: A. Deleting a parent row automatically deletes its child rows – Use with care – one DELETE can remove many related rows.
- Which data type stores JSON documents with validation in MySQL 8?
उत्तर: B. JSON – Use ->> or JSON_EXTRACT to read values.
- GROUP_CONCAT(name) in a GROUP BY query returns:
उत्तर: D. All names of the group joined in one string – Default separator is a comma; SEPARATOR ' | ' changes it.
- [Case study] SELECT COUNT(*) FROM orders; returns:
उत्तर: D. 5 – The orders table has 5 rows (ids 101–105).
- [Case study] SELECT SUM(amount) FROM orders WHERE customer_id = 1; returns:
उत्तर: A. 1700 – Asha's orders: 500 + 1200 = 1700.
- [Case study] Which customer has no orders?
उत्तर: C. John – Customer 4 (John) has no row in orders; find him with LEFT JOIN … WHERE o.id IS NULL.
- [Case study] SELECT COUNT(*) FROM customers c JOIN orders o ON c.id = o.customer_id; returns:
उत्तर: A. 5 – Every order matches exactly one customer, so the inner join has 5 rows.
- [Case study] SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id; returns:
उत्तर: A. 6 – 5 matched rows + 1 row for John with NULL order columns.
- [Case study] SELECT city, SUM(amount) … GROUP BY city — the total for Delhi is:
उत्तर: A. 3900 – Delhi customers Asha (1700) and Meena (2200): 1700 + 2200 = 3900.
- [Case study] Which customer has the highest total order amount?
उत्तर: D. Meena – Totals: Asha 1700, Ravi 300, Meena 2200.
- [Case study] SELECT MAX(amount) FROM orders WHERE order_date < '2026-02-01'; returns:
उत्तर: B. 1200 – January orders are 500 and 1200.
- [Case study] SELECT AVG(amount) FROM orders; returns:
उत्तर: A. 840.0000 – Total 4200 ÷ 5 orders = 840 (MySQL shows 840.0000).
- [Case study] How many customers have total orders above 1000 (… GROUP BY customer_id HAVING SUM(amount) > 1000)?
उत्तर: A. 2 – Asha (1700) and Meena (2200).
- [Case study] SELECT COUNT(DISTINCT customer_id) FROM orders; returns:
उत्तर: D. 3 – Customers 1, 2 and 3 placed orders.
- [Case study] SELECT MONTH(order_date) m, COUNT(*) FROM orders GROUP BY m — the count for month 2 is:
उत्तर: D. 2 – Orders 103 and 104 are in February.
- [Case study] Which query lists customers who placed at least one order?
उत्तर: D. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders); – The subquery returns 1, 2, 3, 3, 1 → Asha, Ravi, Meena.
- [Case study] SELECT name FROM customers WHERE city = 'Delhi' ORDER BY name DESC LIMIT 1; returns:
उत्तर: A. Meena – Delhi names sorted Z–A: Meena, Asha.
- [Case study] For Asha, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) gives order 102 the number:
उत्तर: A. 2 – Asha's orders by date: 101 (1st), 102 (2nd).
- [Case study] To stop deleting a customer who still has orders, the foreign key should use:
उत्तर: D. ON DELETE RESTRICT (the default) – RESTRICT/NO ACTION blocks the delete with error 1451.
- [Case study] Which index would most speed up WHERE customer_id = ? on orders?
उत्तर: D. An index on orders(customer_id) – InnoDB automatically creates this index when you define the foreign key.
- [Case study] SELECT c.name, o.amount FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.amount > (SELECT AVG(amount) FROM orders); returns how many rows?
उत्तर: A. 2 – Average is 840; orders above it: 1200 (Asha) and 1500 (Meena).
- [Case study] SELECT COALESCE(SUM(o.amount), 0) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE c.name = 'John'; returns:
उत्तर: B. 0 – John has no orders, so SUM is NULL and COALESCE turns it into 0.
- [Case study] Which statement adds a 10% discount to orders above 1000?
उत्तर: A. UPDATE orders SET amount = amount * 0.9 WHERE amount > 1000; – UPDATE with WHERE changes only orders 102 and 104.
