Question: Write SQL query - based on the single-company database - For every dependent, find, and output, the dependent and the number of siblings (brothers and

Write SQL query - based on the single-company database - "For every dependent, find, and output, the dependent and the number of siblings (brothers and sisters) of the dependent who are also dependents of the same employee."

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

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

To answer the given question based on the provided database schema we need to identify the dependents of each employee and count how many other dependents are associated with the same employee Given the table DEPENDENT which contains ESSN Employee SSN DEPENDENTNAME SEX BDATE and RELATIONSHIP we can derive a solution SQL Query sql SELECT d1DEPENDENTNAME COUNTd2DEPENDENTNAME AS NUMOFSIBLINGS FROM DEPENDENT d1 JOIN DEPENDENT d2 ON d1ESSN d2ESSN AND d1DEPENDENTNAME d2DEPENDENTNAME GROUP BY d1DEPENDENTNAME Explanation We are using a selfjoin on the DEPENDENT table d1 and d2 to find siblings dependents who share the same employee The condition d1ESSN d2ESSN ensures that we are looking for dependents of the same employee The condition d1DEPENDENTNAME d2DEPENDENTNAME ensures that we do not count the dependent ... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

1 2 3 4 Notes: 5 6 7 8 9 The Power of Compounding Interest Who will have more money at age 65? Sarah's initial contribution is made at the beginning of age 19. Her last contribution is at age 26....

KXB family purchased a bicycle from the AZ store for their son. The facts were more clearly stated that it was more of a father buying a gift for his son. Applying the relevant section/principles of...

A borrower would like to borrower $66,000 to finance a property for 30 years at 16.1% interest. The lender indicates that loan fees (origination and discount points) equal to 3.9% of the loan amount...

Figure shows an overhead view of a ring that can rotate about its center like a merry-go-round. Its outer radius R2 is 0.800 m, its inner radius R1 is R2/2.00, its mass M is 8.00 kg, and the mass of...

Prepare a worksheet for Mannon Co. from the following information using Figure 11.9: a./b.Merchandise Inventory, ending.............15 Store Supplies on hand.............................4...

Change numbers in ordinary notation to scientific notation or change numbers in scientific notation to ordinary notation The escape velocity (the velocity required to leave the Earths gravitational...

Table 10.22 contains summary information on the fit of the best two logistic regression models based on a sample of ???? = 313 observations for models including ???? = 1, 2,, 7 explanatory variables....

Several years ago, Lowell, who is single, acquired 1244 stock in Blue Corporation at a cost of $60,000. He sells the Blue stock for $5,000 in the current year. Determine the amount and nature of...

Hello help please Assume that Nortel manufactures specialty electronic circuitry through a unique photoelectronic process. One of the primary products, Model ZX40, has a standard labor time of 0.5...

Overview The milestone for Project One involves applying accounting principles and methods to long-term liabilities and equity. You will also evaluate these financial statement components for...

Please no hand written answers. (10 points) Find the payment amount at in interest rate of 10%. 50 15 16 17 50 50 50 50 18 19 20 21 40 40 40 22 23 24 012 3 4 5 6 7 8 9 10 11 12 13 14 ? ? ? ? (50) (50)

Canada Importers is a Canadian firm based out of Mississauga Ontario. It was founded in 1979 and imports many different types of products. The company is looking to expand and has found that there is...

4. The data below pertains to the earnings of Juan Manuel for the week ended June 15, 2020: 3 points Hours and rate Earnings to date Overtime rate 42 hours worked at P40/hr P14.00 1 and times the...

Give the assumption or principle that would have been violated if the following entries or information had not been given: 1 . A owner of 3 different businesses wants to combine all of them into one...

huck and his employees have discussed redesigning the process to improve efficiency. Suppose they can increase the rate to 320 boxes per day. What will be their new productivity

Free-Response Questions 1. Suppose there are 100 residents on the island of Sunshine. Whenever someone purchases a palm tree to plant on the island, everyone on the island receives the same benefits...

what is the distinction between traditional evolutionary prototyping and open- source development?

Suppose you need to answer any four of seven essay questions on a history test and you can answer them in any order. a. How many different question combinations are possible? b. What is the...

Using your knowledge of the college environment, identify three transactions that might be handled by online transaction processing (OLTP) systems.

What is a method? What is a message? How do messages relate to methods?

When do you use a make-table query?

Healthy Hots, a fast- food restaurant that offers heart- healthy food, is experiencing several difficulties with operations. Although customers like the idea of heart- healthy foods, and surveys...

Eight work centers must be arranged in an L- shaped building. The locations of centers 1 and 3 are assigned as shown in the accompanying diagram. Assuming transportation costs are $ 1 per load per...

Develop a process layout that will minimize the total distance traveled by patients at a medical clinic, using the following information on projected departmental visits by patients and distance...