LAB PART 2

Constraint
SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the
accuracy and integrity of the data inside table.
Constraints can be divided into the following two types,

1. Column level constraints: Limits only column data.

2. Table level constraints: Limits whole table data.

Constraints are used to make sure that the integrity of data is maintained in the database.
Following are the most used constraints that can be applied to a table.

 NOT NULL
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
 DEFAULT

NOT NULL Constraint

NOT NULL constraint restricts a column from having a NULL value. Once NOT
NULL constraint is applied to a column, you cannot pass a null value to that
column. It enforces a column to contain a proper value.
One important point to note about this constraint is that it cannot be defined at
table level.

Example using NOT NULL constraint

CREATE TABLE Student(s_id int NOT NULL, Name varchar(60),
Age int);
The above query will declare that the s_id field of Student table will not take
NULL value.

UNIQUE Constraint

1|Page Advance database lab pre by jilo D

UNIQUE constraint ensures that a field or column will only have unique values.
A UNIQUE constraint field will not have duplicate data. This constraint can be applied at
column level or table level.

Using UNIQUE constraint when creating a Table (Table

Level)
Here we have a simple CREATE query to create a table, which will have a
column s_id with unique values.

CREATE TABLE Student(s_id int NOT NULL UNIQUE, Name varchar(60),

Age int);

The above query will declare that the s_id field of Student table will only have unique
values and wont take NULL value.

Using UNIQUE constraint after Table is created (Column

Level)
ALTER TABLE Student ADD UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

Primary Key Constraint

Primary key constraint uniquely identifies each record in a database. A Primary Key
must contain unique value and it must not contain null value. Usually Primary Key is
used to index the data inside the table.

Using PRIMARY KEY constraint at Table Level

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT
NULL, Age int);

The above command will creates a PRIMARY KEY on the s_id.

Using PRIMARY KEY constraint at Column Level

ALTER table Student ADD PRIMARY KEY (s_id);

The above command will creates a PRIMARY KEY on the s_id.

Foreign Key Constraint

2|Page Advance database lab pre by jilo D

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to
restrict actions that would destroy links between tables. To understand FOREIGN KEY,
let's see its use, with help of the below tables:
Customer_Detail Table

c_id Customer_Name address

101 Adam Noida

102 Alex Delhi

103 Stuart Rohtak

Order_Detail Table

Order_id Order_Name c_id

10 Order1 101

11 Order2 103

12 Order3 102

In Customer_Detail table, c_id is the primary key which is set as foreign key
in Order_Detail table. The value that is entered in c_id which is set as foreign key
in Order_Detail table must be present in Customer_Detail table where it is set as
primary key. This prevents invalid data to be inserted into c_id column
of Order_Detail table.
If you try to insert any incorrect data, DBMS will return error and will not allow you to
insert the data.

3|Page Advance database lab pre by jilo D

Using FOREIGN KEY constraint at Table Level
CREATE table Order_Detail(

order_id int PRIMARY KEY,

order_name varchar(60) NOT NULL,

c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)

);

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference
of c_id column in Customer_Detail table.

Using FOREIGN KEY constraint at Column Level

ALTER table Order_Detail ADD FOREIGN KEY (c_id) REFERENCES
Customer_Detail(c_id);

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular
record is deleted in the main table. When two tables are connected with Foriegn key,
and certain data in the main table is deleted, for which a record exits in the child table,
then we must have some mechanism to save the integrity of data in the child table.

1. On Delete Cascade : This will remove the record from child table, if that value of
foriegn key is deleted from the main table.
2. On Delete Null : This will set all the values in that record of child table as NULL,
for which the value of foriegn key is deleted from the main table.
3. If we don't use any of the above, then we cannot delete data from the main table
for which data in child table exists. We will get an error if we try to do so.

ERROR : Record in child table exist

CHECK Constraint

4|Page Advance database lab pre by jilo D

CHECK constraint is used to restrict the value of a column between a range. It performs
check on the values, before storing them into the database. Its like condition checking
before saving data into a column.

Using CHECK constraint at Table Level

CREATE table Student(

s_id int NOT NULL CHECK(s_id > 0),

Name varchar(60) NOT NULL,

Age int

);

The above query will restrict the s_id value to be greater than zero.

Using CHECK constraint at Column Level

ALTER table Student ADD CHECK(s_id > 0);

SQL - Using 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 –
Table 1 − CUSTOMERS Table
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | bikila | 32 | naqamte | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | ambo | 2000.00 |
| 4 | caltuu | 25 | gincii | 6500.00 |
| 5 | bontuu | 27 | finfine | 8500.00 |
| 6 | akmal | 22 | shashee | 4500.00 |
| 7 | guyo | 24 | moyale | 10000.00 |
+----+----------+-----+-----------+----------+
+----+----------+-----+-----------+----------+

5|Page Advance database lab pre by jilo D

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;
This would produce the following result.
+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | hacaalu | 23 | 300 |
| 3 | hacaalu | 23 | 300 |
| 2 | caala | 25 | 600 |
| 4 | Caaltu | 25 | 600 |
+----+----------+-----+--------+
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 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 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.
 CARTESIAN JOIN − returns the Cartesian product of the sets of records from the
two or more joined tables.

SQL - UNIONS CLAUSE

The SQL UNION clause/operator is used to combine the results of two or more SELECT
statements without returning any duplicate rows.

6|Page Advance database lab pre by jilo D

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.

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 |

7|Page Advance database lab pre by jilo D

| 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;
This would produce the following result −
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | bikila | NULL | NULL |
| 2 | caala | 1560 | 2009-11-20 00:00:00 |
| 3 | hacaalu | 3000 | 2009-10-08 00:00:00 |
| 3 | hacaalu | 1500 | 2009-10-08 00:00:00 |
| 4 | Caltuu | 2060 | 2008-05-20 00:00:00 |
| 5 | bontuu | NULL | NULL |
| 6 | akmal | NULL | NULL |
| 7 | guyo | 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.

8|Page Advance database lab pre by jilo D

Example
Consider the following two tables,
Table 1 − CUSTOMERS Table is as follows.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | bikila | 32 | Ahmedabad | 2000.00 |
| 2 | caala | 25 | Delhi | 1500.00 |
| 3 | hacaalu | 23 | Kota | 2000.00 |
| 4 | caltuu | 25 | Mumbai | 6500.00 |
| 5 | bontuu | 27 | Bhopal | 8500.00 |
| 6 | akmal | 22 | MP | 4500.00 |
| 7 | guyo | 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 −
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | bikila | NULL | NULL |
| 2 | caala | 1560 | 2009-11-20 00:00:00 |
| 3 | hacaalu | 3000 | 2009-10-08 00:00:00 |
| 3 | hacaalu | 1500 | 2009-10-08 00:00:00 |
| 4 | Caltuu | 2060 | 2008-05-20 00:00:00 |
| 5 | bontuu | NULL | NULL |

9|Page Advance database lab pre by jilo D

| 6 | akmal | NULL | NULL |
| 7 | guyo | NULL | NULL |
| 3 | hacaalu | 3000 | 2009-10-08 00:00:00 |
| 3 | hacaalu | 1500 | 2009-10-08 00:00:00 |
| 2 | caala | 1560 | 2009-11-20 00:00:00 |
| 4 | Caltuu | 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 - NULL Values

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 always 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.
10 | P a g e Advance database lab pre by jilo D
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | bikila | 32 | naqamtee | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | amboo | 2000.00 |
| 4 | Caltuu | 25 | gincii | 6500.00 |
| 5 | bontuu | 27 | holoota | 8500.00 |
| 6 | akmal | 22 | finfine | |
| 7 | guyo | 24 | moyale | |
+----+----------+-----+-----------+----------+
Now, following is the usage of the IS NOT NULLoperator.
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 | bikila | 32 | naqamtee | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | ambo | 2000.00 |
| 4 | Caltuu | 25 | gincii | 6500.00 |
| 5 | bontuu | 27 | holoota | 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 | akmal | 22 | finfine | |
| 7 | guyo | 24 | moyale | |
SQL - Alias Syntax
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.

11 | P a g e Advance database lab pre by jilo D

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 | bikila | 32 | naqamtee | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | amboo | 2000.00 |
| 4 | Caltuu | 25 | gudar | 6500.00 |
| 5 | bontuu | 27 | gincii | 8500.00 |
| 6 | akmal | 22 | finfine | 4500.00 |
| 7 | guyo | 24 | moyale | 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, 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;

12 | P a g e Advance database lab pre by jilo D

This would produce the following result.
+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | hacaalu | 23 | 3000 |
| 3 | hacaalu | 23 | 1500 |
| 2 | caala | 25 | 1560 |
| 4 | Caltuu | 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;
This would produce the following result.
+-------------+---------------+
| CUSTOMER_ID | CUSTOMER_NAME |
+-------------+---------------+
| 1 | bikila |
| 2 | caala |
| 3 | hacaalu |
| 4 | Caltuu |
| 5 | bontuu |
| 6 | akmal |
| 7 | guyo |
+-------------+---------------+
SQL - Indexes
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 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;

13 | P a g e Advance database lab pre by jilo D

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 performance, but also for data integrity. A unique
index does not allow any duplicate values to be inserted into the table. The basic syntax
is as follows.
CREATE UNIQUE INDEX index_name
on table_name (column_name);
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;
SQL - ALTER TABLE Command
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.

14 | P a g e Advance database lab pre by jilo D

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 ALTER TABLE 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);
The basic 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;

15 | P a g e Advance database lab pre by jilo D

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 | bikilq | 32 | naqamte | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | ambo | 2000.00 |
| 4 | Caltuu | 25 | gincii | 6500.00 |
| 5 | bontu | 27 | holota | 8500.00 |
| 6 | akmal | 22 | finfine | 4500.00 |
| 7 | guyo | 24 | moyale | 10000.00 |
+----+----------+-----+-----------+----------+
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 | bikila | 32 | naqmte | 2000.00 | NULL |
| 2 | Ramesh | 25 | gudar | 1500.00 | NULL |
| 3 | kaushik | 23 | ambo | 2000.00 | NULL |
| 4 | kaushik | 25 | gincii | 6500.00 | NULL |
| 5 | Hardik | 27 | holota | 8500.00 | NULL |
| 6 | Komal | 22 | finfine | 4500.00 | NULL |
| 7 | Muffy | 24 | moyale | 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.

16 | P a g e Advance database lab pre by jilo D

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | bikilq | 32 | naqamte | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | ambo | 2000.00 |
| 4 | Caltuu | 25 | gincii | 6500.00 |
| 5 | bontu | 27 | holota | 8500.00 |
| 6 | akmal | 22 | finfine | 4500.00 |
| 7 | guyo | 24 | moyale | 10000.00 |
+----+----------+-----+-----------+----------+

SQL - TRUNCATE TABLE Command

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 | bikilq | 32 | naqamte | 2000.00 |
| 2 | caala | 25 | gudar | 1500.00 |
| 3 | hacaalu | 23 | ambo | 2000.00 |
| 4 | Caltuu | 25 | gincii | 6500.00 |
| 5 | bontu | 27 | holota | 8500.00 |
| 6 | akmal | 22 | finfine | 4500.00 |
| 7 | guyo | 24 | moyale | 10000.00 |
+----+----------+-----+-----------+----------+
Following is the example of a Truncate 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)

17 | P a g e Advance database lab pre by jilo D