Basic SQL questions

1. Difference between DCL and DML  commands?

DCL  commands  will   commit  implicitly   where  in  DML  commands  we  have   to commit explicitly.

2. Table, Rowid, Rownum

Table is a database object, which is having more than one column associated with its data type.

Rowid is the unique binary address of the row in a table

Rownum it  is a temporary  number  in a memory  and  was  assigned  to each  row selected by the statement.

3. Pseudo-Columns

Columns  that are not created explicitly by  the user and can be used  explicitly in queries.  The pseudo-Columns  are rowid,  rownum, currval,  nextval,  sysdate, and level

4. What is a View?

View is Virtual Table, which hold the data at runtime

5. Difference between ordinary view and complex view?

Simple  views  can be  modified  easily  and these  cant hold  the data where   as complex views  cant be modified  directly  and  complex view can  hold  the  data  to modify a complex view we have to use INSTEAD OF TRIGGERS.

6. Forced view

Creating a view on a table, which is not there  in the database.

7. Inline view

Inline view is basically  a subquery  with  an alias  that  you  can  use  like a  view inside a SQL statement.

8. What is an INDEX and what are the types?

INDEX is a database object used in oracle to provide quick access to  rows.

 9. Synonym

Synonym  is an  alias  name for any database  object like tables,  views,  functions, procedures.

10. SELECT statement syntax?

SELECT    From

Where

Group by 

Having

Connect prior

11. What is Constraint? Different Constraints?

Constraints   are  representators  of   the  columns   to  enforce  data  entity   and consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.

12. Difference between Primary key and Unique + Not Null constraints?

Unique + Not Null is a combination  of  two constraints and we can use more  than one Unique + Not Null in any table. Primary Key is a single constraint we can use only one time for a table. It can be a referential key for any column in any table.

13. What is NULL?

Default Value.

14. Dual Table

It is a one row, one column table with value X.

15. Difference between Truncate and Delete?

Truncate will delete all the rows from  the  table  without any condition.  It will commit automatically when it fires Where delete will delete all or specified  rows based upon the condition here we have to commit explicitly.

16.Difference between Char and Varchar2?

Varchar2  is similar to char but can store available number of characters and while querying  the table  varchar2 will  trims  the extra spaces and fetches  the rows that exactly match the criteria.

17.Difference between LOB  and LONG data types?

The maximum size of  an LOB is 4GB. It will support random access to data where in LONG maximum size is 2GB. It will support sequential  access to data.

18.Single Row functions: It will work on single row and give result for all the rows.

Ex: to_char, to_date etc.

19.Group Functions: It will work on group of  rows in a table  and gives  a single row result. Ex: Sum(), Avg(), min(), max().. Etc.

20.String Handling Functions?

Instr   –  it   returns the  position   of   the string  where  it   occur  according   to   the parameters.

Instrb – instr and instrb returns same but in the form of bytes.

Substr – It returns the portion  of  a string depending on the parameters from and to.

Substrb – Substr and Substrb returns the same thing  but Substrb returns in  the form of bytes

21.Sign: Sign is a function it will take numbers, as inputs and it will give

                                       i.   1 for positive integer

ii.    -1 for negative integer

 iii.    0 for ZERO

SQL> Select sign(-1234) from dual;                                                                      O/P: -1

22.Differences between UNION and UNION ALL?

Union:  The values  of  the first  query are returned with  the values  of  the  second query eliminating the duplicates.

Union All: The values of the first query are returned with the values of the second query including the duplicates.

23.Difference between NVL and NVL2 functions?

NVL  is used  to  fill  a NULL  value  to known value.  NVL2  will  identify  the  NULL values  and Filled values  it  returns exp3 if it  is null otherwise it  returns  exp2. We have to pass 3 parameters for NVL2 and 2 parameters for NVL.

24.How can we compare range of values with out using the CASE?

By using Decode with in Decode.

25.Can we Decode with in a Decode?

YES

26.Decode and Case Difference?

Case compares a Range of  values and Decode will work as if else statement.

27.Difference between Replace and Translate?

Replace  is  used  to  replace   the whole  string   and  we can  pass  null  values  in replace.  Translate  is used  to  translate  character-by-character here we  have to pass the three parameters.

28.Difference between where and having clause?

Where used  to specify  condition  and used to restrict  the data. Having  used  to specify the condition on grouped results and used to filter the data.

29.Difference between IN and EXISTS clause?

EXISTS gives the status of  the  inner query.  If the  inner  query  is success  then  it returns true other wise it returns false and IN will compare the list of values.

30.Difference between subquery and correlated subquery?

Query with  in a query  is subquery.  Inner query will  executes first  and based  on the result  the outer query will  be displayed.  Correlated  subquery outer  query will executes first and then inner query will be executed.