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
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
Related questions
Question
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 1 steps
Recommended textbooks for you
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)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
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)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education