Questions to Solve Oracle SQL(Objectives)

1) A bank gives three types of loans: personal, auto, home. The details of all types of loans are stored in the loan table. However there are some attributes that don’t apply to all kinds of loans. For e.g. car no will be applicable only to auto loan. In this case all non-auto loans will contain null values stored in car no attribute. Which sub entities will u creates to replace optional attributes and specialize this scenario.

A) By creating a subentitiy named auto to loan superentity

B) By creating three subentites named auto, home personal to loan superentity

C) By creating a subentity name personal and home to loan superentity

2) In a university students enroll for different types of courses. James is a student. He enrolls for a course in Philosophy .Identity the entity instances in this scenario?

A) James and Student

B) Philosophy and Course

C) James and Philosophy

D) Student and Course

3) Evaluate this SQL statement:

SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

In the statement, which capabilities of a SELECT statement are performed?

A. Selection, projection, join

B. Difference, projection, join

C. Selection, intersection, join

D. Intersection, projection, join

E. Difference, projection, product

4) Consider a relationship between a vehicle and its types. The truck and car are types of car .The relationship is represented as follows in ERD.Identify the relationship (2 mark)

Publish Post

A) Specialization

B) Generalization

C) Aggregation

D) Inclusion

5) User views are ____ schema.

(A) physical

(B) Conceptual

(C) external

(D) Interface

6) Which two statements about views are true? (Choose two.)

A) A view can be created as read only.

B) A view can be created as a join on two or more tables.

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

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

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

F) A view cannot be created as Read only

7) Which statement describes the ROWID data type?

A. Binary data up to 4 gigabytes.

B. Character data up to 4 gigabytes.

C. Raw binary data of variable length up to 2 gigabytes.

D. Binary data stored in an external file, up to 4 gigabytes.

E. A hexadecimal string representing the unique address of a row in its table.

8) ) What is not an option of SAVE Command in Oracle?(1 mark)

a) Create

b) Replace

c) Append

d) Delete

OR

Which of the following ranking functions provide the true rank based on the values in the column?

A) Row_number()

B) Rank()

C) Dense_Rank ()

9) Which statement about SQL is true?

A. Null values are displayed last in the ascending sequences.

B. Data values are displayed in descending order by default.

C. You cannot specify a column alias in an ORDER BY clause.

D. You cannot sort query results by a column that is not included in the SELECT list.

E. The results are sorted by the first column in the SELECT list, if the ORDER BY Clause is not provided

10) Which two statements are true regarding the ORDER BY clause? (Choose two)

A. The sort is in ascending by order by default.

B. The sort is in descending order by default.

C. The ORDER BY clause must precede the WHERE clause.

D. The ORDER BY clause is executed on the client side.

E. The ORDER BY clause comes last in the SELECT statement.

F. The ORDER BY clause is executed first in the query execution.

7) Consider an Region table which consists of Region_id, Region_name.You need to display details of all Regions where Region_name is Asia. But you are not sure that in which case is the Asia Stored. Which query will you use such that it will list all the employees whose Region_name is Asia

A. Select Region_name FROM regions WHERE Region_name= 'Asia';

B. Select Region_name FROM regions WHERE UPPER (Region_name)= 'Asia';

C. Select Region_name FROM regions WHERE Region_name=UPPER ('Asia');

D. Select Region_name name FROM regions WHERE LOWER (Region_name)= 'Asia';

.

9) Cascada forgot her password. Which command must be executed to set a password for username Cascada?

A. Cascada must execute the command. ALTER USER Cascada PASSOWRD BY lion

B. The DBA must execute the command. ALTER USER Cascada IDENTIFIED BY

Cascada

C. Cascada must execute the command ALTER USER Cascada IDENTIFIED BY Cascada

D. The Cascada must execute the command CHANGE password to Cascada WHERE

"user=Cascada";

E. The DBA must execute the command CHANGE password to Sunrays WHERE

"user=Cascada";