Database Questions With Answers


Q) DML à insert, update, delete
     DDL à create, alter, drop, truncate, rename.
     DQL à select
     DCL à grant, revoke.
     TCL à commit, rollback, savepoint.
  
Q) Normalization
            Normalization is the process of simplifying the relationship between data elements in a record.

(i) 1st normal form: - 1st N.F is achieved when all repeating groups are removed, and P.K should be defined. big table is broken into many small tables, such that each table has a primary key.
(ii) 2nd normal form: - Eliminate any non-full dependence of data item on record keys. I.e. The columns in a table which is not completely dependant on the primary key are taken to a separate table.
(iii) 3rd normal form: - Eliminate any transitive dependence of data items on P.K’s. i.e. Removes Transitive dependency. Ie If X is the primary key in a table. Y & Z are columns in the same table. Suppose Z depends only on Y and Y depends on X. Then Z does not depend directly on primary key. So remove Z from the table to a look up table.

Q) Diff Primary key and a Unique key? What is foreign key?
A) Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

Foreign key constraint prevents any actions that would destroy link between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.

Q) Diff Delete & Truncate?
A) Rollback is possible after DELETE but TRUNCATE remove the table permanently and can’t rollback. Truncate will remove the data permanently we cannot rollback the deleted data.

Dropping   :  (Table structure  + Data are deleted), Invalidates the dependent objects, Drops the indexes
Truncating :  (Data alone deleted), Performs an automatic commit, Faster than delete
Delete        : (Data alone deleted), Doesn’t perform automatic commit
           
Q) Diff Varchar and Varchar2?
A) The difference between Varchar and Varchar2 is both are variable length but only 2000 bytes of character of data can be store in varchar where as 4000 bytes of character of data can be store in varchar2.

Q) Diff LONG & LONG RAW?
A) You use the LONG datatype to store variable-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 32760 bytes.
     You use the LONG RAW datatype to store binary data (or) byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. The maximum length of a LONG RAW value is 32760 bytes.

Q) Diff Function & Procedure
            Function is a self-contained program segment, function will return a value but procedure not.
            Procedure is sub program will perform some specific actions.

Q) How to find out duplicate rows & delete duplicate rows in a table?
A)         MPID EMPNAME    EMPSSN
   -----   ----------     -----------
         1 Jack       555-55-5555
         2 Mike       555-58-5555
         3 Jack       555-55-5555
         4 Mike       555-58-5555
SQL> select count (empssn), empssn from employee group by empssn
      having count (empssn) > 1;

COUNT (EMPSSN) EMPSSN
-------------            -----------
            2 555-55-5555
            2 555-58-5555
SQL> delete from employee where (empid, empssn)
    not in (select min (empid), empssn from employee group by empssn);

Q) Select the nth highest rank from the table?
A) Select * from tab t1 where 2=(select count (distinct (t2.sal)) from tab t2 where t1.sal<=t2.sal)

Q) a) Emp table where fields empName, empId, address
   b) Salary table where fields EmpId, month, Amount
these 2 tables he wants EmpId, empName and salary for month November?
A) Select emp.empId, empName, Amount from emp, salary where emp.empId=salary.empId and month=November;

Q) Oracle/PLSQL: Synonyms?
A) A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects

Syntax: -
Create [or replace]  [public] synonym [schema.] synonym_name for [schema.] object_name;

or replace -- allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.
Public -- means that the synonym is a public synonym and is accessible to all users. 
Schema -- is the appropriate schema.  If this phrase is omitted, Oracle assumes that you are referring to your own schema.
object_name -- is the name of the object for which you are creating the synonym. It can be one of the following:
Table
Package
View
materialized view
sequence
java class schema object
stored procedure
user-defined object
Function
Synonym

example:
Create public synonym suppliers for app. suppliers;
Example demonstrates how to create a synonym called suppliers.  Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app.  For example:
Select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:
Create or replace public synonym suppliers for app. suppliers;
Dropping a synonym
It is also possible to drop a synonym.
drop  [public]  synonym  [schema .]  Synonym_name [force];
public -- phrase allows you to drop a public synonym.  If you have specified public, then you don't specify a schema.
Force -- phrase will force Oracle to drop the synonym even if it has dependencies.  It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

Example:
Drop public synonym suppliers;
This drop statement would drop the synonym called suppliers that we defined earlier.

Q) What is an alias and how does it differ from a synonym?
A) An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view.  The alias is not dropped when the table is dropped.

Q) What are joins? Inner join & outer join?
A) By using joins, you can retrieve data from two or more tables based on logical relationships between the tables
Inner Join: - returns all rows from both tables where there is a match.
Outer Join: - outer join includes rows from tables when there are no matching values in the tables.

• LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
• RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
• FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Q. Diff join and a Union?
A) A join selects columns from 2 or more tables. A union selects rows. 
when using the UNION command all selected columns need to be of the same data type. The UNION command eliminate duplicate values.

Q. Union & Union All?
A) The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It cannot eliminate duplicate values.
 > SELECT E_Name FROM Employees_Norway
UNION ALL
    SELECT E_Name FROM Employees_USA

Q) Is the foreign key is unique in the primary table?
A) Not necessary

Q) Table mentioned below named employee
ID
NAME
MID
1
CEO
Null
2
VP
CEO
3
Director
VP

Asked to write a query to obtain the following output
CEO
Null
VP
CEO
Director
VP

A) SQL> Select a.name, b.name from employee a, employee b where a.mid=b.id(+).

Q) Explain a scenario when you don’t go for normalization?
A) If we r sure that there wont be much data redundancy then don’t go for normalization.

Q) What is Referential integrity?
A) R.I refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

Q) What techniques are used to retrieve data from more than one table in a single SQL statement?
A) Joins, unions and nested selects are used to retrieve data.

Q) What is a view? Why use it?
A) A view is a virtual table made up of data from base tables and other views, but not stored separately.

Q) SELECT statement syntax?
A) SELECT [ DISTINCT | ALL ]  column_expression1, column_expression2, ....
  [ FROM from_clause ]
  [ WHERE where_expression ]
  [ GROUP BY expression1, expression2, .... ]
  [ HAVING having_expression ]
  [ ORDER BY order_column_expr1, order_column_expr2, .... ]

column_expression ::= expression [ AS ] [ column_alias ]
from_clause ::= select_table1, select_table2, ...
from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr  ...
from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr  ...
from_clause ::= select_table1 [INNER] JOIN select_table2  ...
select_table ::= table_name [ AS ] [ table_alias ]
select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]
order_column_expr ::= expression [ ASC | DESC ]

Q) DISTINCT clause?
A) The DISTINCT clause allows you to remove duplicates from the result set.
      > SELECT DISTINCT city FROM supplier;

Q) COUNT function?
A) The COUNT function returns the number of rows in a query
       > SELECT COUNT (*) as "No of emps" FROM employees WHERE salary > 25000;

Q) Diff HAVING CLAUSE & WHERE CLAUSE? 
A) Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

Q) Diff GROUP BY & ORDER BY?
A) Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

> SELECT "col_nam1", SUM("col_nam2") FROM "tab_name" GROUP BY "col_nam1"
> SELECT "col_nam" FROM "tab_nam" [WHERE "condition"] ORDER BY "col_nam" [ASC, DESC]

Q) What keyword does an SQL SELECT statement use for a string search?
A) The LIKE keyword allows for string searches.  The % sign is used as a wildcard.

Q) What is a NULL value?  What are the pros and cons of using NULLS?
A) NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. A NULL in a column means no entry has been made in that column. A data value for the column is "unknown" or "not available."

Q) Index? Types of indexes?
A) Locate rows more quickly and efficiently. It is possible to create an index on one (or) more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries. 

Unique Index : -
A unique index means that two rows cannot have the same index value.
   >CREATE UNIQUE INDEX index_name ON table_name (column_name)

When the UNIQUE keyword is omitted, duplicate values are allowed. If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
   >CREATE INDEX PersonIndex ON Person (LastName DESC)

If you want to index more than one column you can list the column names within the parentheses.
   >CREATE INDEX PersonIndex ON Person (LastName, FirstName)

Q) Diff subqueries & Correlated subqueries?
A)subqueries are self-contained. None of them have used a reference from outside the subquery.
correlated subquery cannot be evaluated as an independent query, but can reference columns in a table listed in the from list of the outer query.

Q) Predicates IN, ANY, ALL, EXISTS?
A) Sub query can return a subset of zero to n values. According to the conditions which one wants to express, one can use the predicates IN, ANY, ALL or EXISTS.

IN
The comparison operator is the equality and the logical operation between values is OR.
ANY
Allows to check if at least a value of the list satisfies condition.
ALL
Allows to check if condition is realized for all the values of the list.
EXISTS
If the subquery returns a result, the value returned is True otherwise the value returned is False.

Q) What are some sql Aggregates and other Built-in functions?
A) AVG, SUM, MIN, MAX, COUNT and DISTINCT. 


Enter your email address to get our daily JOBS & INTERVIEW FAQ's Straight to your Inbox.

Make sure to activate your subscription by clicking on the activation link sent to your email