Section
9 Quiz
(Answer all questions in this
section)
1. If you want to include subtotals and
grand totals for all columns mentioned in a GROUP BY clause, you should use
which of the following extensions to the GROUP BY clause? Mark for Review
(1)
Points
HAVING
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
2. Examine the following statement:
SELECT
department_id, manager_id, job_id, SUM(salary)
FROM
employees
GROUP
BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What
data will this query generate?
Mark for Review
(1)
Points
Sum of salaries for (department_id,
job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id,
job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
3. Examine the following statement:
SELECT
department_id, manager_id, job_id, SUM(salary)
FROM
employees
GROUP
BY ROLLUP(department_id, manager_id)
What
extra data will this query generate?
Mark for Review
(1)
Points
Subtotals for department_id, and
grand totals for salary
Subtotals for department_id, job_id
and grand totals for salary
Subtotals for department_id, job_id,
manager_id and grand totals for salary
The statement will fail. (*)
4. You use ROLLUP to: Mark for Review
(1)
Points
produce subtotal values (*)
cross-tabulate values
produce a single result set
5. 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
6. 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
7. Which of the following are correct SET
operators? (choose two) Mark for
Review
(1)
Points
(Choose all
correct answers)
MINUS, PLUS
UNION, MINUS (*)
UNION ALL, PLUS ALL
UNION ALL, INTERSECT (*)
8. The ___________ operator returns all
rows from both tables, after eliminating duplicates. Mark for Review
(1)
Points
UNION ALL
UNION (*)
MINUS
INTERSECT
9. 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.
Mark for Review
(1)
Points
ONCE; LAST (*)
IN ALL; LAST
ONCE; FIRST
TWICE; FIRST
10. If
a select list contains both a column as well as a group function then what
clause is required? Mark for
Review
(1)
Points
HAVING clause
GROUP BY clause (*)
ORDER BY clause
JOIN clause
11. Is
the following statement correct?
SELECT
first_name, last_name, salary, department_id, COUNT(employee_id)
FROM
employees
WHERE
department_id = 50
GROUP
BY last_name, first_name, department_id;
Mark for Review
(1)
Points
Yes
No, because the statement is missing
salary in the GROUP BY clause (*)
Yes, because Oracle will correct any
mistakes in the statement itself
No, beause you cannot have a
WHERE-clause when you use group functions.
12. What
will the following SQL Statement do?
SELECT
job_id, COUNT(*)
FROM
employees
GROUP
BY job_id;
Mark for Review
(1)
Points
Displays each job id and the number
of people assigned to that job id (*)
Displays all the jobs with as many
people as there are jobs
Displays only the number of job_ids
Displays all the employees and
groups them by job
13. 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.)
Mark for Review
(1) Points
(Choose all
correct answers)
SELECT AVG(NVL(salary, 0)) (*)
GROUP BY MAX(salary)
ORDER BY AVG(salary) (*)
WHERE hire_date > AVG(hire_date)
HAVING MAX(salary) > 10000 (*)
14. 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 hire dates in the EMPLOYEES
table that contain NULL values
The latest hire date in the
EMPLOYEES table
The earliest hire date in each department
(*)
The earliest hire date in the
EMPLOYEES table
15. 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?
Mark for Review
(1)
Points
The HAVING clause is missing.
The SALARY column is NOT included in
the GROUP BY clause.
The WHERE clause contains a syntax
error.
The HIRE_DATE column is NOT included
in the GROUP BY clause. (*)
1. Which statement about group functions is
true? Mark for Review
(1)
Points
Group functions can only be used in
a SELECT list.
A query that includes a group
function in the SELECT list must include a GROUP BY clause.
Group functions ignore null values.
(*)
Group functions can be used in a
WHERE clause.
2. 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;
Mark for Review
(1)
Points
The GROUP BY clause must have
something to GROUP.
Salaries cannot be averaged as not
all the numbers will divide evenly.
You cannot use a column alias in the
GROUP BY clause. (*)
The department id is not listed in
the departments table.
3. 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?
Mark for Review
(1)
Points
SELECT prod_price, MIN (prod_cat)
FROM
products
GROUP
BY prod_cat;
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;
4. 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)
GROUP BY job_id, department_id
WHERE job_id <> 69879 (*)
SELECT department_id, AVG(salary)
HAVING AVG(salary) > 35000 (*)
5. 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?
Mark for Review
(1)
Points
SELECT SUM(cost * quantity) TOTAL
FROM
products
WHERE
price < 25.00;
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
GROUP
BY location_id;
SELECT category_id, SUM(cost *
quantity) TOTAL, location_id
FROM
products
WHERE
price < 25.00
GROUP
BY category_id, location_id;
(*)
6. 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?
Mark for Review
(1)
Points
HAVING salary > 15000
HAVING SUM(salary) > 15000
WHERE salary > 15000 (*)
WHERE SUM(salary) > 15000
7. You use GROUPING functions to: Mark for Review
(1)
Points
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
8. Examine the following statement:
SELECT
department_id, manager_id, job_id, SUM(salary)
FROM
employees
GROUP
BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What
data will this query generate?
Mark for Review
(1)
Points
Sum of salaries for (department_id,
job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id,
job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
9. CUBE will cross-reference the columns
listed in the ______ clause to create a superset of groups. Mark for Review
(1)
Points
SELECT
GROUP BY (*)
WHERE
10. You
use ROLLUP to: Mark for Review
(1)
Points
cross-tabulate values
produce subtotal values (*)
produce a single result set
11. GROUPING
SETS is another extension to the GROUP BY clause 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
12. If
you want to include subtotals and grand totals for all columns mentioned in a
GROUP BY clause, you should use which of the following extensions to the GROUP
BY clause? Mark for Review
(1)
Points
ROLLUP
GROUP BY ALL COLUMNS
HAVING
CUBE (*)
13. Which
of the following are correct SET operators? (choose two) Mark for Review
(1)
Points
(Choose all
correct answers)
UNION, MINUS (*)
UNION ALL, PLUS ALL
MINUS, PLUS
UNION ALL, INTERSECT (*)
14. The
difference between UNION and UNION ALL is
Mark for Review
(1)
Points
There is no difference; you get
exactly the same result from both.
UNION ALL is more like a NATURAL
JOIN.
UNION will remove duplicates; UNION
ALL returns all rows from all queries including the duplicates. (*)
UNION is a synomym for UNION ALL.
15. When
using SET operators, the names of the matching columns must be identical in all
of the SELECT statements used in the query. True or False? Mark for Review
(1)
Points
True
False (*)
1. How would you alter the following query
to list only employees where two or more employees have the same last name?
SELECT
last_name, COUNT(employee_id)
FROM
EMPLOYEES
GROUP
BY last_name;
Mark for Review
(1)
Points
SELECT last_name, COUNT(employee_id)
FROM
EMPLOYEES
WHERE
COUNT(*) > 1
GROUP
BY last_name
SELECT last_name, COUNT(last_name)
FROM
EMPLOYEES
GROUP
BY last_name
HAVING
COUNT(last_name) > 1;
(*)
SELECT last_name, COUNT(last_name)
FROM
EMPLOYEES
GROUP
BY last_name
EXISTS
COUNT(last_name) > 1;
SELECT employee_id,
DISTINCT(last_name)
FROM EMPLOYEES
GROUP
BY last_name
HAVING
last_name > 1;
2. Which statement about the GROUP BY
clause is true? Mark for
Review
(1)
Points
To exclude rows before dividing them
into groups using the GROUP BY clause, you should use a WHERE clause. (*)
You can use a column alias in a
GROUP BY clause.
You must use the HAVING clause with
the GROUP BY clause.
By default, rows are not sorted when
a GROUP BY clause is used.
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 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?
Mark for Review
(1)
Points
SELECT SUM(cost * quantity) TOTAL,
location_id
FROM
products
WHERE
price < 25.00
GROUP
BY location_id;
SELECT category_id, SUM(cost *
quantity) TOTAL,location_id
FROM
products
WHERE
price > 25.00
GROUP
BY category_id, location_id;
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
FROM
products
WHERE
price < 25.00;
4. 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?
Mark for Review
(1)
Points
HAVING salary > 100000
HAVING SUM(salary) > 100000 (*)
WHERE salary > 100000
WHERE SUM(salary) > 100000
5. 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?
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
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;
(*)
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;
6. 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)
WHERE job_id <> 69879 (*)
SELECT department_id, AVG(salary)
HAVING AVG(salary) > 35000 (*)
GROUP BY job_id, department_id
7. Examine the following statement:
SELECT
department_id, manager_id, job_id, SUM(salary)
FROM
employees
GROUP
BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What
data will this query generate?
Mark for Review
(1)
Points
Sum of salaries for (department_id,
job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id,
job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
8. You use GROUPING functions to ______
database rows from tabulated rows.
Mark for Review
(1)
Points
COMPUTE
COUNT
DISTINGUISH (*)
CREATE
9. 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, manager_id), (department_id, job_id), (manager_id, job_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, salary), (department_id, job_id), (department_id, manager_id)
10. 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
11. GROUPING
SETS is another extension to the GROUP BY clause 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
12. If
you want to include subtotals and grand totals for all columns mentioned in a
GROUP BY clause, you should use which of the following extensions to the GROUP
BY clause? Mark for Review
(1)
Points
HAVING
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
13. The
___________ operator returns all rows from both tables, after eliminating
duplicates. Mark for Review
(1)
Points
MINUS
UNION ALL
UNION (*)
INTERSECT
14. MINUS
will give you rows from the first query that are not present in the second
query. (True or False?) Mark
for Review
(1)
Points
True (*)
False
15. 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. Mark for Review
(1)
Points
True (*)
False
The PAYMENT table contains these columns:
ReplyDeletePAYMENT_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 ?
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'
GROUP BY customer_id;
(*)
SELECT COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-Jan-2003' AND '30-Jun-2003'
GROUP BY customer_id;
SELECT customer_id, COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-Jan-2003' AND '30-Jun-2003';
Which of the following are correct SET operators? (choose two)
ReplyDeleteUNION, MINUS (*)
UNION ALL, INTERSECT (*)
MINUS, PLUS
UNION ALL, PLUS ALL
Artikel terkait bisnis online :
ReplyDelete1. Bisnis dan Pekerjaan Online Terbaik
2. Pemahaman Bisnis Online bersama Facebook
3. Pemahaman Bisnis Online bersama Google
4. Panduan Bisnis Bitcoin & Cryptocurrency
5. Panduan Memulai Trading Forex
6. Panduan Memulai Trading Saham
This comment has been removed by the author.
ReplyDelete1- Is the following statement correct?
ReplyDeleteSELECT department_id, AVG(salary)
FROM employees;
No, because the AVG function cannot be used on the salary column
No, because a GROUP BY department_id clause is needed (*)
Yes, because the SELECT clause can contain both individual columns and group functions
Yes
2- Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Subtotals for department_id, and grand totals for salary
Subtotals for department_id, job_id and grand totals for salary
Subtotals for department_id, job_id, manager_id and grand totals for salary
The statement will fail. (*)
2. Evaluate this SELECT statement:
ReplyDeleteSELECT SUM(salary), department_id, department_name
FROM employees
WHERE department_id = 1
GROUP BY department;
Which clause of the SELECT statement contains a syntax error?
Mark for Review
(1) Points
WHERE
GROUP BY (*)
FROM
SELECT
7. What is the correct order of the clauses in a SELECT statement?
ReplyDeleteMark for Review
(1) Points
SELECT
FROM
HAVING
GROUP BY
WHERE
ASK FOR
SELECT
FROM
WHERE
GROUP BY
HAVING
ASK FOR (*)
SELECT
FROM
WHERE
HAVING
ASK FOR
GROUP BY
SELECT
FROM
WHERE
ASK FOR
GROUP BY
HAVING
The MANUFACTURER table contains these columns:
ReplyDeleteMANUFACTURER_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?
(1/1) Points
SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer
GROUP BY type;
SELECT location_id, COUNT(type)
FROM manufacturer
GROUP BY location_id;
SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer;
SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer
GROUP BY location_id; (*)
What is the correct order of clauses in a SELECT statement?
ReplyDeleteMark for Review
(1) Points
(*) SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT
FROM
HAVING
GROUP BY
WHERE
ORDER BY
SELECT
FROM
WHERE
HAVING
ORDER BY
GROUP BY
SELECT
FROM
WHERE
ORDER BY
GROUP BY
HAVING
one year ago was really a tough year for me,my wife and my family . I have never been the type to believe in online platforms to make money, I have always believed in rendering services and adding apparent values to anything and then making money from it. But when COVID struck,my business had to shut down,then I was left with no choice but to think of other legit ways to make money . I was presented a lucrative offer in ‘FX INVEST OPTION’ since it was the only thing that was making sense as at the time I wanted to invest. All thanks to = GLOBALFXINVESTMENT2@GMAIL.COM, I cannot thank them enough,one of my business associates referred me to them when he heard of my situation after reaching out to loan some money from him ,I contacted them via their email,and related necessary information and requirements as requested by the specialist who asked for all the necessary details and in less than a week ,they were able to retrieve all I lost to this fake investors,It seemed like a dream to me because I had lost all hope. I told them I will tell the whole world about what they did for me,they literally restored me to life. If you’re seeking funds/Cryptocurrency from online scammers , contact them rightly away;- at GLOBALFXINVESTMENT2@GMAIL.COM and also his Telegram @FX_INVEST_OPTION
ReplyDeleteAlso contact him +1 (505) 317-3340…
9. Can group functions be nested at a depth of?
ReplyDeleteGroup functions cannot be nested.
Four
Two (*)
Three
10. You want to write a report that returns the average salary of all employees in the company, sorted by departments.
ReplyDeleteThe EMPLOYEES table contains the following columns:
EMPLOYEES:
EMP_ID NUMBER(10) PRIMARY KEY
LNAME VARCHAR2(20)
FNAME VARCHAR2(20)
DEPT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
Which SELECT statement will return the information that you require?
(0/1) Points
SELECT AVG salary
FROM employees
BY dept;
SELECT dept, AVG(salary)
FROM employees
GROUP BY dept; (*)
SELECT AVG (salary)
FROM employees
BY dept;
SELECT salary(AVG), dept
FROM employees
GROUP BY dept;
The PRODUCTS table contains these columns:
ReplyDeletePROD_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?
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;
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;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat; (*)
Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
ReplyDeleteSELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????;
grade, gender (*)
last_name, grade
last_name
last_name, gender
Evaluate this SELECT statement:
ReplyDeleteSELECT 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?
WHERE salary > 15000 (*)
HAVING salary > 15000
HAVING SUM(salary) > 15000
WHERE SUM(salary) > 15000
The EMPLOYEES table contains the following columns:
ReplyDeleteEMPLOYEE_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 identification number, date of hire, and salary. The report should include only those employees who have been with the company for more than one year and whose salary exceeds $40,000.
Which of the following SELECT statements will accomplish this task?
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE salary > 40000
AND (sysdate-hire_date) / 365 > 1; (*)
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 40000
AND hire_date IN (sysdate-hire_date) / 365 > 1);
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE salary > 40000 AND hire_date =
(SELECT hire_date
FROM employees
WHERE (sysdate-hire_date) / 365 > 1);
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);
Which of the following SQL statements could display the number of people with the same last name:
ReplyDeleteSELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name; (*)
SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
The PLAYERS and TEAMS tables contain these columns:
ReplyDeletePLAYERS
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?
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
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;
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; (*)
Group functions can be nested to a depth of?
ReplyDeleteTwo (*)
Four
Group functions cannot be nested.
Three
Evaluate this SELECT statement:
ReplyDeleteSELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The highest salary in each department (*)
The employees with the highest salaries
The employee with the highest salary for each department
The highest salary for all employees
Evaluate this SELECT statement:
ReplyDeleteSELECT COUNT(emp_id), mgr_id, dept_id
FROM employees
WHERE status = 'I'
GROUP BY dept_id
HAVING salary > 30000
ORDER BY 2;
Why does this statement return a syntax error?
The HAVING clause must specify an aggregate function.
MGR_ID must be included in the GROUP BY clause. (*)
The ORDER BY clause must specify a column name in the EMPLOYEE table.
A single query cannot contain a WHERE clause and a HAVING clause.