Q1. Inspect the query:
Mysql>SELECT dept_no, emp_no FROM employees JOIN dept _manager USING(emp_no) WHERE dept_no’d004’;
4 rows in set (0.00 sec)
Mysql>EXPLAIN SELECT dept_no, emp_no FROM employees JOIN dept_manager USING (emp_no) WHERE dept_no=’d004’;
2 rows in set (0.00 sec)
Which two statements are true about the EXPLAIN output?
A. All data for the result is read from the indexex.
B. The PRIMARY KEY is used for filtering in both tables.
C. The minimal number of rows possible are read.
D. The dept_manager table has 4 times as many rows than the employees table.
Answer: C
Q2. As a developer, you inherit this table as part of a project: CREATE TABLE exam (
Exam_id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
Examinee_id INTEGER UNSIGNED UNIQUE, Score INTEGER UNSIGNED
)
What change should you make to ensure that examinee_id is an integer value throughout the table?
A. The examinee_id column should be designated as PRIMARY KEY.
B. A NOT NULL qualifier should be moved from exam-id to examinee-id.
C. The PRIMARY KEY should be dropped and re-created as PRIMARY KEY (examinee-id, exam_id).
D. A NOT NULL qualifier should be added to examinee_id.
Answer: A
Q3. A statement exists that can duplicate the definition of the ‘world’table.
What is missing?
CREATE TABLE t1 world
A. FROM
B. USING
C. COPY
D. LIKE
Answer: D
Q4. The city table has the following structure:
Consider the statement with an incorrect field name:
PREPARE countryBYID FROM ‘SELECT country FROM city WHERE ID=?,
What happens if a prepared statement named countryByID already exists when the above statement is executed?
A. A duplicate name error will result because a prepared statement with the same name already exists.
B. An unknown column error will result and the old prepared statement definition will remain in effect.
C. An unknown column error will result and no prepared statement named countryByID will exist.
D. A warning will result and the old prepared statement definition will remain in effect.
Answer: C
Q5. Assume the user has just connected to the MySQL server.
What is the result of the query SELECT @ a?
A. An error that @ a is undefined
B. A single NULL
C. An empty string
D. The value of GLOBAL variable @ a
Answer: B
Q6. Examine this table that contains over two million rows of data:
CREATE TABLE ‘news_feed’ (
.id’bigint (20) NOT NULL AUTO _INCREMENT,
.news _sources_id’varchar (11) NOT NULL,
.dataline’ datetime NOT NULL,
.headline’ varchar (256) NOT NULL,
.story’ text NOT NULL,.tag varchar (32768) DEFAULT NULL, PRIMARY KEY (‘id’)
KEY ‘dateline’ ( ‘dateline’)
)
Examine this query that returns 332 rows of date:
SELECT *
FROM news_feed
WHERE DATE(dateline)= ‘2013-01-01’
Which change would show the greatest improvement in the response time of the query?
A. Use the LIKE operator:
SELECT . . .WHERE dateline LIKE ‘2013-10-01&’
B. USE the DATEDIFF function:
SELECT . . . WHERE DATEDIFF (dateline, ‘2013-01-01’) = 0
C. Use numeric equivalents for comparing the two dates:
SELECT. . .WHERE MOD(UNIX_TIMESTAMP (dateline), 86400 =UNIX_TIMESTAMP (‘2013-01-01’)
D. Use a date range comparison:
SELECT . . . WHERE dateline >= ‘2013-01’ and dateline < ‘2013-01-02’
Answer: D
Q7. You want to compare all columns of table A to columns with matching names in table B. You want to select the rows where those have the same values on both tables.
Which query accomplishes this?
A. SELECT * FROM tableA. tableB
B. SELECT * FROM tableA JOIN tableB
C. SELECT * FROM table A INNER JOIN tableB
D. SELECT * FROM tableA NATURAL JOIN tableB
E. SELECT & FROM tableA STRAIGHT JOIN tableB
Answer: D
Q8. Which two keywords cannot be used in multi-table deletes?
A. USING
B. ORDER BY
C. LIMIT
D. IGNORE
E. JOIN
Answer: B,C
Explanation: Reference:http://dev.mysql.com/doc/refman/5.0/en/delete.html
You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE.
Q9. You want to load data directly from a file into MYSQL by using the SOURCE command. Which types of data can the file contains to perform this?
A. SQL commands
B. Comma-delimited data
C. Tab-delimited data
D. MyISAM or InnoDB data files
Answer: B
Q10. Inspect the SELECT query:
Mysql> EXPLAIN SELECT employees. Emp_no, first_name, last_name FROM employees JOIN title WHERE to_date > ‘2008-10-11’;
2 rows in set (0.00 sec)
Which action will optimize the query?
A. Add an index to the employees. emp _no column.
B. Add the keyword STRAIGHT_JOIN.
C. Add an index on the to_ date column.
D. Add the FORCE INDEX (PRIMARY) optimizer hint for the employees table.
Answer: C