Extra Credit Assignment: Multiple Table Query -Worker - Department Branch Database

100 Point Extra Credit Assignment 

You will use a text editor such as Notepad ++ or Visual Studio Code to write the queries to the sample database provided. I included an ER Diagram, Metadata, and a sample database to test your queries on.

ER Diagram for the Branch Dept Worker Database Download ER Diagram for the Branch Dept Worker Database

Metadata and Information Requests Download Metadata and Information Requests

Link for Sample Database for Branch Dept Worker problems Download Link for Sample Database for Branch Dept Worker problems

Submit The Following Documents

• SQL Script files containing the SQL Script responding to the information requests.  Test File contain the SQL Script must have an SQL Extension

Multiple Table Query Assignment

Below is an illustration of the data of a table named Worker.  Write queries to satisfy the information requests at the bottom of the table. Upload your completed list of queries in a text file with an SQL extension

Assume the following about the data in the table

Branch

Dept

Worker

There is also a sample sandbox database with test data available through the assignment link in Canvas

Multiple Table SELECT Queries for this assignment

100 Point Assignment – Questions are 25 Points for each correct query

Constraints (See the following page for table elements)

Dept table: deptID is the primary key & branchID is a foreign key that references the branchID field in the Branch table.

Branch table: branchID is the primary key.

Worker table: workerID is the primary key and deptID references deptID in the dept table.

1. Write a query to list the name and gender and total pay for all workers. Total Pay is equal to Salary plus Commission
2. Write a query to list the Departments Names for all department in New York and Boston
3. Write a query to list the Work Last Name, Gender, Branch Name, and Department Name for all workers that are female
4. Write a query to find the employees that do NOT earn a commission. In the output include each employee’s workerID, name, salary and department name.