Download Oracle Database SQL : 1Z0-071 Practice Tests and more Exams Computer Science in PDF only on Docsity!
Oracle Database SQL : 1Z0- 071 Practice Tests 1 | Oracle Database SQL : 1Z0- 071 Practice Tests For More
Oracle Database SQL : 1Z0- 071
Oracle Database SQL Certified Associate Exam Question Set
Question 1: Query the details of all customers whose name start with ‘an$’. Which query generates the required output? SELECT * FROM users Where user_name LIKE 'an$'; SELECT * FROM usersWhere user_name LIKE '%an$%' ESCAPE '%’; (Correct) SELECT * FROM users Where user_name LIKE 'an$%' ESCAPE ''; SELECT * FROM users Where user_name LIKE 'an$%' ESCAPE ‘$ '; Explanation In option one, we have used an$ without the “%” wildcard, which means this query will look for usernames with 3 characters only. For the 2nd query, we are using % as an escape character, and in the 4th query, we are using $ as an escape character, which will give the wrong result.
Oracle Database SQL : 1Z0- 071 Practice Tests 2 | Oracle Database SQL : 1Z0- 071 Practice Tests For More Question 2: Examine the structure of the CUSTOMER table. CUSTOMER_ID NOT NULL VARCHAR2 (6) FIRST_NAMEVARCHAR2 (50) LAST_NAME NOT NULL VARCHAR (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25) STATENOT NULL VARCHAR (3) , Which query can be used to display the last names and city names only for members from the states CA and SA? SELECT last_name, city FROM customer WHERE state ='CA' AND state ='SA'; SELECT last_name, city FROM customer WHERE state LIKE 'C%'; SELECT last_name, city FROM customer WHERE state IN ('CA', 'SA'); (Correct) SELECT DISTINCT last_name, city FROM customer HAVINGH state ='CA' OR state ='SA'; Explanation In the first query, we have used AND with "where," which means that both conditions should satisfy, which is not possible. In the second query, we are using LIKE C%, which will look for states starting with C only. In the 4th query, we are using HAVING without a group by, which will give an error.
Oracle Database SQL : 1Z0- 071 Practice Tests 4 | Oracle Database SQL : 1Z0- 071 Practice Tests For More Question 4: When You have a subquery inside of the main query, which query is executed first? The subquery is never executed. Only the main query is executed. They are executed at the same time the main query the subquery (Correct) Explanation The inner query is executed first, and then its result is used in the outer query.
Oracle Database SQL : 1Z0- 071 Practice Tests 5 | Oracle Database SQL : 1Z0- 071 Practice Tests For More Question 5: Which choice is NOT a statement you would use to filter data? GROUP BY (Correct)
- WHERE
- LIMIT
- LIKE Explanation WHERE is used to select rows depending on condition, LIMIT is used to filter out only a specific number of rows, and LIKE is used to get rows with matching criteria only.
Oracle Database SQL : 1Z0- 071 Practice Tests 7 | Oracle Database SQL : 1Z0- 071 Practice Tests For More Question 7: Examine the data in the EMPLOYEE table and the given query SELECT name || ‘ joined on ’ || join_date || ‘, the total amount paid is ‘|| TO_CHAR(ROUND(ROUND(SYSDATE-join_date)/365 )* salary + bonus) “TOTAL COMPENSATION UPTILL DATE” FROM employee; What will be the output of above query?
- It executes successfully but does not give the correct output. (Correct)
- It generates an error because the concatenation operator can be used to combine only two items.
- It generates an error because the usage of the round function in the expression is not valid
- It generates an error because the alias is not valid.
- It executes successfully and gives the correct output. Explanation Option 2 is wrong because we can concatenate as many items as we want, Option 3 is wrong because we can use the round function to round up the output, which we will get after dividing the difference by 365. Option 4 is wrong because we can use aliases. Option 5 is wrong because, by rounding the difference, it is giving the wrong compensation overall.
Oracle Database SQL : 1Z0- 071 Practice Tests 8 | Oracle Database SQL : 1Z0- 071 Practice Tests For More Question 8: Examine the data in the PROJECT table: Which of these statements would execute successfully? (choose 2) SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM project SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM project; SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM project; (Correct) SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM project; (Correct) Explanation NVL is a substitution function, which means it displays one value while another is NULL. The ADD_MONTHS function takes a DATETIME or DATE expression as its first argument, and requires a second integer argument, specifying the number of months to add to the first argument value. A is not correct answer. There is , missing C is incorrect because the two parameters of a NVL function must be of the same data type
Oracle Database SQL : 1Z0- 071 Practice Tests 10 | Oracle Database SQL : 1Z0- 071 Practice Tests For More Question 10: Which statements are true about SQL? (Choose two) It can process multiple data rows with a single command. (Correct) It can be used only for retrieving data from hierarchical databases. It can be used to insert, retrieve, and modify data in relational tables. (Correct) It is an abbreviation for Standard Query Language. It manages data only at the physical level. Explanation SQL retrieve data from relational database SQL stands for structured query language Schema is of three types: Logical Schema, Physical Schema and view Schema.
Question 11: Which command produces output that displays the structure of a table?
CREATE
SHOW
DESCRIBE (Correct)
SELECT ALTER Explanation DESCRIBE Is used to describe a table structure.
Question 14: Examine these employee table rows: empid empname designation ------- ----------- ------------
12330 Scott Manager 10012 King CEO 15235 James Manager 15323 Kent CTO 15124 Scott CMO Now examine this statement: SELECT DISTINCT empname FROM employee; In what order are the empnames displayed? James, Kent, King, Scott Scott, King, James, Kent (Correct) Scott, King, James, Kent, Scott Scott, Scott Explanation Only the last duplicate value will be removed from the final output.
Question 15: In SELECT * FROM x; what does x represent? SQL query
SQL statement
database table (Correct) Explanation It is the table from which you are retrieving the data.
Question 17: Examine these two rows from an employee table: empid emp_name designation ------ ------ ----- ------------- 12330 Scott Manager 10012 King CEO You must display the rows like this: PROFILE ---------- Scott is a Manager King is a CEO Which statements do this? (Choose two)
SELECT emp_name + ' is a ' + designation AS PROFILE FROM emp;
SELECT CONCAT(emp_name," is a ", designation) PROFILE FROM emp;
SELECT CONCAT(emp_name, concat(' is a ' , designation)) PROFILE FROM emp;
SELECT CONCAT(emp_name, " is a " + designation)) PROFILE FROM emp;
SELECT emp_name || ' is a '|| designation AS PROFILE FROM emp; (Correct)
Question 18: An entity set that does not have sufficient attributes to form a primary key is termed a
Strong entity set Variant set Weak entity set (Correct) Variable set Explanation An entity set that has a primary key is termed a strong entity set.
Question 20: Which statements are true about these SQL statements? (Choose two) DROP and DELETE are both data manipulation statements. MERGE and UPDATE are both data definition statements. CREATE TABLE is a data definition statement. (Correct) ROLLBACK is a transaction control statement. (Correct) COMMIT is a data definition statement. Explanation DROP is DDL, UPDATE and MERGE are both DML
Question 21: Examine this product table's column definitions: pid number primary key pname varchar2(50) You must display column headings as shown: Product_ID Product Name Which SELECT statement does this?
- SELECT pid Product_ID, pname Product Name FROM product;
- SELECT pid Product_ID, pname "Product Name" FROM product; (Correct)
- SELECT pid AS Product_ID, pname AS Product Name FROM product;
- SELECT pid @Product_ID, pname @"Product Name" FROM product; Explanation Aliases can only be given inside a double quote or by using “_” and AS is used to give alias.
