MCQs on Advanced MySQL and PHP | PHP Advanced

Dive deeper into MySQL and PHP with this comprehensive set of 30 multiple-choice questions. Explore advanced SQL queries, database normalization, optimization techniques, PDO usage, and best practices in database design.

---

Topic 1: Advanced SQL Queries (JOINs, Subqueries, Transactions) (10 Questions)

1. Which type of JOIN returns only the matching rows from both tables?
   A) INNER JOIN
   B) LEFT JOIN
   C) RIGHT JOIN
   D) FULL OUTER JOIN
2. Which JOIN type returns all rows from the left table and the matched rows from the right table?
   A) INNER JOIN
   B) LEFT JOIN
   C) RIGHT JOIN
   D) FULL OUTER JOIN
3. A subquery in the WHERE clause can be used to:
   A) Select data from a different table
   B) Modify the data of a table
   C) Return multiple values for a condition
   D) None of the above
4. What does a TRANSACTION allow you to do in SQL?
   A) Execute multiple queries as one unit
   B) Rollback queries automatically
   C) Execute queries in parallel
   D) Commit queries without validation
5. Which SQL command is used to commit the changes made in a transaction?
   A) COMMIT
   B) SAVEPOINT
   C) ROLLBACK
   D) UPDATE
6. How can you avoid deadlock issues in transactions?
   A) Always commit after every query
   B) Use nested transactions
   C) Order queries consistently
   D) Disable foreign keys
7. Which of the following is an example of a subquery?
   A) SELECT * FROM users WHERE id = 1
   B) SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE username = ‘John’)
   C) UPDATE users SET age = 30
   D) DELETE FROM products WHERE price > 100
8. Which SQL clause is used to combine the results of two or more SELECT statements?
   A) JOIN
   B) UNION
   C) INTERSECT
   D) GROUP BY
9. What is the default behavior of a LEFT JOIN when there is no matching row in the right table?
   A) It returns NULL for columns from the right table
   B) It returns all rows from the right table
   C) It ignores the row
   D) It returns an error
10. What is the purpose of a HAVING clause in SQL?
    A) To filter rows before grouping
    B) To filter rows after grouping
    C) To combine multiple SELECT statements
    D) To sort rows

---

Topic 2: Database Normalization (5 Questions)

11. What is the primary goal of database normalization?
    A) To reduce redundancy and improve data integrity
    B) To create complex queries
    C) To make the database larger
    D) To combine multiple tables
12. What is the first normal form (1NF) in database normalization?
    A) All attributes are atomic
    B) No transitive dependencies
    C) Every non-key attribute is fully dependent on the primary key
    D) No partial dependencies
13. What does 2NF (Second Normal Form) address?
    A) Atomicity of attributes
    B) Partial dependencies between non-key attributes and the primary key
    C) Elimination of transitive dependencies
    D) Avoiding redundant data
14. Which of the following statements about the third normal form (3NF) is true?
    A) It removes partial dependencies
    B) It removes transitive dependencies
    C) It requires all attributes to be functionally dependent on the key
    D) It combines related tables
15. Which of the following is a disadvantage of over-normalization?
    A) It reduces redundancy
    B) It improves query performance
    C) It may lead to complex JOIN operations
    D) It ensures data consistency

---

Topic 3: Query Optimization (5 Questions)

16. What is the main purpose of query optimization?
    A) To improve query readability
    B) To reduce the execution time of queries
    C) To increase data redundancy
    D) To simplify SQL syntax
17. Which of the following is a method to optimize SQL queries?
    A) Using SELECT * in every query
    B) Using indexes on frequently queried columns
    C) Avoiding the use of WHERE clauses
    D) Using subqueries instead of JOINs
18. What does an index do in a database?
    A) It speeds up data retrieval
    B) It stores data in a table
    C) It limits the size of a table
    D) It enforces data integrity
19. How can you avoid unnecessary full table scans when querying large datasets?
    A) By using indexes on columns involved in WHERE and JOIN conditions
    B) By using SELECT * in queries
    C) By using ORDER BY in all queries
    D) By grouping all queries into a single transaction
20. Which SQL command is used to check the execution plan of a query in MySQL?
    A) EXPLAIN
    B) DESCRIBE
    C) PLAN
    D) ANALYZE

---

Topic 4: Using PDO for Advanced Database Operations (5 Questions)

21. What does PDO stand for in PHP?
    A) PHP Data Objects
    B) Predefined Database Object
    C) Persistent Database Object
    D) PHP Data Optimization
22. How do you prepare a SQL query using PDO in PHP?
    A) $pdo->prepare()
    B) $pdo->query()
    C) $pdo->execute()
    D) $pdo->bind()
23. Which of the following methods is used to execute a prepared statement in PDO?
    A) execute()
    B) prepare()
    C) query()
    D) bind()
24. How can you bind a parameter to a PDO statement?
    A) $stmt->bindParam()
    B) $stmt->bindValue()
    C) $stmt->execute()
    D) $stmt->prepare()
25. Which of the following is a feature of PDO in PHP?
    A) It supports multiple database drivers
    B) It only supports MySQL databases
    C) It automatically escapes SQL queries
    D) It is limited to SELECT queries

---

Topic 5: Database Design Best Practices (5 Questions)

26. Which of the following is a database design best practice?
    A) Use of single table for all data
    B) Defining primary keys and foreign keys
    C) Storing large files in the database
    D) Avoiding normalization
27. When designing a database, what is the primary purpose of creating relationships between tables?
    A) To ensure data integrity and consistency
    B) To reduce the size of the database
    C) To optimize query speed
    D) To store large objects
28. What is a composite key?
    A) A key made up of multiple foreign keys
    B) A primary key made up of more than one column
    C) A unique key that is automatically generated
    D) A key used to store a large object
29. What is the purpose of foreign keys in database design?
    A) To ensure that data in one table corresponds to data in another table
    B) To increase the speed of queries
    C) To store redundant data
    D) To reduce the number of tables
30. Which of the following is an example of a denormalization technique?
    A) Combining multiple tables into one
    B) Splitting a large table into smaller ones
    C) Creating foreign keys between tables
    D) Ensuring all data is atomic

---

Answer Key

Qno	Answer (Option with the text)
1	A) INNER JOIN
2	B) LEFT JOIN
3	A) Select data from a different table
4	A) Execute multiple queries as one unit
5	A) COMMIT
6	C) Order queries consistently
7	B) SELECT * FROM orders WHERE user_id = (SELECT id FROM users WHERE username = ‘John’)
8	B) UNION
9	A) It returns NULL for columns from the right table
10	B) To filter rows after grouping
11	A) To reduce redundancy and improve data integrity
12	A) All attributes are atomic
13	B) Partial dependencies between non-key attributes and the primary key
14	B) It removes transitive dependencies
15	C) It may lead to complex JOIN operations
16	B) To reduce the execution time of queries
17	B) Using indexes on frequently queried columns
18	A) It speeds up data retrieval
19	A) By using indexes on columns involved in WHERE and JOIN conditions
20	A) EXPLAIN
21	A) PHP Data Objects
22	A) $pdo->prepare()
23	A) execute()
24	A) $stmt->bindParam()
25	A) It supports multiple database drivers
26	B) Defining primary keys and foreign keys
27	A) To ensure data integrity and consistency
28	B) A primary key made up of more than one column
29	A) To ensure that data in one table corresponds to data in another table
30	A) Combining multiple tables into one