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)
