Run a mock round

To retrieve all columns for records where FirstName equals 'John', combine SELECT * with a WHERE equality filter:

SELECT * 
FROM Customers 
WHERE FirstName = 'John';

### Case Sensitivity & Index Usage:

• In MySQL with default collation (utf8mb4_general_ci), string comparisons are case-insensitive ('John' matches 'john').
• In PostgreSQL, comparisons are case-sensitive by default ('John' != 'john'). For case-insensitive matching in Postgres, use WHERE FirstName ILIKE 'John'.
• Ensure an index exists on FirstName to avoid an expensive full table scan.

GRANT. To remove: REVOKE.

GRANT SELECT, INSERT ON database.orders TO 'app_user'@'%';
REVOKE DELETE ON database.orders FROM 'app_user'@'%';

A database transaction that finishes all operations successfully and persists its changes permanently to durable storage is said to be Committed:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Transaction is now committed and permanent

Once committed, the transaction satisfies the Durability property of ACID and survives power outages or server crashes.

-- MySQL
SELECT * FROM tbl ORDER BY RAND() LIMIT 10;
-- PostgreSQL
SELECT * FROM tbl ORDER BY RANDOM() LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM tbl ORDER BY NEWID();

Performance note: This is O(n log n) and full table scan. For large tables, use a random ID approach.

Yes — SELF JOIN. You must use aliases to distinguish the two instances.

SELECT a.name AS employee, b.name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.id;

-- MySQL / PostgreSQL
SELECT name, mark FROM Students ORDER BY mark DESC LIMIT 3;
-- SQL Server
SELECT TOP 3 name, mark FROM Students ORDER BY mark DESC;
-- Oracle
SELECT name, mark FROM Students WHERE ROWNUM <= 3 ORDER BY mark DESC;

To retrieve only the FirstName column from the Customers table, use the standard SELECT projection statement:

SELECT FirstName 
FROM Customers;

### Performance Best Practice:
Always select specific column names instead of using SELECT *. Explicit column projection:

• Reduces network transfer bandwidth between the database server and application.
• Decreases application server memory usage during row hydration.
• Allows database engines to leverage Covering Indexes, satisfying queries entirely in-memory from the index tree without scanning table row pages.

• COMMIT — Make changes permanent
• ROLLBACK — Undo all changes in the transaction
• SAVEPOINT — Set a partial rollback point

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Oops, wrong account!
ROLLBACK TO SAVEPOINT before_credit;
-- Now retry with correct account
COMMIT;

Yes, using AS (alias):

SELECT first_name AS fname, last_name AS lname FROM customers;
-- AS is optional but recommended for readability:
SELECT first_name fname FROM customers;