Question

ONE SQL STATEMENT for "Retrieve the social security numbers of all direct and indirect supervisees James...

ONE SQL STATEMENT for "Retrieve the social security numbers of all direct and indirect supervisees James E Borg has"

NAME                SSN       BDATE     SEX     SALARY SUPERSSN DNO           
------------------- --------- --------- --- ---------- --------- --------      
John B Smith        123456789 09-JAN-55 M        30000 333445555 5             
Franklin T Wong     333445555 08-DEC-45 M        40000 888665555 5             
Alicia J Zelaya     999887777 19-JUL-85 F        25000 987654321 4             
Jennifer S Wallace 987654321 20-JUN-31 F        43000 888665555 4              
Ramesh K Narayan    666884444 15-SEP-52 M        38000 333445555 5             
Joyce A English     453453453 31-JUL-62 F        25000 333445555 5             
Ahmad V Jabbar      987987987 29-MAR-59 M        25000 987654321 4             
James E Borg        888665555 10-NOV-27 M        55000           1             

ONE SQL STATEMENT for "Retrieve the social security numbers of all direct and indirect supervisees James E Borg has"

0 0
Add a comment Improve this question Transcribed image text
Answer #1

Select ssn from employee where superssn in(select ssn from employee where name='James E Borg') union select e.ssn from employee e where e.superssn in(select e2.ssn from employee e2 where e2.superssn in(select e3.ssn from employee e3 where e3.name='James E Borg'));

This contains the sub queries the first line gives the result of the direct supervisees and the 2 and 3 lines will gives the indirect supervisees and the union operator between this will combine the both tables and gives as 1 output.

Add a comment
Know the answer?
Add Answer to:
ONE SQL STATEMENT for "Retrieve the social security numbers of all direct and indirect supervisees James...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compar...

    Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compare with your solution to make sure I did it correctly. Thank you very much! LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees...

  • Use the tables in the next page to answer the following questions in SQL. 1. Print...

    Use the tables in the next page to answer the following questions in SQL. 1. Print the names of employees who work in a project located in Houston. 2. Print the names of employees with no dependents. 3. Print the names of employees who are managers. 4. Print the names of employees who have more than one wife. 5. Print the names of employees who work in all the projects. EMPLOYEE Frame John Franklin Minit B T S K A...

  • (SELECT pnumber FROM project, department, employee WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith') UNION (SELECT pnumber FROM...

    (SELECT pnumber FROM project, department, employee WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith') UNION (SELECT pnumber FROM project, works_on, employee WHERE pnumber=pno AND essn=ssnAND lname='Smith') Can you draw the relation step by step , how to solve it ?! Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 Ssn Fname Minit Lname John Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TXM40000...

  • Search all employee who work total on all project less than 40 hrs from works_on table....

    Search all employee who work total on all project less than 40 hrs from works_on table. Please give me a query for this. Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 9876543214 JenniferSWallace 9876543211941-06-20...

  • Q2. Retrieve the names of all employees from the employee table to produce output on CSV...

    Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...

  • Oracle SQL Assignment 4 - List the name of supervisor who has more supervisee than other...

    Oracle SQL Assignment 4 - List the name of supervisor who has more supervisee than other supervisors. The supervisor would be the Mgr_ssn from the Department table. Each employee from the Employee table has a Super_ssn which would be their supervisor. 1 to this PC Picture Tools ew Help Acrobat Storyboarding FormatTell me what you want to de Picture Bonde A Bring Send Selection Tent Pictu楩Layout . | | Forward. Backward . Pane Figure 3.6 One possibie database state for...

  • write the following queris in sql : a) Using nested query , retreive the sependent names...

    write the following queris in sql : a) Using nested query , retreive the sependent names of male employees whose salary > 30000 b ) list the female employee, who are not managers EMPLOYEE Fname MinitLname John Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 4 Ssn Sex Salary Super sen Dno B Smith 123456789 1965 01-09 731 Fondren, Houston, TX M 30000 13334455 AliciaZelaya 999987777 1968-01-19 3321 Castle, Spring, TXF Jennifer S Wallace 987654321 1941-06-20 291 Berry,...

  • Query 1: Retrieve names of all the projects as well as First and Last name of...

    Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...

  • Book: Fundamental of database systems Chapter 5 exercise 5.9 Figure 3-6 here provides examples on how...

    Book: Fundamental of database systems Chapter 5 exercise 5.9 Figure 3-6 here provides examples on how to answer this question: Photos - 20190906_111020[3691].jpg -ox See all photos + Add to Edit & Create Share : Q u ♡ 2 # d. A view that has the project name, controm of employees, and total hours worked per week on the project for each project with more than one employee working on it. 5.9. Consider the following view, DEPT SUMMARY, defined on...

  • drop table department cascade constraints; create table department ( Dname   varchar2(15)   not null, Dnumber int   not...

    drop table department cascade constraints; create table department ( Dname   varchar2(15)   not null, Dnumber int   not null, Mgr_ssn   char(9)   not null, mgr_start_date   Date, primary key (Dnumber), Unique    (Dname)); insert into DEPARTMENT values ('Research', '5', '333445555', '22-May-1988'); insert into DEPARTMENT values ('Administration', '4', '987654321', '01-Jan-1995'); insert into DEPARTMENT values ('Headquarters', '1', '888665555', '19-Jun-1981'); drop table employee cascade constraints; create table employee ( Fname   varchar2(15) not null, Minit   char(1), Lname   varchar2(15) not null, Ssn   char(9), Bdate   date, Address   varchar2(30), Sex   char(1),...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT