Home How to retrieve name of the name of the employees spouse if they have one? SQL

Questions

How to retrieve name of the name of the employees spouse if they have one? SQL

August 31, 2022

I have everything else other than the spouse’s name and put my code and the question below.

For each employee who works for the ‘Research’ department and his/her salary is higher than $30,000, list the employee’s name, salary, and spouse’s name.

This is my code so far.

select fname, lname, dname, salary 
from employee, department 
where dname='Research' 
and dnumber=dno 
and salary >30000;

and this is the table we are pulling data from.

CREATE TABLE DEPT_LOCATIONS 
    (DNUMBER INT NOT NULL , 
     DLOCATION VARCHAR(15) NOT NULL , 
     PRIMARY KEY (DNUMBER, DLOCATION)) ;
 
Insert into dept_locations values('1', 'Houston');
Insert into dept_locations values('4', 'Stafford');
Insert into dept_locations values('5',  'Bellaire');
Insert into dept_locations values('5', 'Sugarland');
Insert into dept_locations values('5', 'Houston');
 
CREATE TABLE DEPARTMENT 
   (DNAME VARCHAR(15) NOT NULL , 
   DNUMBER INT NOT NULL , 
   MGRSSN CHAR(9) NOT NULL , 
   MGRSTARTDATE DATE , 
   PRIMARY KEY (DNUMBER) , 
   UNIQUE (DNAME) );

Insert into department values('Research','5', '333445555', to_date('05/22/1988', 'mm/dd/yyyy'));
Insert into department values('Administration','4','987654321',to_date('01/01/1995',  
'mm/dd/yyyy'));
Insert into department values('Headquarters','1','888665555',to_date('06/19/1981', 'mm/dd/yyyy'));
 
CREATE TABLE EMPLOYEE 
(FNAME CHAR(15) NOT NULL ,
MINIT CHAR, 
LNAME VARCHAR(15) NOT NULL , 
SSN CHAR(9) NOT NULL , 
BDATE DATE,
ADDRESS VARCHAR(30) , 
SEX CHAR, 
SALARY DECIMAL(10,2) , 
SUPERSSN CHAR(9) , 
DNO INT NOT NULL , 
PRIMARY KEY (SSN) ,
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ) ; 
 
-- FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) ,
 
Insert into employee values('John','B','Smith','123456789', to_date('01/09/1955','mm/dd/yyyy'), '731 Fondren, Houston, TX','M',  30000.00, '333445555', '5');
Insert into employee values ('Franklin','T','Wong','333445555', to_date('12/08/1945', 'mm/dd/yyyy'), '638 Voss, Houston, TX','M', 40000.00, '888665555','5');
Insert into employee values ('Alicia','J','Zeleya','999887777',to_date('07/19/1958','mm/dd/yyyy'),'3321 Castle, Spring, TX','F', 25000.00,'987654321','4');
Insert into employee values('Jennifer','S','Wallace','987654321', to_date('06/20/1931' , 'mm/dd/yyyy'),'291 Berry, Bellaire, TX','F',43000.00,'888665555','4');
Insert into employee values('Ramesh','K','Narayan','666884444', to_date('09/15/1952', 'mm/dd/yyyy'),'975 Fire Oak, Humble, TX','M', 38000.00,'333445555','5');
Insert into employee values('Joyce','A','English','453453453', to_date('07/31/1962', 'mm/dd/yyyy'),'5631 Rice, Houston, TX','F', 25000.00,'333445555','5');
Insert into employee values('Ahmad','V','Jabbar ','987987987', to_date('03/29/1959', 'mm/dd/yyyy'),'980 Dallas, Houston, TX', 'M',25000.00,'987654321','4');
Insert into employee values('James','E','Borg','888665555', to_date('11/10/1929', 'mm/dd/yyyy'),'450 Stone, Houston, TX', 'M','55000.00',null,'1');

Create table project
(Pname char(15) not null, 
pnumber int  primary key,
plocation char(15),
dnum int references department(dnumber));
        
insert into project values('ProductX','1','Bellaire','5');
insert into project values('ProductY','2 ','Sugarland ','5');
insert into project values('ProductZ','3','Houston','5');
insert into project values('Computerization','10','Stafford','4');
insert into project values('Reorganization','20','Houston','1');
insert into project values('Newbenefits','30','Stafford','4');

create table WORKS_ON
(essn char(9),
 pno  number references project(pnumber),
 hours decimal(5,2),
 primary key (essn, pno));

insert into works_on values('123456789','1',32.50);
insert into works_on values( '123456789','2',7.50);
insert into works_on values( '666884444','3',40.00);
insert into works_on values( '453453453','1',20.00);
insert into works_on values( '453453453','2 ',20.00);
insert into works_on values( '333445555','2 ',10.00);
insert into works_on values( '333445555','3 ',10.00);
insert into works_on values( '333445555','10 ',10.00);
insert into works_on values( '333445555','20 ',10.00);
insert into works_on values( '999887777','30 ',30.00);
insert into works_on values( '999887777','10 ',10.00);
insert into works_on values( '987987987','10 ',35.00);
insert into works_on values( '987987987','30 ',5.00);
insert into works_on values( '987654321','30 ',20.00);
insert into works_on values( '987654321','20 ',15.00);
insert into works_on values( '888665555','20 ',null);

create table dependent
(essn char(9) references employee(ssn),
 dependent_name char(10),
 sex char,
 bdate date,
 relationship char(10),
 primary key (essn, dependent_name));

insert into dependent values('333445555','Alice','F', 
to_date('04/05/1976','mm/dd/yyyy'),'Daughter');
insert into dependent  values('333445555','Theodore','M', to_date('10/25/1973', 
'mm/dd/yyyy'),'Son');
insert into dependent values('333445555','Joy','F', to_date('05/03/1948', 'mm/dd/yyyy'),'Spouse');
insert into dependent values('987654321','Abner','M', to_date('02/29/1932', 
'mm/dd/yyyy'),'Spouse');
insert into dependent values('123456789','Michael','M', to_date('01/01/1978', 
'mm/dd/yyyy'),'Son');
insert into dependent values('123456789','Alice','F', to_date('12/31/1978', 
'mm/dd/yyyy'),'Daughter');
insert into dependent values('123456789','Elizabeth','F', to_date('05/05/1957', 
'mm/dd/yyyy'),'Spouse');
commit;

>Solution :

LEFT OUTER JOIN to the dependent table using the Employee’s SSN and only include the rows where the dependent is the spouse.

SELECT fname, lname, dname, salary, dependent_name
FROM   employee e
       INNER JOIN department d
       ON (d.dnumber = e.dno)
       LEFT OUTER JOIN dependent p
       ON (e.ssn = p.essn AND p.relationship = 'Spouse    ')
WHERE  dname='Research' 
AND    salary >30000;

Note: you should use VARCHAR2 data types for variable-length strings such as names and relationships and not fixed-length CHARs.

db<>fiddle here