SQL Questions - Page 2

26. What are the type of Joins?

JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of Oracle joins:

Oracle INNER JOIN (or sometimes called simple join)

Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)

Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

INNER JOIN (simple join):
Chances are, you've already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.
 
LEFT OUTER JOIN:
Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
 
RIGHT OUTER JOIN:
Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
 
FULL OUTER JOIN:
Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
 
Read More…
 

 
27. What is Regular Expression ?
Regular expressions enable you to search for patterns in string data.

1. REGEXP_LIKE,
2. REGEXP_COUNT,

3. REGEXP_REPLACE,

4. REGEXP_INSTR,

5. REGEXP_SUBSTR.

Read More…

 

 
28. What is Analytical Function ?
Analytic functions calculate an aggregate value based on a group of rows and return multiple rows for each group.
 

Name	Description
CUME_DIST	Calculate the cumulative distribution of a value in a set of values
DENSE_RANK	Calculate the rank of a row in an ordered set of rows with no gaps in rank values.
FIRST_VALUE	Get the value of the first row in a specified window frame.
LAG	Provide access to a row at a given physical offset that comes before the current row without using a self-join.
LAST_VALUE	Get the value of the last row in a specified window frame.
LEAD	Provide access to a row at a given physical offset that follows the current row without using a self-join.
NTH_VALUE	Get the Nth value in a set of values.
NTILE	Divide an ordered set of rows into a number of buckets and assign an appropriate bucket number to each row.
PERCENT_RANK	Calculate the percent rank of a value in a set of values.
RANK	Calculate the rank of a value in a set of values
ROW_NUMBER	Assign a unique sequential integer starting from 1 to each row in a partition or in the whole result

  
Read More…
 

29. What is NVL?
NVL function is used to convert the null value to its actual value.
 
 

30. What is nvl2 ?
 If the first expression is not null, then the NVL2 function returns the second expression otherwise it return the 3rd expression.
 

31. What is nvlif?
The NULLIF() function returns NULL, if two expressions are equal, otherwise it returns the first expression.
 

32. What is Coalesce
It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.
 

33. Difference between case and decode?
Both are used to implement if -else condition. Case is extended veriosn on decode.
1.CASE can work with logical operators other than ‘=’, decode only work with =
2.Case are faster then Decode.
3.CASE expects datatype consistency, DECODE does not
4. Case can work with predicates(in) and subqueries.
5.Case can even work as a paramter in procedure and function while decode can not.
 
Read More…
 
 

34. Difference between DELETE and TRUNCATE ?

The basic difference in both is DELETE is DML command and TRUNCATE is DDL. 

DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table.

We can use DELETE with WHERE clause but cannot use TRUNCATE with it

 

35. Difference Nested Subquery and Correlated Subquery?
Subquery within another subquery is called as Nested Subquery. If the output of a subquery is depending on column values of the parent query table then the query is called Correlated Subquery.
 

36. What is DB Link ?

A database link is a schema object in one database that enables you to access objects on another database.

Syntax :
CREATE DATABASE LINK Linkname CONNECT TO Makess IDENTIFIED BY Makess  USING 'orcl';

Read More…

37. Type of DB Link ?

There are two types of database links: public and private. Private database links are visible to the owners while public database links are visible to all users in the database. For this reason, public database links may pose some potential security risks.

38. How will you test a DB Link ?
1.select * from dba_db_links; 

2.ping remoreserv 

3.select * from dual@mydb;

 
39. What is Sequence ?
A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax :

CREATE SEQUENCE sequence_name  

MINVALUE value  

MAXVALUE value  

START WITH value  

INCREMENT BY value  

CACHE value;
 

 

40. What is Merge Statement ?

we use  MERGE statement to select rows from one or more sources for update or insertion into a table or view.

MERGE INTO employees e  

USING hr_records ON (e.id = h.emp_id)  

WHEN MATCHED THEN 

   UPDATE SET e.address = h.address 
WHEN NOT MATCHED THEN   

   INSERT (id, address)   VALUES (h.emp_id, h.address);
 

 

41. What is Database schema ?
Schema is a collection of database objects owned by a database user who can create or manipulate new objects within this schema. The schema can contain any DB objects like table, view, indexes, clusters, stored procs, functions, etc.

 
42. What is Data Dictionary ?
Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.

Tables - user_tab_columns, user_tables,user_ind_columns,
 

43. What is Deadlock ?
Deadlock is a situation when two or more users are simultaneously waiting for the data, which is locked by each other. Hence it results in all blocked user sessions.
 
 

44. What is Role ?
Giving access to individual objects to the individual users is a tough administrative task. In order to make this job easy, a group of common privileges is created in a database, which is known as ROLE. The ROLE, once created can be assigned to or revoked from the users by using GRANT & REVOKE command.
 

45. What is synonyms ?
In databases, a synonym is an alias or alternate name for a table, view, sequence, or other schema object. They are used mainly to make it easy for users to access database objects owned by other users. CREATE [PUBLIC] SYNONYM employees for hr.employees.
 

 
46. What do you understaned by Parsing ?
When at SQL statement has been written and generated then first step is parsing of that sql statement. Parsing is nothing but checking the syntax of sql query. All the syntax of query is correct or not is checked by sql Parser. 1. Syntax Analysis      2. Semantic Analysis
 
 

47. What is recursive query ?
A recursive query is a kind of query, in which the DNS server, who received your query will do all the job of fetching the answer, and giving it back to you.
A recursive CTE is a CTE that references itself. In doing so, the initial CTE is repeatedly executed, returning subsets of data, until the complete result is returned.
 

48. What is SCD ?
Slowly Changing Dimensions (SCD) are the most commonly used advanced dimensional technique used in dimensional data warehouses. Slowly changing dimensions are used when you wish to capture the changing data within the dimension over time. There are three methodologies for slowly changing dimensions.
 

49. What is Partition Pruning ?
Partition pruning is an essential performance feature for data warehouses. In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list. This functionality enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement. ReadMore…

 

50. What is LATERAL JOIN ?
A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. So achive this thing we use lateral. ReadMore…