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