Write an SQL query that uses a multiple-column subquery in a FROM clause. Explai

Question

Write an SQL query that uses a multiple-column subquery in a FROM clause. Explain what the query is intended to do.

The data to be used is below.

CREATE TABLE department
(
dep_Id int NOT NULL,
depart_Name varchar(255) NOT NULL,
manager varchar(255),
PRIMARY KEY (dep_ID));

insert into department(dep_Id, depart_Name,manager ) values ('1', 'Computer Science', 'Suresh');
insert into department(dep_Id, depart_Name,manager ) values ('2', 'Chemistry', 'Kumar');
insert into department(dep_Id, depart_Name,manager ) values ('3', 'Biology', 'Murapaka');
insert into department(dep_Id, depart_Name,manager ) values ('4', 'English', 'Geethanshu');

CREATE TABLE employee
(
emp_ID int NOT NULL,
dept_code int NOT NULL,
fname varchar(255) NOT NULL,
lname varchar(255) NOT NULL,
DOB varchar(255) NOT NULL,
manager_id int NOT NULL,
hiredate varchar(255) NOT NULL,
classification varchar(255),
PRIMARY KEY (emp_Id));

insert into employee (emp_id, dept_code, fname, lname, DOB, manager_id, hiredate, classification) values('100', '1', 'Suresh', 'Kumar', '21-09-1987', '2', '21-09-2010','A');
insert into employee (emp_id, dept_code, fname, lname, DOB, manager_id, hiredate, classification) values('101', '2', 'Sekhar', 'Chadra', '21-09-1988', '1', '21-09-2011','B');
insert into employee (emp_id, dept_code, fname, lname, DOB, manager_id, hiredate, classification) values('102', '3', 'Varalaxmi', 'Murapaka', '21-09-1997', '4', '21-09-2012','C');
insert into employee (emp_id, dept_code, fname, lname, DOB, manager_id, hiredate, classification) values('103', '4', 'Anshu', 'Murapak', '21-09-2007', '3', '21-09-2014','D');

CREATE TABLE projects
(
prj_Id int NOT NULL,
Project_name varchar(255) NOT NULL,
Start_date varchar(255) NOT NULL,
End_date varchar(255) NOT NULL,
Critiera_level varchar(255),
PRIMARY KEY (prj_ID));

insert into projects(Prj_ID, Project_name, Start_date, End_date, Critiera_level) values('1000','CSC','01-01-2016','01-12-2016','A');
insert into projects(Prj_ID, Project_name, Start_date, End_date, Critiera_level) values('1002','Protocol','01-01-2015','01-12-2015','B');
insert into projects(Prj_ID, Project_name, Start_date, End_date, Critiera_level) values('1003','Port','01-01-2014','01-12-2014','C');
insert into projects(Prj_ID, Project_name, Start_date, End_date, Critiera_level) values('1004','Domain','01-01-2013','01-12-2013','D');

CREATE TABLE salaried_emp
(
Emp_Id int NOT NULL,
Prj_ID int NOT NULL,
Yearly_salary varchar(255),
PRIMARY KEY (emp_Id),
CONSTRAINT FK_PROJECTS FOREIGN KEY (prj_Id) REFERENCES
projects (prj_Id));

insert into salaried_emp(Emp_Id, Prj_ID, Yearly_salary) values ('100', '1000','100000');
insert into salaried_emp(Emp_Id, Prj_ID, Yearly_salary) values ('104', '1004','140000');
insert into salaried_emp(Emp_Id, Prj_ID, Yearly_salary) values ('102', '1002','120000');
insert into salaried_emp(Emp_Id, Prj_ID, Yearly_salary) values ('103', '1003','130000');

CREATE TABLE hourly_emp
(
Emp_Id int NOT NULL,
Hourly_pay int,
PRIMARY KEY (emp_Id),
CONSTRAINT FK_employee FOREIGN KEY (emp_Id) REFERENCES
employee (emp_Id));

insert into hourly_emp(Emp_Id, Hourly_pay) values ('100', '500');
insert into hourly_emp(Emp_Id, Hourly_pay) values ('101', '600');
insert into hourly_emp(Emp_Id, Hourly_pay) values ('102', '700');
insert into hourly_emp(Emp_Id, Hourly_pay) values ('103', '800');

Explanation / Answer

1. The query will return First name and last name of all hourly based employees

select fname, lname from employee where emp_id IN (select emp_id from hourly_emp)

2. The query will return firstname and last name of all employees working in Computer Science oe English department

select fname, lname from employee where dept_code in (select dept_id from department where depart_name="Computer Science" or depart_name="English")

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.