Database Questions
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.
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.
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.
No comments:
Post a Comment