Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Oracle class > besanttechnologies > Flashcards

besanttechnologies Flashcards

1
Q

What are Schema Objects?

A

Schema objects are tables, views, sequences, synonyms, indexes,databases triggers, procedures, functions, packages.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a Table?

A

The table is collection inforamation.A table is the basic of rows and columns.data’s are stored rows and columns.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is a View?

A

A View is a virtual table.Not a physical table.
representation of one or more tables
Every view has a query attached to it.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is an Index?

A

What is an Index?

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is a Data file?

A

A database’s data files contain all the database data. The data of logical database structures is tables and indexes is physically stored in the data files allocated for a database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is the difference between UNIQUE or PRIMARY KEY Constraint?

A

A column defined as PRIMARY KEY can contain unique values

same as UNIQUE but UNIQUE can contain NULLs while a column defined as PRIMARY KEY cannot contain NULLs.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is PL/SQL ?

A

PL/SQL is a programming language. SQL and procedural programming language constructs such as named blocks,iterations, conditional branching..

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the basic structure of PL/SQL ?

A

PL/SQL uses block structure as its basic two structure. Anonymous blocks or nested blocks can be used in PL/SQL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the difference between %ROWTYPE and TYPE RECORD ?

A

%ROWTYPE is its accept multiple data type.to be used whenever query returns an entire row of a table or view.
TYPE RECORD is accept single data type.to be used whenever query returns columns of different table or views and variables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What is a cursor ? Why cursor is required ?

A

Cursor is a named private sql area from where information can be passed and executed or accessed. Cursors are required to process rows individually for queries returning multiple rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Explain the two types of cursors ?

A

Implicit cursors
Explicit cursors.
For loop cursors.
PL/SQL uses Implicit cursors for queries.
User defined cursors are called explicit cursors.
User using Cursors in for loop the Cursor called for loop Cursor.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is Raise_application_error ?

A

Raise_application_error is a procedure of package DBMS_STANDARD. that allows to user_defined error or error code and error messages from stored sub program or database trigger.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the difference between a PROCEDURE and FUNCTION ?

A

A PROCEDURE may return one or more values or may not return at all.
A FUNCTION must returns a value using the return statement.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is Overloading of procedures ?

A

The same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a package ? What are the advantages of package ?

A

database object that groups logically related procedures.
The advantages of packages are modularity, easier application design, information hiding, reusability and better performance

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is difference between TRUNCATE and DELETE?

A

Truncate is Dml Statements. Cannot be rolled back. Delete allows the filtered deletion.
Deleted records can be rolled back or committed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is a join? Explain the different types of joins?

A

Join is a query,retrieves data from related columns or rows from multiple tables.
Self join – Joining the table with itself
Equi Join – Joining two tables by equating two common columns
Non Equi Join – Joining two tables based on conditions other than equating two common columns
Outer Join – Joining two tables in such a way that query can also retrieve rows that do not have the corresponding join value in the other table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is a Subquery?

A

Subquery is a query embedded with another Query

Outer Query based on the Inner Query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is correlated sub-query?

A

Correlated sub-query is a sub-query, which has reference to the main query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Difference between SUBSTR and INSTR?

A

INSTR(string1, string2 [,n,[m]])
its find the position of the string
SUBSTR(string1, n, m)
SUBSTR returns a character string of size m in string 1, starting from nth position of string1.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Explain UNION, MINUS, UNION ALL, INTERSECT?

A

INTERSECT returns all commmon rows selected by both queries.
MINUS returns all distinct rows selected by the first query but not by the second.
UNION returns all unique rows selected by either query.
UNION ALL returns all rows selected by either query, included all duplicates.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are the Usages of SAVEPOINTS?

A

SAVEPOINTS are Transaction control Language.
its used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction.
Maximums of five save points are allowed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is ROWID?

A

ROWID is a pseudo column attached to each row of a table.

It is 18 characters long, blockno. Rownumber.filenumber are the components of ROWID.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?

A

PRIMARY KEY is a column or a combination of columns of a table which cab be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY is a column or a combination of column which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is ON DELETE CASCADE?

A

ORACLE maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is difference between CHAR and VARCHAR2? What is maximum SIZE allowed for each type?

A

CHAR pads spaces to the maximum length. VARCHAR2 does not pad blank spaces.
For CHAR it is 255 and 2000 for VARCHAR2.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

How to store the results of a query into a file?

A

The results of a query can be stored into a file by spooling into a file.
Eg. SPOOL filename.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Which two statements about subqueries are true? (Choose two.)

A

A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is referred to in its subquery,
unless table B is included in the main query’s FROM clause.
C. A SQL query statement can display data from table B that is referred to in its subquery,without including table B in its own FROM clause.
D. A single row subquery can retrieve data from more than one table.
E. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Which three functions can be used to manipulate character, number, or date column values? (Choose three.)

A 
A. CONCAT
B. ROUND
C. TRUNC
D. RPAD
E. INSTR
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Which statement about a table is true?

A

A. A table can have up to 10,000 columns.
B. The size of a table does NOT need to be specified.
C. A table CANNOT be created while users are using the
database.
D. The structure of a table CANNOT be modified while the table is online.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What will be returned from SIGN(ABS(NVL(-32,0)))?

A 
A. 1
B. 32
C. 1
D. 0
E. NULL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Which functions could you use to strip leading characters from a character string. Select two.

A 
A. LTRIM
B. SUBSTR
C. RTRIM
D. INSTR
E. MOD
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

All the operators are used in single row subquery except one

A

a) Between … and
b) < >
c) =
d) in

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

All the commands executes in iSQLplus except one

A

a) Column
b) Compute
c) define
d) Accept

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Ascript file which will be executed automatically in iSQLPlus is

A

a) afiedt.buf
b) login.sql
c) both a and b
d) none of the above.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

A command in iSQL plus is used to give the status of old and new value of variable

A

a) set feedback
b) set verify
c) set confirm
d) none of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

All commands are used to save the changes of the transaction except one

A

a) Commit
b) exitting from sqlplus
c) DDL command
d) savepoint
e) none of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

A Clause which is used in joining two tables other than equality operator is

A

a) join
b) on
c) in
d) using

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

A Clause which is the pseudocolumn used to know the current value of the sequence

A

a) nextval
b) current_val
c) currval
d) none of the above

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

A Query which is used in top-N analysis is

A

a) subquery
b) correlated subquery
c) inline query
d) outer query

41
Q

An operator is used to get and display the redundant records

A

a) Union all
b) Distinct
c) Union
d) Intersect

42
Q

All the datatypes with respect to Oracle 9i is true except one

A

a) DATE
b) TIMESTAMP
c) TIMSTAMP with TIME ZONE
d) TIMESTAMP WITH LOCAL TIME ZONE
e) None of the above

43
Q

What is database?

A

A database is a logically collection of data with some inherent meaning.the data’s stored rows and collumns
database are used to store the informations.

44
Q

To select records from the given row?

A

select * from SIVAEMP where rownum < =(select count(*)-&n from SIVAEMP);

45
Q

To check the leap year

A

select decode(mod(2004,4),0,’leap year’, ‘not a leap year’) from dual

46
Q

To delete duplicate records

A

delete from sivaemp where rowid not in(select max(rowid) from sivaemp group by empno)

47
Q

To keep latest single record

A

delete from sivaemp where rowid not in(select min(rowid) from sivaemp group by empno)//to keep oldest record

48
Q

To select second max salary

A

select * from sivaemp where salary = (select max(salary) from sivaemp where salary not in (select max(salary) from sivaemp))

49
Q

To select nth max salary

A

select * from sivaemp a where &n-1 = (select count() from sivaemp b where a.salary < b .salary)
select * from sivaemp a where &n = (select count() from sivaemp b where a.salary < =b.salary)

50
Q

To select nth min salary

A

select * from sivaemp a where &n-1 = (select count() from sivaemp b where a.salary > b.salary)
select * from sivaemp a where &n = (select count() from sivaemp b where a.salary > =b.salary)

51
Q

To select top(n) max salaries

A

select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary < b .salary)

52
Q

To select top(n) min salaries

A

select * from sivaemp a where &n > (select count(*) from sivaemp b where a.salary > b.salary)

53
Q

To select records from particular row(the remaining rows)

A

select * from sivaemp minus select * from sivaemp where rownum < = (select count(*)- &n-1 from sivaemp )

54
Q

to select first n rows

A

select * from sivaemp where rownum < = (select count() + &n -count() from sivaemp)

55
Q

Explain the difference between trigger and stored procedure.

A

Trigger in act which is performed automatically before or after a event occur when DML operations are occur Trigger is Fire.
Stored procedure is a set of functionality which is executed when it is explicitly invoked.

56
Q

Differences between DATE and TIMESTAMP in Oracle

A

Date is used to store date and time values including month, day, year, century, hours, minutes and seconds. TimeStamp datatype stores everything that Date stores and additionally stores fractional seconds.
Date: 16:05:14
Timestamp: 16:05:14:

57
Q

Create a copy of EMP table without any data?

A

CREATE TABLE EMP1 AS SELECT * FROM EMP WHERE‘CHIRU’=’VENKY’;

58
Q

Delete the 10th record of EMP table?

A

DELETE FROM EMP WHERE ENAME= (SELECT ENAME FROM EMP WHERE ROWNUM < =10 MINUS SELECT ENAME FROM EMP WHERE ROWNUM<10);

59
Q

Find all the departments which have more than 3 employees?

A

SELECT D.DNAME, COUNT (E.ENAME) FROM EMP E, DEPT D WHERE D.DEPTNO=E.DEPTNO GROUP BY DNAME HAVING COUNT (EMPNO) > 3;

60
Q

Display the manager who is having maximum number of employees working under him?

A

SELECT DISTINCT M.ENAME, COUNT (E.ENAME)
FROM EMP E, EMP M WHERE E.MGR=M.EMPNO
GROUP BY M.ENAME HAVING COUNT (E.ENAME) > =ALL
(SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO GROUP BY M.ENAME);

61
Q

Display the name of employees who joined on the same date?

A

SELECT A.ENAME FROM EMP A, EMP B

WHERE A.EMPNO < > B.EMPNO AND A.HIREDATE=B.HIREDATE;

62
Q

Delete those employees who joined the company 10 years back from today?

A

SELECT ENAME FROM EMP

WHERE (SYSDATE-HIREDATE)/365 > 10

63
Q

Display those employees whose salary is ODD value?

A 
SELECT ENAME, SAL FROM EMP
WHERE MOD (SAL, 2) < > 0;
64
Q

Find out the number of employees whose salary is greater than their manager salary?

A 
SELECT COUNT (E.ENAME) FROM EMP E, EMP M
WHERE E.MGR=M.EMPNO AND E.SAL > M.SAL
65
Q

Select the count of employees in each dept where count is greater than 3?

A

SELECT JOB, COUNT (*) FROM EMP

GROUP BY JOB HAVING COUNT (*) > 3;

66
Q

Display name of those employees who are getting the highest salary?

A

SELECT * FROM EMP WHERE SAL IN (SELECT MAX (SAL) FROM EMP);

67
Q

What is a transaction ?

A

A transaction is a set of SQL statements between any two COMMIT and ROLLBACK statements.

68
Q

What is implicit cursor and how is it used by Oracle ?

A

An implicit cursor is user pass single line queries that time a cursor which is internally created by Oracle.It is created by Oracle for each individual SQL.

69
Q

Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?

A

Public synonyms

70
Q

Is there a PL/SQL Engine in SQL*Plus?

A

SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

71
Q

Can one read/write files from PL/SQL?

A

Included in Oracle 7.3 is a UTL_FILE package that can read and write files.The directory you intend writing to has to be in your INIT.
Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN(‘/home/oracle/tmp’, ‘myoutput’,’W’);
UTL_FILE.PUTF(fileHandler, ‘Value of func1 is %sn’, func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;

72
Q

How can I protect my PL/SQL source code?

A

PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQLPlus and SQLDBA will still understand and know how to execute such scripts.Just be careful, there is no “decode” command available. The syntax is: wrap name=myscript.sql oname=xxxx.yyy

73
Q

Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?

A

From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements.
Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,’CREATE TABLE X (Y DATE)’,
DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

74
Q

What are the various types of parameter modes in a procedure ?

A

IN, OUT AND INOUT.

75
Q

What are the constructs of a procedure, function or a package ?

A

The constructs of a procedure, function or a package are :variables and constants
cursors
exceptions

76
Q

Why Create or Replace and not Drop and recreate procedures ?

A

So that Grants are not dropped.

77
Q

Can you pass parameters in packages ? How ?

A

Yes.You can pass parameters to procedures or functions in a package.

78
Q

What are the parts of a database trigger ?

A

A triggering event or statement
A trigger restriction
A trigger action

79
Q

What are the various types of database triggers ?

A

There are 12 types of triggers
Insert, Delete and Update Triggers.
Before and After Triggers.
Row and Statement Triggers.

80
Q

What is the advantage of a stored procedure over a database trigger ?

A

We have control over the firing of a stored procedure but we have no control over the firing of a trigger.

81
Q

What are cascading triggers? What is the maximum no of cascading triggers at a time?

A

When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.Max = 32.

82
Q

What are mutating triggers ?

A

A trigger giving a SELECT on the table on which the trigger is written.

83
Q

What are constraining triggers ?

A

A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.

84
Q

Describe Oracle database’s physical and logical structure ?

A

Physical : Data files, Redo Log files, Control file.

Logical : Tables, Views, Tablespaces, etc.

85
Q

Can you increase the size of a tablespace ? How ?

A

Yes, by adding datafiles to it.

86
Q

What is the use of Control files ?

A

Contains pointers to locations of various data files, redo log files, etc.

87
Q

What is the use of Data Dictionary ?

A

It Used by Oracle to store information about various physical and logical structures e.g.Tables

88
Q

What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?

A 
The various states of a rollback segment are :
ONLINE
OFFLINE
PARTLY AVAILABLE
NEEDS RECOVERY
INVALID.
89
Q

What is the maximum no.of columns a table can have ?

A

254.

90
Q

Can you pass a parameter to a cursor ?

A

Explicit cursors can take parameters,User create cursor called Explicit Cursor
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

91
Q

What are the various types of RollBack Segments ?

A

The types of Rollback segments are as follows :
Public Available to all instances
Private Available to specific instance

92
Q

What are the disadvantages of SQL ?

A 
Cannot drop a field
Cannot rename a field
Cannot manage memory
Procedural Language option not provided
Index on view or index on index not provided
View updation problem
93
Q

When to create indexes ?

A

To be created when table is queried for less than 2% or 4% to 25% of the table rows.

94
Q

How can you avoid indexes ?

A

To make index access path unavailable Use FULL hint to optimizer for full table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another

95
Q

What is the difference between a view and a synonym ?

A

Synonym is Schema Object,just a second name of table,.View can be created with many tables, and with virtual columns and with conditions.

96
Q

What is the difference between foreign key and reference key ?

A

Foreign key is attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.

97
Q

What is a package cursor ?

A

a cursor declare in the package specification without an SQL statement.
The SQL statement for the cursor is attached at runtime from calling procedures.

98
Q

What are snap shots and views

A

Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables.

Oracle class (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions