Untitled

Name:-
Div:-FY-IT.B
DATE:-25/11/2022
Subject:-DBMS
Roll-No:-
PC_NO:-


PRATICAL ASSIGMENT-5


SQL JOIN

WRITE a SQL query TO display an employee name, employee email AND department name USING employee AND department tables.
       SELECT emp_Name,Email_ID,Dept_Name
       FROM E_Detail INNER JOIN E_Dept ON
       E_Detail.Department = E_Dept.Dept_id;

       Output:

EMP_NAME    EMAIL_ID    DEPT_NAME
Rahul   [email protected] Sales
Priya   [email protected] Development
Isha    [email protected]  HR
Abhishek    [email protected]  Purchase
Neha    [email protected]  House Keeping


2.WRITE a SQL query TO display a employee email, employee contact no, department off USING employee AND department tables whose the department name IS HR.
SELECT emp_Name,Email_ID,Dept_Name
FROM E_Detail INNER JOIN E_Dept ON
E_Detail.Department = E_Dept.Dept_id
WHERE Dept_Name = 'HR';

Output :
EMP_NAME    EMAIL_ID    DEPT_NAME
Isha    [email protected]  HR


3.WRITE a SQL query TO retrieve an ALL the records FROM employee AND department tables.
SELECT *
FROM E_Detail INNER JOIN E_Dept ON
E_Detail.Department = E_Dept.Dept_id;


EMP_ID  EMP_NAME    DEPARTMENT  CONY_NO EMAIL_ID    EMP_HEAD_ID DEPT_ID DEPT_NAME   DEPT_OFF    DEPT_HEAD
104 Rahul   E-104   1234567890  [email protected] 105 E-104   Sales   Sunday  104
102 Priya   E-102   1234567890  [email protected] 103 E-102   Development Tuesday 101
101 Isha    E-101   1234567890  [email protected]  105 E-101   HR  Monday  105
105 Abhishek    E-105   1234567890  [email protected]  102 E-105   Purchase    Tuesday 102
103 Neha    E-103   1234567890  [email protected]  101 E-103   House Keeping   Saturday    103


4.WRITE a SQL query TO retrieve an employee name, employee salary, department id, contact no, employee email AND employee salary USING employee AND employee salary tables.
SELECT emp_Name,Department,Cony_No,Email_ID,Salary
FROM E_Detail INNER JOIN E_Salary ON
E_Detail.emp_id = E_Salary.E_id;


EMP_NAME    DEPARTMENT  CONY_NO EMAIL_ID    SALARY
Isha    E-101   1234567890  [email protected]  2000
Priya   E-102   1234567890  [email protected] 10000
Neha    E-103   1234567890  [email protected]  5000
Rahul   E-104   1234567890  [email protected] 1900
Abhishek    E-105   1234567890  [email protected]  2300


5.WRITE a SQL query TO display an employee name, contact no, email AND employee salary USING employee AND employee salary tables whose salary IS greater than 1000 AND whose employee name starts WITH ‘N’.


SELECT emp_Name,Department,Cony_No,Email_ID,Salary
FROM E_Detail INNER JOIN E_Salary ON
E_Detail.emp_id = E_Salary.E_id
WHERE (Salary > 1000) AND emp_Name LIKE 'N%';


EMP_NAME    DEPARTMENT  CONY_NO EMAIL_ID    SALARY
Neha    E-103   1234567890  [email protected]  5000


6.WRITE a SQL query TO display an employee email,employee contact no, employee salary USING Employee AND employee salary tables.
SELECT Cony_No,Email_ID,Salary
FROM E_Detail INNER JOIN E_Salary ON
E_Detail.emp_id = E_Salary.E_id;


CONY_NO EMAIL_ID    SALARY
1234567890  [email protected]  2000
1234567890  [email protected] 10000
1234567890  [email protected]  5000
1234567890  [email protected] 1900
1234567890  [email protected]  2300

7.WRITE a SQL query TO display a project duration, START YEAR, END YEAR AND employee id USING project AND employee project tables.
SELECT Duration,Start_Year,End_Year,E_ID
FROM Project INNER JOIN E_Project ON
Project.Project_ID = E_Project.Project_ID;


DURATION    START_YEAR  END_YEAR    E_ID
23  2010    2010    101
15  2010    2012    102
45  2013    -   103
2   2014    2015    104
30  2015    -   105


8.WRITE a SQL query TO display a START YEAR, END YEAR, employee id, project id, duration USING project AND employee project tables whose START YEAR FROM 2010.
SELECT Duration,Start_Year,End_Year,E_ID
FROM Project INNER JOIN E_Project ON
Project.Project_ID = E_Project.Project_ID
WHERE Start_Year = 2010;


DURATION    START_YEAR  END_YEAR    E_ID
23  2010    2010    101
15  2010    2012    102


9.WRITE a SQL query TO display an employee email, employee contact no, department off USING employee AND department tables whose the department name IS HR AND Development.

 SELECT Department,Cony_No,Email_ID
FROM E_Detail INNER JOIN E_Dept ON
E_Detail.Department = E_Dept.Dept_id
WHERE Dept_Name IN('HR','Development');


DEPARTMENT  CONY_NO EMAIL_ID
E-101   1234567890  [email protected]
E-102   1234567890  [email protected]


10.WRITE a SQL query TO find employee TABLE TO display an employee name, employee email.
SELECT emp_id,Email_ID
FROM E_Detail A,E_Detail B
WHERE A.emp_id = B.emp_id;


EMP_ID                                      EMAIL_ID
101                                     [email protected]
102                                     [email protected]
103                                     [email protected]
104                                     [email protected]
105                                     [email protected]