Here we will understand how to write SQL Queries. The questions are as shown below.
5. WRITE A QUERY TO FIND THE NAMES OF THE EMPLOYEES WHO WORK FOR THE PROJECT PRODUCT X.
6. WRITE A QUERY TO FIND THE NAMES OF THE EMPLOYEES WHO WORK FOR THE PROJECT LOCATED IN HOUSTON.
These queries are based on our company database.
In this discussion, we will solve the 5th query and the 6th query is left as an exercise.
The link for the query and the output is provided at the last of this discussion.
The image below shows the table with all the data in the company database.
SQL Query 05:
WRITE A QUERY TO FIND THE NAMES OF THE EMPLOYEES WHO WORK FOR THE PROJECT PRODUCT X.
The query states that we have to fetch the names of the employees who work for the project 'PRODUCT X'.
For that to happen we have select 3 tables from the database. They are the employee, project, and works_on table.
Because the names of the employee are there in the employee table, the names of the projects are there in the project table and the projects in which the employees are working is there in the works_on table.
Now the identified tables will be associated with the FROM clause as shown below.
SELECT FROM employee E, project P, works_on W WHERE;
Now the conditions in the WHERE clause have to be identified.
The relationship between the project and the works_on table is done by the pnumber of the project and the pno of works_on.
Therefore the first condition is P.pnumber = W.pno.
By the execution of this condition, we will get the details of the projects which each employee is working on.
For example, choose the first row of the project and works_on the project numbers are the same, which means the employee 123456789 is working in Product X project.
From those, we have to choose the employees who work in the Product X project.
So the second condition is P.pname = 'Product X'.
By the execution of this condition, we will get the essn of the employees who work in the Product X project.
Using that essn we have to fetch the details of the employees in the employee table.
So the third condition is E.ssn = W.essn.
By the execution of this condition, we will get all the details of the employees working in the Product X project.
Now the identified conditions will be associated with the WHERE clause as shown below.
SELECT FROM employee E, project P, works_on W WHERE P.pnumber = W.pno AND P.pname = 'Product X' AND E.ssn = W.essn;
Now we have to choose columns.
We can get the names of the employees from E.Fname and E.Lname.
Finally, the query is SELECT E.fname, E.lname FROM employee E, project P, works_on W WHERE P.pnumber = W.pno AND P.pname = 'Product X' AND E.ssn = W.essn;
SQL Query 06:
WRITE A QUERY TO FIND THE NAMES OF THE EMPLOYEES WHO WORK FOR THE PROJECT LOCATED IN HOUSTON.
This query is similar to that of SQL Query 05.
Please try to solve the query on your own.
The link below contains the final query and the output.
Link for SQL Query 06:
Link for our website:
Follow us on Facebook @
Follow us on Instagram @
Follow us on Twitter @
Mail us @ learningmonkey01@gmail.com
0 Comments