1Z0-882 - MySQL 5.6 Developer

Go back to Oracle

Example Questions

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. 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? 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? 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? Assume your connection uses SQL mode ANSI_QUOTES. Which two statements cause a syntax error? Which statement is true about the difference between HASH and BTREE INDEXES? 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? 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? 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? 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? Using the query: SELECT Code FROM country WHERE Name = `united states' Which statement executed in the mysql client would create a prepared statement? Assume that none of the databases exist. Which statement describes the process of normalizing databases? 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? 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? Which condition must be true in order that a view is considered updateable? 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? 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? Which three connector/J connection strings can be used to connect to the MYSQL server? 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? 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? 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? Identity two ways to configure a PHP application to use the UTF8 character set. 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? 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? Which two code samples demonstrate valid methods for working with loops? 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? 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? Assume the user has just connected to the MySQL server. What is the result of the query SELECT @ a? Which there statements describe valid reasons why queries that use "SELECT" construct are discouraged? 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? Consider the statement: CREATE TABLE t1 (a INT) PARTITION BY KEY /*150611 ALGORITHM = 1*/ What does this statement do? 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? What are two ways in which normalizing your tables helps improve performance In MySQL? What is true about the contents of the INFORMATION_SCHEMATA table? 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? When executing a stored routine, how is the SQL_MODE determined? Which three view types are not updateable? Which two statements provide the definition for a view named view1 in the test database? Which statement is true when working with PHP and prepared statement? When working with stored routines, these details are available: Where can you find these default? You want to query the VARCHAR column ` code' values that match: Assume that sql_mode is blank. Which two queries select only those rows? Which two queries return a value of NULL?

Study Guides