SQL InterviewQuestions-Part4

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

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

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.

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.

Validating User Credentials

Validating User Credentials
---------------------------------

Let us see how to validate the user credentials(login and password)

In aspx.cs under Button_Click() method write the following code

string email= txtemail.Text;
string password = txtpassword.Text;

SqlConnection con = new SqlConnection(connectionstring);
string str = “select * from UserLoginInfo where email=’ ” + email + “ ‘ and password = ‘ “ + password + “ ‘ “;
SqlCommand cmd = new SqlCommand(str, con);

try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
bool userexists = false;

while(dr.Read())
{
userexists = true;
}
if(userexists)
{
Server.Transfer(“Nextpage.aspx”);
}
else
{
lblresult.Text = “ Please enter the valid email and password”;
}
catch(exception e)
{
throw e;
}
finally
{
con.Close();
}
}

Here we are taking the user entered values(textbox values) into strings and checking them with the values in table UserLoginInfo. If both the values matches then the variable userexists becomes true and you will be derected to another(in this case Nextpage.aspx) page otherwise user will get the message “please enter the valid email and password.

In .aspx page create 2 textboxes for email and password and 1 submit button. In button_click method write the above code.

3-Tier Architecture

3-Tier Architecture
-----------------------
Today Iam going to discuss about the 3-Tier Architecture .

PresentationLayer: This is the layer through which user enters the values or values are shown to the user.
BusinessAccessLayer: This layer is used to perform calculations, validations and write business logic.
DataAccessLayer: This layer is used to connect to the database and perform the required actions which may be inserting, deleting, updating values to database or from database.

First create a table with firstname, lastname, age as its columns. Then write a storedprocedure to insert values in to that table.

Then in the project create a class library with the name DataAccessLayer.In this class library create a class with the name UserInformationDAO and write the following code

public class UserInformationDAO
   {
public void InsertUserInfo(string firstname, string lastname, int age)
     {
       SqlConnection con = new SqlConnection(connectionstring);
       SqlCommand cmd = new SqlCommand(“InsertUserData”, con);
       cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(“@firstname” , SqlDbType.Varchar, 30).value = firstname;
cmd.Parameters.Add(“@lastname” , SqlDbType.Varchar, 30).value = lastname;
cmd.Parameters.Add(“@age” , SqlDbType.Int).value = age;

try
       {
         con.Open();
         cmd.ExecuteNonQuery();
       }
catch(Exception e)
       {
         throw e;
       }
finally
       {
         con.Close();
       }
      }
   }

In the above code connectionstring means connection parameters. Which contains servername,databasename, userid, password.
InsertUserData is the name of the storedprocedure to insert values in to the
table. @firstname,@lastname,@age are the storedprocedure variables.Using cmd.Parameters.Add we are adding values to cmd.


Now once again create a class library named BusinessAccessLayer and create a class in that named UserInformationBAO.

public class UserInformationBAO
{
private string firstname;
private string lastname;
private int age;

public string FirstName
       {
          get { return firstname;}
          set { firstname = value;}
       }
public string LastName
       {
           get { return lastname;}
          set { lastname = value;}
       }
public int Age
       {
          get { return age;}
          set { age = value;}
       }

public void InsertUserInfo()
       {
          UserInformationDAO uinfo = new UserInformationDAO()
          uinfo. InsertUserInfo(FirstName, LastName, Age);
       }
}

In the above code we have written 3 properties. In InsertUserInfo method we are creating an object of UserInformationDAO class, using that we are invoking the method InsertUserInfo of UserInformationDAO class.


In .aspx.cs page you need to write the following code to read values from textboxes.

protected void btnsubmit_Click(object sender, EventArgs e)
       {
          UserInformationBAO uinfobao = new UserInformationBAO();

          uinfobao.FirstName = txtfirstname.Text;
          uinfobao.LastName = txtlastname.Text;
          uinfobao.Age = txtage.Text;
          uinfibao.InsertUserInfo();
       }

Here first we are creating an object of UserInformationBAO class and using that object we are reading the values in to properties.

In .aspx page you will have 3 textboxes for firstname, lastname and age. Below these you should create a button so that when this button is clicked values from text boxes will be saved in to database.

<div>
<table>
<tr>
<td>
<asp:Label ID=”lblfirstname” runat= “server”>FirstName </asp:Label>
<asp:Textbox ID=”txtfirstname” runat = “server” ></asp:Textbox>
</td>
</tr>

<tr>
<td>
<asp:Label ID=”lbllastname” runat= “server” >LastName</asp:Label>
<asp:Textbox ID=”txtlastname” runat = “server” ></asp:Textbox>
</td>
</tr>

<tr>
<td>
<asp:Label ID=”lblage" runat= “server” >Age</asp:Label>
<asp:Textbox ID=”txtage” runat = “server” ></asp:Textbox>
</td>
</tr>

<tr>
<td>
<asp:Button ID=”btnsubmit” runat= “server” Text = “Submit” OnClick= “btnsubmit_Click”></asp:Button>
</td>
</tr>
</table>
</div>

Binding Data to Dropdownlist from Database

Binding Data to Dropdownlist from DataBase
---------------------------------------------------------------
Today Iam going to write on Binding Data to Dropdownlist from Database

Consider that we want to populate the Dropdownlist with a list of countries. For this first we need to create a new .aspx page with the following code

<asp:dropdownlist id =" “dpdcountry”" runat="”server”">
</dropdownlist>

Now create a new classlibrary with the class name DataBinding.cs and write the following code

public class Databinding
{
public static DataTable Execute(string sqlstring)
{
SqlConnection con = new SqlConnection(connectionstring);
DataTable dt = new DataTable(“tb1”);
try
{
con.Open();
SqlCommand cmd = new SqlCommand(sqlstring, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt) ;
}
catch(Exception e)
{
throw e;
}
finally
{
con.Close();
}


return dt;
}
}


In the above code Databinding is the class name, Execute is the method which returns a datatable. In the connectionstring you need to specify the 4 parameters of the SqlConnection string.

Now in the .aspx.cs file under Page_Load method you need to write the following the code

If(!IsPostBack)
{
string str = “select country_id , name from Country”;
Datatable dt = Databinding.Execute(str);
dpdcountry.DataValueField = dt.columns[0].ToString();
dpacountry.DataTextField = dt.cloumns[1].ToString();
dpdcountry.DataSource = dt;
dpdcountry.DataBind();
}



On a whole, first we declared a dropdownlist with the name dpdcountry in the .aspx page. Then in .aspx.cs page, in the Page_Load method we are sending the sql string to the class Databinding which connects to the database and returns the data table containg the country names. Then this data table is assigned to the dropdownlist as its datasource. So, when you run this code you get a page with the dropdownlist populated with country names.
Note:
1. You should add the Class Library name in the Referrences of the Project
2. You should use the Class Library name in the directories part of the .aspx.cs page