Wednesday, 19 June 2013

How to create Default Text (SearchBox) in TextBoxes

In this recipe, let's see how to create a search box in ASP.NET with some default information text. The text is displayed only when the search box is out of focus.
Getting ready 1. Add a new web form Recipe1.aspx to the current project.
2. Add a TextBox field with a Search button to the form as below:
3. The CSS class defaultText attached to the TextBox is defined as below:
.defaultText { font-style:italic;
color:#CCCCCC; }
How to do it… 1. In the document.ready() function, retrieve the TextBox control using its ClientID
and save in a local variable:
var searchBox = $('#<%=TextBox1.ClientID%>') ;
2. On the focus event, check if it contains the default text:
searchBox.focus( function() {
if (searchBox.val() == this.title) {
The ToolTip property of the ASP.NET TextBox control is rendered
as title at runtime. Thus, the ToolTip text Enter your search keyword here is retrieved using this.title.
3. If yes, then remove the defaultText css style:searchBox.removeClass("defaultText");
4. Also clear the search field:
searchBox.val("");
} }); 5. On the blur event, check if the TextBox is empty:
searchBox.blur( function() {
if (searchBox.val() == "") {
6. If yes, then attach the "defaultText" css style:
searchBox.addClass("defaultText");
7. Add the default information text to the search field:
searchBox.val(this.title); } });
8. Call the blur event on page load so that the TextBox is initially out of focus:
searchBox.blur();
[code]


    
    
    
    



    
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="Scripts/jquery-1.4.1.js" type="text/javascript"></script>
    <link href="StyleSheet.css" rel="stylesheet" type="text/css"></link>
    <script type="text/javascript">
    $(document).ready(function() {
    var searchBox = $('#<%=TextBox1.ClientID%>');
    searchBox.focus(
    function() {
        if (searchBox.val() == this.title) {
            searchBox.removeClass("defaultText");
            searchBox.val("");
        }
    });
    searchBox.blur(
    function () {
        if (searchBox.val() == "") {
            searchBox.addClass("defaultText");
            searchBox.val(this.title);
        }
    });
searchBox.blur();
});
</script>

</head>
<body>
    <form id="form1" runat="server">
   <div align="center">
    <fieldset style="height: 80px; width: 400px;">
    <asp:textbox cssclass="defaultText" id="TextBox1" runat="server" tooltip="Enter your search keyword here" width="200px"></asp:textbox>
    <asp:button id="btnSubmit" runat="server" text="SEARCH">
    

    </asp:button>
    </fieldset>
</div>
</form>
</body>
</html>
[/code]

Sunday, 9 June 2013

What is Team Foundation Server

                                                   Introduction to TFS
TFS stands for Team Foundation Server which is developed by Microsoft. Integration of TFS with Visual Studio enables a team to work together and organize their efforts to complete a project. Dot Net developers use TFS for source control, bug tracking, requirement gathering and to manage complete life cycle of software development. It has below listed features:-
  1. Communication Enhancement

    TFS enhance the communication among team members by ensuring that no information or work is lost when a team member hand over his task to team. Project of the team is stored on the TFS and every team member has its own credentials to connect to TFS for accessing the project. TFS provides a central location to team project and each team member coordinate his work at this location.
  2. Team Explorer

    All members of a team work together on the team project by using Team Explorer in Visual Studio. Team Explorer connects to the TFS and displays team projects from the server. By using Team Explorer, every team member can get updated work items, projects documents and task to do.
  3. Roles

    Roles can be defined on team project that is on TFS. Each role represents one or more disciplines that are required to successful completion of the team project.
  4. Alerts

    TFS also provides alerts that are sent to team members by e-mail when something has changed on the team project. We can customize alerts on TFS according to our need.
  5. Source Control

    TFS allow the team to manage all source files for a project. We can also add non-source files such as important project documents, project plans etc. to TFS.
  6. Builds

    TFS Build enables the team to create and manage product builds regularly. TFS Build also provides build reports on the status and quality of each build.
  7. Tracking Work Status

    Using TFS project head can track the assigned work status to developers to understand the health of the project.
  8. Work Item History

    All working items are logged on TFS with all changes. Anyone can review the complete history of activity on a work item at any time. The working item history can be exported into Microsoft Excel.
  9. Reports

    Reports related to track status and information (like as work item changes, check-ins and status of product builds etc.) about the project are stored in a database on the TFS.

Monday, 20 May 2013

How to apply css to text box control

How to apply css to text box control Create Website:- 1) Open Visual Studio 2) File->New Website->Give Website name and path. 3) Choose C# Paste below code in your aspx file. [code]
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="Login" %>



    How to apply css to text box control
       


    
[/code]

Sunday, 19 May 2013

How to Edit/Update Gridview Row..

How to Edit/Update Gridview Row..


In This Article I will show you , How Can we Edit/Update Gridview Row.Below I am showing GridView data




If We Want to Edit any Row .Press Edit then We will Get Update and Cancel button .Simply We can Update Row value.




GridWithEditEvent.aspx


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridWithEditEvent.aspx.cs" Inherits="GridWithEditEvent" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:GridView ID="gridview" runat="server" AutoGenerateColumns="False" 
            onrowcancelingedit="onRowCancelingEdit" onrowediting="OnRowEdit" 
            onrowupdating="OnRowUpdate" GridLines="None" Width="67%" Style="text-align: left" BackColor="#99CCFF">
   <Columns>
   <asp:TemplateField HeaderText="Student Id">
    <ItemTemplate>
    <asp:Label ID ="id" runat="server" Text='<%#Eval("StudentId") %>'></asp:Label>
    </ItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField>
    <ItemTemplate>
    <asp:Label ID ="lblname" runat="server" Text='<%#Eval("Name") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtname" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
    </EditItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField HeaderText="Student RollNo">
    <ItemTemplate>
    <asp:Label ID ="lblrollno" runat="server" Text='<%#Eval("RollNo") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtrollno" runat="server" Text='<%#Eval("RollNo") %>'></asp:TextBox>
    </EditItemTemplate>
   </asp:TemplateField>

   <asp:TemplateField HeaderText="Student Course">
    <ItemTemplate>
    <asp:Label ID ="lblcourse" runat="server" Text='<%#Eval("Course") %>'></asp:Label>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:TextBox ID="txtCourse" runat="server" Text='<%#Eval("Course") %>'></asp:TextBox>
    </EditItemTemplate>
   </asp:TemplateField>

    <asp:TemplateField>
    <ItemTemplate>
    <asp:LinkButton ID="lnkbtn" runat="server" CommandName="Edit">Edit</asp:LinkButton>
    </ItemTemplate>
    <EditItemTemplate>
    <asp:LinkButton ID="lnkupdate" runat="server" CommandName="Update">Update</asp:LinkButton>
    <asp:LinkButton ID="lnkcancel" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
    </EditItemTemplate>
   </asp:TemplateField>
   </Columns>
   <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                <AlternatingRowStyle BackColor="White" />
    </asp:GridView>
    </div>
    </form>
</body>
</html>


GridWithEditEvent.aspx.cs[CodeBehind Page]

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class GridWithEditEvent : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(@"Data Source=RAM-PC\SQLEXPRESS;Initial Catalog=CMC;integrated security=true");
    SqlDataAdapter adap = null;
    DataSet ds = null;
    //int rowNo;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetStudentData();
        }
    }
    private void GetStudentData()
    {
        
        adap = new SqlDataAdapter("select * from Student", con);
        ds = new DataSet();
        adap.Fill(ds, "Student");
        gridview.DataSource = ds.Tables["Student"];
        gridview.DataBind();
    }
    protected void OnRowEdit(object sender, GridViewEditEventArgs e)
    {
        gridview.EditIndex = e.NewEditIndex;
        GetStudentData();
    }
    protected void OnRowUpdate(object sender, GridViewUpdateEventArgs e)
    {
        int studentid =Convert.ToInt32( ((Label)gridview.Rows[e.RowIndex].FindControl("id")).Text);
        string studentName = ((TextBox)gridview.Rows[e.RowIndex].FindControl("txtname")).Text;
        int studentRollNo = Convert.ToInt32(((TextBox)gridview.Rows[e.RowIndex].FindControl("txtrollno")).Text);
        string studentCourse = ((TextBox)gridview.Rows[e.RowIndex].FindControl("txtCourse")).Text;
        string query = "update Student set Name='"+studentName+"',RollNo='"+studentRollNo+"',Course='"+studentCourse+"' where StudentId="+studentid+"";
        con = new SqlConnection(@"Data Source=RAM-PC\SQLEXPRESS;Initial Catalog=CMC;integrated security=true");
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter adap = new SqlDataAdapter(cmd);
        int n = adap.SelectCommand.ExecuteNonQuery();
        if (n > 0)
        {
            
            gridview.EditIndex = -1;
            GetStudentData();
            Response.Write("Data Updated successfully");
        }


    }
    protected void onRowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gridview.EditIndex = -1;
        GetStudentData();
    }
}

How to make ImageRotator with JQUERY in ASP.Net

How to make ImageRotator with JQUERY in ASP.Net

Here I am writing a simple Jquery Code that is in Bold lettters.

Code

<%@ Master Language="C#" AutoEventWireup="true" CodeFile="Main.master.cs" Inherits="Main" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
<script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            // create the image rotator
            setInterval("rotateImages()", 2000);
        });
        function rotateImages() {
            var oCurPhoto = $('#photoShow div.current');
            var oNxtPhoto = oCurPhoto.next();
            if (oNxtPhoto.length == 0)
                oNxtPhoto = $('#photoShow div:first');

            oCurPhoto.removeClass('current').addClass('previous');
            oNxtPhoto.css({ opacity: 0.0 }).addClass('current').animate({ opacity: 1.0 }, 1000,
                function () {
                    oCurPhoto.removeClass('previous');
                });
        }
    </script>
    <style type="text/css">
        #photoShow {
            position: absolute;
            height: 200%;
            width: 800px;
             margin: -100px 0 0 -200px;
            top: 32%;
            left: 32%;
            
        }
        #photoShow div {
            position:absolute;
            z-index: 0;
        }
        #photoShow div.previous {
            z-index: 1;
        }
        #photoShow div.current {
            z-index: 2;
    }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     
           <div id="photoShow" align="center">
           <div align="center">
           <asp:Image ID="imgrotat1" ImageUrl="~/Images/1.png" runat="server" />
           </div>
           <div>
           <asp:Image ID="imgrotat2" ImageUrl="~/Images/2.png" runat="server" />
           </div>
           <div>
           <asp:Image ID="imgrotat3" ImageUrl="~/Images/3.png" runat="server" />
           </div>
           <div>
           <asp:Image ID="imgrotat4" ImageUrl="~/Images/4.png" runat="server" />
           </div>
           <div>
           <asp:Image ID="imgrotat5" ImageUrl="~/Images/5.png" runat="server" />
           </div>
           </div>
                 
    </div>
    </form>
</body>
</html>

Thursday, 16 May 2013

How ASP.NET Web Pages are Processed on the Web Server


How ASP.NET Web Pages are Processed on the Web Server



Step 1: The Web Server Receives the HTTP Request


The sole task of a Web server is to accept incoming HTTP requests and to return the requested resource in an HTTP response. The 4Guys Web server runs Microsoft's Internet Information Services (IIS) Web server. The first things IIS does when a request comes in is decide how to handle the request. Its decision is based upon the requested file's extension. For example, if the requested file has the .asp extension, IIS will route the request to be handled by asp.dll.
There are numerous file extensions that map to the ASP.NET engine, some of which include:
  • .aspx, for ASP.NET Web pages,
  • .asmx, for ASP.NET Web services,
  • .config, for ASP.NET configuration files,
  • .ashx, for custom ASP.NET HTTP handlers,
  • .rem, for remoting resources,
  • And others!
In the IIS administration screens, you can configure the extension mappings. The screenshot to the right shows the configuration screen for IIS 5.0. You could, for example, add your own custom extensions here. That is, you could have requests for .scott files routed to the ASP.NET engine.
The diagram below illustrates the steps 0 and 1 of a request for an ASP.NET Web page. When a request comes into the Web server, it is routed to the proper place (perhaps asp.dll for classic ASP page requests, perhaps the ASP.NET engine for ASP.NET requests) based on the requested file's extension.

Step 2: Examining the ASP.NET Engine


An initial request for http://mrjsoftware/home.aspx will reach IIS and then be routed to the ASP.NET engine, but what happens next? The ASP.NET engine is often referred to as the ASP.NET HTTP pipeline, because the incoming request passes through a variable number of HTTP modules on its way to an HTTP handler.
HTTP modules are classes that have access to the incoming request. These modules can inspect the incoming request and make decisions that affect the internal flow of the request. After passing through the specified HTTP modules, the request reaches an HTTP handler, whose job it is to generate the output that will be sent back to the requesting browser. The following diagram illustrates the pipeline an ASP.NET request flows through.
There are a number of pre-built HTTP modules that are included in the HTTP pipeline by default. These modules include:
  • OutputCache, which handles returning and caching the page's HTML output, if needed
  • Session, which loads in the session state based on the user's incoming request and the session method specified in the Web.config file
  • FormsAuthentication, which attempts to authenticate the user based on the forms authentication scheme, if used
  • And others!
In fact, you can see a precise list of what modules are used by default by going to the machine.config file (located in the$WINDOWS$\Microsoft.NET\Framework\$VERSION$\CONFIG directory) and searching for the <httpModules> element. The following shows the default <httpModules> element:

<httpModules>
  <add name="OutputCache" type="System.Web.Caching.OutputCacheModule" />
  <add name="Session" type="System.Web.SessionState.SessionStateModule" />
  <add name="WindowsAuthentication" type="System.Web.Security.WindowsAuthenticationModule" />
  <add name="FormsAuthentication" type="System.Web.Security.FormsAuthenticationModule" />
  <add name="PassportAuthentication" type="System.Web.Security.PassportAuthenticationModule" />
  <add name="UrlAuthorization" type="System.Web.Security.UrlAuthorizationModule" />
  <add name="FileAuthorization" type="System.Web.Security.FileAuthorizationModule" />
  <add name="ErrorHandlerModule" type="System.Web.Mobile.ErrorHandlerModule, 
                                          System.Web.Mobile, Version=1.0.5000.0, 
                                          Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</httpModules>
HTTP handlers are the endpoints in the ASP.NET HTTP pipeline. The job of the HTTP handler is to generate the output for the requested resource. For ASP.NET Web pages, this means rendering the Web controls into HTML and returning this HTML. For a Web service, it would involve executing the specified method and wrapping its return values into an appropriately formatted SOAP response. Different ASP.NET resources use different HTTP handlers. The handlers used by default are spelled out in the machine.config's <httpHandler> section. Entries in this section refer to classes that are either HTTP handlers themselves or are HTTP handler factories. An HTTP handler factory merely returns a suitable HTTP handler instance when invoked.
The following shows a snippet of the <httpHandler> element in the default machine.config file:


<httpHandlers>
  <add verb="*" path="*.vjsproj" type="System.Web.HttpForbiddenHandler" />
  <add verb="*" path="*.java" type="System.Web.HttpForbiddenHandler" />
  <add verb="*" path="*.jsl" type="System.Web.HttpForbiddenHandler" />
  <add verb="*" path="trace.axd" type="System.Web.Handlers.TraceHandler" />
  <add verb="*" path="*.aspx" type="System.Web.UI.PageHandlerFactory" />
  <add verb="*" path="*.ashx" type="System.Web.UI.SimpleHandlerFactory" />
  ...
</httpHandlers>
Realize that you can create your own HTTP modules and HTTP handlers, and then plug them into the pipeline for all Web sites on the Web server by modifying machine.config, or you can add them to a particular Web application by modifying that application's Web.config file. A thorough discussion on using HTTP modules and handlers is far beyond the scope of this article, but realize that you can accomplish some neat things using modules and handlers. For example, you can use HTTP modules to provide a custom URL rewritter, which can be useful for automatically fixing 404 errors to using shorter and user-friendlier URLs. 

Step 3: Generating the Output


The final step is for the suitable HTTP handler to generate the appropriate output. This output, then, is passed back through the HTTP modules and then back to IIS, which then sends it back to the client that initiated the request. (If the client was a Web browser, the Web browser would receive this HTML and display it.)
Since the steps for generating the output differ by HTTP handler, let's focus in on one in particular - the HTTP handler that is used to render ASP.NET Web pages. To retrace the initial steps, when a request comes into IIS for an ASP.NET page (i.e., one with a .aspx extension), the request is handed off to the ASP.NET engine. The request then moves through the modules. The request is then routed to the PageHandlerFactory, since in the machine.config's <httpHandler> section we have the mapping:

<httpHandlers>
  ...
  <add verb="*" path="*.aspx" type="System.Web.UI.PageHandlerFactory" />
  ...
</httpHandlers>
The PageHandlerFactory class is an HTTP handler factory. It's job is to provide an instance of an HTTP handler that can handle the request. What PageHandlerFactory does is find the compiled class that represents the ASP.NET Web page that is being requested.
If you use Visual Studio .NET to create your ASP.NET Web pages you know that the Web pages are composed of two separate files: a .aspx file, which contains just the HTML markup and Web controls, and a .aspx.vb or .aspx.cs file that contains the code-behind class (which contains the server-side code). If you don't use Visual Studio .NET, you likely use a server-side <script> block to hold the server-side code. Regardless of what approach you use, when the ASP.NET Web page is first visited after a change to the HTML or Web control content, the ASP.NET engine creates a class that derives from the System.Web.UI.Page class. This dynamically created class is then compiled.
Not coincidentally, the Page class implements IHttpHandler, thereby indicating that it suffices as an HTTP handler. What the PageHandlerFactory does, then, is check to see if a compiled version of the requested ASP.NET Web page's class exists. If not, it dynamically creates this class and compiles it. This class, then, has a particular method invoked which generates the page's complete HTML markup. It's this HTML markup that is then returned to the client. (Have you noticed that when visiting an ASP.NET Web page after making changes to the HTML or Web control content, there is a bit of a delay? This delay is due to the ASP.NET engine needing to recreate and recompile the ASP.NET page's corresponding class.)
Realize that the 011404-1.aspx class might need to first be generated and compiled by the PageHandlerFactory before it can be invoked to generate the HTML. The process of page rendering - which involves obtaining the HTML markup for the requested ASP.NET Web page - is a bit beyond the scope of this article

Monday, 13 May 2013

How can we compare two columns value in same table in MS Sql Server

How can we compare two columns value in same table in MS Sql Server


We have one table ExamScore 

StudentId
ExamScoreCT1
ExamScoreCT2
TotalMarks
1
87
75
100
2
68
77
100
3
76
91
100
4
88
56
100
we want to compare two compare two Columns  ExamScoreCT1 and ExamScoreCT2 of this table
we want to select greater marks between both of colums ExamScoreCT1 and ExamScoreCT2


query :


SELECT CASE  WHEN ex.ExamScoreCT1 >= ex.ExamScoreCT2  THEN ex.ExamScoreCT1  ELSE ex.ExamScoreCT2 END
FROM ExamScore as ex




Most Asked SQL Server SQL Queries


Most Asked SQL Queries

  2 tables

 Employee                                  Phone
EmployeeID (PK)                       PhoneID
Name                                          Phone-No
DOB                                            EmpID (FK)
Address
Dateofjoining
  

2. Select all employees who doesn't have phone?
SELECT empname
FROM Employee
WHERE (empid NOT IN
(SELECT DISTINCT empid
FROM phone))
3. Select the employee names who is having more than one phone numbers.
SELECT empname
FROM employee
WHERE (empid IN
(SELECT empid
FROM phone
GROUP BY empid
HAVING COUNT(empid) > 1))
4. Select the details of 3 max salaried employees from employee table.
SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC
5. Display all managers from the table. (manager id is same as emp id)
SELECT empname
FROM employee
WHERE (empid IN
(SELECT DISTINCT mgrid
FROM employee)) 

6. Write a Select statement to list the Employee Name, Manager Name under a particular manager?
SELECT e1.empname AS EmpName, e2.empname AS ManagerName
FROM Employee e1 INNER JOIN
Employee e2 ON e1.mgrid = e2.empid
ORDER BY e2.mgrid 

7.  2 tables emp and phone.
emp fields are - empid, name
Ph fields are - empid, ph (office, mobile, home). Select all employees who doesn't have any ph nos.
SELECT *
FROM employee LEFT OUTER JOIN
phone ON employee.empid = phone.empid
WHERE (phone.office IS NULL OR phone.office = ' ')
AND (phone.mobile IS NULL OR phone.mobile = ' ')
AND (phone.home IS NULL OR phone.home = ' ') 

8. Find employee who is living in more than one city.
Two Tables:
          Employee
City
EmpID(PK)
EmpName
Salary

Cityid(PK)
CityName
Empid(FK)


9. SELECT empname, fname, lname
FROM employee
WHERE (empid IN
(SELECT empid
FROM city
GROUP BY empid
HAVING COUNT(empid) > 1))
10. Find all employees who is living in the same city. (table is same as above)
SELECT fname
FROM employee
WHERE (empid IN
(SELECT empid
FROM city a
WHERE city IN
(SELECT city
FROM city b
GROUP BY city
HAVING COUNT(city) > 1))) 

11. There is a table named MovieTable with three columns - moviename, person and role. Write a query which gets the movie details where Mr. Amitabh and Mr. Vinod acted and their role is actor.
SELECT DISTINCT m1.moviename
FROM MovieTable m1 INNER JOIN
MovieTable m2 ON m1.moviename = m2.moviename
WHERE (m1.person = 'amitabh' AND m2.person = 'vinod' OR
m2.person = 'amitabh' AND m1.person = 'vinod') AND (m1.role = 'actor') AND (m2.role = 'actor')
ORDER BY m1.moviename 

12. There are two employee tables named emp1 and emp2. Both contains same structure (salary details). But Emp2 salary details are incorrect and emp1 salary details are correct. So, write a query which corrects salary details of the table emp2
update a set a.sal=b.sal from emp1 a, emp2 b where a.empid=b.empid 

13. Given a Table named “Students” which contains studentid, subjectid and marks. Where there are 10 subjects and 50 students. Write a Query to find out the Maximum marks obtained in each subject.
14. In this same tables now write a SQL Query to get the studentid also to combine with previous results.
15. Three tables – student , course, marks – how do go at finding name of the students who got max marks in the diff courses.
SELECT student.name, course.name AS coursename, marks.sid, marks.mark
FROM marks INNER JOIN
student ON marks.sid = student.sid INNER JOIN
course ON marks.cid = course.cid
WHERE (marks.mark =
(SELECT MAX(Mark)
FROM Marks MaxMark
WHERE MaxMark.cID = Marks.cID)) 

16. There is a table day_temp which has three columns dayid, day and temperature. How do I write a query to get the difference of temperature among each other for seven days of a week?
SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference
FROM day_temp a INNER JOIN
day_temp b ON a.dayid = b.dayid + 1
OR
Select a.day, a.degree-b.degree from temperature a, temperature b where a.id=b.id+1 

17. There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a query to get grand total salary, and total salaries of individual employees in one query.
SELECT empid, SUM(salary) AS salary
FROM employee
GROUP BY empid WITH ROLLUP
ORDER BY empid 

18. How to know how many tables contains empno as a column in a database?
SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno') 

19. Find duplicate rows in a table? OR I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1) 

20. How to delete the rows which are duplicate (don’t delete both duplicate records).
SET ROWCOUNT 1
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
WHILE @@rowcount > 0
DELETE yourtable
FROM yourtable a
WHERE (SELECT COUNT(*) FROM yourtable b WHERE b.name1 = a.name1 AND b.age1 = a.age1) > 1
SET ROWCOUNT 0 

21. How to find 6th highest salary
SELECT TOP 1 salary
FROM (SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary 

22. Find top salary among two tables
SELECT TOP 1 sal
FROM (SELECT MAX(sal) AS sal
FROM sal1
UNION
SELECT MAX(sal) AS sal
FROM sal2) a
ORDER BY sal DESC 

23. Write a query to convert all the letters in a word to upper case
SELECT UPPER('test') 

24. Write a query to round up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.
SELECT CEILING (7.1) 

25. Write a SQL Query to find first day of month?
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

DatePart

Abbreviation

year
Yy, yyyy
quarter
qq,q
month
Mm,m




26. Table A contains column1 which is primary key and has 2 values (1, 2) and Table B contains column1 which is primary key and has 2 values (2, 3). Write a query which returns the values that are not common for the tables and the query should return one column with 2 records.
SELECT tbla.a
FROM tbla, tblb
WHERE tbla.a <>
(SELECT tblb.a
FROM tbla, tblb
WHERE tbla.a = tblb.a)
UNION
SELECT tblb.a
FROM tbla, tblb
WHERE tblb.a <>
(SELECT tbla.a
FROM tbla, tblb
WHERE tbla.a = tblb.a)

OR (better approach)

SELECT a
FROM tbla
WHERE a NOT IN
(SELECT a
FROM tblb)
UNION ALL
SELECT a
FROM tblb
WHERE a NOT IN
(SELECT a
FROM tbla) 

27. There are 3 tables Titles, Authors and Title-Authors (check PUBS db). Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has written the minimum number of books.
SELECT authors.au_lname, COUNT(*) AS BooksCount
FROM authors INNER JOIN
titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN
titles ON titles.title_id = titleauthor.title_id
GROUP BY authors.au_lname
ORDER BY BooksCount DESC 

28. 
UPDATE emp_master
SET emp_sal =
CASE
WHEN emp_sal > 0 AND emp_sal <= 20000 THEN (emp_sal * 1.01) WHEN emp_sal > 20000 THEN (emp_sal * 1.02)
END 

29. List all products with total quantity ordered, if quantity ordered is null show it as 0.
SELECT name, CASE WHEN SUM(qty) IS NULL THEN 0 WHEN SUM(qty) > 0 THEN SUM(qty) END AS tot
FROM [order] RIGHT OUTER JOIN
product ON [order].prodid = product.prodid
GROUP BY name
Result:
coke 60
mirinda 0
pepsi 10