Student : studentId a string of 3 characters, name(20) and city(15) are variable character strings. StudentId is the primary key.

SummerJob: jobId a string of 5 characters, worktype (25) a variable string, company (20), a variable string, city (20), a variable string and hrlyWage, a number with at least 2 digits of precision after the decimal point. JobId is the primary key.

Student_Job: StudentId, JobId, hrsWorked (an integer), StartDate and EndDate. StartDate and EndDate are Date types. Primary Key is (StudentId, JobId). StudentId is a foreign key into Student Table and JobId is a foreign key into SummerJob table. HrsWorked are hours per day.

Creating tables and inserting values

Create tables using schemas given above, clearly defining the primary and foreign key constraints.

Insert values of your choice into the tables (at least five rows per table).

SQL Queries

Write and run the the following queries using SQL.

1) Find the names of student who live and work in the same city.

2) Show the Companies (and their cities) that pay the highest hrly wage.

3) Find the student id, jobid and the no.of days worked she/he worked in that job. DaysWorked can be obtained by endDate-startDate.

4) Show the names of students currently not working in any job.

5) Show the city and the number of Summer jobs available in that city.