Ditutup

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: sql average lower limit, expected salary, use data structure programming, transcript microsoft, transcript jobs, transcript job, total application software company, hire company chicago, template testing software, telecom consulting jobs, sql code oracle, software testing salaries, software testing find job, software testing companies, skills required software testing, simple stock ticker, salary range, salaries, range query, range minimum query, programming jobs salary, programming sql, programming degree, oracle database company, oracle company name

Tentang Majikan:
( 14 ulasan ) Everett, United States

ID Projek: #1599472

29 pekerja bebas membida secara purata $48 untuk pekerjaan ini

samitXI

Please check your inbox. Thanks

$35 USD dalam sehari
(5 Ulasan)
4.9
manhpham

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

$60 USD dalam sehari
(10 Ulasan)
4.3
it2051229

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

$50 USD dalam 0 hari
(16 Ulasan)
4.1
rakib062

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)
3.5
lokeshk9936

I can do this in a day.

$49 USD dalam sehari
(3 Ulasan)
2.8
sathish2connect

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

$30 USD dalam sehari
(6 Ulasan)
2.3
QaisarMaqbool

I offer my services for your job. Thanks

$50 USD dalam 2 hari
(1 Ulasan)
0.0
preeti6250

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)
0.0
GQ89

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)
0.0
lakshman22

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)
0.0
pratikshatech

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

$360 USD dalam 0 hari
(0 Ulasan)
0.0
Awaismohammad041

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

$30 USD dalam sehari
(0 Ulasan)
0.0
amitkumar2127

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

$30 USD dalam sehari
(0 Ulasan)
0.0
atreyee80

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)
0.0
CompletionOnTime

Kindly check your PMB.

$40 USD dalam sehari
(0 Ulasan)
0.0
msxperts

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

$30 USD dalam sehari
(0 Ulasan)
0.0
jshoesmith

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)
0.0
lynwode

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

$30 USD dalam sehari
(0 Ulasan)
0.0
alvicreatives

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)
0.0
pavan45

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)
0.0