Q2. Use Employee.sql and Office.sql and write queries to extract the following information: Get IDs of employees whose first name starts with “pa”, followed by three characters and then ends with “a”. Show the first name along with the employee ID. How many employees at non-regional offices were hired after 2000? Get the Employee ID of the newest Account Rep who is working in a location with audit code=100.
Q2. Use Employee.sql and Office.sql and write queries to extract the following information: Get IDs of employees whose first name starts with “pa”, followed by three characters and then ends with “a”. Show the first name along with the employee ID. How many employees at non-regional offices were hired after 2000? Get the Employee ID of the newest Account Rep who is working in a location with audit code=100.
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
(MySQL) The files are in the images attached.
Q2. Use Employee.sql and Office.sql and write queries to extract the following information:
Get IDs of employees whose first name starts with “pa”, followed by three characters and then ends with “a”. Show the first name along with the employee ID.
How many employees at non-regional offices were hired after 2000?
Get the Employee ID of the newest Account Rep who is working in a location with audit code=100.
Transcribed Image Text:-- MYSQL dump 10.13 Distrib 8.0.23, for Win64 (x86_64)
3
-- Host: localhost
Database: my_schema2
4
-- Server version
8.0.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 •
/*!50503 SET NAMES utf8 */;
11 •
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12 •
/*!40103 SET TIME_ZONE='+00:00' */;
13 •
/*!40014 SET @OLD_UNIQUE_CHECKS=@QUNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 •
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 •
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 •
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18
--
Table structure for table employees
19
--
20
21
22 •
DROP TABLE IF EXISTS employees';
23 •
/*!40101 SET @saved_cs_client
= @@character_set_client */;
24 •
/*!50503 SET character_set_client = utf8mb4 */;
25 •e CREATE TABLE 'employees (
26
*EmployeeID int DEFAULT NULL,
27
* LastName text,
28
*FirstName text,
*Location text,
*Title text,
*Gender text,
29
30
31
32
*Performance' text,
*ManagerID text,
*hired date date DEFAULT NULL,
33
34
35
*salaries double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
36
37 •
38
39
- Dumping data for table employees
40
41
42
43 •
LOCK TABLES employees WRITE;
/*!40000 ALTER TABLE `employees DISABLE KEYS */;
INSERT INTO employees VALUES (10000, 'Milgrom', 'Pamela', 'Boston', 'Manager', 'F','Average','88888','1999-11-2
/*!40000 ALTER TABLE `employees ENABLE KEYS */;
44 •
45 •
46 •
47 •
UNLOCK TABLES;
48 •
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
49
50 •
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
51 •
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
52 •
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
53 •
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
54 •
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
55 •
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
56 •
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
57
58
-- Dump completed on 2021-03-04 12:23:48
59
Transcribed Image Text:MYSQL dump 10.13
Distrib 8.0.23, for Win64 (x86_64)
--
2
3
-- Host: localhost
Database: my_schema2
4
--
-- Server version
8.0.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 •
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 •
/*!50503 SET NAMES utf8 */;
11 •
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12 •
/*!40103 SET TIME_ZONE='+00:00' */;
13 •
/*!40014 SET MOLD UNIQUE CHECKS=@OUNIQUE CHECKS, UNIQUE CHECKS=0 */;
14 •
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@0FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS= */;
15 •
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 •
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18
19
-- Table structure for table office"
20
21
22 •
DROP TABLE IF EXISTS office';
23 •
/*!40101 SET @saved_cs_client
@@character_set_client */;
24 •
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `office (
* Location text,
25 •
26
*Regionaloffice` text,
* AuditCode int DEFAULT NULL
27
28
29
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
30 •
/*!40101 SET character_set_client = @saved_cs_client */;
31
32
33
Dumping data for table office
--
34
--
35
36 •
LOCK TABLES office WRITE;
37 •
/*!40000 ALTER TABLE `office DISABLE KEYS */;
38 •
INSERT INTO office VALUES ('Atlanta', 'No',101), ('Boston', 'Yes',100), ('Chicago', 'No',110), ('New York', 'No',101
39 •
/*!40000 ALTER TABLE `office ENABLE KEYS */;
40 •
UNLOCK TABLES;
41 •
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
42
43 •
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
44 •
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
45 •
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
46 •
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
47 •
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
48 •
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
49 •
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
50
51
-- Dump completed on 2021-03-04 12:21:21
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 2 steps
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY