Sql questions

ROWID: Every record in a database is uniquely identified by system generated value called Rowed. It is a 18 character hexadecimal value. These Rowid's are physically existence.
ROWNUM: It is a pseduocolumn which generates the sequence of numeric values based on the position of the records in the output. These ROWNUM'S logically generated.

NEXTVAL,CURRVAL,SYSDATE,LEVEL ARE PSEDUOCOLUMNS

What is  Set Transaction
Set Transaction is to establish properties for the current transaction.
What is a character functions
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
What is date function
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.

What is a cascade constraint?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

what is spool command

spool command used for printing the out put of the sql statments in a file. Eg.

spool /tmp/sql_out.txt
select emp_name, emp_id from emp where dept='sales';
spool off;

we can see the out on /tmp/sql_out.txt file.

SUB QUERY VS JOIN

Sub query is use to retrieve data which have multiple conditions.
Join is use to give joint to 2 different table using a same field of different table it will retrieve data from two different table.

Which date function returns number value?

Months_between This date function takes 2 valid dates and returns number of months in between them.

Diff between synonym and view

Synonyms and Views are totally different. A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects,

Or

View is a virtual table

·         It can be created on a table or another view.

·          It is just like a window through which we can access or change base table data.

·         It does contain data of its own. It always takes data from its base table.

·         It is stored as a query in data dictionary.Whenever you query a view it gets data from its based table using this query.

    Main advantage of using views

·         You can ristrict access to predetermined set of rows and columns of a table

·          You can hide complexity of query

·         You can hide complexity of calculation

Synonym is alternate name given to table, view, sequence or program unit.

·         It is used to mask real name and owner of the object.

·          You can provide public access to tables by creating public synonym’s.

·         A view is the name for a stored query.

Advantages of view:

1. Restricts the access to particular columns and rows of the base tables.
2. Hide data complexity

3. Can access the data for two different base tables with out performing a join

4. Can display the data in different form from the base tables.(i.e. In the column names can can be changed with effecting the column names of the base tables).

Or

Synonym is representation of a table. it has two subdivision namely private and public. public synonym can be created only from the DBA user which can be accessed by all the user but private synonym cannot be accessed by all the user

Major difference between the synonym and view is that any synonym can represent only one table and cannot represent a result of a sub query but the view can represent even a sub query.

What is integrity constraint?

Integrity constraint is a rule that restricts values to a column in a table.

To see the pagesize

Show pagesize

To retrieve  the drop table

Flashback table emp to before drop;

To create same table records with different table name

create table t2 as select *from emp;

What is difference between truncate and delete?

Truncate removes the all records, can’t be rollback database trigger don’t fire on truncate. Delete records can be rollback database triggers fire on delete. (Or)  Truncate is implicitly commit.