Langsung ke konten utama

Midterm Exam Database Programming With SQL

Section 1
                (Answer all questions in this section)
1.            The SELECT statement retrieves information from the database. In a SELECT statement, you can do all of the following EXCEPT:            Mark for Review
(1) Points
                Joining
                Selection
                Projection
                Manipulation (*)

2.            In the default order of precedence, which operator would be evaluated first?   Mark for Review
(1) Points
                                               
                Multiplications and Divisions are at the same level and would be evaluated first based on left to right order (*)
                                               
                Subtractions and Additions are at the same level and would be evaluated first based on left to right order
                                               
                Divisions and Subtractions are at the same level and would be evaluated first based on left to right order

                Additions and Multiplications are at the same level and would be evaluated first based on left to right order

3.            You cannot use computers unless you completely understand exactly how they work. True or False?     Mark for Review
(1) Points
                True
                False (*)

4.            There is only one kind of software used by all computers. True or False?               Mark for Review
(1) Points
                True
                False (*)

5.            Databases are used in most countries and by most governments. Life, as we know it, would change drastically if we no longer had access to databases. True or False?            Mark for Review
(1) Points
                True (*)
                False

Section 2
                (Answer all questions in this section)
6.            The EMPLOYEES table includes these columns:
EMPLOYEE_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
HIRE_DATE DATE NOT NULL

You want to produce a report that provides the last names, first names, and hire dates of those employees who were hired between March 1, 2000, and August 30, 2000. Which statements can you issue to accomplish this task?

 Mark for Review
(1) Points
                                                                               
                SELECT last_name, first_name, hire_date
FROM employees
AND hire_date >= '01-Mar-2000' and hire_date <= '30-Aug-2000';

                SELECT last_name, first_name, hire_date
FROM employees
GROUP BY hire_date >= '01-Mar-2000' and hire_date <= '30- Aug-2000';

                SELECT last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '30-Aug-2000' AND '01-Mar-2000';

                SELECT last_name, first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '01-Mar-2000' AND '30-Aug-2000';
(*)

7.            When using the "LIKE" operator, the % and _ symbols can be used to do a pattern-matching, wild card search. True or False?    Mark for Review
(1) Points
                True (*)
                False

8.            Which SELECT statement will display both unique and non-unique combinations of the MANAGER_ID and DEPARTMENT_ID values from the EMPLOYEES table?     Mark for Review
(1) Points
                SELECT manager_id, department_id FROM employees; (*)
                SELECT manager_id, DISTINCT department_id FROM employees;
                SELECT manager_id, department_id DISTINCT FROM employees;
                SELECT DISTINCT manager_id, department_id FROM employees;

9.            You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which keyword should you include in the WHERE clause of your SELECT statement to achieve the desired result?            Mark for Review
(1) Points
                BETWEEN
                AND
                IN
                LIKE (*)

10.          Which of the following elements cannot be included in a WHERE clause?               Mark for Review
(1) Points
                A constant
                A column name
                A column alias (*)
                A comparison condition

Section 2
                (Answer all questions in this section)

11.          You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use?               Mark for Review
(1) Points
                "=>"
                >
                !=
                >= (*)

Section 3
                (Answer all questions in this section)
12.          What value will the following SQL statement return?
SELECT employee_id
FROM employees
WHERE employee_id BETWEEN 100 AND 150
   OR employee_id IN(119, 175, 205)
   AND (employee_id BETWEEN 150 AND 200);
 Mark for Review
(1) Points
                200, 201, 202, 203, 204, 205, 206
                100, 101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149 (*)
                19
                No rows will be returned

13.          Which SELECT statement should you use to limit the display of product information to those products with a price of less than 50?      Mark for Review
(1) Points

                SELECT product_id, product_name
FROM products
WHERE price < 50;
(*)

                SELECT product_id, product_name
FROM products
WHERE price <= 50;

                SELECT product_id, product_name
FROM products
HAVING price < 50;

                SELECT product_id, product_name
FROM products
GROUP BY price < 50;

                SELECT product_id, product_name
FROM products
WHERE price < 50.00
GROUP BY price;

14.          Which clause would you include in a SELECT statement to sort the rows returned by the LAST_NAME column?               Mark for Review
(1) Points
                ORDER BY (*)
                WHERE
                HAVING
                FROM

15.          Will the following statement return one row?
SELECT MAX(salary), MIN(Salary), AVG(SALARY)
FROM employees;

 Mark for Review
(1) Points
                Yes, it will return the highest salary from each employee.
                Yes, it will return the average salary from the employees table.

                Yes, it will return the highest salary, the lowest salary, and the average salary from all employees. (*)

                No, it is illegal. You cannot use more than one multi-row function in a SELECT statement.

Section 4
                (Answer all questions in this section)
16.          ROUND and TRUNC functions can be used with which of the following Datatypes?           Mark for Review
(1) Points
                Dates and numbers (*)
                Dates and characters
                Numbers and characters
                None of the above

17.          You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue? Mark for Review
(1) Points

                SELECT orderid, total
FROM orders
WHERE order_date IN ( 01-Jan-2002 , 31-Jan-2002 )
ORDER BY total;

                SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '31-Jan-2002' AND '01-Jan-2002'
ORDER BY total DESC;

                SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;
(*)

                SELECT orderid, total
FROM orders
WHERE order_date LIKE '01-Jan-2002' AND '31-Jan-2002'
ORDER BY total DESC;

18.          The PRICE table contains this data:
PRODUCT_ID     MANUFACTURER_ID
86950    59604

You query the database and return the value 95. Which script did you use?

 Mark for Review
(1) Points

                SELECT TRIM(product_id, -3, 2)
FROM price
WHERE manufacturer_id = 59604;

                SELECT LENGTH(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;

                SELECT SUBSTR(product_id, -1, 3)
FROM price
WHERE manufacturer_id = 59604;

                SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;
(*)

19.          You query the database with this SQL statement:
SELECT LOWER(SUBSTR(CONCAT(last_name, first_name)), 1, 5) "ID"
FROM employee;

In which order are the functions evaluated?

 Mark for Review
(1) Points
                LOWER, CONCAT, SUBSTR
                CONCAT, SUBSTR, LOWER (*)
                LOWER, SUBSTR, CONCAT
                SUBSTR, CONCAT, LOWER

                Section 5
                (Answer all questions in this section)

20.          CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False? Mark for Review
(1) Points
                True (*)
                False

Section 5
                (Answer all questions in this section)
21.          When executed, which statement displays a zero if the TUITION_BALANCE value is zero and the HOUSING_BALANCE value is null?            Mark for Review
(1) Points

                SELECT NVL (tuition_balance + housing_balance, 0) "Balance Due"
FROM student_accounts;
(*)

                SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER (housing_balance, 0), tutition_balance + housing_balance "Balance Due"
FROM student_accounts;

                SELECT NVL(tuition_balance, 0), NVL (housing_balance), tuition_balance + housing_balance "Balance Due"
FROM student_accounts;

                SELECT tuition_balance + housing_balance
FROM student_accounts;

22.          You need to replace null values in the DEPT_ID column with the text N/A. Which functions should you use?                Mark for Review
(1) Points
                TO_CHAR and NULLIF
                TO_NUMBER and NULLIF
                TO_CHAR and NVL (*)
                TO_CHAR and NULL

23.          If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result?      Mark for Review
(1) Points
                1917
                1901
                2017 (*)
                2001

24.          The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
SALARY NUMBER(6)
You need to create a report to display the salaries of all employees. Which SQL Statement should you use to display the salaries in format: "$45,000.00"?

 Mark for Review
(1) Points

                SELECT TO_NUM(salary, '$999,999.00')
FROM employees;

                SELECT TO_CHAR(salary, '$999,999')
FROM employees;

                SELECT TO_NUM(salary, '$999,990.99')
FROM employees;

                SELECT TO_CHAR(salary, '$999,999.00')
FROM employees;
(*)

25.          Which best describes the TO_CHAR function?    Mark for Review
(1) Points

                The TO_CHAR function can be used to remove text from column data that will be returned by the database.

                The TO_CHAR function can be used to display dates and numbers according to formatting conventions that are supported by Oracle. (*)

                The TO_CHAR function can be used to specify meaningful column names in an SQL statement's result set.

                The TO_CHAR function can only be used on Date columns.

Section 6
                (Answer all questions in this section)

26.          Which SELECT statement implements a self join?              Mark for Review
(1) Points

                SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id (+);

                SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id;

                SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id =! t.product_id;

                SELECT p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id;
(*)

27.          Hierarchical queries MUST use the LEVEL pseudo column. True or False?               Mark for Review
(1) Points
                True
                False (*)

28.          Below find the structures of the PRODUCTS and VENDORS tables:
PRODUCTS
PRODUCT_ID NUMBER
PRODUCT_NAME VARCHAR2 (25)
VENDOR_ID NUMBER
CATEGORY_ID NUMBER

VENDORS
VENDOR_ID NUMBER
VENDOR_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

You want to create a query that will return an alphabetical list of products, including the product name and associated vendor name, for all products that have a vendor assigned.

Which two queries could you use?

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

                SELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (vendor_id)
ORDER BY p.product_name;
(*)

                SELECT p.product_name, v.vendor_name
FROM products p
LEFT OUTER JOIN vendors v
ON p.vendor_id = v.vendor_id
ORDER BY p.product_name;

                SELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
USING (p.vendor_id)
ORDER BY p.product_name;

                SELECT p.product_name, v.vendor_name
FROM products p
JOIN vendors v
ON (vendor_id)
ORDER BY p.product_name;

                SELECT p.product_name, v.vendor_name
FROM products p
NATURAL JOIN vendors v
ORDER BY p.product_name;
(*)

29.          You need to join all the rows in the EMPLOYEES table to all the rows in the EMP_REFERENCE table. Which type of join should you create? Mark for Review
(1) Points
                A cross join (*)
                An inner join
                An equijoin
                A full outer join

30.          Which of the following conditions will cause an error on a NATURAL JOIN?           Mark for Review
(1) Points
                If it selects rows from the two tables that have equal values in all matched columns.
                When you attempt to write it as an equijoin.

                If the columns having the same names have different data types, then an error is returned. (*)

                When the NATURAL JOIN clause is based on all columns in the two tables that have the same name.

Section 6
                (Answer all questions in this section)

31.          What is another name for a simple join or an inner join?                Mark for Review
(1) Points
                Equijoin (*)
                Outer Join
                Self Join
                Nonequijoin

32.          The following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;

 Mark for Review
(1) Points
                Equijoin
                Outer Join (*)
                Inner Join
                Optimal Join
                                                               
Section 7
                (Answer all questions in this section)

33.          Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;

 Mark for Review
(1) Points
                No, Oracle will not allow joins in the WHERE clause
                Yes, Oracle will resolve which department_id colum comes from which table.
                No, Oracle will return a Column Ambiguously Defined error. (*)
                Yes, there are no syntax errors in that statement

34.          When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause?                Mark for Review
(1) Points
                3
                0
                2 (*)
                1

35.          Evaluate this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

Which join is evaluated first?

 Mark for Review
(1) Points
                The join between the player table and the team table on TEAM_ID
                The join between the player table and the team table on MANAGER_ID
                The join between the player table and the team table on PLAYER_ID
                The self-join of the player table (*)

Section 8
                (Answer all questions in this section)

36.          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.

37.          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)
                SELECT MIN(AVG(order_amount)) (*)
                WHERE MAX(order_dt) = order_dt
                FROM MAX(order_dt)
                SELECT SUM(order_amount) (*)

38.          Which group function would you use to display the highest salary value in the EMPLOYEES table?             Mark for Review
(1) Points
                COUNT
                MIN
                MAX (*)
                AVG

Section 9
                (Answer all questions in this section)
39.          The difference between UNION and UNION ALL is          Mark for Review
(1) Points

                UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)

                UNION is a synomym for UNION ALL.
                UNION ALL is more like a NATURAL JOIN.
                There is no difference; you get exactly the same result from both.

40.          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, salary), (department_id, job_id), (department_id, manager_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, manager_id), (department_id, job_id), (manager_id, job_id)) (*)

Section 9
                (Answer all questions in this section)
41.          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

42.          Group functions can be nested to a depth of?    Mark for Review
(1) Points
                Group functions cannot be nested.
                Two (*)
                Three
                Four

43.          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 the EMPLOYEES table
                The earliest hire date in each department (*)
                The hire dates in the EMPLOYEES table that contain NULL values

44.          Which statement about the GROUP BY clause is true?    Mark for Review
(1) Points
                By default, rows are not sorted when a GROUP BY clause is used.

                To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)

                You must use the HAVING clause with the GROUP BY clause.
                You can use a column alias in a GROUP BY clause.

Section 10
                (Answer all questions in this section)
45.          Which of the following best describes the meaning of the ANY operator?             Mark for Review
(1) Points
                Equal to any member in the list
                Compare value to each value returned by the subquery (*)
                Equal to each value in the list
                Compare value to the first value returned by the subquery

Section 10
                (Answer all questions in this section)
46.          Which statement about the ANY operator, when used with a multiple-row subquery, is true?    Mark for Review
(1) Points
                The ANY operator can be used with the DISTINCT keyword.
                The ANY operator can be used with the LIKE and IN operators.
                The ANY operator is a synonym for the ALL operator.
                The ANY operator compares every value returned by the subquery. (*)

47.          Evaluate this SELECT statement:
SELECT player_id, name
FROM players
WHERE team_id IN
    (SELECT team_id
     FROM teams
     WHERE team_id > 300 AND salary_cap > 400000);

What would happen if the inner query returned a NULL value?

 Mark for Review
(1) Points
                No rows would be returned by the outer query. (*)
                A syntax error in the inner query would be returned.
                A syntax error in the outer query would be returned.
                All the rows in the PLAYER table would be returned by the outer query.

48.          Which comparison operator can only be used with a single-row subquery?          Mark for Review
(1) Points
                ALL
                ANY
                <> (*)
                IN

49.          Which operator can be used with a multiple-row subquery?        Mark for Review
(1) Points
                IN (*)
                <>
                =
                LIKE

50.          Table aliases must be used when you are writing correlated subqueries. (True or false?)               Mark for Review
(1) Points
                True

                False (*)

Komentar

  1. You want to create a list of all albums that have been produced by the company. The list should include the title of the album, the artist's name, and the date the album was released. The ALBUMS table includes the following columns:
    ALB_TITLE VARCHAR2(150) NOT NULL
    ALB_ARTIST VARCHAR2(150) NOT NULL
    ALB_DATE DATE NOT NULL

    Which statement can you use to retrieve the necessary information?

    Mark for Review
    (1) Points


    SELECT alb_title, alb_artist, alb_dates
    FROM album;


    SELECT *
    FROM albums;


    SELECT alb_title; alb_artist; alb_date
    FROM albums;*


    SELECT alb_title, alb_artist, alb_dates
    FROM albums;

    BalasHapus
  2. A Relational Database generally contains two or more tables. True or False? Mark for Review
    (1) Points


    True*


    False

    BalasHapus
  3. The following is a valid SQL SELECT statement. True or False?
    SELECT first_name || ' ' || last_name alias AS Employee_Name
    FROM employees:

    Mark for Review
    (1) Points


    True


    False*

    BalasHapus
  4. You need to display employees whose salary is in the range of 30000 and 50000. Which comparison operator should you use? Mark for Review
    (1) Points


    IS NULL


    IN


    LIKE


    BETWEEN...AND...*

    BalasHapus
  5. Which of the following are examples of comparison operators used in the WHERE clause? Mark for Review
    (1) Points


    =, >, <, <=, >=, <>


    between ___ and ___


    in (..,..,.. )


    like


    is null


    All of the above*

    BalasHapus
  6. Evaluate this SELECT statement:
    SELECT last_name, first_name, salary
    FROM employees;

    How will the heading for the FIRST_NAME column appear in the display by default in Oracle Application Express?

    Mark for Review
    (1) Points


    The heading will display with the first character capitalized and left justified.


    The heading will display as uppercase and left justified.


    The heading will display with the first character capitalized and centered.


    The heading will display as uppercase and centered.*

    BalasHapus
  7. A column alias can be specified in an ORDER BY Clause. True or False? Mark for Review
    (1) Points


    True*


    False

    BalasHapus
  8. Which of the following best describes the meaning of the LIKE operator? Mark for Review
    (1) Points


    Match a character pattern.*


    To find Null values.


    To test for values in a list.


    Display rows based on a range of values.

    BalasHapus
  9. Which query would return a user password combining the ID of an employee and the first 4 digits of the last name? Mark for Review
    (1) Points


    SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))
    AS "User Passwords"
    FROM employees (*)


    SELECT CONCAT (employee_id, INSTR(last_name,4,1))
    AS "User Passwords"
    FROM employees


    SELECT CONCAT (employee_id, SUBSTR(last_name,4,1))
    AS "User Passwords"
    FROM employees


    SELECT CONCAT (employee_id, INSTR(last_name,1,4))
    AS "User Passwords"
    FROM employees

    BalasHapus
  10. What does the following SQL SELECT statement return?
    SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
    FROM dual;

    Mark for Review
    (1) Points


    Programming


    Database


    PROGRAMMING (*)


    DATABASE

    BalasHapus
  11. You need to subtract three months from the current date. Which function should you use? Mark for Review
    (1) Points


    ROUND


    MONTHS_BETWEEN


    ADD_MONTHS (*)


    TO_DATE

    BalasHapus
  12. Consider the following data in the Employees table: (last_name, commission_pct, manager_id)
    DATA:
    King, null, null
    Kochhar, null, 100
    Vargas, null, 124
    Zlotkey, .2, 100
    What is the result of the following statement:
    SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
    FROM employees ;

    Mark for Review
    (1) Points


    King, -1
    Kochhar, 100
    Vargas, 124
    Zlotkey, 100


    King, null
    Kochhar, 100
    Vargas, 124
    Zlotkey, .2


    Statement will fail


    King, -1
    Kochhar, 100
    Vargas, 124
    Zlotkey, .2 (*)

    BalasHapus
  13. If quantity is a number datatype, what is the result of this statement?
    SELECT NVL(200/quantity, 'zero') FROM inventory; Mark for Review
    (1) Points


    zero


    ZERO


    Null


    The statement fails (*)

    BalasHapus
  14. CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False? Mark for Review
    (1) Points


    True (*)


    False

    BalasHapus
  15. Which SQL Statement should you use to display the prices in this format: "$00.30"? Mark for Review
    (1) Points


    SELECT TO_CHAR(price, '$99,900.99') (*)
    FROM product;


    SELECT TO_CHAR(price, '$99,990.99')
    FROM product;


    SELECT TO_NUMBER(price, '$99,900.99')
    FROM product;


    SELECT TO_CHAR(price, '$99,999.99')
    FROM product;

    BalasHapus
  16. Which statement is true about SQL functions? Mark for Review
    (1) Points


    Functions can convert values or text to another data type.


    Functions can round a number to a specified decimal place.


    Functions can convert upper case characters to lower case characters.


    a, b and c are true. (*)


    None of the above statements are true.

    BalasHapus
  17. Which query represents the correct syntax for a left outer join? Mark for Review
    (1) Points


    SELECT companyname, orderdate, total
    FROM customers c
    LEFT OUTER orders o
    ON c.cust_id = o.cust_id;


    SELECT companyname, orderdate, total
    FROM customers c
    OUTER JOIN orders o
    ON c.cust_id = o.cust_id;


    SELECT companyname, orderdate, total
    FROM customers c
    LEFT OUTER JOIN orders o
    ON c.cust_id = o.cust_id; (*)


    SELECT companyname, orderdate, total
    FROM customers c
    LEFT JOIN orders o
    ON c.cust_id = o.cust_id;

    BalasHapus
  18. A join between tables where the result set includes matching values from both tables but does NOT return any unmatched rows could be called which of the following? (Choose three) Mark for Review
    (1) Points

    (Choose all correct answers)


    Simple join (*)


    Full outer join


    Nonequijoin


    Self join (*)


    Equijoin (*)

    BalasHapus
  19. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? Mark for Review
    (1) Points


    True


    False (*)


    You can do nonequi-joins with ANSI-Syntax. True or False? Mark for Review
    (1) Points


    True (*)


    False

    BalasHapus
  20. Which SELECT statement implements a self join? Mark for Review
    (1) Points


    SELECT e.employee_id, m.manager_id
    FROM employees e
    NATURAL JOIN employees m;


    SELECT e.employee_id, m.manager_id
    FROM employees e, departments m
    WHERE e.employee_id = m.manager_id;


    SELECT e.employee_id, m.manager_id
    FROM employees e, managers m
    WHERE e.employee_id = m.manager_id;


    SELECT e.employee_id, m.manager_id
    FROM employees e, employees m
    WHERE m.employee_id = e.manager_id; (*)

    BalasHapus
  21. You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task? Mark for Review
    (1) Points


    SELECT last_name, first_name, salary
    FROM employees
    WHERE salary <= 25000 AND dept_id = 10;


    SELECT last_name, first_name, salary
    FROM employees
    WHERE salary != 25000 AND dept_id = 10; (*)


    SELECT last_name, first_name, salary
    FROM employees
    WHERE salary > 25000 AND dept_id = 10;


    SELECT last_name, first_name, salary
    FROM employees
    WHERE salary = 25000 AND dept_id = 10;

    BalasHapus
  22. You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
    Which query should you issue? Mark for Review
    (1) Points


    SELECT e.first_name, e.last_name, s.sales
    FROM employees e, sales s
    WHERE e.employee_id = s.employee_id AND revenue > 100000;


    SELECT e.first_name, e.last_name, s.sales
    FROM employees e, sales s
    WHERE e.employee_id = s.employee_id AND revenue >= 100000; (*)


    SELECT e.first_name, e.last_name, s.sales
    FROM employees, sales
    WHERE e.employee_id = s.employee_id AND revenue >= 100000;


    SELECT first_name, last_name, sales
    FROM employees e, sales s
    WHERE e.employee_id = s.employee_id AND revenue > 100000;

    BalasHapus
  23. 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.)

    Mark for Review
    (1) Points

    (Choose all correct answers)


    COUNT (*)


    AVG


    SUM


    MAX (*)


    MIN (*)

    BalasHapus
  24. The basic storage structure in a Relational Database is a _________: Mark for Review
    (1) Points


    Row


    Table (*)


    Field


    Key

    BalasHapus
  25. The basic storage structure in a Relational Database is a _________: Mark for Review
    (1) Points


    Row


    Table (*)


    Field


    Key

    BalasHapus
  26. You cannot use computers unless you completely understand exactly how they work. True or False? Mark for Review
    (1) Points


    True


    False (*)

    BalasHapus
  27. What command can be used to create a new row in a table in the database? Mark for Review
    (1) Points


    NEW


    ADD


    CREATE


    INSERT (*)

    BalasHapus
  28. Which SQL keyword specifies that an alias will be substituted for a column name in the output of a SQL query? Mark for Review
    (1) Points


    AS (*)


    AND


    SUBSTITUTE


    OR

    BalasHapus
  29. The EMPLOYEES table contains these columns:
    SALARY NUMBER(7,2)
    BONUS NUMBER(7,2)
    COMMISSION_PCT NUMBER(2,2)

    All three columns contain values greater than zero.
    There is one row of data in the table and the values are as follows:
    Salary = 500, Bonus = 50, Commission_pct = .5

    Evaluate these two SQL statements:

    1.
    SELECT salary + bonus + commission_pct * salary - bonus AS income
    FROM employees;

    2.
    SELECT (salary + bonus ) + commission_pct * (salary - bonus) income
    FROM employees;

    What will be the result?

    Mark for Review
    (1) Points


    Statement 1 will display a different column heading.


    Statement 2 will return a higher value than statement 1. (*)


    One of the statements will NOT execute.


    Statement 1 will return a higher value than statement 2.

    BalasHapus
  30. The concatenation operator ... Mark for Review
    (1) Points


    Brings together columns or character strings into other columns


    Creates a resultant column that is a character expression


    Is represented by two vertical bars ( || )


    All of the above (*)

    BalasHapus
  31. Komentar ini telah dihapus oleh pengarang.

    BalasHapus
  32. Which of the following elements cannot be included in a WHERE clause? Mark for Review
    (1) Points


    A column alias (*)


    A column name


    A comparison condition


    A constant

    BalasHapus
  33. Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"? Mark for Review
    (1) Points


    SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp;


    SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*)


    SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees;


    SELECT last_name||","|| job_id "Employee and Title" FROM employees;

    BalasHapus
  34. Which example would limit the number of rows returned? Mark for Review
    (1) Points


    SELECT title FROM d_songs WHERE type_code = = 88;


    SELECT title FROM d_songs WHERE type_code = 88; (*)


    SELECT title FROM d_songs WHEN type_code = 88;


    SELECT title FROM d_songs WHEN type_code = = 88;

    BalasHapus
  35. Which of the following are examples of comparison operators used in the WHERE clause? Mark for Review
    (1) Points


    =, >, <, <=, >=, <>


    between ___ and ___


    in (..,..,.. )


    like


    is null


    All of the above (*)

    BalasHapus
  36. Which two statements would select salaries that are greater than or equal to 2500 and less than or equal to 3500? (Choose two) Mark for Review
    (1) Points

    (Choose all correct answers)


    WHERE salary >= 2500 AND salary <= 3500 (*)


    WHERE salary <=2500 AND salary >= 3500


    WHERE salary BETWEEN 3500 AND 2500


    WHERE salary BETWEEN 2500 AND 3500 (*)

    BalasHapus
  37. The function COUNT is a single row function. True or False? Mark for Review
    (1) Points


    True


    False (*)

    BalasHapus
  38. Which of the following would be returned by this SQL statement:
    SELECT First_name, last_name, department_id
    FROM employees
    WHERE department_id IN(50,80)
    AND first_name LIKE ' C% '
    OR last_name LIKE ' %s% '

    Mark for Review
    (1) Points



    FIRST_NAME LAST_NAME DEPARTMENT_ID
    Shelly Higgins 110



    FIRST_NAME LAST_NAME DEPARTMENT_ID
    Curtis Davies 50



    FIRST_NAME LAST_NAME DEPARTMENT_ID
    Randall Matos 50



    FIRST_NAME LAST_NAME DEPARTMENT_ID
    Michael Hartstein 20


    All of the above (*)

    BalasHapus
  39. What clause must you place in a SQL statement to have your results sorted from highest to lowest salary? Mark for Review
    (1) Points


    ORDER BY salary ASC


    None, the database always sorts from highest to lowest on the salary column.


    ORDER BY salary DESC (*)


    ORDER salary BY DESC

    BalasHapus
  40. Evaluate this SELECT statement:
    SELECT employee_id, last_name, first_name, salary 'Yearly Salary'
    FROM employees
    WHERE salary IS NOT NULL
    ORDER BY last_name, 3;

    Which clause contains an error?

    Mark for Review
    (1) Points


    SELECT employee_id, last_name, first_name, salary 'Yearly Salary' (*)


    FROM employees


    WHERE salary IS NOT NULL


    ORDER BY last_name, 3;

    BalasHapus
  41. What is the result of the following query?
    SELECT ADD_MONTHS ('11-Jan-1994',6)
    FROM dual; Mark for Review
    (1) Points


    17-Jan-1994


    11-Jul-1994 (*)


    17-Jul-1994


    11-Jan-1995

    BalasHapus
  42. Character functions accept character arguments and only return character values. True or False? Mark for Review
    (1) Points


    True


    False (*)

    BalasHapus
  43. Which of the following SQL statements would correctly return a song title identified in the database as "All These Years"? Mark for Review
    (1) Points


    WHERE title IN('All','These','Years');


    WHERE title CONTAINS 'Years';


    WHERE title LIKE INITCAP('%all these years'); (*)


    WHERE title LIKE LOWER('all these years');

    BalasHapus
  44. You issue this SQL statement:
    SELECT TRUNC(751.367,-1) FROM dual;
    Which value does this statement display?

    Mark for Review
    (1) Points


    751


    700


    751.3


    750 (*)

    BalasHapus
  45. Which of the following is a conditional expression used in SQL? Mark for Review
    (1) Points


    NULLIF


    CASE (*)


    DESCRIBE


    WHERE

    BalasHapus
  46. Which of the following is a conditional expression used in SQL? Mark for Review
    (1) Points


    NULLIF


    CASE (*)


    DESCRIBE


    WHERE

    BalasHapus
  47. Which of the following General Functions will return the first non-null expression in the expression list? Mark for Review
    (1) Points


    NVL2


    NULLIF


    COALESCE (*)


    NVL

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

    SELECT style_id, style_name, category, cost
    FROM styles
    WHERE style_name LIKE 'SANDAL' AND NVL(cost, 0) < 15.00
    ORDER BY category, cost;

    Which result will the query provide?

    Mark for Review
    (1) Points



    STYLE_ID STYLE_NAME CATEGORY COST
    895840 SANDAL 85909 12.00
    968950 SANDAL 85909 10.00
    869506 SANDAL 89690 15.00
    758960 SANDAL 86979



    STYLE_ID STYLE_NAME CATEGORY COST
    968950 SANDAL 85909 10.00
    895840 SANDAL 85940 12.00
    758960 SANDAL 86979
    (*)




    STYLE_ID STYLE_NAME CATEGORY COST
    895840 SANDAL 85909 12.00
    968950 SANDAL 85909 10.00
    758960 SANDAL 86979
    869506 SANDAL 89690 15.00



    STYLE_ID STYLE_NAME CATEGORY COST
    895840 SANDAL 85940 12.00
    968950 SANDAL 85909 10.00
    758960 SANDAL 86979

    BalasHapus
  49. Which two statements concerning SQL functions are true? (Choose two.) Mark for Review
    (1) Points

    (Choose all correct answers)


    Conversion functions convert a value from one data type to another data type. (*)


    Single-row functions manipulate groups of rows to return one result per group of rows.


    Not all date functions return date values. (*)


    Character functions can accept numeric input.


    Number functions can return number or character values.

    BalasHapus
  50. If you use the RR format when writing a query using the date 27-Oct-17 and the year is 2001, what year would be the result? Mark for Review
    (1) Points


    1917


    2001


    1901


    2017 (*)

    BalasHapus
  51. Sysdate is 12-May-2004.
    You need to store the following date: 7-Dec-89
    Which statement about the date format for this value is true? Mark for Review
    (1) Points


    The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089 (*)


    The RR date format will interpret the year as 2089, and the YY date format will interpret the year as 1989


    Both the YY and RR date formats will interpret the year as 2089


    Both the YY and RR date formats will interpret the year as 1989

    BalasHapus
  52. Which statement about a self join is true? Mark for Review
    (1) Points


    A self join must be implemented by defining a view.


    The NATURAL JOIN clause must be used.


    Table aliases must be used to qualify table names. (*)


    Table aliases cannot be used to qualify table names.

    BalasHapus
  53. 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 NUMBER (6)
    MAX_SALARY NUMBER (6)
    Mark for Review
    (1) Points


    SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
    FROM employees e
    NATURAL JOIN jobs j;


    SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
    FROM employees e
    NATURAL JOIN jobs j
    USING (job_id);


    SELECT first_name, last_name, job_id, min_salary, max_salary
    FROM employees
    NATURAL JOIN jobs;
    (*)



    SELECT first_name, last_name, job_id, min_salary, max_salary
    FROM employees e
    FULL JOIN jobs j (job_id);


    SELECT e.first_name, e.last_name, e.job_id, j.min_salary, j.max_salary
    FROM employees e
    NATURAL JOIN jobs j ON (e.job_title = j.job_title);

    BalasHapus
  54. Evaluate this SELECT statement:
    SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' || b.fname as "Physician", c.admission
    FROM patient a
    JOIN physician b
    ON (b.physician_id = c.physician_id)
    JOIN admission c
    ON (a.patient_id = c.patient_id);

    Which clause generates an error?

    Mark for Review
    (1) Points


    ON (b.physician_id = c.physician_id); (*)


    ON (a.patient_id = c.patient_id)


    JOIN physician b


    JOIN admission c

    BalasHapus
  55. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False? Mark for Review
    (1) Points


    True


    False (*)

    BalasHapus
  56. Which statement about a natural join is true? Mark for Review
    (1) Points


    Columns with the same names must have identical data types.


    Columns with the same names must have compatible data types.


    Columns with the same names cannot be included in the SELECT list of the query.


    Columns with the same names must have the same precision and datatype. (*)

    BalasHapus
  57. Which operator is typically used in a nonequijoin? Mark for Review
    (1) Points


    >=, <=, or BETWEEN ...AND (*)


    NOT


    IN


    OR


    *

    BalasHapus
  58. You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
    Which query should you issue? Mark for Review
    (1) Points


    SELECT e.first_name, e.last_name, s.sales
    FROM employees, sales
    WHERE e.employee_id = s.employee_id AND revenue >= 100000;


    SELECT e.first_name, e.last_name, s.sales
    FROM employees e, sales s
    WHERE e.employee_id = s.employee_id AND revenue > 100000;


    SELECT first_name, last_name, sales
    FROM employees e, sales s
    WHERE e.employee_id = s.employee_id AND revenue > 100000;


    SELECT e.first_name, e.last_name, s.sales
    FROM employees e, sales s
    WHERE e.employee_id = s.employee_id AND revenue >= 100000;
    (*)

    BalasHapus
  59. What happens when you create a Cartesian product? Mark for Review
    (1) Points


    The table is joined to another equal table


    All rows that do not match in the WHERE clause are displayed


    All rows from one table are joined to all rows of another table (*)


    The table is joined to itself, one column to the next column, exhausting all possibilities

    BalasHapus
  60. 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 = .85 and COUNT = 4


    SUM = .85 and COUNT = 6 (*)


    SUM = 1.85 and COUNT =4


    SUM = 1.85 and COUNT = 6

    BalasHapus
  61. The following statement will work, even though it contains more than one GROUP function:
    SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
    FROM employees;
    True or False?

    Mark for Review
    (1) Points


    True (*)


    False

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

    Which result will the query display?

    Mark for Review
    (1) Points


    The total of the SALARY column for all employees 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 number of rows in the EMPLOYEES table that have a salary greater than 30000 (*)

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

    BalasHapus
  64. 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;

    BalasHapus
  65. If a select list contains both a column as well as a group function then what clause is required? Mark for Review
    (1) Points


    ORDER BY clause


    GROUP BY clause (*)


    JOIN clause


    HAVING clause

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

    Mark for Review
    (1) Points


    last_name, grade


    last_name


    last_name, gender


    grade, gender (*)

    BalasHapus
  67. 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, 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)


    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))

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


    CUBE (*)


    HAVING


    GROUP BY ALL COLUMNS

    BalasHapus
  69. What would happen if you attempted to use a single-row operator with a multiple-row subquery? Mark for Review
    (1) Points


    No rows will be selected.


    The data returned may or may not be correct.


    All the rows will be selected.


    An error would be returned. (*)

    BalasHapus
  70. Examine the structures of the PARTS and MANUFACTURERS tables:
    PARTS:
    PARTS_ID VARCHAR2(25) PK
    PARTS_NAME VARCHAR2(50)
    MANUFACTURERS_ID NUMBER
    COST NUMBER(5,2)
    PRICE NUMBER(5,2)

    MANUFACTURERS:
    ID NUMBER PK
    NAME VARCHAR2(30)
    LOCATION VARCHAR2(20)

    Assume that the tables have been populated with data including 100 rows in the PARTS table, and 20 rows in the MANUFACTURERS table. Which SQL statement correctly uses a subquery?

    Mark for Review
    (1) Points


    SELECT parts_name, price, cost
    FROM parts
    WHERE manufacturers_id IN
    (SELECT id
    FROM manufacturers m
    JOIN parts p
    ON (m.id = p.manufacturers_id));
    (*)



    SELECT parts_name
    FROM (SELECT AVG(cost) FROM manufacturers)
    WHERE cost > AVG(cost);


    SELECT parts_name, price, cost
    FROM parts
    WHERE manufacturers_id !=
    (SELECT id
    FROM manufacturers
    WHERE LOWER(name) = 'cost plus');


    UPDATE parts SET price = price * 1.15
    WHERE manufacturers_id =
    (SELECT id
    FROM manufacturers
    WHERE UPPER(location) IN("ATLANTA", "BOSTON", "DALLAS"));

    BalasHapus
  71. When a multiple-row subquery uses the NOT IN operator (equivalent to <>ALL), if one of the values returned by the inner query is a null value, the entire query returns: Mark for Review
    (1) Points


    No rows returned (*)


    All rows that were selected by the inner query including the null values


    A list of Nulls


    All rows that were selected by the inner query minus the null values

    BalasHapus
  72. Which of the following is TRUE regarding the order of subquery execution? Mark for Review
    (1) Points


    The subquery executes once after the main query.


    The subquery executes once before the main query. (*)


    The result of the main query is used with the subquery.


    The outer query is executed first.

    BalasHapus
  73. If the subquery returns no rows, will the outer query return any values? Mark for Review
    (1) Points


    Yes, Oracle will find the nearest value and rewrite your statement implicitly when you run it.


    Yes. It will just run and ignore the subquery.


    No, because you are not allowed to return empty values from a subquery.


    No, because the subquery will be treated like a null value. (*)

    BalasHapus
  74. What command will return data from the database to you?
    Mark for Review

    (1) Points
    GET
    SELECT (*)
    RETURN
    FETCH

    BalasHapus
  75. In which clause of a SELECT statement would you specify the name of the table or tables being queried?
    Mark for Review

    (1) Points
    The FROM clause (*)
    The SELECT clause
    The WHERE clause
    Any of the above options; you can list tables wherever you want in a SELECT statement.

    BalasHapus
  76. Evaluate this SELECT statement:
    SELECT (salary * raise_percent) raise
    FROM employees;

    If the RAISE_PERCENT column only contains null values, what will the statement return?

    Mark for Review

    (1) Points
    A null value or a zero depending on the value of the SALARY column
    Only null values (*)
    A null value or a numeric value depending on the value of the SALARY column
    Only zeroes

    BalasHapus
  77. If you write queries using the BETWEEN operator, it does not matter in what order you enter the values, i.e. BETWEEN low value AND high value will give the same result as BETWEEN high value and low value. True or False?
    Mark for Review

    (1) Points
    True
    False (*)

    BalasHapus
  78. The Concatenation Operator does which of the following?
    Mark for Review

    (1) Points
    Links rows of data together inside the database.
    Separates columns.
    Is represented by the asterisk (*) symbol
    Links two or more columns or literals to form a single output column (*)

    BalasHapus
  79. Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?
    Mark for Review

    (1) Points
    FROM
    IS
    SELECT
    WHERE (*)

    BalasHapus
  80. You need to display only unique combinations of the LAST_NAME and MANAGER_ID columns in the EMPLOYEES table. Which keyword should you include in the SELECT clause?
    Mark for Review

    (1) Points
    DISTINCTROW
    DISTINCT (*)
    UNIQUEONE
    ONLY

    BalasHapus
  81. To restrict the rows returned from an SQL Query, you should use the _____ clause:
    Mark for Review

    (1) Points
    SELECT
    WHERE (*)
    GROUP BY
    CONDITION
    All of the Above

    BalasHapus
  82. You need to change the default sort order of the ORDER BY clause so that the data is displayed in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?
    Mark for Review

    (1) Points
    SORT
    CHANGE
    DESC (*)
    ASC

    BalasHapus
  83. Which statement about the ORDER BY clause is true?
    Mark for Review

    (1) Points
    The ORDER BY clause can only contain columns that are included in the SELECT list.
    The ORDER BY clause should immediately precede the FROM clause in a SELECT statement
    You can use a column alias in the ORDER BY clause. (*)
    The default sort order of the ORDER BY clause is descending.

    BalasHapus
  84. Which of the following are examples of logical operators that might be used in a WHERE clause. (Choose Two)
    Mark for Review

    (1) Points
    AND, OR (*)
    < >, =, <=, >=, <>
    NOT (*)
    LIKES
    All of the above

    BalasHapus
  85. The following statement represents a multi-row function. True or False?
    SELECT UPPER(last_name)
    FROM employees;

    Mark for Review

    (1) Points
    True
    False (*)

    BalasHapus
  86. Evaluate this SQL statement:
    SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
    FROM employees e, employees m
    ORDER BY e.last_name, e.first_name
    WHERE e.employee_id = m.manager_id;

    This statement fails when executed. Which change will correct the problem?

    Mark for Review

    (1) Points
    Include a SORT clause.
    Reorder the clauses in the query. (*)
    Remove the table aliases in the ORDER BY clause.
    Remove the table aliases in the WHERE clause.

    BalasHapus
  87. The PLAYERS table contains these columns:
    PLAYERS TABLE:
    LAST_NAME VARCHAR2 (20)
    FIRST_NAME VARCHAR2 (20)
    SALARY NUMBER(8,2)
    TEAM_ID NUMBER(4)
    MANAGER_ID NUMBER(9)
    POSITION_ID NUMBER(4)

    You want to display all players' names with position 6900 or greater.
    You want the players names to be displayed alphabetically by last name and then by first name.
    Which statement should you use to achieve the required results?

    Mark for Review

    (1) Points
    SELECT last_name, first_name
    FROM players
    WHERE position_id <= 6900
    ORDER BY last_name, first_name;
    SELECT last_name, first_name
    FROM players
    WHERE position_id >= 6900
    ORDER BY last_name DESC, first_name;
    SELECT last_name, first_name
    FROM players
    WHERE position_id >= 6900
    ORDER BY last_name, first_name; (*)
    SELECT last_name, first_name
    FROM players
    WHERE position_id > 6900
    ORDER BY last_name, first_name;

    BalasHapus
  88. Which of the following is true of the ORDER BY clause:? (Choose Two)
    Mark for Review

    (1) Points
    Must be the last clause of the SQL statement (*)
    Displays the fetched rows in no particular order
    Defaults to a descending order (DESC)
    Defaults to an ascending order (ASC) (*)

    BalasHapus
  89. Evaluate this SELECT statement:
    SELECT *
    FROM employees
    WHERE department_id = 34
    OR department_id = 45
    OR department_id = 67;

    Which operator is the equivalent of the OR conditions used in this SELECT statement?

    Mark for Review

    (1) Points
    IN (*)
    AND
    LIKE
    BETWEEN AND ...

    BalasHapus
  90. Evaluate this SELECT statement:
    SELECT last_name, first_name, department_id, manager_id
    FROM employees;

    You need to sort data by manager id values and then alphabetically by employee last name and first name values. Which ORDER BY clause could you use?

    Mark for Review

    (1) Points
    ORDER BY manager_id, last_name, first_name (*)
    ORDER BY manager_id, first_name, last_name
    ORDER BY last_name, first_name, manager_id
    ORDER BY department_id, last_name

    BalasHapus
  91. The PLAYERS table contains these columns:
    PLAYERS TABLE:
    LAST_NAME VARCHAR2 (20)
    FIRST_NAME VARCHAR2 (20)
    SALARY NUMBER(8,2)
    TEAM_ID NUMBER(4)
    MANAGER_ID NUMBER(9)
    POSITION_ID NUMBER(4)

    You must display the player name, team id, and salary for players whose salary is in the range from 25000 through 100000 and whose team id is in the range of 1200 through 1500. The results must be sorted by team id from lowest to highest and then further sorted by salary from highest to lowest. Which statement should you use to display the desired result?

    Mark for Review

    (1) Points
    SELECT last_name, first_name, team_id, salary
    FROM players
    WHERE (salary > 25000 OR salary < 100000)
    AND team_id BETWEEN 1200 AND 1500
    ORDER BY team_id, salary;
    SELECT last_name, first_name, team_id, salary
    FROM players
    WHERE salary BETWEEN 25000 AND 100000
    AND team_id BETWEEN 1200 AND 1500
    ORDER BY team_id, salary DESC; (*)
    SELECT last_name, first_name, team_id, salary
    FROM players
    WHERE salary > 24999.99 AND salary < 100000
    AND team_id BETWEEN 1200 AND 1500
    ORDER BY team_id ASC, salary DESC;
    SELECT last_name, first_name, team_id, salary
    FROM players
    WHERE salary BETWEEN 24999.99 AND 100000.01
    AND team_id BETWEEN 1200 AND 1500
    ORDER BY team_id DESC, salary DESC;

    BalasHapus
  92. Which of the following statements best describes the rules of precedence when using SQL?
    Mark for Review

    (1) Points
    The order in which the columns are displayed
    The order in which the expressions are sorted
    The order in which the operators are returned
    The order in which the expressions are evaluated and calculated (*)
    All of the above

    BalasHapus
  93. Which statement about the default sort order is true?
    Mark for Review

    (1) Points
    Character values are displayed in reverse alphabetical order.
    The earliest date values are displayed first. (*)
    The lowest numeric values are displayed last.
    Null values are displayed first.

    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