Langsung ke konten utama

Section 8 Quiz Oracle Database Programming with SQL

Section 8 Quiz
            (Answer all questions in this section)
                                                           
1.         The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?    Mark for Review
(1) Points

            All except numeric
            Only numeric data types (*)
            Integers only
            Any data type

2.         Which aggregate function can be used on a column of the DATE data type?  Mark for Review
(1) Points

            MAX (*)
            STDDEV
            AVG
            SUM

3.         The VENDORS table contains these columns:
VENDOR_ID NUMBER Primary Key
NAME VARCHAR2(30)
LOCATION_ID NUMBER
ORDER_DT DATE
ORDER_AMOUNT NUMBER(8,2)

Which two clauses represent valid uses of aggregate functions for this table?

 Mark for Review
(1) Points
                                    (Choose all correct answers)   

            SELECT SUM(order_dt)
            FROM MAX(order_dt)
            SELECT SUM(order_amount) (*)
            WHERE MAX(order_dt) = order_dt
            SELECT MIN(AVG(order_amount)) (*)

4.         Group functions return a value for ________________ and ________________ null values in their computations.  Mark for Review
(1) Points

            a row set, ignore (*)
            each row, include
            each row, ignore
            a row set, include

5.         Which group function would you use to display the lowest value in the SALES_AMOUNT column?            Mark for Review
(1) Points

            COUNT
            MAX
            AVG
            MIN (*)

6.         Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)

What is the result of the following statement:

SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);

 Mark for Review
(1) Points

            SUM = 1.85 and COUNT =4
            SUM = 1.85 and COUNT = 6
            SUM = .85 and COUNT = 4
            SUM = .85 and COUNT = 6 (*)

7.         The TRUCKS table contains these columns:
TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)

Which SELECT statement will return the average price for the 4x4 model?

 Mark for Review
(1) Points

            SELECT AVG(price), model
FROM trucks
WHERE model IS '4x4';

            SELECT AVG(price)
FROM trucks
WHERE model IS 4x4;

            SELECT AVG(price)
FROM trucks
WHERE model IS '4x4';

            SELECT AVG(price)
FROM trucks
WHERE model = '4x4';
(*)

8.         You need to compute the total salary amount for all employees in department 10. Which group function will you use?   Mark for Review
(1) Points

            COUNT
            SUM (*)
            VARIANCE
            MAX

9.         Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;

Which statement is true?

 Mark for Review
(1) Points

            The number of rows in the table is displayed. (*)
            An error occurs due to an error in the SELECT clause.
            An error occurs because no WHERE clause is included in the SELECT statement.
            The number of unique PRODUCT_IDs in the table is displayed.

10.       The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
SALARY NUMBER(7,2)
DEPARTMENT_ID NUMBER(9)

You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

 Mark for Review
(1) Points

            SELECT COUNT(*)
FROM employees
WHERE salary < 50000;

            SELECT * FROM employees
WHERE salary < 50000;

            SELECT COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY employee_id, last_name, first_name, salary, department_id;

            SELECT COUNT(*)
FROM employees
WHERE salary > 50000;
(*)

            SELECT * FROM employees
WHERE salary > 50000;

11.       Evaluate this SQL statement:
SELECT COUNT (amount)
FROM inventory;

What will occur when the statement is issued?

 Mark for Review
(1) Points

            The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

            The statement will return the total number of rows in the AMOUNT column.

            The statement will replace all NULL values that exist in the AMOUNT column.
            The statement will return the greatest value in the INVENTORY table.

12.       To include null values in the calculations of a group function, you must:       Mark for Review
(1) Points

            Group functions can never use null values
            Convert the null to a value using the NVL( ) function (*)
            Count the number of null values in that column using COUNT
            Precede the group function name with NULL

13.       Which statement about the COUNT function is true?  Mark for Review
(1) Points

            The COUNT function always ignores null values by default. (*)
            The COUNT function can be used to determine the number of unique, non-null values in a column.
            The COUNT function can be used to find the maximum value in each column.
            The COUNT function ignores duplicates by default.

14.       Which SELECT statement will calculate the number of rows in the PRODUCTS table?          Mark for Review
(1) Points

            SELECT ROWCOUNT FROM products;
            SELECT COUNT(products);
            SELECT COUNT (*) FROM products; (*)
            SELECT COUNT FROM products;

15.       Using your existing knowledge of the employees table, would the following two statements produce the same result?
SELECT COUNT(*)
FROM employees;

SELECT COUNT(commission_pct)
FROM employees;
 Mark for Review
(1) Points

            The second statement is invalid
            The first statement is invalid
            Yes
            No (*)

1.         The TRUCKS table contains these columns:
TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)

Which SELECT statement will return the average price for the 4x4 model?

 Mark for Review
(1) Points

            SELECT AVG(price), model
FROM trucks
WHERE model IS '4x4';

            SELECT AVG(price)
FROM trucks
WHERE model IS 4x4;

            SELECT AVG(price)
FROM trucks
WHERE model IS '4x4';

            SELECT AVG(price)
FROM trucks
WHERE model = '4x4';
(*)

2.         Which group function would you use to display the highest salary value in the EMPLOYEES table? Mark for Review
(1) Points

            MIN
            MAX (*)
            AVG
            COUNT

3.         You need to compute the total salary amount for all employees in department 10. Which group function will you use?   Mark for Review
(1) Points

            SUM (*)
            MAX
            VARIANCE
            COUNT

4.         You need to calculate the average salary of employees in each department. Which group function will you use?          Mark for Review
(1) Points

            AVG (*)
            AVERAGE
            MEDIAN
            MEAN

5.         Examine the data in the PAYMENT table:
PAYMENT_ID            CUSTOMER_ID          PAYMENT_DATE      PAYMENT_TYPE            PAYMENT_AMOUNT
86590586        8908090          10-Jun-2003    BASIC 859.00
89453485        8549038          15-Feb-2003    INTEREST       596.00
85490345        5489304          20-Mar-2003   BASIC 568.00
You need to determine the average payment amount made by each customer in January, February, and March of 2003.
Which SELECT statement should you use?

 Mark for Review
(1) Points

            SELECT AVG(payment_amount)
FROM payment
WHERE payment_date
BETWEEN '01-Jan-2003' AND '31-Mar-2003';
(*)

            SELECT AVG(payment_amount)
FROM payment;

            SELECT AVG(payment_amount)
FROM payment
WHERE TO_CHAR(payment_date) IN (Jan, Feb, Mar);

            SELECT SUM(payment_amount)
FROM payment
WHERE payment_date BETWEEN '01-Jan-2003' and '31-Mar-2003';

6.         Which group function would you use to display the lowest value in the SALES_AMOUNT column?            Mark for Review
(1) Points

            MIN (*)
            MAX
            COUNT
            AVG

7.         The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?    Mark for Review
(1) Points

            Integers only
            Only numeric data types (*)
            Any data type
            All except numeric

8.         Which group function would you use to display the total of all salary values in the EMPLOYEES table?            Mark for Review
(1) Points

            COUNT
            MAX
            AVG
            SUM (*)

9.         Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;

Which statement is true?

 Mark for Review
(1) Points

            The number of unique PRODUCT_IDs in the table is displayed.
            An error occurs due to an error in the SELECT clause.
            The number of rows in the table is displayed. (*)
            An error occurs because no WHERE clause is included in the SELECT statement.

10.       Evaluate this SQL statement:
SELECT COUNT (amount)
FROM inventory;

What will occur when the statement is issued?

 Mark for Review
(1) Points

            The statement will return the greatest value in the INVENTORY table.
            The statement will replace all NULL values that exist in the AMOUNT column.
            The statement will return the total number of rows in the AMOUNT column.
            The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null. (*)

11.       Group functions can avoid computations involving duplicate values by including which keyword?   Mark for Review
(1) Points

            DISTINCT (*)
            SELECT
            UNLIKE
            NULL

12.       The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
SALARY NUMBER(7,2)
DEPARTMENT_ID NUMBER(9)

You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

 Mark for Review
(1) Points

            SELECT * FROM employees
WHERE salary > 50000;

            SELECT COUNT(*)
FROM employees
WHERE salary > 50000;
(*)

            SELECT * FROM employees
WHERE salary < 50000;

            SELECT COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY employee_id, last_name, first_name, salary, department_id;

            SELECT COUNT(*)
FROM employees
WHERE salary < 50000;

13.       Which statement about the COUNT function is true?  Mark for Review
(1) Points

            The COUNT function always ignores null values by default. (*)
            The COUNT function can be used to find the maximum value in each column.
            The COUNT function ignores duplicates by default.
            The COUNT function can be used to determine the number of unique, non-null values in a column.

14.       Which SELECT statement will calculate the number of rows in the PRODUCTS table?          Mark for Review
(1) Points

            SELECT COUNT FROM products;
            SELECT ROWCOUNT FROM products;
            SELECT COUNT (*) FROM products; (*)
            SELECT COUNT(products);

15.       The STYLES table contains this data:
STYLE_ID       STYLE_NAME            CATEGORY    COST
895840            SANDAL         85940  12.00
968950            SANDAL         85909  10.00
869506            SANDAL         89690  15.00
809090            LOAFER          89098  10.00
890890            LOAFER          89789  14.00
857689            HEEL   85940  11.00
758960            SANDAL         86979 
You issue this SELECT statement:

SELECT COUNT(category)
FROM styles;

Which value is displayed?

 Mark for Review
(1) Points

            7 (*)
            6
            The statement will NOT execute successfully.

            0

Komentar

  1. The EMPLOYEES table contains these columns:
    EMPLOYEE_ID NUMBER(9)
    LAST_NAME VARCHAR2(20)
    FIRST_NAME VARCHAR2(20)
    SALARY NUMBER(7,2)
    DEPARTMENT_ID NUMBER(9)

    You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

    Mark for Review
    (1) Points


    SELECT * FROM employees
    WHERE salary < 50000;


    SELECT COUNT(*)
    FROM employees
    WHERE salary > 50000;
    (*)



    SELECT COUNT(*)
    FROM employees
    WHERE salary < 50000;


    SELECT COUNT(*)
    FROM employees
    WHERE salary > 50000
    GROUP BY employee_id, last_name, first_name, salary, department_id;


    SELECT * FROM employees
    WHERE salary > 50000;

    BalasHapus
  2. Given the following data in the employees table (employee_id, salary, commission_pct)
    DATA: (143, 2600, null
    144, 2500, null
    149, 10500, .2
    174, 11000, .3
    176, 8600, .2
    178, 7000, .15)

    What is the result of the following statement:

    SELECT SUM(commission_pct), COUNT(commission_pct)
    FROM employees
    WHERE employee_id IN( 143,144,149,174,176,178);

    Mark for Review
    (1) Points


    SUM = 1.85 and COUNT = 4


    SUM = .85 and COUNT = 4 (*)


    SUM = 1.85 and COUNT = 6


    SUM = .85 and COUNT = 6

    BalasHapus
  3. I cannot thank lemeridian funding service enough and letting people know how grateful I am for all the assistance that you and your team staff have provided and I look forward to recommending friends and family should they need financial advice or assistance @ 1,9% Rate for Business Loan .Via Contact : . lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. Keep up the great work.
    Thanks, Busarakham.

    BalasHapus
  4. 6. What would the following SQL statement return?
    SELECT COUNT(DISTINCT salary)
    FROM employees;

    Mark for Review

    (1) Points
    The number of unique salaries in the employees table (*)
    The total number of rows in the employees table
    The total amount of salaries in the employees table
    A listing of all unique salaries in the employees table

    BalasHapus
  5. 8. The PRODUCTS table contains these columns:
    PROD_ID NUMBER(4)
    PROD_NAME VARCHAR2(30)
    PROD_CAT VARCHAR2(30)
    PROD_PRICE NUMBER(3)
    PROD_QTY NUMBER(4)

    The following statement is issued:

    SELECT AVG(prod_price, prod_qty)
    FROM products;

    What happens when this statement is issued?

    Mark for Review

    (1) Points
    Only the average quantity of the products is returned.
    The values in the PROD_PRICE column and the PROD_QTY column are averaged together.
    Both the average price and the average quantity of the products are returned.
    An error occurs. (*)

    BalasHapus
  6. 10. You can use GROUP functions in all clauses of a SELECT statement. True or False?
    Mark for Review

    (1) Points
    True
    False (*)

    BalasHapus
  7. 11. Given the following data in the employees table (employee_id, salary, commission_pct)
    DATA: (143, 2600, null
    144, 2500, null
    149, 10500, .2
    174, 11000, .3
    176, 8600, .2
    178, 7000, .15)

    What is the result of the following statement:

    SELECT AVG(commission_pct)
    FROM employees
    WHERE employee_id IN( 143,144,149,174,176,178);

    Mark for Review

    (1) Points
    1.2125
    0.0425
    This statement is invalid
    0.2125 (*)

    BalasHapus
  8. 11. Which group function would you use to display the average price of all products in the PRODUCTS table?
    Mark for Review

    (1) Points
    COUNT
    AVG (*)
    SUM
    MAX

    12. What two group functions can be used with any datatype?
    Mark for Review

    (1) Points
    MIN, MAX (*)
    COUNT, SUM
    STDDEV, VARIANCE
    SUM, AVG

    14. You need to calculate the standard deviation for the cost of products produced in the Birmingham facility. Which group function will you use?
    Mark for Review

    (1) Points
    STDDEV (*)
    STDEV
    VARIANCE
    VAR_SAMP

    BalasHapus
  9. Komentar ini telah dihapus oleh pengarang.

    BalasHapus
  10. What would the following SQL statement return?
    SELECT SUPERIOR (name)
    Of employees
    Mark for Review

    (1) Points
    A listing of all unique names in the employees table
    Employees of the total number of rows in the table
    A listing of all non-null names in the employees table
    The total number of non-null names in the employees table (*)

    BalasHapus
  11. 13. Which of the following group functions acts on character, numeric, and date data types? (Select all correct options).
    Mark for review

    (1) Points
    MAX (*)
    AVG
    MIN (*)
    TELL (*)
    SUM

    BalasHapus
  12. 15. Debe calcular la desviación estándar para el costo de los productos fabricados en Birmingham. ¿Qué función de grupo utilizará?
    Marcar para revisión

    (1) Puntos
    VAR_SAMP
    STDEV
    DIFERENCIA
    STDDEV (*)

    BalasHapus
  13. 4. Evaluate this SELECT statement:
    SELECT COUNT(*)
    FROM employees
    WHERE salary > 30000;

    What result will the query show?

    Mark for Review

    (1) Points

    The number of rows in the EMPLOYEES table that have a salary greater than 30,000

    The number of employees with a salary of less than 30,000

    The query generates an error and returns no results.

    The total in the SALARY column for all employees with a salary greater than 30,000


    help me I don't know the answer

    BalasHapus
    Balasan
    1. The number of rows in the EMPLOYEES table that have a salary greater than 30,000

      Hapus
  14. Evalúe esta sentencia SELECT:SELECT COUNT(*)FROM employeesWHERE salary > 30000;

    ¿Qué resultado mostrará la consulta?

    Marcar para Revisión
    (1) Puntos

    El número de empleados que tengan un salario inferior a 30.000

    El número de filas de la tabla EMPLOYEES que tengan un salario superior a 30.000 (*)

    La consulta genera un error y no devuelve resultados.

    El total de la columna SALARY para todos los empleados que tengan un salario superior a 30.000

    BalasHapus
  15. Komentar ini telah dihapus oleh pengarang.

    BalasHapus
  16. The CUSTOMER table contains these columns:
    CUSTOMER_ID NUMBER(9)
    FIRST_NAME VARCHAR2(25)
    LAST_NAME VARCHAR2(30)
    CREDIT_LIMIT NUMBER (7,2)
    CATEGORY VARCHAR2(20)

    You need to calculate the average credit limit for all the customers in each category. The average should be calculated based on all the rows in the table excluding any customers who have not yet been assigned a credit limit value.
    Which group function should you use to calculate this value?

    Mark for Review

    (1) Points
    SUM
    STDDEV
    COUNT
    AVG (*)

    BalasHapus
  17. Examine the data from the LINE_ITEM table:
    LINE_ITEM_ID ORDER_ID PRODUCT_ID PRICE DISCOUNT
    890898 847589 848399 8.99 0.10
    768385 862459 849869 5.60 0.05
    867950 985490 945809 5.60
    954039 439203 438925 5.25 0.15
    543949 349302 453235 4.50
    You query the LINE_ITEM table and a value of 5 is returned. Which SQL statement did you execute?

    Mark for Review

    (1) Points
    SELECT COUNT(discount)
    FROM line_item;
    SELECT SUM(discount)
    FROM line_item;
    SELECT COUNT(*)
    FROM line_item;(*)
    SELECT AVG(discount)
    FROM line_item

    BalasHapus
  18. Evaluate this SELECT statement:
    SELECT COUNT(*)
    FROM employees
    WHERE salary > 30000;

    Which result will the query display?
    Mark for Review
    (1) Points

    The number of rows in the EMPLOYEES table that have a salary greater than 30000 (*)
    The query generates an error and returns no results.
    The number of employees that have a salary less than 30000
    The total of the SALARY column for all employees that have a salary greater than 30000

    BalasHapus
  19. I want to share a testimony of how Mr. Pedro helped me with a $ 2,000,000.00 loan to fund my marijuana cultivation project, I am very grateful and promised to share this legitimate funding company with anyone looking for a way to expand their business project. . financing company. Anyone seeking financial support should contact them at pedroloanss@gmail.com.

    BalasHapus
  20. 4. Given the following data in the employees table (employee_id, salary, commission_pct)
    DATA: (143, 2600, null
    144, 2500, null
    149, 10500, .2
    174, 11000, .3
    176, 8600, .2
    178, 7000, .15)

    What is the result of the following sentence?

    SELECT SUM (commission_pct), COUNT (commission_pct)
    FROM employees
    WHERE employee_id IN (143,144,149,174,176,178);


    SUM = .85 and COUNT = 6
    SUM = .85 and COUNT = 4 (*)
    SUM = 1.85 and COUNT = 4
    SUM = 1.85 and COUNT = 6


    7. Which statement is true about the COUNT function?

    The COUNT function ignores duplicates by default.
    The COUNT function can be used to determine the number of unique non-null values in a column.
    The COUNT function always ignores null values by default. (*)
    The COUNT function can be used to find the maximum value in each column.

    BalasHapus
  21. The EMPLOYEES table contains these columns:
    EMPLOYEE_ID NUMBER(9)
    LAST_NAME VARCHAR2(20)
    FIRST_NAME VARCHAR2(20)
    SALARY NUMBER(9,2)
    HIRE_DATE DATE
    BONUS NUMBER(7,2)
    COMM_PCT NUMBER(4,2)

    Which three functions could be used with the HIRE_DATE, LAST_NAME, or SALARY columns? (Choose three.)

    (Choose all correct answers)
    SUM
    AVG
    MIN (*)
    MAX (*)
    COUNT (*)

    BalasHapus
  22. Which group functions below act on character, number, and date data types? (Choose three)
    (Choose all correct answers)
    COUNT (*)
    AVG
    SUM
    MIN (*)
    MAX (*)

    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