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%)
