Free Oracle MySQL 8.0 Database Developer Exam 1Z0-909 Exam Practice Test

Which two statements are true about aggregate functions?

• SUM () returns o if there are no rows to aggregate.
• MAX () returns null if there are no rows to aggregate.
• COUNT (distinct) returns a count of the number of rows with different values including Null.
• MIN () cannot use distinct when it executes as a Windows function.
• AVG () does not allow use of the distinct option.

Which select statement returns true?

• SELECT NULL <> NULL;
• SELECT NULL <=> NULL;
• SELECT NULL = NULL;
• SELECT NULL := NULL;

Which change will prevent negative ages to be inserted into the people table?

• DELIMITER // CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW. age < 0 THEN SET NEW.age =0; END IF;// DELIMITER ;
• ALTER TABLE people ADD COLUMN valid_age=ABS(check_age) GENERATED ALWAYS;
• DELIMITER // CREATE TRIGGER age check AFTER INSERT ON people FOR EACH ROW IF NEW. age < 0 THEN SET NEW.age =0; END IF;// DELIMITER;
• ALTER TABLE people ADD CONSTRAINT check_age CHECK (ABS(age)>=0);

Examine these commands which execute successfully: mYsql> CREATE TABLE income (acct_num INT, amount DECIMAL(10,2)); mysql> CREATE TRIGGER subtotal BEFORE INSERT ON income FOR EACH ROW SET @subtotal = ©subtotal + NEW.amount; Which is true for the income table?

• The trigger activates after any row has been inserted into the table.
• The trigger body set causes trigger activation.
• The trigger activates after any row in the table has been updated.
• Execution of an insert statement causes the trigger to activate.

A server hosts MySQL Server and Apache Webserver supporting a PHP/PDO based application. The application must be migrated from PHP to their Java application server on another host. The MySQL instance remains on the original host. Examine the PDO connection string used in the existing application: Mysql:host-localhost;dbname=sales;unix_socket=/var/run/mysql.sock Which two prevent Java from using the Unix socket?

• The X Dev API protocol must be enabled to use sockets in Connector/J driver.
• The socket is not implemented in Connector/J driver.
• The socket can only be accessed from the local host.
• socket is a reserved word in Java.
• Java treats the socket file as insecure.

You must enforce data integrity for data Inserted in a JSON column. Which statement successfully creates a constraint in a 3SON column?

• CREATE TABLE fshop (product JSON CHECK (JSON_VALID(product) ) ) ;
• CREATE TABLE fshop ( product JSON, f INT' GENERATED ALWAYS AS (product->"S - id") ) ;
• CREATE TABLE fshop (id INT NOT NULL AUTOINCREMENT, product JSON, PRIMARY KEY (id)) ENGINE=InnoDB;
• CREATE TABLE fshop (id INT NOT NULL AUTO_ INCREMENT, product JSON, CHECK (id>0) ) ENGXNE=InnoDB;

What is an advantage of using mysqli in PHP programs rather than using PHP Data Objects (PDO)?

• mysqli supports object oriented programming.
• mysqli can access databases from other vendors.
• mysqli supports non blocking, asynchronous queries.
• mysqli includes X DevAPI functions.

Which two are true about indexes?

• Secondary index access will always be faster than a table scan.
• Indexing all of a table's columns improves performance.
• Indexes contain rows sorted by key values.
• Indexes reduce disk space used.
• Indexes are used to enforce unique constraints.

Examine the statement which executes successfully: SET sql_mode=' NO_ENGINE_SUBSTITTJTION' ; You try to create a table with a storage engine that is not available. What will happen?

• An error occurs and the create table statement fails.
• The server will create the table but it will be unusable until the specified storage engine is available.
• The server will create the table but report an error when the first attempt to insert a row is performed.
• The server will create the table using the default storage engine.

Which two differences exist between the timestamp and date time data types?

• timestamp has larger range of values.
• timestamp uses less storage space.
• timestamp stores more decimal points in seconds
• timestamp converts the value based on the session time zone.
• timestamp stores the interval between two dates.