Oracle Certified Professional, MySQL 5.6 Developer
Question No: 21
You have two tables: CREATE TABLE department (
Department_ID int unsigned NOT NULL auto_increment PRIMARY KEY, Department _Name varchar(12) NOT NULL
CREATE TABLE employee (
Employee_Number int unsigned NOT NULL PRIMARY KEY, Employee_Name varchar(10) NOT NULL,
Department_ID int unsigned DEFAULT NULL,
FOREIGN KEY (Department ID) REFERENCES Department (Department_ID) ON UPDATE SET NULL ON DELETE CASCADE
) ENGINE= InnoDB
The tables have the data: Department
You execute the statement:
REPLACE INTO department (Department_ID, Department_Name) VALUES (1, ‘Admin’); What data is in the employee table after the statement?
Question No: 22
You want to query the VARCHAR column ‘ code’ values that match:
->Start with “p”
->End with “_”
->Contain more than 3 characters Assume that sql_mode is blank.
Which two queries select only those rows?
SELECT code FROM operations WHERE code LIKE “p%%_”;
SELECT code FROM operations WHERE code LIKE ” ‘%’%_’ “ESCAPE ” ‘ ”;
SELECT code FROM operations WHERE code LIKE “p%_\_”;
SELECT code FROM operations WHERE code LIKE “p_\%\_”;
SELECT code FROM operations WHERE code LIKE “p_%_%;_”ESCAPE “/”;
Question No: 23
You have been tasked to create a database that will store a list of all managers and the employees who report directly to them. The following is stipulated:
->No manage is managing more than three people.
->No employee can work for more than one manage.
Which of these designs represents a normalized schema that meets the project requirements?
CREATE TABLE ‘manager’ ‘manager’ varchar (50) DEFAULT NULL,
’employee2′ varchar (50) DEFAULT NULL, ’employee’ varchar (50) DEFAULT NULL,
UNIQUE ( ‘manager ‘, ’employee1′, ’employee2, ’employee3′)
CREATE TABLE ‘managers’ (
“id’ int(11) NOT NULL AUTO_INCREMENT,
‘manager’ varchar (50) DEFAULT NULL , PRIMARY KEY (‘id’)
CREATE TABLE “employees’ (
‘id’ int(11) NOT NULL AUTO _INCREMENT,
‘manager_id’ int(11) DEFAULT NULL, ’employee varchar (25) DEFAULT NULL, PRIMARY KEY (‘id’)
CREATE TABLE ‘manager’ ( ‘manager’ varchar (50) DEFAULT NULL,
’employee_list’varchar (150) DEFAULT NULL,
CREATE TABLE ‘message’ (
‘id’ int(11) NOT NULL AUTO_INCREMENT,
‘manager’ varchar(50) DEFAULT NULL, PRIMARY KEY (“id’)
CREATE TABLE ’employees’ (
‘id int (11) NOT NULL AUTO _INCREMENT,
‘ employees’ varchar(25) DEFAULT NULL,
Question No: 24
The people table contains the data as shown:
Which two statements return two rows each?
SELECT DISTINCT last_name, first_name FROM people
SELECT 1,2 FROM people GROUP BY last_name
SELECT first_name, last _name FROM people WHERE age LIKE ‘2’
SELECT 1, 2 FROM people WHERE last _name =’smith’
SELECT first _name, last_name FROM people LIMIT 1, 2
Question No: 25
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:
WHERE DATE(dateline)= ‘2013-01-01’
Which change would show the greatest improvement in the response time of the query?
Use the LIKE operator:
SELECT . . .WHERE dateline LIKE ‘2013-10-01amp;’
USE the DATEDIFF function:
SELECT . . . WHERE DATEDIFF (dateline, ‘2013-01-01’) = 0
Use numeric equivalents for comparing the two dates:
SELECT. . .WHERE MOD(UNIX_TIMESTAMP (dateline), 86400 =UNIX_TIMESTAMP (‘2013-01-01’)
Use a date range comparison:
SELECT . . . WHERE dateline gt;= ‘2013-01’ and dateline lt; ‘2013-01-02’
Question No: 26
Using the MYSQL command -line client you have received the error “Lost connection to MYSQL server query”
Which three are possible causes of the error?
The MYSQL server stopped working during query execution.
The network connection was interrupted during query execution.
The connection that issued the query was killed.
The client connection stayed idle for longer than interactive -timeout seconds and was closed.
The client sent an erroneous query to the server causing the connection to be closed.
The server interrupted client connection after max-connect-errors was achieved.
Question No: 27
A table (t1) contains 1000 random integer values in the first column (col1).The random values are in the range of 0-1000.
Examine this query:
SELECT col1 FROM t1 WHERE col1 lt;=100 UNION
SELECT col1 FROM t1 WHERE col1 gt;=900 ORDER BY col1 DESC
What is the expected output?
A list of all values, including duplicates, sorted in descending order in the ranges of 0- 100 and 900-1000
A list of all random unsorted values, including duplicates, in the range of 0-100 followed by the list of all values, including in the range of 900-1000 sorted in descending order
A list of unique random values in the range of 0-100 followed by the list of unique values in the range of 900-1000 sorted in descending order
A list of all unique values sorted in descending order within the ranges of 0-100 and 900- 1000
Question No: 28
Which three are valid identifiers for the user table in the mysq1 database?
Question No: 29
You have two tables: news_source and news_feed.
Here is some sample data from the news _feed table:
Question No: 30
Which two keywords cannot be used in multi-table deletes?
Reference: http://dev.mysql.com/doc/refman/5.0/en/delete.html Explanation:
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.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|