1.
The wise college of IT
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
2.
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 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.
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?
A) ALTER B) DELETE C) INSERT
D) UPDATE E) SELECT
0 comments:
Post a Comment