DBMS Tutorial-2

1. create table Job ( job_id varchar2 (15), job_title varchar2 (30) , min_sal number (7,2) , max_sal number (7,2));

   • insert into Job values('&job_id','&job_title','&min_sal','&max_sal');

2. create table Employee (emp_no number (3), emp_name varchar2 (30), emp_sal number (8,2), emp_comm number (6,1), dept_no number (3));

   • insert into Employee values ('01','&emp_name','&emp_sal','&emp_comm','&dept_no');

3. Create table deposit_gtu(a_no varchar2 (5),cname varchar2 (15),bname varchar2 (10),amount number (7,2), a_date date);

   • insert into deposit_gtu values('&a_no','&cname','&bname','&amount','&a_date');

4. Create table borrow_gtu(loanno varchar2 (5),cname varchar2 (15),bname varchar2 (10),amount number (7,2));

   • insert into borrow_gtu values('&loanno','&cname','&bname','&amount');

Queries

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

   • select * from Employee;
   • select * from Job;
   • select * from deposit_gtu;

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

   • select ACTNO,AMOUNT from DEPOSIT where ADATE BETWEEN '01-JAN-06' and '25-JUL-03';

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

   • select * 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 "salary of employee" 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 BETWEEN 10 and 20;