Question

Question : Write MS-SQL Server Script for : (check for null at all times)

1) Select all ManagerIDas SupervisorNumber from table EmpJob (if managerID is null, do not include in the output)

Table name : EmpJob

Available Data :

ManagerID

553434

NULL

2134323

324343

Expected output :

Supervisor Number

553434

2134323

324343

2) Select name (attribute) the as branch(new col name) from table FoLocation (check for null. If null no data in the output)

table name : FoLocation

Available Data :

name

Toronto

Missisauga

San Fransisco

Expected Output :

Branch

Toronto

Missisauga

San Fransisco

3) Select defaultJobLevel as Sup/Mgr(new col name) from table JobCode

table Name : JobCode

Available Data :

defaultJobLevel

Associate

IT

IT

Manager

Supervisor

Manager

IT

Expected Output :

No

No

No

Yes

Yes

Yes

No

4) Select EmployeeType (attribute) as PersonType(new col name) from table EmpJob

table name : EmpJob

Available Data : (needs to map these number code to the different type of employee)

EmployeeType (attribute)

814

72250

72249

72248

72251

72248

Expected Output :

PersonType (new col name)

Agency

Consultant

Contract

Co-op

Employee

Co-op

5) Select emplstatus(attribute) as Status(new col name) from EmpJob

table name : EmpJob

available data :

emplstatus

802

802

801

800

810

811

812

Expected Output :

Active Assignment

Active Assignment

FMLA

LTD
STD
STD Paid 100

unpaid

6) Select CustomString18 (attribute) as workathome from table EmpJob

Table Name : EmpJob

Available Data :

CustomString18

NULL
NULL

500

500

NULL

Expected Output :

N

N

Y

Y

N

7) Select CostCenter as Division from EmpJob

table Name : EmpJob

Available Data : (take first 3 digits from Cost Center)

CostCenter

100907623473

200348329843

10002323243

10034343443

200543534533

Expected Output :

Division

100

200

100

100

200

8) Select name (attribute) as Territory (new col name) from table FoCostCenter

table name : FoCostCenter

available data : (separate all the name and select the third as output)

name

100.234.234324.45654.546546.12334.F

100.34543.3454353.3243.45643.43543.F

Expected output :

Territory

234324

3454353

9) Select name (attribute) as Department (new col name) from table FoCostCenter

table name : FoCostCenter

available data : (separate all the name and select the second as output)

name

100.234.234324.45654.546546.12334.F

100.34543.3454353.3243.45643.43543.F

expected output :  

Department

234

34543

10 )  Select name (attribute) as Category (new col name) from table FoCostCenter

table name : FoCostCenter

available data : (separate all the name and select the fourth as output)

name

100.234.234324.45654.546546.12334.F

100.34543.3454353.3243.45643.43543.F

expected output :  

category

45654

3243

11)  Select name (attribute) as project (new col name) from table FoCostCenter

table name : FoCostCenter

available data : (separate all the name and select the fifth as output)

name

100.234.234324.45654.546546.12334.F

100.34543.3454353.3243.45643.43543.F

expected output :  

project

546546

45643

12 ) Select name (attribute) as vendor (new col name) from table FoCostCenter

table name : FoCostCenter

available data : (separate all the name and select the sixth as output)

name

100.234.234324.45654.546546.12334.F

100.34543.3454353.3243.45643.43543.F

expected output :  

vendor

12334

43543

13 ) Select name (attribute) as EmployeeType (new col name) from table FoCostCenter

table name : FoCostCenter

available data : (separate all the name and select the seventh as output)

name

100.234.234324.45654.546546.12334.F

100.34543.3454353.3243.45643.43543.F

expected output :  

EmployeeType

F

F

Answer 1

1)

SELECT ManagerID as SupervisorNumber FROM EmpJob WHERE ManagerID IS NOT NULL;

2)

SELECT name AS branch FROM FoLocation WHERE attribute IS NOT NULL;

3)

SELECT CASE

WHEN defaultJobLevel = 'Manager' THEN 'Yes'

WHEN defaultJobLevel = 'Supervisor' THEN 'Yes'

ELSE 'No'

END

AS "Sup/Mgr"

FROM JobCode;

4)

SELECT CASE

WHEN EmployeeType = 814 THEN 'Agency'

WHEN EmployeeType = 72250 THEN 'Consultant'

WHEN EmployeeType = 72249 THEN 'Contract'

WHEN EmployeeType = 72248 THEN 'Co-op'

WHEN EmployeeType = 72251 THEN 'Employee'

END

AS PersonType

FROM EmpJob;

NOTE: As per Chegg policy, I am allowed to answer specific number of questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.