Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
What's wrong with my SQL code? It doesn't run. The SQL files are in the images attached. Queries should extract the answers directly.  Use Employee.sql and Office.sql and write queries to extract the following information:  # Retrieve all information about employees whose hired date is 31 and whose last name consists of exactly five characters. # SELECT * from employees where to_char(hired_date, 'DD') =31 and length(LastName)=5; # Make a list of all employees who have a salary more than the average salary of all employees. # SELECT AVG(salaries) FROM employees SELECT * FROM employees WHERE salary > ALL(SELECT avg(salary)FROM employee); # How many female employees have a salary higher than the average salary of all employees? # SELECT COUNT FROM employees WHERE salary > ALL(SELECT avg(salary) FROM employees )AND Gender='Female';

What's wrong with my SQL code? It doesn't run. The SQL files are in the images attached. Queries should extract the answers directly.  Use Employee.sql and Office.sql and write queries to extract the following information:  # Retrieve all information about employees whose hired date is 31 and whose last name consists of exactly five characters. # SELECT * from employees where to_char(hired_date, 'DD') =31 and length(LastName)=5; # Make a list of all employees who have a salary more than the average salary of all employees. # SELECT AVG(salaries) FROM employees SELECT * FROM employees WHERE salary > ALL(SELECT avg(salary)FROM employee); # How many female employees have a salary higher than the average salary of all employees? # SELECT COUNT FROM employees WHERE salary > ALL(SELECT avg(salary) FROM employees )AND Gender='Female';

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

What's wrong with my SQL code? It doesn't run. The SQL files are in the images attached. Queries should extract the answers directly. 

Use Employee.sql and Office.sql and write queries to extract the following information: 

# Retrieve all information about employees whose hired date is 31 and whose last name consists of exactly five characters. #
SELECT * from employees where to_char(hired_date, 'DD') =31 and length(LastName)=5;

# Make a list of all employees who have a salary more than the average salary of all employees. #
SELECT AVG(salaries)
FROM employees
SELECT * FROM employees
WHERE salary > ALL(SELECT avg(salary)FROM employee);

# How many female employees have a salary higher than the average salary of all employees? #
SELECT COUNT FROM employees WHERE salary > ALL(SELECT avg(salary) FROM employees )AND Gender='Female';

-- 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) 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
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
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
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
Intermediate SQL concepts
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