728x90 AdSpace

Latest Article

Mutiple Choice Question For Database SQL Query Online Sample

1.               The wise college of IT is a private college providing short courses in information technology (java, oracle, internet etc). The college is expanding and wants to set up a database to help manage the administration of their courses. Students can enrol for as many courses as they wish. When they enrol, they must pay the full cost of all the courses they are enrolling for. Each course runs on a separate day of the week. Only one lecturer teaches on a given course. All courses start on the same date, and end on the same date.

The following are the set of normalised (3NF) relations. (Primary keys emboldened):-

Student (sno, sname, saddr, stelno)
Course (cno, cname, cost, day, lno)
Enrolment (sno, cno, result)
Lecturer (lno lname, laddr, itelno)

Data Dictionary:

Sno                unique identifier for each student
Sname                        name of student
Saddr             address of student
Stelno                        telephone number of student
Cno unique identifier for each course
Cname                       title of a course
Cost               the price of a course
Day                the day (mon, tue, wed etc) that a course runs on
Result                        result student gets for a course….can be either ‘pass’ or ‘fail’
Lno                unique identifier for each lecturer
Lname                        name of lecturer
Laddr             address of lecturer
Itelno             telephone number of lecturer

You are required to:

1.   produce some queries to display                                                                                            

i)                 Popularity of courses.
ii)               Revenue of each course
iii)             Which student is taking which course
iv)             Revenue earned by each course
v)               Which day of the week does java, oracle and internet run on

i                   Which two operators can be used in an outer join condition? (Choose two.)

A). =                    B). OR                        C). IN                          D). AND

ii                 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

iii               Which two clauses must be included in a SELECT statement? (Choose two.)

A). FROM                 B). WHERE                C). SELECT
D). HAVING                        E). ORDER BY          F). GROUP BY

iv               Write a query to display the name, job and salary for all employees whose job is Clerk or Analyst and their salary are not equal to 1000, 3000 or 5000.

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.

vi               Write a query to Display the name of all employees who have two Ls in their name and are in department 30 or their manager is 7782.

vii             In which clause would you place the outer join condition?

A). FROM                       B). WHERE                C). SELECT
D). HAVING                  E). GROUP BY          F). ORDER BY

viii           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

ix               What will be the result of these

            Function                                                      Result
CONCAT(‘Good’, ‘String’)                         ………………………….
SUBSTR(‘String’,1,3)                                   ………………………….
LENGTH(‘String’)                                        ………………………….
INSTR(‘String’, ‘r’)                                      ………………………….
LPAD(sal,10,’*’)                                           ……………………….....

x                   Which single row function can be used on a varchar2 column.

a)  NVL           b)   TRUNC              c)   ROUND               d)  SYSDATE

xi               When sorting query results from a table, what is the sort limit?

A). one column                            B). the number of columns in the table
C). the number of columns in the SELECT list              D). none

xii             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

xiii           Select job, distinct deptno,ename
      From emp;

a)               This query will distinct job, deptno and ename
b)               This will distinct deptno and ename
c)               This will distinct only deptno
d)              This query will not execute successfully.

xiv           1.Create table 1234;
      2.Create table A1234;

A)            Statement 1 will execute successfully.
B)            Statement 2 will execute successfully.
C)            Both will not execute.
D)            Both will execute successfully.

xv             What will be result of these values?

Select ROUND (99.923,2), ROUND (99.923,0), ROUND (99.923,-1), TRUNC(99.923,2), TRUNC(99.923,0), TRUNC(99.923,-1) FROM DUAL;

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

xvii         Which function would you use to display date value in “mm/yy” format?

A). To_Char         B). To_Date     C). To_Number                       D). None

xviii       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.imd_number

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

xx             Which command would cause a pending transaction to end?


no image
  • Title : Mutiple Choice Question For Database SQL Query Online Sample
  • Posted by :
  • Date : 07:10
  • Labels :

  • Blogger Comments
  • Facebook Comments


Post a Comment