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
1. Which operator can be used with a multiple-row subquery? Mark for Review
(1) Points
IN (*)
<>
=
LIKE
2. Table aliases must be used when you are writing correlated subqueries. (True or false?) Mark for Review
(1) Points
True
False (*)
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
Comments
Post a Comment