ORACLE Solved assignment of amity

Body:

ORACLE

Assignment-A

What are exceptions? How they declared and used in PL/SQL block. What are the main differences between user defined exception system-defined exceptions. Write any five system-defined exceptions.
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.
Differentiate the procedures, Functions and Triggers. What are the advantages of having Subprogram’s rather than the normal PL/SQL blocks?
What do you mean by Integrity constant? Explain in brief different integrity Constraints available in SQL?
What is structured Query Language? Explain in brief different type of SQL Sub Language?

Assignment-B

CREATE SALESPEOPLE, CUSTOMER AND ORDER TABLE WITH FOLLOWING

ATTRIBUTES AND INSERT VALUES IN THEM.

SALESPEOPLE CUSTOMER ORDER

SNUM CNUM ONUM
SNAME CNAME AMT

CITY CITY O DATE

COMM RATING CNUM

SNUM SNUM

3.What are cursors and for what purpose these are used in PL/SQL block. Explain various cursor attributes.

Assignment – 3

Which character function can be used to return a specified portion of a character string?

INSTR

SUBSTRING

SUBSTR

POS

What does SQL stand for?

Structured Query Language

Strong Question Language

Structured Question Language

None of the above

Ans – Structure Query Language

Question No: 3

Which SQL statement is used to update data in a database?

SAVE

MODIFY

UPDATE

SAVE AS

Question No: 4

Which SQL statement is used to delete data from a database?

DELETE

COLLAPSE

REMOVE

None of the above

Ans -

Question No: 5

With SQL, how do you select a column named "First Name" from a table named "Persons"?

SELECT Persons.FirstName

EXTRACT First Name FROM Persons

SELECT First Name FROM Persons

None of the above

Question No: 6

With SQL, how do you select all the columns from a table named "Persons"?

SELECT [all] FROM Persons

SELECT Persons

SELECT *.Persons

SELECT * FROM Persons

Ans – SELECT * FROM Persons

Question No: 7

With SQL, how do you select all the records from a table named "Persons" where the value of the column "First Name" is "Peter"?

SELECT [all] FROM Persons WHERE First Name='Peter'

SELECT * FROM Persons WHERE First Name='Peter'

SELECT * FROM Persons WHERE First Name LIKE 'Peter'

SELECT [all] FROM Persons WHERE First Name LIKE 'Peter'

Ans –

Question No: 8

How many columns are presented after executing this query: SELECT address1||','||address2||','||address2 "Adress" FROM employee;

None of the above

Ans – 1

Question No: 9

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

Primary key access

Access via unique index

Table access by ROWID

Full table scan

Ans – Ta

Question No: 10

Which of the following can be a valid column name?

Column

1966_Invoices

Catch_#22

None of the above

Ans – Catch_#22

Question No: 11

Which command will delete all data from a table and will not write to the rollback segment?

DROP

DELETE

CASCADE

TRUNCATE

Ans – TRUNCATE

Question No: 12

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

SELECT * FROM Persons WHERE First Name LIKE '%a'

SELECT * FROM Persons WHERE First Name='a'

SELECT * FROM Persons WHERE First Name LIKE 'a%'

SELECT * FROM Persons WHERE First Name='%a%'

Ans –

Question No: 13

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

False

True

Both (a) and (b)

None of the above

Ans – True

Question No: 14

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

SELECT * FROM Persons WHERE First Name='Peter' AND Last Name='Jackson'

SELECT * FROM Persons WHERE First Name LIKE 'Peter' AND Last Name LIKE 'Jackson'

SELECT First Name='Peter', Last Name='Jackson' FROM Persons

None of the above

Question No: 15

Which SQL statement is used to return only different values? SELECT DIFFERENT

SELECT DIFFERENT

SELECT DISTINCT

SELECT UNIQUE

None of these

Question No: 16

Which SQL keyword is used to sort the result-set?`

ORDER

SORT

SORT BY

ORDER BY

Question No: 17

With SQL, how can you return all the records from a table named "Persons" sorted descending by "First Name"?

SELECT * FROM Persons SORT 'First Name' DESC

SELECT * FROM Persons ORDER BY First Name DESC

SELECT * FROM Persons ORDER First Name DESC

SELECT * FROM Persons SORT BY 'First Name' DESC

Question No: 18

With SQL, how can you insert "Olsen" as the "Last Name" in the "Persons" table?

INSERT INTO Persons ('Olsen') INTO Last Name

INSERT INTO Persons (Last Name) VALUES ('Olsen')

INSERT ('Olsen') INTO Persons (Last Name)

None of these

Question No: 19

With SQL, how can you delete the records where the "First Name" is "Peter" in the Persons Table?

DELETE FROM Persons WHERE First Name = 'Peter'

DELETE First Name='Peter' FROM Persons

DELETE ROW First Name='Peter' FROM Persons

None of these

Question No: 20

With SQL, how can you return the number of records in the "Persons" table?

SELECT COLUMNS (*) FROM Persons

SELECT COUNT () FROM Persons

SELECT COUNT (*) FROM Persons

SELECT COLUMNS () FROM Persons

Question No: 21

You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?

SELECT last_name, department_name
FROM employees e LEFT OUTER
JOIN departments d ON (e.department_id = d.department_id);

SELECT last_name, department_name
FROM employees e RIGHT OUTER
JOIN departments d ON (e.department_id = d.department_id);

SELECT last_name, department_name
FROM employees e FULL OUTER
JOIN departments d ON (e.department_id = d.department_id);

None

Question No: 22

Which statement about views are true?

A view can be created as read only.

A view cannot have an ORDER BY clause in the SELECT statement.

A view cannot be created with a GROUP BY clause in the SELECT statement.

A view must have aliases defined for the column names in the SELECT statement

Ans – A view can be created as read only

Question No: 23

In which case would you use a FULL OUTER JOIN?

Both tables have NULL values.

You want all unmatched data from one table.

You want all matched data from both tables.

None of the above

Ans –None of the above

Question No: 24

Which of the statement is true regarding the use of outer joins?

You cannot use IN operator in a condition that involves an outerjoin.

You use (+) on both sides of the WHERE condition to perform an outerjoin.

You use (*) on both sides of the WHERE condition to perform an outerjoin.

You use an outerjoin to see only the rows that do not meet the join condition.

Ans – You use an outer join to see only the rows that do not meet the join condition.

Question No: 25

Examine the structure of the EMPLOYEES and DEPARTMENTS tables

EMPLOYEES
EMPLOYEE_ID NUMBER
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
LAST_NAME VARCHAR2(25)

DEPARTMENTS
DEPARTMENT_ID NUMBER
MANAGER_ID NUMBER
DEPARTMENT_NAME VARCHAR2(35)
LOCATION_ID 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?

SELECT last_name, department_name, location_id FROM employees , departments ;

SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id;

SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id;

Ans –SELECT e.last_name, d.department_name, d.location_id FROM employee e, department D WHERE e.department_id =d.department_id.

Question No: 26

In which case would you use an outer join?

The tables being joined have NOT NULL columns.

The tables being joined have only matched data.

The tables being joined have only unmatched data.

None of the above

Question No: 27

The primary key on table EMP is the EMPNO column. Which of the following statements will not use the associated index on EMPNO?

select * from EMP where nvl(EMPNO, '00000') = '59384';

select * from EMP where EMPNO = '59384';

select EMPNO, LASTNAME from EMP where EMPNO = '59384';

select 1 from EMP where EMPNO = '59834';

Question No: 28

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?

None of the above

Question No: 29

which of the following SQL functions can operate on any datatype?

TO_CHAR

LOWER

LPAD

None of the above

Question No: 30

What is a trigger

A piece of logic written in PL/SQL

Executed at the arrival of a SQL*FORMS event

Both A & B

None of the above

Question No: 31

POST-BLOCK trigger is a

Navigational trigger

Key Trigger

Transactional Trigger

None of the above

Question No: 32

Databases overall structure is maintained in a file called

Redolog file,

Data file,

Control file,

All of the above

Ans –Control file

Question No: 33

It is very difficult to grant and manage common privileges needed by different groups of database users using the roles

True

False

Ans – False

Question No: 34

The command used to open a CURSOR FOR loop is

open

fetch

parse

None, cursor for loops handle cursor opening implicitly

Ans – None , cursor for loops handle cursor opening implicitly.

Question No: 35

Can we invoke Triggers?

Yes

Ans –Yes

Question No: 36

Which clause specifies conditions that determine the

Having Clause

Where Clause

Distinct

Exists

Ans – Having Clause

Question No: 37

Which clause returns only one copy of each set of duplicate rows selected?

Unique

Group By

Distinct

None of the above

Ans – Distinct

Question No: 38

Which command is used to set a set of privileges that can be granted to users or to others roles ?

Create Role

Create Grant

Create Authority

Create Authentication

Ans –Create Role

Question No: 39

Which operator is used in character strings comparison with pattern matching?

Between…And

Equal Operator

Set Operator

Question No: 40

Which three of the following are implicit cursor attributes?

%found

%too_many_rows

%notfound

None of th

Title:

ORACLE Solved assignment of amity (With Online Typing & Filling)

Short Name or Subject Code: ORACLE Solved assignment of amity

Short Description: .

University: Amity

Select Type:

With Online Typing & Filling

Service Type: Assignments

Select Semester: Semester- II Select Cource: B.Sc (IT)

commerce line item type:

Price:

₹800.00

Product:

ORACLE Solved assignment of amity

Dissertation Work

3.What are cursors and for what purpose these are used in PL/SQL block. Explain various cursor attributes.

Ans – SELECT * FROM Persons

Ans – True

Filll this form and get price list instantly

Academic Assignment Writing Services

Academic Assignment Writing Services Work :

Search form

ORACLE Solved assignment of amity

Dissertation Work

3.What are cursors and for what purpose these are used in PL/SQL block. Explain various cursor attributes.

Ans – SELECT * FROM Persons

Ans – True

Academic Assignment Writing Services Work :