Specify the following queries on the database in Figure 5.5 in SQL. Show the query results if
Question:
Specify the following queries on the database in Figure 5.5 in SQL. Show the query results if each query is applied to the database state in Figure 5.6.
a. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.
b. Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees (as in Exercise 7.5a). Can we specify this query in SQL? Why or why not?
Figure 5.5
Figure 5.6
Transcribed Image Text:
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname San Bdate Address Sex Salary Super_ssn Dno 123456789 1965-01-09 731 Fondren, Houston, TX M 333445555 1955-12-08 638 Voss, Houston, TX 999887777 1968-01-19 3321 Castle, Spring, TX B 30000 333445555 M 40000 888665555 John Smith Franklin Wong Alicia Zelaya 25000 987654321 43000 888665555 F 4 Jennifer Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M English 453453453 1972-07-31 5631 Rice, Houston, TX 987987987 1969-03-29 980 Dallas, Houston, TX Ramesh 38000 333445555 Joyce F 25000 333445555 Ahmad Jabbar M 25000 987654321 4 James Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 DEPARTMENT DEPT LOCATIONS Dname Dnumber Mgr_san Mgr start_date Dnumber Dlocation Research 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 888665555 1981-06-19 5 Bellaire Sugarland 5 Houston WORKS ON PROJECT Essn Pno Hours Prame Pnumber Plocation Dnum 123456789 32.5 ProductX 1. Bellaire 123456789 2 7.5 ProductY Sugarland 5 666884444 3 40.0 Productz 3. Houston 453453453 20.0 Computerization 10 Stafford 453453453 20.0 Reorganization 20 Houston 333445555 2 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent_name Sex Relationship 1986-04-05 Daughter Bdate 999887777 30 30.0 333445555 Alice F 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 987987987 10 35.0 333445555 Joy 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner M. 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M. 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse AVE
Fantastic news! We've Found the answer you've been seeking!
Step by Step Answer:
Answer rating: 55% (9 reviews)
a For each department whose average employee salary is more than 300...View the full answer
Answered By
Bhartendu Goyal
Professional, Experienced, and Expert tutor who will provide speedy and to-the-point solutions. I have been teaching students for 5 years now in different subjects and it's truly been one of the most rewarding experiences of my life. I have also done one-to-one tutoring with 100+ students and help them achieve great subject knowledge. I have expertise in computer subjects like C++, C, Java, and Python programming and other computer Science related fields. Many of my student's parents message me that your lessons improved their children's grades and this is the best only thing you want as a tea...
3.00+
2+ Reviews
10+ Question Solved
Related Book For
Fundamentals Of Database Systems
ISBN: 9780133970777
7th Edition
Authors: Ramez Elmasri, Shamkant Navathe
Question Posted:
Students also viewed these Computer science questions
-
In SQL, specify the following queries on the database in Figure 5.5 using the concept of nested queries and other concepts described in this chapter. a. Retrieve the names of all employees who work...
-
In SQL, specify the following queries on the database specified in Figure 3.5 using the concept of nested queries and the concepts described in this chapter. a. Retrieve the names of all employees...
-
Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would...
-
How can we measure a security's contribution to the risk of the market portfolio?
-
For each of the following data sets, carry out an analysis of variance, including making a table of cell and marginal means and making a bar graph of the cell means. Use the .05 significance level....
-
Describe the purpose of each of the following parts of a manual system: a. Source document b. Turnaround document c. General ledger d. General journal e. Special journal f. Subsidiary ledger
-
Aldona Bartosz is a computer science student in her last semester. She just completed a class on how to build smartphone apps. The last day of class, Aldonas professor pulled her aside and told her...
-
Consumer Reports uses a survey of readers to obtain customer satisfaction ratings for the nations largest retailers (Consumer Reports, March 2012). Each survey respondent is asked to rate a specified...
-
You are planning a 30-day vacation in Malaysia in one year's time. The present charge for a luxury suite plus meals is RM760.00 per day and the RM is presently trading at RM 3.8000/$. Hence the $...
-
Create a public functional interface Validate with a method: public boolean validate name(String name); Create a public class ValidateUtility with the below methods: public static Validate...
-
Describe conceptually how an SQL retrieval query will be executed by specifying the conceptual order of executing each of the six clauses.
-
Discuss how each of the following constructs is used in SQL, and discuss the various options for each construct. Specify what each construct is useful for. a. Nested queries b. Joined tables and...
-
What is attribution theory? How can locus of control be applied to workers and managers? Discuss.
-
Given sides of length a, b, c, the area of a triangle may be calculated as follows (Heron's formula): s=(a+b+c)/2 Area-sqrt(s*(s-a)*(s-b)*(s-c)) Write a program called 'triangle. java' that asks the...
-
Trial Balance for the financial year (FY) ended 31st March 2022 of M/s Deepakshi shows following details: Particulars Debit (`) Credit (`) Purchase & Sales 10,00,000 12,00,000 Debtors & Creditors...
-
Demonstrate how each of the following transactions will affect the accounting equation (Assets = Capital + Liabilities). Your answer should include a summary total for each of Assets, Capital and...
-
In the model you created, which single metric tells you whether your model has any predictive validity? The adjusted R-squared The multiple R-squared The p-value corresponding to the F-statistic The...
-
Entombment (placing a casket in an above-ground crypt) of a wood casket is not a good idea. If families want to entomb, you should suggest to the client family that they purchase a metal casket. Why,...
-
Tandem with a Twist (a) Use the method of the previous problem to solve the systems (b) Explain how the calculation of part (a) can be used to solve the matrix equation For the unknown 3 ( 2 matrix...
-
Ball bearings are widely used in industrial applications. You work for an industrial food machinery manufacturer and your role is to design the driveshaft assembly on a new type of equipment that...
-
Let T be a tree with n positions. Define the lowest common ancestor (LCA) between two positions p and q as the lowest position in T that has both p and q as descendants (where we allow a position to...
-
Let the rank of a position p during a traversal be defined such that the first element visited has rank 1, the second element visited has rank 2, and so on. For each position p in a tree T, let...
-
We can define a binary tree representation T² for an ordered general tree T as follows (see Figure 8.21): ¢ For each position p of T, there is an associated position p² of T²....
-
Provide the information that the CEO requests as follows: A . List any invoices that were dropped during cleaning ( due to wrong format, etc. ) Use one row per invoice. Include invoice number,...
-
The owners of the USHL stated they can tolerate 5 - yrs of operations if their team is sustainable after that time. At the end of the 5 - yrs , which team ( s ) will cease operations? Which team ( s...
-
Asset A has an annual return of 10% and a standard deviation of 15%. Asset B has an annual return of 8% and a standard deviation of 10%. The portfolio consists of these two assets in different...
Study smarter with the SolutionInn App