Consider the following relational schema. An employee can work in more than one

Question

Consider the following relational schema. An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department.

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct_time: integer)

Dept(did: integer, budget: real, managerid: integer)

1. Write SQL statements to create tables for the above schema. Show the execution of your SQL statements and results by transcripts or screenshots.

2. Write the following queries in SQL and run them with MySQL. Print the sql statements and query results.

a) Find the names and salary of each employee who works in both the Sales Department and the Engineering department (assume that Sales Dept ID =101 0 and Engineering Dept ID = 201 2). Notice the correction on Dept ID above.

b) Find the department(s) with the highest average salary of employees.

c) Find the name and salary of each employee whose salary exceeds the budget of every department that he or she works in.

Explanation / Answer

Solution:

The first question is done as per Chegg guidelines, please repost others.  

1)

CREATE TABLE Emp(
    eid int(11) NOT NULL,
ename varchar(50),

age int(11),

salary float,
    PRIMARY KEY (eid)
);

CREATE TABLE Dept(
did int(11) NOT NULL,
dname varchar(50),
managerid int(11)'

    PRIMARY KEY (did)
);

CREATE TABLE Works(
eid int(11) NOT NULL,
did int(11),

pct_time int(11),

salary float,

CONSTRAINT FK_eid FOREIGN KEY (eid)
    REFERENCES emp(eid)

CONSTRAINT FK_did FOREIGN KEY (did)
    REFERENCES Dept(did)
);

);

Also, if you don't have any questions left to post, then let me know, I will try to help. Thanks  

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.