+
Related Flashcards
Cards In This Set
| Front | Back |
|
Consider the following SQL statement. Which of the following could be good ideas for limiting the amount of data returned by it? (Choose 2)
SELECT * FROM MY_TABLE
A. If possible, convert the query to a stored procedure
B. If possible within your application, reduce the number of fields retrieved by the
query by specifying each field individually as part of the query
C. If possible, add a WHERE clause
D. If supported by the DBMS, convert the query to a view
E. If the DBMS allows it, use prepared statements
|
The two best tips for optimizing this query are, if possible, to limit the amount of data
extracted by it by adding a WHERE clause and specifying the exact fields you want extracted
from it. In general, unless otherwise dictated by the circumstances, you should not use SELECT *, both because of the waste of data and because it exposes your application to problems arising from changes in the database structure. This makes answers B and C correct.
|
|
The dataset returned by a query can be filtered by adding a ________ clause to it.
Your Answer: ____________________________
|
Queries can be filtered in a number of ways, but it’s clear here that the question asks about
filtering performed on the dataset to be returned by a query and, therefore, the WHERE clause is the correct answer.
|
|
What does an “inner join” construct do?
A. It joins two tables together into a third permanent table based on a common column
B. It creates a result set based on the rows in common between two tables
C. It creates a result set based on the rows based on one table
D. It creates a result set by joining two tables together and taking all the rows in common plus the rows belonging to one of the tables
E. None of the above
|
The answer that comes closest to the truth is definitely Answer B. Inner joins are used to join the contents of two tables based on a specific set of commonalities and then create a dataset that only contains rows in common between them.
|
|
Which of the following DBMSs do not have a native PHP extension?
A. MySQL
B. IBM DB/2
C. PostgreSQL
D. Microsoft SQL Server
E. None of the above
|
Answer E is correct. PHP has dedicated extensions for PostgreSQL and MySQL, while DB/2 can be accessed through ODBC and Microsoft SQL Server using TDS and the mssql extension. This question tests your knowledge of PHP’s capabilities—which could come in handy if you were discussing database adoption with your development team.
|
|
Consider the following script. Assuming that the mysql_query function sends an unfiltered query to a database connection already established elsewhere, which of the following are true? (Choose 2)
|
Answers B and D are correct. This script is very dangerous because the data inputted from the user is not escaped or filtered in any way by the application before being sent to the DBMS. Therefore, if the URL contained the parameter ID=0+OR+1, the query would become DELETE FROM MYTABLE WHERE ID = 0 OR 1, causing the database to delete all the rows from the table.
|
|
The ___________ statement can be used to add a new row to an existing table.
Your Answer: ____________________________
|
The INSERT statement is, obviously the correct answer.
|
|
Which of the following is true?
A. Indexing can speed up the insertion of new rows in a table
B. A good indexing strategy helps prevent cross-site scripting attacks
C. Indexes should be designed based on the database’s actual usage
D. Deleting a row from a table causes its indexes to be dropped
E. Indexes are necessary on numeric rows only
|
Answer C is correct. Writing good indexes often means analyzing the actual usage of a
database and determining its weak points. It’s also a good way of optimizing scripts that
perform redundant queries needlessly!
|
|
Can joins be nested?
Yes
No
|
Yes. You can nest an arbitrary number of join clauses, although the results may not always
be what you expect.
|
|
Consider the following database table and query. Which of the indexes below will help
speed up the process of executing the query?
CREATE TABLE MYTABLE (
ID INT,
NAME VARCHAR (100),
ADDRESS1 VARCHAR (100),
ADDRESS2 VARCHAR (100),
ZIPCODE VARCHAR (10),
CITY VARCHAR (50),
PROVINCE VARCHAR (2)
)
SELECT ID, VARCHAR
FROM MYTABLE
WHERE ID BETWEEN 0 AND 100
ORDER BY NAME, ZIPCODE
A. Indexing the ID column
B. Indexing the NAME and ADDRESS1 columns
C. Indexing the ID column, and then the NAME and ZIPCODE columns separately
D. Indexing the ZIPCODE and NAME columns
E. Indexing the ZIPCODE column with a full-text index
|
Answer C is correct. Indexing the ID column will ensure prompt filtering of the dataset from
the WHERE clause, while indexing NAME and ZIPCODE will make the sorting operation significantly faster.
|
|
What will happen at the end of the following sequence of SQL commands?
BEGIN TRANSACTION
DELETE FROM MYTABLE WHERE ID=1
DELETE FROM OTHERTABLE
ROLLBACK TRANSACTION
A.The contents of OTHERTABLE will be deleted
B. The contents of both OTHERTABLE and MYTABLE will be deleted
C. The contents of OTHERTABLE will be deleted, as will be all the contents of MYTABLE whose ID is 1
D. The database will remain unchanged to all users except the one that executes these
queries
E. The database will remain unchanged
|
Given that this set of queries is contained within a transaction and that the transaction is
rolled back at the end, no changes will be made to the database. Therefore, Answer E is correct.
|
|
What does the DESC keyword do in the following query?
SELECT * FROM MY_TABLE
WHERE ID > 0
ORDER BY ID, NAME DESC
A. It causes the dataset returned by the query to be sorted in descending order
B. It causes rows with the same ID to be sorted by NAME in ascending order
C. It causes rows with the same ID to be sorted by NAME in descending order
D. It causes rows to be sorted by NAME first and then by ID
E. It causes the result set to include a description of the NAME field
|
Answer C is correct. The DESC keyword is used to reverse the default sorting mechanism
applied to a column. In this case, therefore, it will cause the rows to be first sorted by ID and then by NAME in descending order.
|
|
Which of the following is not an SQL aggregate function?
A. AVG
B. SUM
C. MIN
D. MAX
E. CURRENT_DATE()
|
The CURRENT_DATE function is not a standard SQL aggregate function (although it might exist as a function supported by a particular database platform, it is not an aggregate).
|
|
Which of the following correctly identify the requirements for a column to be part of the
result set of a query that contains a GROUP BY clause?
A. The column must be indexed
B. The column must be included in the GROUP BY clause
C. The column must contain an aggregate value
D. The column must be a primary key
E. The column must not contain NULL values
|
Answers B and C are correct. In standard SQL, if a GROUP BY column is present, all the
columns that are part of the result set must either be aggregate values or be part of the GROUP BY statement itself. Some DBMSs—notably, MySQL—allow you to break these rules, but they do not behave in a standard way and your queries will not work if ported to other database systems.
|
|
What will the following query output?
SELECT COUNT(*) FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.ID <> TABLE2.ID
A.The number of rows that TABLE1 and TABLE2 do not have in common
B. A list of the rows in common between the two tables
C. The number of rows in TABLE1 times the number of rows in TABLE2 minus the number
of rows that the two tables have in common
D. A list of the rows that the two tables do not have in common
E. The number 2
|
This is a very tricky question—and, yet, it illustrates a very common conceptual mistake
about the way joins work. Despite the fact that one might be tempted to think that this query extracts the rows that the two tables do not have in common, the database takes it to mean “extract all the rows in which the IDs are different.” There is a substantial difference at work here: the DBMS will simply take every row on the left and add to the result set every row on the right that doesn’t have a matching ID. Therefore, the query will extract every row from TABLE1 times every row from TABLE2, minus the rows that the two have in common, thus making Answer C correct.
|
|
_____________ are used to treat sets of SQL statements atomically.
Your Answer: ____________________________
|
Transactions fit the bill perfectly. They are used to group together an arbitrary number of
SQL statements so that they can either be all committed or rolled back as a single unit.
|
