Introduction To SQL

Introduction
There are many RDBMS such as MySQL, Microsoft SQL Server, PostgreSQL, Oracle, etc.
that allow us to create a database consisting of relations and to link one or more relations for
efficient querying to store, retrieve and manipulate data on that database. In this chapter, we
will learn how to create, populate and query database using MySQL Software and SQL
language.

1. Structured Query Language (SQL)

One has to write application programs to access data in case of a file system. However, for
database management systems there are special kind of programming languages called query
language that can be used to access data from the database. The Structured Query Language
(SQL) is the most popular query language used by major relational database management
systems such as MySQL, ORACLE, SQL Server, etc. SQL is a database computer language
designed for managing data in relational database management systems (RDBMS).

SQL, is a standardized computer language that was originally developed by IBM for querying,
altering and defining relational databases, using declarative statements. SQL is easy to learn as
the statements comprise of descriptive English words and are not case sensitive. We can create
and interact with a database using SQL in an efficient and easy way. The benefit with SQL is
that we don’t have to specify how to get the data from the database. Rather, we simply specify
what is to be retrieved, and SQL does the rest. Although called a query language, SQL can do
much more besides querying. SQL provides statements for defining the structure of the data,
manipulating data in the database, declare constraints and retrieve data from the database in
various ways, depending on our requirements.
In this chapter, we will learn how to create a database using MySQL as the RDBMS software.

2. Data Types and Constraints in SQL (using MySQL software)

We know that a database consists of one or more relations and each relation (table) is made up
of attributes (column). Each attribute has a data type. We can also specify constraints for each
attribute of a relation.

2.1. Data type of Attribute

Data type indicates the type of data value that an attribute can have. The data type of an
attribute decides the operations that can be performed on the data of that attribute. For
 example, arithmetic operations can be performed on numeric data but not on character data.
Commonly used data types in MySQL are numeric types, date and time types, and string
(character and byte) types as shown in Table 1.

Table 1: commonly used data type.

Data type Description
Specifies character type data of length n where n could be any value from 0
CHAR(n) to 255. CHAR is of fixed length, means, declaring CHAR (10) implies to
reserve spaces for 10 characters. If data does not have 10 characters (for
example, ‘city’ has four characters), MySQL fills the remaining 6
characters with spaces padded on the right.

Specifies character type data of length ‘n’ where n could be any value from
VARCHAR(n) 0 to 65535. But unlike CHAR, VARCHAR is a variablelength data type.
That is, declaring VARCHAR (30) means a maximum of 30 characters can
be stored but the actual allocated bytes will depend on the length of entered
string. So ‘city’ in VARCHAR (30) will occupy the space needed to store 4
characters only.

INT specifies an integer value. Each INT value occupies 4 bytes of storage.
INT The range of values allowed in integer type are -2147483648 to
2147483647. For values larger than that, we have to use BIGINT, which
occupies 8 bytes.

Holds numbers with decimal points. Each FLOAT value occupies 4 bytes.
FLOAT
The DATE type is used for dates in 'YYYY-MM-DD' format. YYYY is the
DATE 4 digit year, MM is the 2 digit month and DD is the 2 digit date. The
supported range is '1000-01-01' to '9999-12-31'.

2.2. Constraints
Constraints are certain types of restrictions on the data values that an attribute can have. They
are used to ensure the accuracy and reliability of data. However, it is not mandatory to define
constraint for each attribute of a table. Table 2 lists various SQL constraints.
Constraint Description
NOT NULL Ensures that a column cannot have NULL values where NULL means
missing/ unknown/not applicable value.

Ensures that all the values in a column are distinct/unique.

UNIQUE
DEFAULT A default value specified for the column if no value is provided.

PRIMARY The column which can uniquely identify each row or record in a table.
KEY

FOREIGN The column which refers to value of an attribute defined as primary key in
KEY another table.

2.3. SQL SYNTAX ON CONSTRAINTS

2.3.1. NOT NULL Constraint:

Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five
columns, three of which, ID and NAME and AGE, specify not to accept NULLs:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
 ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

If CUSTOMERS table has already been created, then to add a NOT NULL constraint to
SALARY column in Oracle and MySQL, you would write a statement similar to the
following:

ALTER TABLE CUSTOMERS

MODIFY SALARY DECIMAL (18, 2) NOT NULL;

2.3.2. DEFAULT Constraint:

Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five
columns. Here, SALARY column is set to 5000.00 by default, so in case INSERT INTO
statement does not provide a value for this column, then by default this column would be set
to 5000.00.

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25) ,

SALARY DECIMAL (18, 2) DEFAULT 5000.00,
PRIMARY KEY (ID)
);

If CUSTOMERS table has already been created, then to add a DFAULT constraint to
SALARY column, you would write a statement similar to the following:

ALTER TABLE CUSTOMERS

MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00;

Drop Default Constraint:

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE CUSTOMERS

4
 ALTER COLUMN SALARY DROP DEFAULT;

2.3.3. UNIQUE Constraint:

Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five
columns. Here, AGE column is set to UNIQUE, so that you can not have two records with
same age:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE
column, you would write a statement similar to the following:

ALTER TABLE CUSTOMERS

MODIFY AGE INT NOT NULL UNIQUE;

You can also use the following syntax, which supports naming the constraint in multiple
columns as well:

ALTER TABLE CUSTOMERS

ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);

DROP a UNIQUE Constraint:

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE CUSTOMERS

DROP CONSTRAINT myUniqueConstraint;

If you are using MySQL, then you can use the following syntax:

ALTER TABLE CUSTOMERS

DROP INDEX myUniqueConstraint;
5
 2.3.4. PRIMARY Key:
A table can have only one primary key, which may consist of single or multiple fields. When
multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records having
the same value of that field(s).

Note: You would use these concepts while creating database tables.

Create Primary Key:

Here is the syntax to define ID attribute as a primary key in a CUSTOMERS table.

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

To create a PRIMARY KEY constraint on the "ID" column when CUSTOMERS table
already exists, use the following SQL syntax:

ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

NOTE: If you use the ALTER TABLE statement to add a primary key, the primary key
column(s) must already have been declared to not contain NULL values (when the table was
first created).
For defining a PRIMARY KEY constraint on multiple columns, use the following SQL
syntax:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID, NAME)

6
 );
To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when
CUSTOMERS table already exists, use the following SQL syntax:

ALTER TABLE CUSTOMERS

ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

Delete Primary Key:

You can clear the primary key constraints from the table, Use Syntax:

ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

2.3.5. FOREIGN Key:

A foreign key is a key used to link two tables together. This is sometimes called a referencing
key.

The relationship between 2 tables matches the Primary Key in one of the tables with a
Foreign Key in the second table.
If a table has a primary key defined on any field(s), then you cannot have two records having
the same value of that field(s).

Example:
Consider the structure of the two tables as follows:

CUSTOMERS table:
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

ORDERS table:
CREATE TABLE ORDERS (
ID INT NOT NULL,

7
 DATE DATETIME,
CUSTOMER_ID INT references CUSTOMERS(ID),
AMOUNT double,
PRIMARY KEY (ID)
);

If ORDERS table has already been created, and the foreign key has not yet been, use the
syntax for specifying a foreign key by altering a table.

ALTER TABLE ORDERS

ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

DROP a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE ORDERS

DROP FOREIGN KEY;

2.3.6. CHECK Constraint:

The CHECK Constraint enables a condition to check the value being entered into a record. If
the condition evaluates to false, the record violates the constraint and isn’t entered into the
table.

Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five
columns. Here, we add a CHECK with AGE column, so that you cannot have any
CUSTOMER below 18 years:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL CHECK (AGE >= 18),
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

8
If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE
column, you would write a statement similar to the following:

ALTER TABLE CUSTOMERS

MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );

You can also use following syntax, which supports naming the constraint and multiple
columns as well:

ALTER TABLE CUSTOMERS

ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18);

DROP a CHECK Constraint:

To drop a CHECK constraint, use the following SQL. This syntax does not work with
MySQL:

ALTER TABLE CUSTOMERS

DROP CONSTRAINT myCheckConstraint;

3. SQL for Data Definition

SQL provides commands for defining the relation schemas, modifying relation schemas and
deleting relations. These are called Data Definition Language (DDL) through which the set of
relations are specified, including their schema, data type for each attribute, the constraints as
well as the security and access related authorisations.

Data definition starts with the create statement. This statement is used to create a database and
its tables (relations). Before creating a database, we should be clear about the number of
tables in the database, the columns (attributes) in each table along with the data type of each
column. This is how we decide the relation schema.

3.1. Create Database

To create a database, we use the CREATE DATABASE statement as shown in the following
syntax:
CREATE DATABASE databasename;

To create a database called Studentdb, we will type following command. Create

database Studentdb;

3.2. Create Table

9
After creating database Studentdb, we need to define relations (create tables) in this database
and specify attributes for each relation along with data types for each attribute. This is done
using the CREATE TABLE statement. Syntax:
CREATE TABLE tablename(
attributename1 datatype constraint,
attributename2 datatype constraint,
:
AttributenameN datatype constraint);

It is important to observe the following points with respect to the Create Table statement:

- N is the degree of the relation, means there are N columns in the table.
- Attribute name specifies the name of the column in the table.
- Datatype specifies the type of data that an attribute can hold.
- Constraint indicates the restrictions imposed on the values of an attribute. By default,
each attribute can take NULL values except for the primary key.

Example :

CREATE TABLE departments(

deptID int not null primary key,
depName varchar(50) not null);

CREATE TABLE students(

matricule varchar(10) not null primary key,
Fname varchar(50) not null,
age int ,
deptID int foreign key references departments (detpID) );

3.3. ALTER TABLE

After creating a table we may realize that we need to add/remove an attribute or to modify the
datatype of an existing attribute or to add constraint in attribute. In all such cases, we need to
change or alter the structure of the table by using the alter statement.
Syntax:
ALTER TABLE tablename ADD/Modify/DROP attribute1, attribute2,..

Sometimes, we may need to add an additional attribute in a table. It can be done using the
syntax given below:

ALTER TABLE table_name ADD attribute_name DATATYPE;

Example : alter table students add gender char(1) ;

10
 3.4. Drop Statement

Sometimes a table in a database or the database itself needs to be removed. We can use DROP
statement to remove a database or a table permanently from the system. However, one should
be very cautious while using this statement as it cannot be undone.
Syntax to drop a table:
DROP TABLE table_name;
Syntax to drop a database:
DROP DATABASE database_name;

Cautions:
1) Using the Drop statement to remove a database will ultimately remove all the tables
within it.
2) DROP statement will remove the tables or database created by you. Hence you may
apply DROP statement at the end of the chapter.

4. SQL for Data Manipulation

In the previous section, we created the database Studentdb having two relations STUDENT,
and depatment. When we create a table, only its structure is created but the table has no data.
To populate records in the table, INSERT statement is used. Similarly, table records can be
deleted or updated using SQL data manipulation statements.

Data Manipulation using a database means either retrieval (access) of existing data, insertion
of new data, removal of existing data or modification of existing data in the database.

4.1. INSERTION of Records

INSERT INTO statement is used to insert new records in a table. Its syntax is:
INSERT INTO tablename
VALUES(value 1, value 2,....);
Here, value 1 corresponds to attribute 1, value 2 corresponds to attribute 2 and so on. Note
that we need not to specify attribute names in insert statement if there are exactly same
number of values in the INSERT statement as the total number of attributes in the table.

11
 Example : insert into department values (1, « History ») ;

Caution: While populating records in a table with foreign key, ensure that records in
referenced tables are already populated.
Example : insert into students values (« 12UYT », « peupie », « Guy »,12,1, « M » ) ;

If we want to provide values only for some of the attributes in a table (supposing other
attributes having NULL or any other default value), then we shall specify the attribute name
alongside each data value as shown in the following syntax of INSERT INTO statement.
Syntax:
INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);

Example : TO-DO

4.2. SQL for Data Query

So far we have learnt how to create database as well as to store and manipulate data. We are
interested to store data in a database as it is easier to retrieve data in future from databases in
whatever way we want. The Structured Query Language (SQL) has efficient mechanisms to
retrieve data stored in multiple tables in a MySQL database (or any other RDBMS). The user
enters the SQL commands called queries where the specific requirements for data to be
retrieved are provided. The SQL statement SELECT is used to retrieve data from the tables in
a database and is also called query statement.

a) SELECT Statement

The SQL statement SELECT is used to retrieve data from the tables in a database and the
output is also displayed in tabular form.
Syntax:
SELECT attribute1, attribute2, ...
FROM table_name
WHERE condition

Here, attribute1, attribute2, ... are the column names of the table table_name from which we
want to retrieve data. The FROM clause is always written with SELECT clause as it specifies
the name of the table from which data is to be retrieved. The WHERE clause is optional and is
used to retrieve data that meet specified condition(s).

Example : To display the name and gender of student with matricule number «454TR » , we
write the following query:

Select Fname, gender from students where matricule = «454TR » ;

To select all the students in any department we use the syntax:
12
Select* from students

b) ORDER BY statement

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in descending order, use the DESC keyword.

ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

4.3. Data Updation and Deletion

Updation and deletion of data are also the parts of SQL data manipulation. In this section, we
are going to apply these two data manipulation methods.

4.3.1. Data Updation

We may need to make changes in the value(s) of one or more columns of existing records in a
table. For example, we may require some changes in address, phone number or spelling of
name, etc. The UPDATE statement is used to make such modifications in the existing data.

The Syntax is as follow :

UPDATE table_name SET attribute1 = value1, attribute2 = value2, ...

WHERE condition;

4.3.2. Data Deletion

The DELETE statement is used to delete one or more record(s) from a table.
Syntax:
DELETE FROM table_name
WHERE condition;

13
Example : TO-DO.

Caution: Like UPDATE statement, we need to be careful to include WHERE clause while
using DELETE statement to delete records in a table. Otherwise, all the records in the table
will get deleted.

5. SQL Joins

The SQL Joins clause is used to combine records from two or more tables in a database. A
JOIN is a
means for combining fields from two tables by using values common to each.
Consider the following two tables,

(a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
|1|Ramesh|32|Ahmedabad|2000.00|
|2|Khilan|25|Delhi|1500.00|
|3| kaushik |23|Kota|2000.00|
|4|Chaitali|25|Mumbai|6500.00|
|5|Hardik|27|Bhopal|8500.00|
|6|Komal|22| MP |4500.00|
|7|Muffy|24|Indore|10000.00|
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+----------------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+------------------+-------------+-------------------+
|102|2009-10-08|3|3000|
|100|2009-10-08|3|1500|
|101|2009-11-20|2|1560|
|103|2008-05-20|4|2060|
+-----+---------------------+-------------+---------------+

Now, let us join these two tables in our SELECT statement as follows:

SQL> SELECT ID, NAME, AGE, AMOUNT

FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
14
 This would produce the following result:

+----+----------+-----+-----------------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+----------------+
|3| kaushik |23|3000|
|3| kaushik |23|1500|
|2|Khilan|25|1560|
|4|Chaitali|25|2060|
+----+----------+-----+----------------+

Here, it is noticeable that the join is performed in the WHERE clause. Several operators can
be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can
all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available in SQL:
• INNER JOIN: returns rows when there is a match in both tables.
• LEFT JOIN: returns all rows from the left table, even if there are no matches in the right
table.
• RIGHT JOIN: returns all rows from the right table, even if there are no matches in the
left table.
• FULL JOIN: returns rows when there is a match in one of the tables.
• SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily
renaming at least one table in the SQL statement.
• CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or
more joined tables.

5.1. INNER JOIN

The most frequently used and important of the joins is the INNER JOIN. They are also
referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables
(table1 and table2) based upon the join-predicate. The query compares each row of table1
with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the
join-predicate is satisfied, column values for each matched pair of rows of A and B are
combined into a result row.

Syntax:
The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

15
Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
|1|Ramesh|32|Ahmedabad|2000.00|
|2|Khilan|25|Delhi|1500.00|
|3| kaushik |23|Kota|2000.00|
|4|Chaitali|25|Mumbai|6500.00|
|5|Hardik|27|Bhopal|8500.00|
|6|Komal|22| MP |4500.00|
|7|Muffy|24|Indore|10000.00|
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+-------------+
| OID | DATE | ID | AMOUNT |
+-----+---------------------+-------------+--------------+
|102|2009-10-08|3|3000|
|100|2009-10-08|3|1500|
|101|2009-11-20|2|1560|
|103|2008-05-20|4|2060|
+-----+---------------------+-------------+--------+

Now, let us join these two tables using INNER JOIN as follows:

SQL> SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
|3| kaushik |3000|2009-10-08|
|3| kaushik |1500|2009-10-08|
|2|Khilan|1560|2009-11-20|
|4|Chaitali|2060|2008-05-20|
+----+----------+--------+---------------------+

16
 5.2. LEFT JOIN
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the
right table. This means that if the ON clause matches 0 (zero) records in right table, the join
will still return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from
the right table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1
LEFT JOIN table2
ON table1.common_filed = table2.common_field;

Here given condition could be any given expression based on your requirement.

Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
|1|Ramesh|32|Ahmedabad|2000.00|
|2|Khilan|25|Delhi|1500.00|
|3| kaushik |23|Kota|2000.00|
|4|Chaitali|25|Mumbai|6500.00|
|5|Hardik|27|Bhopal|8500.00|
|6|Komal|22| MP |4500.00|
|7|Muffy|24|Indore|10000.00|
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------+-------------+---------------------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------+-------------+---------------------+
|102|2009-10-08|3|3000|
|100|2009-10-08|3|1500|
|101|2009-11-20|2|1560|
|103|2008-05-20|4|2060|
+-----+---------------------+-------------+--------+

Now, let us join these two tables using LEFT JOIN as follows:

SQL> SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS
LEFT JOIN ORDERS
17
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+----+----------+--------+---------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+--------------+
|1|Ramesh| NULL| NULL |
|2|Khilan|1560|2009-11-20|
|3| kaushik |3000|2009-10-08|
|3| kaushik |1500|2009-10-08|
|4|Chaitali|2060|2008-05-20|
|5|Hardik| NULL | NULL |
|6|Komal| NULL | NULL |
|7|Muffy| NULL | NULL |
+----+----------+--------+--------------+

5.3. RIGHT JOIN

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in
the left table. This means that if the ON clause matches 0 (zero) records in left table, the join
will still return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values
from the left table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of RIGHT JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM table1
RIGHT JOIN table2
ON table1.common_filed = table2.common_field;

Example:
Consider the following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRE| SALARY |
+----+----------+-----+-----------+-------------+
|1|Ramesh|32|Ahmedabad|2000.00|
|2|Khilan|25|Delhi|1500.00|
|3| kaushik |23|Kota|2000.00|
|4|Chaitali|25|Mumbai|6500.00|
|5|Hardik|27|Bhopal|8500.00|
|6|Komal|22| MP |4500.00|
|7|Muffy|24|Indore|10000.00|
18
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+-------------+-------------+-----------------------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+--------------+--------------------+----------------+
|102|2009-10-08|3|3000|
|100|2009-10-08|3|1500|
|101|2009-11-20|2|1560|
|103|2008-05-20|4|2060|
+-----+---------------------+-------------+--------+

Now, let us join these two tables using RIGHT JOIN as follows:

SQL> SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+------+----------+--------+--------------+
| ID | NAME | AMOUNT | DAT |
+------+----------+--------+--------------+
|3| kaushik |3000|2009-10-08|
|3| kaushik |1500|2009-10-08|
|2|Khilan|1560|2009-11-20|
|4|Chaitali|2060|2008-05-20|
+------+----------+--------+-------------+

6. SQL Functions

6.1. The SQL MIN() and MAX() Functions

The MIN() function returns the smallest value of the selected column.

The MAX() function returns the largest value of the selected column.

19
MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;

6.2. The SQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows and as well all the rows that matches a
specified criterion.

COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
COUNT() Syntax to select all rows
SELECT COUNT(*)(column_name)
FROM table_name
WHERE condition;

The AVG() function returns the average value of a numeric column.

AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

The SUM() function returns the total sum of a numeric column.

SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

20