1.
You attempt to query the database with this command: (25)
SELECT NVL (100 / quantity, ‘none’)
FROM
inventory;
Why does this
statement cause an error when QUANTITY values are null?
A.
The expression attempts to
divide by a null value.
B.
The data types in the
conversion function are incompatible.
C.
The character string none
should be enclosed in single quotes (' ').
D.
A null value used in an
expression cannot be converted to an actual value.
2.
For which task would you use the TO_DATE function?
A.
to convert a number value to a
date value
B.
to convert a date value to a
number value
C.
to convert a date value to a
VARCHAR2 character string
D.
to convert a character string
representing a date to a date value
E.
You query
the database with this command.
3.
SELECT id_number,
description, price "Cost Per Unit"
FROM inventory
WHERE price > 5.00
ORDER BY "Cost Per Unit";
How is the data
sorted?
A). Randomly B). ascending numerically C). descending numerically
D). ascending
alphabetically E). descending
alphabetically
4.
Evaluate
this command:
SELECT COUNT(*)
FROM inventory
WHERE price > 5.00
GROUP BY
manufacturer_id
HAVING COUNT(*) > 10
ORDER BY
order_date;
Which clause
specifies which rows will be returned from the INVENTORY table?
A.
WHERE price > 5.00
B.
HAVING COUNT(*) > 10
C.
ORDER BY order_date;
D.
GROUP BY manufacturer_id;
5.
Which two commands would cause an implicit COMMIT command? (Choose
two.)
A). GRANT B). UPDATE C).
CREATE D). COMMIT
E). SELECT F). ROLLBACK
6.
Which two operators can be used in an outer join condition? (Choose
two.)
A). = B). OR C). IN
D). AND
7.
SQL> Delete from emp where rowid in ( SELECT rowid FROM emp group by rowid,empno,ename,job minus SELECT min(rowid) FROM emp group by empno,ename,job); A.This query will delete all the rows in the emp table.
B.This query will delete all the duplicate rows in the emp table.
C.This query will delete only the empno, ename and job values in the emp table.
D.This query will generate an error.
8.
You attempt to query the
database with this command:
SELECT i.id_number, m.id_number
FROM inventory i, manufacturer m
WHERE i.manufacturer_id = m.id_number
ORDER BY inventory.description;
Which clause
causes an error?
A.
ORDER BY inventory.description;
B.
FROM inventory i, manufacturer
m
C.
WHERE i.manufacturer_id =
m.id_number
D.
SELECT i.id_number,
m.manufacturer_name
9.
How Column headings are
displayed:
A.
Date left justified
B.
Character left justified
C.
Number right justified
D.
All of the above
E.
None of the above
10.
Which characteristic relates to
a single row function?
A.
Act on each row returned in the
query.
B.
Return one result per row.
C.
May return a data value of a
different type than that referenced.
D.
May expect one or more argument.
E.
All of the above.
F.
None of the above.
11. Which function would you use to display date value in “mm/yy”
format?
A). To_Char B).
To_Date C). To_Number D). None
12.
The STUDENT table contains
these columns:
ID NUMBER(9) PK
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
SUBJECT_ID NUMBER(9)
Compare these two
SQL statements:
1. SELECT
DISTINCT subject_id, last_name, first_name
FROM student
ORDER BY
1;
2. SELECT
id, last_name, first_name, subject_id
FROM student
ORDER BY
subject_id;
How will the
results differ?
A.
Statement 1 will be sorted
alphabetically; statement 2 will not.
B.
Statement 1 will limit
duplicate subject ids; statement 2 will not.
C.
Statement 1 will not eliminate
duplicate rows from the output; statement 2 will.
D.
Statement 2 will display
distinct combinations of the values in the STUDENT table; statement 1 will not.
13.
Evaluate this command:
SELECT id_number, description, SUM(price)
FROM inventory
WHERE
price > 6.00
GROUP BY
id_number
ORDER BY
manufacturer_id;
Why will this
command cause an error?
A.
The PRICE column must be
included in the GROUP BY clause.
B.
The ORDER BY clause should
immediately follow the WHERE clause.
C.
The MANUFACTURER_ID column is
not included in the SELECT clause.
D.
The ORDER BY clause cannot be
used in a SELECT statement with a GROUP BY clause.
E.
The DESCRIPTION and
MANUFACTURER_ID columns are not included in the GROUP BY clause.
14. Evaluate this command:
CREATE FORCE VIEW id_number_description
AS SELECT
id_number "Product Number", description
FROM inventory
WHERE price > 5.00
GROUP BY
description
ORDER BY
id_number;
Which clause will
cause an error?
A.
FROM inventory
B.
WHERE price > 5.00
C.
ORDER BY id_number;
D.
GROUP BY description
E.
AS SELECT id_number
"Product Number", description.
15. You query the database with this command:
SELECT manufacturer_id
FROM inventory
WHERE manufacturer_id LIKE '%N\%P\%O%' ESCAPE ‘\’;
For which character pattern is the LIKE operator searching?
A). NPO B).
N\P\O C). N%P%O D). N\%P\%O
16.
Which SQL statement creates the
PARTS_456874_VU view that contains the ID_NUMBER, DESCRIPTION, and QUANTITY
columns for MANUFACTURER_ID 456874 from the INVENTORY table and does not allow
the manufacturer values to be changed through the view?
A.
CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
WITH CHECK
CONSTRAINT;
B.
CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
HAVING manufacturer_id = 456874
WITH READ ONLY;
C.
CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
WHERE manufacturer_id = 456874
WITH READ ONLY;
D.
CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
WHERE manufacturer_id = 456874
WITH CHECK OPTION;
17.
Character functions Accept
character input and can return:
A.
Only character data.
B.
Both character and number data.
C.
Only number data.
D.
None of the above.
18.
Which statement would you use to query
the database for the ID_NUMBER and DESCRIPTION values of each item that was
ordered before January 1,
1997 and whose price is less than 1.00 or greater than 5.00?
A.
SELECT id_number, description
FROM inventory
WHERE price IN (1.00, 5.00)
OR order_date < '01-JAN-97';
B.
SELECT id_number, description
FROM inventory
WHERE price BETWEEN 1.00 AND 5.00
OR order_date < '01-JAN-97';
C.
SELECT id_number, description
FROM inventory
WHERE price < 1.00
OR price > 5.00
AND order_date < '01-JAN-97';
D.
SELECT id_number, description
WHERE (price <1.00 OR price > 5.00)
AND order_date < '01-JAN-97';
FROM inventory
19.
You query the database with this
command:
SELECT price
FROM
inventory
WHERE
price (BETWEEN 1 AND 50)
OR
(price IN(25, 70, 95)
AND
price BETWEEN 25 AND 75);
Which value could
the statement retrieve?
A). 30 B). 51 C). 75 D).
95
20.
The TEACHER table contains these
columns:
ID NUMBER (9)
SALARY NUMBER (7, 2)
SUBJECT_ID
NUMBER (3)
You need to create a SQL script that will
prompt the user to input an id number and a percent increase value. Each teacher's salary should be multiplied by
the percent increase provided.
Which SQL*Plus script would you use to
achieve the desired results?
A.
UPDATE teacher
SET salary = salary * &increase;
B.
UPDATE teacher
SET salary = salary * &increase
WHERE id
= &id;
C.
UPDATE teacher
SET salary = &salary
WHERE subject_id = &subject_id
D.
UPDATE teacher
SET salary = salary * &increase
WHERE subject_id = &subject_id
21.
You issue this command:
CREATE FORCE VIEW parts_vu
(company, contact)
AS SELECT manufacturer_name, contact_name
FROM inventory
WITH READ ONLY;
Which command can
be issued on the PARTS_VU view?
A). UPDATE B). DELETE C). SELECT D). INSERT
22.
You logged onto the database to update
the INVENTORY table. After your session
began, you issued three UPDATE commands and then you issued an ALTER table
command to add a column constraint. You
were about to issue a COMMIT command when the system crashed. Which changes were made to the INVENTORY
table?
A.
Only the UPDATE commands.
B.
Only the ALTER TABLE command.
C.
both the UPDATE commands and
the ALTER TABLE command
D.
NONE.
23.
Which two commands would cause an
implicit COMMIT command? (Choose two.)
A). GRANT B). UPDATE C).
CREATE D). COMMIT
E). SELECT F). ROLLBACK
24.
Which clause restricts the groups of
rows displayed to those groups meeting a specified condition?
A). FROM B). WHERE C). SELECT
D). HAVING E). GROUP BY F). ORDER BY
25.
Which ALTER TABLE statement would you
use to add a primary key constraint on the MANUFACTURER_ID column of the
INVENTORY table?
A.
ALTER TABLE inventory
ADD CONSTRAINT
manufacturer_id PRIMARY KEY;
B.
ALTER TABLE inventory
ADD CONSTRAINT
PRIMARY KEY (manufacturer_id);
C.
ALTER TABLE inventory
MODIFY
manufacturer_id CONSTRAINT PRIMARY KEY;
D.
ALTER TABLE inventory
MODIFY CONSTRAINT
PRIMARY KEY manufacturer_id;
(Part II)
2.
(10)
I.
Write a query to display the total
number of employees and of that total the number who were hired in
1980,1981,1982 and 1983.
II.
Write a query that will Display the name
of all employees with the first letter capitalized and all other letters
lowercase and the length of their name, for all employees whose name starts
with J, A or ends with N. give each column an appropriate heading.
III.
Create a view named mbaitm4 that
contains the employee number, employee name, and department number for all
employees in department 20. Label the view columns employee_id, employee, and department_id.
IV.
Create a sequence to be used with the
primary key column of the department table. The sequence should start at 60 and
have a maximum value of 200. Have your sequence increment by ten numbers. Name
the sequence dept_id_seq.
V.
Write a query to display the name,
salary and commission for all employees whose commission amount is greater than
their salary increased by 10%. Save your query as p2q13.sql.
3.
(A). How
can you create alter and drop views and sequences provide some examples.(8)
(B). Write a Script to
create a Script with some table and column level constraints. (7)
0 comments:
Post a Comment