31) What is the difference between stored procedure and a trigger?
a. Storedprocedure
1. We should call it explicitly
2. They can't be inactive
Trigger
1. They can be called automatically inplace of or after (data modifications) triggering actions like Insert, delete or update
2. Thay can be inactive
32) What is normalization?
a. It is a process of organizing data efficiently in database.
33) Advantages of normalization?
a. 1. It reduces redundant data
2. Ensures that data dependencies make sense
34) Why we use join?
a. Joins are used to query data from two or more tables, based on a relationship between certain columns in these tables
35) Types of joins?
a. 1. Join: Returns rows which have match in both the tables
2. Left Join: Returns rows which have match and the rows from the left table which have no match for them in right table
3. Right Join: Returns rows which have match and the rows from the right table which have no match for them in left table
4. Full Join : Returns rows when there is a match in one of the tables
36) why we use Rollback statement?
a. It cancels the proposed changes in a pending database transaction
37) why we use Check constraint?
a. It is used to limit the value range that can be placed in a column
38) why we use NOW() function?
a. It returns the current system date and time
39) What is a subquery?
a. A subquery is a query that appears with in another SQL data manipulation statement
40) Why we use DATEPART() function?
a. It Returns a single part of a date/time
Wednesday, March 24, 2010
Thursday, February 4, 2010
SQL InterviewQuestions-Part3
21) What is select into command?
a. It selects values from one table and inserts them into another table at the same time
ex: SELECT column1, column2,
INTO table2
FROM table1
22) Why we use DISTINCT command?
a. This is used to return only distinct values. It is used only with Select command.
ex: Select DISTINCT column(s)
From table
23) What are the wild cards?
a. These are used to substitute one or more characters when searching for data in a database
% is used to substitute zero or more characters
[charlist] any single character in charlist
_ substitute for exactly single character
[^charlist] any single character not in list
24) Can we create indexes on views?
a. Yes
25) what is the first index that we can create on a view?
a. It must be unique clustered index
26) what are temporary stored procedures?
a. Temporary stored procedures are like temporary tables and are dropped automatically when you disconnect. They are stored in tempdb and are useful when connected to earlier versions of SQL Server.They can be used when an application builds dynamic t-sql statements that are executed several times.Rather than have the t-sql statements recompiled each time, you can create a temporary stored procedure that is compiled on the first execution and then execute the precompiled plan multiple times.
27) What is referential integrity?
a. It indicates that the relationships between tables have been properly maintained
28) what is Default constraint?
a. It is used to insert default value in to a column
29) How many bytes a currency datatype requires?
a. 8 bytes
30) Can we use Where clause with aggrigate functions?
a. No
a. It selects values from one table and inserts them into another table at the same time
ex: SELECT column1, column2,
INTO table2
FROM table1
22) Why we use DISTINCT command?
a. This is used to return only distinct values. It is used only with Select command.
ex: Select DISTINCT column(s)
From table
23) What are the wild cards?
a. These are used to substitute one or more characters when searching for data in a database
% is used to substitute zero or more characters
[charlist] any single character in charlist
_ substitute for exactly single character
[^charlist] any single character not in list
24) Can we create indexes on views?
a. Yes
25) what is the first index that we can create on a view?
a. It must be unique clustered index
26) what are temporary stored procedures?
a. Temporary stored procedures are like temporary tables and are dropped automatically when you disconnect. They are stored in tempdb and are useful when connected to earlier versions of SQL Server.They can be used when an application builds dynamic t-sql statements that are executed several times.Rather than have the t-sql statements recompiled each time, you can create a temporary stored procedure that is compiled on the first execution and then execute the precompiled plan multiple times.
27) What is referential integrity?
a. It indicates that the relationships between tables have been properly maintained
28) what is Default constraint?
a. It is used to insert default value in to a column
29) How many bytes a currency datatype requires?
a. 8 bytes
30) Can we use Where clause with aggrigate functions?
a. No
Tuesday, January 26, 2010
SQL InterviewQuestions Part2
11) what is a constraint?
a) A constraint is a property which is assigned to a column or set of columns in a table. There are 5 types of constraints
Primary key, Unique key, Foreign Key, Check, Not Null
12) What is an entity integrity?
a) This ensures that there can not be duplicate rows in a table
13) What is a Trigger?
a) A trigger is a special of stored procedure which will be executed
automatically in place of or after data modifications. There are 2 types of triggers
After triggers: This is the trigger which will be executed automatically after the statement which triggered it completes
Instead of Triggers This is the trigger which will be executed automatically in place of triggering statement
14) How many after triggers we can crate for a table?
a) We can create any number of after triggers for each Insert, Update, Delete operations on a table
15) How many Instead of triggers can be created for a table?
a) Only one for each Insert, Delete, Update operations
16) Can a trigger be encrypted?
a) Yes.
17) what is the stored procedure used to set the order of after triggers?
a) sp_settriggerorder
18) can we create after triggers on a view?
a) No
19) what is a view?
a) A view is a virtual table which contains columns from one or more tables.
20) what are the advantages of views?
a) 1. It provides security. means if a view is created on a table then the users can access only those columns which are there in view. they cannot access all the columns
2. It simplifies query execution means a complex query can be stored in the form of a view and view can be accessed using a simple query.
a) A constraint is a property which is assigned to a column or set of columns in a table. There are 5 types of constraints
Primary key, Unique key, Foreign Key, Check, Not Null
12) What is an entity integrity?
a) This ensures that there can not be duplicate rows in a table
13) What is a Trigger?
a) A trigger is a special of stored procedure which will be executed
automatically in place of or after data modifications. There are 2 types of triggers
After triggers: This is the trigger which will be executed automatically after the statement which triggered it completes
Instead of Triggers This is the trigger which will be executed automatically in place of triggering statement
14) How many after triggers we can crate for a table?
a) We can create any number of after triggers for each Insert, Update, Delete operations on a table
15) How many Instead of triggers can be created for a table?
a) Only one for each Insert, Delete, Update operations
16) Can a trigger be encrypted?
a) Yes.
17) what is the stored procedure used to set the order of after triggers?
a) sp_settriggerorder
18) can we create after triggers on a view?
a) No
19) what is a view?
a) A view is a virtual table which contains columns from one or more tables.
20) what are the advantages of views?
a) 1. It provides security. means if a view is created on a table then the users can access only those columns which are there in view. they cannot access all the columns
2. It simplifies query execution means a complex query can be stored in the form of a view and view can be accessed using a simple query.
Wednesday, January 20, 2010
SQL InterviewQuestions Part1
1) Differences between Having and Where
Having:
1) It applies to a group as a whole
2) It selects rows afetr grouping
3) It can contain aggrigate functions
4) It is used only in select clause
Where:
1) It applies to individual rows
2) It selects rows before grouping
3) It can't contain aggrigate functions
4) It can be used in select, delete, insert etc
2) What is a primary key?
a) 1)A primary key is used to uniquely identify a row in a table.
2) A table can have only one primary key.
3) It does not allows nulls
4) It creats a clusterd index on a column
3) What is a clustered index?
a) In a clustered index the logical order of index matches the physical
stored order of the rows in a disk. A table can have only one clustered
index. The leaf nodes of clustered index contains the data pages(actual data)
4) What is a transaction and what are its properties?
a) A transaction is a logical unit of work in which all steps must be performed or none. It has 4 main properties
a) Atomicity
b) Consistency
c) Isolation
d) Durability
5) How many non clustered indexes we can create on a table
a) More than one
6) Differences between Union and UnionAll
a) Union:
1) This is used to eliminate duplicate rows
2) This selects only distinct rows
3) It can be used to combine any number of queries
4) It can't contain aggrigate functions
UnionAll:
1) It will not eliminate duplicate rows
2) It selects all the values
3) It can be used to combine maximum of 2 quesries
4) It can contain aggrigate functions
7) What is a composite key?
a) A key formed by combining 2 or more columns is called composite key
8) how many nulls a unique key allows?
a) It allows only one null
9) Differences between Delete and Truncate?
a) Delete:
1) It is a DML statement
2) It can activate a trigger
3) It can include a Where clause
Truncate:
1) It is a DDL statement
2) It cann't activate a trigger
3) It can't include Where clause
10) what is Atomicity?
a) It states that database modifications must follow all or none,
means if a part of a transaction fails then the entire transaction fails.
Having:
1) It applies to a group as a whole
2) It selects rows afetr grouping
3) It can contain aggrigate functions
4) It is used only in select clause
Where:
1) It applies to individual rows
2) It selects rows before grouping
3) It can't contain aggrigate functions
4) It can be used in select, delete, insert etc
2) What is a primary key?
a) 1)A primary key is used to uniquely identify a row in a table.
2) A table can have only one primary key.
3) It does not allows nulls
4) It creats a clusterd index on a column
3) What is a clustered index?
a) In a clustered index the logical order of index matches the physical
stored order of the rows in a disk. A table can have only one clustered
index. The leaf nodes of clustered index contains the data pages(actual data)
4) What is a transaction and what are its properties?
a) A transaction is a logical unit of work in which all steps must be performed or none. It has 4 main properties
a) Atomicity
b) Consistency
c) Isolation
d) Durability
5) How many non clustered indexes we can create on a table
a) More than one
6) Differences between Union and UnionAll
a) Union:
1) This is used to eliminate duplicate rows
2) This selects only distinct rows
3) It can be used to combine any number of queries
4) It can't contain aggrigate functions
UnionAll:
1) It will not eliminate duplicate rows
2) It selects all the values
3) It can be used to combine maximum of 2 quesries
4) It can contain aggrigate functions
7) What is a composite key?
a) A key formed by combining 2 or more columns is called composite key
8) how many nulls a unique key allows?
a) It allows only one null
9) Differences between Delete and Truncate?
a) Delete:
1) It is a DML statement
2) It can activate a trigger
3) It can include a Where clause
Truncate:
1) It is a DDL statement
2) It cann't activate a trigger
3) It can't include Where clause
10) what is Atomicity?
a) It states that database modifications must follow all or none,
means if a part of a transaction fails then the entire transaction fails.
Subscribe to:
Posts (Atom)
