This homework is to practice database queries using SQL. The database used is EmployeesDB, within which...

  

Transcribed Image Text:

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) Employees EmplD Last Name First Name Position Department Shift Salary Hire Date When a field name has two parts, that field name must be in a pair of bracket. Compare: Emel [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 IIMIMIT Accountant Product Marketer Engineer Admin Assist Design Specialist Inventory Accounting Marketing Engineering Admin Art D Warehouse Chief Scientist R and D Group Admin Assist R and D 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. Data types: DEPT-Text; LastName-Text, Shift - Number, the others are obvious. When specifying a condition, if the condition includes a number, type that number condition directly, if the condition includes a text, that text condition must be in a pair of quotes, such as "R and D". 3 points 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, 3 points (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 300 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 4 points $20,000; for (Group 2, HZ) the R and D department, or those employees whose salary is more than $70,000 and position is Engineer. 4 points 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. 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) Employees EmplD Last Name First Name Position Department Shift Salary Hire Date When a field name has two parts, that field name must be in a pair of bracket. Compare: Emel [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 IIMIMIT Accountant Product Marketer Engineer Admin Assist Design Specialist Inventory Accounting Marketing Engineering Admin Art D Warehouse Chief Scientist R and D Group Admin Assist R and D 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. Data types: DEPT-Text; LastName-Text, Shift - Number, the others are obvious. When specifying a condition, if the condition includes a number, type that number condition directly, if the condition includes a text, that text condition must be in a pair of quotes, such as "R and D". 3 points 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, 3 points (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 300 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 4 points $20,000; for (Group 2, HZ) the R and D department, or those employees whose salary is more than $70,000 and position is Engineer. 4 points 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.

Answer rating: 100% (QA)

Problem 1 SQL Query SELECT lastName firstName dept salary ... View the full answer