Hightlight Music November 2022

(Answer all questions in this section) 1. The SELECT statement retrieves information from the database. In a SELECT statement, you can do all of the following EXCEPT: Mark for Review

 (1) Points Joining Selection Projection Manipulation (*)

 2. In the default order of precedence, which operator would be evaluated first? Mark for Review (1) Points Multiplications and Divisions are at the same level and would be evaluated first based on left to right order (*) 

Subtractions and Additions are at the same level and would be evaluated first based on left to right order Divisions and Subtractions are at the same level and would be evaluated first based on left to right order Additions and Multiplications are at the same level and would be evaluated first based on left to right order 

 3. You cannot use computers unless you completely understand exactly how they work. True or False? Mark for Review (1) Points True False (*) 

 4. There is only one kind of software used by all computers. True or False? Mark for Review (1) Points True False (*)

 5. Databases are used in most countries and by most governments. Life, as we know it, would change drastically if we no longer had access to databases. True or False? Mark for Review (1) Points True (*) False Section 2 (Answer all questions in this section) 

6. The EMPLOYEES table includes these columns: EMPLOYEE_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(15) NOT NULL FIRST_NAME VARCHAR2(10) NOT NULL HIRE_DATE DATE NOT NULL You want to produce a report that provides the last names, first names, and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task? Mark for Review (1) Points SELECT last_name, first_name, hire_date FROM employees AND hire_date >= '01-Mar-2000' and hire_date <= '30-Aug-2000'; SELECT last_name, first_name, hire_date FROM employees GROUP BY hire_date >= '01-Mar-2000' and hire_date <= '30- Aug-2000'; SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '30-Aug-2000' AND '01-Mar-2000'; SELECT last_name, first_name, hire_date FROM employees WHERE hire_date BETWEEN '01-Mar-2000' AND '30-Aug-2000'; (*) 

 7. When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False? Mark for Review (1) Points True (*) False 

 8. Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table? Mark for Review (1) Points SELECT manager_id, department_id FROM employees; (*) SELECT manager_id, DISTINCT department_id FROM employees; SELECT manager_id, department_id DISTINCT FROM employees; SELECT DISTINCT manager_id, department_id FROM employees; 

 9. You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result? Mark for Review (1) Points BETWEEN AND IN LIKE (*) 

 10. Which of the following elements cannot be included in a WHERE clause? Mark for Review (1) Points A constant A column name A column alias (*) A comparison condition Section 2 (Answer all questions in this section) 

 11. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use? Mark for Review (1) Points "=>" > != >= (*) Section 3 (Answer all questions in this section) 

12. What value will the following SQL statement return? SELECT employee_id FROM employees WHERE employee_id BETWEEN 100 AND 150 OR employee_id IN(119, 175, 205) AND (employee_id BETWEEN 150 AND 200); Mark for Review (1) Points 200, 201, 202, 203, 204, 205, 206 100, 101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149 (*) 19 No rows will be returned 

 13. Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50? Mark for Review (1) Points SELECT product_id, product_name FROM products WHERE price < 50; (*) SELECT product_id, product_name FROM products WHERE price <= 50; SELECT product_id, product_name FROM products HAVING price < 50; SELECT product_id, product_name FROM products GROUP BY price < 50; SELECT product_id, product_name FROM products WHERE price < 50.00 GROUP BY price; 

 14. Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column? Mark for Review (1) Points ORDER BY (*) WHERE HAVING FROM 

 15. Will the following statement return one row? SELECT MAX(salary), MIN(Salary), AVG(SALARY) FROM employees; Mark for Review (1) Points Yes, it will return the highest salary from each employee. Yes, it will return the average salary from the employees table. Yes, it will return the highest salary, the lowest salary, and the average salary from all employees. (*) No, it is illegal. You cannot use more than one multi-row function in a SELECT statement. Section 4 (Answer all questions in this section) 

16. ROUND and TRUNC functions can be used with which of the following Datatypes? Mark for Review (1) Points Dates and numbers (*) Dates and characters Numbers and characters None of the above 

 17. You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue? Mark for Review (1) Points SELECT orderid, total FROM orders WHERE order_date IN ( 01-Jan-2002 , 31-Jan-2002 ) ORDER BY total; SELECT orderid, total FROM orders WHERE order_date BETWEEN '31-Jan-2002' AND '01-Jan-2002' ORDER BY total DESC; SELECT orderid, total FROM orders WHERE order_date BETWEEN '01-Jan-2002' AND '31-Jan-2002' ORDER BY total DESC; (*) SELECT orderid, total FROM orders WHERE order_date LIKE '01-Jan-2002' AND '31-Jan-2002' ORDER BY total DESC; 

 18. The PRICE table contains this data: PRODUCT_ID MANUFACTURER_ID 86950 59604 You query the database and return the value 95. Which script did you use? Mark for Review (1) Points SELECT TRIM(product_id, -3, 2) FROM price WHERE manufacturer_id = 59604; SELECT LENGTH(product_id, 3, 2) FROM price WHERE manufacturer_id = 59604; SELECT SUBSTR(product_id, -1, 3) FROM price WHERE manufacturer_id = 59604; SELECT SUBSTR(product_id, 3, 2) FROM price WHERE manufacturer_id = 59604; (*) 

 19. You query the database with this SQL statement: SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID" FROM employee; In which order are the functions evaluated? Mark for Review (1) Points LOWER, CONCAT, SUBSTR CONCAT, SUBSTR, LOWER (*) LOWER, SUBSTR, CONCAT SUBSTR, CONCAT, LOWER Section 5 (Answer all questions in this section) 

 20. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False? Mark for Review (1) Points True (*) False Section 5 (Answer all questions in this section) 

21. When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null? Mark for Review (1) Points SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due" FROM student_accounts; (*) SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due" FROM student_accounts; SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due" FROM student_accounts; SELECT tuition_balance + housing_balance FROM student_accounts; 

 22. You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use? Mark for Review (1) Points TO_CHAR and NULLIF TO_NUMBER and NULLIF TO_CHAR and NVL (*) TO_CHAR and NULL 

 23. If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result? Mark for Review (1) Points 1917 1901 2017 (*) 2001 

 24. The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(9) LAST_NAME VARCHAR2 (25) FIRST_NAME VARCHAR2 (25) SALARY NUMBER(6) You need to create a report to display the salaries of all employees. Which SQL Statement should you use to display the salaries in format: "$45,000.00"? Mark for Review (1) Points SELECT TO_NUM(salary, '$999,999.00') FROM employees; SELECT TO_CHAR(salary, '$999,999') FROM employees; SELECT TO_NUM(salary, '$999,990.99') FROM employees; SELECT TO_CHAR(salary, '$999,999.00') FROM employees; (*) 

 25. Which best describes the TO_CHAR function? Mark for Review (1) Points The TO_CHAR function can be used to remove text from column data that will be returned by the database. The TO_CHAR function can be used to display dates and numbers according to formatting conventions that are supported by Oracle. (*) The TO_CHAR function can be used to specify meaningful column names in an SQL statement's result set. The TO_CHAR function can only be used on Date columns. Section 6 (Answer all questions in this section) 

 26. Which SELECT statement implements a self join? Mark for Review (1) Points SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id = t.product_id (+); SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id = t.product_id; SELECT p.part_id, t.product_id FROM part p, product t WHERE p.part_id =! t.product_id; SELECT p.part_id, t.product_id FROM part p, part t WHERE p.part_id = t.product_id; (*) 

 27. Hierarchical queries MUST use the LEVEL pseudo column. True or False? Mark for Review (1) Points True False (*) 

 28. Below find the structures of the PRODUCTS and VENDORS tables: PRODUCTS PRODUCT_ID NUMBER PRODUCT_NAME VARCHAR2 (25) VENDOR_ID NUMBER CATEGORY_ID NUMBER VENDORS VENDOR_ID NUMBER VENDOR_NAME VARCHAR2 (25) ADDRESS VARCHAR2 (30) CITY VARCHAR2 (25) REGION VARCHAR2 (10) POSTAL_CODE VARCHAR2 (11) You want to create a query that will return an alphabetical list of products, including the product name and associated vendor name, for all products that have a vendor assigned. Which two queries could you use? Mark for Review (1) Points (Choose all correct answers) SELECT p.product_name, v.vendor_name FROM products p JOIN vendors v USING (vendor_id) ORDER BY p.product_name; (*) SELECT p.product_name, v.vendor_name FROM products p LEFT OUTER JOIN vendors v ON p.vendor_id = v.vendor_id ORDER BY p.product_name; SELECT p.product_name, v.vendor_name FROM products p JOIN vendors v USING (p.vendor_id) ORDER BY p.product_name; SELECT p.product_name, v.vendor_name FROM products p JOIN vendors v ON (vendor_id) ORDER BY p.product_name; SELECT p.product_name, v.vendor_name FROM products p NATURAL JOIN vendors v ORDER BY p.product_name; (*) 

 29. You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create? Mark for Review (1) Points A cross join (*) An inner join An equijoin A full outer join 

 30. Which of the following conditions will cause an error on a NATURAL JOIN? Mark for Review (1) Points If it selects rows from the two tables that have equal values in all matched columns. When you attempt to write it as an equijoin. If the columns having the same names have different data types, then an error is returned. (*) When the NATURAL JOIN clause is based on all columns in the two tables that have the same name. Section 6 (Answer all questions in this section) 

 31. What is another name for a simple join or an inner join? Mark for Review (1) Points Equijoin (*) Outer Join Self Join Nonequijoin 

 32. The following statement is an example of what kind of join? SELECT car.vehicle_id, driver.name FROM car LEFT OUTER JOIN driver ON (driver_id) ; Mark for Review (1) Points Equijoin Outer Join (*) Inner Join Optimal Join Section 7 (Answer all questions in this section) 

 33. Will the following statement work? SELECT department_name, last_name FROM employees, departments WHERE department_id = department_id; Mark for Review (1) Points No, Oracle will not allow joins in the WHERE clause Yes, Oracle will resolve which department_id colum comes from which table. No, Oracle will return a Column Ambiguously Defined error. (*) Yes, there are no syntax errors in that statement 

 34. When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause? Mark for Review (1) Points 3 0 2 (*) 1 

 35. Evaluate this SELECT statement: SELECT p.player_id, m.last_name, m.first_name, t.team_name FROM player p LEFT OUTER JOIN player m ON (p.manager_id = m.player_id) LEFT OUTER JOIN team t ON (p.team_id = t.team_id); Which join is evaluated first? Mark for Review (1) Points The join between the player table and the team table on TEAM_ID The join between the player table and the team table on MANAGER_ID The join between the player table and the team table on PLAYER_ID The self-join of the player table (*) Section 8 (Answer all questions in this section) 

 36. Evaluate this SELECT statement: SELECT COUNT(*) FROM products; Which statement is true? Mark for Review (1) Points The number of unique PRODUCT_IDs in the table is displayed. An error occurs due to an error in the SELECT clause. The number of rows in the table is displayed. (*) An error occurs because no WHERE clause is included in the SELECT statement. 

 37. The VENDORS table contains these columns: VENDOR_ID NUMBER Primary Key NAME VARCHAR2(30) LOCATION_ID NUMBER ORDER_DT DATE ORDER_AMOUNT NUMBER(8,2) Which two clauses represent valid uses of aggregate functions for this table? Mark for Review (1) Points (Choose all correct answers) SELECT SUM(order_dt) SELECT MIN(AVG(order_amount)) (*) WHERE MAX(order_dt) = order_dt FROM MAX(order_dt) SELECT SUM(order_amount) (*) 

 38. Which group function would you use to display the highest salary value in the EMPLOYEES table? Mark for Review (1) Points COUNT MIN MAX (*) AVG Section 9 (Answer all questions in this section) 

39. The difference between UNION and UNION ALL is Mark for Review (1) Points UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*) UNION is a synomym for UNION ALL. UNION ALL is more like a NATURAL JOIN. There is no difference; you get exactly the same result from both. 

 40. Examine the following statement: SELECT department_id, manager_id, job_id, SUM(salary) FROM employees GROUP BY GROUPING SETS(.......); Select the correct GROUP BY GROUPING SETS clause from the following list: Mark for Review (1) Points GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id) GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id)) GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id) GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*) Section 9 (Answer all questions in this section) 

41. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review (1) Points True (*) False 

 42. Group functions can be nested to a depth of? Mark for Review (1) Points Group functions cannot be nested. Two (*) Three Four 

 43. Evaluate this SELECT statement: SELECT MIN(hire_date), department_id FROM employees GROUP BY department_id; Which values are displayed? Mark for Review (1) Points The latest hire date in the EMPLOYEES table The earliest hire date in the EMPLOYEES table The earliest hire date in each department (*) The hire dates in the EMPLOYEES table that contain NULL values 

 44. Which statement about the GROUP BY clause is true? Mark for Review (1) Points By default, rows are not sorted when a GROUP BY clause is used. To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*) You must use the HAVING clause with the GROUP BY clause. You can use a column alias in a GROUP BY clause. Section 10 (Answer all questions in this section) 

45. Which of the following best describes the meaning of the ANY operator? Mark for Review (1) Points Equal to any member in the list Compare value to each value returned by the subquery (*) Equal to each value in the list Compare value to the first value returned by the subquery Section 10 (Answer all questions in this section) 

46. Which statement about the ANY operator, when used with a multiple-row subquery, is true? Mark for Review (1) Points The ANY operator can be used with the DISTINCT keyword. The ANY operator can be used with the LIKE and IN operators. The ANY operator is a synonym for the ALL operator. The ANY operator compares every value returned by the subquery. (*) 

 47. Evaluate this SELECT statement: SELECT player_id, name FROM players WHERE team_id IN (SELECT team_id FROM teams WHERE team_id > 300 AND salary_cap > 400000); What would happen if the inner query returned a NULL value? Mark for Review (1) Points No rows would be returned by the outer query. (*) A syntax error in the inner query would be returned. A syntax error in the outer query would be returned. All the rows in the PLAYER table would be returned by the outer query. 

 48. Which comparison operator can only be used with a single-row subquery? Mark for Review (1) Points ALL ANY <> (*) IN 

 49. Which operator can be used with a multiple-row subquery? Mark for Review (1) Points IN (*) <> = LIKE 

 50. Table aliases must be used when you are writing correlated subqueries. (True or false?) Mark for Review (1) Points True False (*)