Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
Write a query to get the First and Last Names of all employees who earned more than $70,000 in any year. Use subquery in the WHERE clause.

Write a query to get the First and Last Names of all employees who earned more than $70,000 in any year. Use subquery in the WHERE clause.

Database System Concepts
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
See similar textbooks
icon
Related questions
Question

As a recent Albany BBA graduate, you have been directly recruited by the new CEO of ABC – a major multinational firm to assist with implement her vision. Before starting the internal reorganization, the CEO wants to understand the structure of the company and assigns you to the project. The IT person in charge is on long term leave but he emails you the EER diagram of the employees database along with the user id and password of the MySQL database of the company.

NOTE: You only need the employees, salaries and dept_emp tables for the queries.

  1. Write a query to get the First and Last Names of all employees who earned more than $70,000 in any year. Use subquery in the WHERE clause.
salaries dept emp emp_no INT(11) 1 emp_no INT(11) 1 dept_no CHAR(4) salary INT(11) from_date DATE O to_date DATE from_date DATE Ko to_date DATE Indexes Indexes PRIMARY PRIMARY emp_no emp_no dept_no employees emp_no INT(11) birth_date DATE first_name VARCHAR(14) departments # last_name VARCHAR(16) + gender ENUM('M', F') dept_no CHAR(4) dept_name VARCHAR(40) hire_date DATE Indexes Indexes PRIMARY dept_name PRIMARY dept_manager titles 1 dept_no CHAR(4) 1 emp_no INT(11) t emp_no INT(11) 9 title VARCHAR(50) 9 from_date DATE from_date DATE Ko to_date DATE O to_date DATE Indexes Indexes PRIMARY PRIMARY emp_no emp_no dept_no
Transcribed Image Text:salaries dept emp emp_no INT(11) 1 emp_no INT(11) 1 dept_no CHAR(4) salary INT(11) from_date DATE O to_date DATE from_date DATE Ko to_date DATE Indexes Indexes PRIMARY PRIMARY emp_no emp_no dept_no employees emp_no INT(11) birth_date DATE first_name VARCHAR(14) departments # last_name VARCHAR(16) + gender ENUM('M', F') dept_no CHAR(4) dept_name VARCHAR(40) hire_date DATE Indexes Indexes PRIMARY dept_name PRIMARY dept_manager titles 1 dept_no CHAR(4) 1 emp_no INT(11) t emp_no INT(11) 9 title VARCHAR(50) 9 from_date DATE from_date DATE Ko to_date DATE O to_date DATE Indexes Indexes PRIMARY PRIMARY emp_no emp_no dept_no
Employees Table Salaries Table Dept Emp Table emp_no birth_date first_name last_name gender hire_date emp_no salary from_date to_date emp_no dept_no from_date to_date Georai Bezalel 60117 62102 10001 1953-09-02 Facello M 1986-06-26 10001 1986-06-26 1987-06-26 10001 d005 1986-06-26 9999-01-01 1964-06-02 Simmel Bamford Koblick 10002 F 1985-11-21 10001 1987-06-26 1988-06-25 d007 d004 10002 1996-08-03 9999-01-01 10003 1959-12-03 Parto M 1986-08-28 10001 66074 1988-06-25 1989-06-25 10003 1995-12-03 9999-01-01 10004 1954-05-01 Chirstian M 1986-12-01 10001 66596 1989-06-25 1990-06-25 10004 d004 1986-12-01 9999-01-01 10005 1955-01-21 Kvoichi Maliniak M 1989-09-12 10001 66961 1990-06-25 1991-06-25 do03 d005 10005 1989-09-12 9999-01-01 10006 1953-04-20 Anneke Preusia 1989-06-02 10001 71046 1991-06-25 1992-06-24 10006 1990-08-05 9999-01-01 10007 1957-05-23 Tzvetan Zielinski F 1989-02-10 10001 74333 1992-06-24 1993-06-24 10007 d08 1989-02-10 9999-01-01 10008 1958-02-19 Saniva Kalloufi M 1994-09-15 10001 75286 1993-06-24 1994-06-24 10008 d005 1998-03-11 2000-07-31 10009 1952-04-19 Sumant Peac F 1985-02-18 10001 75994 1994-06-24 1995-06-24 10009 do006 1985-02-18 9999-01-01 10010 1963-06-01 Duanakaew Piveteau 1989-08-24 10001 76884 1995-06-24 1996-06-23 10010 d004 1996-11-24 2000-06-26 10011 10012 Marv Patricio Eberhardt 1953-11-07 Sluis F 1990-01-22 10001 80013 1996-06-23 1997-06-23 10010 d006 2000-06-26 9999-01-01 1960-10-04 Bridaland M 1992-12-18 10001 81025 1997-06-23 1998-06-23 do09 do05 d003 10011 1990-01-22 1996-11-09 10013 1963-06-07 Terkki M 1985-10-20 10001 81097 1998-06-23 1999-06-23 9999-01-01 9999-01-01 10012 1992-12-18 84917 85112 10014 1956-02-12 Berni Genin M 1987-03-11 10001 1999-06-23 2000-06-22 10013 1985-10-20 Nooteboom M Cappelletti 10015 1959-08-19 Guoxiang 1987-07-02 10001 2000-06-22 2001-06-22 10014 d005 1993-12-29 9999-01-01 10016 1961-05-02 Kazuhito M 1995-01-27 10001 85097 2001-06-22 2002-06-22 10015 d008 1992-09-19 1993-08-22 Bouloucos Peha 10017 1958-07-06 Cristinel F 1993-08-03 10001 88958 2002-06-22 9999-01-01 10016 d007 1998-02-11 9999-01-01 1954-06-19 Kazuhide 1953-01-23 1987-04-03 1999-04-30 10018 F 10002 65828 1996-08-03 1997-08-03 10017 do01 1993-08-03 9999-01-01 10019 Lillian Haddadi M 10002 65909 1997-08-03 1998-08-03 10018 d004 1992-07-29 9999-01-01
Transcribed Image Text:Employees Table Salaries Table Dept Emp Table emp_no birth_date first_name last_name gender hire_date emp_no salary from_date to_date emp_no dept_no from_date to_date Georai Bezalel 60117 62102 10001 1953-09-02 Facello M 1986-06-26 10001 1986-06-26 1987-06-26 10001 d005 1986-06-26 9999-01-01 1964-06-02 Simmel Bamford Koblick 10002 F 1985-11-21 10001 1987-06-26 1988-06-25 d007 d004 10002 1996-08-03 9999-01-01 10003 1959-12-03 Parto M 1986-08-28 10001 66074 1988-06-25 1989-06-25 10003 1995-12-03 9999-01-01 10004 1954-05-01 Chirstian M 1986-12-01 10001 66596 1989-06-25 1990-06-25 10004 d004 1986-12-01 9999-01-01 10005 1955-01-21 Kvoichi Maliniak M 1989-09-12 10001 66961 1990-06-25 1991-06-25 do03 d005 10005 1989-09-12 9999-01-01 10006 1953-04-20 Anneke Preusia 1989-06-02 10001 71046 1991-06-25 1992-06-24 10006 1990-08-05 9999-01-01 10007 1957-05-23 Tzvetan Zielinski F 1989-02-10 10001 74333 1992-06-24 1993-06-24 10007 d08 1989-02-10 9999-01-01 10008 1958-02-19 Saniva Kalloufi M 1994-09-15 10001 75286 1993-06-24 1994-06-24 10008 d005 1998-03-11 2000-07-31 10009 1952-04-19 Sumant Peac F 1985-02-18 10001 75994 1994-06-24 1995-06-24 10009 do006 1985-02-18 9999-01-01 10010 1963-06-01 Duanakaew Piveteau 1989-08-24 10001 76884 1995-06-24 1996-06-23 10010 d004 1996-11-24 2000-06-26 10011 10012 Marv Patricio Eberhardt 1953-11-07 Sluis F 1990-01-22 10001 80013 1996-06-23 1997-06-23 10010 d006 2000-06-26 9999-01-01 1960-10-04 Bridaland M 1992-12-18 10001 81025 1997-06-23 1998-06-23 do09 do05 d003 10011 1990-01-22 1996-11-09 10013 1963-06-07 Terkki M 1985-10-20 10001 81097 1998-06-23 1999-06-23 9999-01-01 9999-01-01 10012 1992-12-18 84917 85112 10014 1956-02-12 Berni Genin M 1987-03-11 10001 1999-06-23 2000-06-22 10013 1985-10-20 Nooteboom M Cappelletti 10015 1959-08-19 Guoxiang 1987-07-02 10001 2000-06-22 2001-06-22 10014 d005 1993-12-29 9999-01-01 10016 1961-05-02 Kazuhito M 1995-01-27 10001 85097 2001-06-22 2002-06-22 10015 d008 1992-09-19 1993-08-22 Bouloucos Peha 10017 1958-07-06 Cristinel F 1993-08-03 10001 88958 2002-06-22 9999-01-01 10016 d007 1998-02-11 9999-01-01 1954-06-19 Kazuhide 1953-01-23 1987-04-03 1999-04-30 10018 F 10002 65828 1996-08-03 1997-08-03 10017 do01 1993-08-03 9999-01-01 10019 Lillian Haddadi M 10002 65909 1997-08-03 1998-08-03 10018 d004 1992-07-29 9999-01-01
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Types of Database Architectures
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
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
SEE MORE TEXTBOOKS