Assignment A
Q1) What is Structured Query Language? Explain in brief different type of SQL Sub Languages?
Q2) What do you mean by Integrity Constraint? Explain in brief different integrity Constraints available in SQL?
Q3) Differentiate the Procedures, Functions and Triggers. What are the advantages of having subprograms rather than the normal PL/SQL blocks?
Q4) How the various SQL statements are executed within a PL/SQL block. What are the advantages of using SQL statements within PL/SQL block rather than executing them separately?
Q5) What are exceptions? How they declared and used in PL/SQL block. What are the main differences between user-defined exceptions and system-defined exception. Write any five system-defined exception used in Oracle.
Assignment B
Q1) CREATE SALESPEOPLE, CUSTOMER AND ORDER TABLE WITH FOLLOWING ATTRIBUTES AND INSERT VALUES IN THEM.
SALESPEOPLE
SNUM SNAME CITY COMM
CUSTOMER ORDE
CNUM ONUM
CNAME AMT
CITY ODATE
RATING CNUM
SNUM SNUM
Q2) On the Basis of Below three tables (Salesppl, Customer & Order_dept) solve the queries.
SALESPPL
SNUM SNAME CITY COMM
1 manish bombay 20
2 rakesh delhi 20
3 mohit banglore 30
4 John delhi 50
5 asley delhi 10
6 aman bombay 20
7 deepika banglore 30
8 sangeeta Jaipur 15
9 yogesh chandigarh 45
10 liri delhi 20
CUSTOMER
CNUM CNAME CITY RATING SNUM
1 mahesh delhi 10 2
2 preeti bombay 10 1
3 rajan banglore 30 10
4 kritika delhi 20 2
5 kashish bombay 10 1
6 anurag Jaipur 30 2
7 poonam Chandigarh 30 6
8 ankit delhi 10 8
9 anuj Jaipur 20 4
10 anushka bombay 30 2
ORDER_DET;
ONUM AMT ODATE CNUM SIN
1 15000 01-JAN-99 1 2
2 20000 15-JAN-99 4 5
3 4500 10-MAR-98 2 2
4 5780 10-JAN-99 3 7
5 1000 13-MAR-99 2 8
6 8976 01-JAN-99 2 1
7 1345 22-SEP-99 1 1
8 5694 28-AUG-99 2 3
9 3456 01-JAN-99 7 7
10 1234 13-APR-99 4 6
11 30000 30-JUN-99 1 2
a) COUNT THE NUMBER OF SALESPEOPLE GETTING COMMISSION >20
b) GIVE MAXIMUM AMOUNT ORDER DETAILS DATEWISE.
c) GIVE TOTAL AMOUNT OF ORDERS ON ’01-JAN-99′.
d) QUERY THAT SELECT THE HIGHEST RATING IN EACH CITY.
e) WRITE THE QUERY STATEMENT THAT DISPLAYS SALESPERSON GETTING SAME COMMISSION.
f) QUERY THAT GIVES THE NAMES OF BOTH THE SALESPERSON AND CUSTOMER FOR EACH ORDER NUMBER.
g) QUERY THAT GIVES THE ORDER DETAILS OF ALL THE CUSTOMERS WHO ARE FROM CITY ‘BOMBAY’.
h) QUERY THAT GIVES THE CUSTOMER DETAILS WITH ORDER AMOUNT>15000.
i) QUERY THAT GIVES THE DETAILS OF SALESPEOPLE HAVING MORE THAN 1 CUSTOMER.
j) WRITE AN UPDATE STATEMENT TO INC. THE RATING OF ALL BOMBAY CUSTOMERS BY 50.
Q3) What are cursors and for what purpose these are used in PL/SQL block. Explain various cursor attributes.
Assignment C
1. What does SQL stand for?
a) Structured Query Language
b) Strong Question Language
c) Structured Question Language
2. Which SQL statement is used to update data in a database?
a) SAVE
b) MODIFY
c) UPDATE
d) SAVE AS
3.Which SQL statement is used to delete data from a database?
a) DELETE
b) COLLAPSE
c) REMOVE
4. With SQL, how do you select a column named “First Name” from a table named “Persons”?
a) SELECT Persons.FirstName
b) EXTRACT First Name FROM Persons
c) SELECT First Name FROM Persons
5. With SQL, how do you select all the columns from a table named “Persons”?
a) SELECT [all] FROM Persons
b) SELECT Persons
c) SELECT *.Persons
d) SELECT * FROM Persons
6. With SQL, how do you select all the records from a table named “Persons” where the value
of the column “First Name” is “Peter”?
a) SELECT [all] FROM Persons WHERE First Name=’Peter’
b) SELECT * FROM Persons WHERE First Name=’Peter’
c) SELECT * FROM Persons WHERE First Name LIKE ‘Peter’
d) SELECT [all] FROM Persons WHERE First Name LIKE ‘Peter’
7. With SQL, how do you select all the records from a table named “Persons” where the value
of the column “First Name” starts with an “a”?
a) SELECT * FROM Persons WHERE First Name LIKE ‘%a’
b) SELECT * FROM Persons WHERE First Name=’a’
c) SELECT * FROM Persons WHERE First Name LIKE ‘a%’
d) SELECT * FROM Persons WHERE First Name=’%a%’
8.The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
a) False
b) True
9. With SQL, how do you select all the records from a table named “Persons” where the
“FirstName” is “Peter” and the “Last Name” is “Jackson”?
a) SELECT * FROM Persons WHERE First Name=’Peter’ AND Last Name=’Jackson’
b) SELECT * FROM Persons WHERE First Name LIKE ‘Peter’ AND Last Name LIKE ‘Jackson’
c) SELECT First Name=’Peter’, Last Name=’Jackson’ FROM Persons
10. Which SQL statement is used to return only different values?
a) SELECT DIFFERENT
b) SELECT DISTINCT
c) SELECT UNIQUE
11. Which SQL keyword is used to sort the result-set?
a) ORDER
b) SORT
c) SORT BY
d) ORDER BY
12. With SQL, how can you return all the records from a table named “Persons” sorted descending by “First Name”?
a) SELECT * FROM Persons SORT ‘First Name’ DESC
b) SELECT * FROM Persons ORDER BY First Name DESC
c) SELECT * FROM Persons ORDER First Name DESC
d) SELECT * FROM Persons SORT BY ‘First Name’ DESC
13.) With SQL, how can you insert “Olsen” as the “Last Name” in the “Persons” table?
a) INSERT INTO Persons (‘Olsen’) INTO Last Name
b) INSERT INTO Persons (Last Name) VALUES (‘Olsen’)
c) INSERT (‘Olsen’) INTO Persons (Last Name)
14.) With SQL, how can you delete the records where the “First Name” is “Peter” in the Persons Table?
a) DELETE FROM Persons WHERE First Name = ‘Peter’
b) DELETE First Name=’Peter’ FROM Persons
c) DELETE ROW First Name=’Peter’ FROM Persons
15.With SQL, how can you return the number of records in the “Persons” table?
a) SELECT COLUMNS (*) FROM Persons
b) SELECT COUNT () FROM Persons
c) SELECT COUNT (*) FROM Persons
d) SELECT COLUMNS () FROM Persons
16) You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?
A) SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);
B) SELECT last_name, department_name
FROM employees e RIGHT OUTER
JOIN departments d ON (e.department_id = d.department_id);
C) SELECT last_name, department_name
FROM employees e FULL OUTER
JOIN departments d ON (e.department_id = d.department_id);
17)Which statement about views are true?
A) A view can be created as read only.
B) A view cannot have an ORDER BY clause in the SELECT statement.
C) A view cannot be created with a GROUP BY clause in the SELECT statement.
D) A view must have aliases defined for the column names in the SELECT statement
18) ln which case would you use a FULL OUTER JOIN?
A) Both tables have NULL values.
B) You want all unmatched data from one table.
C) You want all matched data from both tables.
D) You want all unmatched data from both tables.
E) One of the tables has more data than the other.
F) You want all matched and unmatched data from only one table.
19) Which of the statement is true regarding the use of outer joins?
A) You cannot use IN operator in a condition that involves an outerjoin.
B) You use (+) on both sides of the WHERE condition to perform an outerjoin.
C) You use (*) on both sides of the WHERE condition to perform an outerjoin.
D) You use an outerjoin to see only the rows that do not meet the join condition.
20) Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEEJD NUMBER DEPARTMENTJD NUMBER MANAGERJD NUMBER LAST_NAME VARCHAR2(25)
DEPARTMENTS
DEPARTMENTJD NUMBER
MANAGERJD NUMBER
DEPARTMENTJlAME VARCHAR2(35)
LOCATIONJD NUMBER
You want to create a report displaying employee last names, department names, and
locations. Which query should you use to create an equi-join?
A) SELECT last_name, department_name, locationjd FROM employees , departments ;
B) SELECT employees.Iast_name, departments.department_name, departments.locationjd FROM employees e, departments D WHERE e.departmentjd =d.departmentjd;
C) SELECT e.last_name, d.department_name, d.locationjd FROM employees e, departments D WHERE managerjd =managerjd;
D) SELECT e.last_name, d.department_name, d.locationjd FROM employees e, departments D WHERE e.departmentjd =d.departmentjd;
21) In which case would you use an outer join?
A) The tables being joined have NOT NULL columns.
B) The tables being joined have only matched data.
C) The tables being joined have only unmatched data.
D) The tables being joined have both matched and unmatched data.
E) Only when the tables have a primary key/foreign key relationship.
PL/ SQL Part (C.2)
1) How many columns are presented after executing this query:
SELECTaddresslH71 |address2| |’/| |address2 “Adress” FROM employee;
A)l B)2 C)3 D)0 E)4
2) Which Oracle access method is the fastest way for Oracle to retrieve a single row?
A) Primary key access
B) Access via unique index
C) Table access by ROWID
D) Full table scan
3) Which of the following can be a valid column name?
A) Column
B) 1966_lnvoices
C) Catch_#22
D) #lnvoices
E) None of the above
4) Which command will delete all data from a table and will not write to the rollback
segment?
A) DROP
B) DELETE
C) CASCADE
D) TRUNCATE
5) Which character function can be used to return a specified portion of a character string?
A) INSTR
B) SUBSTRING C)SUBSTR D)POS
6) The primary key on table EMP is the EMPNO column. Which of the following statements
will not use the associated index on EMPNO?
A) select * from EMP where nvl(EMPNO, ‘00000’) = ‘59384’;
B) select * from EMP where EMPNO = ‘59384’;
C) select EMPNO, LASTNAME from EMP where EMPNO = ‘59384’;
D) select 1 from EMP where EMPNO = ‘59834’;
7) To produce a meaningful result set without any cartesian products, what is the minimum
number of conditions that should appear in the WHERE clause of a four-table join?
A) 8 B)2 C)3 D)4 E)5
F) There is no such criteria
TIP: You must have at least (N-1) join conditions to prevent a cartesian product
8) which of the following SQL functions can operate on any datatype?
A)TO_CHAR
B) LOWER
C) LPAD D)MAX E) CEIL
9) What is a trigger
A) A piece of logic written in PL/SQL
B) Executed at the arrival of a SQL*FORMS event
C) Both A & B
D) None of the above
10) POST-BLOCK trigger is a
A) Navigational trigger
B) Key Trigger
C) Transactional Trigger
D) None of the above
11) Databases overall structure is maintained in a file called
A) Redologfile,
B) Data file,
C) Control file,
D) All of the above
12) It is very difficult to grant and manage common privileges needed by different groups of
database users using the roles
A)True B) False
13) The command used to open a CURSOR FOR loop is
a. open
b. fetch
c. parse
d. None, cursor for loops handle cursor opening implicitly
14) Can we invoke Triggers?
A) Yes
B) No
15) Which clause specifies conditions that determine the groups included in the query.
A) Having Clause
B) Where Clause
C) Distinct
D) Exists
016) Which clause returns only one copy of each set of duplicate rows selected?
A) Unique
B) Group By
C) Distinct
D) None of the above
17) Which command is used to set a set of privileges that can be granted to users or to others
roles ?
A) Create Role
B) Create Grant
C) Create Authority
D) Create Authentication
18) Which operator is used in character strings comparison with pattern matching?
A) Like
B) Between…And
C) Equal Operator
D) Set Operator
19) Which three of the following are implicit cursor attributes?
a. %found
b. %too_many_rows
c. %notfound
d. %rowcount
e. %rowtype
Reviews
There are no reviews yet.