1Z0-882 - MySQL 5.6 Developer
Go back to Oracle
Cormcctor/Net supports a decoupled database connection mode, where a database Connection is established only when needed. ' Choose the three parts that are involved when working with decoupled data.
DataSet, MySqlDataAdapter, MySqlCommand Builder
You have access to a MySQL 5.6 database with the SELECT ,INSERT, and DELETE privileges on all tables in the mydb database. The mydb database. The mydb t1 table has five rows of data. You use the statement below to remove all rows in the t1 table: Mysql> TRUNCATE TABLE mysql.t1; What is the result?
The command succeeds and o rows are affected.
You want to use the SHA -256 Authentication plugin with Connector/J. Which two parameter settings achieve this?
You wish to create a trigger on the `city' table that will check the value of the `District' field before any INSERT. The trigger needs to change it to" Unknown" for an empty string or NULL. CREATE TRIGGER City_bi BEFORE INSERT ON CITY FOR EACH ROW BEGIN IF OLD. District IS NULL OR OLD.District= . . THEN SET NEW.District='Unknown'; END IF : END; Does the CREATE TRIGGER statement accomplish this goal?
Yes; the trigger works correctly.
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?
You create a new,empty database called `test'. You want to change the database `s CHARACTER SET to "latin1" and the database `sCOLLATION to `latin_german_ci'. Which statement is true?
You can do this one command:ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_german_ci
Assume your connection uses SQL mode ANSI_QUOTES. Which two statements cause a syntax error?
CREATE TABLE FRIENDS (NAME CHAR (10))
CREATE TABLE BINARY (PRIMARY SMALLINT)
Which statement is true about the difference between HASH and BTREE INDEXES?
HASH indexes are used only for equality comparisons (= or<=>),whereas BTREE indexes can also be used for range searches (>or<).
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: 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'))
You wish to create a trigger on the country table. It will populate two session variables based on the row that is deleted: You may assume that only one row is ever deleted at a time. CREATE TRIGGER Country_ad AFTER DELETE ON Country FOR EACH ROW SET @old _CountryName= NEW.Name, @ old _CountryCode=NEW.Code; What is the outcome of the CREATE TRIGGER statement?
An error results because FOR EACH ROW is invalid syntax.
Consider the CREATE FUNCTION statement: CREATE FUNCTION countrycount () BEGIN DECLARE count INT; SELECT COUNT (*) INTO count FROM country; RETURN count ; END What is the outcome when you try to create the function?
An error result as the variable type returned by the function must be defined with a RETURNS clause.
A MySQL command- line client is started with safe updates disabled. Mysql - -safe updates=0 What happens when you execute an UPDATE statement without a WHERE clause?
Results in an error
Using the query: SELECT Code FROM country WHERE Name = `united states' Which statement executed in the mysql client would create a prepared statement?
PREPARE countrycode FROM `SELECT code FROM country WHERE Name =?.;
Assume that none of the databases exist.
CREATE DATABASE 12
Which statement describes the process of normalizing databases?
Columns that contain repeating data values are split into separate tables to reduce item duplication.
Which two keywords cannot be used in multi-table deletes?
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?
The examinee_id column should be designated as PRIMARY KEY.
An application packs several fields of information into the details column of the table sensors. The first six characters of that data represent a location code. Example: "ABCDEFOO ooozzz comments will be here FIELDS----FIELD64" Given the query pattern: SELECT----FROM sensors WHERE details LIKE `ABCDEF Which three ALTER TABLE commands enable the optimizer to user an index for this WHERE patterns?
ALTER TABLE sensors ADD KEY (details ) USING BTREE
ALTER TABLE sensors ADD KEY (details) USING HASH
Which condition must be true in order that a view is considered updateable?
There must be a one-to-one relationship between the rows in the view and the rows in the underlying table.
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 <=100 UNION SELECT col1 FROM t1 WHERE col1 >=900 ORDER BY col1 DESC What is the expected output?
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
You try to add a foreign key to the InnoDB table employees: Mysq1> ALTER TABLE employees ADD FOREIGN KEY (Department_ID) REFERENCES departments (Department_ID); ERROR 1215 (HY000): cannot add foreign key constraint Which command will provide additional information about the error?
SHOW ENGINE INNODB STATUS
Which three connector/J connection strings can be used to connect to the MYSQL server?
Jdbc :mysql:replication://master,slave1.slave2. /test?user=xxx&password=xxx
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 list of all values , including duplicates, in the range of 1-200 and a list of unique values in the range of 900-1000
You create a table and a stored procedure: CREATE TABLE t1 (f1 int); INSERT INTO t1 VALUES (1), (2) , (3), (4), (5); CREATE PROCEDURE sum_t1() BEGIN DECLARE done INT DEFAULT 0; DECLARE va1 INT; DECLARE result CURSOR FOR SELECT f1 FROM t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cur; REPEAT FETCH cur INTO va1; IF NOT done THEN SET result = result +va1; END IF: UNTIL done END REPEAT; SELECT result; END CALL sum_t1(); What is the result of the CALL statement?
An infinite loop will be running until the command is killed.
A statement exists that can duplicate the definition of the `world'table. What is missing? CREATE TABLE t1 ___________world
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?
It immediately proceeds and causes an implicit commit of the transaction in connection1.
Identity two ways to configure a PHP application to use the UTF8 character set.
mysqli : :set_charset (`utf8')
An application tracks usage of educational courses in a company. Many people can take one course. Each person can take multiple courses. The data has been stored in one table but it is growing too large. You decide to normalize the table. What would a normalized data model contain?
Two tables: employee and course, with foreign keys on employee ID and course ID.
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?
SELECT * FROM table A INNER JOIN tableB
Which two code samples demonstrate valid methods for working with loops?
DECLARE i INT DEFAULT 0;WHILE i < 5 DoSET i = i + 1;END WHILE;
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?
SELECT Name FROM country LIMIT 100,50
SELECT Name FROM country LIMIT 35
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?
Use a date range comparison:SELECT . . . WHERE dateline >= `2013-01' and dateline < `2013-01- 02'
Assume the user has just connected to the MySQL server. What is the result of the query SELECT @ a?
The value of GLOBAL variable @ a
Which there statements describe valid reasons why queries that use "SELECT" construct are discouraged?
SELECT *causes your application to depend on the columns present when you wrote it , so your application could break if the table structure changes.
SELECT * causes the statements to return all rows from the table.
You have a transaction that queries a table at the beginning of the transaction and performs the same query later. Which two transaction isolation levels guarantee that you get the same results both times?
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?
The MYSQL server error log file
The binary log file
Consider the statement: CREATE TABLE t1 (a INT) PARTITION BY KEY /*150611 ALGORITHM = 1*/ What does this statement do?
Create the t1 table partitioned by KEY using algorithm 1 only in MYSQL versions 5.6.11 or newer and the default algorithm in older versions.
Which three database objects have non-case-sensitive names on all operating system?
A SELECT statement without an ORDER BY clause return some rows. Which statement is always true about the order of the returned results?
The results are in the order inserted.
What are two ways in which normalizing your tables helps improve performance In MySQL?
Normalizing Improves the performance of innodb_file_per _table.
What is true about the contents of the INFORMATION_SCHEMATA table?
It contains information about all the tables, triggers, and views for all databases.
Which two Functions can be used in a C program to retrieve information about warning?
You have created your connector/Net object to connect to MySQL. What are three valid database operations you can call?
ExecuteReader, ExecuteNonQuery, ExecuteScalar
When executing a stored routine, how is the SQL_MODE determined?
By the current SQL _MODE of the session
Which three view types are not updateable?
A view containing a GROUP BY clause
A view containing a WHERE clause
A view containing a HAVING clause
Which two statements provide the definition for a view named view1 in the test database?
SHOW CREATE VIEW view1 FROM test
SELECT VIEW_DEFINITION_SCHEMA.VIEWSFROM INFORMATION _SCHEMA.VIEWSWHERE TABLE_NAME="view1"AND TABLE_SCHEMA = "test"
Which statement is true when working with PHP and prepared statement?
The PDO_MySQL extension supports both client side and server side emulation.
When working with stored routines, these details are available: Where can you find these default?
In the Diagnostics area, part, of which can be stored in user-defined or routine variables
You want to query the VARCHAR column ` code' values that match: 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 "p_7_&;_"ESCAPE "/";
Which two queries return a value of NULL?
SELECT NULL =NULL
SELECT NULL is NULL