imp quesitons for DBMS

RDBMS

SECTION-A

1. Explain disadvantages in file based processing.(markch/ap09,oc/nov09)
2. Differentiate between hierarchical model and network model.(mark/ap2009)
3. Define instance and scheme( oct/nov09 08)
4. List the responsibilities of database manager.
5. What are symbols used in E-R diagrams?(March/April.2009,oct/nov.07)
6. Explain data independence. (march/april.09,oct/nov.07/nov2010)
7. What are the difference between generalization and specialization
8. Explain about concept of aggregation. (oct/nov.09)
9. What do you means by SQL? What are the benefits of SQL?.
10. What is an embedded SQL?
11. What do you meant by pseudo column? Explain with examples. (oct/nov.09,08)
12. What are the differences between Char and varchar2 data types?(M/A.09)
13. Write any four data functions with syntax and examples
14. State the four features of SQL. (March/April 2009)
15. What is the process of creating a Table
16. Define a sequence
17. Difference between view and table.
18. Write the names of four schema objects.
19. Define is PL/SQL global and system variables.
20. List the features of PL/SQL.
21. What are Naming Conventions in PL/SQL?
22. List any four predefined PL/SQL exceptions.
23. Compare various parameter modes.
24. What is a cursor? Explain the management of explicit cursor.
25. What is implicit cursor?
26. What is a stored sub program
27. List the advantages of packages
28. How to compile a PL/SQL program?
29. What is packaged procedure? Give example.
30. Define package.

Section-b

31. What is database model?(oct/nov08)
32. What do you meant by generalization, specialization and aggregation. (Oct/Nov.09,08,07)
33. Explain the various mapping cardinalities. (Oct/Nov.07)
34. Explain the various normal forms in details. (Oct/Nov.09)
35. What is database abstraction? Explain with the help of a diagram.(m/ap09,oc/no-09)
36. Explain the functions of Database Administrator
37. Explain various data types available in SQL. (Oct/Nov.2009, March/April.2009)
38. Explain about any 12 function of SQL. (MARCH/APRIL 2009)
39. Explain the following SQL statements (a) insert (b)update   (March/April.2009)
40. (a)Explain the various data format models in SQL.                        (b)Explain set operators of SQL
41. Explain group function of SQL. (Oct/Nov.2008, 2007)
42. (a.)Explain about DML statements (Oct/Nov.2009)                                                             (b) explain about data types supported by SQL. (Oct/Nov.2009)
43.  Explain the process of creating (a) sequence (b)cluster (c)synonym (d) index
44. Explain the procedure for creating, altering and dropping a sequence
45. (a.) Explain the space management of database table.  (b) Explain about indexes.
46. What is an integrity constraint? List the various types of integrity constraints.
47. What is a view? Explain the syntax for creating a view.
48. Explain the procedure of creating, altering and dropping tables.
49. Explain the following commands: (i.)commit (ii)rollback and save point.
50. Explain the various data types of PL/SQL in detail.
51. Explain propagation and re-raising of exceptions.
52. What is an exception? Explain the rising of an exception.
53. Explain about implicit and explicit cursor.
54. (a)Explain recursion with an example.
55. Explain package state and dependency.
56. Explain procedure overloading in PL/SQL with an example.

Advanced PL/SQL

1. What is stored Sub Program?

Ans: it is a sub program which may be procedure or function in a database object that are logically group of SQL and PL/SQL programming language statements together to perform a specific task.

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL:

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

Implicit Cursors:

When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.

Attributes	Return Value	Example
%FOUND	The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.	SQL%FOUND
	The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.
%NOTFOUND	The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.	SQL%NOTFOUND
	The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.
%ROWCOUNT	Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT	SQL%ROWCOUNT

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:

DECLARE  var_rows number(5);

BEGIN

  UPDATE employee 

  SET salary = salary + 1000;

  IF SQL%NOTFOUND THEN

    dbms_output.put_line('None of the salaries where updated');

  ELSIF SQL%FOUND THEN

    var_rows := SQL%ROWCOUNT;

    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

  END IF; 

END; 

In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.