SQL

 1.How do you create a table in SQl ?
Create table <table name> (<column name1> <datatype>,<column name2> <datatype>)
Create table A (Name varchar(20),Age int)


2. How do you add new column to a table?
Alter table <tablename> add <columnname> <datatype>

Alter table A Add Location varchar(20).


3.How do you remove a column from a table?
Alter table <tablename> drop  coulmn <columnname>
Alter table A drop column Location.

4.Diff bt Drop ,Delete and Truncate.

Drop	Trucate	Delete
DDL Commands	DDL commands	DML command
No Rollback possible	No Rollback possible	Can be rolled back
Drops the complete table along with structure and data	Removes the data	Removes the data
Drops complete table	Drops complete table	Selective deletion is possible

 
 5.Select all the students whose age is between 10 to 15.
Student(Name,Age)

select name from Student where Age between 10 and 15


6.Diff bt Primary Key and Unique Key.
Both are used to uniquely identify rows

Primary Key-Cannot be null,Only one primary key can exist in a table
Unique Key-can be null,can have more than one unique key in table.


7.Whats the diff bt having and where clause?
Where -used in all DML statements.used only with group by function
Having -used with aggregate or group.can be used only with SELECT


8.How to you copy large data to SQL Server?
Using  a tool called Bulk Copy.


9.Find all the students whose marks is greater than the average marks.
Student(Name,Marks)
Select Name from Student having Marks>Avg(Marks) group by Name.

10.Whats the diff bt group by and orderby ? 
Group by-This is used only with aggregate functions.It sorts by rows.The results are displayed in groups.
orderby-mainly used with select statements.sorts by columns.

11.What is a default TCP/IP socket assigned for SQL Server?
1433

12.Whats the output of the foll sql statement-Student(Name,Roll no) contains 3 rows.Select 1,2,'India' from Student

It prints 3 columns and 3 rows . the first rows all the column contains 1. the second column contains 2 and 3rd contains india.

13.How do you delete repeated data by maintaining atleast on occurence of the data.

Student(Name,Roll no)

a)if there are 2 repeated records it deletes the one one occurence.

delete top(1) from student where name='Cha'

b)select distinct *  from student

c)Rowcount can be set.the belwo queries deletes only one occurrence of repeated data.

set rowcount 1

delete from student where name='cha'

Stored Procedure

1.What are Stored Procedure ?
They are set of SQL statements which have been grouped together and stored in database.

They can be executed over and over again.

2.How to compile stored procedure on each execution?

By using recompile option.

exec sp_recompile MyStoredProcedure.

3.Write a SP using input parameters.
create procedure sp
@id int
as
select * from <tablename> where ID=@id

exec sp 22

4.Write a SP to using input and output parameters.
create procedure sp
@id int,
@Name varchar output
as
select Name from recent where ID=@id

declare @Name varchar
exec sp 22,@Name output

You have to declare the output parameter before printing it.

5.Write a sp to update a column taking user inputs.

create procedure sp
@Name varchar(20),
@id int
as
update  <tablename> set Name =@Name where ID=@id

exec sp 'test' ,22

6. How Implement error handling in stored procedure ?

There are many diff ways-
a)Try-Catch Block
b)@@Errorcount

7.Whats the diff bt user defined function and stored procedure.
 A user-defined function is a routine that encapsulates useful logic for use in other queries
a)UDF functions can be used with select statement.Sp cannot.
b)SP can /cannot return more than one value.UDF has to return values and it can return only one value.
c)SP can have both input and output parameters wheras UDF can have only input parameters.
d)Sp can have try catch block to handle exceptions ,this cannot be done in UDF.
e)UDF can be called from procedure whereas visa versa is not possible.

Trigger

1.What is Trigger ?

They are special kind of stored procedure that are automatically executed. They are executed when an insert,update or delete operation is performed.

2.Can you fire Trigger from c#?

No

3.can stored procedure fire Triggers?

Yes

JOINS

  

                                                   Student                     Teacher 

 
1. What do you mean by cross join ?
It joins both the tables and returns the Cartesian product of both of them .


select * from  Student,Teacher

2.What is inner join ?
it joins two tables based on the atleast one match in both the tables.
select * from Student s inner join teacher t on s.id=t.id



3.what is left outer join ?
Prints all the values in the first table even if there are no matches in the second table .corresponding unmatched value in second table is printed as null

3.what is right outer join ?
Prints all the values in the second table. corresponding value in first table is printed as null

UNION

1.What is union?
combines only distinct values from both the tables into a single table.
select * from Student
union
select * from Teacher

select * from Teacher
union
select * from Student

 
2.What is union all ?
Combines all the values from both the tables.


select * from Teacher
union all
select * from Student

View
1.What is View?
It is a virtual table which encapsulated many complex queries.

2.Advantages and disadvantage of using a view?
a)A temp table can be created by joining many tables.
b)selective exposure of data from the original table.
c)restrict the access to the original table.
d)They do not consume space as they are created dynamically.
disadvantage
a)When  table is dropped view becomes inactive
b)cannot use DML if view is created using more than one table

3.How do you create a View?
Create View<Viewname> as
select * from <tablename>

4. If data is changed in actual table is it reflected in view ?
Yes

5.If data is changed in  View is it reflected in actual table?
No


Index
1.What are Indexes ?
Indexes is similar to indexes in dictionary.it helps in finding the data quickly.Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

2.How do you create Indexes ?
Create index A on Student (Name,Age)


3.What are diff types of index in SQL?
Clustered index- Reorders the records in the way they are created in the table.The logical grouping of table is similar to physical grouping.A table can have only one clustered index.

Non clustered index-he logical order of the index does not match the physical stored order of the rows on disk.A table can have more than one clustered index.

Note--i personally found this website really good for sql -http://www.w3schools.com