PHP Level 3, SQL

1. You have a query that can return thousands of rows. What MySQL statement will cause your query to return fewer rows?

GROUP

LIMIT

OFFSET

JOIN

TOP

LIMIT limits the number of rows returned. TOP does not exist in MySQL.

Explanation

LIMIT limits the number of rows returned. TOP does not exist in MySQL.

2. What is wrong with this SQL INSERT statement?

INSERT INTO tbl (name, group_id) VALUES (joe, 10)

"joe" requires single quotes

The group_id value needs single quotes.

Tbl is not a valid name for a table.

`name` and `group_id` need backticks.

The given SQL INSERT statement is incorrect because the value "joe" should be enclosed in single quotes. In SQL, string values should always be enclosed in quotes to indicate that they are text literals. Therefore, the correct statement should be: INSERT INTO tbl (name, group_id) VALUES ('joe', 10).

Explanation

The given SQL INSERT statement is incorrect because the value "joe" should be enclosed in single quotes. In SQL, string values should always be enclosed in quotes to indicate that they are text literals. Therefore, the correct statement should be: INSERT INTO tbl (name, group_id) VALUES ('joe', 10).

3. The rows of SQL result sets are most similar to which PHP data structure?

Associative arrays.

Objects.

Numerically indexed arrays.

Packed binary strings.

Serialized array structures.

The rows of SQL result sets are most similar to associative arrays in PHP. This is because both structures store data in a key-value format, where each row in the result set corresponds to an associative array in PHP. In SQL, each column value is associated with a specific column name, just like in an associative array where each value is associated with a specific key. Therefore, associative arrays are the most suitable PHP data structure to represent SQL result sets.

Explanation

The rows of SQL result sets are most similar to associative arrays in PHP. This is because both structures store data in a key-value format, where each row in the result set corresponds to an associative array in PHP. In SQL, each column value is associated with a specific column name, just like in an associative array where each value is associated with a specific key. Therefore, associative arrays are the most suitable PHP data structure to represent SQL result sets.

4. Adding an index to a table will cause some things to speed up, and other things to slow down. What are its main effects you usually have to consider?

Speeds up updates, slows down selects.

Speeds up selects, slows down inserts.

Slows down updates, speeds up inserts.

Slows down deletions and truncations, speeds up selects.

Adding an index to a table improves the speed of select queries as the database can quickly locate the required data using the index. However, it slows down inserts because the database needs to update the index each time new data is added. This is because the index needs to be maintained and updated whenever new records are inserted into the table. Therefore, while adding an index can enhance the performance of select operations, it can slightly decrease the speed of insert operations.

Explanation

Adding an index to a table improves the speed of select queries as the database can quickly locate the required data using the index. However, it slows down inserts because the database needs to update the index each time new data is added. This is because the index needs to be maintained and updated whenever new records are inserted into the table. Therefore, while adding an index can enhance the performance of select operations, it can slightly decrease the speed of insert operations.

5. If you're in an SQL command line environment (like "mysql"), which SQL statement would be useful if you wanted to add two numbers together, like 540 + 232? With what word would your SQL statement begin?

SELECT

SUM()

PRINT

ADD

In an SQL command line environment, the SELECT statement would be useful if you wanted to add two numbers together, like 540 + 232. The SELECT statement is used to retrieve data from a database, and it can also be used to perform calculations or operations on the data. In this case, you would use the SELECT statement to select the two numbers and then use the appropriate SQL function or operator to add them together.

Explanation

In an SQL command line environment, the SELECT statement would be useful if you wanted to add two numbers together, like 540 + 232. The SELECT statement is used to retrieve data from a database, and it can also be used to perform calculations or operations on the data. In this case, you would use the SELECT statement to select the two numbers and then use the appropriate SQL function or operator to add them together.

6. You need to implement yet another user and group system. The rule is that a user can belong to only one group. What's a typical way to create a report of all the users for a specific group (for example, group_id=1).

Select * from users where group_id=1

Select * from groups where groups.id=1

Select * from groups join users on users.group_id=groups.id WHERE users.group_id=1

Select * from users join groups on users.group_id=groups.id WHERE group.id=1

C is typical. D is likey to be a lot slower than C, but return the same data.

Explanation

C is typical. D is likey to be a lot slower than C, but return the same data.