Oracle-1

$10.00

SKU: AMSEQ-200 Category:

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

DEPARTMENTJ\lAME 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