Fifteen SQL queries

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.

Your task

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 whic

Kemahiran: SQL

Lihat lebih lanjut: find average lower limit salary offered company sql, sql average lower limit, what is your expected salary, what is oracle company, what is data structure in programming, what are the application of data structure, what are jobs that hire at 13, use of data structure in programming, transcript microsoft, transcript jobs, transcript job, total application software company, the hire company chicago, the chicago hire company, template for testing software, telecom consulting jobs, sql to oracle, sql code oracle, software testing salaries, software testing find job, software testing companies, skills required for a software testing, simple stock ticker, salary range, salaries

Tentang Majikan:
( 14 ulasan ) Everett, United States

ID Projek: #1599472

29 pekerja bebas membida secara purata $48 untuk pekerjaan ini


Please check your inbox. Thanks

$35 USD dalam sehari
(5 Ulasan)

I am SQL and Oracle expert. I am ready to help you do this task.

$60 USD dalam sehari
(10 Ulasan)

Hi, kindly check your inbox for my inquiries. Thanks.

$50 USD dalam 0 hari
(16 Ulasan)

Hi, I am working as a database admin,designer and developer with 8+ years of experience in oracle, ms access,sql server. My area of expertise is to design,implement database and write query to meet business requirem Lagi

$50 USD dalam 2 hari
(7 Ulasan)

I can do this in a day.

$49 USD dalam sehari
(3 Ulasan)

Hi, I will complete it within few hours from now. Ready to start. Thanks.

$30 USD dalam sehari
(6 Ulasan)

I offer my services for your job. Thanks

$50 USD dalam 2 hari
(1 Ulasan)

Dear Employer, I have read the problem statement mentioned above. I would require the attached script for the specific format you want me to follow to created table as per you requirement. I have star Lagi

$30 USD dalam 2 hari
(0 Ulasan)

Dear user, with the following I guarantee that I am fully able to fulfill your tasks and I have the skills to do it. I have already succeded the Database System modules into two different University (Kingston Universi Lagi

$40 USD dalam sehari
(0 Ulasan)

Hi, I am a Microsoft certified database administrator. I can give your queries with in 3 hours Can you please mail me back. Thanks, Lakshman

$30 USD dalam sehari
(0 Ulasan)

Its Just an 1 hour job . please allow me to do the same.

$360 USD dalam 0 hari
(0 Ulasan)

i am all set to do it...........

$30 USD dalam sehari
(0 Ulasan)

I have been working on oracle queries for so many year and ready to perform this task.

$30 USD dalam sehari
(0 Ulasan)

Dear Sir, I have completed my Masters in Computer Applications. Databases is my area of strength and interest. I would appreciate if you would give me this assignment. I have gone through the specific and I could no Lagi

$30 USD dalam sehari
(0 Ulasan)

Kindly check your PMB.

$40 USD dalam sehari
(0 Ulasan)

I am ready to work with you, check the Private Message

$30 USD dalam sehari
(0 Ulasan)

Hi. I'm a database developer and SQL expert with over 10 years experience. My experience includes 4 years working with Oracle databases. I love data and would find this project fun to work on. Thanks, Justin.

$30 USD dalam sehari
(0 Ulasan)

Hi, Over 15 years commercial Oracle experience. Please check PMB for more details.

$30 USD dalam sehari
(0 Ulasan)

Hi, Alvicreatives has been long into web application development & designing and development business. The last 5 years, we are working on web based ASP.net / C#, Sqlserver 2005/2008, Ajax, CSS, Flash,PHP,MySql,Wor Lagi

$70 USD dalam sehari
(0 Ulasan)

Hi, I am Pavan with 3 years of experience Software domain. I am good at SQL queries. I have extensively worked on Oracle SQL and MySQL. Looking forward to help you as this project is very much interested to me. Lagi

$30 USD dalam 0 hari
(0 Ulasan)