Homework answers / question archive / IS 312 Database Assignment 2 (DBA2): Basic SQL Queries This homework is to practice database queries using SQL

IS 312 Database Assignment 2 (DBA2): Basic SQL Queries This homework is to practice database queries using SQL

Computer Science

Share With

---

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

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.