Primary Key :- The attribute or combination of attributes that uniquely identifies a row or record.
Foreign Key:- an attribute or combination of attribute in a table whose value match a
primary key in another table.
Composite key:- A primary key that consists of two or more attributes is known as
composite key
candidate key:- is a column in a table which has the ability to become a primary key.
Alternate Key:- Any of the candidate keys that is not part of the primary key is called an alternate key.
secondary key:- alternate of primary key.

What are the difference between DDL, DML and DCL commands?

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

Data Control Language (DCL) statements. Some examples:

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

What is Stored Procedure?

Stored Procedure: In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner), and improving productivity (statements in a stored procedure only need to be written one time).

A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.
Packages have many advantages over standalone procedures and functions. For example, they:

· Let you organize your application development more efficiently.

· Let you grant privileges more efficiently.

· Let you modify package objects without recompiling dependent schema objects.

· Enable Oracle to read multiple package objects into memory at once.

· Let you overload procedures or functions. Overloading means creating multiple procedures with the same name in the same package, each taking arguments of different number or datatype.

· Can contain global variables and cursors that are available to all procedures and functions in the package.

A procedure or function is a schema object that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task. Procedures and functions are created in a user's schema and stored in a database for continued use. You can execute a procedure or function interactively using an Oracle tool, such as SQL*Plus, or call it explicitly in the code of a database application, such as an Oracle Forms or Precompiler application, or in the code of another procedure or trigger. Figure illustrates a simple procedure stored in the database, being called by several different database applications.

A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users. Figure illustrates a package that encapsulates a number of procedures used to manage an employee database.

What is a Database Trigger?

Database trigger is a PL/SQL block that is executed on an event in the database. Triggers are executed when you issues a data manipulation command like INSERT, DELETE, UPDATE on a table for which the trigger has been created. They are automatically executed and also transparent to the user.

The general syntax of CREATE TRIGGER is :
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_statement

The general syntax of DROP TRIGGER is : DROP TRIGGER trigger_name.

Triggers may be used for any of the following:

_ To implement complex business rule, which cannot be implemented using integrity

constraints.

_ To audit the process. For example, to keep track of changes made to a table.

_ To automatically perform an action when another concerned action takes place. For

example, updating a table whenever there is an insertion or a row into another table.

Triggers are similar to stored procedures, but stored procedures are called explicitly and triggers are called implicitly by Oracle when the concerned event occurs.

Types of Triggers

Depending upon, when a trigger is fired, it may be classified as :

_ Statement-level trigger

_ Row-level trigger

_ Before triggers

_ After triggers

Statement-level Triggers

A statement trigger is fired only for once for a DML statement irrespective of the number of rows affected by the statement.

Row-level Trigger

A row trigger is fired once for each row that is affected by DML command. For example, if an UPDATE command updates 100 rows then row-level trigger is fired 100 times whereas a statement-level trigger is fired only for once.

Before Triggers

While defining a trigger, you can specify whether the trigger is to be fired before the command (INSERT, DELETE, and UPDATE) is executed or after the command is executed.

Before triggers are commonly used to check the validity of the data before the action is

performed. For instance, you can use before trigger to prevent deletion of row if deletion

should not be allowed in the given case.

AFTER Triggers

After triggers are fired after the triggering action is completed. For example, If after trigger is associated with INSERT command then it is fired after the row is inserted into the table.

What is Database Cursor
A database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. Cursors are used by database programmers to process individual rows returned by database system queries. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.
To work with cursors you must use the following SQL statements:

   DECLARE CURSOR

   OPEN

   FETCH

   CLOSE

What is Database Architecture?
ANSI SPARC is an acronym for the American National Standard Institute Standard Planning and Requirements Committee. A standard three level approach to database design has been agreed.

- External level
- Conceptual level
- Internal level (includes physical data storage)

The 3 Level Architecture has the aim of enabling users to access the same data but with a personalized view of it. The distancing of the internal level from the external level means that users do not need to know how the data is physically stored in the database. This level separation also allows the Database Administrator (DBA) to change the database storage structures without affecting the users' views.
External Level (User Views)
A user's view of the database describes a part of the database that is relevant to a particular user. It excludes irrelevant data as well as data which the user is not authorized to access.

Conceptual Level
The conceptual level is a way of describing what data is stored within the whole database and how the data is inter-related. The conceptual level does not specify how the data is physically stored.

Internal Level
The internal level involves how the database is physically represented on the computer system. It describes how the data is actually stored in the database and on the computer hardware.

Database Schema
The database schema provide an overall description of the database structure (not actual data). There are three types of schema which relate to the 3 Level Database Architecture.

External Schemas or subschema’s relate to the user views. The Conceptual Schema describes all the types of data that appear in the database and the relationships between data items. Integrity constraints are also specified in the conceptual schema. The Internal Schema provides definitions for stored records, methods of representation, data fields, indexes, and hashing schemes etc...

What is Entity-Relationship (E-R) Model?

Entity-Relationship (E-R) Model is based on a view of a real world that consists of set of objects called entities and relationships among entity sets which are basically a group of similar objects. The relationships between entity sets is represented by a named E-R relationship and is of 1:1, 1: N or M: N type which tells the mapping from one entity set to another.

The E-R model is shown diagrammatically using Entity-Relationship (E-R) diagrams which represent the elements of the conceptual model that show the meanings and the relationships between those elements independent of any particular DBMS and implementation details.

An entity-relationship (ER) diagram is a specialized graphic that illustrates the relationships between entities in a database. ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.

There are three types of relationships between entities:

one-to-one: one instance of an entity (A) is associated with one other instance of another entity (B). For example, in a database of employees, each employee name (A) is associated with only one social security number (B).
one-to-many: one instance of an entity (A) is associated with zero, one or many instances of another entity (B), but for one instance of entity B there is only one instance of entity A. For example, for a company with all employees working in one building, the building name (A) is associated with many different employees (B), but those employees all share the same singular association with entity A.
many-to-many: one instance of an entity (A) is associated with one, zero or many instances of another entity (B), and one instance of entity B is associated with one, zero or many instances of entity A. For example, for a company in which all of its employees work on multiple projects, each instance of an employee (A) is associated with many instances of a project (B), and at the same time, each instance of a project (B) has multiple employees (A) associated with it.

PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

A Simple PL/SQL Block:

Each PL/SQL program consists of SQL and PL/SQL statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:

The Declaration section (optional).
The Execution section (mandatory).
The Exception (or Error) Handling section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.

This is how a sample PL/SQL Block looks.

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;

Normalization, The five Normal Forms; Formal Definitions.

First Normal Form
‘A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.’

Second Normal Form
‘A relation R is in second normal form (2NF) if and only if it is in 1NF and every nonkey attribute is fully dependent on the primary key.’
Third Normal Form
‘A relation R is in third normal form (3NF) if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.’
Boyce/Codd Normal Form
‘A relation R is in Boyce/Codd normal form (BCNF) if and only if every determinant is a candidate key.’
Fourth Normal Form
‘A relation R is in fourth normal form (4NF) if and only if, wherever there exists an MVD in R, say A -> -> B, then all attributes of R are also functionally dependent on A. In other words, the only dependencies (FDs or MVDs) in R are of the form K -> X (i.e. a functional dependency from a candidate key K to some other attribute X). Equivalently: R is in 4NF if it is in BCNF and all MVD’s in R are in fact FDs.’
Fifth Normal Form
‘A relation R is in fifth normal form (5NF) – also called projection-join normal form (PJ/NF) if and only if every join dependency in R is a consequence of the candidate keys of R.’
For every normal form it is assumed that every occurrence of R can be uniquely identified by a primary key using one or more attributes in R.
FD = Functional Dependency
MVD = Multi-Valued Dependency

SQL Commands?

The SQL SELECT clause selects data from one or more database tables and/or views. In its basic form the SQL SELECT syntax looks like this:

SELECT ColumnName1, ColumnName2, …
FROM Table1

Let's have a look at the SELECT SQL statement above. The first part starts with the SELECT clause followed by a list of columns separated by commas. This list of columns defines which columns we are selecting data from. The second part of our SQL SELECT starts with the FROM clause followed by name of table from where we are extracting data.

We will use a table called Weather with 3 columns – City, AverageTemperature and Date, to give a real world SQL SELECT example:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005

To select all cities from the above table, we will use this SQL SELECT statement:

SELECT City
FROM Weather

The result will be:

City

New York

Seattle

Washington

If we want to select all the data (all columns) from the Weather table we can do it with the following SQL SELECT:

SELECT *
FROM Weather

The * replaced the column list following the SELECT SQL clause, thus instructing the SQL interpreter to return all columns.

The SQL INSERT INTO clause facilitates the process of inserting data into a SQL table. Here is how you can insert a new row into the Weather table, using SQL INSERT INTO:

INSERT INTO Weather (City, AverageTemperature, Date)
VALUES ('Los Angeles', 20, '10/10/2005')

The result of the execution of the SQL INSERT INTO above will look like this:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
Los Angeles	20 C	10/10/2005

You can produce the same result, with a slightly modified SQL INSERT INTO syntax:

INSERT INTO Weather
VALUES ('Los Angeles', 20, '10/10/2005')

You are allowed to omit the list of column names in the SQL INSERT INTO clause, if you enter values for each of the table columns.

The SQL WHERE clause works in conjunction with other SQL clauses like SELECT, INSERT and UPDATE to specify a search condition for these statements. We are going to give an example of the SQL WHERE clause used along with the SQL SELECT clause:

SELECT AverageTemperature
FROM Weather
WHERE City = 'New York'

The first 2 lines of the SELECT SQL statement above are already familiar from the previous chapter, but the 3rd line is new and specifies the SQL WHERE search condition:

WHERE City = 'New York'

If we translate the SQL statement to plain English, it would sound like this "Select the average temperature for the city of New York"

The result of the added SQL WHERE condition will be:

AverageTemperature

22 C

The SQL UPDATE clause serves to update data in database table. The SQL UPDATE clause basic syntax looks like this:

UPDATE Table1
SET Column1 = Value1, Column2 = Value2, …

The first line of the above SQL UPDATE statement defines which table we are updating. The second line starts with the SET SQL keyword followed by one or more Column = Value pairs separated by commas. The second line of the UPDATE statement defines which table columns to update and with what value.

Please consider the following SQL UPDATE syntax:

UPDATE Weather
SET AverageTemperature = 20

Before we run this UPDATE SQL expression, our Weather table looks like this:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
New York	18 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	17 C	10/09/2005

After the update it looks like this:

City	AverageTemperature	Date
New York	20 C	10/10/2005
Seattle	20 C	10/10/2005
Washington	20 C	10/10/2005
New York	20 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	20 C	10/09/2005

UPDATE Weather
SET AverageTemperature = 20
WHERE City = 'New York'

The SQL DELETE clause is used to delete data from a database table. The simplest SQL DELETE syntax looks like this:

DELETE FROM Table1

The SQL DELETE statement above will delete all data from the Table1 table.

Most of the time we will want to delete only table rows satisfying certain search criteria defined in the SQL WHERE clause. We will use the Weather table again to illustrate how to use SQL DELETE to delete a limited number of rows from a table:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
New York	18 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	17 C	10/09/2005

If we wanted to delete all rows containing Weather data for New York, we would use the following SQL DELETE statement:

DELETE FROM Weather
WHERE City = 'New York'

Be extremely careful when using SQL DELETE, as you cannot restore data once you delete it from the table. You might want to make a backup of important data before performing delete on it.

The SQL TRUNCATE TABLE clause deletes all rows from a database table. Here is the SQL TRUNCATE TABKE syntax:

TRUNCATE TABLE Weather

The SQL TRUNCATE TABLE clause does the same as a SQL DELETE clause which doesn't have a SQL WHERE clause. The following two SQL statements are equivalent:

TRUNCATE TABLE Weather

DELETE FROM Weather

Use SQL TRUNCATE TABLE only when you want to delete all rows in a table.

The SQL DISTINCT clause works in conjunction with the SQL SELECT clause and selects only distinct (unique) data from a database table(s). Here is an example of SQL DISTINCT clause:

SELECT DISTINCT Column1
FROM Table1

As you can see the DISTINCT keyword goes immediately after the SELECT clause and is then followed by a list of one or more column names. I'll give you an example why you might need to use the DISTINCT SQL clause. I'll use the Weather table from the SQL WHERE tutorial to demonstrate the SQL DISTINCT application:

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
New York	18 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	17 C	10/09/2005

Consider the following SQL statement utilizing SQL DISTINCT:

SELECT DISTINCT City
FROM Weather

This SQL DISTINCT expression will return a list with all cities found in the City column of the Weather table, but it will remove the duplicates and leave only a single entry for each city:

City

New York

Seattle

Washington

The SQL JOIN clause selects data from two or more tables tied together by matching table columns. To illustrate how to use the SQL JOIN clause we will use the already familiar Weather table and we will introduce a new one called State.

Weather table

City	AverageTemperature	Date
New York	22 C	10/10/2005
Seattle	21 C	10/10/2005
Washington	20 C	10/10/2005
New York	18 C	10/09/2005
Seattle	20 C	10/09/2005
Washington	17 C	10/09/2005

State table

State	City
District of Columbia	Washington
Washington	Seattle
New York	New York
Texas	Houston

If we want to select all the data from the Weather table, and to add one more additional column called State to the result, we can do it the following way:

SELECT Weather.City, Weather.AverageTemperature, Weather.Date, State.State
FROM Weather, State
WHERE Weather.City = State.City

In the SQL SELECT list we see 4 column names prefixed with their respective table names, so there is no confusion which column from which table we are referencing.
The SQL FROM clause has comma separated list of the two tables we are selecting data from. Finally the SQL WHERE clause defines that the City column from the weather table has to match the City column from the State table.

We can re-write the above SQL statement using SQL JOIN and the result will be the same, but the performance in general will be much better:
SELECT Weather.City, Weather.AverageTemperature, Weather.Date, State.State
FROM Weather JOIN State
ON Weather.City = State.City

The first line of this SQL JOIN statement is identical to the first line of the previous SQL expression. The second line uses the SQL JOIN clause instead of comma, and the third line uses the ON keyword to define the columns we are joining on.

The result of this SQL JOIN will be:

City	AverageTemperature	Date	State
New York	22 C	10/10/2005	New York
Seattle	21 C	10/10/2005	Washington
Washington	20 C	10/10/2005	District of Columbia
New York	18 C	10/09/2005	New York
Seattle	20 C	10/09/2005	Washington
Washington	17 C	10/09/2005	District of Columbia

The SQL UNION clause merges the results of two or more SELECT SQL queries into one result set. When using SQL UNION, all the SQL expressions participating in the UNION must have the same structure (they have to have the same number of columns and same or compatible data types). You have to keep the column order of all unionized SQL SELECT expressions uniform, otherwise you’ll get an error.

Here is an example of SQL UNION statement:

SELECT City, AverageTemperature, Date
FROM Weather

UNION

SELECT City, AverageTemperature, Date
FROM WeatherArchive

The result of the SQL UNION statement above will merge all records from the Weather and the WeatherArchive tables and the column names in the result will remain the same as the column names in the first SELECT expression in the UNION statement. This SQL UNION expression will also remove all duplicates (if any).

Something important to remember when using SQL UNION is that the duplicated rows are removed from the result set. If you want all rows, including duplicate ones to be returned, simply put the ALL keyword after the UNION clause:

SELECT City, AverageTemperature, Date
FROM Weather

UNION ALL

SELECT City, AverageTemperature, Date
FROM WeatherArchive

Subquery or Inner query
Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.
Subqueries are an alternate way of returning data from multiple tables.
Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

SELECT
INSERT
UPDATE
DELETE

For Example:
1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like,
SELECT first_name, last_name, subject
FROM student_details
WHERE games NOT IN ('Cricket', 'Football');
The output would be similar to:

first_name	last_name	subject
-------------	-------------	----------
Shekar	Gowda	Badminton
Priya	Chandra	Chess

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.
An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the subquery on the right side.
A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

This command is used to create a table. The syntax of this command is:

create table tablename

(column1name datatype [constraint],

column2name datatype [constraint],

column3name datatype [constraint]);

Keywords specifying Items that may be created

table

Data Types

char(size) - Fixed length string of characters of the set size. The size of the string is limited to 255 characters.
date
number(maxsize) - Number with a maximum number of digits specified by "maxsize".
number(maxdigits,maxright) - A decimal number with a manimum number of "maxdigits" with "a maximum number of digits to the right of the decimal, "maxright".
varchar(maxsize) - A character string with variable lingth limited to "maxsize".

Constraints

Constraints are rules for the column.. Possible values include:

not null - The column values must have a value and cannot be null.
primary key - Each record is uniquely identified by this column.
unique - No two values may be the same in the column

Example

create table citylist

(name varchar(20),

state varchar(20),

population number(8),

zipcode number(5) unique);

SQL SERVER – Primary Key Constraints and Unique Key Constraints

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.
Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.
Alter table to add unique constraint to column:
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO

What is file allocation table?

A table that the operating system uses to locate files on a disk. Due to fragmentation, a file may be divided into many sections that are scattered around the disk. The FAT keeps track of all these pieces.
In DOS systems, FATs are stored just after the boot sector. The FAT system for older versions of Windows 95 is called FAT16, and the one for new versions of Windows 95 and Windows 98 is called FAT32.

Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views

For example to retrieve all rows from emp table.

SQL> select empno, ename, sal from emp;

Or (if you want to see all the columns values

You can also give * which means all columns)

SQL> select * from emp;

Suppose you want to see only employee names and their salaries then you can type the following statement

SQL> select name, sal from emp;

You can filter information using where conditions like suppose you want to see only those employees whose salary is above 5000 then you can type the following query with where condition

SQL>select * from emp where sal > 5000;

To see those employees whose salary is less than 5000 then the query will be

SQL> select * from emp where sal < 5000;

The UPDATE statement is used to update existing records in a table.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob

Now we want to update the person "Tjessem, Jakob" in the "Persons" table.
We use the following SQL statement:

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

The "Persons" table will now look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger
4	Nilsen	Johan	Bakken 2	Stavanger
5	Tjessem	Jakob	Nissestien 67	Sandnes

You can place constraints to limit the type of data that can go into a table. Such constraints can be specified when the table when the table is first created via the CREATE TABLE statement, or after the table is already created via the ALTER TABLE statement.
Common types of constraints include the following:

NOT NULL Constraint: Ensures that a column cannot have NULL value.
DEFAULT Constraint: Provides a default value for a column when none is specified.
UNIQUE Constraint: Ensures that all values in a column are different.
CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
Primary Key Constraint: Used to uniquely identify a row in the table.
Foreign Key Constraint: Used to ensure referential integrity of the data.

Each constraint is discussed in the following sections.

What is Join?

A join combines records from two or more tables in a relational database. In the Structured Query Language (SQL), there are two types of joins: "inner" and "outer". Outer joins are subdivided further into left outer joins, right outer joins, and full outer joins.
Inner join
This is the default join method if nothing else is specified. An inner join essentially finds the intersection between the two tables. The join takes all the records from table A and finds the matching record(s) from table B. If no match is found, the record from A is not included in the results. If multiple results are found in B that match the predicate then one row will be returned for each (the values from A will be repeated).
Special care must be taken when joining tables on columns that can be NULL since NULL values will never match each other
Left outer join
A left outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned—but with NULL values for each column from B.

Right outer join
A right outer join is much like a left outer join, except that the tables are reversed. Every record from the right side, B, will be returned, and NULL values will be returned for those that have no matching record in A.

Full outer join
Full outer joins are the combination of left and right outer joins. These joins will show records from both tables, and fill in NULLs for missing matches on either side.

Query Joins

A join is a temporary relationship that you can create between two tables in a database query that do not already have an established relationship or common field with the same fieldname or data type. Database tables that are joined in a query are related in that query only, and nowhere else. The type of join that you use indicates which records the query will select or perform the chosen actions on.
Note: Creating a query join will not establish a permanent relationship between the tables. Permanent relationships can only be created in the Microsoft Access relationships window.

Inner Join

Definition: An inner join is a join that selects only those records from both database tables that have matching values. Records with values in the joined field that do not appear in both of the database tables will be excluded from the query. One or more fields can serve as the join fields.

The inner join is also known as an equi-join.
The inner join is the default join type in Microsoft Access

The above shows a conceptual diagram of the inner join between Customer data and Order data.
Analogy: Consider a business that employs both managers and engineers - and some employees that are both. An inner join is like a union of this set; it selects the set of people that are both managers and engineers and provides information about them in both roles.

Outer Join

Definition: An outer join selects all of the records from one database table and only those records in the second table that have matching values in the joined field. In a left outer join, the selected records will include all of the records in the first database table. In a right outer join, the selected records will include all records of the second database table. One or more fields can serve as the join fields.

Left Outer Join example:

The above conceptual diagram details the Left Outer Join between Customer data and Order data
Analogy: Consider again the business that employs both managers and engineers. A left outer join selects all of the managers, providing the information about them, but in the case of managers who are also engineers, it provides additional information about them.

Right Outer Join example:

The above shows a conceptual diagram of a Right Outer Join between the Customer data and the Order data.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
NULL values are treated differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.

Note: It is not possible to compare NULL and 0; they are not equivalent.

SQL Working with NULL Values

Look at the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola		Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari		Stavanger

Suppose that the "Address" column in the "Persons" table is optional. This means that if we insert a record with no value for the "Address" column, the "Address" column will be saved with a NULL value.
How can we test for NULL values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.

SQL IS NULL

How do we select only the records with NULL values in the "Address" column?
We will have to use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

The result-set will look like this:

LastName	FirstName	Address
Hansen	Ola
Pettersen	Kari

Tip: Always use IS NULL to look for NULL values.

SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?
We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

The result-set will look like this:

LastName	FirstName	Address
Svendson	Tove	Borgvn 23

In the next chapter we will look at the ISNULL(), NVL(), IFNULL() and COALESCE() functions.

What is View, Explain?

A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query. To create a view, you use the SQL syntax:

        CREATE OR REPLACE VIEW <view_name> AS

        SELECT <any valid select query>;

• The view query itself is saved in the database, but it is not actually run until it is called with another SELECT statement. For this reason, the view does not take up any disk space for data storage, and it does not create any redundant copies of data that is already stored in the tables that it references (which are sometimes called the base tables of the view).

• Although it is not required, many database developers identify views with names such as v_Customers or Customers_view. This not only avoids name conflicts with base tables, it helps in reading any query that uses a view.

• The keywords OR REPLACE in the syntax shown above are optional. Although you don’t need to use them the first time that you create a view, including them will overwrite an older version of the view with your latest one, without giving you an error message.

• The syntax to remove a view from your schema is exactly what you would expect:

        DROP VIEW <view_name>;

Using views

A view name may be used in exactly the same way as a table name in any SELECT query. Once stored, the view can be used again and again, rather than re-writing the same query many times.

• The most basic use of a view would be to simply SELECT * from it, but it also might represent a pre-written subquery or a simplified way to write part of a FROM clause.

• In many systems, views are stored in a pre-compiled form. This might save some execution time for the query, but usually not enough for a human user to notice.

• One of the most important uses of views is in large multi-user systems, where they make it easy to control access to data for different types of users. As a very simple example, suppose that you have a table of employee information on the scheme Employees = {employeeID, empFName, empLName, empPhone, jobTitle, payRate, managerID}. Obviously, you can’t let everyone in the company look at all of this information, let alone make changes to it.

• Your database administrator (DBA) can define roles to represent different groups of users, and then grant membership in one or more roles to any specific user account (schema). In turn, you can grant table-level or view-level permissions to a role as well as to a specific user. Suppose that the DBA has created the roles managers and payroll for people who occupy those positions. In Oracle®, there is also a pre-defined role named public, which means every user of the database.

• You could create separate views even on just the Employees table, and control access to them like this:

        CREATE VIEW phone_view AS

        SELECT empFName, empLName, empPhone FROM Employees;

        GRANT SELECT ON phone_view TO public;

        CREATE VIEW job_view AS

        SELECT employeeID, empFName, empLName, jobTitle, managerID FROM Employees;

        GRANT SELECT, UPDATE ON job_view TO managers;

        CREATE VIEW pay_view AS

        SELECT employeeID, empFName, empLName, payRate FROM Employees;

        GRANT SELECT, UPDATE ON pay_view TO payroll;

• Only a very few trusted people would have SELECT, UPDATE, INSERT, and DELETE privileges on the entire Employees base table; everyone else would now have exactly the access that they need, but no more.

• When a view is the target of an UPDATE statement, the base table value is changed. You can’t change a computed value in a view, or any value in a view that is based on a UNION query. You may also use a view as the target of an INSERT or DELETE statement, subject to any integrity constraints that have been placed on the base tables.

Materialized views

Sometimes, the execution speed of a query is so important that a developer is willing to trade increased disk space use for faster response, by creating a materialized view. Unlike the view discussed above, a materialized view does create and store the result table in advance, filled with data. The scheme of this table is given by the SELECT clause of the view definition.

• This technique is most useful when the query involves many joins of large tables, or any other SQL feature that could contribute to long execution times. You might encounter this in a Web project, where the site visitor simply can’t be kept waiting while the query runs.

• Since the view would be useless if it is out of date, it must be re-run, at the minimum, when there is a change to any of the tables that it is based on. The SQL syntax to create a materialized view includes many options for when it is first to be run, how often it is to be re-run, and so on. This requires an advanced reference manual for your specific system, and is beyond the scope of this tutorial.

What is Indexes?

An index, as you would expect, is a data structure that the database uses to find records within a table more quickly. Indexes are built on one or more columns of a table; each index maintains a list of values within that field that are sorted in ascending or descending order. Rather than sorting records on the field or fields during query execution, the system can simply access the rows in order of the index.

Unique and non-unique indexes: When you create an index, you may allow the indexed columns to contain duplicate values; the index will still list all of the rows with duplicates. You may also specify that values in the indexed columns must be unique, just as they must be with a primary key. In fact, when you create a primary key constraint on a table, Oracle and most other systems will automatically create a unique index on the primary key columns, as well as not allowing null values in those columns. One good reason for you to create a unique index on non-primary key fields is to enforce the integrity of a candidate key, which otherwise might end up having (nonsense) duplicate values in different rows.

Queries versus insertion/update: It might seem as if you should create an index on every column or group of columns that will ever by used in an ORDER BY clause (for example: lastName, firstName). However, each index will have to be updated every time that a row is inserted or a value in that column is updated. Although index structures such as B or B+ trees allow this to happen very quickly, there still might be circumstances where too many indexes would detract from overall system performance. This and similar issues are often covered in more advanced courses.

Syntax: As you would expect by now, the SQL to create an index is:

        CREATE INDEX <indexname> ON <tablename> (<column>, <column>...);

To enforce unique values, add the UNIQUE keyword:

        CREATE UNIQUE INDEX <indexname> ON <tablename> (<column>, <column>...);

To specify sort order, add the keyword ASC or DESC after each column name, just as you would do in an ORDER BY clause.

To remove an index, simply enter:

        DROP INDEX <indexname>;

What is Data Dictionary
In database management systems, a file that defines the basic organization of a database. A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field. Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its contents.
Data dictionaries do not contain any actual data from the database, only bookkeeping information for managing it. Without a data dictionary, however, a database management system cannot access data from the database.

SQL is a standard language for accessing and manipulating databases.

What is SQL?

SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views

SQL is a Standard - BUT....

Although SQL is an ANSI (American National Standards Institute) standard, there are many different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

Using SQL in Your Web Site

To build a web site that shows some data from a database, you will need the following:

An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
A server-side scripting language, like PHP or ASP
SQL
HTML / CSS

RDBMS

RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables.
A table is a collection of related data entries and it consists of columns and rows.

The Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is an object-relational database management system (ORDBMS)[2] produced and marketed by Oracle Corporation.

Oracle Built-in Datatypes

The table that follows summarizes Oracle built-in datatypes. Please refer to the syntax in the preceding sections for the syntactic elements. The codes listed for the datatypes are used internally by Oracle Database. The datatype code of a column or object attribute is returned by the DUMP function.

Table 2-1 Built-in Datatype Summary

Code	Datatype	Description
1	`VARCHAR2`(size [`BYTE` \| `CHAR`])	Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for `VARCHAR2`. `BYTE` indicates that the column will have byte length semantics; `CHAR` indicates that the column will have character semantics.
1	`NVARCHAR2`(size)	Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for `AL16UTF16` encoding and three times size for `UTF8` encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for `NVARCHAR2`.
2	`NUMBER`[(precision [, scale]])	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
8	`LONG`	Character data of variable length up to 2 gigabytes, or 2³¹ -1 bytes. Provided for backward compatibility.
12	`DATE`	Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the `NLS_DATE_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, and `SECOND`. It does not have fractional seconds or a time zone.
21	`BINARY_FLOAT`	32-bit floating point number. This datatype requires 5 bytes, including the length byte.
22	`BINARY_DOUBLE`	64-bit floating point number. This datatype requires 9 bytes, including the length byte.
180	`TIMESTAMP` [(fractional_seconds)]	Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the `NLS_DATE_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, and `SECOND`. It contains fractional seconds but does not have a time zone.
181	`TIMESTAMP` [(fractional_seconds)] WITH `TIME` `ZONE`	All values of `TIMESTAMP` as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the `SECOND` datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the `NLS_DATE_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `TIMEZONE_HOUR`, and `TIMEZONE_MINUTE`. It has fractional seconds and an explicit time zone.
231	`TIMESTAMP` [(fractional_seconds)] WITH `LOCAL` `TIME` `ZONE`	All values of `TIMESTAMP` `WITH` `TIME` `ZONE`, with the following exceptions: · Data is normalized to the database time zone when it is stored in the database. · When the data is retrieved, users see the data in the session time zone. The default format is determined explicitly by the `NLS_DATE_FORMAT` parameter or implicitly by the `NLS_TERRITORY` parameter. The sizes varies from 7 to 11 bytes, depending on the precision.
182	`INTERVAL` `YEAR` [(year_precision)] `TO` `MONTH`	Stores a period of time in years and months, where year_precision is the number of digits in the `YEAR` datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.
183	`INTERVAL` `DAY` [(day_precision)] `TO` `SECOND` [(fractional_seconds)]	Stores a period of time in days, hours, minutes, and seconds, where · day_precision is the maximum number of digits in the `DAY` datetime field. Accepted values are 0 to 9. The default is 2. · fractional_seconds_precision is the number of digits in the fractional part of the `SECOND` field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.
23	`RAW`(size)	Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a `RAW` value.
24	`LONG RAW`	Raw binary data of variable length up to 2 gigabytes.
69	`ROWID`	Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the `ROWID` pseudocolumn.
208	`UROWID` [(size)]	Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type `UROWID`. The maximum size and default is 4000 bytes.
96	`CHAR` [(size [`BYTE` \| `CHAR`])]	Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. `BYTE` and `CHAR` have the same semantics as for `VARCHAR2`.
96	`NCHAR`[(size)]	Fixed-length character data of length size characters. The number of bytes can be up to two times size for `AL16UTF16` encoding and three times size for `UTF8` encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
112	`CLOB`	A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
112	`NCLOB`	A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.
113	`BLOB`	A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).
114	`BFILE`	Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

SQL: Data Types

The following is a list of general SQL datatypes that may not be supported by all relational databases.

Data Type	Syntax	Explanation (if applicable)
integer	integer
smallint	smallint
numeric	numeric(p,s)	Where p is a precision value; s is a scale value. For example, numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after the decimal.
decimal	decimal(p,s)	Where p is a precision value; s is a scale value.
real	real	Single-precision floating point number
double precision	double precision	Double-precision floating point number
float	float(p)	Where p is a precision value.
character	char(x)	Where x is the number of characters to store. This data type is space padded to fill the number of characters specified.
character varying	varchar2(x)	Where x is the number of characters to store. This data type does NOT space pad.
bit	bit(x)	Where x is the number of bits to store.
bit varying	bit varying(x)	Where x is the number of bits to store. The length can vary up to x.
date	date	Stores year, month, and day values.
time	time	Stores the hour, minute, and second values.
timestamp	timestamp	Stores year, month, day, hour, minute, and second values.
time with time zone	time with time zone	Exactly the same as time, but also stores an offset from UTC of the time specified.
timestamp with time zone	timestamp with time zone	Exactly the same as timestamp, but also stores an offset from UTC of the time specified.
year-month interval		Contains a year value, a month value, or both.
day-time interval		Contains a day value, an hour value, a minute value, and/or a second value.

Kerala PSC Questions

Saturday, 3 December 2011

C-DIT DCA Reference Questions and Answers - RDBMS