Pages

Monday, April 30, 2012

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

No comments:

Post a Comment