TRB PG Assistant - MySQL Integration - database connection, table creation with constraints, CRUD operations, joins, subqueries, querying MySQL with PHP Question Bank with Options

1. Which SQL command is used to drop (delete) a table from a database?

A) DELETE TABLE table_name;

B) REMOVE TABLE table_name;

C) DROP TABLE table_name;

D) TRUNCATE TABLE table_name;

2. What is an index in MySQL, and why is it used?

A) A type of join used to combine tables; used for data integrity.

B) A special lookup table that the database search engine can use to speed up data retrieval operations.

C) A constraint that enforces data uniqueness; used for security.

D) A method to automatically update records; used for performance.

3. Which of the following represents a valid way to define a `CHECK` constraint in MySQL?

A) CREATE TABLE orders (order_id INT, quantity INT CHECK (quantity > 0));

B) CREATE TABLE orders (order_id INT, quantity INT WHERE quantity > 0);

C) CREATE TABLE orders (order_id INT, quantity INT REQUIRE quantity > 0);

D) CREATE TABLE orders (order_id INT, quantity INT CONSTRAINT positive_quantity CHECK (quantity > 0));

4. What is the primary benefit of using the `mysqli` extension over the older `mysql_*` functions in PHP?

A) The `mysql_*` functions are faster.

B) `mysqli` supports parameterized queries and improved security features.

C) The `mysql_*` functions are still officially supported.

D) `mysqli` requires a different database system.

5. Which SQL statement is used to add a new column named 'email' to an existing table 'users'?

A) ADD COLUMN email VARCHAR(255) TO users;

B) ALTER TABLE users ADD COLUMN email VARCHAR(255);

C) MODIFY TABLE users ADD email VARCHAR(255);

D) CREATE COLUMN email VARCHAR(255) IN users;

6. What does the `LIMIT` clause in MySQL do?

A) It restricts the number of rows returned by a query.

B) It sorts the results.

C) It filters rows based on a condition.

D) It joins tables.

7. To sort results in descending order using `ORDER BY`, what keyword is appended?

A) ASC

B) DESC

C) DOWN

D) DES

8. What is the purpose of the `ORDER BY` clause in SQL?

A) To filter records based on a condition.

B) To combine rows from two tables.

C) To sort the result set by one or more columns.

D) To group records.

9. Which PHP function is used to get the number of rows in a MySQL result set?

A) mysqli_affected_rows()

B) mysqli_num_rows()

C) mysqli_fetch_row_count()

D) mysqli_count_results()

10. What is the difference between `WHERE` and `HAVING` clauses in SQL?

A) WHERE filters individual rows; HAVING filters groups.

B) WHERE filters groups; HAVING filters individual rows.

C) WHERE is used for SELECT; HAVING is used for UPDATE.

D) There is no functional difference.

11. The `HAVING` clause is used to filter groups created by the `GROUP BY` clause. What kind of condition can `HAVING` filter on?

A) Individual row values.

B) Aggregate function results.

C) Table names.

D) Column data types.

12. Which SQL clause is used to group rows that have the same values in specified columns into summary rows?

A) ORDER BY

B) WHERE

C) GROUP BY

D) HAVING

13. What is the main advantage of using `mysqli_real_escape_string()` in PHP before inserting user input into SQL queries?

A) It optimizes the query execution speed.

B) It sanitizes the input to prevent SQL injection attacks.

C) It automatically converts data types.

D) It formats the output of the query.

14. Consider the SQL query: `SELECT COUNT(*) FROM users WHERE country = 'USA';`. What does `COUNT(*)` represent?

A) The average age of users in the USA.

B) The total number of users in the USA.

C) The maximum user ID in the USA.

D) The sum of some numeric column for users in the USA.

15. What does `mysqli_fetch_all()` do?

A) Fetches all rows from a result set as an array of associative arrays.

B) Fetches a single row as an associative array.

C) Fetches all rows as an array of objects.

D) Fetches the total number of rows.

16. Which PHP function is used to fetch the next row from a result set as a numeric array?

A) mysqli_fetch_assoc()

B) mysqli_fetch_object()

C) mysqli_fetch_row()

D) mysqli_fetch_array()

17. What is the SQL statement to create a table named `products` with columns `id` (INT, PRIMARY KEY, AUTO_INCREMENT), `name` (VARCHAR(255), NOT NULL), and `price` (DECIMAL(10, 2))?

A) CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2));

B) CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2));

C) CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) AUTO_INCREMENT);

D) CREATE TABLE products (id INT, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) PRIMARY KEY AUTO_INCREMENT);

18. Which PHP function is used to get the last inserted ID from an AUTO_INCREMENT column after an INSERT query?

A) mysqli_insert_id()

B) mysqli_last_id()

C) mysqli_new_id()

D) mysqli_get_last_id()

19. When querying MySQL with PHP using `mysqli_query()`, what is returned if the query is a data retrieval statement (like SELECT)?

A) A boolean `true` or `false`.

B) The number of affected rows.

C) A result object or resource, or `false` on failure.

D) An associative array of the first row.

20. What is the purpose of the `ON DELETE CASCADE` option for a foreign key?

A) It prevents deletion of rows in the parent table.

B) It automatically deletes corresponding rows in the child table when a row in the parent table is deleted.

C) It updates the foreign key value to NULL when the parent row is deleted.

D) It logs the deletion event.

21. Which of the following best describes the `FOREIGN KEY` constraint?

A) It ensures uniqueness within a table.

B) It links a column in one table to the primary key of another table.

C) It prevents NULL values.

D) It defines a default value for a column.

22. What is the recommended method for handling errors when performing MySQL operations in PHP?

A) Ignoring all error messages.

B) Displaying all errors directly to the user.

C) Using `try-catch` blocks with `mysqli_sql_exception` or checking return values.

D) Logging errors to a temporary file only.

23. In PHP, what is the correct way to close a MySQLi connection?

A) mysqli_disconnect($connection);

B) mysqli_close($connection);

C) mysqli_end($connection);

D) mysqli_terminate($connection);

24. Which operator is commonly used to compare the result of a subquery with a value or column in the outer query?

A) LIKE

B) BETWEEN

C) IN

D) ALL

25. What is the purpose of the `WHERE` clause when used with a subquery?

A) To specify which columns to retrieve.

B) To filter the results of the outer query based on the subquery's output.

C) To define the join condition between tables.

D) To sort the final result set.

26. Which clause can a subquery typically be used within?

A) INSERT

B) UPDATE

C) SELECT

D) All of the above

27. What is a subquery in SQL?

A) A statement used to join multiple tables.

B) A query nested inside another SQL query.

C) A command to create a new database.

D) A function to calculate aggregate values.

28. Consider two tables, `Customers` (CustomerID, Name) and `Orders` (OrderID, CustomerID, OrderDate). A `LEFT JOIN` between `Customers` and `Orders` on `CustomerID` will show:

A) Only customers who have placed orders.

B) All customers, including those who have not placed any orders.

C) Only orders placed by customers.

D) All orders, regardless of customer.

29. Which JOIN type returns all rows from both tables, and fills in NULLs where there is no match on either side?

A) LEFT JOIN

B) RIGHT JOIN

C) INNER JOIN

D) FULL OUTER JOIN

30. An `INNER JOIN` returns only rows where the join condition is met in both tables. What kind of result does it produce?

A) All rows from the first table.

B) All rows from the second table.

C) Only rows that have matching values in both tables.

D) All rows from both tables, including unmatched ones.

31. Which type of JOIN returns all rows from the left table and the matched rows from the right table, or NULLs if there is no match?

A) INNER JOIN

B) RIGHT JOIN

C) LEFT JOIN

D) FULL OUTER JOIN

32. What is a `JOIN` operation in SQL used for?

A) To delete records from a table.

B) To combine rows from two or more tables based on a related column.

C) To create new tables.

D) To update existing records.

33. What does `mysqli_bind_param()` do in the context of prepared statements?

A) It binds the prepared statement to the database connection.

B) It fetches the results of the prepared statement.

C) It binds variables to parameter placeholders in the prepared statement.

D) It executes the prepared statement.

34. Which PHP function is used to prepare a statement for execution in MySQLi?

A) mysqli_query()

B) mysqli_prepare()

C) mysqli_execute()

D) mysqli_bind_param()

35. What is the primary purpose of using prepared statements in PHP with MySQL?

A) To make queries run faster by caching them.

B) To prevent SQL injection vulnerabilities and improve security.

C) To simplify the syntax for complex JOIN operations.

D) To automatically format query results into HTML tables.

36. Which PHP function is used to retrieve the number of rows affected by an INSERT, UPDATE, or DELETE query?

A) mysqli_num_rows()

B) mysqli_fetch_row()

C) mysqli_affected_rows()

D) mysqli_fetch_array()

37. What does `mysqli_fetch_assoc()` do in PHP when processing query results?

A) It executes the SQL query.

B) It fetches the next row from the result set as an associative array.

C) It establishes a connection to the database.

D) It closes the database connection.

38. In PHP, what is the typical function used to execute a SQL query against a MySQL database after establishing a connection?

A) mysqli_fetch_assoc()

B) mysqli_query()

C) mysqli_connect_error()

D) mysqli_close()

39. Which SQL statement is used to remove records from a MySQL table?

A) UPDATE

B) INSERT

C) SELECT

D) DELETE

40. To modify existing records in a MySQL table, which SQL statement is used?

A) INSERT

B) SELECT

C) UPDATE

D) DELETE

41. Which SQL statement corresponds to the 'Read' operation in CRUD?

A) INSERT

B) SELECT

C) UPDATE

D) DELETE

42. Which SQL statement is used for the 'Create' operation in CRUD?

A) SELECT

B) UPDATE

C) INSERT

D) DELETE

43. What does 'CRUD' stand for in the context of database operations?

A) Create, Read, Update, Delete

B) Connect, Retrieve, Update, Discard

C) Create, Retrieve, Undo, Destroy

D) Capture, Read, Upload, Download

44. In SQL, what does the `AUTO_INCREMENT` attribute typically do for a column?

A) It assigns a default string value if no value is provided.

B) It automatically generates a unique sequential number for each new record.

C) It prevents duplicate values in the column.

D) It ensures the column is always populated with a date.

45. What is the purpose of the `UNIQUE` constraint in a MySQL table?

A) To enforce that all values in a column are different.

B) To prevent NULL values in a column.

C) To specify a default value for a column.

D) To link two tables together.

46. Which constraint is used to ensure that a column cannot have a NULL value?

A) UNIQUE

B) DEFAULT

C) NOT NULL

D) CHECK

47. When creating a table in MySQL, what does the `PRIMARY KEY` constraint signify?

A) It allows duplicate values in the column.

B) It ensures that all values in the column are unique and not NULL.

C) It is optional and can contain NULL values.

D) It automatically assigns a unique ID to each row.

48. What does the `mysqli_error()` function return if a connection attempt fails?

A) A boolean `false` value.

B) An integer representing the error code.

C) A string describing the error message.

D) An empty string.

49. Which of the following is a common parameter for mysqli_connect() to specify the database server?

A) username

B) password

C) hostname

D) database_name

50. What is the primary purpose of the mysqli_connect() function in PHP for MySQL integration?

A) To execute a SQL query and fetch results.

B) To establish a new connection to a MySQL database.

C) To close an existing MySQL connection.

D) To create a new MySQL database.

MySQL Integration - database connection, table creation with constraints, CRUD operations, joins, subqueries, querying MySQL with PHP - Question Bank