MySQL Integration - database connection, table creation with constraints, CRUD operations, joins, subqueries, querying MySQL with PHP - One Line Questions
1.
What does the `mysqli_error()` function return if a connection attempt fails? —
A string describing the error message.
2.
When querying MySQL with PHP using `mysqli_query()`, what is returned if the query is a data retrieval statement (like SELECT)? —
A result object or resource, or `false` on failure.
3.
What is a subquery in SQL? —
A query nested inside another SQL query.
4.
What is an index in MySQL, and why is it used? —
A special lookup table that the database search engine can use to speed up data retrieval operations.
5.
Which SQL statement is used to add a new column named 'email' to an existing table 'users'? —
ALTER TABLE users ADD COLUMN email VARCHAR(255);
6.
An `INNER JOIN` returns only rows where the join condition is met in both tables. What kind of result does it produce? —
Only rows that have matching values in both tables.
7.
To sort results in descending order using `ORDER BY`, what keyword is appended? —
DESC
8.
Which of the following represents a valid way to define a `CHECK` constraint in MySQL? —
CREATE TABLE orders (order_id INT, quantity INT CONSTRAINT positive_quantity CHECK (quantity > 0));
9.
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))? —
CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2));
10.
What does 'CRUD' stand for in the context of database operations? —
Create, Read, Update, Delete
11.
Which SQL command is used to drop (delete) a table from a database? —
DROP TABLE table_name;
12.
What does `mysqli_fetch_all()` do? —
Fetches all rows from a result set as an array of associative arrays.
13.
What is the recommended method for handling errors when performing MySQL operations in PHP? —
Using `try-catch` blocks with `mysqli_sql_exception` or checking return values.
14.
The `HAVING` clause is used to filter groups created by the `GROUP BY` clause. What kind of condition can `HAVING` filter on? —
Aggregate function results.
15.
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? —
LEFT JOIN
16.
Which SQL statement corresponds to the 'Read' operation in CRUD? —
SELECT
17.
To modify existing records in a MySQL table, which SQL statement is used? —
UPDATE
18.
Which clause can a subquery typically be used within? —
All of the above
19.
When creating a table in MySQL, what does the `PRIMARY KEY` constraint signify? —
It ensures that all values in the column are unique and not NULL.
20.
In SQL, what does the `AUTO_INCREMENT` attribute typically do for a column? —
It automatically generates a unique sequential number for each new record.
21.
What does `mysqli_bind_param()` do in the context of prepared statements? —
It binds variables to parameter placeholders in the prepared statement.
22.
Which of the following best describes the `FOREIGN KEY` constraint? —
It links a column in one table to the primary key of another table.
23.
What does `mysqli_fetch_assoc()` do in PHP when processing query results? —
It fetches the next row from the result set as an associative array.
24.
What is the main advantage of using `mysqli_real_escape_string()` in PHP before inserting user input into SQL queries? —
It sanitizes the input to prevent SQL injection attacks.
25.
What is the purpose of the `ON DELETE CASCADE` option for a foreign key? —
It automatically deletes corresponding rows in the child table when a row in the parent table is deleted.
26.
What does the `LIMIT` clause in MySQL do? —
It restricts the number of rows returned by a query.
27.
Which JOIN type returns all rows from both tables, and fills in NULLs where there is no match on either side? —
FULL OUTER JOIN
28.
Which operator is commonly used to compare the result of a subquery with a value or column in the outer query? —
IN
29.
Which PHP function is used to get the number of rows in a MySQL result set? —
mysqli_num_rows()
30.
In PHP, what is the correct way to close a MySQLi connection? —
mysqli_close($connection);
31.
In PHP, what is the typical function used to execute a SQL query against a MySQL database after establishing a connection? —
mysqli_query()
32.
Which PHP function is used to fetch the next row from a result set as a numeric array? —
mysqli_fetch_row()
33.
Which PHP function is used to get the last inserted ID from an AUTO_INCREMENT column after an INSERT query? —
mysqli_insert_id()
34.
Which PHP function is used to retrieve the number of rows affected by an INSERT, UPDATE, or DELETE query? —
mysqli_affected_rows()
35.
Which PHP function is used to prepare a statement for execution in MySQLi? —
mysqli_prepare()
36.
Consider two tables, `Customers` (CustomerID, Name) and `Orders` (OrderID, CustomerID, OrderDate). A `LEFT JOIN` between `Customers` and `Orders` on `CustomerID` will show: —
All customers, including those who have not placed any orders.
37.
Which SQL clause is used to group rows that have the same values in specified columns into summary rows? —
GROUP BY
38.
Which SQL statement is used for the 'Create' operation in CRUD? —
INSERT
39.
What is the primary benefit of using the `mysqli` extension over the older `mysql_*` functions in PHP? —
`mysqli` supports parameterized queries and improved security features.
40.
Consider the SQL query: `SELECT COUNT(*) FROM users WHERE country = 'USA';`. What does `COUNT(*)` represent? —
The total number of users in the USA.
41.
What is a `JOIN` operation in SQL used for? —
To combine rows from two or more tables based on a related column.
42.
What is the purpose of the `UNIQUE` constraint in a MySQL table? —
To enforce that all values in a column are different.
43.
What is the primary purpose of the mysqli_connect() function in PHP for MySQL integration? —
To establish a new connection to a MySQL database.
44.
What is the purpose of the `ORDER BY` clause in SQL? —
To sort the result set by one or more columns.
45.
What is the primary purpose of using prepared statements in PHP with MySQL? —
To prevent SQL injection vulnerabilities and improve security.
46.
What is the purpose of the `WHERE` clause when used with a subquery? —
To filter the results of the outer query based on the subquery's output.
47.
Which constraint is used to ensure that a column cannot have a NULL value? —
NOT NULL
48.
Which SQL statement is used to remove records from a MySQL table? —
DELETE
49.
Which of the following is a common parameter for mysqli_connect() to specify the database server? —
hostname
50.
What is the difference between `WHERE` and `HAVING` clauses in SQL? —
WHERE filters individual rows; HAVING filters groups.