Online Exam Sample Question for Oracle Database and SQL Query Midterm Final its Helpfull for preparation
1. 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
2. The PERSONNEL table contains these columns:
ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
MANAGER_ID NUMBER(9)
For this example, department managers are personnel.
Evaluate these two SQL statements:
SELECT p.last_name, p.first_name, m.last_name, m.first_name
FROM personnel p, personnel m
WHERE m.id = p.manager_id;
SELECT p.last_name, p.first_name, m.last_name, m.first_name
FROM personnel p, personnel m
WHERE m.manager_id = p.id;
How do the two SQL statements differ?
A. One of the statements will not execute.
B. One of the statements is not a self-join.
C. The results of the statements will be the same, but the format will be different.
D. The results of the statements will be different; but the display will be the same.
3. 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.
4. 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.
5. 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;
6. 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
7. The STUDENT table contains these columns:
ID NUMBER(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Evaluate this SQL statement:
SELECT *
FROM student
WHERE id =
(SELECT id
FROM student
WHERE UPPER(last_name) = 'SHEHZAD')
AND UPPER(first_name) = 'IRFAN');
What would cause this statement to fail?
A. There are no students named IRFAN SHEHZAD.
B. There is more than one student named SHEHZAD.
C. There is more than one student named IRFAN SHEHZAD.
D. The FIRST_NAME and LAST_NAME values in the database are in lowercase.
8. 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.
9. You attempt to query the database with this command:
SELECT inventory.id_number, manufacturer.id_number
FROM inventory i, manufacturer m
WHERE i.manufacturer_id = m.id_number
ORDER BY 1;
Which clause causes an error?
A. ORDER BY 1;
B. FROM inventory i, manufacturer m
C. WHERE i.manufacturer_id = m.id_number
D. SELECT inventory.id_number, manufacturer.id_number
10. Evaluate this PL/SQL block:
DECLARE
v_quota BOOLEAN := NULL;
v_stock BOOLEAN := NULL;
v_approval BOOLEAN;
BEGIN
v_approval := v_quota AND v_stock;
END;
Which value is assigned to the V_APPROVAL?
A). TRUE B). NULL C). FALSE D). NONE
1.A database server is a server that:
A). hosts application logic and services for an information system, that must communicate on the front end with clients, and on the back end with database servers.
B). hosts one or more shared databases but also executes all database commands and services for information systems.
C). hosts services for e-mail, calendaring, and other work group functionality.
D). hosts services that ultimately ensure that all database updates for a single business transaction succeed or fail as a whole.
E). none of the above
2.1. Create table 1234
(Empno number);
Create Table A1234
(Empno number);
A. Statement 1 will execute successfully.
B. Statement 2 will execute successfully.
C. Both will not execute.
D. Both will execute successfully.
3.For which condition would you use an equijoin query?
A. The INVENTORY table has two corresponding columns.
B. The INVENTORY and MANUFACTURER tables have corresponding columns.
C. The INVENTORY and MANUFACTURER tables do not have any corresponding columns.
D. The INVENTORY and MANUFACTURER tables have corresponding columns, but the column in the INVENTORY table contains null values that need to be displayed.
4.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. ELECT 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
5.Evaluate this command:
SELECT id_number "Part Number", SUM(price) "Price"
FROM inventory
WHERE price > 5.00
GROUP BY "Part Number"
ORDER BY 2;
Which clause will cause an error?
A. ORDER BY 2;
B. FROM inventory
C. WHERE price > 5.00
D. GROUP BY "Part Number"
E. SELECT id_number "Part Number", SUM(price) "Price"
6.You attempt to query the database with this command:
SELECT 100/NVL(quantity, 0)
FROM inventory;
Why does this statement cause an error when QUANTITY values are null?
A. The expression attempts to divide by zero.
B. The expression attempts to divide by a null value.
C. The datatypes in the conversion function are incompatible.
D. A null value used in an expression cannot be converted to an actual value.
7.Evaluate this command:
SELECT id_number
FROM inventory
WHERE manufacturer_id IN
(SELECT manufacturer_id
FROM inventory
WHERE price < 1.00
OR price > 6.00);
How many values can the subquery return?
A). 0 B). ONLY 1 C). UP TO 2 D). UNLIMITED
8.You issue this command:
GRANT update
ON inventory
TO joe
WITH GRANT OPTION;
Which task has been accomplished?
A. Only a system privilege was given to user JOE.
B. Only an object privilege was given to user JOE.
C. User JOE was granted all privileges on the object.
D. Object privilege and a system privilege were given to user JOE.
9.You attempt to query the database with this command:
SELECT inventory.id_number, manufacturer.id_number
FROM inventory i, manufacturer m
WHERE i.manufacturer_id = m.id_number
ORDER BY 1;
Which clause causes an error?
A. ORDER BY 1;
B. FROM inventory i, manufacturer m
C. WHERE i.manufacturer_id = m.id_number
D. SELECT inventory.id_number, manufacturer.id_number
