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.
(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.
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.
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.
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.
Function
is a self-contained program segment, function will return a value but procedure
not.
Procedure
is sub program will perform some specific actions.
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;
------------- -----------
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);
A) Select
* from tab t1 where 2=(select count (distinct (t2.sal)) from tab t2 where
t1.sal<=t2.sal)
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;
A) A synonym is an
alternative name for objects such as tables, views, sequences, stored
procedures, and other database objects
Create [or replace] [public] synonym
[schema.] synonym_name for [schema.] object_name;
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.
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.
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.
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.
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
A) Not necessary
|
ID |
NAME |
MID |
|
1 |
CEO |
Null |
|
2 |
VP |
CEO |
|
3 |
Director |
VP |
|
CEO |
Null |
|
VP |
CEO |
|
Director |
VP |
A) If we r sure that
there wont be much data redundancy then don’t go for normalization.
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.
A) Joins, unions and
nested selects are used to retrieve data.
A) A view is a
virtual table made up of data from base tables and other views, but not stored
separately.
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, .... ]
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
]
A) The DISTINCT clause allows you to remove duplicates from the result set.
> SELECT DISTINCT city FROM supplier;
A) The COUNT function
returns the number of rows in a query
> SELECT COUNT (*) as "No of emps" FROM employees WHERE
salary > 25000;
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.
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_nam" FROM
"tab_nam" [WHERE "condition"] ORDER BY "col_nam"
[ASC, DESC]
A) The LIKE keyword
allows for string searches. The % sign is used as a wildcard.
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."
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.
A unique index means that two rows cannot
have the same index value.
>CREATE UNIQUE INDEX index_name ON table_name (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)
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
IN:The comparison operator is the equality and the logical operation between values is OR.wants to express, one can use the predicates IN, ANY, ALL or EXISTS.
ANY:Allows to check if at least a value of the list satisfies condition.Q) What are some sql Aggregates and other Built-in functions?
A) AVG, SUM, MIN, MAX, COUNT and DISTINCT.