Monday 18 June 2012

Practice Question for Constraints


Written By:- Isha Malhotra (Software Developer)
Email:-malhotra.isha3388@gmail.com.
Note: -if you find this article helpful then kindly leave your comment


For Asp.net Training with C# and Sql Server Click Here



                      Practice Question for Constraints                    

Create Following Tables with constraints

1.     EmpReg
Column Name
Data type
Is Null
Constraints
EmpId
Int
No
Primary key
EmpName
Varchar(100)
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:-
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

No comments:

Post a Comment