Langsung ke konten utama

Final Exam Database Programming With SQL

Section 12
                (Answer all questions in this section)

1.            In a conditional multi-table insert, you can specify either __________ or __________. Mark for Review
(1) Points
                All; First (*)
                First; Second
                All; Second
                Null; Default

2.            A multi-table insert statement can insert into more than one table. (True or False?)        Mark for Review
(1) Points
                True (*)
                False

3.            Which of the following statements will add a new customer to the customers table in the Global Fast Foods database?           Mark for Review
(1) Points

                INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641);
(*)

                INSERT IN customers (id, first_name, last_name, address, city, state, zip, phone_number);

                INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES ("145", 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', "98008", "8586667641");

                INSERT INTO customers
(id 145, first_name 'Katie', last_name 'Hernandez', address '92 Chico Way', city 'Los Angeles', state 'CA', zip 98008, phone_number 8586667641);

4.            You have been instructed to add a new customer to the CUSTOMERS table. Because the new customer has not had a credit check, you should not add an amount to the CREDIT column.
The CUSTOMERS table contains these columns:
CUST_ID NUMBER(10)
COMPANY VARCHAR2(30)
CREDIT NUMBER(10)
POC VARCHAR2(30)
LOCATION VARCHAR2(30)

Which two INSERT statements will accomplish your objective?

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

                INSERT INTO customers
VALUES (cust_id, company, credit, poc, location) (200, 'InterCargo', 0, 'tflanders', 'samerica');

                INSERT INTO customers
VALUES (200, InterCargo, 0, tflanders, samerica);

                INSERT INTO customers
VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');
(*)

                INSERT INTO customers (cust_id, company, poc, location)
VALUES (200, 'InterCargo', 'tflanders', 'samerica');
(*)

5.            You need to remove a row from the EMPLOYEES table. Which statement would you use?            Mark for Review
(1) Points
                INSERT with a WHERE clause
                DELETE with a WHERE clause (*)
                UPDATE with a WHERE clause
                MERGE with a WHERE clause

Section 12
                (Answer all questions in this section)

6.            Examine the structures of the PRODUCTS and SUPPLIERS tables:
SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, Primary Key
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, Primary Key
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table
CATEGORY_ID NUMBER
QTY_PER_UNIT NUMBER
UNIT_PRICE NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER

You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?

 Mark for Review
(1) Points

                DELETE FROM products
WHERE UPPER(city) = 'ATLANTA';

                DELETE FROM products
WHERE supplier_id =
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');

                DELETE FROM suppliers
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ALANTA');

                DELETE FROM products
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');
(*)

7.            DELETE statements can use correlated subqueries? (True or False)           Mark for Review
(1) Points
                True (*)
                False

8.            Which two commands can be used to modify existing data in a database row?   Mark for Review
(1) Points
                                                (Choose all correct answers)      
                DELETE
                SELECT
                UPDATE (*)
                MERGE (*)

Section 13
                (Answer all questions in this section)
9.            Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)

The EMPLOYEE_ID column currently contains 500 employee identification numbers. Business requirements have changed and you need to allow users to include text characters in the identification values. Which statement should you use to change this column's data type?

 Mark for Review
(1) Points

                You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty. (*)

                ALTER employee TABLE
MODIFY COLUMN (employee_id VARCHAR2(15));

                ALTER TABLE employee
MODIFY (employee_id VARCHAR2(9));

                ALTER TABLE employee
REPLACE (employee_id VARCHAR2(9));

10.          The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)

The TEAMS table is currently empty. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?

 Mark for Review
(1) Points

                ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));

                ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)

                ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));

                ALTER teams
MODIFY (mgr_id VARCHAR2(15));

                You CANNOT modify the data type of the MGR_ID column.

Section 13
                (Answer all questions in this section)

11.          Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?    Mark for Review
(1) Points
                DROP TABLE
                TRUNCATE TABLE (*)
                ALTER TABLE
                MODIFY

12.          You want to issue the following command on a database that includes your company's inventory information:
ALTER TABLE products SET UNUSED COLUMN color;
What will be the result of issuing this command?

 Mark for Review
(1) Points
                The column named COLOR in the table named PRODUCTS will be created.

                The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as it has been deleted logically. (*)

                The column named COLOR in the table named PRODUCTS will be assigned default values.
                The column named COLOR in the table named PRODUCTS will be deleted.

13.          The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?   Mark for Review
(1) Points
                True
                False (*)

14.          Evaluate this statement:
Which statement about this TRUNCATE TABLE statement is true?             Mark for Review
(1) Points
                You can produce the same results by issuing the 'DROP TABLE employee' statement.
                You can issue this statement to retain the structure of the employees table. (*)
                You can produce the same results by issuing the 'DELETE employees' statement.
                You can reverse this statement by issuing the ROLLBACK statement.

15.          The TIMESTAMP data type allows what?               Mark for Review
(1) Points
                Time to be stored as an interval of years and months.
                Time to be stored as a date with fractional seconds. (*)
                Time to be stored as an interval of days to hours, minutes and seconds.
                None of the above.

Section 13
                (Answer all questions in this section)

16.          The ELEMENTS column is defined as:
NUMBER(6,4)
How many digits to the right of the decimal point are allowed for the ELEMENTS column?

 Mark for Review
(1) Points
                Two
                Zero
                Four (*)
                Six

17.          I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.       Mark for Review
(1) Points

                impossible; no matter what, there can never be two tables with the same name, even if they are in separate schemas.

                impossible; School_Friends is a reserved term in SQL.

                possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas. (*)

                possible; our data will merge into one table, and we can more easily access our mutual friends information.

18.          Which column name is valid?      Mark for Review
(1) Points
                1NUMBER
                NUMBER
                NUMBER_1$ (*)
                1_NUMBER#

19.          Which CREATE TABLE statement will fail?              Mark for Review
(1) Points
                CREATE TABLE date (date_id NUMBER(9)); (*)
                CREATE TABLE time (time_id NUMBER(9));
                CREATE TABLE time_date (time NUMBER(9));
                CREATE TABLE date_1 (date_1 DATE);

Section 14
                (Answer all questions in this section)

20.          A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?            Mark for Review
(1) Points
                True
                False (*)

Section 14
                (Answer all questions in this section)

21.          A table must have at least one not null constraint and one unique constraint. True or False?        Mark for Review
(1) Points
                True
                False (*)

22.          A table can have more than one UNIQUE key constraint. True or False? Mark for Review
(1) Points
                True (*)
                False

23.          You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:
ALTER TABLE employees
ENABLE employee_id_pk;

Which statement is true?

 Mark for Review
(1) Points
                The statement will NOT execute because it contains a syntax error. (*)
                The statement will execute, but will not verify that the existing values are unique.
                The statement will achieve the desired result.
                The statement will execute, but will ensure that the new ID values are unique.

24.          The LINE_ITEM table contains these columns:
LINE_ITEM_ID NUMBER PRIMARY KEY
PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
QUANTITY NUMBER(9)
UNIT_PRICE NUMBER(5,2)

You need to disable the FOREIGN KEY constraint. Which statement should you use?

 Mark for Review
(1) Points

                ALTER TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)

                ALTER TABLE line_item
ENABLE CONSTRAINT product_id_fk;

                ALTER TABLE line_item
DELETE CONSTRAINT product_id_fk;

                ALTER TABLE line_item
DROP CONSTRAINT product_id_fk;

25.          A Primary Key that is made up of more than one column is called a:         Mark for Review
(1) Points
                Multiple Primary Key
                Composite Primary Key (*)
                Double Key
                Primary Multi-Key
                None of the Above

Section 14
                (Answer all questions in this section)

26.          The table that contains the Primary Key in a Foreign Key Constraint is known as:                Mark for Review
(1) Points
                Child Table
                Mother and Father Table
                Parent Table (*)
                Detail Table

Section 15
                (Answer all questions in this section)
27.          What is one advantage of using views?  Mark for Review
(1) Points
                To provide restricted data access (*)
                To provide data dependence
                To be able to store the same data in more than one place

28.          Which of the following statements is a valid reason for using a view?      Mark for Review
(1) Points
                Views allow access to the data because the view displays all of the columns from the table.
                Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
                Views are not valid unless you have more than one user.

                Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*)

29.          Which statement would you use to alter a view?              Mark for Review
(1) Points
                CREATE OR REPLACE VIEW (*)
                MODIFY VIEW
                ALTER VIEW
                ALTER TABLE

30.          The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
COMMISSOIN NUMBER(7,2)
HIRE_DATE DATE

Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?

 Mark for Review
(1) Points

                SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary
     FROM employees
     ORDER BY salary)
WHERE ROWNUM <=10 AND job_id LIKE 'CLERK' AND department_id = 70;

                SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id
     FROM employees
     WHERE job_id LIKE 'CLERK' AND department_id = 70
     ORDER BY salary)
WHERE ROWNUM <=10;
(*)

                SELECT ROWNUM "Ranking", last_name||' ,'||first_name "Employee", salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id, dept_id
     FROM employees
     WHERE ROWNUM <=10
     ORDER BY salary)
WHERE job_id LIKE 'CLERK' AND department_id = 70;

                The only way is to use the data dictionary.

Section 15
                (Answer all questions in this section)

31.          A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?         Mark for Review
(1) Points
                True (*)
                False

32.          What is the purpose of including the WITH CHECK OPTION clause when creating a view?               Mark for Review
(1) Points

                To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)

                To keep views form being queried by unauthorized persons
                To make sure that the parent table(s) actually exist
                To make sure that data is not duplicated in the view

33.          Which option would you use when creating a view to ensure that no DML operations occur on the view?                Mark for Review
(1) Points
                WITH READ ONLY (*)
                FORCE
                WITH ADMIN OPTION
                NOFORCE

34.          If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he include when creating the view?      Mark for Review
(1) Points
                WITH CHECK OPTION (*)
                FORCE
                WITH READ ONLY
                WITH CONSTRAINT CHECK

Section 16
                (Answer all questions in this section)

35.          When creating a sequence, which keyword or option specifies the minimum sequence value?   Mark for Review
(1) Points
                MINVALUE (*)
                CYCLE
                NOMAXVALUE
                MAXVALUE

Section 16
                (Answer all questions in this section)

36.          Which statement would you use to remove the EMP_ID_SEQ sequence?             Mark for Review
(1) Points
                DROP SEQUENCE emp_id_seq; (*)
                ALTER SEQUENCE emp_id_seq;
                DELETE SEQUENCE emp_id_seq;
                REMOVE SEQUENCE emp_id_seq;

37.          When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?       Mark for Review
(1) Points
                CACHE (*)
                NOCACHE
                NOCYCLE
                LOAD
                MEMORY

38.          What kind of INDEX is created by Oracle when you create a primary key?              Mark for Review
(1) Points
                UNIQUE INDEX (*)
                NONUNIQUE INDEX
                INDEX
                Oracle cannot create indexes automatically.

39.          Indexes can be used to speed up queries. True or False?              Mark for Review
(1) Points
                True (*)
                False

40.          Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?                Mark for Review
(1) Points
                An index (*)
                A PRIMARY KEY constraint
                A FOREIGN KEY constraint
                A CHECK constraint

Section 17
                (Answer all questions in this section)

41.          System privileges are:    Mark for Review
(1) Points
                A collection of objects, such as tables, views, and sequences.
                Required to manipulate the content of objects in the database.
                Required to gain access to the database. (*)
                Named groups of related privileges given to a user.

42.          Evaluate this statement:
ALTER USER bob IDENTIFIED BY jim;

Which statement about the result of executing this statement is true?

 Mark for Review
(1) Points
                A new user JIM is created from user BOB's profile.
                The user BOB is assigned the same privileges as user JIM.
                The user BOB is renamed and is accessible as user JIM.
                A new password is assigned to user BOB. (*)

43.          Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?      Mark for Review
(1) Points
                REGEXP_SUBSTR
                REGEXP (*)
                REGEXP_REPLACE
                REGEXP_LIKE

44.          Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False?      Mark for Review
(1) Points
                True (*)
                False

45.          A role can be granted to another role. True or False?      Mark for Review
(1) Points
                True (*)
                False

Section 17
                (Answer all questions in this section)

46.          Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ï¾ select ON ï¾ scott_king.employees TO jennifer_cho;

True or False: Allison's statement will fail.

 Mark for Review
(1) Points
                True (*)
                False

47.          Which keyword would you use to grant an object privilege to all database users?              Mark for Review
(1) Points
                PUBLIC (*)
                ALL
                ADMIN
                USERS
Section 18
                (Answer all questions in this section)
48.          If Oracle crashes, your changes are automatically rolled back. True or False?        Mark for Review
(1) Points
                True (*)
                False

49.          When you logout of Oracle, your data changes are automatically rolled back. True or False?         Mark for Review
(1) Points
                True
                False (*)

Section 19
                (Answer all questions in this section)
50.          A software verification and validation method.  Mark for Review
(1) Points
                Documentation
                Unit testing (*)
                Software engineering

                Production

Komentar

  1. Super answer ������

    BalasHapus
  2. wow! I appropriate your contribution for trainees. Thank you. keep in touch.

    BalasHapus
  3. You need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task?
    A subquery (*)
    A SET clause
    A function
    An ON clause

    The PRODUCTS table contains these columns:
    PRODUCT_ID NUMBER NOT NULL
    PRODUCT_NAME VARCHAR2 (25)
    SUPPLIER_ID NUMBER NOT NULL
    LIST_PRICE NUMBER (7,2)
    COST NUMBER (5,2)
    QTY_IN_STOCK NUMBER(4)
    LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL

    Which INSERT statement will execute successfully?

    INSERT INTO products(product_id, product_name)
    VALUES (2958, 'Cable');
    INSERT INTO products
    VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);
    INSERT INTO products(product_id, product_name, supplier_id
    VALUES (2958, 'Cable', 8690, SYSDATE);
    INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
    VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)


    Which of the following represents the correct syntax for an INSERT statement?
    INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
    INSERT INTO customers VALUES '3178' 'J.' 'Smith' '123 Main Street' 'Nashville' 'TN' '37777';
    INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777'); (*)
    INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;
    Correct

    Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?
    UPDATE copy_emp
    SET department_id = (SELECT department_id
    FROM employees
    WHERE employee_id = 100)
    WHERE job_id = (SELECT job_id
    FROM employees
    WHERE employee_id = 200);

    The statement will fail because the subqueries are returning data from different rows.
    No, this statement will return an error.
    No, this does nothing.
    Yes, this is a perfectly valid statement. (*)

    BalasHapus
  4. What would happen if you issued a DELETE statement without a WHERE clause?
    All the rows in the table would be deleted. (*)
    An error message would be returned.
    Only one row would be deleted.
    No rows would be deleted.

    Using MERGE accomplishes an __________ and __________ simultaneously.
    UPDATE; SELECT
    INSERT; UPDATE (*)
    UPDATE; DELETE
    INSERT; SELECT

    The default value must match the __________ of the column.
    Table
    Datatype (*)
    Size
    Column name

    You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?
    CREATE TABLE travel
    (destination_id number primary key, departure_date date, return_date date, JOIN emp_id number(10) ON employees (emp_id));
    CREATE TABLE travel
    (destination_id number primary key, departure_date date, return_date date, t.emp_id = e.emp_id);
    CREATE TABLE travel
    (destination_id primary key, departure_date date, return_date date, emp_id REFERENCES employees (emp_id));
    CREATE TABLE travel
    (destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id)); (*)

    Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth?
    CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);
    CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)
    CREATE table BIRTHDAYS (employee number, name, date of birth);
    CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Date of Birth DATE);

    BalasHapus
  5. CREATE TABLE student_table
    (id NUMBER(6),
    lname VARCHAR(20),
    fname VARCHAR(20),
    lunch_num NUMBER(4));
    Which of the following statements best describes the above SQL statement:

    Creates a table named student_table with four columns: lname, fname, lunch, num
    Creates a table named student with four columns: id, lname, fname, lunch_num
    Creates a table named student_table with four columns: lname, fname, lunch, num
    Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)

    The ELEMENTS column is defined as:
    NUMBER(6,4)
    How many digits to the right of the decimal point are allowed for the ELEMENTS column?

    Four (*)
    Six
    Two
    Zero

    You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?
    DATE
    CHAR
    NUMBER (*)
    VARCHAR2


    RENAME old_name to new_name can be used to:
    Rename a row.
    Rename a column.
    Rename a table. (*)
    All of the above.

    You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
    True
    False (*)

    The PLAYERS table contains these columns:
    PLAYER_ID NUMBER(9) PRIMARY KEY
    LAST_NAME VARCHAR2(20)
    FIRST_NAME VARCHAR2(20)
    TEAM_ID NUMBER(4)
    SALARY NUMBER(9,2)

    Which statement should you use to decrease the width of the FIRST_NAME column to 10 if the column currently contains 1500 records, but none are longer than 10 bytes or characters?

    ALTER players TABLE
    MODIFY COLUMN first_name VARCHAR2(10);
    ALTER TABLE players
    MODIFY (first_name VARCHAR2(10)); (*)
    ALTER players TABLE
    MODIFY COLUMN (first_name VARCHAR2(10));
    ALTER TABLE players
    RENAME first_name VARCHAR2(10);

    Your supervisor has asked you to modify the AMOUNT column in the ORDERS table. He wants the column to be configured to accept a default value of 250. The table contains data that you need to keep. Which statement should you issue to accomplish this task?
    ALTER TABLE orders
    MODIFY (amount DEFAULT 250); (*)
    DELETE TABLE orders;
    CREATE TABLE orders
    (orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
    customerid varchar2(5) REFERENCES customers (customerid),
    orderdate date,
    amount DEFAULT 250);
    ALTER TABLE orders
    CHANGE DATATYPE amount TO DEFAULT 250;
    DROP TABLE orders;
    CREATE TABLE orders
    (orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
    customerid varchar2(5) REFERENCES customers (customerid),
    orderdate date,
    amount DEFAULT 250);

    Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
    Single quotes ' ' (*)
    Double quotes " "
    Parentheses ( )
    Brackets { }


    BalasHapus
  6. You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?
    RENAME employees emp;
    ALTER TABLE employees TO emp;
    RENAME employees TO emp; (*)
    ALTER TABLE employees RENAME TO emp

    A unique key constraint can only be defined on a not null column. True or False?
    True
    False (*)

    Which constraint can only be created at the column level?
    FOREIGN KEY
    CHECK
    UNIQUE
    NOT NULL (*)

    Which statement about the NOT NULL constraint is true?
    The NOT NULL constraint requires a column to contain alphanumeric values.
    The NOT NULL constraint can be defined at either the column level or the table level.
    The NOT NULL constraint prevents a column from containing alphanumeric values.
    The NOT NULL constraint must be defined at the column level. (*)

    Which statement about a non-mandatory foreign key constraint is true?
    A foreign key value cannot be null.
    A foreign key value must either be null or match an existing value in the parent table. (*)
    A foreign key value must match an existing value in the parent table.
    A foreign key value must be unique.

    Which clause could you use to ensure that cost values are greater than 1.00?
    CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
    CHECK CONSTRAINT part_cost_ck (cost > 1.00)
    CONSTRAINT CHECK cost > 1.00
    CONSTRAINT CHECK part_cost_ck (cost > 1.00)

    The PO_DETAILS table contains these columns:
    PO_NUM NUMBER NOT NULL, Primary Key
    PO_LINE_ID NUMBER NOT NULL, Primary Key
    PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table
    QUANTITY NUMBER
    UNIT_PRICE NUMBER(5,2)

    Evaluate this statement:

    ALTER TABLE po_details
    DISABLE CONSTRAINT product_id_pk CASCADE;

    For which task would you issue this statement?

    To drop and recreate the PRIMARY KEY constraint on the PO_NUM column
    To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index
    To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)
    To create a new PRIMARY KEY constraint on the PO_NUM column

    BalasHapus
  7. Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.
    CREATE OR REPLACE VIEW salary_vu
    AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
    FROM employees e, departments d, job_grades j
    WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal; (*)
    CREATE OR REPLACE VIEW salary_vu
    AS (SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
    FROM employees emp, departments d, job grades j
    WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal);
    CREATE OR REPLACE VIEW salary_vu
    AS SELECT e.empid "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
    FROM employees e, departments d, job_grades j
    WHERE e.department_id = d.department_id NOT e.salary BETWEEN j.lowest_sal and j.highest_sal;
    CREATE OR REPLACE VIEW salary_vu
    AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
    FROM employees e, departments d, job_grades j
    WHERE e.department_id equals d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;

    Examine the structures of the PRODUCTS and SUPPLIERS tables.
    PRODUCTS:
    PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
    PRODUCT_NAME VARCHAR2 (25)
    SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
    LIST_PRICE NUMBER (7,2)
    COST NUMBER (7,2)
    QTY_IN_STOCK NUMBER
    QTY_ON_ORDER NUMBER
    REORDER_LEVEL NUMBER
    REORDER_QTY NUMBER

    SUPPLIERS:
    SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
    SUPPLIER_NAME VARCHAR2 (25)
    ADDRESS VARCHAR2 (30)
    CITY VARCHAR2 (25)
    REGION VARCHAR2 (10)
    POSTAL_CODE VARCHAR2 (11)

    Evaluate this statement:

    ALTER TABLE suppliers
    DISABLE CONSTRAINT supplier_id_pk CASCADE;

    For which task would you issue this statement?

    To remove all constraint references to SUPPLIERS table
    To drop the FOREIGN KEY constraint on the PRODUCTS table
    To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
    To remove all constraint references to the PRODUCTS table
    To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)

    BalasHapus
  8. Which action can be performed by using DML statements?
    Disabling an index
    Creating PRIMARY KEY constraints
    Deleting records in a table (*)
    Altering a table

    Which option would you use when creating a view to ensure that no DML operations occur on the view?
    WITH READ ONLY (*)
    FORCE
    NOFORCE
    WITH ADMIN OPTION

    Evaluate this CREATE VIEW statement:
    CREATE VIEW pt_view AS
    (SELECT first_name, last_name, status, courseid, subject, term
    FROM faculty f, course c
    WHERE f.facultyid = c.facultyid);

    Which type of view will this statement create?

    Inline
    Simple
    Nested
    Complex (*)

    Which of the following keywords cannot be used when creating a view?
    HAVING
    WHERE
    ORDER BY
    They are all valid keywords when creating views. (*)

    In order to query a database using a view, which of the following statements applies?
    You can retrieve data from a view as you would from any table. (*)
    The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
    Use special VIEW SELECT keywords.
    You can never see all the rows in the table through the view.

    A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
    True (*)
    False

    Which statement about an inline view is true?
    An inline view is a schema object.
    An inline view is a complex view.
    An inline view is a subquery in the FROM clause, often named with an alias. (*)
    An inline view can be used to perform DML operations.

    When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?
    CACHE (*)
    MEMORY
    NOCYCLE
    NOCACHE
    LOAD

    Which pseudocolumn returns the latest value supplied by a sequence?
    NEXTVAL
    CURRVAL (*)
    CURRENT
    NEXT

    Examine the code for creating this sequence:
    CREATE SEQUENCE track_id_seq
    INCREMENT BY 10
    START WITH 1000 MAXVALUE 10000
    What are the first three values that would be generated by the sequence?

    100010011002
    1000, 1010, 1020 (*)
    1100, 1200, 1300
    0, 1, 2

    BalasHapus
  9. Which of the following statements best describes indexes and their use?
    They are just copies of data in no particular order.
    They contain the column value and pointers to the data in the table, but the data is sorted. (*)
    They contain all the rows and columns from the table
    None of the above

    Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?
    ALTER TABLE employees
    DROP INDEX last_name_idx;
    DROP INDEX last_name_idx; (*)
    DROP INDEX last_name_idx(last_name);
    DROP INDEX last_name_idx(employees.last_name);

    Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?
    A CHECK constraint
    An index (*)
    A FOREIGN KEY constraint
    A PRIMARY KEY constraint

    User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:
    GRANT SELECT ON employees TO mary WITH GRANT OPTION;

    Which of the following statements can MARY now execute successfully? (Choose two)

    (Choose all correct answers)
    REVOKE SELECT ON bob.employees FROM bob;
    SELECT FROM bob.employees; (*)
    GRANT SELECT ON bob.employees TO PUBLIC; (*)
    DROP TABLE bob.employees;

    User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table?
    No (*)
    Yes

    Which of the following statements is true?
    Database Links allow users to work on remote database objects without having to log into the other database. (*)
    Database Links are never used in the real world.
    Database Links can be created by any user of a database. You do not need any special privileges to create them.
    Database Links are pointers to another schema in the same database.

    User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?
    GRANT SELECT ON emp_view ONLY TO rudi;
    SELECT * FROM emp_view FOR rudi;
    GRANT SELECT ON emp_view TO rudi; (*)
    CREATE SYNONYM emp_view FOR employees;

    You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?
    CREATE USER jones IDENTIFIED BY mark;
    GRANT CREATE SESSION TO jones;
    GRANT CREATE TABLE TO jones; (*)
    GRANT CREATE SESSION TO jones;
    GRANT CREATE TABLE TO jones;
    CREATE USER jones IDENTIFIED BY mark;
    GRANT CREATE TABLE TO jones;
    CREATE USER jones IDENTIFIED BY mark;
    GRANT CREATE SESSION TO jones;

    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