SQL Notes

SQL Notes

What is Database

A database is an organized collection of data. Database handlers create database in such a way that only one set of software program provide access of data to all the users.
The main purpose of database is to operate large amount of information by storing, retrieving and managing.
There are many dynamic websites on the World Wide Web now a days which are handled through databases. For example, a model to checks the availability of rooms in a hotel. It is an example of dynamic website that uses database.
There are many database available like MySQL, Sybase, Oracle, Mango DB, Informix, Postgre, SQL Server etc.
SQL or Structured Query Language is used to perform operation on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.

 What is SQL

  • SQL stands for Structured Query Language.
  • It is designed for managing data in a relational database management system (RDBMS).
  • It is pronounced as S-Q-L or sometime See-Qwell.
  • SQL is a database language, it is used for database creation, deletion, fetching rows and modifying rows etc.
  • SQL depends on relational algebra and tuple relational calculus.

What is RDBMS

RDBMS stands for Relational Database Management Systems..
All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL and Microsoft Access are based on RDBMS.
It is called Relational Data Base Management System (RDBMS) because it is based on relational model introduced by E.F. Codd.

History of RDBMS

During 1970 to 1972, E.F. Codd published a paper to propose the use of relational database model.
RDBMS is originally based on that E.F. Codd's relational model invention.

 What is table
The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data.
A table is the simplest example of data storage in RDBMS.

History of Oracle

Oracle began in 1977 and celebrating its 32 wonderful years in the industry (from 1977 to 2009).
·        1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work.
·        1979 - Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).
·        1981 - RSI started developing tools for Oracle.
·        1982 - RSI was renamed to Oracle Corporation.
·        1983 - Oracle released version 3.0, rewritten in C language and ran on multiple platforms.
·        1984 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.
·        1985 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.
·        2007 - Oracle has released Oracle11g. The new version focused on better partitioning, easy migration etc.

Database Normalization

Database normalization is the process of efficiently organizing data in a database. There are two reasons of the normalization process:
·        Eliminating redundant data, for example, storing the same data in more than one tables.
·        Ensuring data dependencies make sense.
Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.
Normalization guidelines are divided into normal forms; think of form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure so that it complies with the rules of first normal form, then second normal form, and finally third normal form.
It's your choice to take it further and go to fourth normal form, fifth normal form, and so on, but generally speaking, third normal form is enough.
·        First Normal Form (1NF)
·        Second Normal Form (2NF)
·        Third Normal Form (3NF)

 

SQL Data Types


 The SQL data type defines a kind of value that a column can contain.
In a database table, every column is required to have a name and a data type.
These are the general data types in SQL.
Data-type
Syntax
Explanation
Numeric
NUMERIC(SIZE)

Numeric
NUMERIC(P,S)
where 'p' is precision value and 's' is scale value.
    Character
CHAR(X)
where 'x' is the character's number to store
  Character     varying
VARCHAR2(X)
where 'x' is the character's number to store
Date
DATE
it stores year, month and days values.
Time
TIME
it stores hour, minute and second values
Timestamp
TIMESTAMP
        it stores year, month, day, hour, minute and second values

Operator

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations.
Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.
·         Arithmetic operators
·         Comparison operators
·         Logical operators
·         Operators used to negate conditions
SQL Arithmetic Operators:
Assume variable a holds 10 and variable b holds 20 then:

 Show Example :

Operator
Description
Example
+
Addition - Adds values on either side of the operator
a + b will give 30
-
Subtraction - Subtracts right hand operand from left hand operand
a - b will give -10
*
Multiplication - Multiplies values on either side of the operator
a * b will give 200
/
Division - Divides left hand operand by right hand operand
b / a will give 2
%
Modulus - Divides left hand operand by right hand operand and returns remainder
b % a will give 0


SQL Comparison Operators:
Assume variable a holds 10 and variable b holds 20 then:

=
Checks if the value of two operands are equal or not, if yes then condition becomes true.
(a = b) is not true.
!=
Checks if the value of two operands are equal or not, if values are not equal then condition becomes true.
(a != b) is true.
<> 
Checks if the value of two operands are equal or not, if values are not equal then condition becomes true.
(a <> b) is true.
> 
Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
(a > b) is not true.
< 
Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
(a < b) is true.
>=
Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
(a >= b) is not true.
<=
Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
(a <= b) is true.
!<
Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
(a !< b) is false.
!>
Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.
(a !> b) is true.


SQL Logical Operators:
Here is a list of all the logical operators available in SQL.


Operator
Description
ALL
The ALL operator is used to compare a value to all values in another value set.
AND
The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANY
The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN
The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS
The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN
The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE
The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT
The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN etc. This is negate operator.
OR
The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL
The NULL operator is used to compare a value with a NULL value.
UNIQUE
The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

Data Definition Language

DDL:  DR CAT: drop, rename, create , truncate
SQL CREATE TABLE
SQL CREATE TABLE statement is used to create table in a database.
If you want to create a table, you should name the table and define its column and each column's data type.
Let's see the simple syntax to create the table.
create table "tablename"  ("column1" "data type",  
"column2" "data type",  
"column3" "data type",  
...  
"columnN" "data type");  
The data type of the columns may vary from one database to another. For example, NUMBER is supported in Oracle database for integer value whereas INT is supported in MySQL.
Let us take an example to create a STUDENTS table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.
SQL>   CREATE TABLE "SYSTEM"."STUDENT"
   (           "SNO" NUMBER(3,0),
                "SNAME" VARCHAR2(20 BYTE),
                "SAGE" NUMBER(2,0)
   )

You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else you can use DESC command as follows:

SQL> DESC STUDENT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNO                                                NUMBER(3)
 SNAME                                              VARCHAR2(20)
 SAGE                                               NUMBER(2)

Now you have the STUDENTS table available in your database and you can use to store required information related to students.
SQL DROP TABLE
A SQL DROP TABLE statement is used to delete a table definition and all data from a table.
This is very important to know that once a table is deleted all the information available in the table is lost forever, so we have to be very careful when using this command.
Let's see the syntax to drop the table from the database.
1.      
     DROP TABLE "table_name";  
Let us take an example:
First we verify STUDENTS1 table and then we would delete it from the database.
SQL> DESC STUDENTS;
FIELD
TYPE
NULL
KEY
DEFAULT
EXTRA
ID
Int(11)
NO
PRI


NAME
Varchar(20)
NO



AGE
Int(11)
NO



ADDRESS
Varchar(25)
YES

NULL

4 rows in set (0.00 sec)
This shows that STUDENTS table is available in the database, so we can drop it as follows:

 SQL>DROP TABLE STUDENTS1;
Query OK, 0 rows affected (0.01 sec)

 SQL RENAME TABLE

 SQL RENAME TABLE syntax is used to change the name of a table. Sometimes, we choose non-meaningful name for the table. So it is required to be changed.
Let's see the syntax to rename a table from the database.

 ALTER TABLE table_name  RENAME TO new_table_name;  
Optionally, you can write following command to rename the table.
RENAME old_table _name To new_table_name;  
Let us take an example of a table named "STUDENTS", now due to some reason we want to change it into table name "ARTISTS".
Table1: students2

Name
Age
City
Amrita gill
25
Amritsar
Amrender sirohi
22
Ghaziabad
Divya khosla
20
Delhi
You should use any one of the following syntax to RENAME the table name:
1.      
        ALTER TABLE STUDENTS2  RENAME TO ARTISTS;  
Or
1.       RENAME STUDENTS TO ARTISTS;  

After that the table "students" will be changed into table name "artists" 

SQL TRUNCATE TABLE

A truncate SQL statement is used to remove all rows (complete data) from a table. It is similar to the DELETE statement with no WHERE clause.

TRUNCATE TABLE Vs DELETE TABLE

Truncate table is faster and uses lesser resources than DELETE TABLE command.

TRUNCATE TABLE Vs DROP TABLE

Drop table command can also be used to delete complete table but it deletes table structure too. TRUNCATE TABLE doesn't delete the structure of the table.
Let's see the syntax to truncate the table from the database.
TRUNCATE TABLE table_name;  
For example, you can write following command to truncate the data of employee table
TRUNCATE TABLE Employee;  
Note: The rollback process is not possible after truncate table statement. Once you truncate a table you cannot use a flashback table statement to retrieve the content of the table.

 SQL COPY TABLE

If you want to copy a SQL table into another table in the same SQL server database, it is possible by using the select statement.
The syntax of copying table from one to another is given below:
Select * into <destination table> from <source table>  
For example, you can write following command to copy the records of hr_employee table into employee table.
Select * into admin_employee from hr_employee;  

SQL TEMP TABLE

The concept of temporary table is introduced by SQL server. It helps developers in many ways:
Temporary tables can be created at run-time and can do all kinds of operations that a normal table can do. These temporary tables are created inside tempdb database.
There are two types of temp tables based on the behavior and scope.
  1. Local Temp Variable
  2. Global Temp Variable

Local Temp Variable

Local temp tables are only available at current connection time. It is automatically deleted when user disconnects from instances. It is started with hash (#) sign.
CREATE TABLE #local temp table (  
User id int,  
Username varchar (50),  
User address varchar (150)  
)  

Global Temp Variable

Global temp tables name starts with double hash (##). Once this table is created, it is like a permanent table. It is always ready for all users and not deleted until the total connection is withdrawn.
CREATE TABLE ##new global temp table (  
User id int,  
User name varchar (50),  
User address varchar (150)  
)  

SQL ALTER TABLE

The ALTER TABLE statement is used to add, modify or delete columns in an existing table. It is also used to rename a table.
You can also use SQL ALTER TABLE command to add and drop various constraints on an existing table.

SQL ALTER TABLE Add Column

If you want to add columns in SQL table, the SQL alter table syntax is given below:
ALTER TABLE table_name ADD column_name column-definition;  
If you want to add multiple columns in table, the SQL table will be
ALTER TABLE table_name   
ADD (column_1 column-definition,  
           column_2 column-definition,  
           .....  
           column_n column-definition);  

SQL ALTER TABLE Modify Column

If you want to modify an existing column in SQL table, syntax is given below:
ALTER TABLE table_name MODIFY column_name column_type;  
If you want to modify multiple columns in table, the SQL table will be
ALTER TABLE table_name   
MODIFY (column_1 column_type,  
                  column_2 column_type,  
                 .....  
                  column_n column_type);  

SQL ALTER TABLE DROP Column

The syntax of alter table drop column is given below:
ALTER TABLE table_name DROP COLUMN column_name;  

SQL ALTER TABLE RENAME Column

The syntax of alter table rename column is given below:
ALTER TABLE table_name  RENAME COLUMN old_name to new_name;  

DATA MANUPLATION LANGUAGE

DML: SUDI : select , update , delete , insert

SQL SELECT                

The most commonly used SQL command is SELECT statement. It is used to query the database and retrieve selected data that follow the conditions we want.
In simple words, we can say that the select statement used to query or retrieve data from a table in the database.
Let's see the syntax of select statement.
SELECT expressions  
FROM tables  
WHERE conditions;  
Here expression is the column that we want to retrieve.
Tables indicate the tables, we want to retrieve records from.

Optional clauses in SELECT statement

There are some optional clauses in SELECT statement:
[WHERE Clause] : It specifies which rows to retrieve.
[GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be applied to each group.
[HAVING Clause] : It selects among the groups defined by the GROUP BY clause.
[ORDER BY Clause] : It specifies an order in which to return the rows.
For example, let a database table: student_details;
1
Amar
Sharma
20
Maths
Cricket
2
Akbar
Khan
22
Biology
Football
3
Anthony
Milton
25
Commerce
Gambling
From the above example, select the first name of all the students. To do so, query should be like this:

 SELECT first_name FROM student_details;  

 Note: the SQL commands are not case sensitive. We can also write the above SELECT statement as:
select first_name from student_details;  
Now, you will get following data:
Amar
Akbar
Anthony
We can also retrieve data from more than one column. For example, to select first name and last name of all the students, you need to write
SELECT first_name, last_name FROM student_details;  
Now, you will get following data:
Amar
Sharma
Akbar
Khan
Anthony
Milton
We can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement.
Here a point is notable that only SELECT and FROM statements are necessary in SQL SELECT statements. Other clauses like WHERE, GROUP BY, ORDER BY, HAVING may be optional.

 SQL SELECT UNIQUE

Actually, there is no difference between DISTINCT and UNIQUE.
SELECT UNIQUE is an old syntax which was used in oracle description but later ANSI standard defines DISTINCT as the official keyword.
After that oracle also added DISTINCT but did not withdraw the service of UNIQUE keyword for the sake of backward compatibility.
In simple words, we can say that the select statement used to query or retrieve data from a table in the database.
Let's see the syntax of select unique statement.
SELECT UNIQUE column_name  FROM table_name;  

SQL SELECT DISTINCT

The SQL DISTINCT command is used with SELECT key word to retrieve only distinct or unique data.
In a table, there may be a chance to exist a duplicate value and sometimes we want to retrieve only unique values. In such scenarios, SQL SELECT DISTINCT statement is used.
Let's see the syntax of select distinct statement.
SELECT DISTINCT column_name ,column_name  FROM  table_name;  
Let's try to understand it by the table given below:
Here is a table of students from where we want to retrieve distinct information For example: distinct home-town.
SELECT DISTINCT home_town  FROM students

  
Student_Name
Gender
Cell_Number
Home_Town
Raja
Male
9876541255
Chennai
Sudha
Female
7845924578
Chennai
Suresh
Male
8547896542
Hyderabad
Now, it will return two rows.
Home_Town
Chennai
Hyderabad

 

SQL WHERE

A WHERE clause in SQL is a data manipulation language statement.
WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query.
Actually. it filters the records. It returns only those queries which fulfill the specific conditions.
WHERE clause is used in SELECT, UPDATE, DELETE statement etc.
Let's see the syntax for sql where:
SELECT column1, column 2, ... column n  
FROM    table_name  
WHERE [conditions]  
WHERE clause uses some conditional selection
=
Equal
> 
greater than
< 
less than
>=
greater than or equal
<=
less than or equal
<> 
not equal to


SQL AND

The SQL AND condition is used in SQL query to create two or more conditions to be met.
It is used in SQL SELECT, INSERT, UPDATE and DELETE statements.
Let's see the syntax for SQL AND:
SELECT columns  FROM tables  WHERE condition 1  AND condition 2;  
The SQL AND condition requires that both conditions should be met.
The SQL AND condition also can be used to join multiple tables in a SQL statement.

SQL "AND" example with "INSERT" statement

This is how an SQL "AND" condition can be used in the SQL INSERT statement.
For example:
INSERT INTO suppliers  
(supplier_id, supplier_name)  
SELECT account_no, name  
FROM customers  
WHERE customer_name ='IBM'  
AND employees =1000;  

SQL "AND" example with "UPDATE" statement

This is how the "AND" condition can be used in the SQL UPDATE statement.
For example:
UPDATE suppliers  
SET supplier_name = 'HP'  
WHERE supplier_name = 'IBM'  
AND offices = 8;  

SQL "AND" example with "DELETE" statement

This is how an SQL "AND" condition can be used in the SQL DELETE statement.
For example:
DELETE FROM suppliers  
WHERE supplier_name = 'IBM'  
AND product = 'PC computers';  

SQL OR

The SQL OR condition is used in a SQL query to create a SQL statement where records are returned when any one of the condition met. It can be used in a SELECT statement, INSERT statement, UPDATE statement or DELETE statement.
Let's see the syntax for OR condition:
SELECT columns  
FROM tables  
WHERE condition 1  
OR condition 2;  

SQL "OR" example with SQL SELECT

SELECT *   
FROM suppliers  
WHERE city = 'New York'  
OR available_products >= 250;  

SQL "OR" example with SQL INSERT

You can see in below example that how an SQL "OR" condition is used with SQL insert statement.
For example:
INSERT INTO suppliers(supplier_id, supplier_name)  
SELECT account_no, name  
FROM customers  
WHERE city = 'New Delhi'  
OR city = 'Ghaziabad';  

SQL "OR" example with SQL UPDATE

For example:
UPDATE suppliers   
SET supplier_name = 'HP'  
WHERE supplier_name = 'IBM'  
OR available_product >36;  

SQL "OR" example with SQL DELETE

For example:
DELETE FROM suppliers  
WHERE supplier_name = 'IBM'  
OR employee <=100;
SQL WITH CLAUSE
The SQL WITH clause is used to provide a sub-query block which can be referenced in several places within the main SQL query. It was introduced by oracle in oracle 9i release2 database.
There is an example of employee table:
Syntax for the SQL WITH clause ?
This syntax is for SQL WITH clause using a single sub-query alias.
WITH <alias_name> AS (sql_sub-query_statement)  
SELECT column_list FROM <alias_name> [table name]  
[WHERE <join_condition>]  
When you use multiple sub-query aliases, the syntax will be as follows.
WITH <alias_name_A>  AS (sql_sub-query_statement)  
<alias_name_B> AS (sql_sub-query_statement_from_alias_name_A  
Or sql_sub-query_statement)  
SELECT <column_list>  
FROM <alias_name_A >,< alias_name_B >, [tablenames]  
[WHERE < join_condition>]
SQL ORDER BY Clause
The SQL ORDER BY clause is used for sorting data in ascending and descending order based on one or more columns.
Some databases sort query results in ascending order by default.
SQL ORDER BY syntax:
SELECT expressions  
FROM tables  
WHERE conditions  
ORDER BY expression [ASC | DESC];  
Let us take a CUSTOMERS table having the following records:

ID
     NAME              AGE          ADDRESS
SALARY
1
Himani gupta
21
Modinagar
22000

2
Shiva tiwari
22
Bhopal
21000

3
Ajeet bhargav
45
Meerut
65000

4
Ritesh yadav
36
Azamgarh
26000

This is an example that would sort the result in ascending order by NAME and SALARY.

 SELECT * FROM CUSTOMERS  
ORDER BY NAME, SALARY;  
This would produce the following result.
ID
NAME
AGE
ADDRESS
SALARY
3
Ajeet bhargav
45
Meerut
65000
5
Balwant singh
45
Varanasi
36000
1
Himani gupta
21
Modinagar
22000
6
Mahesh Sharma
26
Mathura
22000
4
Ritesh yadav
36
Azamgarh
26000
2
Shiva tiwari
22
Bhopal
21000
This is an example to sort the result in descending order by NAME.

 SELECT * FROM CUSTOMERS  
ORDER BY NAME DESC;  
This would produce the following result.
ID
NAME
AGE
ADDRESS
SALARY
2
Shiva tiwari
22
Bhopal
21000
4
Ritesh yadav
36
Azamgarh
26000
6
Mahesh Sharma
26
Mathura
22000
1
Himani gupta
21
Modinagar
22000
5
Balwant singh
45
Varanasi
36000
3
Ajeet bhargav
45
Meerut
65000

SQL ORDER BY CLAUSE WITH ASCENDING ORDER

This statement is used to sort data in ascending order. If you miss the ASC attribute, SQL ORDER BY query takes ascending order by default.
Let?s take an example of supplier
SELECT supplier_city  
FROM suppliers  
WHERE supplier_name = 'IBM'  
ORDER BY supplier_city;  
Let us take a CUSTOMERS table having the following records:
ID
NAME
AGE
ADDRESS
SALARY
1
Himani gupta
21
Modinagar
22000
2
Shiva tiwari
22
Bhopal
21000
3
Ajeet bhargav
45
Meerut
65000
4
Ritesh yadav
36
Azamgarh
26000
5
Balwant singh
45
Varanasi
36000
6
Mahesh Sharma
26
Mathura
22000

This is an example to sort the result in ascending order by NAME and SALARY.

 SELECT * FROM CUSTOMERS  ORDER BY NAME, SALARY;  
This would produce the following result.
ID
NAME
AGE
ADDRESS
SALARY
3
Ajeet bhargav
45
Meerut
65000
5
Balwant singh
45
Varanasi
36000
1
Himani gupta
21
Modinagar
22000
6
Mahesh Sharma
26
Mathura
22000
4
Ritesh yadav
36
Azamgarh
26000
2
Shiva tiwari
22
Bhopal
21000

SQL ORDER BY CLAUSE WITH DESCENDING ORDER:

This statement is used to sort data in descending order. You should use the DESC attribute in your ORDER BY clause as follows.
SELECT supplier_city  
FROM suppliers  
WHERE supplier_name = 'IBM'  
ORDER BY supplier_city DESC;  
Let's see an example of an employee table:
ID
NAME
AGE
ADDRESS
SALARY
1
Himani gupta
21
Modinagar
22000
2
Shiva tiwari
22
Bhopal
21000
3
Ajeet bhargav
45
Meerut
65000
4
Ritesh yadav
36
Azamgarh
26000
5
Balwant singh
45
Varanasi
36000
6
Mahesh Sharma
26
Mathura
22000
This is an example to sort the result in descending order by NAME.

 SELECT * FROM CUSTOMERS  
ORDER BY NAME DESC;  
This would produce the following result.
ID
NAME
AGE
ADDRESS
SALARY
2
Shiva tiwari
22
Bhopal
21000
4
Ritesh yadav
36
Azamgarh
26000
6
Mahesh Sharma
26
Mathura
22000
1
Himani gupta
21
Modinagar
22000
5
Balwant singh
45
Varanasi
36000
3
Ajeet bhargav
45
Meerut
65000

SQL ORDER BY RANDOM

If you want the resulting record to be ordered randomly, you should use the following codes according to several databases.
Here a question occurs that what is the need of fetching a random record or a row from a database?
Sometimes you may want to display random information like articles, links, pages etc. to your user.
If you want to fetch random rows from any of the databases you have to use some queries which are altered according to the databases.

Select a random row with MySQL:

If you want to return a random row with MY SQL, Use the following code:
SELECT column FROM table  
ORDER BY RAND ()  
LIMIT 1  

Select a random row with Postgre SQL:

SELECT column FROM table   
ORDER BY RANDOM ()  
LIMIT 1  

Select a random row with oracle:

SELECT column FROM   
(SELECT column FROM table  
ORDER BY dbms_random.value)  
WHERE rownum = 1  

SQL ORDER BY LIMIT

We can retrieve limited rows from the database. I can be used in pagination where are forced to show only limited records like 10, 50, 100 etc.

LIMIT CLAUSE FOR ORACLE SQL:

If you want to use LIMIT clause with SQL, you have to use ROWNUM queries because it is used after result are selected.
You should use the following code:
SELECT name, age  
FROM   
(SELECT name, age, ROWNUM r  
FROM   
(SELECT name, age, FROM employee_data  
ORDER BY age DESC  
)  
WHERE ROWNUM <=40  
)  
WHERE r >= 21;  
This query will give you 21th to 40th rows.
SQL SORTING ON MULTIPLE COLUMNS
Let's take an example of customer table which has many columns, the following SQL statement selects all customers from the table named "customer", stored by the "country" and "Customer-Name" columns:
SELECT * FROM customers  ORDER BY country, Customer-Name;  

 

SQL DELETE TABLE

The DELETE statement is used to delete rows from a table. If you want to remove a specific row from a table you should use WHERE condition.
DELETE FROM table_name [WHERE condition];  
But if you do not specify the WHERE condition it will remove all the rows from the table.
DELETE FROM table_name;  
There are some more terms similar to DELETE statement like as DROP statement and TRUNCATE statement but they are not exactly same there are some differences between them.
Here we point out them?

Difference between delete and truncate statements

There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified.
But it does not free the space containing by the table.
The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.
To delete all the rows from student table, the query should be like this:
TRUNCATE TABLE students;  

Difference b/w DROP and TRUNCATE statement:

When you use the drop statement it deletes the table's row together with the table's definition so all the relationships of that table with other tables will no longer be valid.
When you drop a table:
  • Table structure will be dropped
  • Relationship will be dropped
  • Integrity constraints will be dropped
  • Access privileges will also be dropped
On the other hand when we TRUNCATE a table, the table structure remains the same, so you will not face any of the above problems.

DATA MANUPLATION LANGUAGE

SQL INSERT STATEMENT

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.
There are two ways to insert data in a table:
  1. By SQL insert into statement
    1. By specifying column names
    2. Without specifying column names
  2. By SQL insert into select statement

1) Inserting data directly into a table

You can insert a row in the table by using SQL INSERT INTO command. But there are 2 ways to do this.
You can specify or ignore the column names while using INSERT INTO statement.
To insert partial column values, you must have to specify the column names. But if you want to insert all the column values, you can specify or ignore the column names.
If you specify the column names, syntax of the insert into statement will be as follows:
INSERT INTO TABLE_NAME  
[(col1, col2, col3,.... col N)]  
VALUES (value1, value2, value 3, .... Value N);  
Here col1, col2, col3, .... colN are the columns of the table in which you want to insert data.
But, If you ignore the column names, syntax of the insert into statement will be as follows:
INSERT INTO TABLE_NAME  
VALUES (value1, value2, value 3, .... Value N);  

2) Inserting data through SELECT Statement

SQL INSERT INTO SELECT Syntax
INSERT INTO table_name  
[(column1, column2, .... column)]  
SELECT column1, column2, .... Column N  
FROM table_name [WHERE condition];  
SQL INSERT INTO VALUE
There are two ways to insert values in a table.
In the first method there is no need to specify the column name where the data will be inserted, you need only their values.
INSERT INTO table_name  
VALUES (value1, value2, value3....);  
The second method specifies both the column name and values which you want to insert.
INSERT INTO table_name (column1, column2, column3....)  
VALUES (value1, value2, value3.....);  
Let's take an example of table which has five records within it.
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)  
VALUES (1, ABHIRAM, 22, ALLAHABAD);  
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)  
VALUES (2, ALKA, 20, GHAZIABAD);  
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)  
VALUES (3, DISHA, 21, VARANASI);  
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)  
VALUES (4, ESHA, 21, DELHI);  
INSERT INTO STUDENTS (ROLL_NO, NAME, AGE, CITY)  
VALUES (5, MANMEET, 23, JALANDHAR);  
It will show the following table as the final result.
ROLL_NO
NAME
AGE
CITY
1
ABHIRAM
22
ALLAHABAD
2
ALKA
20
GHAZIABAD
3
DISHA
21
VARANASI
4
ESHA
21
DELHI
5
MANMEET
23
JALANDHAR
You can create a record in CUSTOMERS table by using this syntax also.

 INSERT INTO CUSTOMERS   
VALUES (6, PRATIK, 24, KANPUR);  
The following table will be as follow:
ROLL_NO
NAME
AGE
CITY
1
ABHIRAM
22
ALLAHABAD
2
ALKA
20
GHAZIABAD
3
DISHA
21
VARANASI
4
ESHA
21
DELHI
5
MANMEET
23
JALANDHAR
6
PRATIK
24
KANPUR


SQL INSERT MULTIPLE ROWS
Many times developers ask that is it possible to insert multiple rows into a single table in a single statement. Currently developers have to write multiple insert statement when they insert values in a table. It is not only boring, also time consuming. To get rid from this you should try this syntax. Actually there are three different methods to insert multiple values into a single table.
  1. Traditional method (simple insert)
  2. SQL insert select
  3. SQL server 2008+ Row Construction
Insert multiple values in SQL server
CREATE TABLE student (ID INT VALUE VARCHAR (100));  
1. SQL INSERT: (TRADITIONAL INSERT)
INSERT INTO student (ID, NAME)  
VALUES (1, 'ARMAAN');  
INSERT INTO student (ID, NAME)  
VALUES (2, 'BILLY');  
INSERT INTO student (ID, NAME)  
VALUES (3, 'CHARLIE');  
TO CLEAN-UP:
TRUNCATE TABLE student;  
2. INSERT SELECT: (SELECT UNION INSERT)
INSERT INTO student (ID, NAME)  
SELECT 1, 'ARMAAN'  
UNION ALL   
SELECT 2, 'BILLY'  
UNION ALL  
SELECT 3, 'CHARLIE';  
TO CLEAN-UP:  
TRUNCATE TABLE student;  
3.SQL Server 2008+ Row Construction
INSERT INTO student (ID, NAME)  
VALUES (1, 'ARMAAN'), (2, 'BILLY'), (3, 'CHARLIE');  
TO CLEAN-UP:  
DROP TABLE student;  

 

SQL UPDATE

The SQL commands (UPDATE and DELETE) are used to modify the data that is already in the database. The SQL DELETE command uses a WHERE clause.
SQL UPDATE statement is used to change the data of the records held by tables. Which rows is to be update, it is decided by a condition. To specify condition, we use WHERE clause.
The UPDATE statement can be written in following form:
UPDATE table_name SET [column_name1= value1,... column_nameN = valueN] [WHERE condition]  
Let's see the Syntax:
UPDATE table_name  
SET column_name = expression  
WHERE conditions  
Let's take an example: here we are going to update an entry in the source table.
SQL statement:
UPDATE students  
SET User_Name = 'beinghuman'  
WHERE Student_Id = '3'  
Source Table:

Student_Id           
First_name
Last_Name
User_Name
1
Ada
Sharma
Sharmili
2
Rahul
Maurya
Sofamous
3
James
Walker
jonny








See the result after updating value:

Student_Id
First_Name
Last_Name
User_Name
1
Ada
Sharma
Sharmili
2
Rahul
Maurya
Sofamous
3
James
Walker
beinghuman

Updating Multiple Fields:

If you are going to update multiple fields, you should separate each field assignment with a comma.
SQL UPDATE statement for multiple fields:
1.       UPDATE students  
2.       SET User_Name = 'beserious', First_Name = 'Johnny'  
3.       WHERE Student_Id = '3'  
Result of the table is given below:

Student_Id
First_Name
Last_Name
ser_Name
1
Ada
Sharma
sharmili
2
Rahul
Maurya
sofamous
3
Johnny
Walker
beserious

MYSQL SYNTAX FOR UPDATING TABLE:

 UPDATE table_name  
SET field1 = new-value1, field2 = new-value2,  
[WHERE CLAUSE]  
SQL UPDATE SELECT:
SQL UPDATE WITH SELECT QUERY:
We can use SELECT statement to update records through UPDATE statement.
SYNTAX:
UPDATE tableDestination  
SET tableDestination.col = value  
WHERE EXISTS (  
SELECT col2.value  
FROM  tblSource  
WHERE tblSource.join_col = tblDestination. Join_col  
AND  tblSource.Constraint = value)  
You can also try this one -
UPDATE   
Table   
SET  
Table.column1 = othertable.column 1,  
Table.column2 = othertable.column 2  
FROM   
Table  
INNER JOIN  
Other_table  
ON  
Table.id = other_table.id  
My SQL SYNTAX:
If you want to UPDATE with SELECT in My SQL, you can use this syntax:
Let's take an example having two tables. Here,
First table contains -
Cat_id, cat_name,
And the second table contains -
Rel_cat_id, rel_cat_name

 SQL UPDATE COLUMN:

 We can update a single or multiple columns in SQL with SQL UPDATE query.
SQL UPDATE EXAMPLE WITH UPDATING SINGLE COLUMN:
UPDATE students  
SET student_id = 001  
WHERE student_name = 'AJEET';  
This SQL UPDATE example would update the student_id to '001' in the student table where student_name is 'AJEET'.
SQL UPDATE EXAMPLE WITH UPDATING MULTIPLE COLUMNS:
To update more than one column with a single update statement:
UPDATE students  
SET student_name = 'AJEET',  
Religion = 'HINDU'  
WHERE student_name = 'RAJU';  
This SQL UPDATE statement will change the student name to 'AJEET' and religion to 'HINDU' where the student name is 'RAJU'.

SQL UPDATE with JOIN

SQL UPDATE JOIN means we will update one table using another table and join condition.
Let us take an example of a customer table. I have updated customer table that contains latest customer details from another source system. I want to update the customer table with latest data. In such case, I will perform join between target table and source table using join on customer ID.
Let's see the syntax of SQL UPDATE query with JOIN statement.
UPDATE customer_table  
INNER JOIN  
Customer_table  
ON customer_table.rel_cust_name = customer_table.cust_id  
SET customer_table.rel_cust_name = customer_table.cust_name  

How to use multiple tables in SQL UPDATE statement with JOIN

Let's take two tables, table 1 and table 2.
Create table1
CREATE TABLE table1 (column1 INT, column2 INT, column3 VARCHAR (100))  
INSERT INTO table1 (col1, col2, col3)  
SELECT 1, 11, 'FIRST'  
UNION ALL  
SELECT 11,12, 'SECOND'  
UNION ALL   
SELECT 21, 13, 'THIRD'  
UNION ALL   
SELECT 31, 14, 'FOURTH'  
Create table2
CREATE TABLE table2 (column1 INT, column2 INT, column3 VARCHAR (100))  
INSERT INTO table2 (col1, col2, col3)  
SELECT 1, 21, 'TWO-ONE'  
UNION ALL  
SELECT 11, 22, 'TWO-TWO'  
UNION ALL   
SELECT 21, 23, 'TWO-THREE'  
UNION ALL   
SELECT 31, 24, 'TWO-FOUR'  
Now check the content in the table.
SELECT * FROM table_1  
SELECT * FROM table_2  

col 1
col 2
Col 3
1
1
11
First
2
11
12
Second
3
21
13
Third
4
31
14
Fourth

col 1
Col 2
Col 3
1
1
21
Two-One
2
11
22
Two-Two
3
21
23
Two-Three
4
31
24
Two-Four

Our requirement is that we have table 2 which has two rows where Col 1 is 21 and 31. We want to update the value from table 2 to table 1 for the rows where Col 1 is 21 and 31.
We want to also update the values of Col 2 and Col 3 only.
The most easiest and common way is to use join clause in the update statement and use multiple tables in the update statement.

 UPDATE table 1  
SET Col 2 = t2.Col2,  
Col 3 = t2.Col3  
FROM table1 t1  
INNER JOIN table 2 t2 ON t1.Col1 = t2.col1  
WHERE t1.Col1 IN (21,31)  
Check the content of the table
SELECT FROM table 1
SELECT FROM table 2

col 1
Col 2
Col 3
1
1
11
First
2
11
12
Second
3
21
23
Two-Three
4
31
24
Two-Four

col 1
Col 2
Col 3
1
1
21
First
2
11
22
Second
3
21
23
Two-Three
4
31
24
Two-Four
Here we can see that using join clause in update statement. We have merged two tables by the use of join clause.

 SQL UPDATE DATE

How to update a date and time field in SQL?
If you want to update a date & time field in SQL, you should use the following query.
let's see the syntax of sql update date.
UPDATE table   
SET Column_Name = 'YYYY-MM-DD HH:MM:SS'  
WHERE Id = value  
Let us check this by an example:
Firstly we take a table in which we want to update date and time fields.
If you want to change the first row which id is 1 then you should write the following syntax:
UPDATE table   
SET EndDate = '2014-03-16 00:00:00.000'   
WHERE Id = 1   
<pNote: you should always remember that SQL must attach default 00:00:00.000 automatically.
This query will change the date and time field of the first row in that above assumed table.

SQL DELETE

The SQL DELETE statement is used to delete rows from a table. Generally DELETE statement removes one or more records from a table.

SQL DELETE Syntax

Let's see the Syntax for the SQL DELETE statement:
DELETE FROM table_name [WHERE condition];  
Here table_name is the table which has to be deleted. The WHERE clause in SQL DELETE statement is optional here.

SQL DELETE Example

Let us take a table, named ?EMPLOYEE? table.
EMP_ID
EMP_NAME
CITY
SALARY
01
Adarsh Singh
Obra
20000
02
Sanjay Singh
Meerut
21000
03
Priyanka Sharma
Raipur
25000
04
Esha Singhal
Delhi
26000
Example of delete with WHERE clause is given below:

 DELETE FROM EMPLOYEE WHERE ID=101;  

 Resulting table after the query:
EMP_ID
EMP_NAME
CITY
SALARY
02
Sanjay Singh
Meerut
21000
03
Priyanka Sharma
Raipur
25000
04
Esha Singhal
Delhi
26000
Another example of delete statement is given below

 DELETE FROM EMPLOYEE;  

 Resulting table after the query:
EMP_ID
EMP_NAME
CITY
SALARY
It will delete all the records of EMPLOYEE table.
It will delete the all the records of EMPLOYEE table where ID is 101.
The WHERE clause in the SQL DELETE statement is optional and it identifies the rows in the column that gets deleted.
WHERE clause is used to prevent the deletion of all the rows in the table, If you don't use the WHERE clause you might loss all the rows.

Invalid DELETE Statement for ORACLE database

You cannot use * (asterisk) symbol to delete all the records.
DELETE * FROM EMPLOYEE;  
SQL DELETE ROW
Let us take an example of student.
Original table:
ID
STUDENT _NAME
ADDRESS
001
AJEET MAURYA
GHAZIABAD
002
RAJA KHAN
LUCKNOW
003
RAVI MALIK
DELHI
If you want to delete a student with id 003 from the student_name table, then the SQL DELETE query should be like this:
DELETE FROM student_name  
WHERE id = 003;  
Resulting table after SQL DELETE query:
ID
STUDENT_NAME
ADDRESS
001
AJEET MAURYA
GHAZIABAD
002
RAJA KHAN
LUCKNOW
SQL DELETE ALL ROWS
The statement SQL DELETE ALL ROWS is used to delete all rows from the table. If you want to delete all the rows from student table the query would be like,

 DELETE FROM STUDENT_NAME;  
Resulting table after using this query:
ID
STUDENT_NAME
ADDRESS
SQL DELETE DUPLICATE ROWS
If you have got a situation that you have multiple duplicate records in a table, so at the time of fetching records from the table you should be more careful. You make sure that you are fetching unique records instead of fetching duplicate records.
To overcome with this problem we use DISTINCT keyword.
It is used along with SELECT statement to eliminate all duplicate records and fetching only unique records.
SYNTAX:
The basic syntax to eliminate duplicate records from a table is:
SELECT DISTINCT column1, column2,....columnN  
FROM table _name  
WHERE [conditions]  
EXAMPLE:
ID
STUDENT_NAME
ADDRESS
001
AJEET MAURYA
GHAZIABAD
002
RAJA KHAN
LUCKNOW

Firstly we should check the SELECT query and see how it returns the duplicate percentage records.

 SQL > SELECT PERCENTAGE FROM STUDENTS  
ORDER BY PERCENTAGE;  

PERCENTAGE
63.5
71.2
72.3
72.3
72.8
75.5
Now let us use SELECT query with DISTINCT keyword and see the result. This will eliminate the duplicate entry.

 SQL > SELECT DISTINCT PERCENTAGE FROM STUDENTS  ORDER BY PERCENTAGE;  

PERCENTAGE
63.5
71.2
72.3
72.8
75.5

 

TRANSCATON CONTROL LANGUAGE


A database transaction is an atomic unit of work that may consist of one or more related SQL statements. It is called atomic because the database modifications brought about by the SQL statements that constitute a transaction can collectively be either committed, i.e., made permanent to the database or rolled back (undone) from the database.
A successfully executed SQL statement and a committed transaction are not same. Even if an SQL statement is executed successfully, unless the transaction containing the statement is committed, it can be rolled back and all changes made by the statement(s) can be undone.

 Starting an Ending a Transaction

A transaction has a beginning and an end. A transaction starts when one of the following events take place:
·        The first SQL statement is performed after connecting to the database.
·        At each new SQL statement issued after a transaction is completed.
A transaction ends when one of the following events take place:
·        A COMMIT or a ROLLBACK statement is issued.
·        A DDL statement, like CREATE TABLE statement, is issued; because in that case a COMMIT is automatically performed.
·        A DCL statement, such as a GRANT statement, is issued; because in that case a COMMIT is automatically performed.
·        User disconnects from the database.
·        User exits from SQL*PLUS by issuing the EXIT command, a COMMIT is automatically performed.
·        SQL*Plus terminates abnormally, a ROLLBACK is automatically performed.
·        A DML statement fails; in that case a ROLLBACK is automatically performed for undoing that DML statement.

Committing a Transaction

A transaction is made permanent by issuing the SQL command COMMIT. The general syntax for the COMMIT command is:
COMMIT;
For example,
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,'Ramesh',32,'Ahmedabad',2000.00);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,'Khilan',25,'Delhi',1500.00);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,'kaushik',23,'Kota',2000.00);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,'Chaitali',25,'Mumbai',6500.00);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,'Hardik',27,'Bhopal',8500.00);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,'Komal',22,'MP',4500.00);
COMMIT;


Rolling Back Transactions


Changes made to the database without COMMIT could be undone using the ROLLBACK command.
The general syntax for the ROLLBACK command is:
ROLLBACK [TO SAVEPOINT <savepoint_name>];
When a transaction is aborted due to some unprecedented situation, like system failure, the entire transaction since a commit is automatically rolled back. If you are not using savepoint, then simply use the following statement to rollback all the changes:
ROLLBACK;

Savepoints


Savepoints are sort of markers that help in splitting a long transaction into smaller units by setting some checkpoints. By setting savepoints within a long transaction, you can roll back to a checkpoint if required. This is done by issuing the SAVEPOINT command.
The general syntax for the SAVEPOINT command is:
SAVEPOINT <savepoint_name>;
For example:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,'Rajnish',27,'HP',9500.00);
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8,'Riddhi',21,'WB',4500.00);
SAVEPOINT sav1;
 
UPDATE CUSTOMERS
SET SALARY = SALARY +1000;
ROLLBACK TO sav1;
 
UPDATE CUSTOMERS
SET SALARY = SALARY +1000
WHERE ID =7;
UPDATE CUSTOMERS
SET SALARY = SALARY +1000
WHERE ID =8;
COMMIT;
Here,ROLLBACK TO sav1; statement rolls back the changes up to the point, where you had marked savepoint sav1 and after that new changes will start.

Automatic Transaction Control


To execute a COMMIT automatically whenever an INSERT, UPDATE or DELETE command is executed, you can set the AUTOCOMMIT environment variable as:
SET AUTOCOMMIT ON;
You can turn-off auto commit mode using the following command:
SET AUTOCOMMIT OFF;

 

DATA CONTROL LANGUAGE

 

DCL are used to configure and control database object.

DCL : Grant, Revoke

Grant : Gives user right and privileges on database  object or schema .

Syntax:

Grant [privilege] [column] on [table] to [schema]

 

Ex:

Grant  update, insert(SNO,SNAME) on STUDENT to SYSTEM; 

Revoke : Remove or restricts user rights or privileges on database objects .

Syntax:

Revoke [privilege] [column] on [table] from [schema]

Ex:

Grant  update, insert(SNO,SNAME) on STUDENT from SYSTEM;

FUNCTIONS


SINGLE ROW FUNCTIONS :
CHAR/LENGHT :
SELECTLENGTH('SURESH') FROM DUAL;

SELECTCHR(65) FROM DUAL;
ASCII:

SELECT  FIRST_NAME, CHR(ASCII(FIRST_NAME)+32) FROM HR.EMPLOYEES;

INITCAP:

SELECTINITCAP('suresh') FROM DUAL;

UPPER:

SELECTupper('suresh') FROM DUAL;

LOWER:

SELECTlower('SURESH') FROM DUAL;

LTRIM:

SELECTlTRIM('     suresh') AS A FROM DUAL;

RTRIM:

SELECTrTRIM('suresh             ') AS A FROM DUAL;

TRIM:

SELECTTRIM('             suresh             ') AS A FROM DUAL;

LPAD:

SELECTLPAD('suresh',10,'*') FROM DUAL;

RPAD:

SELECTrPAD('suresh',10,'*') FROM DUAL;

VSIZE:

SELECTVSIZE('suresh') FROM DUAL;

REVERSE :

SELECTREVERSE('suresh') FROM DUAL;

CONCAT:

SELECTCONCAT('suresh','RAMESH') FROM DUAL;

SUBSTR:

SELECTSUBSTR('suresh',4,5) FROM DUAL;

SOUNDEX :

SELECT * FROM TEST2 WHERESOUNDEX(ENAME)=SOUNDEX('MURTHI');

TRANSLATE:

SELECTTRANSLATE('BLUE','B','JE' ) FROM DUAL;

REPLACE :
SELECTREPLACE('JACK AND JUE','J','BL' ) FROM DUAL;

MATH OF NUMARIC FUNCTIONS :

SELECTABS(-9.7) FROM DUAL;

ROUND:
SELECTROUND(9.7) FROM DUAL;

FLOOR:

SELECTFLOOR(9.7) FROM DUAL;

CELI:

SELECTCEIL(9.011) FROM DUAL;

SQRT:

SELECTSQRT(9.7) FROM DUAL;

POWER:

SELECTPOWER(10,2) FROM DUAL;

LOG:

SELECTLOG(10,10) FROM DUAL;

LN:

SELECTLN(10) FROM DUAL;

GREATEST:

SELECTGREATEST(10,524,8547,10) FROM DUAL;

LEAST:

SELECTLEAST(10,524,8547,10) FROM DUAL;

SIGN:                                --0,1 +VE,-VE

SELECTSIGN(1) FROM DUAL;

MOD:

SELECTMOD(10,2) FROM DUAL;


SIN:

SELECTSIN(0) FROM DUAL;

COS:

SELECTCOS(90) FROM DUAL;

TAN:

SELECTTAN(0) FROM DUAL;

DATE FUNCTIONS :

ADD_MONTHS:

SELECT ADD_MONTHS(SYSDATE,12)  FROM DUAL;

MONTHS_BETWEEN:

SELECT MONTHS_BETWEEN('19-JUL-16',SYSDATE)  FROM DUAL;

LAST_DAY:

SELECT LAST_DAY(SYSDATE)  FROM DUAL;

NEXT_DAY :

SELECT NEXT_DAY (SYSDATE,'SUNDAY')  FROM DUAL;

EXTREACT:


SELECTEXTRACT (YEARFROMSYSDATE)  FROM DUAL;
SELECTEXTRACT (DAYFROMSYSDATE)  FROM DUAL;


MULTIPLE ROWS:

The COUNT() function returns the number of rows that matches a specified criteria.
SELECT COUNT(column_name) FROM table_name;
The FIRST() function returns the first value of the selected column.
SELECT FIRST(column_name) FROM table_name;
The LAST() function returns the last value of the selected column.
SELECT LAST(column_name) FROM table_name;
The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name) FROM table_name;
The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name;
The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name;

GROUP BY
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
HAVING
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
The UCASE() function converts the value of a field to uppercase.
SELECT UCASE(column_name) FROM table_name;
The LCASE() function converts the value of a field to lowercase.
SELECT LCASE(column_name) FROM table_name;
The MID() function is used to extract characters from a text field.
SELECT MID(column_name,start,length) AS some_name FROM table_name;
The LEN() function returns the length of the value in a text field.
SELECT LEN(column_name) FROM table_name;
The ROUND() function is used to round a numeric field to the number of decimals specified.
SELECT ROUND(column_name,decimals) FROM table_name;
The NOW() function returns the current system date and time.
SELECT NOW() FROM table_name;
The FORMAT() function is used to format how a field is to be displayed.
SELECT FORMAT(column_name,format) FROM table_name;

CONSTRAINTS

SQL PRIMARY KEY

A column or columns is called primary key (PK) that uniquely identifies each row in the table.
If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.
When multiple columns are used as a primary key, it is known as composite primary key.
In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.
Inn terms of performance, less data means the database can process faster.

Points to remember for primary key:

  • Primary key enforces the entity integrity of the table.
  • Primary key always has unique data.
  • A primary key length cannot be exceeded than 900 bytes.
  • A primary key cannot have null value.
  • There can be no duplicate value for a primary key.
  • A table can contain only one primary key constraint.

Main advantage of primary key:

The main advantage of this uniqueness is that we get fast access.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.
CREATE TABLE students  
(  
S_Id number NOT NULL PRIMARY KEY,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
Address varchar (255),  
City varchar (255),  
)  

SQL primary key for multiple columns:


CREATE TABLE students  
(  
S_Id number NOT NULL,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
Address varchar (255),  
City varchar (255),  
CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)  
)  
Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id and LastName).

SQL primary key on ALTER TABLE

When table is already created and you want to create a PRIMARY KEY constraint on the ?S_Id? column you should use the following SQL:
Primary key on one column:
ALTER TABLE students  
ADD PRIMARY KEY (S_Id)  
Primary key on multiple column:
ALTER TABLE students  
ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)  

How to DROP a PRIMARY KEY constraint?

If you want to DROP (remove) a primary key constraint, you should use following syntax:
SQL Server / Oracle / MS Access:
ALTER TABLE students  
DROP CONSTRAINT pk_StudentID  

SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.
In simple words you can say that, a foreign key in one table used to point primary key in another table.
Let us take an example to explain it:
Here are two tables first one is students table and second is orders table.
Here orders are given by students.
First table:
S_Id
LastName
FirstName
CITY
1
MAURYA
AJEET
ALLAHABAD
2
JAISWAL
RATAN
GHAZIABAD
3
ARORA
SAUMYA
MODINAGAR
Second table:
O_Id
OrderNo
S_Id
1
99586465
2
2
78466588
2
3
22354846
3
4
57698656
1

  • The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table.
  • The "S_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The foreign key constraint is generally prevents action that destroy links between tables.
It also prevents invalid data to enter in foreign key column.

SQL FOREIGN KEY constraint ON CREATE TABLE:

(Defining a foreign key constraint on single column)
To create a foreign key on the "S_Id" column when the "Orders" table is created:
SQL Server /Oracle / MS Access:
CREATE TABLE Orders  
(  
O_Id int NOT NULL PRIMAY KEY,  
Order_No int NOT NULL,  
S_Id int FOREIGN KEY REFERENCES persons (S_Id)  
)  

SQL FOREIGN KEY constraint for ALTER TABLE:

If the Order table is already created and you want to create a FOREIGN KEY constraint on the ?S_Id? column, you should write the following syntax:
Defining a foreign key constraint on single column:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders  
ADD CONSTRAINT fk_PerOrders  
FOREIGN KEY(S_Id)  
REFERENCES Students (S_Id)  

DROP SYNTAX for FOREIGN KEY COSTRAINT:

If you want to drop a FOREIGN KEY constraint, use the following syntax:
SQL Server / Oracle / MS Access:
ALTER TABLE Orders  
DROP CONSTRAINT fk_PerOrders  

Difference between primary key and foreign key in SQL:

These are some important difference between primary key and foreign key in SQL-
Primary key cannot be null on the other hand foreign key can be null.
Primary key is always unique while foreign key can be duplicated.
Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.
There is only one primary key in the table on the other hand we can have more than one foreign key in the table.
By default primary key adds a clustered index on the other hand foreign key does not automatically create an index, clustered or non-clustered. You must manually create an index for foreign key.
UNIQUE KEY IN SQL

A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.
You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.
The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.
There is an automatically defined unique key constraint within a primary key constraint.
There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.
SQL UNIQUE KEY constraint on CREATE TABLE:
If you want to create a UNIQUE constraint on the ?S_Id? column when the ?students? table is created, use the following SQL syntax:
SQL Server / Oracle / MS Access:
(Defining a unique key constraint on single column):
CREATE TABLE students  
(  
S_Id int NOT NULL UNIQUE,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
City varchar (255)  
)  
(Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
 CREATE TABLE students  
(  
S_Id int NOT NULL,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
City varchar (255),  
CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)  
)  
SQL UNIQUE KEY constraint on ALTER TABLE:
If you want to create a unique constraint on ?S_Id? column when the table is already created, you should use the following SQL syntax:
(Defining a unique key constraint on single column):
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE students  
ADD UNIQUE (S_Id)  
(Defining a unique key constraint on multiple columns):
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE students  
ADD CONSTRAINT uc_StudentId UNIQUE  (S_Id, LastName)  
DROP SYNTAX FOR A FOREIGN KEY constraint:
If you want to drop a UNIQUE constraint, use the following SQL syntax:
SQL Server / Oracle / MS Access:
ALTER TABLE students  
DROP CONSTRAINT uc_studentID

JOINS :

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to combine two or more tables".
The SQL JOIN clause takes records from two or more tables in a database and combines it together.
ANSI standard SQL defines five types of JOIN :
  1. inner join,
  2. left outer join,
  3. right outer join,
  4. full outer join, and
  5. cross join.
In the process of joining, rows of both tables are combined in a single table.

Why SQL JOIN is used?

If you want to access more than one table through a select statement.
If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement.
The joining of two or more tables is based on common field between them.
SQL INNER JOIN also known as simple join is the most common type of join.

How to use SQL join or SQL Inner Join?

Let an example to deploy SQL JOIN process:
1.Staff table

ID
Staff_NAME
Staff_AGE
STAFF_ADDRESS
Monthley_Package
1
ARYAN
22
MUMBAI
18000
2
SUSHIL
32
DELHI
20000
3
MONTY
25
MOHALI
22000
4
AMIT
20
ALLAHABAD
12000
2.Payment table
Payment_ID
DATE
Staff_ID
AMOUNT
101
30-12-09
1
3000
102
22-02-10
3
2500
103
23-02-10
4
3500
So if you follow this JOIN statement to join these two tables ?
SELECT Staff_ID, Staff_NAME, Staff_AGE, AMOUNT   
   FROM STAFF s, PAYMENT p  
   WHERE s.ID =p.STAFF_ID;  
This will produce the result like this:
STAFF_ID
NAME
Staff_AGE
AMOUNT
3
MONTY
25
2500
1
ARYAN
22
3000
4
AMIT
25
3500
1
ARYAN
22
3000

SQL OUTER JOIN

In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.
If you take an example of employee table
Outer join of two types:
1.Left outer join (also known as left join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.
2.Right outer join (also known as right join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.
This diagram shows the different type of joins:
SQL LEFT JOIN

The SQL left join returns all the values from the left table and it also includes matching values from right table, if there are no matching join value it returns NULL.
BASIC SYNTAX FOR LEFT JOIN:
SELECT table1.column1, table2.column2....  
FROM table1   
LEFTJOIN table2  
ON table1.column_field = table2.column_field;  
let us take two tables in this example to elaborate all the things:
CUSTOMER TABLE:
ID
NAME
AGE
SALARY
1
ARYAN
51
56000
2
AROHI
21
25000
3
VINEET
24
31000
4
AJEET
23
32000
5
RAVI
23
42000
This is second table

ORDER TABLE:

O_ID
DATE
CUSTOMER_ID
AMOUNT
1
20-01-12
2
3000
2
12-02-12
2
2000
3
22-03-12
3
4000
4
11-04-12
4
5000

join these two tables with LEFT JOIN:

SQL SELECT ID, NAME, AMOUNT,DATE  
FROM CUSTOMER  
LEFT JOIN ORDER  
ON CUSTOMER.ID = ORDER.CUSTOMER_ID;  
This will produce the following result:
ID
NAME
AMOUNT
DATE
1
ARYAN
NULL
NULL
2
AROHI
3000
20-01-12
2
AROHI
2000
12-02-12
3
VINEET
4000
22-03-12
4
AJEET
5000
11-04-12
5
RAVI
NULL
NULL

SQL RIGHT JOIN

The SQL right join returns all the values from the rows of right table. It also includes the matched values from left table but if there is no matching in both tables, it returns NULL.
Basic syntax for right join:

SELECT table1.column1, table2.column2.....  
FROM table1   
RIGHT JOIN table2  
ON table1.column_field = table2.column_field;  
let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.
CUSTOMER TABLE:
ID
NAME
AGE
SALARY
1
ARYAN
51
56000
2
AROHI
21
25000
3
VINEET
24
31000
4
AJEET
23
32000
5
RAVI
23
42000
and this is the second table:
ORDER TABLE:
DATE
O_ID
CUSTOMER_ID
AMOUNT
20-01-12
1
2
3000
12-02-12
2
2
2000
22-03-12
3
3
4000
11-04-12
4
4
5000
Here we will join these two tables with SQL RIGHT JOIN:

SQL> SELECT ID,NAME,AMOUNT,DATE  
FROM CUSTOMER  
RIGHT JOIN ORDER  
ON CUSTOMER.ID = ORDER.CUSTOMER_ID;  
ID
NAME
AMOUNT
DATE
2
AROHI
3000
20-01-12
2
AROHI
2000
12-02-12
3
VINEET
4000
22-03-12
4
AJEET
5000
11-04-12

SQL FULL JOIN

The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.
SQL full outer join and SQL join are same. generally it is known as SQL FULL JOIN.

SQL full outer join:

What is SQL full outer join?
SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don?t care its matched or unmatched) from the both participating tables.
Syntax for full outer join:
SELECT *  
FROM table1  
FULL OUTER JOIN table2  
ON table1.column_name = table2.column_name;  
Note:here table1 and table2 are the name of the tables participating in joining and column_name is the column of the participating tables.
Let us take two tables to demonstrate full outer join:
table_A
A
M
1
M
2
N
4
O
table_B
A
N
2
P
3
Q
5
R

Resulting table

A
M
A
N
2
N
2
P
1
M
-
-
4
O
-
-
-
-
3
Q
-
-
5
R

Because this is a full outer join so all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.
Pictorial representation of full outer join:
SQL CROSS JOIN

When each row of first table is combined with each row from the second table, known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table.
We can specify a CROSS JOIN in two ways:
  1. Using the JOIN syntax.
  2. the table in the FROM clause without using a WHERE clause.
SYNTAX of SQL CROSS JOIN:

SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]  
OR  
SELECT * FROM [ TABLE1] , [TABLE2]  
Let us take an example of two tables,

Table1 - MatchScore

Player
Department_id
Goals
Franklin
1
2
Alan
1
3
Priyanka
2
2
Rajesh
3
5

Table2 - Departments

Department_id
Department_name
1
IT
2
HR
3
Marketing
SQL Statement:

SELECT * FROM MatchScore CROSS JOIN Departments  
After executing this query , you will find the following result:
Player
Department_id
Goals
Depatment_id
Department_name
Franklin
1
2
1
IT
Alan
1
3
1
IT
Priyanka
2
2
1
IT
Rajesh
3
5
1
IT
Franklin
1
2
2
HR
Alan
1
3
2
HR
Priyanka
2
2
2
HR
Rajesh
3
5
2
HR
Franklin
1
2
3
Marketing
Alan
1
3
3
Marketing
Priyanka
2
2
3
Marketing
Rajesh
3
5
3
Marketing

 

           VIEWS

A view is a virtual table.
This chapter shows how to create, update, and delete a view.

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

SQL Dropping a View

You can delete a view with the DROP VIEW command.
SQL DROP VIEW Syntax
DROP VIEW view_name

SYNONYM

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.
You can refer to synonyms in the following DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE.
Examples
CREATE SYNONYM: Examples To define the synonym offices for the table locations in the schema hr, issue the following statement:
CREATE SYNONYM offices 
   FOR hr.locations;


SEQUENCE

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.
When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.
Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.
Once a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.
MAXVALUE Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater thanSTART WITH and must be greater than MINVALUE.
NOMAXVALUE  Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
Example
Creating a Sequence: Example The following statement creates the sequence customers_seq in the sample schema oe. This sequence could be used to provide customer ID numbers when rows are added to the customers table.
CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
The first reference to customers_seq.nextval returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.

Partitioning 

Partitioning is powerful functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.


Range Partitioning
Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.
When using range partitioning, consider the following rules:
·         Each partition has a VALUES LESS THAN clause, which specifies a noninclusive upper bound for the partitions. Any values of the partition key equal to or higher than this literal are added to the next higher partition.
·         All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
·         A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.
A typical example is given in the following section. The statement creates a table (sales_range) that is range partitioned on the sales_date field.

Range Partitioning Example


CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))
);
List Partitioning
List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition and from hash partitioning, where a hash function controls the row-to-partition mapping. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
The details of list partitioning can best be described with an example. In this case, let's say you want to partition a sales table by region. That means grouping states together according to their geographical location as in the following example.

List Partitioning Example


CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);
 

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within the set of values that describes the partition. For example, the rows are inserted as follows:
·         (10, 'Jones', 'Hawaii', 100, '05-JAN-2000') maps to partition sales_west
·         (21, 'Smith', 'Florida', 150, '15-JAN-2000') maps to partition sales_east
·         (32, 'Lee', 'Colorado', 130, '21-JAN-2000') maps to partition sales_other
Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.
The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.

Hash Partitioning

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:
·         You do not know beforehand how much data maps into a given range
·         The sizes of range partitions would differ quite substantially or would be difficult to balance manually
·         Range partitioning would cause the data to be undesirably clustered
·         Performance features such as parallel DML, partition pruning, and partition-wise joins are important
The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.

Hash Partitioning Example

CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (ts1, ts2, ts3, ts4);
 

The preceding statement creates a table sales_hash, which is hash partitioned on salesman_id field. The tablespace names are ts1, ts2, ts3, andts4. With this syntax, we ensure that we create the partitions in a round-robin manner across the specified tablespaces.

INDEX

Use the CREATE INDEX statement to create an index on:
  • One or more columns of a table, a partitioned table, an index-organized table, or a cluster
  • One or more scalar typed object attributes of a table or a cluster
  • A nested table storage table for indexing a nested table column
An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. Oracle Database supports several types of index:
  • Normal indexes. (By default, Oracle Database creates B-tree indexes.)
  • Bitmap indexes, which store rowids associated with a key value as a bitmap
  • Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table
  • Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
  • Domain indexes, which are instances of an application-specific index of type indextype requisites
      To create an index in your own schema, one of the following conditions must be true:
·         The table or cluster to be indexed must be in your own schema.
·         You must have the INDEX object privilege on the table to be indexed.
·         You must have the CREATE ANY INDEX system privilege.

Creating an Index: Example The following statement shows how the sample index ord_customer_ix on the customer_id column of the sample table oe.orders was created:

CREATE INDEX ord_customer_ix
   ON orders (customer_id);

In the next statements showing index creation and subsequent query, Oracle Database will use index income_ix even though the columns are in reverse order in the query:

CREATE INDEX income_ix 
   ON employees(salary + (salary*commission_pct));
SELECT first_name||' '||last_name "Name"
FROM employees   WHERE (salary*commission_pct) + salary > 15000;

 

CLUSTER

Use the create cluster statement to create a cluster. A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key.
Creating a Cluster:
The following statement creates a cluster named personnel with the cluster key column department, a cluster size of 512 bytes, and storage parameter values:

 
CREATE CLUSTER personnel
   (department NUMBER(4))
SIZE 512 
STORAGE (initial 100K next 50K);

Cluster Keys:
The following statement creates the cluster index on the cluster key of personnel:

 
CREATE INDEX idx_personnel ON CLUSTER personnel;
 

After creating the cluster index, you can add tables to the index and perform DML operations on those tables.

Adding Tables to a Cluster:

 The following statements create some departmental tables from the sample hr.employees table and add them to the personnel cluster created in the earlier example:

 
CREATE TABLE dept_10
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 10;
 
CREATE TABLE dept_20
   CLUSTER personnel (department_id)
   AS SELECT * FROM employees WHERE department_id = 20;

Hash Clusters: Examples

 The following statement creates a hash cluster named language with the cluster key column cust_language, 
a maximum of 10 hash key values, each of which is allocated 512 bytes, and storage parameter values:

 
CREATE CLUSTER language (cust_language VARCHAR2(3))
   SIZE 512 HASHKEYS 10
   STORAGE (INITIAL 100k next 50k);
 

Because the preceding statement omits the HASH IS clause, Oracle Database uses the internal hash function for the cluster.
The following statement creates a hash cluster named address with the cluster key made up of the columns postal_code and country_id, and uses a SQL expression containing these columns for the hash function:

 
CREATE CLUSTER address
   (postal_code NUMBER, country_id CHAR(2))
   HASHKEYS 20
   HASH IS MOD(postal_code + country_id, 101);

 


EmoticonEmoticon

Subscribe to: Posts (Atom)