Database Management ,(GTU)Gujarat Technological University Lab Manual Solution Practical-2

 Database Management ,(GTU)Gujarat Technological University Lab Manual Solution

Practical-2 

Perform following queries

(1)  Retrieve all data from employee, jobs and deposit.

Select * from employee;

Select * from jobs;

Select * from deposit;

(2) Give details of account no. and deposited rupees of customers having account opened between dates 01-01-06 and 25-07-06.

Select a_no,amount from deposit  where to_date(d_date,'DD/MM/YYYY') 

BETWEEN TO_DATE('01-01-06 ', 'DD-MM-YYYY')AND TO_DATE('25-07-06', 'DD-MM-YYYY');

(3)  Display all jobs with minimum salary is greater than 4000.

Select min(min_sal) from job where min_sal>4000;

(4) Display name and salary of employee whose department no is 20. Give alias name to name of employee.

Select emp_name “Name of Employee” ,emp_sal from employee where dept_no =20

(5)Display employee no,name and department details of those employee whose department lies in(10,20)

Select emp_no,emp_name ,dept_no from employee where dept_no in(10,20)

To study various options of LIKE predicate

(1)Display all employee whose name start with ‘A’ and third character is ‘ ‘a’.

Select * from employee where emp_name Like(‘A_a%’)

    (2) Display name, number and salary of those employees whose name is            5 characters long and first three characters are ‘Ani’.

     Select emp_name,emp_no,emp_sal where emp_name Like (‘Ani__’)

    (3) Display the non-null values of employees and also employee name              second character should be ‘n’ and string should be 5 character long.

 Select emp_name from employee where emp_name IS NOT NULL AND emp_name LIKE (_n%___)

    (4) Display the null values of employee and also employee name’s third             character should be ‘a’.

Select emp_name from employee where emp_nameIS  NULL AND emp_name LIKE (__a%)

Database Management ,(GTU)Gujarat Technological University Lab Manual Solution For Practical - 5

Database Management ,(GTU)Gujarat Technological University Lab Manual Solution

                                                       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, and 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;