Consider the following relations. 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, dname: string, budget: real, managerid:integer) Write and execute the following queries in SQL: 1. Print the names and ages of each employee who works in the Hardware department. 2. For each department with more than 20 employees, print the did together with the number of employees that work in that department. 3. Print the name of each employee whose salary exceeds the average salary of all employees. 4. Find the managerids of managers who manage only departments with budgets greater than $1 million. 5. Find the employees who work in the 'Operations' department and print the information (eid, ename, age, and salary) for each employee. The output should be ordered by ename. Steps: 1. Create the tables emp, dept, and works using the file make-tables.sql. Make sure that you open the .sql file and change it to your database before creating the tables. Tables can be created individually or in batch-mode. 2. Load data into the tables using load data local infile 'emp.txt' into table emp fields terminated by ','; as we introduced in the tutorial. Load data in the following order: emp, dept, works. 3. Create SQL code for the queries above. 4. Catch the run result by either taking a screenshot or by using copy and paste. 142519864,2,100 242518965,1,100 141582651,1,50 141582651,5,50 141582657,1,25 141582657,5,75 011564812,3,100 254099823,3,100 356187925,2,100 489456522,7,100 287321212,2,100 248965255,3,100 159542516,4,100 090873519,2,100 486512566,4,100 619023588,1,100 489221823,2,100 548977562,4,100 578875478,6,100 051135593,2,100 060839453,2,100 112348546,2,100 115987938,2,100 132977562,2,100 269734834,2,100 280158572,2,100 301221823,2,100 318548912,2,100 320874981,2,100 322654189,2,100 348121549,2,100 351565322,2,100 451519864,2,100 455798411,2,100 550156548,2,50 552455318,2,25 556784565,2,25 567354612,2,75 573284895,2,50 574489456,2,50 015645489,6,100 015487874.6.100

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question
Consider the following relations. 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, dname: string, budget: real, managerid:integer)
Write and execute the following queries in SQL:
1. Print the names and ages of each employee who works in the Hardware department.
2. For each department with more than 20 employees, print the did together with the number of
employees that work in that department.
3. Print the name of each employee whose salary exceeds the average salary of all employees.
4. Find the managerids of managers who manage only departments with budgets greater than
$1 million.
5. Find the employees who work in the 'Operations' department and print the information (eid,
ename, age, and salary) for each employee. The output should be ordered by ename.
Steps:
1. Create the tables emp, dept, and works using the file make-tables.sql. Make sure that you
open the .sql file and change it to your database before creating the tables. Tables can be
created individually or in batch-mode.
2. Load data into the tables using load data local infile 'emp.txt' into table emp fields
terminated by ','; as we introduced in the tutorial. Load data in the following order: emp,
dept, works.
3. Create SQL code for the queries above.
4. Catch the run result by either taking a screenshot or by using copy and paste.
Transcribed Image Text:Consider the following relations. 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, dname: string, budget: real, managerid:integer) Write and execute the following queries in SQL: 1. Print the names and ages of each employee who works in the Hardware department. 2. For each department with more than 20 employees, print the did together with the number of employees that work in that department. 3. Print the name of each employee whose salary exceeds the average salary of all employees. 4. Find the managerids of managers who manage only departments with budgets greater than $1 million. 5. Find the employees who work in the 'Operations' department and print the information (eid, ename, age, and salary) for each employee. The output should be ordered by ename. Steps: 1. Create the tables emp, dept, and works using the file make-tables.sql. Make sure that you open the .sql file and change it to your database before creating the tables. Tables can be created individually or in batch-mode. 2. Load data into the tables using load data local infile 'emp.txt' into table emp fields terminated by ','; as we introduced in the tutorial. Load data in the following order: emp, dept, works. 3. Create SQL code for the queries above. 4. Catch the run result by either taking a screenshot or by using copy and paste.
142519864,2,100
242518965,1,100
141582651,1,50
141582651,5,50
141582657,1,25
141582657,5,75
011564812,3,100
254099823,3,100
356187925,2,100
489456522,7,100
287321212,2,100
248965255,3,100
159542516,4,100
090873519,2,100
486512566,4,100
619023588,1,100
489221823,2,100
548977562,4,100
578875478,6,100
051135593,2,100
060839453,2,100
112348546,2,100
115987938,2,100
132977562,2,100
269734834,2,100
280158572,2,100
301221823,2,100
318548912,2,100
320874981,2,100
322654189,2,100
348121549,2,100
351565322,2,100
451519864,2,100
455798411,2,100
550156548,2,50
552455318,2,25
556784565,2,25
567354612,2,75
573284895,2,50
574489456,2,50
015645489,6,100
015487874.6.100
Transcribed Image Text:142519864,2,100 242518965,1,100 141582651,1,50 141582651,5,50 141582657,1,25 141582657,5,75 011564812,3,100 254099823,3,100 356187925,2,100 489456522,7,100 287321212,2,100 248965255,3,100 159542516,4,100 090873519,2,100 486512566,4,100 619023588,1,100 489221823,2,100 548977562,4,100 578875478,6,100 051135593,2,100 060839453,2,100 112348546,2,100 115987938,2,100 132977562,2,100 269734834,2,100 280158572,2,100 301221823,2,100 318548912,2,100 320874981,2,100 322654189,2,100 348121549,2,100 351565322,2,100 451519864,2,100 455798411,2,100 550156548,2,50 552455318,2,25 556784565,2,25 567354612,2,75 573284895,2,50 574489456,2,50 015645489,6,100 015487874.6.100
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 1 steps

Blurred answer
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education