Write an SQL query that uses a multiplecolumn subquery in a

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\');

Solution

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\")