You will need two tables

@ employees

@ positions

Design the ERD $($Entity Relationship Diagram$)$ using www sq$1$ designer

Tutorial on www sql designer is in our notes and will be covered in class

include a screen shot image of the design with your final zip file

You need to make sure

Each table has an appropriately named PK field

One table will need a FK

You will need to change the name of some of the fields

You need to split one of the fields into two fields

Use the appropriate field types

Use the default charset$/$collate that we have been using so far

Create the two tables

Have the create statements inside a filename $.$sq$1$

Then have the alter to put in the FK

Create the inserts using the data below

Some of the data is in the wrong format $($the dates$)$

You may want to do the "positions" table inserts first, this will let you put the FK$\text{'}$s for position$\_$id into the employees table without having to do a bunch of udpates $\backslash$table$[[$Name$,$city,state,zip,date hired,position$],[$Joe Shmoe,Burbank,CA$,91501,07/04/11,$Manager$],[$John Jacob,Mesa,AZ$,85202,04/05/09,$Worker Drone$],[$Sally Struthers,Mesa,AZ$,85203,09/15/10,$CEO$],[$Michelle Smith,Pasadena,CA$,91103,10/05/10,$Manager$],[$John Smith,Mesa,AZ$,85202,03/14/15,$Worker Drone$],[$Paul Lynn$,$Mesa,AZ$,85280,02/02/18,$Worker Drone$],[$Natasha Romanof,Burbank,CA$,91502,09/22/11,$Specialist$],[$Sam Hill,Mesa,AZ$,85201,01/15/12,$Worker Drone$],[$Stan Lee,Burbank,CA$,95103,01/05/14,$Manager$],[$Bruce Banner,Mesa,AZ$,85212,04/29/20,$Worker Drone$],[$Michiru Kagemori,Chandler,AZ$,85289,09/29/21,$Worker Drone$],[$Shirou Ogami,New Anima City,AZ$,85000,08/12/10,$Specialist$],[$Nazuna Hiwatashi,Scottsdale,AZ$,85333,09/15/21,$Worker Drone$],[$Alan Sylvasta,Foutain Hills,AZ$,85322,07/22/05,$CEO$],[$Marie Itami,Phoenix,AZ$,85444,08/17/09,$Specialist$],[$Barbaray Rose,New Anima City,AZ$,85000,04/29/20,$Manager$],[$Amalia True,San Jose,CA$,95233,06/22/18,$Specialist$],[$Penance Adair,San Jose,CA$,95233,06/22/18,$Worker Drone$],[$Maladie Jones,San Jose,CA$,95233,11/24/22,$Human Resources$],[$Hugo Swan,Burbank,CA$,95103,01/01/04,$Manager$]]$

NOTE: Remember that the FROM portion of your query is the tough part. You will need to set up one JOIN for the two tables.

Create an UPDATE Ouerv to fix the spelling on "Foutain Hills" NOTE: Remember that the FROM portion of your query is the tough part. You will need to set up one JOIN for the two tables.

Create an UPDATE Query to fix the spelling on "Foutain Hills"

Query that Selects ALL FIELDs except the PKs from the two tables $($should have $20$ results$)$

Queries that select ALL FIELDS except the PKs but:

only selects those with a Position of Manager

only selects "Worker Drone" from $"$AZ$"$

only selects "Manager" from "Pasadena"

Query that lists every worker from "Mesa" sorted by last name

Queries that aggregate $($group$)$ data for totals, these can NOT use all fields

Query that counts the number of "Manager"s by state. $($GROUP BY state$).$ This one can NOT use all fields.

Query that gives $($counts$)$ the number of workers by position, by state $($group by position and state$).$ Again, can only validly include a few fields $($state and position name, and the value$)$

You will need to use where and and count$()$ to do the above queries.

Zip the following into a $.$zip file $($called myfirstdb.zip or whatever is appropriate $$

Image of ERD $($from screen shot$)$

one or more $.$sql files that contain

Your two CREATE TABLE statements and the ALTER to add the FK$.$ They MUST WORK.

INSERTS for your two tables $($in the order you want me to run them, again they MUST WORK$)$

The $7$ SELECT Queries and $1$ UPDATE query