Halaman

Sunday, August 11, 2019

Database Programming with SQL Final Exam


Database Programming with SQL Final Exam


Evaluate this statement:
DELETE FROM customer;

Which statement is true?
                                                              
                                               
                The statement deletes all the rows from the CUSTOMER table. (*)

                                               
                The statement removes the structure of the CUSTOMER table from the database.

                                               
                The statement deletes the first row in the CUSTOMERS table.

                                               
                The statement deletes the CUSTOMER column.

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


Which two commands can be used to modify existing data in a database row?   

                                               
                DELETE

                                               
                MERGE (*)

                                               
                SELECT

                                               
                UPDATE (*)

                                                                               
The PLAYERS table contains these columns:
PLAYER_ID NUMBER NOT NULL
PLAYER_LNAME VARCHAR2(20) NOT NULL
PLAYER_FNAME VARCHAR2(10) NOT NULL
TEAM_ID NUMBER
SALARY NUMBER(9,2)

You need to increase the salary of each player for all players on the Tiger team by 12.5 percent. The TEAM_ID value for the Tiger team is 5960. Which statement should you use?
                                                                  
                                               
                UPDATE players (salary)
SET salary = salary * 1.125;

                                               
                UPDATE players
SET salary = salary * .125
WHERE team_id = 5960;

                                               
                UPDATE players
SET salary = salary * 1.125
WHERE team_id = 5960;
(*)

                                               
                UPDATE players (salary)
VALUES(salary * 1.125)
WHERE team_id = 5960;

                                                                               
The MERGE statement first tries to update one or more rows in a table that match the criteria; if no row matches the criteria for the update, a new row will automatically be inserted instead. True or False?                                                        
                                               
                True (*)

                                               
                False

If a default value was set for a null column, Oracle sets the column to the default value. However, if no default value was set when the column was created, Oracle inserts an empty space. True or False?                                                                  
                                               
                True

                                               
                False (*)

                                                                               
When inserting rows into a table, all columns must be given values. True or False?       
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause? 
                                                        
                                               
                Yes, you can just list as many rows as you want; just remember to separate the rows with commas.

                                               
                No, there is no such thing as INSERT ... VALUES.

                                               
                No, you can only create one row at a time when using the VALUES clause. (*)

                                                                               
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?
                                                                    
                                               
                ALTER teams
MODIFY (mgr_id VARCHAR2(15));

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

                                               
                ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)
  
                                               
                You CANNOT modify the data type of the MGR_ID column.

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

                                                                               
The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped. True or False?   
                                                                               
                                               
                True

                                               
                False (*)

Which statement about decreasing the width of a column is true?  

                                               
                When a character column contains data, you can decrease the width of the column if the existing data does not violate the new size. (*)

                                               
                You cannot decrease the width of a character column unless the table in which the column resides is empty.

                                               
                When a character column contains data, you can decrease the width of the column without any restrictions.

                                               
                When a character column contains data, you cannot decrease the width of the column.

                                                                               
The following code creates a table named student_table with four columns: id, lname, fname, lunch_num
CREATE TABLE student_table
    (id NUMBER(6),
     lname VARCHAR(20),
     fname VARCHAR(20),
     lunch_num NUMBER(4));

The lunch_num column in the above table has been marked as UNUSED.
Which of the following is the best statement you can use if you wish to remove the UNUSED column from the student_table?

                                   
                ALTER TABLE DELETE ALL COLUMNS

                                               
                DROP column

                                               
                ALTER TABLE DELETE UNUSED COLUMNS

                                               
                ALTER TABLE DROP UNUSED COLUMNS (*)

                                                                               
When you use ALTER TABLE to add a column, the new column:  

                                               
                Will not be created because you cannot add a column after the table is created

                                               
                Can be placed by adding a GROUP BY clause

                                               
                Becomes the first column in the table

                                               
                Becomes the last column in the table (*)

                                                                               
                                                               
Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?               
                                                                               
                                               
                TRUNCATE TABLE (*)

                                               
                DROP TABLE

                                               
                ALTER TABLE

                                               
                MODIFY

                                                                               
Evaluate this CREATE TABLE statement:
CREATE TABLE sales
(sales_id NUMBER,
customer_id NUMBER,
employee_id NUMBER,
sale_date TIMESTAMP WITH TIME ZONE,
sale_amount NUMBER(7,2));

Which statement about the SALE_DATE column is true?
                                                            
                                               
                Data stored will not include seconds.

                                               
                Data stored in the column will be returned in the database's local time zone. (*)

                                               
                Data will be normalized to the client time zone.

                                               
                Data will be stored using a fractional seconds precision of 5.


A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?   

                                               
                TIMESTAMP

                                               
                INTERVAL YEAR TO MONTH

                                               
                DATETIME

                                               
                INTERVAL DAY TO SECOND (*)

                                                                               
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 NUMBER, Empname CHAR(20), Date of Birth DATE);

                                               
                CREATE table BIRTHDAYS (employee number, name, date of birth);

                                               
                CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);

                                               
                CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)

                                                                               
When creating a new table, which of the following naming rules apply. (Choose three)  

                                               
                Must be an Oracle reserved word

                                               
                Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and # (*)

                                               
                Can have the same name as another object owned by the same user

                                               
                Must be between 1 to 30 characters long (*)

                                               
                Must begin with a letter (*)

                                                                               
Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.              
                                                                               
                                               
                CREATE TABLE emp (employee_id, first_name, last_name, salary, department_id);

                                               
                CREATE TABLE emp
SELECT (employee_id, first_name, last_name, salary, department_id FROM employees);

                                               
                CREATE TABLE emp
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;
(*)


                                               
                CREATE TABLE employee
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees;

                                                                               
If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?          
                                                                               
                                               
                True (*)

                                               
                False

Which statement about the NOT NULL constraint is true?            
                                                                               
                                               
                The NOT NULL constraint can be defined at either the column level or the table level.

                                               
                The NOT NULL constraint must be defined at the column level. (*)

                                               
                The NOT NULL constraint prevents a column from containing alphanumeric values.

                                               
                The NOT NULL constraint requires a column to contain alphanumeric values.

                                                                               
Which of the following is not a valid Oracle constraint type?       
                                                                               
                                               
                NOT NULL

                                               
                EXTERNAL KEY (*)

                                               
                PRIMARY KEY

                                               
                UNIQUE KEY

                                                                               
                                                               
Which line of the following code will cause an error:
CREATE TABLE clients
(client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY,
first_name VARCHAR2(14),
last_name VARCHAR2(13),
hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
department_id NUMBER(4),
CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
                                                                               
                                               
                Line 2

                                               
                Line 3

                                               
                Line 5 (*)

                                               
                Line 7

                                                                               
Evaluate the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER NOT NULL, Primary Key
DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE

Which CREATE TABLE statement should you use to create the DONATIONS table?
                                                            
                                               
                CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);
(*)
  
                                               
                CREATE TABLE donations
pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE;

                                               
                CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER,
amount_paid NUMBER,
payment_dt DATE);

                                               
                CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY NOT NULL,
donor_id NUMBER FOREIGN KEY donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);

                                                                               
Which of the following would definitely cause an integrity constraint error?  

                                               
                Using a subquery in an INSERT statement.

                                               
                Using the UPDATE command on rows based in another table.

                                               
                Using the DELETE command on a row that contains a primary key with a dependent foreign key declared without either an ON DELETE CASCADE or ON DELETE SET NULL. (*)

                                               
                Using the MERGE statement to conditionally insert or update rows.

What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?                                                                 
                                               
                ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
(*)


                                               
                ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

                                               
                DROP CONSTRAINT table_name (constraint_name);

                                               
                ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;

                                                                               
The CUSTOMER_FINANCE table contains these columns:
CUSTOMER_ID NUMBER(9)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
PAYMENTS NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
CREDIT_LIMIT NUMBER(7)

You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest.

Which SELECT statement clause is included in your query?
                                                          
                                               
                Inner query: WHERE ROWNUM = 800

                                               
                Outer query: ORDER BY new_balance DESC

                                               
                Inner query: ORDER BY new_balance DESC (*)

                                               
                Inner query: SELECT customer_id, new_balance ROWNUM

                                                                               
                                                               
The CUSTOMER_FINANCE table contains these columns:
CUSTOMER_ID NUMBER(9)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
PAYMENTS NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
CREDIT_LIMIT NUMBER(7)

You execute this statement:

SELECT ROWNUM "Rank", customer_id, new_balance
FROM (SELECT customer_id, new_balance FROM customer_finance)
WHERE ROWNUM <= 25
ORDER BY new_balance DESC;

What statement is true?
                                                               
                                               
                The 25 greatest new balance values were displayed from the highest to the lowest.

                                               
                The statement failed to execute because the ORDER BY clause does NOT use the Top-n column.

                                               
                The statement failed to execute because an inline view was used.

                                               
                The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY clause. (*)

                                                                               
                                                               
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?      

                                               
                WITH CONSTRAINT CHECK

                                               
                FORCE

                                               
                WITH CHECK OPTION (*)

                                               
                WITH READ ONLY

                                                                               
Only one type of view exists. True or False?   
                                                                               
                                               
                True

                                               
                False (*)

Given the following view, which operations would be allowed on the emp_dept view?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name,
    e.salary,
    e.hire_date,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id >=50;
                                                                
                                               
                SELECT, DELETE

                                               
                SELECT, INSERT

                                               
                SELECT, UPDATE of some columns, DELETE (*)

                                               
                SELECT, UPDATE of all columns

                                                                               
Which of the following statements is a valid reason for using a view?     

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

                                               
                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.

                                                                               
Which statement about the CREATE VIEW statement is true?     
                                                                               
                                               
                A CREATE VIEW statement CANNOT contain a function.

                                               
                A CREATE VIEW statement CANNOT contain an ORDER BY clause.

                                               
                A CREATE VIEW statement CAN contain a join query. (*)

                                               
                A CREATE VIEW statement CANNOT contain a GROUP BY clause.

                                                                               
                                                               
You need to create a view that will display the name, employee identification number, first and last name, salary, and department identification number. The display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition should be created regardless of the existence of the EMPLOYEES table. No DML may be performed when using this view. Evaluate these statements:
CREATE OR REPLACE NOFORCE VIEW EMP_SALARY_V
AS SELECT employee_id, last_name, first_name, salary, department_id
FROM employees WITH READ ONLY;

SELECT *
FROM emp_salary_v
ORDER BY salary, last_name, first_name;

Which statement is true?

                              
                The statements will NOT return all of the desired results because the WITH CHECK OPTION clause is NOT included in the CREATE VIEW statement.

                                               
                The CREATE VIEW statement will fail if the EMPLOYEES table does not exist. (*)

                                               
                To achieve all of the desired results this ORDER ON clause should be added to the CREATE VIEW statement: 'ORDER ON salary, last_name, first_name'.

                                               
                When both statements are executed all of the desired results are achieved.

                                                                               
                                                               
Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;

Which statement is true?

                            
                The sequence will continue to generate values after reaching its maximum value.

                                               
                The sequence preallocates values and retains them in memory.

                                               
                The sequence will start with 1. (*)

                                               
                The sequence has no maximum value.


You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?     
                                                                               
                                               
                Create a synonym.

                                               
                Specify a UNIQUE constraint on the CUSTOMER_ID column.

                                               
                Create a sequence. (*)

                                               
                Do nothing. Oracle automatically generates unique values for columns that are defined as primary keys.

                                                                               
You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
ALTER TABLE manufacturers
MODIFY (location_id NUMBER(6));

Which statement about the LOCATION_ID_SEQ sequence is true?

                                                                               
                                               
                The sequence is deleted and must be recreated.

                                               
                The current value of the sequence is reset to zero.

                                               
                The sequence is unchanged. (*)

                                               
                The current value of the sequence is reset to the sequence's START WITH value.

                                                                               
Which statement about an index is true?        
                                                                               
                                               
                An index created on multiple columns is called a composite or concatenated index. (*)

                                               
                Creating an index reorders the data in the underlying table.

                                               
                An index can only be created on a single table column.

                                               
                Creating an index will always improve query performance.

                                                                               
You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query?       
                                                                               
                                               
                USER_OBJECTS

                                               
                USER_IND_COLUMNS (*)

                                               
                USER_INDEXES

                                               
                USER_TABLES

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

                                               
                A FOREIGN KEY constraint

                                               
                A PRIMARY KEY constraint

                                               
                A CHECK constraint

Which of the following best describes a role in an Oracle database?       
                                                                               
                                               
                A role is an object privilege which allows a user to update a table.

                                               
                A role is a type of system privilege.

                                               
                A role is the part that a user plays in querying the database.

                                               
                A role is a name for a group of privileges. (*)

                                                                               
The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco?  
                                                                               
                                               
                SELECT

                                               
                CREATE TABLE (*)

                                               
                CREATE ANY TABLE

                                               
                CREATE OBJECT

                                                                               
Which data dictionary view shows which system privileges have been granted to a user?                                                                           
                                               
                USER_SYSTEM_PRIVS

                                               
                USER_SYS_PRIVS (*)

                                               
                USER_TAB_PRIVS

                                               
                USER_SYSTEM_PRIVILEGES

                                                                               
                                                               
Which of the following statements is true?    
                                                                               
                                               
                Database Links are never used in the real world.

                                               
                Database Links allow users to work on remote database objects without having to log into the other database. (*)

                                               
                Database Links are pointers to another schema in the same database.

                                               
                Database Links can be created by any user of a database. You do not need any special privileges to create them.

                                                                               
                                                               
You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use?    
                                                                               
                                               
                GRANT SELECT ON employees TO bob WITH GRANT OPTION; (*)

                                               
                GRANT SELECT ON employees TO bob WITH ADMIN OPTION;

                                               
                GRANT SELECT ON employees TO bob;

                                               
                GRANT SELECT ON employees TO PUBLIC WITH GRANT OPTION;

Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False? 
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
                                                               
Select the correct REGULAR EXPRESSION functions: (Choose two)           
                                                                               
                                               
                REGEXP_LIKE, REGEXP_NEAR

                                               
                REGEXP_REPLACE, REGEXP_REFORM

                                               
                REGEXP_INSTR, REGEXP_SUBSTR (*)

                                               
                REGEXP_LIKE, REGEXP_REPLACE (*)

                                                                               
                                                               
If Oracle crashes, your changes are automatically rolled back. True or False?    
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
                                                               
Which of the following best describes the term "read consistency"?      
                                                                               
                                               
                It ensures that all changes to a table are automatically committed

                                               
                It prevents users from querying tables on which they have not been granted SELECT privilege

                                               
                It prevents other users from querying a table while updates are being executed on it

                                               
                It prevents other users from seeing changes to a table until those changes have been committed (*)

                                                                               
Testing is done by programmers. True or False? 
                                                                               
                                               
                True (*)

                                               
                False

No comments:

Post a Comment

Final Exam Java Programming 2019 Learner - English

Final Exam