Assignment: MS SQL

1. Provide id and last name of employees whose second last character of the last name is ‘e', ‘f', ‘n' or ‘t' in the ascending order of the last name.

There are two tables here:

Table Name: MIDTERM.EMPSUP

eid lname fname salary supervisorID deptid

101 smith john 200000.00 NULL 100

102 wang john 80000.00 101 100

103 johnson nancy 70000.00 101 100

104 johnsen nancy 60000.00 102 200

105 bush paul 150000.00 103 200

106 clinton bob 40000.00 NULL 300

107 clinton steve 500000.00 NULL 300

108 bush judy 600000.00 104 400

109 cook tim 700000.00 108 400

110 zucker mark 800000.00 NULL NULL

Table Name: MIDTERM.DEPENDENT

EmpID DependentID Gender DoB LName FName

101 1 M 1991-10-01 Smith Tom

101 2 F 1994-09-12 Smith Nancy

101 3 F 1998-01-13 Smith Mary

102 1 M 1989-10-04 Johnson Steve

102 2 F 1985-05-01 Carlson Cindy

104 1 M 1996-07-06 Thompson Paul

104 2 M 1997-11-15 Thompson Marc

104 3 F 1992-03-14 Thompson Jo

104 4 M 1997-09-23 Thompson John

105 1 F 1996-02-03 Lee Nancy

2. Provide employee id, last name and department name of all employees in the ascending order of employee id. Make sure you include all employees even if some may not have the departmental information available. Provide user friendly decent column names using aliases. If you need to use the outer join operation, use left-outer join.

3. Provide the Department ID, average, maximum, and minimum of salaries paid for each department. Do not include employees without departmental affiliation. Provide descriptive column names using aliases.