|
SQL: Questions
|
| What is the difference between Delete and Truncate command in
SQL? |
| 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 where the 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? |
|
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. |
| 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
<NULL> 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. |
| Write a query to select the second highest salary from a
table. |
| SELECT
max(salary) AS salary2 FROM orders WHERE salary < (SELECT max(salary) AS
salary1 FROM orders) |
| Write a query to select the 5th highest salary from
a table. |
| Answer:
SELECT min(salary) AS high5 FROM employee WHERE salary IN(SELECT DISTINCT TOP 5
salary FROM orders ORDER BY salary DESC) |
| How to find duplicate records with the number they are
duplicated? |
| SELECT
Id, count (*) as number_records from table group by id having count (*) > 1.
|
|
|