Technical Interview Questions and Answers :: SQL
31 / 108
: The insert and Delete commands are known as magic tables in SQL.
32 / 108
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.
33 / 108
Nnormalization is step by step process of spiliting the bigger table into smaller table without changing any functionality. This wil improve the performance.
There are some way to normalize table
A. 1nf
B.2nf
C.3nf
34 / 108
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.
35 / 108
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.
36 / 108
No Discussion on this question yet!
37 / 108
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.
38 / 108
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
39 / 108
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.
40 / 108
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.