Interview Questions
« Previous | 0 | 1 | 2 | 3 | 4 | Next »

1.What is view? What is use of view?

Views are a logical way of looking at the physical data located in the tables. A view does not represent any physical data. Dropping a view has no effect on underlying tables. But insert and update on view affects the base tables.
Use: –

  • Restrict data access to users: – You can give access to selected columns (vertical restriction) or selected rows (horizontal restriction).
  • Ease of SQL query: – You can create a view on complex but frequently used queries so that users can access the result using simple select * from view.

2.I have a Table and I create a View. I drop the Table and recreate the Table with the same name. Will the View be working?

Yes that will work fine because it take the ID of the table from sysobjects table at the time of execution.

3.Can I update a View if I am using 2 tables?

  • Yes you can. You can update columns of only one table using a single update statement. i.e. columns to be updated are from different tables, you have to use multiple update statements – one for each table.
  • View should not contain distinct clause or aggregate functions.

4.Can I delete from a View?

  • Yes you can. A single delete statement deletes rows from multiple base tables.
  • View should not contain distinct clause or aggregate functions.

5.Can you insert in a View if it is created on multiple base tables?

  • Yes you can. Columns being inserted into the view should belong to a single base table.
  • All the columns in the base tables, which are not included in the view, should be defined to allow null or should have a default values defined.
  • View should not contain distinct clause or aggregate functions.

6.While inserting records through a View, what precautions will you take?

Explained above

Where does the text of a View creation stored?
  • In syscomments system table
    How will you see the definition of a View?
  • select text from syscomments
    where id = object_id(‘NameOfTheView’)
  • sp_helptext ‘NameOfTheView’

7.6 Can I use group by in views? Can I use compute by clause in a View?

You can have group by clause but you can not have compute/compute by clause in view.

8.What if with check option in view?

The with check option flag prevents insertion or updating of rows that will not meet the view criteria.
e.g.:
create view cal_publishers_chk as
select *
from publishers
where state = “CA”
with check option

update cal_publishers_chk
set state = “OH”

Update fails because, after the update, the modified rows would fail to appear in the view.

9.Tell about 5 system tables?

  • sysobjects :-all the objects in the database
  • syscolumns :-all columns of tables
  • sysindexes :-Index details
  • syscomments :-comments and texts of stored procedure, view, trigger, rule, default and other database objects.
  • sysdepends :-details of a objects which depends on which table or stored procedures
  • sysprotects :- permission list to a particular object to users or alias
  • sysusers :-user and alias details in the database.
  • sysprocedures :-Details of procedure, defaults, rules, trigger and views

10.What is stored in sysprocedures?

Details of procedure, defaults, rules, trigger and views

11.What is normalization?

Normalization in a relational database is an approach to structuring information in order to avoid redundancy and inconsistency. This is done to promote efficient maintenance, storage, and updating.
There are three main rules or levels of normal forms. First, Second and Third.
First normal forms, the least structured, are groups of records in which each field (column) contains unique and non-repeating information. Second and third normal forms break down first normal forms, separating them into different tables by defining successively finer interrelationships between fields.
For relational databases such as Adaptive Server, the standard design creates tables in Third Normal Form.
Sybase recommends that you design databases for Third Normal Form, however, if performance issues arise, you may have to denormalize to solve them.

12.9 What is the need for de-normalization?

  • Nearly all of the most frequent queries require access to the full set of joined data.
  • A majority of applications perform table scans when joining tables.
  • Computational complexity of derived columns requires temporary tables or excessively complex queries.

13.What are triggers? Why do we use them? Diff types of triggers? What are magic tables?

  • A triggeris a stored procedure bound to a table, which is implicitly executed when a user attempts to modify data with an insert, delete, or update command.
  • Use: –
  • i. Validation purpose
    ii. Referential integrity
  • Type of trigger: – update/delete/insert
  • Two tables are created at the time of trigger execution 1. Inserted 2. Deleted which are called magic tables.

14.What is the concept of magic-tables?

When a trigger is fired, two tables are created ‘inserted’ and ‘deleted’, which are referred as magic tables. These tables are special views of the transaction log that last for the duration of a trigger. They reflect the changes made in the table by the statement that caused the trigger to execute. The structure of these tables is same as that of the table on which the trigger is executed.

StatementInsertedDeleted
InsertNew row being insertedBlank
UpdateNew values of the row being updatedOld values of the row being updated
DeleteBlankRow being deleted

15.What is the depth of Triggers?

16 Levels i.e. 16 nested triggers can be there in Sybase

16. How do you check that the depth of the Triggerdoesn’t cross the limit?

Just check the @@nestlevel global variable for the same

17.Can u call triggers explicitly? Can u disable triggers?

No you cannot, no you cannot in Sybase

18.What are stored procedures?

Stored procedure is a database object which is collection of SQL statements or constructs. Parameters can be passed and returned, and error codes may be checked. Stored procedures, after their first execution, become memory resident and do not need to be reparsed, reoptimized, or recompiled. So they are faster.

19.What are the advantages and disadvantages of S.P? What are diff parameters we can use in SP? Can I use my output parameter as an input parameter in Sybase?

  • Fast execution: –Stored procedures, after their first execution, become memory resident and do not need to be reparsed, reoptimized, or recompiled. So they are faster.
  • Reduced network traffic: – As they are memory resident, less SQL need to cross busy network lines.
  • Modular Programming: – A way to brake up the logic in small modules.
  • Restricted, function based access to tables: – You can grant permissions in such a way as to allow a user access to tables only through the stored procedures.
  • Enforced consistency: – If users are accessing tables only through stored procedures, ad-hoc modifications can be easily avoided.
  • Disadvantages: –
  • A stored procedure can not create views, defaults, rules or triggers.
  • You can create and use temporary tables. These tables are dropped at procedure termination. A table can’t be created, dropped and re-created with the same name in a single procedure.
  • Stored procedures are parsed in singe pass and will not resolve forward or backward references. I.e. a stored procedure must create temporary table before referencing it, or it must exist at the time of stored procedure creation.
  • Types of Parameters: – Input and Output
  • Maximum number of parameters: – 255
  • A single parameter can’t be input as well as output.

20.19 If there are nnumbers of select statements in a SP, and one select is not working properly, how will you exactly point out the select statement that is not functioning correctly?
OR
How will you debug a SP?

Debug it by using print /select statement.

« Previous | 0 | 1 | 2 | 3 | 4 | Next »


copyright © 2014 - all rights riserved by javatechnologycenter.com