Let’s create Employee
table and insert data as shown below
to perform basic sql query operation.
CREATE TABLE #Employee
(
EmpID INT Identity,
Empname Varchar(100),
EmpSal Decimal (10,2),
DEPARTMENT varchar(100),
JOINING_DATE varchar(100)
)
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
Sub query.
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
DEPARTMENT
(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'
AND '31/12/2012'
(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”
|
