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