C
Java
JS
SQL
PHP
21.
SQL - ORDER BY Clause
SQL
The SQL
ORDER BY
clause is used to sort the data in ascending or descending order,
based on one or more columns. Some databases sort the query results in an ascending
order by default.
Syntax
The basic syntax of the ORDER BY clause is as follows:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
You can use more than one column in the ORDER BY clause. Make sure whatever column
you are using to sort that column should be in the column-list.
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
The following code block has an example, which would sort the result in an ascending
order by the NAME and the SALARY.
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;
63
SQL
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
The following code block has an example, which would sort the result in the descending
order by NAME.
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
+----+----------+-----+-----------+----------+
64
22.
SQL - Group By
SQL
The SQL
GROUP BY
clause is used in collaboration with the SELECT statement to arrange
identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT
statement and precedes the ORDER BY clause.
Syntax
The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP
BY clause must follow the conditions in the WHERE clause and must precede the ORDER
BY clause if one is used.
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
Example
Consider the CUSTOMERS table is having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
If you want to know the total amount of the salary on each customer, then the GROUP BY
query would be as follows.
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
65
SQL
This would produce the following result:
+----------+-------------+
| NAME | SUM(SALARY) |
+----------+-------------+
| Chaitali | 6500.00 |
| Hardik | 8500.00 |
| kaushik | 2000.00 |
| Khilan | 1500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 2000.00 |
+----------+-------------+
Now, let us look at a table where the CUSTOMERS table has the following records with
duplicate names:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Now again, if you want to know the total amount of salary on each customer, then the
GROUP BY query would be as follows:
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
66
SQL
This would produce the following result:
+---------+-------------+
| NAME | SUM(SALARY) |
+---------+-------------+
| Hardik | 8500.00 |
| kaushik | 8500.00 |
| Komal | 4500.00 |
| Muffy | 10000.00 |
| Ramesh | 3500.00 |
+---------+-------------+
67
23.
SQL - Distinct Keyword
SQL
The SQL
DISTINCT
keyword is used in conjunction with the SELECT statement to
eliminate all the duplicate records and fetching only unique records.
There may be a situation when you have multiple duplicate records in a table. While
fetching such records, it makes more sense to fetch only those unique records instead of
fetching duplicate records.
Syntax
The basic syntax of DISTINCT keyword to eliminate the duplicate records is as follows:
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
First, let us see how the following SELECT query returns the duplicate salary records.
SQL> SELECT SALARY FROM CUSTOMERS
ORDER BY SALARY;
68
SQL
This would produce the following result, where the salary (2000) is coming twice which is
a duplicate record from the original table.
+----------+
| SALARY |
+----------+
| 1500.00 |
| 2000.00 |
| 2000.00 |
| 4500.00 |
| 6500.00 |
| 8500.00 |
| 10000.00 |
+----------+
Now, let us use the DISTINCT keyword with the above SELECT query and then see the
result.
SQL> SELECT DISTINCT SALARY FROM CUSTOMERS
ORDER BY SALARY;
This would produce the following result where we do not have any duplicate entry.
+----------+
| SALARY |
+----------+
| 1500.00 |
| 2000.00 |
| 4500.00 |
| 6500.00 |
| 8500.00 |
| 10000.00 |
+----------+
69
24.
SQL - SORTING Results
SQL
The SQL
ORDER BY
clause is used to sort the data in ascending or descending order,
based on one or more columns. Some databases sort the query results in an ascending
order by default.
Syntax
The basic syntax of the ORDER BY clause which would be used to sort the result in an
ascending or descending order is as follows:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
You can use more than one column in the ORDER BY clause. Ma ke sure that whatever
column you are using to sort, that column should be in the column-list.
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Following is an example, which would sort the result in an ascending order by NAME and
SALARY.
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME, SALARY;
70
SQL
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
The following code block has an example, which would sort the result in a descending
order by NAME.
SQL> SELECT * FROM CUSTOMERS
ORDER BY NAME DESC;
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
+----+----------+-----+-----------+----------+
71
SQL
To fetch the rows with their own preferred order, the SELECT query used would be as
follows:
SQL> SELECT * FROM CUSTOMERS
ORDER BY (CASE ADDRESS
WHEN 'DELHI' THEN 1
WHEN 'BHOPAL' THEN 2
WHEN 'KOTA' THEN 3
WHEN 'AHMADABAD' THEN 4
WHEN 'MP' THEN 5
ELSE 100 END) ASC, ADDRESS DESC;
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
This will sort the customers by ADDRESS in your
ownoOrder
of preference first and in a
natural order for the remaining addresses. Also, the remaining Addresses will be sorted in
the reverse alphabetical order.
72
25.
SQL - Constraints
SQL
Constraints are the rules enforced on the data columns of a table. These are used to limit
the type of data that can go into a table. This ensures the accuracy and reliability of the
data in the database.
Constraints could be either on a column level or a table level. The column level constraints
are applied only to one column, whereas the t able level constraints are applied to the
whole table.
Following are some of the most commonly used constraints available in SQL. These
constraints have already been discussed in
SQL - RDBMS Concepts
chapter, but it’s worth
to revise them at this point.
NOT NULL Constraint
: Ensures that a column cannot have a NULL value.
DEFAULT Constraint
: Provides a default value for a column when none is specified.
UNIQUE Constraint
: Ensures that all values in a column are different.
PRIMARY Key
: Uniquely identifies each row/record in a database table.
FOREIGN Key
: Uniquely identifies row/record in any of the given database tables.
CHECK Constraint
: The CHECK constraint ensures that all the values in a column
satisfies certain conditions.
INDEX
: Used to create and retrieve data from the database very quickly.
Constraints can be specified when a table is created with the CREATE TABLE statement or
you can use the ALTER TABLE statement to create constraints even after the table is
created.
SQL - NOT NULL Constraint
By default, a column can hold NULL values. If you do not want a column to have a NULL
value, then you need to define such a constraint on this column specifying that NULL is
now not allowed for that column.
A NULL is not the same as no data, rather, it represents unknown data.
Example
For example, the following SQL query creates a new table called CUSTOMERS and adds
five columns, three of which are – ID, NAME and AGE. In this we specify not to accept
NULLs:
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
73
SQL
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 the
SALARY column in Oracle and MySQL, you would write a query like the one that is shown
in the following code block.
ALTER TABLE CUSTOMERS
MODIFY SALARY DECIMAL (18, 2) NOT NULL;
SQL - DEFAULT Constraint
The DEFAULT constraint provides a default va lue to a column when the INSERT INTO
statement does not provide a specific value.
Example
For example, the following SQL creates a new table called CUSTOMERS and adds fiv e
columns. Here, the SALARY column is set to 5000.00 by default, so in case the 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 the CUSTOMERS table has already been created, then to add a DEFAULT constraint to
the SALARY column, you would write a query like the one which is shown in the code block
below.
ALTER TABLE CUSTOMERS
MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00;
74
SQL
Drop Default Constraint
To drop a DEFAULT constraint, use the following SQL query.
ALTER TABLE CUSTOMERS
ALTER COLUMN SALARY DROP DEFAULT;
SQL - UNIQUE Constraint
The UNIQUE Constraint prevents two records from having identical values in a column. In
the CUSTOMERS table, for example, you might want to prevent two or more people from
having an identical age.
Example
For example, the following SQL query creates a new table called CUSTOMERS and adds
five columns. Here, the AGE column is set to UNIQUE, so that you cannot have two records
with the 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 the CUSTOMERS table has already been created, then to add a UNIQUE constraint to
the AGE column. You would write a statement like the query that is given in the code block
below.
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);
75
SQL
DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL query.
ALTER TABLE CUSTOMERS
DROP CONSTRAINT myUniqueConstraint;
If you are using MySQL, then you can use the following syntax:
ALTER TABLE CUSTOMERS
DROP INDEX myUniqueConstraint;
SQL - Primary Key
A primary key is a field in a table which uniquely identifies each row/record in a database
table. Primary keys must contain unique values. A primary key column cannot have NULL
values.
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
comp osite 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 the 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 the 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) should have already been declared to not contain NULL values (when the table
was first created).
76
SQL
For defining a PRIMARY KEY constraint on multiple columns, use the SQL syntax given
below.
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)
);
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 with the syntax given below.
ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;
SQL - Foreign Key
A foreign key is a key used to link two tables together. This is sometimes also called as a
referencing key.
A Foreign Key is a column or a combination of columns whose values ma tch a Primary Key
in a different table.
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).
77
SQL
Example
Consider the structure of the following two tables.
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,
DATE DATETIME,
CUSTOMER_ID INT references CUSTOMERS(ID),
AMOUNT double,
PRIMARY KEY (ID)
);
If the OR DERS table has already been created and the foreign key has not yet been set,
the 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 syntax.
ALTER TABLE ORDERS
DROP FOREIGN KEY;
78
SQL
SQL - CHECK Constraint
The CHECK Constraint enables a condition to check the value being entered into a record.
If the condition evaluates t o false, the record violates the constraint and isn't entered the
table.
Example
For example, the following program 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
who is 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)
);
If the CUSTOMERS table has already been created, then to add a CHECK constraint to AGE
column, you would write a statement like the one given below.
ALTER TABLE CUSTOMERS
MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );
You can also use the following syntax, which supports naming the constraint in 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 syntax. This syntax does not work with
MySQL.
ALTER TABLE CUSTOMERS
DROP CONSTRAINT myCheckConstraint;
79
SQL
SQL - INDEX Constraint
The INDEX is used to create and retrieve data from the database very quickly. An Index
can be created by using a single or a group of columns in a table. When the index is
created, it is assigned a
ROWID
for each row before it sorts out the data.
Proper indexes are good for performance in large databases, but you need to be careful
while creating an index. A selection of fields depends on what you are using in your SQL
queries.
Example
For example, the following SQL syntax creates a new table called CUSTOMERS and adds
five columns:
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)
);
Now, you can create an index on a single or multiple columns using the syntax given
below.
CREATE INDEX index_name
ON table_name ( column1, column2.....);
To create an INDEX on the AGE column, to optimize the search on customers for a specific
age, follow the SQL syntax which is given below.
CREATE INDEX idx_age
ON CUSTOMERS ( AGE );
DROP an INDEX Constraint
To drop an INDEX constraint, use the following SQL syntax.
ALTER TABLE CUSTOMERS
DROP INDEX idx_age;
80
SQL
Dropping Constraints
Any constraint that you have defined can be dropped using the ALTER TABLE command
with the DROP CONSTRAINT option.
For example, to drop the primary key constraint in the EMPLOYEES table, you can use the
following command.
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
Some implementations may provide shortcuts for dropping certain constraints. For
example, to drop the primary key constraint for a table in Oracle, you can use the following
command.
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
Some implementations allow you to disable constraints. Instead of permanently dropping
a constraint from the database, you may want to temporarily disable the constraint and
then enable it later.
Integrity Constraints
Integrity constraints are used to ensure accuracy and consistency of the data in a relational
database. Data integrity is handled in a relational database through the concept of
referential integrity.
There are many types of integrity constraints that play a role in
Referential Integrity
(RI)
. These constraints include Primary Key, Foreign Key, Unique Constraints and other
constraints which are mentioned above.
81
26.
SQL - Using Joins
SQL
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:
Table 1:
CUSTOMERS Table
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS Table
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as shown below.
SQL> SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
82
SQL
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 operat ors
can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT;
they can all be used t o join tables. However, the most common operator is the equal to
symbol.
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 mat ches 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.
Let us now discuss each of these joins in detail.
SQL - INNER JOIN
The most important and frequently used 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 ea ch matched pair of rows of A and B are
combined into a result row.
Syntax
83
SQL
The basic syntax of the
INNER JOIN
is as follows.
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
84
SQL
Now, let us join these two tables using the 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 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+
SQL - 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 the right
table; the join will still return a row in the result, but with NULL in each column from the
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 a
LEFT JOIN
is as follows.
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Here, the given condition could be any given expression based on your requirement.
85
SQL
Example
Consider the following two tables,
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
Orders Table is as follows.
+-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables using the LEFT JOIN as follows.
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result:
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
86
SQL
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+----+----------+--------+---------------------+
SQL - 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 the left
table; the join will still return a row in the result, but with NULL in each column from the
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 a
RIGHT JOIN
is as follow.
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
Example
Consider the following two tables,
Table 1:
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 |
87
SQL
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables using the R IGHT 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 | DATE |
+------+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
SQL - FULL JOIN
The SQL
FULL JOIN
combines the results of both left and right outer joins.
The joined table will contain all records from both the tables and fill in NULLs for missing
matches on either side.
88
SQL
Syntax
The basic syntax of a
FULL JOIN
is as follows:
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
Here, the given condition could be any given expression based on your requirement.
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
89
SQL
Now, let us join these two tables using FULL JOIN as follows.
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
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 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
If your Database does not support FULL JOIN (MySQL does not support FULL JOIN), then
you can use
UNION ALL
clause to combine these two JOINS as shown below.
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
90
SQL
SQL - SELF JOIN
The SQL
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.
Syntax
The basic syntax of
SELF JOIN
is as follows:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
Here, the WHERE clause could be any given expression based on your requirement.
Example
Consider the following table.
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 |
+----+----------+-----+-----------+----------+
Now, let us join this table using SELF JOIN as follows:
SQL> SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;
91
SQL
This would produce the following result:
+----+----------+---------+
| ID | NAME | SALARY |
+----+----------+---------+
| 2 | Ramesh | 1500.00 |
| 2 | kaushik | 1500.00 |
| 1 | Chaitali | 2000.00 |
| 2 | Chaitali | 1500.00 |
| 3 | Chaitali | 2000.00 |
| 6 | Chaitali | 4500.00 |
| 1 | Hardik | 2000.00 |
| 2 | Hardik | 1500.00 |
| 3 | Hardik | 2000.00 |
| 4 | Hardik | 6500.00 |
| 6 | Hardik | 4500.00 |
| 1 | Komal | 2000.00 |
| 2 | Komal | 1500.00 |
| 3 | Komal | 2000.00 |
| 1 | Muffy | 2000.00 |
| 2 | Muffy | 1500.00 |
| 3 | Muffy | 2000.00 |
| 4 | Muffy | 6500.00 |
| 5 | Muffy | 8500.00 |
| 6 | Muffy | 4500.00 |
+----+----------+---------+
SQL - CARTESIAN or CROSS JOIN
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records
from two or more joined tables. Thus, it equates to an inner join where the join-condition
always evaluates to either True or where the join-condition is absent from the statement.
Syntax
The basic syntax of the
CARTESIAN JOIN
or the
CROSS JOIN
is as follows:
SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ]
92
SQL
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS Table is as follows:
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables using INNER JOIN as follows:
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS, ORDERS;
This would produce the following result:
93
SQL
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 1 | Ramesh | 3000 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 1500 | 2009-10-08 00:00:00 |
| 1 | Ramesh | 1560 | 2009-11-20 00:00:00 |
| 1 | Ramesh | 2060 | 2008-05-20 00:00:00 |
| 2 | Khilan | 3000 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 2 | Khilan | 2060 | 2008-05-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 2060 | 2008-05-20 00:00:00 |
| 4 | Chaitali | 3000 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | 3000 | 2009-10-08 00:00:00 |
| 5 | Hardik | 1500 | 2009-10-08 00:00:00 |
| 5 | Hardik | 1560 | 2009-11-20 00:00:00 |
| 5 | Hardik | 2060 | 2008-05-20 00:00:00 |
| 6 | Komal | 3000 | 2009-10-08 00:00:00 |
| 6 | Komal | 1500 | 2009-10-08 00:00:00 |
| 6 | Komal | 1560 | 2009-11-20 00:00:00 |
| 6 | Komal | 2060 | 2008-05-20 00:00:00 |
| 7 | Muffy | 3000 | 2009-10-08 00:00:00 |
| 7 | Muffy | 1500 | 2009-10-08 00:00:00 |
| 7 | Muffy | 1560 | 2009-11-20 00:00:00 |
| 7 | Muffy | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+
94
27.
SQL - UNIONS CLAUSE
SQL
The SQL
UNION
clause/operator is used to combine the results of two or more SELECT
statements without returning any duplicate rows.
To use this UNION clause, each SELECT statement must have
The same number of columns selected
The same number of column expressions
The same data type and
Have them in the same order
But they need not have to be in the same length.
Syntax
The basic syntax of a
UNION
clause is as follows:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here, the given condition could be any given expression based on your requirement.
95
SQL
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as follows:
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
96
SQL
This would produce the following result:
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+------+----------+--------+---------------------+
The UNION ALL Clause
The UNION ALL operator is used to combine the results of two SELECT statements
including duplicate rows.
The same rules that apply to the UNION clause will apply to the UNION ALL operator.
Syntax
The basic syntax of the
UNION ALL
is as follows.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here, the given condition could be any given expression based on your requirement.
Example
97
SQL
Consider the following two tables,
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as follows:
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result:
98
SQL
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
There are two other clauses (i.e., operators), which are like the UNION clause.
SQL
INTERSECT Clause
: This is used to combine two SELECT statements, but
returns rows only from the first SELECT statement that are identical to a row in the
second SELECT statement.
SQL
EXCEPT Clause
: This combines two SELECT statements and returns rows from
the first SELECT statement that are not returned by the second SELECT statement.
SQL - INTERSECT Clause
The SQL
INTERSECT
clause/operator is used to combine two SELECT statements, but
returns rows only from the first SELECT statement that are identical to a row in the second
SELECT statement. This means INTERSECT returns only common rows returned by the
two SELECT statements.
Just as with the UNION operator, the same rules apply when using the INTERSECT
operator. MySQL does not support the INTERSECT operator.
Syntax
The basic syntax of
INTERSECT
is as follows.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
99
SQL
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
Here, the given condition could be any given expression based on your requirement.
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS
Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as follows.
100
SQL
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INTERSECT
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 | DATE |
+------+---------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Ramesh | 1560 | 2009-11-20 00:00:00 |
| 4 | kaushik | 2060 | 2008-05-20 00:00:00 |
+------+---------+--------+---------------------+
SQL - EXCEPT Clause
The SQL
EXCEPT
clause/operator is used to combine two SELECT statements and returns
rows from the first SELECT statement that are not returned by the second SELECT
statement. This means EXCEPT returns only rows, which are not available in the second
SELECT statement.
Just as with the UNION operator, the same rules apply when using the EXCEPT operator.
MySQL does not support the EXCEPT operator.
Syntax
The basic syntax of
EXCEPT
is as follows.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
EXCEPT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
101
SQL
[WHERE condition]
Here, the given condition could be any given expression based on your requirement.
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
Table 2:
ORDERS table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables in our SELECT statement as shown below.
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT
102
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 | DATE |
+----+---------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+----+---------+--------+---------------------+
103
28.
SQL - NULL Values
SQL
The SQL
NULL
is the term used to represent a missing value. A NULL value in a table is a
value in a field that appears to be blank.
A field with a NULL value is a field with no value. It is very important to understand that
a NULL value is different than a zero value or a field that contains spaces.
Syntax
The basic syntax of
NULL
while creating a table.
SQL> 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)
);
Here,
NOT NULL
signifies that column should always accept an explicit value of the given
data type. There are two columns where we did not use NOT NULL, which means these
columns could be NULL.
A field with a NULL value is the one that has been left blank during the record creation.
Example
The NULL value can cause problems when selecting data. However, because when
comparing an unknown value to any other value, the result is alwa ys unknown and not
included in the results. You must use the
IS NULL
or
IS NOT NULL
operators to check
for a NULL value.
Consider the following CUSTOMERS table having the records as shown below.
+----+----------+-----+-----------+----------+
| 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 |
104
SQL
| 6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore | |
+----+----------+-----+-----------+----------+
Now, following is the usage of the
IS NOT NULL
operator.
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
This would produce the following result:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Now, following is the usage of the
IS NULL
operator.
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;
This would produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore | |
+----+----------+-----+-----------+----------+
105
29.
SQL - Alias Syntax
SQL
You can rename a table or a column temporarily by giving another name known as
Alias
.
The use of table aliases is to rename a table in a specific SQL statement. The renaming is
a temporary change and the actual table name does not change in the database. The
column aliases are used to rename a table's columns for the purpose of a particular SQL
query.
Syntax
The basic syntax of a
table
alias is as follows.
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];
The basic syntax of a
column
alias is as follows.
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];
Example
Consider the following two tables.
Table 1:
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 |
+----+----------+-----+-----------+----------+
106
SQL
Table 2:
ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
Now, the following code block shows the usage of a
table alias
.
SQL> SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE C.ID = O.CUSTOMER_ID;
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 |
+----+----------+-----+--------+
Following is the usage of a
column alias
.
SQL> SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;
107
SQL
This would produce the following result.
+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
| 1 | Ramesh |
| 2 | Khilan |
| 3 | kaushik |
| 4 | Chaitali |
| 5 | Hardik |
| 6 | Komal |
| 7 | Muffy |
+-------------+---------------+
108
30.
SQL – Indexes
SQL
Indexes are
special lookup tables
that the database search engine can use to speed up
data retrieval. Simply put, an index is a pointer to data in a table. An index in a database
is very similar to an index in the back of a book.
For example, if you want to reference all pages in a book that discusses a certain topic,
you first refer to the index, which lists all the topics alphabetically and are then referred
to one or more specific page numbers.
An index helps to speed up
SELECT
queries and
WHERE
clauses, but it slows down data
input, with the
UPDATE
and the
INSERT
statements. Indexes can be created or dropped
with no effect on the data.
Creating an index involves the
CREATE INDEX
statement, which allows you to name the
index, to specify the table and which column or columns to index, and to indicate whether
the index is in an ascending or descending order.
Indexes can also be unique, like the
UNIQUE
constraint, in that the index prevents
duplicate entries in the column or combination of columns on which there is an index.
The CREATE INDEX Command
The basic syntax of a
CREATE INDEX
is as follows.
CREATE INDEX index_name ON table_name;
Single-Column Indexes
A single-column index is created based on only one table column. The basic syntax is as
follows.
CREATE INDEX index_name
ON table_name (column_name);
Unique Indexes
Unique indexes are used not only for performa nce, but also for data integrity. A unique
index does not allow any duplicate values to be inserted into the table. The ba sic syntax
is as follows.
CREATE UNIQUE INDEX index_name
on table_name (column_name);
109
SQL
Composite Indexes
A composite index is an index on two or more columns of a table. Its basic syntax is as
follows.
CREATE INDEX index_name
on table_name (column1, column2);
Whether to create a single-column index or a composite index, take into consideration the
column(s) that you may use very frequently in a query's WHERE clause as filter conditions.
Should there be only one column used, a single-column index should be the choice. Should
there be two or more columns that are frequently used in the WHERE clause as filters, the
composite index would be the best choice.
Implicit Indexes
Implicit indexes are indexes that are automatically created by the database server when
an object is created. Indexes are automatically created for primary key constraints and
unique constraints.
The DROP INDEX Command
An index can be dropped using SQL
DROP
command. Care should be taken when dropping
an index because the performance may either slow down or improve.
The basic syntax is as follows:
DROP INDEX index_name;
You can check the
INDEX Constraint
chapter to see some actual examples on Indexes.
When should indexes be avoided?
Although indexes a re intended to enhance a database's performance, there are times when
they should be avoided.
The following guidelines indicate when the use of an index should be reconsidered.
Indexes should not be used on small tables.
Tables that have frequent, large batch update s or insert operations.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.
SQL - INDEX Constraint
The INDEX is used to create and retrieve data from the database very quickly. Index can
be created by using a single or group of columns in a table. When the index is created, it
is assigned a ROWID for each row before it sorts out the data.
110
SQL
Proper indexes are good for performance in large databases, but you need to be careful
while creating an index. Selection of fields depends on what you are using in your SQL
queries.
Example
For example, the following SQL creates a new table called CUSTOMERS and adds five
columns in it.
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)
);
Now, you can create an index on a single or multiple columns using the syntax given
below.
CREATE INDEX index_name
ON table_name ( column1, column2.....);
To create an INDEX on the AGE column, to optimize the search on customers for a specific
age, you can use the following SQL syntax:
CREATE INDEX idx_age
ON CUSTOMERS ( AGE );
DROP an INDEX Constraint
To drop an INDEX constraint, use the following SQL syntax.
ALTER TABLE CUSTOMERS
DROP INDEX idx_age;
111
31.
SQL - ALTER TABLE Command
SQL
The SQL
ALTER TABLE
command is used to add, delete or modify columns in an existing
table. You should also use the ALTER TABLE command to add and drop various constraints
on an existing table.
Syntax
The basic syntax of an ALTER TABLE command to add a
New Column
in an existing table
is as follows.
ALTER TABLE table_name ADD column_name datatype;
The basic syntax of an ALTER TABLE command to
DROP COLUMN
in an existing table is
as follows.
ALTER TABLE table_name DROP COLUMN column_name;
The basic syntax of an ALTER TABLE command to change the
DATA TYPE
of a column in
a table is as follows.
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
The basic syntax of an ALTER TABLE command to add a
NOT NULL
constraint to a column
in a table is as follows.
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
The basic syntax of an ALTER TABLE command to
ADD UNIQUE CONSTRAINT
to a table
is as follows.
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
The basic syntax of an ALTER TABLE command to
ADD CHECK CONSTRAINT
to a table
is as follows.
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
112
SQL
The ba sic syntax of an ALTER TABLE command to
ADD PRIMARY KEY
constraint to a
table is as follows.
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
The basic syntax of an ALTER TABLE command to
DROP CONSTRAINT
from a table is as
follows.
ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;
If you're using MySQL, the code is as follows:
ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;
The basic syntax of an ALTER TABLE command to
DROP PRIMARY KEY
constraint from
a table is as follows.
ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;
If you're using MySQL, the code is as follows:
ALTER TABLE table_name
DROP PRIMARY KEY;
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
113
SQL
+----+----------+-----+-----------+----------+
Following is the example to ADD a
New Column
to an existing table:
ALTER TABLE CUSTOMERS ADD SEX char(1);
Now, the CUSTOMERS table is changed and following would be output from the SELECT
statement.
+----+---------+-----+-----------+----------+------+
| ID | NAME | AGE | ADDRESS | SALARY | SEX |
+----+---------+-----+-----------+----------+------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | NULL |
| 2 | Ramesh | 25 | Delhi | 1500.00 | NULL |
| 3 | kaushik | 23 | Kota | 2000.00 | NULL |
| 4 | kaushik | 25 | Mumbai | 6500.00 | NULL |
| 5 | Hardik | 27 | Bhopal | 8500.00 | NULL |
| 6 | Komal | 22 | MP | 4500.00 | NULL |
| 7 | Muffy | 24 | Indore | 10000.00 | NULL |
+----+---------+-----+-----------+----------+------+
Following is the example to DROP sex column from the existing table.
ALTER TABLE CUSTOMERS DROP SEX;
Now, the CUSTOMERS table is changed and following would be the output from the SELECT
statement.
+----+---------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+---------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Ramesh | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | kaushik | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+---------+-----+-----------+----------+
114
SQL
115
32.
SQL - TRUNCATE TABLE Command
SQL
The SQL
TRUNCATE TABLE
command is used to delete complete data from an existing
table.
You can also use DROP TABLE command to delete complete table but it would remove
complete table structure form the database and you would need to re-create this table
once again if you wish you store some data.
Syntax
The basic syntax of a
TRUNCATE TABLE
command is as follows.
TRUNCATE TABLE table_name;
Example
Consider a CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Following is the example of a Truncat e command.
SQL > TRUNCATE TABLE CUSTOMERS;
Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as
shown in the code block below:
SQL> SELECT * FROM CUSTOMERS;
Empty set (0.00 sec)
116
33.
SQL - Using Views
SQL
A view is nothing more than a SQL statement that is stored in the database with an
associated name. A view is actually a composition of a table in the form of a prede fined
SQL query.
A view can contain all rows of a table or select rows from a table. A view can be created
from one or many tables which depends on the written SQL query to create a view.
Views, which are a type of virtual tables allow users to do the following:
Structure data in a way that users or classes of users find natural or intuitive.
Restrict access to the data in such a way that a user can see and (sometimes)
modify exa ctly what they need and no more.
Summarize data from various tables which can be used to generate reports.
Creating Views
Database views are created using the
CREATE VIEW
statement. Views can be created
from a single table, multiple tables or another view.
To create a view, a user must have the appropriate system privilege according to the
specific implementation.
The basic
CREATE VIEW
syntax is as follows:
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
You can include multiple tables in your SELECT statement in a similar way as you use them
in a normal SQL SELECT query.
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
117
SQL
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example to create a view from the CUSTOMERS table. This view would be
used to have customer name and age from the CUSTOMERS table.
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age
FROM CUSTOMERS;
Now, you can query CUSTOMERS_VIEW in a similar way as you query an actual table.
Following is an example for the same.
SQL > SELECT * FROM CUSTOMERS_VIEW;
This would produce the following result.
+----------+-----+
| name | age |
+----------+-----+
| Ramesh | 32 |
| Khilan | 25 |
| kaushik | 23 |
| Chaitali | 25 |
| Hardik | 27 |
| Komal | 22 |
| Muffy | 24 |
+----------+-----+
The WITH CHECK OPTION
The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH
CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the
view definition.
If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
The following code block has an example of creating same view CUSTOMERS_VIEW with
the WITH CHECK OPTION.
CREATE VIEW CUSTOMERS_VIEW AS
118
SQL
SELECT name, age
FROM CUSTOMERS
WHERE age IS NOT NULL
WITH CHECK OPTION;
The WITH CHECK OPTION in this case should deny the entry of any NULL values in the
view's AGE column, because the view is defined by data that does not have a NULL value
in the AGE column.
Updating a View
A view can be updated under certain conditions which are given below –
The SELECT clause may not contain the keyword DISTINCT.
The SELECT clause may not contain summary functions.
The SELECT clause may not contain set functions.
The SELECT clause may not contain set operators.
The SELECT clause may not contain an ORDER BY clause.
The FROM clause may not contain multiple tables.
The WHERE clause may not contain subqueries.
The query may not contain GROUP BY or HAVING.
Calculated columns may not be updated.
All NOT NULL columns from the base table must be included in the view in order
for the INSERT query to function.
So, if a view satisfies all the above-mentioned rules then you can update that view. The
following code block has an example to update the age of Ramesh.
SQL > UPDATE CUSTOMERS_VIEW
SET AGE = 35
WHERE name='Ramesh';
This would ultimately update the base table CUSTOMERS and the sa me would reflect in
the view itself. Now, try to query the base table and the SELECT statement would produce
the following result.
119
SQL
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | 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 |
+----+----------+-----+-----------+----------+
Inserting Rows into a View
Rows of data can be insert ed into a view. The same rules that apply to the UPDATE
command also apply to the INSERT command.
Here, we cannot insert rows in the CUSTOMERS_VIEW because we have not included all
the NOT NULL columns in this view, otherwise you can insert rows in a view in a similar
way as you insert them in a table.
Deleting Rows into a View
Rows of data can be deleted from a view. The same rules that apply to the UPDATE and
INSERT commands apply to the DELETE command.
Following is an example to delete a record having AGE = 22.
SQL > DELETE FROM CUSTOMERS_VIEW
WHERE age = 22;
This would ultimately delete a row from the base table CUSTOMERS and the same would
reflect in the view itself. Now, try to query the base table and the SELECT statement would
produce the following result.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 35 | 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 |
120
SQL
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Dropping Views
Obviously, where you have a view, you need a way to drop the view if it is no longer
needed. The syntax is very simple and is given below:
DROP VIEW view_name;
Following is an example to drop the CUSTOMERS_VIEW from the CUSTOMERS table.
DROP VIEW CUSTOMERS_VIEW;
121
34.
SQL - Having Clause
SQL
The
HAVING
Clause
enables you to specify conditions that filter which group results
appear in the results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause
places conditions on groups created by the GROUP BY clause.
Syntax
The following code block shows the position of the HAVING Clause in a query .
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
The HAVING clause must follow the GROUP BY clause in a query and must also precede
the ORDER BY clause if used. The following code block has the syntax of the SELECT
statement including the HAVING clause:
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
Example
Consider the CUSTOMERS table having the following records.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
122
SQL
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an example, which would display a record for a similar age count that would
be more than or equal to 2.
SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;
This would produce the following result:
+----+--------+-----+---------+---------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+--------+-----+---------+---------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
+----+--------+-----+---------+---------+
123
35.
SQL – Transactions
SQL
A transaction is a unit of work that is performed against a database. Tra nsactions are units
or sequences of work accomplished in a logical order, whether in a manual fashion by a
user or automatically by some sort of a database program.
A transaction is the propagation of one or more changes to the database. For example, if
you are creating a record or updating a record or deleting a record from the table, then
you are performing a transaction on that table. It is important to control these transactions
to ensure the data integrity and to handle database errors.
Practically, you will club many SQL queries into a group and you will execute all of them
together as a part of a transaction.
Properties of Transactions
Transactions have the following four standard properties, usually referred to by the
acronym
ACID
.
Atomicity:
ensures that all operations within the work unit are completed
successfully. Otherwise, the transaction is abort ed at the point of failure and all the
previous operations a re rolled back to their former state.
Consistency:
ensures that the database properly changes states upon a
successfully committed transaction.
Isolation:
enables transactions to operate independently of and transparent to
each other.
Durability:
ensures that the result or effect of a committed transaction persists in
case of a system failure.
Transaction Control
The following commands are used to control transactions.
COMMIT:
to save the changes.
ROLLBACK:
to roll back the changes.
SAVEPOINT:
creates points within the groups of transactions in which to
ROLLBACK.
SET TRANSACTION:
Places a name on a transaction.
Transactional Control Commands
Transactional control commands are only used with the
DML Commands
such as –
INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping
them because these operations are automatically committed in the database.
124
SQL
The COMMIT Command
The COMMIT command is the transactional command used to save changes invoked by a
transaction to the database. The COMMIT command saves all the transactions to the
database since the last COMMIT or ROLLBACK command.
The syntax for the COMMIT command is as follows.
COMMIT;
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Following is an example which would delete those records from the table which have age
= 25 and then COMMIT the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
Thus, two rows from the table would be deleted and the SELECT statement would produce
the following result.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
125
SQL
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The ROLLBACK Command
The ROLLBACK command is the transactional command used to undo transactions that
have not already been saved to the database. This command can only be used to undo
transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for a ROLLBACK command is as follows:
ROLLBACK;
Example
Consider the CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
Following is an example, which would delete those records from the table which have the
age = 25 and then ROLLBACK the changes in the database.
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
126
SQL
Thus, the delete operation would not impact the table and the SELECT statement would
produce the following result.
+----+----------+-----+-----------+----------+
| 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 |
+----+----------+-----+-----------+----------+
The SAVEPOINT Command
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain
point without rolling back the entire transaction.
The syntax for a SAVEPOINT command is as shown below.
SAVEPOINT SAVEPOINT_NAME;
This command serves only in the creation of a SAVEPOINT among all the transactional
statements. The ROLLBACK command is used to undo a group of transactions.
The syntax for rolling back t o a SAVEPOINT is as shown below.
ROLLBACK TO SAVEPOINT_NAME;
Following is an example where you plan to delete the three different records from the
CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can
ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original
state.
Example
Consider the CUSTOMERS table having the following records.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
127
SQL
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
The following code block contains the series of operations.
SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.
Now that the three deletions have taken place, let us assume that you have changed your
mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because
SP2 was created after the first deletion, the last two deletions are undone:
SQL> ROLLBACK TO SP2;
Rollback complete.
Notice that only the first deletion took place since you rolled back to SP2.
SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
128
SQL
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.
The RELEASE SAVEPOINT Command
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have
created.
The syntax for a RELEASE SAVEPOINT command is as follows.
RELEASE SAVEPOINT SAVEPOINT_NAME;
Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to
undo transactions performed since the last SAVEPOINT.
The SET TRANSACTION Command
The SET TRANSACTION command can be used to initiate a database transaction. This
command is used to specify cha racteristics for the transaction that follows. For example,
you can specify a transaction to be read only or read write.
The syntax for a SET TRANSACTION command is as follows.
SET TRANSACTION [ READ WRITE | READ ONLY ];
129
36.
SQL - Wildcard Operators
SQL
We have already discussed about the SQL
LIKE
operator, which is used to compare a
value to similar values using the wildcard operators.
SQL supports two wildcard operators in conjunction with the LIKE operator which are
explained in detail in the following table .
Wildcard Operators Descr iption
Matches one or more characters.
The percent sign (%)
Note:
MS Access uses the asterisk (*) wildcard character
instead of the percent sign (%) wildcard character.
Matches one cha racter.
The underscore (_)
Note:
MS Access uses a question mark (?) instead of the
underscore (_) to match any one character.
The percent sign represents zero, one or multiple characters. The underscore represents
a single number or a character. These symbols can be used in combinations.
Syntax
The basic syntax of a
'%'
and a
'_'
operator is as follows.
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
130
SQL
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'
You can combine N number of conditions using the AND or the OR operators. Here, XXXX
could be any numeric or string value.
Example
The following table has a number of examples showing the WHERE part having different
LIKE clauses with '%' and '_' operators.
Statement Description
WHERE SALARY LIKE '200%' Finds any values that start with 200.
WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position.
WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third
positions.
WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3
characters in length.
WHERE SALARY LIKE '%2' Finds any values that end with 2.
WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position
and end with a 3.
WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with
2 and end with 3.
131
H.K.I.T
