17)Use SAVEPOINT commands

  SQL> select * 2 from department; DNAME DNO MGRSSN MGRSTARTD ——————– ———- ———- ——— Transport 2 1235 13-NOV-93 Packing 4 1236 17-DEC-95 Marketing 3 1237 14-FEB-96 Product 5 1238 30-JAN-97 Accounts 1 1234 30-OCT-98 sales 6 1239 04-MAR-92 6 rows selected. SQL> savepoint a; Savepoint created. SQL> insert into department 2 values('bank','7','1240','05-may-1998'); 1 row … Read more17)Use SAVEPOINT commands

Use Commit and Rollback Commands.

Commit:   SQL> select * 2 from publisher; NAME ADDRESS PHONE ————— ——————– ———- AJ PUBLISHER Kulai Mangalore 9472308100 AB Book Company Mangalore 9632587412 Ram Publishers Bangalore 9874563215 Raj Books Shringeri 8542658965 Hanuman Books Puttur DK 9458547562 SQL> insert into publisher 2 values('Guru Publisher','Sakaleshpur','9856475214'); 1 row created. SQL> commit; Commit complete. SQL> select * 2 … Read moreUse Commit and Rollback Commands.

Illustrate use of create view command and manipulating

  SQL> create view EMP as 2 select * 3 from employee 4 where salary>14000; View created. SQL> select * 2 from EMP; FNAME MINIT LNAME ——————– ——————– —————- SSN BDATE ADDRESS SEX ——————– ——— ——————– —— SALARY SUPER_SSN DNO ———- ——————– ———- Varun ganesh deva 0236 30-OCT-98 sediyapu puttur M 15000 1230 1 prjwal … Read moreIllustrate use of create view command and manipulating

Perform UPDATE, ALTER,DELETE,DROP operations on table.

Update:   SQL> select * 2 from publisher; NAME ADDRESS PHONE ————— ——————– ———- AJ PUBLISHER DARBE PUTTUR 9472308100 AB Book Company Mangalore 9632587412 Ram Publishers Bangalore 9874563215 Raj Books Shringeri 8542658965 Hanuman Books Puttur DK 9458547562 SQL> update publisher 2 set address='Kulai Mangalore' 3 where name='AJ PUBLISHER'; 1 row updated. SQL> select * 2 … Read morePerform UPDATE, ALTER,DELETE,DROP operations on table.

Query multiple tables using JOIN operation

1)Retrieve the details of dependent table of Fname on who is depending on and his name.   SQL> select fname,essn,dependent_name 2 from employee,dependent 3 where ssn=essn; FNAME ESSN DEPENDENT_NAME ——————– ——————– ————– Varun 0236 yash prjwal 4126 indrita Thilak 7894 diganth pranav 0662 radhika   2) Retrieve book_id,branch_id,no_of_copies of a book in ascending order of … Read moreQuery multiple tables using JOIN operation

SQL operators(AND, OR, LIKE, BETWEEN, IN)

BETWEEN and AND:   SQL> select * 2 from employee 3 where salary between 14000 and 15000; FNAME MINIT LNAME ——————– ——————– —————- SSN BDATE ADDRESS SEX ——————– ——— ——————– —— SALARY SUPER_SSN DNO ———- ——————– ———- Varun ganesh deva 0236 30-OCT-98 sediyapu puttur M 15000 1230 1 prjwal k raj 4126 13-DEC-93 jp nagara … Read moreSQL operators(AND, OR, LIKE, BETWEEN, IN)

Aggregate function in SQL (Count,Sum,Max,Min,Avg)

  SQL> select fname,lname,salary,dno,super_ssn 2 from employee; FNAME LNAME SALARY DNO —————- ——————– ———- ———- SUPER_SSN ——————– Varun deva 15000 1 1230 prjwal raj 14500 2 1240 pranav naik 14000 3 1250 FNAME LNAME SALARY DNO ————— ——————– ———- ———- SUPER_SSN ——————– Thilak naik 15000 4 1260 SQL> select sum(salary),max(salary),min(salary),avg(salary),count(salary) 2 from employee; SUM(SALARY) MAX(SALARY) … Read moreAggregate function in SQL (Count,Sum,Max,Min,Avg)

Grouping the result of query – GROUP BY clause and HAVING clause

1)select name address and salary of employee according to their dept name   SQL> select fname,lname,address,salary,dname 2 from employee e,department d 3 where e.dno=d.dno 4 group by dname,fname,lname,address,salary; FNAME LNAME ADDRESS SALARY ——————– ——————– ——————– ———- DNAME ——————– Varun deva sediyapu puttur 15000 Accounts pranav naik vijay nagar mysore 14000 Marketing Thilak naik beliyoorkatte puttur … Read moreGrouping the result of query – GROUP BY clause and HAVING clause

Query sorted- ORDER BY command

  Retrive the details of company database with respect to f_name,last_name and b_date in ascending order.     SQL> select Fname,Lname,Bdate 2 from employee 3 order by fname asc; FNAME LNAME BDATE ——————– ——————– ——— Thilak naik 30-AUG-96 Varun deva 30-OCT-98 pranav naik 01-NOV-92 prjwal raj 13-DEC-93     Retrive the details of dependent table … Read moreQuery sorted- ORDER BY command