Q1. The people table contains the data as shown:
Which two statements return two rows each?
A. SELECT DISTINCT last_name, first_name FROM people
B. SELECT 1,2 FROM people GROUP BY last_name
C. SELECT first_name, last _name FROM people WHERE age LIKE ‘2’
D. SELECT 1, 2 FROM people WHERE last _name =’smith’
E. SELECT first _name, last_name FROM people LIMIT 1, 2
Answer: C,E
Q2. You started a MySQL command –line session with sq1_ mode (empty), and created the person table with the structure:
Mysql> DESC person;
You issue:
INSERT INTO person VALUES (‘casper’, ‘undefined’) What is the effect?
A. ‘Casper’ and ‘ undefined values are inserted into the ‘name’ and gender’ column.
B. The server returns an error indicating that ‘undefined’ cannot be inserted into a column of ENUM type
C. The server returns a warning and the empty string is inserted to the ‘gender’ column.
D. The server returns a warning and the first specified value ,”male” is inserted to the gender column.
Answer: B
Q3. A SELECT statement without an ORDER BY clause return some rows. Which statement is always true about the order of the returned results?
A. The results are in ascending order.
B. The results are in descending order.
C. The results are in the order inserted.
D. The results are not in a set order.
Answer: C
Explanation: Reference: http://www.postgresql.org/docs/8.2/static/sql-select.html#SQL-ORDERBY
Q4. Which statement is true about the difference between HASH and BTREE INDEXES?
A. HASH indexes support rightmost prefixing of keys, which makes them faster than BTREE indexes in many causes.
B. HASH indexes can be used by the optimizer to speed up ORDER BY operations and not BTREE indexes.
C. HASH indexes are used only for equality comparisons (= or<=>),whereas BTREE indexes can also be used for range searches (>or<).
D. HASH indexes are much faster than BTREE indexes but can only be used for a single column.
Answer: C
Explanation: Reference:https://devcenter.heroku.com/articles/postgresql-indexes
Q5. Which two code samples demonstrate valid methods for working with loops?
A. DECLARE I INT DEFAULT 0;
Test_loop: LOOP SET i =i +1;
IF i> =5 THEN
LEAVE test_loop; END IF;
END LOOP test_loop;
B. DECLARE i INT DEFAULT 0; WHILE I < 5ITERATE
SET i = i +1; END WHILE;
C. DECLARE i INT DEFAULT 0; WHILE i < 5 Do
SET i = i + 1; END WHILE;
D. DECLARE i INT DEFAULT 0;
Test _loop; LOOP SET i =i +1;
IF i >=5 THEN LEAVE; END IF;
END LOOP test_loop;
Answer: C,D
Q6. A table (t1) contains 1000 random integer values in the first column (col1). The random values range from 1 to 1000.
You execute this query:
SELECT col1 FROM t1 WHERE col1< 100 UNION
SELECT col1 FROM t1 WHERE col1 BETWEEN 100 and 200
UNION ALL
SELECT col1 FROM t1 WHERE col 1 >=900
What is the output?
A. A list of unique values within the ranges of 1-200 and 900-1000
B. A list of unique values within the range of 1-200 and a list of all values, including duplicates, on the table within the range of 900-1000
C. A list of all values , including duplicates, in the range of 1-200 and a list of unique values in the range of 900-1000
D. A list of all values, including duplicates, in the ranges of 1-200 and 900-1000
E. An error, because mixing UNION and UNION ALL in the same query is not permitted
Answer: C
Q7. You have a database ‘dev’ that contains 15 tables, all of which use the CHARACTER SET ‘utfs’ and the COLLATION ‘utfs_general_ci’.
You perform the command:
ALTER DATABASE ‘dev’ CHARACTER SET =’latin’ COLLATION=’latin1’_swedish_ci’ What is the result?
A. You get an error because database are not allowed to have CHARACTER SET or COLLATION attributes.
B. You get an error because the settings for CHARACTER SET and COLLATION attributes do not match the settings for the tables inside the database.
C. You get an error while trying to change from a more inclusive CHARACTER SET like ‘utfs to a less’ inclusive CHARACTER SET like ‘latin’.
D. You get an error because changes to the CHARACTER SET or COLLATION attribute can happen only for empty databases.
E. The statement succeeds and new tables created in this database use the new settings as their default values.
F. The statement succeeds and all of the tables inside the database are converted to user the new settings.
Answer: E
Q8. Your MYSQL server was successfully running for a days, and then suddenly stopped .You are sure that no mysqld process is running.
Which two may provide diagnostic information to help determine why the MYSQL server stopped?
A. The general query log file
B. The syslog on Linux/UNIX or the Event view on windows
C. The slow query log file
D. The MYSQL server error log file
E. The binary log file
Answer: D,E
Q9. Inspect the CREATE TABLE below:
Mysql> CREATE TABLE foo (a INT, PRIMARY KEY (a)) ENGINE =InnoDB;
Query Ok, 0 rows affected, 2 warnings (0.11 sec) Mysql> SHOW WARNINGS;
Which two is true connecting the meaning of the warnings?
A. The InnoDB storage engine was disabled during server startup.
B. Global variable skip _innodb was set to ON after the server had started.
C. The default storage engine MYISAM was used for the table created.
D. MYSQL server was not started with the option default –storage –engine=InnoDB
E. Needed to specify TYPE = InnoDB instead of ENGINE=InnoDB
Answer: D,E
Q10. Which Three options describe benefits of using the InnoDB memcached API?
A. Provides a simple, well supported method for accessing and updating data.
B. Provides a total in –memory storage system that eliminates disk1/0 overhead.
C. Bypasses the SQL layer thus avoiding extra processing.
D. Implements a fast caching mechanism to replace the query cache.
E. Provides protection via InnoDB buffers and crash recovery.
Answer: C,D,E