Fifteen SQL queries
- Status Closed
- Budget N/A
- Total Bids 30
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, [url removed, login to view] 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, [url removed, login to view] should be a transcript of running your queries against the data. You must submit both the files to get credit for this assignment.
[url removed, login to view] jobs from consulting companies located in Chicago.
[url removed, login to view] Applicants older than 24 with a BS or MS degree whose expected salary is over $50000
[url removed, login to view] jobs located at Dayton that require knowledge in databases.
[url removed, login to view] applicants who have at least an expertiselevel of 4 in programming.
[url removed, login to view] the names and ages of applicants interviewed by Microsoft, showing the hired applicants at the bottom..
[url removed, login to view] the average lower limit of salaries offered by Intel
[url removed, login to view] 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.
[url removed, login to view] 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".
[url removed, login to view] the jobs located in Dayton that require either database or analysis skills, ordered by company and jobid.
[url removed, login to view] the companies who have more than 2 jobs listed for the Dayton location.
[url removed, login to view] the outcome of all job applications of Monica (once again, note that there may be multiple Monica's in the database).
[url removed, login to view] the applicants who have applied to more than 2 different companies.
[url removed, login to view] the jobs that require no skills (well, as far as the data is concerned), i.e., jobs for whicGet free quotes for a project like this
Looking to make some money?
- Set your budget and the timeframe
- Outline your proposal
- Get paid for your work
Hire Freelancers who also bid on this project
Looking for work?
Work on projects like this and make money from home!Sign Up Now
- The New York Times
- Wall Street Journal
- Times Online