IS 312 Database Assignment 2 (DBA2): Basic SQL Queries

Assigned Week 5 Class 1 (Mon); Due Week 6 Class 1 (Mon). 12+2=14 points

This homework is to practice database queries using SQL. The database used is EmployeesDB, within which the table Employees will be used. (This DB has only one table)

When a field name has two parts, that field name must be in a pair of bracket. Compare: EmpID [Last Name] Department [Hire Date] Those fields having a two-part name need brackets [..]

Sample of positions & dept names (so you know how to type those names) Position Department

Write the SQL codes for the stated business logic (scenarios/needs).

Every question has two scenarios, each for a group of students:

A~G is group 1, and

H~Z is group 2.

Problem 1: Projection (display subset of columns) and Selection (display subset of rows)

List last name, first name, department, and salary,

(Group 1, A~G) for the Admin department, order by position, then w/in a position order by last name;

(Group 2, H~Z) for the R and D department, order by position, then w/in a position order by salary.

Problem 2: Combination of criteria with AND (the two conditions must be SIMULTANEOUSLY satisfied)

List employees' last name, first name, department, and salary,

(Group 1, A~G) for those in Marketing department, whose salary is $40,000 or more;

(Group 2, H~Z) for those in R and D department, whose salary is $50,000 or less.

Problem 3: Combination of criteria with AND and OR (watch the logic; carefully determine whether or not to use parentheses, and *if* using, where to use). List last name, first name, department, and salary,

for (Group 1, A~G) the Accounting department, or those employees in Shift 3whose salary is below $20,000;

for (Group 2, H~Z) the R and D department, or those employees whose salary is more than $70,000 and position is Engineer.

Problem 4: GROUP BY, with ORDER BY (Two prints)

(Group 1, A~G) Print 1: List average salary for each department;Print 2: List average salary for each department, order the output by the average salary, high to low.

(Group 2, H~Z) Print 1: List the number of employees (COUNT last name) for each department;

Print 2: List the number of employees (COUNT last name) for each department, order the output by the number of employees, high to low.