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

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_namelike(‘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%)