Oracle Lab – Sql Practice Questions

1. what is the difference between delete, drop and truncate?

2. What is the varchar,varchar2 and char?

3. How will you check the table size and index size?

4. How will you check when the object is created?

5. What is difference between object and segment? Give examples.

6. Where will you check if my view is valid/invalid?

7. How will you compile a view?

8. Is it possible to create a view without table?

9. What is the size of view if the table size is 1gb?

10. What is public and private synonym?

11. What is sequence?

12. what is outer join?

13. List out the commands that does sorting?

14. print the sysdate in ’24/07/2010′ format;

15. print the sysdate in ’24-07-2010:23:00:00′ format;

16. create a table One as give below.

create table one (tdate date);
insert into one values(sysdate);
insert into one values(sysdate);
insert into one values(sysdate);
insert into one values(sysdate);

select * from one where tdate=sysdate;

select * from one where TRUNC(tdate)=TRUNC(sysdate);

17. Disable all primary and foreign keys keys, expain the steps;

18. How will you clean the schema?

19. Scenario:

We have table “CHILD” of 10M records. It has reference to another table “PARENT”. It has foreign key.
now we need to upload 10M records to CHILD, we are sure that all the new records are compatible with PARENT.
How will you load the data quickly ?
HINT: disable constraint., Load the data, enable constraint with no validate – if you dont have time for validate,
When time permits validate the constraint.

Explain all the steps.

20. Difference between union and union ALL?

21. Whan will you use MINUS & INTERSECT?

22. Print sysdate twice


23. Types of views?= Read only, simple, complex view.

24. What are object and system privileges, where will you check that.

25. Explain with grant option and with admin option.

26. How will you delete duplicate records?

27. What are pseudo columns. Name them.

28. How will increament sequence?

29. Create a table two;

create table two(a varchar2(10));

select func(a) from two;

what should be the func(a);

Hint : NVL, MAX

30. What is corelated subquery? Give example.