MS SQL Server

Assignment # 1

Topic: Tables, Constraint, Identity, Select Time: 2 Hr

A small IT firm designing business software for its clients wants to store and manage its data. It has
identified following entities for which, it will maintain data.

1. Clients
2. Employees
3. Departments
4. Projects
5. EmpProjectTasks *

The tables below describe attributes for each entity. Keep the table name same.

Clients
Attribute Name Attribute Type Constraint Remarks
Client_ID Integer Primary Key
Cname STRING(30) Not Null
Address STRING(30)
Email STRING(30) Unique
Phone STRING(10)
Business STRING(20) Not Null Business type of client
like Manufacturer,
Reseller, Consultant,
Professional etc.

Employees
Attribute Name Attribute Type Constraint Remarks
Empno Integer Primary Key
Ename STRING(20) Not Null
Job STRING(15)
Salary Decimal Must be positive Use CHECK constraint
to ensure salary is > 0
Deptno Integer Foreign Key Deptno as per
Departments table

Departments
Attribute Name Attribute Type Constraint Remarks
Deptno Integer Primary Key
Dname STRING(15) Not Null
Loc STRING(20)

Projects
 Attribute Name Attribute Type Constraint Remarks
Project_ID Integer Primary Key
Descr STRING(30) Not Null Description of project
like ‘Accounting’ ,
‘Inventory’, ‘Payroll’
etc.
Start_Date DATE Start date of project
Planned_End_Date DATE Planned End date of
Project
Actual_End_date DATE Must be later than Actual End date of
Planned_End_Date project (Use CHECK
constraint)
Budget Decimal Must be positive Use CHECK constraint
to ensure budget is > 0
Client_ID INTEGER Foreign Key Client ID from Clients
Table

EmpProjectTasks
Attribute Name Attribute Type Constraint Remarks
Project_ID Integer Primary Key, Foreign Key Composite primary key
Empno INTEGER Primary Key, Foreign Key and foreign keys
referring Projects and
Employees table
Start_Date DATE Start date when
employee begins task
on this project
End_Date DATE End date when
employee finishes task
on this project
Task STRING(25) Not Null Task performed
by employee like
designing, coding,
review, testing etc.
Status STRING(15) Not Null Status of task like
‘in progress’,
‘complete’,’cancelled’

Clients
Client ID Cname AddresS Email Phone Business

1001 ACME Utilities Noida contact@acmeutil.com 9567880032 Manufacturin

g
1002 Trackon Consultants Mumbai consult@trackon.com 8734210090 Consultant

1003 MoneySaver Kolkata save@moneysaver.com 7799886655 Reseller

Distributors
1004 Lawful Corp Chennai justice@lawful.com 9210342219 Professional

Employees
 Empno Ename Job Salary Dept No

7001 Sandeep Analyst 25000 10

7002 Rajesh Designer 30000 10

7003 Madhav Developer 40000 20

7004 Manoj Developer 40000 20

7005 Abhay Designer 35000 10

7006 Uma Tester 30000 30

7007 Gita Tech.Writer 30000 40

7008 Priya Tester 35000 30

7009 Nutan Developer 450 20

00
7010 Smita Analyst 200 10
00
7011 Anand Project 650 10
Mgr 00

Departments

Deptno Dname Loc

10 Design Pune
20 Development Pune
30 Testing Mumbai
40 Document Mumbai

Projects

Project_I Descr Start_Date Planned_End_Date Actual_End_date Budget Client_ID

D
401 Inventory 01-Apr-2011 01-Oct-2011 31-Oct-2011 150000 1001
402 Accounting 01-Aug-2011 01-Jan-2012 500000 1002
403 Payroll 01-Oct-2011 31-Dec-2011 75000 1003
404 Contact Mgmt 01-Nov-2011 31-Dec-2011 50000 1004

EmpProjectTasks

Project_ID Empno Start_Date End_DaTe Task Status

 401 7001 01-Apr-2011 20-Apr- System Analysis Completed
2011
401 7002 21-Apr-2011 30-May- System Design Completed
2011
401 7003 01-Jun-2011 15-Jul-2011 Coding Completed

401 7004 18-Jul-2011 01-Sep- Coding Completed

2011
401 7006 03-Sep-2011 15-Sep- Testing Completed
2011
401 7009 18-Sep-2011 05-Oct- Code Change Completed
2011
401 7008 06-Oct-2011 16-Oct- Testing Completed
2011
401 7007 06-Oct-2011 22-Oct- Documentation Completed
2011
401 7011 22-Oct-2011 31-Oct- Sign off Completed
2011
402 7010 01-Aug-2011 20-Aug- System Analysis Completed
2011
402 7002 22-Aug-2011 30-Sep- System Design Completed
2011
402 7004 01-Oct-2011 Coding In Progress

SQL Assignment # 1 – Questions

1. Create the Clients table using the information provided above. Once tables are ready, fill in the given
data of this table.
2. Create the Departments table using the information provided above. Once tables are ready, fill in the
 given data of this table.
3. Create the Employees table using the information provided above. Once tables are ready, fill in the
given data of this table.
4. Create the Projects table using the information provided above. Once tables are ready, fill in the given
data of this table.
5. Create the EmpProjectTasks table using the information provided above. Once tables are ready, fill in
the given data of this table.
6. Display customer details with business as ‘Consultant’.
7. Display employee details who are not ‘Developers’.
8. Display project details with budget > 100000.
9. Display details of project that are already finished.
10. Display employee names beginning with ‘M’.
11. Display employee names ending with ‘a’.
12. Display the task that is ‘In Progress’.
13. Display details of departments located in Pune.
14. Display employee name and salary in descending order of salary.
15. Display tasks in ascending order of end date.
16. Display distinct jobs from Employees table.
17. Display employee name, salary and bonus calculated as 25% of salary.

MS SQL Server
Assignment # 1
Topic: Assignment 1 Submitted By:Nikhil

1.Create the Clients table using the information provided above. Once tables are ready, fill in the given data of
this table.

ANS - CREATE TABLE acroschema_17.CLIENTS(

CLIENT_ID int PRIMARY KEY,

CNAME VARCHAR(40) NOT NULL,

ADDRESS VARCHAR(30),

EMAIL VARCHAR(30) UNIQUE,

PHONE VARCHAR(10),

BUSINESS VARCHAR(20) NOT NULL

);

Insert into acroschema_17.CLIENTS (CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)

values (1001,'ACME Utilities','Noida','contact@acmeutil.com',9567880032,

'Man ufacturing');

Insert into acroschema_17.CLIENTS (CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)

values (1002,'Trackon Consultants','Mumbai','consult@trackon.com',8734210090, 'Consultant');

Insert into acroschema_17.CLIENTS (CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)

values (1003,'MoneySaver Distributors','Bangalore','save@moneysaver.com',7799886655,'Reseller');

Insert into acroschema_17.CLIENTS (CLIENT_ID,CNAME,ADDRESS,EMAIL,PHONE,BUSINESS)

values (1004,'Lawful Corp','Chennai','justice@lawful.com',9210342219,'Professi onal');

2.Create the Departments table using the information provided above. Once tables are ready, fill in the given data
of this table.

ANS -
CREATE TABLE acroschema_17.DEPARTMENTS(

DEPTNO int PRIMARY KEY,

DNAME VARCHAR(15) NOT NULL,

LOC VARCHAR(20));

Insert into acroschema_17.DEPARTMENTS (DEPTNO,DNAME,LOC) values (10,'Design','Pune');

Insert into acroschema_17.DEPARTMENTS (DEPTNO,DNAME,LOC) values (20,'Development','Pune');

Insert into acroschema_17.DEPARTMENTS (DEPTNO,DNAME,LOC) values (30,'Testing','Mumbai');

Insert into acroschema_17.DEPARTMENTS (DEPTNO,DNAME,LOC) values (40,'Document','Mumbai');

3.Create the Employees table using the information provided above. Once tables are ready, fill in the given data of
this table

ANS - CREATE TABLE acroschema_17.EMPLOYEES(

EMPNO INT PRIMARY KEY,

ENAME VARCHAR(20) NOT NULL,

JOB VARCHAR(15),

SALARY INT CHECK (SALARY > 0),

DEPTNO INT );

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7001,'Sandeep','Analyst',25000,10);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7002,'Rajesh','Designer',30000,10);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7003,'Madhav','Developer',40000,20);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7004,'Manoj','Developer',40000,20);
Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7005,'Abhay','Designer',35000,10);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values (7006,'Uma','Tester',30000,30);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values (7007,'Gita','Tech. Writer',30000,40);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values (7008,'Priya','Tester',35000,30);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7009,'Nutan','Developer',45000,20);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values (7010,'Smita','Analyst',20000,10);

Insert into acroschema_17.EMPLOYEES (EMPNO,ENAME,JOB,SALARY,DEPTNO)

values(7011,'Anand','Project Mgr',65000,10);

4.Create the Projects table using the information provided above. Once tables are ready, fill in the given data of
this table.

ANS - CREATE TABLE acroschema_17.PROJECTS(

PROJECT_ID int PRIMARY KEY,

DESCR VARCHAR(30) NOT NULL,

START_DATE DATE,
PLANNED_END_DATE DATE,

ACTUAL_END_DATE DATE,

BUDGET Decimal(18,2) CHECK(BUDGET > 0),

CLIENT_ID int );

Insert into acroschema_17.PROJECTS

(PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID)

values(401,'Inventory','01-APR-2011','01-OCT-2011','31-OCT- 11',150000,1001);

Insert into acroschema_17.PROJECTS

(PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID)

values(402,'Accounting','01-AUG-2011','01-JAN-2012',null,500000,1002);

Insert into acroschema_17.PROJECTS

(PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID)

values(403,'Payroll','01-OCT-2011','31-DEC-2011',null,75000,1003);

Insert into acroschema_17.PROJECTS

(PROJECT_ID,DESCR,START_DATE,PLANNED_END_DATE,ACTUAL_END_DATE,BUDGET,CLIENT_ID)

values (404,'Contact Mgmt','01-NOV-2011','31-DEC- 11',null,50000,1004);

5.Create the EmpProjectTasks table using the information provided above. Once tables are ready, fill in the given
data of this table.

ANS - CREATE TABLE acroschema_17.EMPPROJECTTASKS( PROJECT_ID int ,

EMPNO int ,

START_DATE DATE,

END_DATE DATE,

TASK VARCHAR(25) NOT NULL,

STATUS VARCHAR(15) NOT NULL,

PRIMARY KEY(PROJECT_ID,EMPNO));

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7001,'01-APR-2011','20-APR-2011','System Analysis','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7002,'21-APR-2011','30-MAY-2011','System Design','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7003,'01-JUN-2011','15-JUL-2011','Coding','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7004,'18-JUL-2011','01-SEP-2011','Coding','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7006,'03-SEP-2011','15-SEP-2011','Testing','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7009,'18-SEP-2011','05-OCT-2011','Code Change','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7008,'06-OCT-2011','16-OCT-2011','Testing','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7007,'06-OCT-2011','22-OCT-2011','Documentation','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (401,7011,'22-OCT-2011','31-OCT-2011','Sign off','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (402,7010,'01-AUG-2011','20-AUG-2011','System Analysis','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (402,7002,'22-AUG-2011','30-SEP-2011','System Design','Completed');

Insert into acroschema_17.EMPPROJECTTASKS (PROJECT_ID,EMPNO,START_DATE,END_DATE,TASK,STATUS)

values (402,7004,'01-OCT-2011',null,'Coding','In Progress');

6.Display customer details with business as ‘Consultant’.

Ans - select * from acroschema_17.CLIENTS where BUSINESS = 'Consultant'

7.Display employee details who are not ‘Developers’.

Ans - SELECT * FROM acroschema_17.EMPLOYEES WHERE NOT JOB='Developer'

8.Display project details with budget > 100000.
Ans - SELECT * FROM acroschema_17.PROJECTS WHERE BUDGET > 100000

9.Display details of project that are already finished.

Ans - select * from acroschema_17.PROJECTS where ACTUAL_END_DATE IS NOT NULL

10.Display employee names beginning with ‘M’.

Ans - SELECT * FROM acroschema_17.EMPLOYEES WHERE ENAME LIKE 'M%'

11.Display employee names ending with ‘a’.

Ans - SELECT * FROM acroschema_17.EMPLOYEES WHERE ENAME LIKE '%a'

12.Display the task that is ‘In Progress’.

Ans - SELECT * FROM acroschema_17.EMPPROJECTTASKS WHERE STATUS = 'In Progress'

13.Display details of departments located in Pune.

Ans - select * from acroschema_17.DEPARTMENTS where LOC = 'Pune'

14.Display employee name and salary in descending order of salary.

Ans- SELECT ENAME,SALARY FROM acroschema_17.EMPLOYEES ORDER BY SALARY DESC

15.Display tasks in ascending order of end date.

Ans - select TASK from acroschema_17.EMPPROJECTTASKS ORDER BY END_DATE ASC
16.Display distinct jobs from Employees table.
Ans - SELECT DISTINCT JOB FROM acroschema_17.EMPLOYEES

17.Display employee name, salary and bonus calculated as 25% of salary.

Ans - SELECT ENAME,SALARY,SALARY * 0.25 FROM acroschema_17.EMPLOYEES