This article demonstrates some commonly asked SQL queries in a .NET/SQL job interview.
Let’s create Employee table and insert data as shown below
to perform basic sql query operation.
CREATE TABLE #Employee
EmpID INT Identity,
EmpSal Decimal (10,2),
INSERT INTO #Employee VALUES ('Krishna',50000,'System Administrator','10/01/2011');
INSERT INTO #Employee VALUES ('John',30000,'Network Engineer','23/08/2011');
INSERT INTO #Employee VALUES ('Vicky',22000,'Help Desk','23/01/2012');
INSERT INTO #Employee VALUES ('Mayur',35000,'System Administrator','05/04/2012');
INSERT INTO #Employee VALUES ('Nitin',25000,'Help Desk','20/05/2011');
INSERT INTO #Employee VALUES ('Sagar',25000,'Network Engineer','25/01/2011');
One of Basic query
(1) How can we find the highest salary in a table?
Answer:The aggregate function SQL MAX() is used to find the maximum
value or highest value of a certain column or expression. This function is useful to
determine the largest of all selected values of a column.
SELECT MAX(Empsal) as salary FROM #Employee
(2) SQL Query to find second highest salary of Employee.
Answer: There are many ways to find second highest salary of Employee in SQL,
you can either use SQL Join or Sub query to solve this problem. Here is SQL query using
SELECT MAX(Empsal) as Salary FROM #Employee
where EmpSal not in(SELECT MAX(Empsal) FROM #Employee)
(3) Query to find Max Salary from each department.
Answer: For this use Group By clause is used for grouping the records of the database
table(s).This clause creates a single row for each group and this process is called aggregation.
To use group by clause we have to use at least one aggregate function in Select statement.
We can use group by clause without where clause.
SELECT DEPARTMENT, MAX(empsal) as salary FROM #Employee GROUP BY
(4) Write an SQL Query find number of employees whose DOJ is
between 01/01/2011 to 31/12/2012.
Answer: Use BETWEEN operator selects values within a range.
The values can be numbers, text, or dates.
SELECT COUNT(*) from #Employee WHERE JOINING_DATE BETWEEN '01/01/2011'
(5) Write an SQL Query find number of employees according to
Department whose DOJ is between 01/01/2011 to 31/12/2012.
SELECT COUNT(*) as total,DEPARTMENT from #Employee
WHERE JOINING_DATE BETWEEN'01/01/2011' AND '31/12/2012'
GROUP BY DEPARTMENT
(6) Write an SQL Query to find employee whose Salary is equal or
greater than 30000
SELECT EmpName FROM #Employee WHERE EmpSal >=30000
(7) SQL Query to find name of employee whose name Start with ‘K’
Answer: The LIKE operator is used to search for a specified pattern in a column.
SELECT * FROM #Employee WHERE EmpName like 'k%'
“I HOPE THIS WILL HELP YOU”
Sunday, 6 April 2014
Posted By:Krishna Patil | At:02:30
Posted By:Krishna Patil | At:02:13
Here I will explain how to merge cells in asp.net grid view Footer as per requirement.