Monday 15 April 2013

SQL Server Queries for Practice


Written By:- Isha Malhotra

Our Website:-Tech Altum

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







2.       Country
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