Microsoft SQL Server Level 2 Excercises

  Dear Readers,Here i have provided the Level 2 Exercises of Microsoft SQL Server.In my previous article i have provided Level 1 Exercises of Microsoft SQL Server.Please go through the exercises and practice well.If you have any queries on the same.Please do comment on the same.I have added few interview questions in the my previous articles along with the specific topics which are related to the SQL Server.

 

1.Can we apply a joins without creating the table

2.Justify the below query result is 1.
Whether it is correct are not
Select B.A from(Select 1 A)A JOIN (Select 1 A,2 B)B ON A.A=B.A

3.Create a table with the below structure
create table tbl_T(Team VARCHAR(100))

INSERT INTO tbl_T VALUES('India')
INSERT INTO tbl_T VALUES('Australia')
INSERT INTO tbl_T VALUES('Srilanka')
INSERT INTO tbl_T VALUES('England')
INSERT INTO tbl_T VALUES('Pakistan')

Implement a query Each team should play match with other team at least once

4.Implement a sql query to accept a input from the user and print the data in a separate line on a character wise.
For. E.G: Sql Server
O/p:S
q
l

5.Implement a sql function to split the data into a multiple rows
For. E.G: 'A,B,C'
O/P:
S.No Data
1       A
2       B
3       C    

6. Create a two tables  below
tbl_Emp(ID INT IDENTITY,Name VARCHAR(100),DeptId INT)
tbl_Dept(ID INT IDENTITY,Name VARCHAR(100),Location VARCHAR(100))
there are 10 records in the tbl_Emp and 5 records in the tbl_Dept how many records would be displayed from the below query
Select * From tbl_Emp, tbl_Dept

7.Implement a tables as below.Implment a query to fetch the records which are not there in the tbl_B without using not keyword
create table tbl_A(A INT)
create table tbl_B(B INT)

INSERT INTO tbl_A(A) VALUES(1)
INSERT INTO tbl_A(A) VALUES(2)
INSERT INTO tbl_A(A) VALUES(3)
INSERT INTO tbl_A(A) VALUES(4)
INSERT INTO tbl_A(A) VALUES(NULL)
INSERT INTO tbl_A(A) VALUES(NULL)


INSERT INTO tbl_B(B) VALUES(1)
INSERT INTO tbl_B(B) VALUES(2)
INSERT INTO tbl_B(B) VALUES(2)
INSERT INTO tbl_B(B) VALUES(5)
INSERT INTO tbl_B(B) VALUES(NULL)


8.Create a table as below and insert some sample records into those

tbl_Emp(ID INT IDENTITY,Name VARCHAR(100),DeptId INT,MgrId INT)
tbl_Dept(ID INT IDENTITY,Name VARCHAR(100),Location VARCHAR(100))
there are 10 records in the tbl_Emp and 5 records in the tbl_Dept how many records would be

I.    Implement a query to find the employees whose department id is belongs to tbl_Dept table
II.    Implement a query to display the employees using left join,right join and full outer join in sql server
III.    Implement a query to apply self join and cartesian join of the table
IV.    Implement a query to find the manager name of every individual employee
V.    Implement a query to find the employees information who doesn't have any departmentid

9.Display all the employee names using comma separated from tbl_Emp table
For E.G:Microsoft,Sathya,Sundar






10.Implement a sql query to find the duplicate (which means whose email id is repeated more than once) email id in the given table
tbl_Emp(ID INT IDENTITY,Name VARCHAR(100),EmailId VARCHAR(100),DeptId INT,MgrId INT)


11.Implement a below sql queries using below table
create table tbl_Employee(Id INT Identity,username varchar(100) primary key,DOJ DATETIME,sal decimal(18,3),deptid int,Location VARCHAR(100),startTime DATETIME,EndTime DATETIME)

I.    Implement a sql query to find the 3rd highest salary using correlated query
II.    Implement a sql query to find the 5th highest salary using rank functions query
III.    Implement a sql query to find the 7th highest salary using CTE
IV.    Implement a sql query to find the employees whose DOJ is within the year of 2022
V.    Implement a sql query whose salary is greater than the company's average salary
VI.    Implement a sql query to remove the primary key from the above table
VII.    Implement a sql query to find the department id's whose count is greater than > 1
VIII.    Implement a sql query to update the starttime of the current time to 30 mins and extends the end time to 60 mins
IX.    Implement a sql query that the employee doesn't belongs to the any department

12.Implement a Sql Program to generate a days in between two claendar days.

E.G: 01/01/2022 (dd/mm/yyyy) to 15/01/2022
O/p:01/01/2022
02/01/2022
....
15/01/2022

13.Implement a Sql Program to display the monday and wednesday dates in between two dates

14. Implement a Sql Program for the below scenarios
create table tbl_Students(User_Id INT Identity,username varchar(100) primary key)
create table tbl_training_details(User_training_Id INT Identity,User_Id INT,Training_Id INT,Training_Date DATETIME)

I.    Implement a sql query to find the students who are attended the classes more than 1 time in a day
II.    Implement a sql query to find the students who are not attended the at least single classes in a day
III.    Implement a sql query to find the latest user_training_id of a particular student
IV.    Implement a sql query to find the total number of sessions on the date wise
V.    Implement a sql query to find the no session days dates

15.Solve the below queries
I.    Can we insert a values into a identity column justify with your answer
II.    What is the basic  use case of CTE
III.    What is the output of the below snippet     select abs(-20)
IV.    What is the output of the below snippet     select abs(20.786)
V.    Implement a query to find the difference of two dates
VI.    What is the difference between Rank() and Dense_Rank()
VII.    What is the difference between Union and UnionAll
VIII.    What is the difference between varchar and nvarchar
IX.    What is the difference between single row and multi row functions in sql server
X.    What is the output of the below query
Select username from tbl_users order by 2 desc
XI.    What is the output of the below query
Select sum(1) from tbl_users
Select sum(2) from tbl_users
Select sum(3) from tbl_users
Select sum(4) from tbl_users



16.Implement a sql query to print the opposite values in the given columns
Create table tbl_Data(Col1 varchar(10),Col2 Varchar(10))
Col1    Col2
1    0
0    1
0    0
O/p: should be like below
Col1    Col2
0    1
1    0
1    1



17. When we will use Where Clause and When we will use Having Clause implement a example using where and having clause

18.What is the use of group by clause implement a example using group by clause
 

19.Assume below table has a 3 records what is the output of below code
BEGIN TRAN
TRUNCATE TABLE tbl_Student
ROLLBACK
SELECT * FROM tbl_Student





20. Implement a below table
create table tbl_Color(ID INT IDENTITY,C1 VARCHAR(10),C2 VARCHAR(10),C3 VARCHAR(10))
INSERT INTO tbl_Color(C1,C2,C3) VALUES('Red','Yellow','Blue')
INSERT INTO tbl_Color(C1,C2,C3) VALUES(Null,'Red','Green')
INSERT INTO tbl_Color(C1,C2,C3) VALUES('Yellow',Null,'Violet')
I.    Implement a sql query to find the Yellow colour in the given table
II.    Implement a sql query to find the yellow colour in the given table without using or keyword
III.    Implement a sql query whose colour is Null in the given table
IV.    Implement a sql query whose colour has a value in all the three columns
V.    Implement a sql query whose color is repeated more than once in any of the particular column
 

21.Implement a sql query to get the latest id from the table without using max function

22.Solve the below queries
I.    What is the use of Scope_Identity() when it can be used
II.    Name 5 global variables / functions with examples
III.    What is the difference between in and exists
IV.    Implement  a query with Fetch next keyword
V.    Suppose if tables has 5 records which has identity column now the client wants to insert id as a 9 how can we achieve this type of scenario
VI.    List out the aggregate functions with examples

23.Implement a Sql query to generate the invoice number using below table
InvoiceNo    Description    Amount    Invoice GenerateDate
PNV032022_1001    Website development    50,000/-    03/01/2022
PNV032022_1002    Application Development    1,00,000/-    03/02/2022

Here in the invoiceNo logic shoud be predefined prefix PNV next extension with mm(Month)yy(Year)

24.Implement a Sql query to give the full number in words
For E.G: If user input is 10,500/-
O/p: Ten Thousand Five Hundred Rupees Only

25.Implement a Sql query based on the below structure
CREATE TABLE tbl_Employee(ID INT IDENTITY,Name VARCHAR(100),ReferredBy VARCHAR(100),ReferredOn DATETIME)

INSERT INTO tbl_Employee(Name,ReferredBy,ReferredOn) VALUES ('Sai',NULL,'01/01/2021')
INSERT INTO tbl_Employee(Name,ReferredBy,ReferredOn) VALUES ('Kiran','Vikram','05/01/2021')
INSERT INTO tbl_Employee(Name,ReferredBy,ReferredOn) VALUES ('Dinesh','Vijay','01/01/2022')
INSERT INTO tbl_Employee(Name,ReferredBy,ReferredOn) VALUES ('Pranay','Vikram','02/01/2022')
INSERT INTO tbl_Employee(Name,ReferredBy,ReferredOn) VALUES ('Srinivas','Vikram','03/01/2022')
INSERT INTO tbl_Employee(Name,ReferredBy,ReferredOn) VALUES ('Harish','Vijay','03/02/2022')

I.    Display the output in the below way
ReferredBy    NoOfEmployees
Vikram    3
Vijay    2
II.    Implement a sql query to find the month wise referral information from Januay2022 to Dec'2022
ReferredBy    Month-Year    NoOfEmployees
Vijay    01-2022    1
Vikram    02-2022    1
Vikram    03-2022    1
Vijay    03-2022    1
III.    Implement  a sql query to display the employee who don't have referred by data

26.Implement a Sql query to display the data with the below values
S.No    Amount
1    10.78
2    1513.786
3    64568.980

O/p:
S.No    Amount    Rs.    Paisa
1    10.78    10    78
2    1513.786    1513    786
3    64568.980    64568    980


27.Implement a sql query to round off the amount whose decimal value is greater than >.50 to next number if it is less than <.50 then it should be  current number
For E.G:        O/P
23.75 is equal to 24
18.48 is equal to 18

28.Implement a sql query to add number 1 whose existing value is < 5 and 2 whose existing value is greater than 10 

29.Implement a sql query to display the -ve marks from the marks table

30.Implement a sql query to display the calendar of the year with date,day and juliet number
E.G:
S.no    Date    Day    Juliet Day
1    01-01-2022    Saturday    1
2    02-01-2022    Sunday    2
3    03-01-2022    Monday    3