1. Triggers
In a DBMS, a trigger is a SQL procedure that initiates an action (i.e., fires an action) when an event (INSERT, DELETE or UPDATE) occurs. Since triggers are event-driven specialized procedures, they are stored in and managed by the DBMS. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion.
2. Index Covering
A nonclustered index that includes (or covers) all columns used in a query is called a covering index. Index covering means that “Data can be found only using indexes, without touching the tables”.
3. DB JOINs
At the very first JOIN means catesian product of two tables and join can take some codition to filter out the results.
As example consider two table and a sample query
TableA(id, name)
TableB(id, address)
select * from TableA ta $1 TableB tb on ta.id=tb.id
1. if $1=INNER JOIN
JOIN is equivalent to INNER JOIN
Records will be picked up if ta.id=tb.id
2. if $1=LEFT OUTER JOIN
Records will be picked up, if ta.id=tb.id union ta.id=null
3. if $1=RIGHT OUTER JOIN
Records will be picked up, for ta.id=tb.id union tb.id=null
4. if $1=NATURAL JOIN
Records will be picked up, for ta.id=tb.id because their column name is same in both tables.
The NATURAL JOIN keyword specifies that the attributes whose values will be matched between the two tables are those with matching names.
5. CROSS JOIN
Records will be picked up, without any codition, Means its a INNER JOIN without comparision check.
6. FULL OUTER JOIN
A full outer join combines the results of both left and right outer joins
7. SELF JOIN
An INNER JOIN of own table
6. CURSOR
Cursors are supported inside stored procedures and functions and triggers. The syntax is as in embedded SQL. Cursors in MySQL have these properties:
* Asensitive: The server may or may not make a copy of its result table
* Read only: Not updatable
* Non-scrollable: Can be traversed only in one direction and cannot skip rows
CURSOR works with FETCH, REPEAT,
7. How to quantize data in Database
database are quantified , by the size of the directory that
hold the database , and the number of entry on each tables
8. What is a “constraint”?
A constraint allows you to apply simple referential integrity checks to a table.
There are four primary types of constraints that are currently supported by SQL Server:
PRIMARY/UNIQUE - enforces uniqueness of a particular table column.
DEFAULT - specifies a default value for a column in case an insert operation does not provide one.
FOREIGN KEY - validates that every value in a column exists in a column of another table.
CHECK - checks that every value stored in a column is in some specified list
Each type of constraint performs a specific type of action.
9. What action do you have to perform before retrieving data from the next result set of a stored procedure?
Move the cursor down one row from its current position. A ResultSet cursor is initially positioned before the first row.
10. What are two methods of retrieving SQL?
1.select
2.cursor
11. What does the term unnormalized relation refer to? How did the normal forms develop historically from first normal form up to Boyc-Codd normal form?
Unnormalized relation will contain update,insert,deletion anamolies. 1nf will remove repeating colums. 2nf will remove subsets of data. In 2nf every non-key attribute should depend on entire primary key
In 3nf, all columns should depend directly upon primary key and transitive dependency should be removed(a->b b->c c->a).
In 4nf, multivalued dependancy should be removed.
In BCNF, every determinant should be a candidate key.
No comments:
Post a Comment