Table Name:- Employee Empid EmpName Department ContactNo EmailId EmpHeadId 101 Isha E-101 1234567890 isha@gmail.com 105 102 Priya E-104 1234567890 priya@yahoo.com 103 103 Neha E-101 1234567890 neha@gmail.com 101 104 Rahul E-102 1234567890 rahul@yahoo.com 105 105 Abhishek E-101 1234567890 abhishek@gmail.com 102 Table :- EmpDept DeptId DeptName Dept_off DeptHead E-101 HR Monday 105 E-102 Development Tuesday 101 E-103 Hous Keeping Saturday 103 E-104 Sales Sunday 104 E-105 Purchage Tuesday 104 Table :- EmpSalary EmpId Salary IsPermanent 101 2000 Yes 102 10000 Yes 103 5000 No 104 1900 Yes 105 2300 Yes Table :- Project ProjectId Duration p-1 23 p-2 15 p-3 45 p-4 2 p-5 30 Table :- Country cid cname c-1 India c-2 USA c-3 China c-4 Pakistan c-5 Russia Table :- ClientTable ClientId ClientName cid cl-1 ABC Group c-1 cl-2 PQR c-1 cl-3 XYZ c-2 cl-4 tech altum c-3 cl-5 mnp c-5 Table :- EmpProject EmpId ProjectId ClientID StartYear EndYear 101 p-1 Cl-1 2010 2010 102 p-2 Cl-2 2010 2012 103 p-1 Cl-3 2013 104 p-4 Cl-1 2014 2015 105 p-4 Cl-5 2015 through these tables make a following qeuries by using nested quries like join: 1. Select the department name of the company which is assigned to the employee whose employee id is grater 103. 2. Select the name of the employee who is working under Abhishek. 3. Select the name of the employee who is department head of HR. 4. Select the name of the employee head who is permanent. 5. Select the name and email of the Dept Head who is not Permanent. 6. Select the employee whose department off is monday 7. select the indian clinets details. 8. select the details of all employee working in development department.
Table Name:- Employee
Empid |
EmpName |
Department |
ContactNo |
EmailId |
EmpHeadId |
101 |
Isha |
E-101 |
1234567890 |
isha@gmail.com |
105 |
102 |
Priya |
E-104 |
1234567890 |
priya@yahoo.com |
103 |
103 |
Neha |
E-101 |
1234567890 |
neha@gmail.com |
101 |
104 |
Rahul |
E-102 |
1234567890 |
rahul@yahoo.com |
105 |
105 |
Abhishek |
E-101 |
1234567890 |
abhishek@gmail.com |
102 |
Table :- EmpDept
DeptId |
DeptName |
Dept_off |
DeptHead |
E-101 |
HR |
Monday |
105 |
E-102 |
Development |
Tuesday |
101 |
E-103 |
Hous Keeping |
Saturday |
103 |
E-104 |
Sales |
Sunday |
104 |
E-105 |
Purchage |
Tuesday |
104 |
Table :- EmpSalary
EmpId |
Salary |
IsPermanent |
101 |
2000 |
Yes |
102 |
10000 |
Yes |
103 |
5000 |
No |
104 |
1900 |
Yes |
105 |
2300 |
Yes |
Table :- Project
ProjectId |
Duration |
p-1 |
23 |
p-2 |
15 |
p-3 |
45 |
p-4 |
2 |
p-5 |
30 |
Table :- Country
cid |
cname |
c-1 |
India |
c-2 |
USA |
c-3 |
China |
c-4 |
Pakistan |
c-5 |
Russia |
Table :- ClientTable
ClientId |
ClientName |
cid |
cl-1 |
ABC Group |
c-1 |
cl-2 |
PQR |
c-1 |
cl-3 |
XYZ |
c-2 |
cl-4 |
tech altum |
c-3 |
cl-5 |
mnp |
c-5 |
Table :- EmpProject
EmpId |
ProjectId |
ClientID |
StartYear |
EndYear |
101 |
p-1 |
Cl-1 |
2010 |
2010 |
102 |
p-2 |
Cl-2 |
2010 |
2012 |
103 |
p-1 |
Cl-3 |
2013 |
|
104 |
p-4 |
Cl-1 |
2014 |
2015 |
105 |
p-4 |
Cl-5 |
2015 |
through these tables make a following qeuries by using nested quries like join:
1. Select the department name of the company which is assigned to the employee whose employee id is grater 103.
2. Select the name of the employee who is working under Abhishek.
3. Select the name of the employee who is department head of HR.
4. Select the name of the employee head who is permanent.
5. Select the name and email of the Dept Head who is not Permanent.
6. Select the employee whose department off is monday
7. select the indian clinets details.
8. select the details of all employee working in development department.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps