Langsung ke konten utama

Section 9 Quiz Oracle Database Programming with SQL

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

Komentar

  1. 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 ?



    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';

    BalasHapus
  2. Which of the following are correct SET operators? (choose two)


    UNION, MINUS (*)


    UNION ALL, INTERSECT (*)


    MINUS, PLUS


    UNION ALL, PLUS ALL

    BalasHapus
  3. Komentar ini telah dihapus oleh pengarang.

    BalasHapus
  4. 1- Is the following statement correct?
    SELECT 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. (*)

    BalasHapus
  5. 2. Evaluate this SELECT statement:
    SELECT 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

    BalasHapus
  6. 7. What is the correct order of the clauses in a SELECT statement?
    Mark 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

    BalasHapus
  7. 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?

    (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; (*)

    BalasHapus
  8. What is the correct order of clauses in a SELECT statement?
    Mark 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

    BalasHapus
  9. 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
    Also contact him +1 (505) 317-3340…

    BalasHapus
  10. 9. Can group functions be nested at a depth of?

    Group functions cannot be nested.
    Four
    Two (*)
    Three

    BalasHapus
  11. 10. You want to write a report that returns the average salary of all employees in the company, sorted by departments.
    The 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;

    BalasHapus
  12. 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?

    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; (*)

    BalasHapus
  13. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
    SELECT COUNT(last_name), grade, gender
    FROM STUDENTS
    GROUP_BY ?????;

    grade, gender (*)
    last_name, grade
    last_name
    last_name, gender

    BalasHapus
  14. 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?

    WHERE salary > 15000 (*)
    HAVING salary > 15000
    HAVING SUM(salary) > 15000
    WHERE SUM(salary) > 15000

    BalasHapus
  15. 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 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);

    BalasHapus
  16. Which of the following SQL statements could display the number of people with the same last name:
    SELECT 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;

    BalasHapus
  17. 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?

    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; (*)

    BalasHapus
  18. Group functions can be nested to a depth of?
    Two (*)
    Four
    Group functions cannot be nested.
    Three

    BalasHapus
  19. Evaluate this SELECT statement:
    SELECT 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

    BalasHapus
  20. Evaluate this SELECT statement:
    SELECT 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.

    BalasHapus
  21. Saya punya pesan penting mengenai bagaimana saya memperoleh pinjaman dari pemberi pinjaman yang memiliki reputasi baik setelah pengalaman yang sulit dengan orang-orang yang melakukan penipuan. Pemberi pinjaman Best Loan membantu saya memperoleh pinjaman, dan saya sangat menyarankan untuk menghubungi mereka jika Anda membutuhkan bantuan keuangan. Anda dapat menghubungi mereka melalui email di pedroloanss@gmail.com atau WhatsApp di +393510140339. Jangan ragu untuk menghubungi mereka dan mengucapkan terima kasih kepada saya nanti. Percayalah, mereka tidak akan mengecewakan Anda. Terima kasih. - Nazgul William.

    BalasHapus

Posting Komentar

Postingan populer dari blog ini

Section 6 Quiz Oracle Database Programming with SQL

Section 6 Quiz             (Answer all questions in this section)                                                             1.         Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee ï¾’ s possible minimum and maximum salaries based on their job title? EMPLOYEES Table: Name   Null?    Type EMPLOYEE_ID          NOT NULL     NUMBER (6) FIRST_NAME             VARCHAR2 (20) LAST_NAME  NOT NULL     VARCHAR2 (25) EMAIL NOT NULL     VARCHAR2 (25) PHONE_NUMBER                  VARCHAR2 (20) HIRE_DATE   NOT NULL     DATE JOB_ID           NOT NULL     VARCHAR2 (10) SALARY                     NUMBER (8,2) COMMISSION_PCT                NUMBER (2,2) MANAGER_ID                       NUMBER (6) DEPARTMENT_ID                 NUMBER (4) JOBS Table: Name   Null?    Type JOB_ID           NOT NULL     VARCHAR2 (10) JOB_TITLE     NOT NULL     VARCHAR2 (35) MIN_SALARY                      

Section 10 Quiz Database Programming With SQL

Section 10 Quiz             (Answer all questions in this section) 1.         A multiple-row operator expects how many values?   Mark for Review (1) Points             One or more (*)             Only one             Two or more             None 2.         The salary column of the f_staffs table contains the following values: 4000 5050 6000 11000 23000 Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?  Mark for Review (1) Points             SELECT last_name, first_name FROM f_staffs WHERE salary IN (SELECT last_name, first_name FROM f_staffs WHERE salary <5000 o:p="">             SELECT last_name, first_name FROM f_staffs WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000);             SELECT last_name, first_name FROM f_staffs WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)             SELEC

Section 6 Quiz Database Design Oracle

Section 6 Quiz             (Answer all questions in this section) 1.         Examine the following Entity and decide which rule of Normal Form is being violated: ENTITY: CLIENT ATTRIBUTES:     # CLIENT ID     FIRST NAME     LAST NAME     STREET     CITY     ZIP CODE  Mark for Review (1) Points             1st Normal Form.             2nd Normal Form.             3rd Normal Form.             None of the above, the entity is fully normalised. (*) 2.         A transitive dependency exists when any attribute in an entity is dependent on any other non-UID attribute in that entity.            Mark for Review (1) Points             True (*)             False 3.         When any attribute in an entity is dependent on any other non-UID attribute in that entity, this is known as:        Mark for Review (1) Points             Functional dependency             Dependency             Transitive dependency (*)             Non-dependency