Q1. Consider a table my_table , with contents shown:
You execute: SELECT a b, b a
FROM my_table WHERE a < s ORDER BY b;
What does this statement return?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Q2. Consider the stored procedure CREATE PROCEDURE param_test ( IN P_in INT,
OUT P_out INT, INPUT P_inout INT) BEGIN
SELECT P_in, P_out, P_ inout; SET P_in, P_inout
END
You execute a series of commands:
What is the output of the CALL and SELECT?
A. (0,0,0) and (0,0,0)
B. (0,0,0,) and (0,200,300)
C. (0,NULL,0) and(0,200,300)
D. (0,NULL,0) and (100,200,300)
Answer: C
Q3. You are connected to a MySQL server and using a prepared statement. You accidentally exit your session.
What will happen if you log back in to use your prepared statement?
A. The statement exists, but will need to be deallocated and re-created.
B. The statement exists, but the user variables need to be redefined.
C. The statement can be used, if the MySQL server hasn’t been restarted.
D. The statement no longer exists.
Answer: A
Explanation: Reference:http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html
Q4. Examine the structure and content of the MemberLocation table:
You want to have the field location returned in all letters (example: BERLIN). Which query would you use?
A. SELECT UPPER (Location) as location FROM MemberLocation
B. SELECT UPPER (BINARY location) as location FROM MemberLocation
C. SELECT UPPER (location AS UPPER ) as location FROM Memberlocation
D. SELECT CONVERT (Location AS UPPER ) as location FROM memberlocation
Answer: A
Explanation:
https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_upper
Q5. Consider the my_table table with two integer columns, a and b, and the contents as shown; Mysql > SELECT a, b FROM my_table;
1 row in set result of this query? SELECT a—b
FROM my_table;
A. 0
B. 2
C. 4
D. An error message
Answer: A
Q6. A table country exists with a column Name. A user variable @ limitcount contains the value 20.
Which two statements are valid uses of the LIMIT clause?
A. SELECT Name FROM country LIMIT 100-50
B. SELECT Name FROM country LIMIT 100,50
C. SELECT Name FROM country LIMIT 35
D. SELECT Name FROM country LIMIT @limitcount
E. SELECT Name FROM country LIMIT RAND ()
Answer: B,C
Q7. Which two Functions can be used in a C program to retrieve information about warning?
A. mysql_info
B. mysql_error
C. mysql_warning_count
D. mysql_errno
Answer: A,C
Explanation: http://dev.mysql.com/doc/refman/5.6/en/c-api-function-overview.html
Q8. In MYSQL 5.6 you have the table t1: CREATE TABLE t1 (
id int unsigned NOT NULL PRIMARY key) ENGINE = InnoDB; There are two connections to the server. They execute in this order:
Connection 1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Connection 1> START TRANSACTION;
Connection 1> SELECT * FROM t1 WHERE id =1; Connection 2> TRUNCATE TABLE t1;
What happens to the TRUNCATE TABLE command in connection 2?
A. It immediately proceeds and causes an implicit commit of the transaction in connection1.
B. It runs concurrently with the transaction in connection 1 as each connection has its own view of the data in the t1 table.
C. It blocks waiting for a metadata lock until the transaction in connection 1 ends.
D. It blocks waiting for a table lock until the transaction in connection 1 ends.
Answer: C