SQL QUERY
Write a query to find out the highest salary with and without using TOP?
HIGHEST SALARY USING TOP:-
SELECT DISTINCT TOP 1 [EmpSalary]
FROM EMPLOYEE
ORDER BY [EmpSalary] DESC
HIGHEST SALARY WITHOUT USING TOP:-
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [EmpSalary] DESC) AS rownumber,[EmpSalary]
FROM [dbo].[Employee]) AS A
WHERE rownumber = 1
Delete duplicate records from the table using CTE?
DELETE RECORDS FROM THE TABLE USING CTE:-
WITH EmployeesCTE as(
SELECT*, ROW_NUMBER() over (PARTITION BY [Id] ORDER BY [Id]) as RowNumber
FROM [dbo].[Employee])
DELETE FROM EmployeesCTE WHERE RowNumber>1
Write a query whose name starts with ‘a’ with and without using Like operator?
NAME STARTS WITH ‘A’ USING LIKE OPERATOR:-
SELECT * FROM Employee WHERE Name like ‘A%’
NAME STARTS WITH ‘A’ WITHOUT USING LIKE OPERATOR:-
SELECT * FROM Employee WHERE CHARINDEX (‘A’, Name) = 1
Queries on Joins -Inner Joins, Left Joins, Right Joins, Full Outer Joins, Cross Joins, and Self Join?
INNER JOIN:-
SELECT *
FROM [dbo].[Customer]
INNER JOIN [dbo].[Employee]
ON [dbo].[Customer].Id=[dbo].[Employee].Id
LEFT JOIN:-
SELECT *
FROM [dbo].[Customer]
Left JOIN [dbo].[Employee]
ON [dbo].[Customer].Id=[dbo].[Employee].Id
RIGHT JOIN:-
SELECT *
FROM [dbo].[Customer]
Right JOIN [dbo].[Employee]
ON [dbo].[Customer].Id=[dbo].[Employee].Id
FULL OUTER JOIN:-
SELECT *
FROM [dbo].[Customer]
Full JOIN [dbo].[Employee]
ON [dbo].[Customer].Id=[dbo].[Employee].Id
CROSS JOIN:-
Get Vaishali’s stories in your inbox
Join Medium for free to get updates from this writer.
SELECT c.[First_Name], e.[Salary]
FROM [dbo].[Customer] c
CROSS JOIN [dbo].[Employee] e
SELF JOIN:-
SELECT a.[Name], b.[Salary]
FROM [dbo].[Employee] a,[dbo].[Employee] b
How can you create an empty table from an existing table?
SELECT * Into NewTbl From [dbo].[Employee] WHERE 2=1;
OR
SELECT TOP 0 *
INTO NewTbl1
FROM [dbo].[Employee];
- This Query does not copy indexes, keys, etc.
- Generating a script, we can create an empty table from an existing table.
- First, right-click on source table Then click on Script Table → CREATE To →New Query Editor Window.
How to fetch common records from two tables?
INTERSECT:- By using Intersect we fetch the Common records only.
SELECT [Salary]
FROM [dbo].[Employee]
INTERSECT
SELECT [Salary]
FROM [dbo].[NewTbl]
INNER JOIN:- By using Inner Join we fetch common records with the duplicate records.
SELECT *
FROM [dbo].[Employee]
INNER JOIN [dbo].[NewTbl]
ON [dbo].[Employee].[Id]= [dbo].[NewTbl].[Id]
How to fetch alternate records from a table?
SELECT *
FROM (SELECT *,
ROW_NUMBER() over (order by id) as no
FROM Employee) t
WHERE (t.no % 2) = 1
What is the command used to fetch the first 5 characters of the string?
SUBSTRING(): Substring extract some characters from a string.
SELECT SUBSTRING(Name,1,5) as Name FROM [dbo].[Employee]
LEFT(): The LEFT() function extracts a number of characters from a string (starting from left).
SELECT LEFT(Name, 5) AS Name FROM [dbo].[Employee]
Update query to change male to female
UPDATE [dbo].[Detail]
SET [Gender]= CASE WHEN [Gender]=’Female’ then ‘Male’ ELSE ‘Female’ END
If you are new to database learning — SQL Server recommended is the following must-watch the video: -
