Answer the query using SQL.For each department which has more than 5 female employees, retrieve the department name, thenumber of female employees in the department, and their average salary. EMPLOYEEENAMEMINITLNAMESSNBDATEADDRESSSEXSALARYSUPERSSNDNODEPARTMENTDNAMEDNUMBERMGRSSNMGRSTARTDATEDEPT LOCATIONSDNUMBERDLOCATIONPROJECTPNAMEPNUMBERPLOCATIONDNUMWORKS_ONESSNPNOHOURSDEPENDENTESSNDEPENDENT_NAMESEXBDATERELATIONSHIP

In the following Query we will retrieve three things: The department name The number of female employees And their average salarySelect dname,count(dno),Avg(salary) From department d ,employee e Where d.dnumber=e.dnoAnd sex=‘Female’ //if not worked try "F" instead of "Female".And count(dno)>5;

Answered: Answer the query using SQL. For each…

Engineering

Computer Science

Answer the query using SQL. For each department which has more than 5 female employees, retrieve the department name, the number of female employees in the department, and their average salary.

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

Related questions

Q: The query should list all employees whose Position begins with the word greenhouse and whose weekly…

A: These question answer is as follows,

Q: LIST THE FIRST AND LAST NAMES OF ALL OWNERS WHO OWN A TWO-BEDROOM PROPERTY. USE THE IN OPERATOR IN…

A: Considering there are 2 tables:- OWNER: which holds the OWNER_NUM, FIRST_NAME, and LAST_NAME…

Q: List the years and their total gross earnings (sum of earnings per year), but do not include gross…

A: ANSWER:-

Q: Write a query to list the book number, title, and number of times each book has been checked out…

A: Query The query for viewing book number, title and the number of times each book had been checked…

Q: Use SQL and TAL Distributors database to complete the following questions: List the item number and…

A: List the item number and description for all items. The descriptions should appear in uppercase…

Q: --List all products whose unit price is less than the average price. --List the shipper id,…

A: Given: consider the tables as. product { productID, productNAME, price_per_unit, category_product }…

Q: How will you represent the following multiple relation in SQL DBM

A: For M:N relationship we need to create a new relational table for the relationship. Here for…

Q: Display employees where the first name or last name starts with S. using sql plus hr user

A: To select data from a database, SELECT statement are used. The data is saved in a result table…

Q: Question 6 (Must include subquery): Write, a query that returns every customer that has a loan at…

A: The query is given below with output screenshot Demo data has been used in tables

Q: List the number of customers living at each state that is included in the Customer_T table.

A: Query:-

Q: List the LastName, FirstName, and the total number of orders placed by each customer (as OrderCount)…

A: SQL is a structured query language, that's designed to facilitate the extraction the specific…

Q: Show the product names for products that have been ordered in quantities equal to or exceeding 120.…

A: SQL, or Structured Query Language, is a specialized programming language used for managing and…

Q: Explain the benefits of using the identity column capability in SQL.

A: The identity column generates the key values. It makes the increment in the unique values.

Q: Use a query to compute the total of all purchases, the number of purchases, and the average purchase…

A: SQL query The Round() function is used to round off the number to the desired decimal places. The…

Q: Write the SQL code that counts the number of assignments ordered for each project. Call this…

A: We have four tables namely: JOB PROJECT EMPLOYEE ASSIGNMENT We wish to write a query that counts…

Q: Write a query to display the first name, last name, and email address of employees hired from May 1,…

A: Here we have to write 2 queries whose details are as follows:Write a query to display the first…

Q: How do you sort the results of a Select query in SQL?

A: Sorting the results of a Select query in SQL: SQL uses the ORDER BY clause with its SELECT statement…

Q: Write a query to display the book number, title and cost of books that have the lowest cost of any…

A: Query: In general, the term query describes a question or request that is made by a user or another…

Q: Using My SQL Workbench Find all cities in Argentina whose name begins with S or M a. display city…

A: Logic:- To display names that begins with any character , use like operator and put first character…

Q: Write the following as the MySQL query: List the employee ID, first name, last name, hire date,…

A: In database administration, it is frequently essential to extract particular data from a dataset…

Q: Create a PL/SQL query to display the customer name, employee name and the coin product returned. In…

A: Create table ReturnDetails(CustomerName varchar(30),EmployeeName varchar(30),CoinReturn…

Q: 4. Which customers live in Georgia or New Jersey? Put the results in ascending order by last name.…

A: Assume that, the name of the table is Customer The entities of Customer table are last name, first…

Q: Query list of all our customers full name and email. Show those whose customer id is either…

A: The above question is solved in step 2:-

Q: by using the table in the query attached: Show the name, contact number and email address of the…

A: Demo data has been used to solve the query The SQL query is given below with output screenshot

Q: database HR Display the last name, salary, and commission for all employees who earn…

A: /* Creation of EMP table, I am creating column EMP_FIRST_NAME and EMP_LAST_NAME instead of…

Q: Using nwind database, create a query to show company name, product name, and quantity for items…

A: Structured query language SQL is a programming language for storing and processing information in a…

Q: Write a query that returns the Staff ID for the staff member who is involved in the most rentals

A: The goal of this search is to identify the employee who handles the most rentals. In order to…

Q: Using SQL Show the distinct location_id for all the locations where the company has departments

A: SQL SQL stands for Structured Query Language, and it is a programming language used to store,…

Q: For each different job_id group in the employee table, show the average salary and job_id for the…

A: SELECT Average_salary, Job_id FROM employees WHERE EXISTS (SELECT *…

Q: Print out names, phone numbers, house ID, and status of people who live in the same house as anyone…

A: Database: A database acts as a storage of data. A large amount of data is being stored in the…

Q: write a SQL query to find fifth maximum salary from table Employee (EID, EName, Salary)

A: To Find the 5th maximum salary we will use subqueries or co-related queries. In the inner query we…

Q: Using Northwind: USE MICROSOFT SQL SERVER Create a query selecting the Manager first name, last…

A: For writing the given Sql query we will use the join clause.

Q: Write SQL query to increment SALARY of all employees by 100 whose current SALARY is 1000 in the…

A: The SQL UPDATE statement modifies the data of one or more records in the table. All rows can be…

Q: Answer the query using SQL. For each department that controls more than 3 projects, retrieve the…

A: Introduction of DBMS: A database is a collection of data that are used to produce useful information…

Q: 4. Determine which orders had a higher total amount due than order 1008. SQL commands

A: SQL is the standardized programming language that is used to manage relational databases and perform…

Q: Use SQL commands. Include both SQL commands and results in your answers. Include screenshots of the…

A: Join: A JOIN clause is used to combine rows from two or more tables, based on a related column…

Q: Write the SQL code to list the customer ID, first name, last name, street, city, state, zip and the…

A: SQL stands for Structured Query Language. It is a standard programming language designed for…

Q: salary is above 4000 and job_id starts with a letter “S”

A: 1. SELECT * FROM employee WHERE (salary > 4000) AND job_id LIKE 'S%' 2. SELECT *,…

Q: ery: List the IDs of the tutors who are currently tutoring more t

A: From the question, we need to write a Query of the list of the IDs of the tutors. SQL that would…

Q: IN SQL write a query to get the names of all Students who passed the exam from the table…

A: Required:

Q: List the product ID from the highest selling product in France (Inner join ) Write a query to…

A: We need to write queries for given scenarios.

Q: List the names of the production companies and the titles of their movies. This time, include all…

A: Given that, The names of the tables are Movie and Production Assume that, The attributes of Movie…

Q: PL/SQL please use oracle sql developer. I know it would be hard to slove without the data but please…

A: Query for passenger info between dates.

Concept explainers

SQL Query

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed by using SQL.

Question

Answer the query using SQL.
For each department which has more than 5 female employees, retrieve the department name, the
number of female employees in the department, and their average salary.

EMPLOYEE
ENAME
MINIT
LNAME
SSN
BDATE
ADDRESS
SEX
SALARY
SUPERSSN
DNO
DEPARTMENT
DNAME
DNUMBER
MGRSSN
MGRSTARTDATE
DEPT LOCATIONS
DNUMBER
DLOCATION
PROJECT
PNAME
PNUMBER
PLOCATION
DNUM
WORKS_ON
ESSN
PNO
HOURS
DEPENDENT
ESSN
DEPENDENT_NAME
SEX
BDATE
RELATIONSHIP

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!

SEE SOLUTION Check out a sample Q&A here

Note:

VIEW

Query

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

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

List the movie titles and the names of their production companies. Only include production companies that have movies. Sort alphabetically by the movie title. use sql ( so theres a table of movie and a table of production)
How do you sort the results of a Select query in descending order in SQL?
What variables should be taken into account when determining whether or not to establish an index for a column in SQL.
List the names of all customers that have a credit limit of $7,500 or less
Display details of jobs where the minimum salary is greaterthan 10000. using sql plus also show output
SQL query using Rank() Retrieve the top-selling product of each day.Use the total sold quantity to determine the top-selling product.The top-selling product has the highest total sold quantity.If there is a tie, the solution must pick up the tie.Include the order date, product id, and the total sold quantity of the top-selling product of each day in the returned data.Sort the returned data by the order date.*/
Quèstion 12 Write SQL queries for the following: i. Insert all rows from Product table into Parts table by using subquery. ii. Find the average of the sales Amount column in Sales table where supplier ID is S212. i. select all the details of the suppliers from the Supplier table where Supplier.address starts with "Toy. TTT Arial 3 (12pt)
Show the employees who were in the engineering department as of Jan. 1, 2005 (using SQL)
Write SQL statements to the following queries. Retrieve the PRODUCT_NAME that product UNIT_PRICE less than the 10 (use subquery).
Please help: Using the following sql file, how would you find List all the rows in which the prescription dates occur on or after November 14. How many doctors provide prescriptions with 30 tablets in total? List doctor’s details where speciality is either Dermatology or Neurology. (Use INoperator). List the results by the first name in ascending order and the last name indescending order Find all patients rows whose first names include ‘George’ (Assume case sensitivity). List number of patients that do not have a phone number. Beginfor c in (select table_name from user_tables) loopexecute immediate ('drop table '||c.table_name||' cascade constraints');end loop;End; //* Assignment_1 */ ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';CREATE TABLE DOCTOR (DOC_ID varchar2(5) PRIMARY KEY,DOC_LNAME varchar2(15),DOC_FNAME varchar2(15),DOC_INITIAL varchar2(1),DOC_SPECIALTY varchar2(15));INSERT INTO DOCTOR…
List all vendors with their address(city, state,zipcode) for all the vendors who have credited account (credit total>0)) Hint:Vendors & Invoices tables with credit total column WHERE vendors.vendor id = invoices.vendor id AND CREDIT_TOTAL>0; %3D
Write a query to determine how many products you can find in the Production.Product table. This is an SQL Table.