Professional Documents
Culture Documents
Jawaban Exam
Jawaban Exam
Mark fo
r Review
(1) Points
Three
Four
Two (*)
Group functions cannot be nested.
Correct
Correct
2.
Mark fo
r Review
(1) Points
Group functions ignore null values. (*)
Group functions can only be used in a SELECT list.
Group functions can be used in a WHERE clause.
A query that includes a group function in the SELECT list must include a
GROUP BY clause.
Correct
Correct
3.
The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You display the total of the extended costs for each product category by locatio
n.
You need to include only the products that have a price less than $25.00.
The extended cost of each item equals the quantity value multiplied by the cost
value.
Which SQL statement will display the desired result?
Correct
Correct
4.
What is the best explanation as to why this SQL statemen
t will NOT execute?
SELECT department_id "Department", AVG (salary)"Average"
FROM employees
GROUP BY Department;
Mark for Review
(1) Points
Salaries cannot be averaged as not all the numbers will divide evenly.
You cannot use a column alias in the GROUP BY clause. (*)
The GROUP BY clause must have something to GROUP.
The department id is not listed in the departments table.
Correct
Correct
5.
Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;
Which clauses restricts the result? Choose two.
Mark for Review
(1) Points
(Choose all correct answers)
SELECT department_id, AVG(salary)
WHERE job_id <> 69879 (*)
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000 (*)
Correct
Correct
6.
The MANUFACTURER table contains these columns:
MANUFACTURER_ID NUMBER
MANUFACTURER_NAME VARCHAR2(30)
TYPE VARCHAR2(25)
LOCATION_ID NUMBER
You need to display the number of unique types of manufacturers at each location
. Which SELECT statement should you use?
Mark for Review
(1) Points
SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer
GROUP BY location_id;
(*)
Correct
Correct
7.
PROD_ID NUMBER(4)
PROD_NAME VARCHAR(20)
PROD_CAT VARCHAR2(15)
PROD_PRICE NUMBER(5)
PROD_QTY NUMBER(4)
You need to identify the minimum product price in each product category.
Which statement could you use to accomplish this task?
Mark for Review
(1) Points
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;
(*)
Correct
Correct
8.
What is the correct order of clauses in a SELECT stateme
nt?
Mark for Review
(1) Points
SELECT
FROM
WHERE
ORDER BY
GROUP BY
HAVING
SELECT
FROM
HAVING
GROUP BY
WHERE
ORDER BY
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
(*)
SELECT
FROM
WHERE
HAVING
ORDER BY
GROUP BY
Correct
Correct
9.
The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
You want to create a report that includes each employee's last name, employee id
entification number, date of hire, and salary. The report should include only th
ose employees who have been with the company for more than one year and whose sa
lary exceeds $40,000.
Which of the following SELECT statements will accomplish this task?
Mark for Review
(1) Points
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 40000
AND hire_date =
(SELECT hire_date
FROM employees
WHERE (sysdate-hire_date) / 365 > 1);
Correct
Correct
10.
The PLAYERS and TEAMS tables contain these columns:
PLAYERS
PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
TEAM_ID NUMBER
POSITION VARCHAR2 (25)
TEAMS
TEAM_ID NUMBER NOT NULL, PRIMARY KEY
TEAM_NAME VARCHAR2 (25)
You need to create a report that lists the names of each team with more than thr
ee goal keepers.
Which SELECT statement will produce the desired result?
Mark for Review
(1) Points
SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name;
SELECT t.team_name, COUNT(p.player_id)
FROM players
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
HAVING COUNT(p.player_id) > 3;
11.
Evaluate this SELECT statement:
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
Mark for Review
(1) Points
The highest salary for all employees
The highest salary in each department (*)
The employees with the highest salaries
The employee with the highest salary for each department
Correct
Correct
12.
The PAYMENT table contains these columns:
PAYMENT_ID NUMBER(9) PK
PAYMENT_DATE DATE
CUSTOMER_ID NUMBER(9)
Which SELECT statement could you use to display the number of times each custome
r payment was made between January 1, 2003 and June 30, 2003 ?
Mark for Review
(1) Points
SELECT COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003';
SELECT customer_id, COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003';
SELECT COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003'
GROUP BY customer_id;
Correct
Correct
13.
Mark for Review
(1) Points
To exclude rows before dividing them into groups using the GROUP BY clau
se, you should use a WHERE clause. (*)
You can use a column alias in a GROUP BY clause.
By default, rows are not sorted when a GROUP BY clause is used.
You must use the HAVING clause with the GROUP BY clause.
Correct
Correct
14.
When using SET operators, the names of the matching colu
mns must be identical in all of the SELECT statements used in the query. True or
False? Mark for Review
(1) Points
True
False (*)
Incorrect
15.
The ___________ operator returns all rows from both tabl
es, after eliminating duplicates.
Mark for Review
(1) Points
UNION (*)
UNION ALL
INTERSECT
MINUS
Correct
Correct
16.
You use GROUPING functions to ______ database rows from
tabulated rows. Mark for Review
(1) Points
CREATE
DISTINGUISH (*)
COMPUTE
COUNT
Correct
Correct
17.
(1) Points
produce subtotal values (*)
cross-tabulate values
produce a single result set
Correct
Correct
18.
CUBE will cross-reference the columns listed in the ____
__ clause to create a superset of groups.
Mark for Review
(1) Points
GROUP BY (*)
WHERE
SELECT
Incorrect
19.
GROUPING SETS is another extension to the GROUP BY claus
e and is used to specify multiple groupings of data but provide a single result
set. True or False?
Mark for Review
(1) Points
True (*)
False
Incorrect
Section 6
(Answer all questions in this section)
20.
Mark fo
r Review
(1) Points
A query that returns only one row from the inner SELECT statement (*)
A query that returns one or more rows from the inner SELECT statement
A query that returns only one column value from the inner SELECT stateme
nt
A query that returns one or more column values from the inner SELECT sta
tement
Correct
Correct
21.
You need to produce a report that contains all employeerelated information for those employees who have Brad Carter as a supervisor. Ho
wever, you are not sure which supervisor ID belongs to Brad Carter. Which query
should you issue to accomplish this task?
Mark for Review
(1) Points
SELECT *
FROM employees
WHERE supervisor_id =
(SELECT supervisor_id
FROM employees
WHERE last_name = 'Carter');
SELECT *
FROM supervisors
WHERE supervisor_id =
(SELECT supervisor_id
FROM employees
WHERE last_name = 'Carter');
SELECT *
FROM supervisors
WHERE supervisor_id =
(SELECT employee_id
FROM supervisors
WHERE last_name = 'Carter');
SELECT *
FROM employees
WHERE supervisor_id =
(SELECT employee_id
FROM employees
WHERE last_name = 'Carter');
(*)
Correct
Correct
22.
The Oracle server performs a correlated subquery when th
e subquery references a column from a table referred to in the parent. True or F
alse?
Mark for Review
(1) Points
True (*)
False
Correct
Correct
23.
A correlated subquery will _______ a candidate row from
an outer query, _______ the inner query using candidate row value, and _______ v
alues from the inner query to qualify or disqualify the candidate row. Mark fo
r Review
(1) Points
CREATE; EXECUTE; USE
DELETE; UPDATE; INSERT
GET; EXECUTE; USE (*)
ROLLUP; GRANT; DROP
Correct
Correct
24.
When creating a report of all employees earning more tha
n the average salary for their department, a __________ ____________ can be used
to first calculate the average salary of each department, and then compare the
salary for each employee to the average salary of that employee's department.
Mark for Review
(1) Points
WITH CLAUSE
CORRELATED SUBQUERY (*)
GROUP BY
Correct
Correct
25.
The WITH clause enables a SELECT statement to define the
subquery block at the start of the query, process the block just once, label th
e results, and then refer to the results multiple times. True or False? Mark fo
r Review
(1) Points
True (*)
False
Correct
Correct
26.
What would happen if you attempted to use a single-row o
perator with a multiple-row subquery?
Mark for Review
(1) Points
An error would be returned. (*)
No rows will be selected.
All the rows will be selected.
The data returned may or may not be correct.
Correct
Correct
27.
You are looking for Executive information using a subque
ry.
What will the following SQL statement display?
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN
(SELECT department_id FROM departments WHERE department_name = 'Executive');
Mark for Review
(1) Points
The department ID, department name, and last name for every employee in
the Executive department
The department ID, last name, and department name for every Executive in
the employees table
The department ID, last name, and job ID from departments for Executive
employees
The department ID, last name, and job ID for every employee in the Execu
tive department (*)
Incorrect
28.
Evaluate this SELECT statement that includes a subquery:
SELECT last_name, first_name
FROM customer
WHERE area_code IN
(SELECT area_code
FROM sales
WHERE salesperson_id = 20);
Which statement is true about the given subquery?
Mark for Review
(1) Points
The outer query executes before the nested subquery.
The results of the inner query are returned to the outer query. (*)
An error occurs if either the inner or outer queries do not return a val
ue.
Both the inner and outer queries must return a value, or an error occurs
.
Incorrect
29.
Examine the structures of the CUSTOMER and ORDER_HISTORY
tables:
CUSTOMER
CUSTOMER_ID NUMBER(5)
NAME VARCHAR2(25)
CREDIT_LIMIT NUMBER(8,2)
OPEN_DATE DATE
ORDER_HISTORY
ORDER_ID NUMBER(5)
CUSTOMER_ID NUMBER(5)
ORDER_DATE DATE
TOTAL NUMBER(8,2)
Which of the following scenarios would require a subquery to return the desired
results?
Mark for Review
(1) Points
You need to display the date each customer account was opened.
You need to display each date that a customer placed an order.
You need to display all the orders that were placed on a certain date.
You need to display all the orders that were placed on the same day as o
rder number 25950. (*)
Correct
Correct
30.
If you use the equality operator (=) with a subquery, ho
w many values can the subquery return? Mark for Review
(1) Points
Only 1 (*)
Up to 2
Up to 5
Unlimited
Correct
Correct
31.
The EMPLOYEES and ORDERS tables contain these columns:
EMPLOYEES
EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
ADDRESS VARCHAR2(25)
CITY VARCHAR2(20)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
ORDERS
ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY
EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY
ORDER_DATE DATE
TOTAL NUMBER(10)
Which SELECT statement will return all orders generated by a sales representativ
e named Franklin during the year 2001?
Mark for Review
(1) Points
Correct
Correct
32.
Using a subquery in which clause will return a syntax er
ror?
Mark for Review
(1) Points
WHERE
FROM
HAVING
You can use subqueries in all of the above clauses. (*)
Correct
Correct
33.
Which operator can be used with subqueries that return o
Mark for Review
ANY
ALL
IN
Correct
Correct
34.
You need to create a report to display the names of prod
ucts with a cost value greater than the average cost of all products. Which SELE
CT statement should you use?
Mark for Review
(1) Points
SELECT product_name
FROM products
WHERE cost > (SELECT AVG(cost)
FROM products);
(*)
SELECT product_name
FROM products
WHERE cost > AVG(cost);
SELECT AVG(cost), product_name
FROM products
WHERE cost > AVG(cost)
GROUP by product_name;
SELECT product_name
FROM (SELECT AVG(cost) FROM product)
WHERE cost > AVG(cost);
Correct
Correct
35.
The TEACHERS and CLASS_ASSIGNMENTS tables contain these
columns:
TEACHERS
TEACHER_ID NUMBER(5) Primary Key
NAME VARCHAR2 (25)
SUBJECT_ID NUMBER(5)
CLASS_ID NUMBER(5)
CLASS_ASSIGNMENTS
CLASS_ID NUMBER (5) Primary Key
TEACHER_ID NUMBER (5)
DATE
SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id FROM class_assignments WHERE class_id = 45
963);
(*)
SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id, class_assignments WHERE max_capacity > 0)
;
SELECT *
FROM teachers
WHERE teacher_id LIKE (SELECT teacher_id FROM class_assignments WHERE max_capaci
ty > 1000);
SELECT *
FROM class_assignments
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher
_id);
Incorrect
36.
You need to display all the players whose salaries are g
reater than or equal to John Brown's salary. Which comparison operator should yo
u use? Mark for Review
(1) Points
=
>
<=
>= (*)
Incorrect
Section 7
(Answer all questions in this section)
37.
You have been instructed to add a new customer to the CU
STOMERS table. Because the new customer has not had a credit check, you should n
ot add an amount to the CREDIT column.
The CUSTOMERS table contains these columns:
CUST_ID NUMBER(10)
COMPANY VARCHAR2(30)
CREDIT NUMBER(10)
POC VARCHAR2(30)
LOCATION VARCHAR2(30)
Which two INSERT statements will accomplish your objective?
Mark for Review
(1) Points
(Choose all correct answers)
INSERT INTO customers (cust_id, company, poc, location)
VALUES (200, 'InterCargo', 'tflanders', 'samerica');
(*)
Correct
Correct
38.
You need to copy rows from the EMPLOYEE table to the EMP
LOYEE_HIST table. What could you use in the INSERT statement to accomplish this
task?
Mark for Review
(1) Points
An ON clause
A SET clause
A subquery (*)
A function
Correct
Correct
39.
The STUDENTS table contains these columns:
STU_ID NUMBER(9) NOT NULL
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
DOB DATE
STU_TYPE_ID VARCHAR2(1) NOT NULL
ENROLL_DATE DATE
You create another table, named FT_STUDENTS, with an identical structure.You wan
t to insert all full-time students who have a STU_TYPE_ID value of "F" into the
new table. You execute this INSERT statement:
INSERT INTO ft_students
(SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date
FROM students
WHERE UPPER(stu_type_id) = 'F');
What is the result of executing this INSERT statement?
Mark for Review
(1) Points
All full-time students are inserted into the FT_STUDENTS table. (*)
An error occurs because the FT_STUDENTS table already exists.
An error occurs because you CANNOT use a subquery in an INSERT statement
.
An error occurs because the INSERT statement does NOT contain a VALUES c
lause.
Correct
Correct
40.
Assume all the column names are correct. The following S
QL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
Mark for Review
(1) Points
100 will be inserted into the department_id column.
1700 will be inserted into the manager_id column.
70 will be inserted into the department_id column. (*)
"Public Relations" will be inserted into the manager_name column.
Correct
Correct
41.
Multi-table inserts can be conditional or unconditional. True or False?
Mark for Review
(1) Points
True (*)
False
Incorrect
42.
Aliases can be used with MERGE statements. True or False
?
Mark for Review
(1) Points
True (*)
False
Correct
Correct
43.
The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You want to execute one DML statement to change the salary of all employees in d
epartment 10 to equal the new salary of employee number 89898. Currently, all em
ployees in department 10 have the same salary value. Which statement should you
execute?
Mark for Review
(1) Points
UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND departm
ent_id = 10);
Correct
Correct
44.
columns:
TEACHERS:
TEACHER_ID NUMBER(5)
NAME VARCHAR2(25)
SUBJECT_ID NUMBER(5)
HIRE_DATE DATE
SALARY NUMBER(9,2)
CLASS_ASSIGNMENTS:
CLASS_ID NUMBER(5)
TEACHER_ID NUMBER(5)
START_DATE DATE
MAX_CAPACITY NUMBER(3)
Which scenario would require a subquery to return the desired results?
Mark for Review
(1) Points
You need to display the start date for each class taught by a given teac
her.
You need to create a report to display the teachers who were hired more
than five years ago.
You need to display the names of the teachers who teach classes that sta
rt within the next week.
You need to create a report to display the teachers who teach more class
es than the average number of classes taught by each teacher. (*)
Correct
Correct
45.
You need to remove a row from the EMPLOYEES table. Which
statement would you use?
Mark for Review
(1) Points
UPDATE with a WHERE clause
INSERT with a WHERE clause
DELETE with a WHERE clause (*)
MERGE with a WHERE clause
Correct
Correct
46.
You want to enter a new record into the CUSTOMERS table.
Which two commands can be used to create new rows?
Mark for Review
(1) Points
INSERT, CREATE
MERGE, CREATE
INSERT, MERGE (*)
INSERT, UPDATE
Correct
Correct
47.
ut a WHERE clause?
(1) Points
Correct
Correct
48.
You need to update both the DEPARTMENT_ID and LOCATION_I
D columns in the EMPLOYEES table using one UPDATE statement. Which clause should
you include in the UPDATE statement to update multiple columns?
Mark fo
r Review
(1) Points
The USING clause
The ON clause
The WHERE clause
The SET clause (*)
Correct
Correct
49.
The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You need to increase the salary for all employees in department 10 by 10 percent
. You also need to increase the bonus for all employees in department 10 by 15 p
ercent. Which statement should you use?
Mark for Review
(1) Points
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10;
(*)
UPDATE employees
SET salary = salary * 1.10 AND bonus = bonus * 1.15
WHERE department_id = 10;
UPDATE employees
SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)
WHERE department_id = 10;
UPDATE employees
SET salary = salary * .10, bonus = bonus * .15
WHERE department_id = 10;
Correct
Correct
50.
You need to delete a record in the EMPLOYEES table for T
im Jones, whose unique employee identification number is 348. The EMPLOYEES tabl
e contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)
Which DELETE statement will delete the appropriate record without deleting any a
dditional records?
Mark for Review
(1) Points
DELETE FROM employees
WHERE employee_id = 348;
(*)
FROM employees;
Correct
Correct