Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Consider the following relational schema. An employee can work in more than one department; also, the percentTime field of the Works relations shows the percentage

Consider the following relational schema. An employee can work in more than one department; also, the percentTime field of the Works relations shows the percentage of time that a given employee works in a given department. Employees(eID integer, fName string, lName string, age integer, salary real) Locations(lID integer, address string, city string, state string, zip string) Departments(dID integer, dName string, budget real, managerEID integer, locationID integer) WorksIn(eID integer, dID integer, percentTime integer, startDate date, endDate date ) The primary key fields are underlined, and the domain of each field is listed after the field name. Thus eID is the key for the table Employees, lID is the key for Locations, dID is the key for Departments, and eID,and dID together form the key for WorksIn. These tables have been created and the data have been populated using MySQL server. Download HW3.sql to your computer and run it on MySQL. Execute your SQL statements based on these tables. Some notes to keep in mind as you go: While we have tried to limit the possible number of correct solutions to each question, keep in mind that there still may be multiple correct solutions. For each of the question, there is a relatively short solution, which is preferred. Unless specified in the question, your queries should produce the correct answer for any instance of the relations. We have provided a script that will contain DDL for creating a sample database with some test data, but your queries should work over any legitimate instance of the relation. Unless specified in the question, return the entire tuple. E.g. retrieve students would mean return the entire students tuples. Return the columns in the order we specify. E.g. question 6 would require you to output the fields in name, salary order. Note that the dataset provided is minimal. We will be testing your queries on a much larger dataset, so be sure to add to the one provided to make sure your queries are in fact correct Write the following queries in SQL statements. The query answers must not contain duplicates, but you should use the SQL keyword DISTINCT only when necessary. When your SQL query statement is not accepted by MySQL, it will display error messages. You need to read that message and fix all problems accordingly. Note that MySQL does not support INTERSECT and EXCEPT currently. When you need to find the intersection and difference of two result sets, you can use IN and NOT IN instead. In addition, for this assignment, creation of temporary tables is not allowed, i.e., for each question you must write exactly one SQL statement. Task: Retrieve the name and salary of the two highest salaried employees for the department Research. Hints: The DENSE_RANK() function may use to assign a rank for each row within a partition or result set with no gaps in ranking values.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

MySQL/PHP Database Applications

Authors: Brad Bulger, Jay Greenspan, David Wall

2nd Edition

0764549634, 9780764549632

More Books

Students also viewed these Databases questions

Question

b. Will there be one assigned leader?

Answered: 1 week ago

Question

d. How will lack of trust be handled?

Answered: 1 week ago

Question

b. Does senior management trust the team?

Answered: 1 week ago