Database Programming Section 9 Quiz - Oracle iLearning

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
                   
           
    GROUP BY ALL COLUMNS
   
           
    HAVING
   
           
    ROLLUP
   
           
    CUBE (*)
   
                   
               
[Correct]         Correct
   
                   
        2.     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
   
                   
               
[Correct]         Correct
   
                   
        3.     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.
   
                   
               
[Correct]         Correct
   
                   
        4.     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
   
                   
               
[Correct]         Correct
   
                   
        5.     CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups.     Mark for Review
(1) Points
                   
           
    WHERE
   
           
    SELECT
   
           
    GROUP BY (*)
   
                   
               
[Correct]         Correct
     (Answer all questions in this section)
                   
        6.     You use ROLLUP to:     Mark for Review
(1) Points
                   
           
    produce subtotal values (*)
   
           
    cross-tabulate values
   
           
    produce a single result set
   
                   
               
[Correct]         Correct
   
                   
        7.     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 in each department (*)
   
           
    The highest salary for all employees
   
           
    The employees with the highest salaries
   
           
    The employee with the highest salary for each department
   
                   
               
[Correct]         Correct
   
                   
        8.     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
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
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 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, 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]         Correct
   
                   
        9.     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 latest hire date in the EMPLOYEES table
   
           
    The earliest hire date in each department (*)
   
           
    The earliest hire date in the EMPLOYEES table
   
           
    The hire dates in the EMPLOYEES table that contain NULL values
   
                   
               
[Correct]         Correct
   
                   
        10.     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 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_price;

   
           
    SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;

(*)
   
                   
               
[Correct]         Correct
     (Answer all questions in this section)
                   
        11.     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(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;

(*)
   
           
    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
EXISTS COUNT(last_name) > 1;

   
           
    SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;

   
                   
               
[Correct]         Correct
   
                   
        12.     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 SUM(salary) > 15000
   
           
    HAVING salary > 15000
   
           
    WHERE salary > 15000 (*)
   
           
    WHERE SUM(salary) > 15000
   
                   
               
[Incorrect]         Incorrect. Refer to Section 9 Lesson 1.
   
                   
        13.     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 (*)
   
                   
               
[Correct]         Correct
   
                   
        14.     The ___________ operator returns all rows from both tables, after eliminating duplicates.     Mark for Review
(1) Points
                   
           
    UNION ALL
   
           
    UNION (*)
   
           
    MINUS
   
           
    INTERSECT
   
                   
               
[Correct]         Correct
   
                   
        15.     INTERSECT will give you the common rows found in both queries. (True or False?)     Mark for Review
(1) Points
                   
           
    True (*)
   
           
    False
   

Oracle Ilearning for Database Design

Pelajar yang menyelesaikan Desain & Pemrograman Database Oracle Academy dengan kursus SQL dapat sebagai gantinya (atau juga) memilih untuk mempersiapkan dan mengikuti ujian sertifikasi SQL Server SQL Expert. Namun, ujian Oracle Database SQL Expert berisi topik tambahan yang akan membutuhkan lebih banyak belajar mandiri dan berlatih sendiri untuk mempersiapkan.



Membutuhkan penyelesaian yang berhasil sebelum Ujian Sertifikasi Oracle Database SQL Expert, Pengantar Oracle Ujian Sertifikasi SQL, atau Oracle Database : Ujian Sertifikasi SQL Fundamentals.



Di atas adalah soal dan jawaban kuis oracle ilearning - oracle academy, sebagai penambah ilmu pengetahuan untuk kita semua, yang pada intinya jangan lupa untuk dipahami dan dikuasai.

Pembahasan didalam Oracle Ilearning - Oracle Academy

• Oracle Ilearning for Database Design


• Oracle Ilearning for Course Resources


• Oracle Ilearning for Introduction


• Oracle Ilearning for Entities and Attributes


• Oracle Ilearning for Relationship Basics


• Oracle Ilearning for Super/Sub Types and Business Rules


• Oracle Ilearning for Relationship Fundamentals


• Oracle Ilearning for UIDs and Normalization


• Oracle Ilearning for Arcs, Hierarchies, and Recursive Modeling


• Oracle Ilearning for Changes and Historical Modeling


• Oracle Ilearning for Mapping


• Oracle Ilearning for Creating Database Projects


• Oracle Ilearning for Presenting Database Projects


• Oracle Ilearning quiz answers for Database Design English Quizzes and Exams


• Oracle Ilearning quiz answers for Database Design Midterm Exam


• Oracle ilearning quiz answers for Database Design Final Exam

Selain Sebagai Penyedia Panduan Belajar Database dan Tutorial Pemrograman, Kami Juga Membagikan Kumpulan Source Code Program Aplikasi dan Ebook Pemrograman Terlengkap yang Bisa Anda Dapatkan Secara Gratis di Halaman :