Brainscape's Knowledge GenomeTM

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


See full index

Data Engineer/SQL Developer/SQL DBA Study Topics > PLSQL - Oracle Server > Flashcards

PLSQL - Oracle Server Flashcards

1
Q

How do you retrieve the current date and time from the local database server?

A

In order to extract part of the date, we use the TO_CHAR function on SYSDATE and specify the format we need.

SELECT SYSDATE FROM dual;

SELECT id, TO_CHAR(SYSDATE, ‘yyyy/mm/dd’) from InterviewBitEmployeeTable where customer_id < 200;

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

What PL/SQL syntax returns the user id of the current session?

A

SELECT USER FROM dual;

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

What are the various PL/SQL String Functions available for manipulating character data?

A

The functions that are used for manipulating the character data are called String Functions.

LEFT: This function returns the specified number of characters from the left part of a string.
Syntax: LEFT(string_value, numberOfCharacters).
For example, LEFT(‘InterviewBit’, 9) will return ‘Interview’.

RIGHT: This function returns the defined number of characters from the right part of a string.
Syntax: RIGHT(string_value, numberOfCharacters)
For example, RIGHT(‘InterviewBit’,3) would return ‘Bit’.

SUBSTRING: This function would select the data from a specified start position through the number of
characters defined from any part of the string.
Syntax: SUBSTRING(string_value, start_position, numberOfCharacters)
For example, SUBSTRING(‘InterviewBit’,2,4) would return ‘terv’.

LTRIM: This function would trim all the white spaces on the left part of the string.
Syntax: LTRIM(string_value)
For example, LTRIM(’ InterviewBit’) will return ‘InterviewBit’.

RTRIM: This function would trim all the white spaces on the right part of the string.
Syntax: RTRIM(string_value)
For example, RTRIM(‘InterviewBit ‘) will return ‘InterviewBit’.

UPPER: This function is used for converting all the characters to the upper case in a string.
Syntax: UPPER(string_variable)
For example, UPPER(‘interviewBit’) would return ‘INTERVIEWBIT’.

LOWER: This function is used for converting all the characters of a string to lowercase.
Syntax: LOWER(string_variable)
For example, LOWER(‘INterviewBit’) would return ‘interviewbit’

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

What is the difference between ROLLBACK and ROLLBACK TO statements in PL/SQL?

A

ROLLBACK command is used for rolling back all the changes from the beginning of the transaction.

ROLLBACK TO command is used for undoing the transaction only till a SAVEPOINT. The transactions cannot be rolled back before the SAVEPOINT and hence the transaction remains active even before the command is specified.

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

What is the use of SYS.ALL_DEPENDENCIES?

A

SYS.ALL_DEPENDENCIES is used for describing all the dependencies between procedures, packages, triggers, functions that are accessible to the current user. It returns the columns like name, dependency_type, type, referenced_owner etc.

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

What are the virtual tables available during the execution of the database trigger?

A

The THEN and NOW tables are the virtual tables that are available during the database trigger execution. The table columns are referred to as THEN.column and NOW.column respectively.

Only the NOW.column is available for insert-related triggers.
Only the THEN.column values are available for the DELETE-related triggers.
Both the virtual table columns are available for UPDATE triggers.

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

Differentiate between the cursors declared in procedures and the cursors declared in the package specifications.

A

The cursors that are declared in the procedures will have the local scope and hence they cannot be used by other procedures.

The cursors that are declared in package specifications are treated with global scope and hence they can be used and accessed by other procedures.

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

What are COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL?

A

These are the three transaction specifications that are available in PL/SQL.

COMMIT: Whenever any DML operations are performed, the data gets manipulated only in the database buffer and not the actual database. In order to save these DML transactions to the database, there is a need to COMMIT these transactions.

COMMIT transaction action does saving of all the outstanding changes since the last commit and the below steps take place:

The release of affected rows.

The transaction is marked as complete.

The details of the transaction would be stored in the data dictionary.

Syntax: COMMIT;

ROLLBACK: In order to undo or erase the changes that were done in the current transaction, the changes need to be rolled back. ROLLBACK statement erases all the changes since the last COMMIT.

Syntax: ROLLBACK;

SAVEPOINT: This statement gives the name and defines a point in the current transaction process where any changes occurring before that SAVEPOINT would be preserved whereas all the changes after that point would be released.

Syntax: SAVEPOINT

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

How can you debug your PL/SQL code?

A

We can use DBMS_OUTPUT and DBMS_DEBUG statements for debugging our code:

DBMS_OUTPUT prints the output to the standard console.
DBMS_DEBUG prints the output to the log file.

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

What is the difference between a mutating table and a constraining table?

A

A table that is being modified by the usage of the DML statement currently is known as a mutating table. It can also be a table that has triggers defined on it.

A table used for reading for the purpose of referential integrity constraint is called a constraining table.

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

Is it possible to declare column which has the number data type and its scale larger than the precision? For example defining columns like: column name NUMBER (10,100), column name NUMBER (10,-84)

A

Yes, these type of declarations are possible.

Number (9, 12) indicates that there are 12 digits after decimal point. But since the maximum precision is 9, the rest are 0 padded like 0.000999999999.

Number (9, -12) indicates there are 21 digits before the decimal point and out of that there are 9 possible digits and the rest are 0 padded like 999999999000000000000.0

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

Explain the uses of database trigger.

A

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :

1) Audit data modifications.
2) Log events transparently.
3) Enforce complex business rules.
4) Maintain replica tables
5) Derive column values
6) Implement Complex security authorizations

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

What are the two types of exceptions?

A

Error handling part of PL/SQL block is called Exception. They have two types :

user_defined and predefined.

Some predefined exceptions are:

DUP_VAL_ON_INDEX
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
etc.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How many triggers can be applied to a table?

A

A maximum of 12 triggers can be applied to one table.

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

What operators deal with NULL?

A

NVL converts NULL to another specified value.
var:=NVL(var2,’Hi’);

IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.

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

Explain 3 basic parts of a trigger.

A

A triggering statement or event.
A restriction
An action

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

What are the uses of MERGE?

A

MERGE is used to combine multiple DML statements into one.

Syntax : merge into tablename

using(query)

on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command

18
Q

What are the SQL* Plus required connection details?

A

Username:
Password:
Host String:

After successful connection we need to execute “set serveroutput on” if we need to see the output of the code. Now we are ready to work with the SQL* Plus tool.

19
Q

Write a simple PL/SQL program for printing “Hello World” using “Anonymous block”.

A

BEGIN
dbms_output.put_line (‘Hello World..’);
END;
/

Note: A block should be always followed by ‘/’ which sends the information to the compiler about the end of the block. Till the compiler encounters ‘/’, it will not consider the block is completed, and it will not execute it.

20
Q

Declaring a variable “text” of a VARCHAR2 type with size 25
Assign the value “Hello World” to the variable “text”.
Print the value of the variable “text”.

A 
DECLARE text VARCHAR2(25);
   BEGIN
      text:= ‘Hello World’;
      dbms_output.put_line (text);
    END:
/
21
Q

What are the four Groups of Data Types used in PL/SQL?

A

A data type specifies the storage format, constraints and valid range of values.

  1. Scalar Data Types. No internal components
  2. Composite Data Types. Has internal components which can be manipulated.
  3. Reference Data Types
  4. LOB Data Types. Used to store large size data in the database.
22
Q

There are at least 32 different Scalar Data Types used in PL/SQL. What are they?

A

BINARY_DOUBLE
BINARY_FLOAT
BINARY_INTEGER
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMBER – Used to store fixed and floating point numbers. SYNTAX= Number(Precision, Scale)
PLS_INTEGER
POSITIVE
POSITIVEN
REAL
SIGNTYPE
SMALLINT
CHAR
CHARACTER – Stores the fixed length of character data. SYNTAX= CHAR(Maximum_Length)
LONG’LONG RAW
NCHAR
NVARCHAR2
RAW
ROWID and UROWID
-ROWID column stores binary values called row_id’s
-Each row_id represents the storage address of a row
-A physical row_id identified a row in an ordinary table.
-A logical row_id identified a row in an index organized table.
-ROWID Data Type can only store physical row_id’s.
-UROWID(universal row_id) data type can store physical, logical or foreign (non-Oracle)
row_id’s.
STRING
VARCHAR
VARCHAR2 – Stores the variable length of character data. SYNTAX= VARCHAR2(Maximum_Length)
BOOLEAN – Stores the values TRUE, FALSE and the non-value NULL. Does not take any parameters.
DATE – stores fixed-length data values.
Valid date range includes January 1, 4712 BC to December 31, 4712 AD.

23
Q

There are at least 3 different Composite Data Types used in PL/SQL. What are they?

A

RECORD
TABLE
VARRAY

24
Q

There are at least 2 different Reference Data Types used in PL/SQL. What are they?

A

REF CURSOR

REF object_type

25
Q

There are at least 2 different Special Data Types used in PL/SQL. What are they?

A

Used to declare a variable with the reference to the table

%type – used to specify the particular column from a table.

        - SYNTAX= Name details.name%type;
        - SYNTAX= City details.city%type;

%rowtype – used to specify a complete row from a table.

  • Is declared within the declaration section of a PL/SQL.
  • SYNTAX= Record details%rowtype;
26
Q

There are at least 4 different LOB Data Types used in PL/SQL. What are they?

A

LOB is known as large object. LOB type variables are used to store data in large size.

Used to store blocks of Unstructured Data and these data types are used to store text, graphic images, video and sounds.

We can store maximum of 4gb of data in this variables data types.

BFILE – stores large binary objects as a file in the file system.
-Uses a pointer or file locator to store a file outside the database.
-Locator includes a directory alias which specifies a full path name.
-Read-only, cannot modify
-cannot exceed 4gb.
-cannot participate in transactions, are not recoverable, cannot be replicated.
BLOB – can convert to RAW data type.
-Used to store large binary objects in the database
-Every BLOB variable stores a locator in the database which points to a large binary object.
-cannot exceed 4gb.
-can participate in transactions, are recoverable, can be replicated.
CLOB – can convert to CHAR and VARCHAR types.
-Used to store large blocks of character data in the database
-Supports both fixed-width and variable-width character sets.
-Every CLOB variable stores a locator in the db which points to a large block of character data.
-cannot exceed 4gb.
-can participate in transactions, are recoverable, can be replicated.
NCLOB –Used to store large blocks of NCHAR data in the database
-Supports both fixed-width and variable-width character sets.
-Every NCLOB variable stores a locator which points to a large block of NCHAR data.
-cannot exceed 4gb.
-can participate fully in transactions, are recoverable, can be replicated.

27
Q

How is a data type converted from one data type to another data type in PL/SQL?

A
  1. Implicit Conversion. Is an Automatic data type conversion.
  2. Explicit Conversion. Built-in function is used to make the conversion.
    TO_DATE or TO_NUMBER is used to convert the char data type to date data type.
    TO_CHAR function is used to convert the date data type to the character data type.
28
Q

Declaration section of a PL/SQL program or query begins with what keyword?

A

DECLARE

Eg.
	Set serveroutput on
	DECLARE
	num integer;
	val integer;
	total number(5);
29
Q

Executable section of a PL/SQL program or query begins with what keyword?

A

BEGIN

Eg.
	BEGIN
	   Num := 5;
	   Val := 10;
 	   Total := 5 +10;
	   Dbms_output.put_line(total);
	END;
	/
-statement Returns 15.
30
Q

Create a check constraint on a data column for email addresses. This can use regular expressions or other string manipulation to validate the address.

A 
create table t (
  email varchar2(320) check (
    regexp_like(email, '[[:alnum:]]+@[[:alnum:]]+\.[[:alnum:]]')
  )
);

insert into t values (‘abc@xyz.com’);

1 row inserted.

insert into t values (‘abc@xyz.co.uk’);

1 row inserted.

insert into t values (‘ab.com’);

SQL Error: ORA-02290: check constraint (CHRIS.SYS_C0014586) violated

insert into t values (‘abc@xyz’);

SQL Error: ORA-02290: check constraint (CHRIS.SYS_C0014586) violated

31
Q

What is Anchored Datatype in PL/SQL?

A

Anchored data types are those data type which you assign to a variable based on a database object. They are called anchored data type because unlike the variable data type it is not dependent on that of any underlying object.

SYNTAX= variable_name typed-attribute%type

Example 1: How to Declare a variable with Anchored Datatype

Write an anonymous block where I will declare a variable with anchored data type and then initialize that variable by fetching value from this table.

SET SERVEROUTPUT ON;
DECLARE
v_fname students.first_name%TYPE;

Here In the declaration section I have declared a variable by the name of v_fname with identical data type as the column First Name of table Students. This means that the data type of variable v_fname will be the varchar2 with data width 8. This is the data type and data width of the column first name of our table students.

Adding the execution section to this anonymous PL/SQL block and initializing this variable v_fname by fetching data from the table students.

BEGIN
SELECT first_name INTO v_fname FROM students WHERE stu_id =1; DBMS_OUTPUT.PUT_LINE (v_fname); END;

In this execution block I have a Select… Into statement which I am fetching first name of the student whose stu_id is 1 and storing it into our variable v_fname.

That’s how we declare a variable with anchored data type.

32
Q

Create a FOR LOOP in PL/SQL.

A

The lower limit and the higher limit will be specified and as long as the loop variable is in between this range, the loop will be executed. The loop variable is self-incremental, so no explicit increment operation is needed in this loop. The loop variable need not to be declared, as it is declared implicitly.

FOR in ..
LOOP

.
.
.

END LOOP;

  • In the above syntax, keyword ‘FOR’ marks beginning of the loop and ‘END LOOP’ marks the end of the loop.
  • Loop variable is evaluated every time before executing the execution part.
  • The execution block contains all the code that needs to be executed. The execution part can contain any execution statement.
  • The loop_variable is declared implicitly during the execution of the entire loop, and the scope of this loop_variable will be only inside this loop.
  • If the loop variable came out of the range, then control will exit from the loop.
  • The loop can be made to work in the reverse order by adding the keyword ‘REVERSE’ before lower_limit.
BEGIN
dbms Qutput.put linef.Prp.gram started.' );
FOR a IN 1 .. 5
LOOP
dbms_output.put_line(a);
END LOOP:
dbms_output.put_iine('Program completed.'); 
END;
/
OUTPUT:
	Program started.
	1
	2
	3
	4
	5
	Program completed.
33
Q

Create a NESTED LOOP in PL/SQL.

A

The loop statements can also be nested. The outer and inner loop can be of different types. In the nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.

LOOP -outer

LOOP — inner

END LOOP;

END LOOP;

In this example, we are going to print number from 1 to 3 using FOR loop statement. Each number will be printed as many times as its value. For that, we will execute the following code.

DECLARE 
b NUMBER;
BEGIN
dbms output put line(‘Program started' );
FOR a IN 1..3 
LOOP
b:=1;
WHILE (a>=b)
LOOP
dbms output put line(a);
b:=b+1;
END LOOP;‭	
END LOOP;‭	‬
dbms_output.put_line('Program completed' );
END;
/
OUTPUT:
	Program started.
	1
	2
	2
	3
	3
	3
	Program completed
34
Q

Create a WHILE LOOP in PL/SQL.

A

While Loop in PL/SQL works similar to the basic loop statement, except the EXIT condition is at the very beginning of the loop. It works like an entry-checking loop where the execution block will only execute if the condition is satisfied, as the exit condition is checked before execution. It does not explicitly require the ‘EXIT’ keyword to exit from the loop since it validates the condition implicitly each time.

WHILE
LOOP

.
.
.

END LOOP;

In the above syntax, keyword ‘WHILE’ marks beginning of the loop and ‘END LOOP’ marks the end of the loop.

EXIT condition is evaluated each time before the execution part is starting executing.

The execution block contains all the code that needs to be executed.

The execution part can contain any execution statement.

DECLARE
a NUMBER :=1;
BEGIN
dbms_output.put_line('Program started');
WHILE (a <= 5) 
LOOP
dbms_output.put_line(a);
a:=a+1;
END LOOP;
dbms_output.put_line(‘Program completed' ); 	
END:
/
OUTPUT:
	Program started.
	1
	2
	3
	4
	5
	Program completed
35
Q

What are the DML transactions in PL/SQL?

A

Select
Insert
Update
Delete

36
Q

What is the syntax for an INSERT statement in PL/SQL?

A

There are two types of INSERT statements:

BEGIN
INSERT INTO (,,…)
VALUES(,…:);
END;

The above syntax shows the INSERT INTO command. The table name and values are a mandatory fields, whereas column names are not mandatory if the insert statements have values for all the column of the table.
The keyword ‘VALUES’ is mandatory if the values are given separately as shown above.

BEGIN

  INSERT INTO (,,...,)
     SELECT ,,..  FROM ;
END;

The above syntax shows the INSERT INTO command that takes the values directly from the using the SELECT command.
The keyword ‘VALUES’ should not be present in this case as the values are not given separately.

37
Q

What is the syntax for a DELETE statement in PL/SQL?

A

Data deletion means to delete one full record from the database table. The ‘DELETE’ command is used for this purpose.

	BEGIN
	  DELETE
	  FROM  
 	  WHERE ; 
	END;

The above syntax shows the DELETE command. The keyword ‘FROM’ is optional and with or without ‘FROM’ clause the command behaves in the same way.

‘WHERE’ clause is optional. If this clause is not given, then the entire table will be deleted.

38
Q

What is the syntax for an UPDATE statement in PL/SQL?

A

Data update simply means an update of the value of any column in the table. This can be done using ‘UPDATE’ statement. This statement takes the table name, column name and value as the input and updates the data.

	BEGIN	
  	UPDATE 
  	SET =,=,= 
 	 WHERE ; 
	END;

The above syntax shows the UPDATE. The keyword ‘SET’ instruct that PL/SQL engine to update the value of the column with the value given.

‘WHERE’ clause is optional. If this clause is not given, then the value of the mentioned column in the entire table will be updated.

39
Q

What is the syntax for a SELECT statement in PL/SQL?

A

Data projection/fetching means to retrieve the required data from the database table. This can be achieved by using the command ‘SELECT’ with ‘INTO’ clause. The ‘SELECT’ command will fetch the values from the database, and ‘INTO’ clause will assign these values to the local variable of the PL/SQL block.

‘SELECT’ statement should return only one record while using ‘INTO’ clause as one variable can hold only one value. If the ‘SELECT’ statement returns more than one value than ‘TOO_MANY_ROWS’ exception will be raised.

‘SELECT’ statement will assign the value to the variable in the ‘INTO’ clause, so it needs to get at least one record from the table to populate the value. If it didn’t get any record, then the exception ‘NO_DATA_FOUND’ is raised.

The number of columns and their datatype in ‘SELECT’ clause should match with the number of variables and their datatypes in the ‘INTO’ clause.

The values are fetched and populated in the same order as mentioned in the statement.

‘WHERE’ clause is optional that allows to having more restriction on the records that are going to be fetched.

‘SELECT’ statement can be used in the ‘WHERE’ condition of other DML statements to define the values of the conditions.

The ‘SELECT’ statement when using ‘INSERT’, ‘UPDATE’, ‘DELETE’ statements should not have ‘INTO’ clause as it will not populate any variable in these cases.

BEGIN
  		SELECT ,.. INTO ,. . 
   		FROM 
   		WHERE ;
END;

The above syntax shows the SELECT-INTO command. The keyword ‘FROM’ is mandatory that identifies the table name from which the data needs to be fetched.

‘WHERE’ clause is optional. If this clause is not given, then the data from the entire table will be fetched

40
Q

How is a COMMON TABLE EXPRESSION (CTE) written in PL/SQL?

A

To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery. The following example defines CTEs named cte1 and cte2 in the WITH clause, and refers to them in the top-level SELECT that follows the WITH clause:

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Data Engineer/SQL Developer/SQL DBA Study Topics (11 decks)

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