45 SQL Server Queries for practice(21-45)- Part 2
Create the Following Tables
1.
EmpReg
Column Name
|
Data type
|
Is Null
|
Constraints
|
EmpId
|
Int
|
No
|
Primary key
|
EmpName
|
Varchar(100)
|
No
|
it can not be blank
|
Gender
|
Varchar(10)
|
No
|
It can not be blank
|
DeptId
|
Int
|
No
|
Depends on the table
dept with column Id
|
Phone
|
Varchar(100)
|
Yes
|
|
Mobile
|
Varchar(100)
|
No
|
Should be unique
|
Email
|
Varchar(100)
|
no
|
Should be unique
|
Country
|
Int
|
No
|
Depends on table
country with column cid
|
state
|
Int
|
no
|
Depends on table
country with column sid
|
Salary
|
Varchar(100)
|
No
|
Should be between
5000 to 15000
|
DateOfJoining
|
datetime
|
no
|
|
Column Name
|
Data type
|
Is Null
|
Constraints
|
Cid
|
Int
|
No
|
Primary key
|
CName
|
Varchar(100)
|
No
|
Country name should
not be repeated
|
3.
State
Column Name
|
Data type
|
Is Null
|
Constraints
|
Sid
|
Int
|
No
|
Primary key
|
Cid
|
int
|
No
|
Depends on table
country with id cid
|
SName
|
Varchar(100)
|
No
|
Should not be
repeated
|
4.
EmpAttendence
Column Name
|
Data type
|
Is Null
|
Constraints
|
AttendenceId
|
Int
|
No
|
Primary key
|
Emp_id
|
int
|
No
|
Depends on the table
EmpReg with EmpId
|
DateOfAttendence
|
Date
|
No
|
|
InTime
|
Time
|
Yes
|
|
OutTime
|
Time
|
No
|
|
IsPresent
|
Char(3)
|
No
|
It takes only two
values that is ‘yes’ and ‘No’
|
5.
Dept
Column Name
|
Data type
|
Is Null
|
Constraints
|
Id
|
Int
|
No
|
Primary key
|
Dept_name
|
Varchar(100)
|
No
|
Department name
should not be repeated
|
NoOfPaidLeave
|
Int
|
no
|
|
DeptHeadid
|
int
|
no
|
Depends on the empReg
table with empid column
|
Note:- while creating the each table all
constraints will be added after adding all columns.
Queries
For this Tables:-
Simple Queries
1.
Select the name of the
Department who is giving paid leaves more than 10.
2.
Select the state of India and
china.
3.
Select the states of all except
india and china.
4.
Select the all employee name
whose which is containing word ‘kr’.
5.
Select the name of all employee
who is taking salary more than 60000.
6.
Select the date on which any
candidate not present.
7.
Select the detail of employee
who is taking salary between 20000 to 30000.
8.
Select the all states name
which belongs to india and starting from ‘A’.
9.
Select the all states name
which either belongs to india or containing the word ‘mi’.
10.
Select the name of the employee whose 3rd
character is ‘p’.
11.
Select the total of all
salaries.
12.
Select the name of the employee
who is taking minimum salary.
13.
Select the name of the employee
who is taking highest salary.
14.
Select the average of salary.
15.
Select the total salary of each
Department.
16.
Select the count of all male
candidate.
17.
Select the count of all female
candidate.
18.
Select the count of all male
and female candidate in a single query.
Nested and Joins Quries
1.
Select the employee name with
their department name and paid leaves.
2.
Select the name of the employee
who doesn’t have any paid leave.
3.
Select the email id of each
department head with their department name.
4.
Select the name of each
department head with their attendance.
5.
Select the name of each
department head with their paid leaves
6.
Select the highest salary of
department HR.
7.
Select the minimum salary of
Department HR.
No comments:
Post a Comment