SQL

1.write a query to find the total number of rows in a table.

      Select count(*) from t_employee;

2. What is a transaction and ACID? 

  A transaction is a logical unit of work. All steps must be ACID – Atomicity, Consistency, lsolation and       Durability, these are the unique entitles of a transaction.

3. What is a Self join?

A Join created by joining two or more instances of a same table.

Query: select A.firstname, B. firstname from_employee A, t_employee B Where A. supervior_id=B.employee_id;

4- Write a query to insert a record into a table

Insert into t_employee values(‘empid35’,’Barack’,’Obama’);

Q5- Write a query to delete a record from a table

 delete from t_employee where id=’empid35’;

Q6- Write a query to display a row using serial number
For this, the indexed column of the table needs to be set as a parameter in the wher clause

Select*from t_employee where ag_id=’22’;

Q7- Write a query to fetch the highest record in a table, bases on a record, say salary field in the 

t_salary table

 Select max(salary) from t_ salary;

Q 8. Write a procedure in  Oracle to insert the record in a table?

create or replace PROCEDURE  INS_ABC (v_ag_code  IN  varchar2 ,v_agency_name  in varchar2) AS
Begin
insert into abc_master (abc_code,abc_name) values (v_abc_code,v_abc_name);
commit;
End ins_agency_master;

 Q 9 . How can run the above procedure ?

Execute INS_ABC  ('ABC','SUNEEL')

10. Delete duplicate rows

        DELETE FROM table_name 

               WHERE 

        rowid NOT IN (SELECT max(rowid) FROM table_name GROUP BY id);

12. To delete all the tables from user tables

Select 'Drop table '||table_name||' cascade constraints;' from user_tables;

13. To create table as on another table but only structure not data.

               Create table table_name2 as(select * from table_name1 where 1=2);

14. Find the highest nth salary in the table.

        select * from emp e1 where (3-1) = (select count(distinct

(e2.sal)) from emp e2 where e1.sal>e2.sal);

15.   Display the records between two range 

        select rownum, empno, ename  from emp  where  rowid in 

        (select rowid from emp where rownum <=&upto 

        minus 

        select rowid from emp where rownum<&Start); 

Enter value for upto: 10 

Enter value for Start: 7

16. DISPLAY   TOP 3 SALARIES FROM EMP;

                     SELECT SAL FROM ( SELECT  * FROM EMP ORDER  BY SAL DESC ) WHERE ROWNUM  <4;

17. TO DISPLAY 5 TO 7 ROWS FROM A TABLE

             select ename from emp

         where rowid in(select rowid from emp where rownum<=7

         minus

          select rowid from empi where rownum<5)