sql interview question
Delete command and truncate command both will delete the data, however the truncate command can not be rolled back as delete can be. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate.
® What is Magic Table in SQL?
The insert and Delete commands are known as magic tables in SQL.
® Can Primary key is a Foreign Key on the same table?
Yes, Consider a category table in a e-commerce web site.
Category_Id, Category_Name, Parent_Category_ID. In this table all the parent categories are also categories. When we create a self join category id will be treated as foreign key to the same table.
® What is Normalization? What are it’s rules?
Normalisation is the technique in the database design wher ethe idea is to reduce the redundancy of non key data items across the table.
* Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.
* Rule 2: A field should have the same meaning in each row of the table.
* Rule 3: Each table should represent at most one entity.
* Rule 4: Multiple instances of an entity should be represented by multiple rows in a table.
* Rule 5: Joins should be based only on primary and foreign-key equality.
* Rule 6: Make sure keys are linked correctly.
® What are the advantages and disadvantages of Normalization?
Answer: There are several advantages of normalization as under:
* Faster sorting and index creation.
* A larger number of clustered indexes.
* Narrower and more compact indexes.
* Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements
* Fewer null values and less opportunity for inconsistency, which increase database compactness.
Beside the above benefits there are few disadvantages as well:
* Increased amount of Normalization increases the amount of complexity of joins between tables and that hinders the performance.
® What are the conditions to achieve the normalization?
There are few conditions to achieve the normalization:
* There should be a unique row identifier.
* A table should store only data for a single type of entity. For e.g. details for book’s publisher and book’s author should be saved under different table.
* A table should avoid columns which can be null-able.
* A table should avoid duplication of data and columns.
® What is a Stored Procedure? State its advantage.
A stored procedure is a set of pre-compiled SQL commands (query statements), which are stored in the server. It is faster then the loose SQL statements processed on client, as it is pre-compiled. It can execute more then one SQL commands once as they are bundled in a single entity. We can use control statements within the stored procedure, which will allow us to repeat some SQL command. It can send return values depending upon the result. Stored procedures are used to reduce network traffic.
® What is a Trigger?
Triggers are a special type of stored procedure, which gets invoked upon a certain event. They can be performed upon an INSERT, UPDATE and DELETE.
® What is a Clustered Index?
The data rows are stored in order based on the clustered index key. Data stored is in a sequence of the index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index. A clustered index usually provides faster access to data than does a non-clustered index
® What is a Non-Clustered Index?
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. In a clustered index, the physical order of the rows in the table is not same as the logical (indexed) order of the key values.
® Describe the three levels of data abstraction?
The are three levels of abstraction:
* Physical level: The lowest level of abstraction describes how data are stored.
* Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
* View level: The highest level of abstraction describes only part of entire database.
® What is DDL (Data Definition Language)?
A data base schema which is specified by a set of definitions expressed by a special language is called DDL. Data Definition Language (DDL) is used to define and manage all the objects in an SQL database.
® What is DML?
It is a special language used to manipulate the Data. Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL.
® 14. What is a PRIMARY KEY?
The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.
® What is a FOREIGN KEY?
A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.
® What is a UNIQUE KEY?
A UNIQUE KEY is one or more columns that must be unique for each row of the table.
® What is the difference between UNIQUE and PRIMARY KEY?
The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as value is also restricted.
® What is a VIEW?
A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
® What is a ROWID?
ROWID is the logical address of a row, and it is unique within the database.
® What is INDEX?
INDEX is a general term for an SQL feature used primarily to speed up execution and impose UNIQUENESS upon data. You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table. The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify.
® What is a cursor?
An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.
® The Difference between 'Count' and 'Count (*)'?
'Count': Counts the number of non-null values. 'Count (*)': Counts the number of rows in the table, including null values and duplicates
