Single row functions
1. select staff_name,Lpad(staff_sal,15,'$') sal from staff_masters;
2. select student_name,to_char(student_dob,'month,dd yyyy') from student_masters;
3. select staff_name,round(months_between(sysdate,hiredate)) months_employed from
staff_masters order by months_employed;
4. select* from staff_masters where to_char(hiredate,'mon')='jan' and
to_number(to_char(hiredate,'dd'))<15;
5. SELECT STAFF_NAME,STAFF_SAL
CASE
WHEN STAFF_SAL >=50000 THEN 'A'
WHEN STAFF_SAL >25000 AND STAFF_SAL<50000 THEN 'B'
WHEN STAFF_SAL >10000 AND STAFF_SAL<25000 THEN 'C'
ELSE 'D'
END CASE
FROM STAFFMASTER;
9 . select staff_name, hiredate ,to_char(hiredate,'day') day from staff_masters order by day;
Joins and subqueries
1. select s.staff_name,s.dept_code,d.dept_name,s.staff_sal from staff_masters s inner join
department_masters d on s.dept_code=d.dept_code where s.staff_sal>20000;
2. select s.staff_name,d.dept_name,s.staff_code,s.mgr_code from staff_masters s inner
join department_masters d on s.dept_code=d.dept_code ;
3. SELECT S.STUDENT_CODE,S.STUDENT_NAME,B.BOOK_CODE,BB.BOOK_NAME FROM
STUDENTMASTER S,BOOK_TRANSACTIONS B, BOOK_MASTER BB WHERE
S.STUDENT_CODE=B.STUDENT_CODE AND TO_CHAR(B.BOOK_EXPECTED_RETURN_DATE,'DD
MM YYYY') LIKE TO_CHAR(SYSDATE,'DD MM YYYY');
6. select staff_code,staff_name,staff_sal from staff_masters where staff_sal<(select avg(staff_sal)from
staff_masters);
7. select book_pub_author,book_name from book_masters where book_pub_author in(select book_pub_author from
book_masters group by book_pub_author having count(*)>1);
8. select s.staff_code,s.staff_name,d.dept_name from staff_masters s, book_transactions b,department_masters d
where s.dept_code=d.dept_code and s.staff_code=b.staff_code and s.staff_code in (select staff_code from
book_transactions group by staff_code having count(staff_code)>1);
99. select s.student_code,s.student_name, d.dept_code from student_masters s ,department_masters d where
d.dept_code=s.dept_code and s.dept_code in (select dept_code from department_masters where rownum=1 group
by dept_code ) ;
10. select s.staff_code,s.staff_name ,d.dept_name,dd.design_name ,months_between(sysdate,[Link])from
staff_masters s,department_masters d,designation_masters dd where s.dept_code=d.dept_code and
s.design_code=dd.design_code and months_between(sysdate,[Link])<3;
11. select mgr_code,count(*) from staff_masters group by mgr_code;
13. select d.dept_code,d.dept_name ,count(s.staff_name)from department_masters d,staff_masters s where
d.dept_code=s.dept_code group by d.dept_code,d.dept_name;
Data base objects
1. create table cust(customerid number(5),
cust_name varchar2(20),
address1 varchar2(30),
address2 varchar2(30));
Table created.
2. alter table cust rename column cust_name to customername ;
alter table cust modify customername varchar2(30) not null;
3. alter table cust add (gender varchar2(1),age number(3),phoneno number(10));
alter table cust rename to cust_table;
4. insert into cust_table values(1002, 'Becker', '#114 New York', '#114 New York', 'M', 45, 431525);
5 alter table cust_table add constraint custid_prim primary key(customerid);
7. alter table cust_table disable constraint custid_prim;
8. alter table cust_table drop constraint custid_prim;
10. truncate table cust_table;
11. alter table cust_table add(email varchar2(30));
12. alter table cust_table drop column email;