728x90 AdSpace

Latest Article

Practice Question for SQL Query Database Sample Online MCQs

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            

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
               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

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

B.                 CREATE VIEW parts_456874_vu
AS SELECT   id_number, description, quantity
FROM            inventory
HAVING         manufacturer_id = 456874

C.                 CREATE VIEW parts_456874_vu
AS SELECT    id_number, description, quantity
FROM             inventory
WHERE          manufacturer_id = 456874

D.                CREATE VIEW parts_456874_vu
AS SELECT     id_number, description, quantity
FROM              inventory
WHERE           manufacturer_id = 456874

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)

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

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            

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

B.                 ALTER TABLE inventory

C.                 ALTER TABLE inventory

D.                ALTER TABLE inventory

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

no image
  • Title : Practice Question for SQL Query Database Sample Online MCQs
  • Posted by :
  • Date : 07:15
  • Labels :

  • Blogger Comments
  • Facebook Comments


Post a Comment