You have chosen to sponsor your bid up to a maximum amount of .
you are going to create a small database in your Oracle account, populate it, and use it to run some queries. I have picked a simple database structure for a simple job placement system. The database includes the following tables:
•Company(compid, compname, comptype): The company table has information about Companies - the ID is the stock ticker code unique for companies, name is as usual, and type is the type of company it is, like software, hardware, telecom, etc.
•Job(jobid, jobtitle, salarylow, salaryhigh, location, compid*) Each job is identified by a jobid, with a title, a low and high salary range, the location of the job. A job is posted by one company (a company may have multiple jobs posted)
•Skills(skillid, skillname) A skillname is something like "programming" or "database" or "management", etc. Each skill is associated with an id as well.
•Jobskills(jobid*, skillid*, expertiseneeded) This table has the skills required for each of the job. A job may require multiple skills. For each skill, the jobs need to specify what level of expertise is needed for that skill on a scale of 1-5 (1 indicating minimal knowledge, and 5 indicating very high knowledge).
•Applicant(appid, name, age, highdegree, expected_salary) Each applicant is identified by an ID, and has a name, age, highest degree obtained and the minimum expected salary of the applicant.
•AppSkills(appid*, skillid*, expertise) Similar to the JobSkills table, this is a set of skills that an applicant possesses.
•Applies(jobid*, appid*, appdate, decisiondate, outcome) Finally, an applicant may apply for one or more jobs through this database, and as they do, the dates of application, decision, and the outcome of the application ("hire", "processing", "nohire")
I have attached a script that will create the tables and insert some sample rows. Note that for some of the queries, you may need to insert more rows for testing.
Solve the following queries in SQL. For this part you should create two files. The first, hw3a-queries.txt must be an SQL script file (see the template attached to this homework), containing only valid SQL queries and comments indicating the query number and query text following the REM keyword. I have attached a template for your convenience. The sceond file, hw3a-results.html should be a transcript of running your queries against the data. You must submit both the files to get credit for this assignment.
1.Find jobs from consulting companies located in Chicago.
2.Find Applicants older than 24 with a BS or MS degree whose expected salary is over $50000
3.Find jobs located at Dayton that require knowledge in databases.
4.Find applicants who have at least an expertiselevel of 4 in programming.
5.Find the names and ages of applicants interviewed by Microsoft, showing the hired applicants at the bottom..
6.Find the average lower limit of salaries offered by Intel
7.Find the Jobs that satisfy the expected salary of Monica (Note that there may be more than one Monica in the database). Note that a job that satisfies Monica's expected salary may not be one she has applied to. Also, a job satisfies Monica's expected salary if the lower limit of that job is at or below her expected salary and the higher limit is at or above her expected salary.
8.For each company in the database, show the total number of jobs listed by that company, and average minimum salary. Show the
number of jobs as "numjobs" and average minimum salary as "avgmin".
9.Find the jobs located in Dayton that require either database or analysis skills, ordered by company and jobid.
10.Find the companies who have more than 2 jobs listed for the Dayton location.
11.Find the outcome of all job applications of Monica (once again, note that there may be multiple Monica's in the database).
12.Find the applicants who have applied to more than 2 different companies.
13.Find the jobs that require no skills (well, as far as the data is concerned), i.e., jobs for whic