Question 9. What Are PL/SQL Blocks? How Many Types of PL/SQL Block Definition Exist? Explain

Question 9. What are PL/SQL blocks? How many types of PL/SQL block definition exist? Explain with suitable
examples.
Answer:
Pl/SQL is a extension of SQl by adding constructs found in procedural languages,resultin in structural language much more powerful then SQL.pl/sql is structured in such a way that it can use conditional statements,loops and branches.there are three type of pl/sql blocks-anonymous procedure,named procedure and named funtion.

Question 12. State the differences between SQL and PL/SQL.
Answer:
SQL is structured query language and PL/SQL is Procedural Language/SQL.Their differences are
1)PROCEDURAL CAPABILITIES- SQL provides no procedural capabilities such as condition-testing, looping, branching etc. on the other hand PL/SQL is the procedural extension to ORACLE SQL. That is, apart from supporting SQL, it also supports high level language features like block structure, looping statements etc.
2)time factor- SQL is time consuming as it sends one statement at a time to the ORACLE engine whereas in PL/SQL it sends one entire block of statement(containing multiple statements) to the ORACLE Engine.
3)ERROR HANDLING ROUTINES-SQL has no error handling mechanism i.e it does not know what to do in case of an error,but in PL/SQL it supports customized error handling routines i.e an user can write his/her own error handling routines.

Question 17. Define the following terms in the context of Database
(i) Relation
(ii) Tuple
Answer:
(i)In database context Relation is also known as Table.
(ii) No. of rows in the table is known as Tuple.

Question 10. “NULL is not Equal to a NUL” do you agree with the statement. State reason.
Answer:
No, because NULL is a keyword and it is considered as a value which is not empty or equal to zero on the other hand NUL is a general string not a keyword.

Question 19. Give one major difference between Front End technology and Back End Technology in terms of
Software Projects. Give one example of each?
Answer:
ANSWER :- front end refer to server side who process the data for e.g microsoft visual basic and back end refer to the client side who made the data for processing for e.g microsoft access

Question 15. How do we specify comments in visual basic? How do we break a long line of code to a new line?
Answer:
In VB we can put comment using ' symbol or Rem keyword in front of the text.
To break a long line of code we put _ (underscore)symbol as a last character and then write rest of the code in next line.

Question 8. Write the output of the following PL/SQL code segment
DECLARE
x CHAR(3) := ’ABC’;
i NUMBER;
BEGIN
FOR i IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (x, -3, i));
END LOOP ;
END;
Answer:
A
AB
ABC

2. Answer the following questions :

(a) What is a syntax error in the context of a program ? Give an example. 2

(a) A syntax error is an error of language resulting from code that does notconform to the syntax of the programming language. Syntax errors can berecognized at compilation time.

Any one example of a syntax error in Visual Basic Or PL/SQL.

(b) Differentiate between Do While loop and Do Until loop of Visual Basicgiving a suitable example of each. 2

(b) Do While loop repeats a block of statements until a specified condition istrue.

Do Until repeats a block of statements until a specified condition is false.

Any example using one of the following syntax for do while loop :

Do While Condition Do

Statement Statement

......

Loop Loop While Condition

Any example using one of the following syntax for do until loop:

Do Until Condition Do

Statement Statement

......

Loop Loop Until Condition

(1 mark each for any correct example/syntax of each loop)

(½ mark each for any correct explanation of each loop withoutexample)

The Record Source property of ADODC specifies the source of therecords accessible through bound controls on the form. It can be set toa table Or an SQL statement, or stored procedure.

(2 mark for any correct definition/explanation/self explanatoryexample)

(d) Define the term Library Functions in Visual Basic. Name the differentcategories of library functions available in Visual Basic. Give the usage andsyntax of any two library functions. 4

(d) Visual Basic offers many functions that are pre-defined or built-in into theVB interpreter, which can be used directly in any program. These functionsare highly reliable and can tremendously reduce the amount of codingrequired for a program.

Different categories are:

?Math functions

?String functions

?Date Functions

?Conversion Functions

?Type Checking functions

3. (a) Define SQL. Name the different SQL subcategories (give full form). 2

(a) SQL (Structured Query Language) is a standard computer language for

accessing and manipulating databases.

The different categories are:

DDL - Data Definition Language

DML - Data Manipulation Language

DCL - Data Control Language

TCL - Transaction Control Language

(b) Explain the IN operator of SQL, specifying its syntax and usage. 2

(b) The IN operator implements comparison to a list of values, i.e. it testswhether a value matches any value in a list of values.

Syntax :

SELECT column_name PROM table_name WHERE column name IN

(valuel,value2,..);

Usage/Example :

SELECT name FROM travel WHERE city IN (‘Rome’, ‘Paris’) ;

(1 mark for any correct definition)

(c) Single-row functions return a single result row for every row of a queriedtable or view processed. Example: Number functions, Character functions,Datetime functions, Conversion functions etc.

Multiple row functions work on a set of rows. Each function has one inputargument and returns one result for each group of rows processed. Example:

SUM, AVG, COUNT, MAX or MIN.

(d) What is a Cursor in PL/SQL ? List any two commands that are associatedwith cursor control. 2

(d) Whenever a SQL statement is issued the Database server opens an areaof memory in which the command is parsed and executed. This area iscalled a cursor. Cursors can be explicitly defined and manipulated allowingthe processing of multiple rows.

DECLARE, OPEN, FETCH and CLOSE are the commands, which areassociated with cursor control.

(e) Write a PL/SQL procedure called NEXTMONTH that takes a date asparameter and adds 30 days to that date and displays it.

(e) CREATE OR REPLACE PROCEDURE NEXTMONTH (D1 DATE) AS /IS

D2 DATE;

BEGIN

D2 := D1 + 30;

DBMS_OUTPUT.PUT_LINE (TO_CHAR(D2)); TO_CHAR is optional

END;