Database Management System (DBMS) - 2130703, (GTU) Gujarat Technological University Lab Manual Solution - 5

                                                       Practical – 5

Displaying data from Multiple Tables (join)

(1)    Give details of customers ANIL.

Select * from deposit d inner join customer c on d.cname=c.cname where c.cname=’anil’;

(2)    Give name of customer who are borrowers and depositors and having 

living city Nagpur.

Select b.cname from borrow b inner join deposit d on b.cname=d.cname where (b.bname=’vrce’ or b.bname=’ajni’)and(d.bname=’vrce’ or d.bname=’ajni’);

Note: Consider bname(vrce & ajni)which having city Nagpur at the place of city(Nagpur)

(3)    Give city as their city name of customers having same living branch.

SELECT B.CITY  “CITY NAME OF CUSTOMERS” FROM BRANCH B, CUSTOMERS C WHERE B.CITY=C.CITY;

(4)    Write a query to display the last name, department number, an department name for all employees.

SELECT e.last_name , e.department_id, d.department_name FROM Employee e , Department d WHERE e.department_id=d.department_id;

(5)    Create a unique listing of all jobs that are in department 30. Include the location of the department in the output.

 SELECT DISTINCT job_id, location_id FROM Employee, Department WHERE Employee.department_id=Department.department_id AND Emploee.department_id = 30 ;

(6)    Write a query to display the employee name, department number, and department name for all employees who work in NEW YORK.

SELECT e.last_name, e.job_id, e.department_id, d.department_name FROM Employee e JOIN department d ON (e.department_id=d.department_id) JOIN location l ON (d.location_id=l.location_id) WHERE  l.city= 'NEW YORK' ;

(7)    Display the employee last name and employee number along with their manager’s last name and   manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively.

SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#" FROM Employee w JOIN Employees m ON (w.manager_id = m.employee_id) ;

(8)    Create a query to display the name and hire date of any employee hired after employee SCOTT.

SELECT e.last_name, e.hire_date FROM employee e, employee SCOTT WHERE SCOTT.last_name='SCOTT' AND SCOTT.hire_date  <  e.hire-date ;

            OR

SELECT e.last_name, e.hire_date FROM Employee e JOIN Employee SCOTT ON (SCOTT.last_name = 'SCOTT' )  WHERE SCOTT.hire_date < e.hire_date;