1. EMPLOYEE
--CREATING TABLE
CREATE TABLE Employee (EID INT PRIMARY KEY, EName VARCHAR(10), Salary INT)
--INSERTING RECORDS
INSERT INTO Employee (EID, EName, Salary)
VALUES (1, 'John', 20000)
,(2, 'Rob', 2000)
,(3, 'Bob', 10000)
,(4, 'Robert', 20300)
,(5, 'Bruce', 40000)
,(6, 'Williams', 5000)
,(7, 'Megan', 900)
,(8, 'Steven', 9000)
,(9, 'George', 12500)
,(10, 'Alex', 14000)
A.) List employees who are earning more than 10000
SELECT EName
FROM Employee
WHERE Salary > 10000
B.) List employees whose name starts with ‘R’
SELECT EName
FROM Employee
WHERE EName LIKE 'R%'
C.) List employees whose length of the name is more than 4
SELECT EName
FROM Employee
WHERE LEN(EName) > 4
D.) List employees who are earning between 8000 and 20000 and whose name starts with G or A
SELECT EName
FROM Employee
WHERE Salary BETWEEN 8000 AND 20000
AND (EName LIKE 'G%' OR EName LIKE 'A%')
E.) List employees who born in 1985
SELECT EName
FROM Employee
WHERE DATEPART(YYYY,DOB) = 1985
F.) List employees who born in March moth
-- Option : 1
SELECT EName
FROM Employee
WHERE DATEPART(MM,DOB) = 3 -- MARCH
-- Option : 2
SELECT EName
FROM Employee
WHERE DATENAME(MM,DOB) = 'March'
G.) List employees who born after 1990 or earning more than 20000
SELECT EName
FROM Employee
WHERE DOB > '1990-12-31'
OR Salary > 20000
-------------------------------------------------------------------------------------------
2. EMPLOYEE - PHONE
--CREATING TABLE
CREATE TABLE Phones (PhoneID INT PRIMARY KEY IDENTITY, EID INT, Phone VARCHAR(10) )
-- INSERTING RECORDS
INSERT INTO Phones (EID, Phone)
VALUES
(1, '123456789')
,(2,'4597861356')
,(3,'6556454656')
,(2,'7878979899')
,(6,'7845454656')
,(1,'1235465654')
,(3,'5446556656')
,(4,'1215455465')
,(4,'7894521656')
,(7,'5464212664')
,(5,'6564646565')
,(5,'7854554546')
,(4,'1244544794')
A.) List of employees who has at least one phone
-- Option : 1
SELECT EName
FROM Employee
WHERE EID IN (SELECT EID FROM Phones)
-- Option : 2
SELECT EName
FROM Employee E
WHERE EXISTS (SELECT 1 FROM Phones P WHERE E.EID = P.EID)
-- Option : 3
SELECT DISTINCT EName
FROM Employee E
INNER JOIN Phones P ON E.EID = P.EID
B.) List of employees who does not have phone
-- Option : 1
SELECT EName
FROM Employee
WHERE EID NOT IN (SELECT EID FROM Phones)
-- Option : 2
SELECT EName
FROM Employee E
WHERE NOT EXISTS (SELECT 1 FROM Phones P WHERE E.EID = P.EID)
-- Option : 3
SELECT EName
FROM Employee E
LEFT JOIN Phones P ON E.EID = P.EID
WHERE P.EID IS NULL
C.) List of total number of phones per employee. If employee does not have phone then display 0
-- Option : 1
SELECT E.EID, EName, ISNULL(TotalPhones,0) TotalPhones
FROM Employee E
LEFT JOIN
(
SELECT EID, COUNT(Phone) TotalPhones
FROM Phones P
GROUP BY EID
) X ON E.EID = X.EID
-- Option : 2
SELECT E.EID, EName, ISNULL(COUNT(Phone),0) TotalPhones
FROM Employee E
LEFT JOIN Phones P ON E.EID = P.EID
GROUP BY E.EID, EName
D.) List employees (who born after 1985) with total number of phones. If employee does not have phone then it should display 0
SELECT E.EID, EName, DOB, ISNULL(COUNT(Phone),0) TotalPhones
FROM Employee E
LEFT JOIN Phones P ON E.EID = P.EID
WHERE DOB > '1985-12-31'
GROUP BY E.EID, EName, DOB
-----------------------------------------------------------------------------------------------------
3. CRICKETER - MANAGER
-- Creating table
CREATE TABLE Cricker (CID INT IDENTITY(1,1), CName VARCHAR(10), ManagerID INT)
-- Inserting records
INSERT INTO Cricker (CName, ManagerID)
VALUES ('Dhoni',NULL)
,('Sehwag',1)
,('Raina',1)
,('Virat',2)
,('Yuvi',3)
,('Yusuf',2)
,('Zaheer',1)
,('Bhajji',7)
,('Nehra',7)
,('Ashwin',8)
,('Vijay',2)
A.) List of cricketers along with their respective manager. If cricketer does not have manager then ‘No Manager’ should be displayed
SELECT C.CName, ISNULL(M.CName,'No Manager') ManagerName
FROM Cricker C
LEFT JOIN Cricker M ON C.ManagerID = M.CID
B.) List of cricketers along with total number of direct subordinates. If no subordinate then 0 should be displayed
SELECT C.CName, ISNULL(TotalSubordinates ,0) TotalSubordinates
FROM Cricker C
LEFT JOIN (
SELECT ManagerID, COUNT(*) TotalSubordinates
FROM Cricker
GROUP BY ManagerID
) X ON C.CID = X.ManagerID
C.) List of cricketer(s) who has/have highest number of direct subordinates
CREATE VIEW vwCricketerSubordinates
AS
SELECT ManagerID , COUNT(*) TotalSubordinates
FROM Cricker
GROUP BY ManagerID
Go
SELECT CName
FROM vwCricketerSubordinates V
INNER JOIN Cricker C ON V.ManagerID = C.CID
WHERE TotalSubordinates = (
SELECT MAX(TotalSubordinates)
FROM vwCricketerSubordinates
)
------------------------------------------------------------
GET THE FIRST DAY OF THE MONTH
SELECT DATENAME(DW, DATEADD(DD,-(DATEPART(DD,GETDATE()))+1, GETDATE()))
GET COUNTRIES IN SORTED ORDER WITH INDIA AS FIRST COUNTRY
SELECT * FROM COUNTRY ORDER BY CASE WHEN CountryName = 'India' THEN '-1' ELSE CountryName END
CHANGE GENDER (REPLACE "Male" WITH "Female" AND VICE VERSA)
UPDATE UserProfile SET Gender = CASE WHEN 'Male' THEN 'Female' WHEN 'Female' THEN 'Male' ELSE NULL END
GIVE THE RANK BASED ON TOTAL MARK
Using Rank Function
Using Co-related Subquery
Now, give the rank in alphabetical order in case of same TotalMarks
REMOVE DUPLICATES FROM A TABLE
CREATE TABLE DuplicateCheck (a int, b varchar(10))
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')
-- APPROACH 1 (USING Identity COLUMN, co-related query)
ALTER TABLE DuplicateCheck ADD IdentityColumn INT IDENTITY
SELECT * FROM DuplicateCheck
DELETE DC
FROM DuplicateCheck DC
WHERE NOT EXISTS
(
SELECT 1
FROM (SELECT MIN(D.IdentityColumn) MinIdentityColumn,A,B FROM DuplicateCheck D GROUP BY A,B) X
WHERE DC.IdentityColumn = X.MinIdentityColumn
)
ALTER TABLE DuplicateCheck DROP COLUMN IdentityColumn
SELECT * FROM DuplicateCheck
-- APPROACH 2 (USING TEMP table)
TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')
CREATE TABLE #TEMP (A INT, B VARCHAR(10))
INSERT INTO #TEMP (A,B) SELECT DISTINCT A,B FROM DuplicateCheck
TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (A,B) SELECT DISTINCT A,B FROM #TEMP
DROP TABLE #TEMP
SELECT * FROM DuplicateCheck
-- APPROACH 3 (USING Identity column, SELF JOIN)
TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')
ALTER TABLE DuplicateCheck ADD IdentityColumn INT IDENTITY
SELECT * FROM DuplicateCheck
DELETE DCMain
FROM DuplicateCheck DCMain
INNER JOIN DuplicateCheck DCRunning
ON DCMain.a = DCRunning.a
AND DCMain.b = DCRunning.b
AND DCMain.IdentityColumn > DCRunning.IdentityColumn
ALTER TABLE DuplicateCheck DROP COLUMN IdentityColumn
SELECT * FROM DuplicateCheck
-- APPROACH 4 (USING CTE)
TRUNCATE TABLE DuplicateCheck
INSERT INTO DuplicateCheck (a,b) VALUES
(1,'a'),(1,'a'),(1,'a'),
(2,'b'),(2,'b'),
(3,'c'),(3,'c'),
(4,'d')
WITH del (A,B,DuplicateCount)
AS
(
SELECT A,B,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY A) DuplicateCount FROM DuplicateCheck
)
DELETE FROM del WHERE DuplicateCount > 1
SELECT * FROM DuplicateCheck
FIND FACTORIAL
-- APPROACH 1 (Recursive Function)
CREATE FUNCTION Facto (@number INT)
RETURNS DECIMAL(38,0)
BEGIN
DECLARE @result DECIMAL(38,0)
SET @result = @number
IF @number = 0
SET @result = 1
ELSE
SET @result = @result * dbo.Facto(@number-1)
RETURN @result
END
select dbo.Facto(-4)
-- APPROACH 2 (CTE)
CREATE FUNCTION FactCTE (@number INT)
RETURNS DECIMAL(38,0)
BEGIN
DECLARE @result DECIMAL(38,0);
WITH CTE AS
(
SELECT 0 number, CONVERT(DECIMAL(38,0),1) FactResult
UNION ALL
SELECT number+1, FactResult * (number+1) FROM CTE WHERE number < @number
)
SELECT @result = FactResult FROM CTE
RETURN @result
END
select dbo.FactCTE(4)
