Test: Mid Term Exam Semester 2 - Part II ORACLE ACADEMY

Test: Mid Term Exam Semester 2 - Part II
1.  	The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL 
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE NOT NULL DEFAULT SYSDATE
Which INSERT statement will execute successfully?	
 
 
INSERT INTO products 
VALUES (2958, 'Cable', 8690, 7.09, 4.04, 700);
(*)
 
 
INSERT INTO products 
VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);
 
 
INSERT INTO products(product_id, product_name) 
VALUES (2958, 'Cable');
 
 
INSERT INTO products(product_id, product_name, supplier_id 
VALUES (2958, 'Cable', 8690, SYSDATE);
 
 
 	 	 	 	 	 	Correct
 
 
 	 	2.  	You have been instructed to add a new customer to the CUSTOMERS table. Because the new customer has not had a credit check, you should not 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?	 Označit pro zhodnocení 
(1) Body 
 
 	 	 	(Vyberte všechny správné odpovědi)	 
 
 
INSERT INTO customers (cust_id, company, poc, location) 
VALUES (200, 'InterCargo', 'tflanders', 'samerica');
(*)
 
 
INSERT INTO customers 
VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');
(*)
 
 
INSERT INTO customers 
VALUES (cust_id, company, credit, poc, location) (200, 'InterCargo', 0, 'tflanders', 'samerica');
 
 
INSERT INTO customers 
VALUES (200, InterCargo, 0, tflanders, samerica);
 
 
 	 	 	 	 	 	Correct
 
 
 	 	3.  	Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments (department_id, department_name, manager_id, location_id) 
VALUES (70, 'Public Relations', 100, 1700);	 Označit pro zhodnocení 
(1) Body 
 
 
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
 
 
 	 	4.  	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 want 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?	 Označit pro zhodnocení 
(1) Body 
 
 
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 clause.
 
 
 	 	 	 	 	 	Correct
 
 
 	 	5.  	One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY 
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)
Which UPDATE statement will accomplish your objective?	 Označit pro zhodnocení 
(1) Body 
 
 
UPDATE employees 
SET last_name = 'cooper' 
WHERE last_name = 'roper'; (*)
 
 
UPDATE employees last_name = 'cooper' 
WHERE last_name = 'roper';
 
 
UPDATE employees 
SET last_name = 'roper' 
WHERE last_name = 'cooper';
 
 
UPDATE employees 
SET cooper = 'last_name' 
WHERE last_name = 'roper';
 
 
 	 	 	 	 	 	Correct
 
 
 	 	6.  	Examine the structures of the PRODUCTS and SUPPLIERS tables:
SUPPLIERS 
SUPPLIER_ID NUMBER NOT NULL, Primary Key 
SUPPLIER_NAME VARCHAR2 (25) 
ADDRESS VARCHAR2 (30) 
CITY VARCHAR2 (25) 
REGION VARCHAR2 (10) 
POSTAL_CODE VARCHAR2 (11)
PRODUCTS 
PRODUCT_ID NUMBER NOT NULL, Primary Key 
PRODUCT_NAME VARCHAR2 (25) 
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table 
CATEGORY_ID NUMBER 
QTY_PER_UNIT NUMBER 
UNIT_PRICE NUMBER (7,2) 
QTY_IN_STOCK NUMBER 
QTY_ON_ORDER NUMBER 
REORDER_LEVEL NUMBER
You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?	 Označit pro zhodnocení 
(1) Body 
 
 
DELETE FROM products 
WHERE supplier_id IN 
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');
(*)
 
 
DELETE FROM products 
WHERE UPPER(city) = 'ATLANTA';
 
 
DELETE FROM products 
WHERE supplier_id = 
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');
 
 
DELETE FROM suppliers 
WHERE supplier_id IN 
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ALANTA');
 
 
 	 	 	 	 	 	Correct
 
 
 	 	7.  	Which of the following represents the correct syntax for an INSERT statement?	 Označit pro zhodnocení 
(1) Body 
 
 
INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
 
 
INSERT INTO customers VALUES '3178' 'J.' 'Smith' '123 Main Street' 'Nashville' 'TN' '37777';
 
 
INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777'); (*)
 
 
INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;
 
 
 	 	 	 	 	 	Correct
 
 
 	 	8.  	Which two commands can be used to modify existing data in a database row?	 Označit pro zhodnocení 
(1) Body 
 
 	 	 	(Vyberte všechny správné odpovědi)	 
 
 
DELETE
 
 
MERGE (*)
 
 
SELECT
 
 
UPDATE (*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	9.  	One of your employees was recently married. Her employee ID is still 189, however, her last name is now Rockefeller. Which SQL statement will allow you to reflect this change?	 Označit pro zhodnocení 
(1) Body 
 
 
INSERT INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
 
 
INSERT my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
 
 
UPDATE INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;
 
 
UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; (*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	10.  	When the WHERE clause is missing in a DELETE statement, what is the result?	 Označit pro zhodnocení 
(1) Body 
 
 
All rows are deleted from the table. (*)
 
 
The table is removed from the database.
 
 
An error message is displayed indicating incorrect syntax.
 
 
Nothing. The statement will not execute.
 
 
 	 	 	 	 	 	Correct
 
 
Test: Mid Term Exam Semester 2 - Part II
 
 
Projděte své odpovědi, odezvy a hodnocení otázek dole. Hvězdička (*) značí správnou odpověď.
 
 	Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL also.	 
 
 	Section 7
 	(Odpovězte na všechny otázky v této části)
 
 	 	11.  	What keyword in an UPDATE statement speficies the columns you want to change?	 Označit pro zhodnocení 
(1) Body 
 
 
SELECT
 
 
WHERE
 
 
SET (*)
 
 
HAVING
 
 
 	 	 	 	 	 	Correct
 
 
 	 	12.  	You need to update the expiration date of products manufactured before June 30th . In which clause of the UPDATE statement will you specify this condition?	 Označit pro zhodnocení 
(1) Body 
 
 
The ON clause
 
 
The WHERE clause (*)
 
 
The SET clause
 
 
The USING clause
 
 
 	 	 	 	 	 	Correct
 
 
 	 	13.  	Aliases can be used with MERGE statements. True or False?	 Označit pro zhodnocení 
(1) Body 
 
 
True (*)
 
 
False
 
 
 	 	 	 	 	 	Correct
 
 
 	 	14.  	Multi-table inserts are used when the same source data should be inserted into _____________ target table.	 Označit pro zhodnocení 
(1) Body 
 
 
Ten
 
 
A very large
 
 
More than one (*)
 
 
A data warehouse
 
 
 	 	 	 	 	 	Correct
 
 
 
 
 	Section 6
 	(Odpovězte na všechny otázky v této části)
 
 	 	15.  	The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent. True or False?	 Označit pro zhodnocení 
(1) Body 
 
 
True (*)
 
 
False
 
 
 	 	 	 	 	 	Correct
 
 
 	 	16.  	A correlated subquery will _______ a candidate row from an outer query, _______ the inner query using candidate row value, and _______ values from the inner query to qualify or disqualify the candidate row.	 Označit pro zhodnocení 
(1) Body 
 
 
CREATE; EXECUTE; USE
 
 
DELETE; UPDATE; INSERT
 
 
GET; EXECUTE; USE (*)
 
 
ROLLUP; GRANT; DROP
 
 
 	 	 	 	 	 	Incorrect. Refer to Section 6 Lesson 4.
 
 
 	 	17.  	The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times. True or False?	 Označit pro zhodnocení 
(1) Body 
 
 
True (*)
 
 
False
 
 
 	 	 	 	 	 	Correct
 
 
 	 	18.  	Which statement is false?	 Označit pro zhodnocení 
(1) Body 
 
 
The WITH clause retrieves the results of one or more query blocks
 
 
The WITH clause decreases performance (*)
 
 
The WITH clause makes the query simple to read
 
 
The WITH clause stores the results for the user who runs the query
 
 
 	 	 	 	 	 	Correct
 
 
 	 	19.  	Examine the data in the PAYMENT table:
>>>>
PAYMENT_ID	CUSTOMER_ID	PAYMENT_DATE	PAYMENT_TYPE	PAYMENT_AMOUNT
86590586	8908090	10-JUN-03	BASIC	859.00
89453485	8549038	15-FEB-03	INTEREST	596.00
85490345	5489304	20-MAR-03	BASIC	568.00
This statement fails when executed:
SELECT payment_date, customer_id, payment_amount 
FROM payment 
WHERE payment_id = 
   (SELECT payment_id 
    FROM payment 
    WHERE payment_date >= '05-JAN-2002' OR payment_amount > 500.00);
Which change could correct the problem?	 Označit pro zhodnocení 
(1) Body 
 
 
Remove the subquery WHERE clause.
 
 
Change the outer query WHERE clause to 'WHERE payment_id IN'. (*)
 
 
Include the PAYMENT_ID column in the select list of the outer query.
 
 
Remove the single quotes around the date value in the inner query WHERE clause.
 
 
 	 	 	 	 	 	Correct
 
 
 	 	20.  	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?	 Označit pro zhodnocení 
(1) Body 
 
 
No rows would be returned by the outer query. (*)
 
 
A syntax error in the outer query would be returned.
 
 
A syntax error in the inner query would be returned.
 
 
All the rows in the PLAYER table would be returned by the outer query.
 
 
 	 	 	 	 	 	Correct
 
 
 	Stránka 2 z 5	 	 
 
 
 
 
 
Test: Mid Term Exam Semester 2 - Part II
 
 
Projděte své odpovědi, odezvy a hodnocení otázek dole. Hvězdička (*) značí správnou odpověď.
 
 	Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL also.	 
 
 	Section 6
 	(Odpovězte na všechny otázky v této části)
 
 	 	21.  	Which statement about the ANY operator when used with a multiple-row subquery is true?	 Označit pro zhodnocení 
(1) Body 
 
 
The ANY operator compares every value returned by the subquery. (*)
 
 
The ANY operator can be used with the DISTINCT keyword.
 
 
The ANY operator is a synonym for the ALL operator.
 
 
The ANY operator can be used with the LIKE and IN operators.
 
 
 	 	 	 	 	 	Correct
 
 
 	 	22.  	If a single-row subquery returns a null value and uses the equality comparison operator, what will the outer query return?	 Označit pro zhodnocení 
(1) Body 
 
 
No rows (*)
 
 
All the rows in the table
 
 
A null value
 
 
An error
 
 
 	 	 	 	 	 	Correct
 
 
 	 	23.  	Which statement about the <> operator is true?	 Označit pro zhodnocení 
(1) Body 
 
 
The <> operator is NOT a valid SQL operator.
 
 
The <> operator CANNOT be used in a single-row subquery.
 
 
The <> operator returns the same result as the ANY operator in a subquery.
 
 
The <> operator can be used when a single-row subquery returns only one row. (*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	24.  	Which statement about subqueries is true?	 Označit pro zhodnocení 
(1) Body 
 
 
Subqueries should be enclosed in double quotation marks.
 
 
Subqueries cannot contain group functions.
 
 
Subqueries are often used in a WHERE clause to return values for an unknown conditional value. (*)
 
 
Subqueries generally execute last, after the main or outer query executes.
 
 
 	 	 	 	 	 	Correct
 
 
 	 	25.  	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 representative named Franklin during the year 2001?	 Označit pro zhodnocení 
(1) Body 
 
 
SELECT order_id, total 
FROM ORDERS (SELECT employee_id 
FROM employees 
WHERE last_name = 'Franklin') 
WHERE order_date BETWEEN '01-jan-01' AND '31-dec-01';
 
 
SELECT (SELECT employee_id FROM employees WHERE last_name = 'Franklin') AND order_id, total 
FROM ORDERS 
WHERE order_date BETWEEN '01-jan-01' AND '31-dec-01';
 
 
SELECT order_id, employee_id, total 
FROM ORDERS 
WHERE order_date BETWEEN '01-jan-01' AND '31-dec-01' AND emp_id = 'Franklin';
 
 
SELECT order_id, total 
FROM ORDERS 
WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Franklin') 
AND order_date BETWEEN '01-jan-01' AND '31-dec-01';
(*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	26.  	The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS 
TEACHER_ID NUMBER(5) Primary Key 
NAME VARCHAR2 (25) 
SUBJECT_ID NUMBER(5)
CLASS_ASSIGNMENTS 
CLASS_ID NUMBER (5) Primary Key 
TEACHER_ID NUMBER (5) 
DATE 
MAX_CAPACITY NUMBER (3)
All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries? (Choose two.)	 Označit pro zhodnocení 
(1) Body 
 
 	 	 	(Vyberte všechny správné odpovědi)	 
 
 
SELECT * 
FROM class_assignments 
WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);
(*)
 
 
SELECT * 
FROM teachers 
WHERE teacher_id = (SELECT teacher_id FROM teachers WHERE class_id = 45963);
(*)
 
 
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_capacity > 1000);
 
 
SELECT * 
FROM class_assignments 
max_capacity = (SELECT AVG(max_capacity) FROM class_assignments GROUP BY teacher_id);
 
 
 	 	 	 	 	 	Correct
 
 
 	 	27.  	Which operator can be used with a multiple-row subquery?	 Označit pro zhodnocení 
(1) Body 
 
 
IN (*)
 
 
<>
 
 
=
 
 
LIKE
 
 
 	 	 	 	 	 	Correct
 
 
 	 	28.  	Using a subquery in which clause will return a syntax error?	 Označit pro zhodnocení 
(1) Body 
 
 
WHERE
 
 
FROM
 
 
HAVING
 
 
You can use subqueries in all of the above clauses. (*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	29.  	Which operator can be used with subqueries that return only one row?	 Označit pro zhodnocení 
(1) Body 
 
 
LIKE (*)
 
 
ANY
 
 
ALL
 
 
IN
 
 
 	 	 	 	 	 	Correct
 
 
 	 	30.  	Which of the following is TRUE regarding the order of subquery execution?	 Označit pro zhodnocení 
(1) Body 
 
 
The outer query is executed first
 
 
The subquery executes once after the main query
 
 
The subquery executes once before the main query (*)
 
 
The result of the main query is used with the subquery
 
 
 	 	 	 	 	 	Correct
 
 
 	Stránka 3 z 5	 	 
 
 
 
 
 
Test: Mid Term Exam Semester 2 - Part II
 
 
Projděte své odpovědi, odezvy a hodnocení otázek dole. Hvězdička (*) značí správnou odpověď.
 
 	Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL also.	 
 
 	Section 6
 	(Odpovězte na všechny otázky v této části)
 
 	 	31.  	You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use?	 Označit pro zhodnocení 
(1) Body 
 
 
SELECT product_name 
FROM products 
WHERE cost > (SELECT AVG(cost) 
FROM product);
(*)
 
 
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
 
 
 
 
 	Section 5
 	(Odpovězte na všechny otázky v této části)
 
 	 	32.  	You use ROLLUP to:	 Označit pro zhodnocení 
(1) Body 
 
 
produce subtotal values (*)
 
 
cross-tabulate values
 
 
produce a single result set
 
 
 	 	 	 	 	 	Correct
 
 
 	 	33.  	CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False?	 Označit pro zhodnocení 
(1) Body 
 
 
True (*)
 
 
False
 
 
 	 	 	 	 	 	Correct
 
 
 	 	34.  	You use GROUPING function to:	 Označit pro zhodnocení 
(1) Body 
 
 
Produce subtotal and cross-tabulated values
 
 
Identify the extra row values created by either a ROLLUP or CUBE operation (*)
 
 
Aggregate rows using SUM, MIN, MAX, and COUNT
 
 
 	 	 	 	 	 	Correct
 
 
 	 	35.  	You use GROUPING function to ______ database rows from tabulated rows.	 Označit pro zhodnocení 
(1) Body 
 
 
CREATE
 
 
DISTINGUISH (*)
 
 
COMPUTE
 
 
COUNT
 
 
 	 	 	 	 	 	Correct
 
 
 	 	36.  	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 location. 
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?	 Označit pro zhodnocení 
(1) Body 
 
 
SELECT category_id, SUM(cost * quantity) TOTAL,location_id 
FROM products 
WHERE price > 25.00 
GROUP BY category_id, location_id;
 
 
SELECT SUM(cost * quantity) TOTAL, location_id 
FROM products 
WHERE price < 25.00 <br /> GROUP BY location_id;
 
 
SELECT category_id, SUM(cost * quantity) TOTAL, location_id 
FROM products 
WHERE price < 25.00 <br /> GROUP BY category_id, location_id;
(*)
 
 
SELECT SUM(cost * quantity) TOTAL 
FROM products 
WHERE price < 25.00;
 
 
 	 	 	 	 	 	Correct
 
 
 	 	37.  	The PRODUCTS table contains these columns:
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?	 Označit pro zhodnocení 
(1) Body 
 
 
SELECT prod_cat, MIN (prod_price) 
FROM products 
GROUP BY prod_price;
 
 
SELECT prod_cat, MIN (prod_price) 
FROM products 
GROUP BY prod_cat;
(*)
 
 
SELECT MIN (prod_price), prod_cat 
FROM products 
GROUP BY MIN (prod_price), prod_cat;
 
 
SELECT prod_price, MIN (prod_cat) 
FROM products 
GROUP BY prod_cat;
 
 
 	 	 	 	 	 	Correct
 
 
 	 	38.  	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 three goal keepers. 
Which SELECT statement will produce the desired result?	 Označit pro zhodnocení 
(1) Body 
 
 
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;
 
 
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 
HAVING COUNT(p.player_id) > 3;
 
 
SELECT t.team_name, COUNT(p.player_id) 
FROM players p 
JOIN teams t ON (p.team_id = t.team_id) 
WHERE UPPER(p.position) = 'GOAL KEEPER' 
GROUP BY t.team_name 
HAVING COUNT(p.player_id) > 3;
(*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	39.  	Evaluate this SELECT statement:
SELECT SUM(salary), department_id, manager_id 
FROM employees 
GROUP BY department_id, manager_id;
Which SELECT clause allows you to restrict the rows returned, based on a group function?	 Označit pro zhodnocení 
(1) Body 
 
 
HAVING SUM(salary) > 100000 (*)
 
 
WHERE SUM(salary) > 100000
 
 
WHERE salary > 100000
 
 
HAVING salary > 100000
 
 
 	 	 	 	 	 	Correct
 
 
 	 	40.  	Evaluate this SELECT statement: 
SELECT MAX(salary), department_id 
FROM employees 
GROUP BY department_id;
Which values are displayed?	 Označit pro zhodnocení 
(1) Body 
 
 
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
 
 
 	Stránka 4 z 5	 	 
 
 
 
 
 
Test: Mid Term Exam Semester 2 - Part II
 
 
Projděte své odpovědi, odezvy a hodnocení otázek dole. Hvězdička (*) značí správnou odpověď.
 
 	Semester 2 Mid Term Exam Part II covers Sections 5-7 of Database Programming with SQL also.	 
 
 	Section 5
 	(Odpovězte na všechny otázky v této části)
 
 	 	41.  	Evaluate this SELECT statement:
SELECT COUNT(employee_id), department_id 
FROM employees 
GROUP BY department_id;
You only want to include employees who earn more than 15000. 
Which clause should you include in the SELECT statement?	 Označit pro zhodnocení 
(1) Body 
 
 
WHERE salary > 15000 (*)
 
 
HAVING salary > 15000
 
 
WHERE SUM(salary) > 15000
 
 
HAVING SUM(salary) > 15000
 
 
 	 	 	 	 	 	Correct
 
 
 	 	42.  	What is the correct order of clauses in a SELECT statement?	 Označit pro zhodnocení 
(1) Body 
 
 
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
 
 
 	 	43.  	The EMPLOYEES table contains these columns:
ID_NUMBER NUMBER Primary Key 
NAME VARCHAR2 (30) 
DEPARTMENT_ID NUMBER 
SALARY NUMBER (7,2) 
HIRE_DATE DATE
Evaluate this SQL statement:
SELECT id_number, name, department_id, SUM(salary) 
FROM employees 
WHERE salary > 25000 
GROUP BY department_id, id_number, name 
ORDER BY hire_date;
Why will this statement cause an error?	 Označit pro zhodnocení 
(1) Body 
 
 
The HAVING clause is missing.
 
 
The WHERE clause contains a syntax error.
 
 
The SALARY column is NOT included in the GROUP BY clause.
 
 
The HIRE_DATE column is NOT included in the GROUP BY clause. (*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	44.  	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.	 Označit pro zhodnocení 
(1) Body 
 
 	 	 	(Vyberte všechny správné odpovědi)	 
 
 
SELECT department_id, AVG(salary)
 
 
WHERE job_id <> 69879 (*)
 
 
GROUP BY job_id, department_id
 
 
HAVING AVG(salary) > 35000 (*)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	45.  	Which statement about group functions is true?	 Označit pro zhodnocení 
(1) Body 
 
 
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
 
 
 	 	46.  	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 customer payment was made between January 1, 2003 and June 30, 2003 ?	 Označit pro zhodnocení 
(1) Body 
 
 
SELECT customer_id, COUNT(payment_id) 
FROM payment 
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003' 
GROUP BY customer_id;
(*)
 
 
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
 
 
 	 	47.  	What is the best explanation as to why this SQL statement will NOT execute?
SELECT department_id "Department", AVG (salary)"Average" 
FROM employees 
GROUP BY Department;	 Označit pro zhodnocení 
(1) Body 
 
 
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
 
 
 	 	48.  	The PLAYERS table contains these columns:
PLAYER_ID NUMBER PK 
PLAYER_NAME VARCHAR2 (30) 
TEAM_ID NUMBER 
HIRE_DATE DATE 
SALARY NUMBER (8,2)
Which clauses represent valid uses of aggregate functions? (Choose three.)	 Označit pro zhodnocení 
(1) Body 
 
 	 	 	(Vyberte všechny správné odpovědi)	 
 
 
ORDER BY AVG(salary) (*)
 
 
GROUP BY MAX(salary)
 
 
SELECT AVG(NVL(salary, 0)) (*)
 
 
HAVING MAX(salary) > 10000 (*)
 
 
WHERE hire_date > AVG(hire_date)
 
 
 	 	 	 	 	 	Correct
 
 
 	 	49.  	To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query.	 Označit pro zhodnocení 
(1) Body 
 
 
ONCE; FIRST
 
 
TWICE; FIRST
 
 
ONCE; LAST (*)
 
 
IN ALL; LAST
 
 
 	 	 	 	 	 	Correct
 
 
 	 	50.  	When using SET operators the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False.	 Označit pro zhodnocení 
(1) Body 
 
 
True (*)
 
 
False
 
 
 	 	 	 	 	 	Incorrect. Refer to Section 5 Lesson 3.
 
 
 	Stránka 5 z 5