oracle 1z0-909 practice test
MySQL 8.0 Database Developer
Question 1
Examine these statements:
SET collation_connection=utf8mb4_0900_as_cs;
SELECT STRCMPCAlice', UCASE ('Alice* )) ;
What is displayed?
- A. 0
- B. ERROR: 1267 (HYOOO): Illegal mix of collations
- C. -1
- D. NULL
- E. 1
Answer:
C
Question 2
Examine these commands and output:
Which is true?
- A. Existing emp_vuL is dropped and a new emp_vu1 created with the new definition.
- B. A new view is created because the previous was dropped on execution of the drop table statement.
- C. It returns an error because the CREATE TABLE statement automatically recreated the view.
- D. It returns an error because the DROP TABLE statement did not drop the view.
Answer:
D
Question 3
Which select statement returns true?
- A. SELECT NULL <> NULL;
- B. SELECT NULL <=> NULL;
- C. SELECT NULL = NULL;
- D. SELECT NULL := NULL;
Answer:
B
Question 4
Examine the structure of the emp table:
Examine the structure of the emp_vu1 view based on the emp table:
Now, examine this statement:
mysq1> INSERT INTO emp_vul VALUES ('Alice',20000) ;
What is true about executing the statement?
- A. It inserts a row in the emp table.
- B. It returns an error because an insert operation is not allowed on views.
- C. It inserts a row in the view only.
- D. It returns an error because the PRIMARY ACCOUNT column is not selected for the view definition.
Answer:
A
Question 5
Examine this statement which has executed successfully:
- A. Execution performance can be improved by using like instead of RLIKE.
- B. The statement takes advantage of index description_idx.
- C. Execution performance can be improved by, using a composite index with column description as the leftmost prefix column description.
- D. No index will improve statement performance.
- E. Execution performance can be improved by adding an index on column description.
Answer:
A
Question 6
Which two differences exist between the timestamp and date time data types?
- A. timestamp has larger range of values.
- B. timestamp uses less storage space.
- C. timestamp stores more decimal points in seconds
- D. timestamp converts the value based on the session time zone.
- E. timestamp stores the interval between two dates.
Answer:
BD
Question 7
Which two are true about MySQL Document Store?
- A. It helps to store data items in a schema-less key-value store.
- B. It can store documents greater than 4 GB.
- C. It depends heavily on strictly typed data.
- D. It allows one to bypass the SQL layer of the server.
- E. There is no access to relational tables.
Answer:
AD
Question 8
Examine this statement and output:
Now, examine these desired output values:
Which statement updates the table data as require
- A. UPDATE exam_result SET score=TRUNCATE
- B. UPDATE examresult SET score=ROUND(CEIL(score
- C. UPDATE exam_result SET score=CEIL(TRUNCATE(sco
- D. UPDATE exam_result SET score=CEIL(ROUND(score,1));
- E. UPDATE exam_result SET score=ROUND(score,1);
- F. UPDATE exam_result SET score=TRUNCATE(score,1);
Answer:
E
Question 9
Examine this statement:
- A. Inserting COMMIT; SET @m :=: before line 4
- B. user who creates the procedure needing the create and execute privileges
- C. user who creates the procedure needing the create routine privilege
- D. inserting USE <database >; before line 3
- E. Inserting DEFINER 'username '@' localhost' clause into the CREATE PROCEDURE statement
Answer:
E
Question 10
You must enforce data integrity for data Inserted in a JSON column.
Which statement successfully creates a constraint in a 3SON column?
- A. CREATE TABLE fshop (product JSON CHECK (JSON_VALID(product) ) ) ;
- B. CREATE TABLE fshop ( product JSON, f INT' GENERATED ALWAYS AS (product->"S - id") ) ;
- C. CREATE TABLE fshop (id INT NOT NULL AUTOINCREMENT, product JSON, PRIMARY KEY (id)) ENGINE=InnoDB;
- D. CREATE TABLE fshop (id INT NOT NULL AUTO_ INCREMENT, product JSON, CHECK (id>0) ) ENGXNE=InnoDB;
Answer:
C
Question 11
Examine these my.cnf settings:
Examine this entry from /data/slow.log
Which option is also set in my.cnf?
- A. log_queries_not_using_indexes
- B. log__slow_admin_statements=1
- C. log_queries_not_using_indexes=ON
- D. log_throttle_queries_not_using_indexes=100
Answer:
B
Question 12
Examine this bar graph based on columns from the players table:
Which two statements would generate this bar graph?
- A. SELECT Name, Gender, Sport, REPEAT('# 'Y GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;
- B. SELECT Name, Gender, Sport, LENGTH (GPA*10, '# ') AS GPA_Graph FROM players ORDER BY GPA DESC;
- C. SELECT Name, Gender, Sport, CHAR_LENGTH ('# ' GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;
- D. SELECT Name, Gender, Sport, RPAD ('# ' GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;
- E. SELECT Name, Gender, Sport, CONCAT ('# ' GPA*10) AS GPA_Graph FROM players ORDER BY GPA DESC;
Answer:
AD
Question 13
Examine these commands which execute successfully in the sequence shown in Sessions S1 and S2:
Now, examine this statement that execute successfully in s1:
S1> SELECT * FROM emp;
Which is true about the result of the select statement?
- A. The inserted row is returned because the transaction is auto committed in S2.
- B. The inserted row is not returned because the isolation level is READ COMMITTED in S2.
- C. The inserted row is not returned because the transaction still active in s2.
- D. The inserted row is returned because the isolation level is RPEATABLE READ in S1.
Answer:
C
Question 14
Which two statements are true about AUTO_INCREMENT?
- A. AUTO_INCREMENT values allocated to a transaction that is rolled back are not reused.
- B. A table can have multiple AUTO_INCREMENT columns.
- C. A server restart always resets the AUTO_INCREMENT value to largest value in the AUTO_INCREMENT column plus 1.
- D. The decimal data type supports AUTO_INCREMENT.
- E. An AUTO_INCREMENT column must be indexed.
Answer:
AE
Question 15
Examine the contents of these tables:
Now examine the expected results for a user with privileges to access the table:
Which query returns the expected results?
A)
B)
C)
D)
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer:
B