MySQL SQL

12
Revision Tour

In TWis Chapter

12.1 Introduction 12.9 Viewing a Table Structure

12.10 Inserting Data Into Another Table
12.2 Relational Data Model

12.3 MysQL-A Popular Relational Database 12.11 Modifying Data in Tables

Management System 12.12 Deleting Data from Tables

12.4 Accessing Database in MysQL 12.13 Altering Tables
12 5 Creating Tables in MysQL 12.14 Dropping Tables

12.6 Inserting Data into Table 12.15 SQL joins

12.7 Making Simple Queries Through Select Command 12.16 Indexes in Database
12.8 Creating Tables with SQL Constraints

12.1 INTRODUCTION
A database system is basically a computer based record keeping system. The collection of data,

usually referred to as the database, contains information about one particular enterprise. In a
typical file-processing system, permanent records are stored in various files. A number of
aifferent application programs are written to extract records from files and add records to the
limitations and disadvantages, such as
appropriate files. But this scheme has a number of major
data redundancy (duplication of data), data inconsistency, unsharable data, unstandardized
aata, insecure data, incorrect data etc. A database nanagement system is answer to all these problems
as it provides a centralized control of the data.

Various advantages of database systems are:

Database systems reduce data redundancy (data duplication) to a large extent.
Database systems control data inconsistency to a large extent.
Databases facilitate sharing of data.
Databases enforce standards.
Centralized databases can ensure data security.
Integrity can be maintained through databases.
455
 COMPUTER SCIENCE WITH PYTHON

456

12.2 RELATIONAL DA A MOr

describe the
structure of a database, and Cow
and certain
set of concepts to
A data model refers to a
the database
should obey.
constraints (restrictions) that
relational data model.
for databases is the
data model
O s t commonly used
rows and columns). These tahla
is organized into tables (i.e.,
are called relations.
In relational data A row
model, in
the a table
data represents a relationship among a set of values. Since a tahl
rm
referred to using the mathematical ter
collection of such relationships,
it is generally
1S a name.
model derives its
relation, from which the relational data
model.
Let us now revise different terms
used in relational
data must be atomic
in a cell; all rOws
or
related data ;
Relatio A table storing logically rows and
columns is immaterial,
of
of this table are distinct; ordering in a
from which the actual values appearing given
Domain This is a pool of values
column are drawn.
relation is generally referred to
as a tuple.
Tuple A row of a
to as an attribute.
Attribute A column of a relation is generally referred
attributes in a relation.
Degree This refers to the number of
in a relation.
Cardinality This refers to the number of tuples
It is a virtual table that does not really
exist in its own right but is instead
View
derived from one or more underlying base table(s).
This refers to a set of one or more attributes that can uniquely identify
Primary Key
tuples within the relation.
Candidate Key All attribute combinations inside a relation that can serve as primary key
are candidate keys as these are candidates for primary key position.
Alternate Key A candidate key that is not primary key, is called an alternate key.
whose values are derived from the primary key of
Foreign Key A non-key attribute,
some other table, is known as foreign key in its current table.

Referential Integrity
A referential integrity is a system of rules that a DBMS uses to ensure that relationships between
records in related tables are valid, and that users don't accidentally delete or change related
data.
You can set referential integrity when all of the following conditions are met
The matching field from the primary table is a primary key or has a unique index.
The related fields have the same data type.
Both tables belong to the same database. Referential integrity can't be enforced for linked
table from databases in other formats.
When referential integrity is enforced, you must observe the following rules
You can't enter a value in the foreign key field of the related table that doesn't exist in the
primary key of the primary table. However, you can enter a Null value in the foreign key
specifying that the records are unrelated.
You can't deletea record from primary table if matching records exist in a related tabie
a

You can't change a

primary key value in the primary table, if that record has relate
records.
 REVISION TOUR
457
12: MYSQL SQL
Choprer

RELATIONAL DATABASE MANAGEMENT SYSTEM

MYSQL - A POPULAR
(RDBMS) that
MuSOL freely available open source Relational Database Managemenl System
is a
It is downloadable from site www.mysql.org. In
a
11SeS Structured Query Language (SQL). contain many
information is stored in Tables. A single MySQL database can
MySOL database, with a rich set
store thousands of individual records. MySQL provides you
tables at once and and accessing data.
a secure environment for storing, maintaining,
of features that suPport
scalable alternative to many of the commercial RDBMSs
available today.
MySQL isa fast, reliable,
MySQL Database System
server instance and a MySQL
MySQL system refers to the combination of a MySQL
database
on the
architecture in which the server runs
database. MySQL operates using client/server network.
clients connect to the server over a
machine containing the databases and
in over the network and
The server (MySQL server) listens for client requests coming
that to the clients.
accesses database contents according
to those requests and provides
issue queries in a
are programs that connect
to the database server and
Clients with the standards based SQL (Structured
pre-specified format. MySQL is compatible
Query Language).
12.3.1 MySQL and SQL
and users must use, Structured
In order to data within the MySQL database, all programs
access
all RDBMSs.
(SQL). SQL is the set of commands that is recognised by nearly
Query Language
that enables you to create and operate
on
The Structured Query Language (SQL) is language
a

related information stored in tables.

relational databases, which are sets of

lassitication of SQL Statements

commands
of commands used for different purposes. SQL
SQL provides many different types

canbe mainly divided into following categories

Commands. Commands that allow you to perform
) Data Definition Language (DDL)
tasks related to data definition e,g,
and roles.
granting and revoking privileges
creating, altering and dropping.
maintenance commands
Commands. Commands that allow you to perform
(7) Data Manipulation Language (DML) modification of data stored in a
retrieval, insertion, deletion and
data manipulation e.g.,
database. allow you to
Commands. Commands that
Transaction Control Language (TCL)
(n) transaction is one complete unit
of work
transactions (a
manage and control the
involving many steps), eg
database, permanent
making changes to
database, permanent
undoing changes to
Creating savepoints
transactions
for current
Settingproperties above three categories of commands
are

commands also but

Ther
e are other categories
of SQL
mainly used by learners.
 COMPUTER SCIENCE WITH PYTHON - X
458

12.3.2 Common MySQL Data Types

operations for handling it
associated
Data types are means to identify the type of data and used data types of
fixed set of properties with the value. Some commonly
value's datatype associates a
MySQL are briefly listed below.
MySQL Data Types

Data Type
Spec
DataType Spec Integer(-2147483648 to 2147483647)
CHAR String (0 - 255) INT

BIGINT Integer(-9223372036854775808 to
VARCHAR String (0-255) 9223372036854775807)
Decimal (precise to 23 digits)
TINYTEXT String (0 - 255) FLOAT

DOUBLE
Decimal (24 to 53 digits)
TEXT String (0- 65535)
"DOUBLE" stored as string
DECIMAL
BLOB String(0 -65535)
DATE YYYY-MM-DD
MEDIUMTEXT String (0 16777215)
YYYY-MM-DDHH:MM:SS
MEDIUMBLOB String (0- 16777215) DATETIME
TIMESTAMP YYYYMMDDHHMMSS
LONGTEXXT String (0 - 4294967295)

TIME HH:MM:SS
LONGBLOB String (0 4294967295)
TINYINT Integer (- 128 to 127) ENUM One of preset options

SMALLINNT Integer (-32768 to 32767) SET Selection of preset options

MEDIUMINT Integer (-8388608 to 8388607) BOOLEAN TINYINT(1)

Difference between Char and Varchar
The difference between CHAR and VARCHAR is that offixed length and variable length. The CHAR
datatype specifies a fixed length character string. When a column is given datatype as CHAR(n),
then MySQL ensures that all values stored in that column have this length ie., n bytes. If a value
is shorter than this length n then blanks are added, but the size of value remains n bytes.
VARCHAR, on the other hand, specifies a variable length string. When a column is given
datatype as VARCHAR(7), then the maximum size a value in this column can have is nbytes.
Each value that is stored in this column stores exactly as you specify it i.e., no blanks are
added if the length is shorter than maximum length n. However, if you exceed the maximum
length n then an error message is displayed.

12.4 ACCESSING DATABASE IN MYSOL

Before you start writing SQL commands or making queries upon the data in tables of a database
you need to open the database for use. For this, after logging into MySQL, you need to issue a
Commarnd

Use <databasename
the
For example, we want to work on our sample database namely menagerie, so we shall write tn
following command after logging in MySOL.
mysql UsE menagerie ;
Database changed

mysql
Chapter 12: MYSQL SQL REVISION TOUR

459
12.5 CREATING TABLES IN MYSQL
Tables are defined with the
CREATE TABLE command. When a table is
named, data types and sizes are
column.
supplied for each column. Each table created, its columns are
must have at least one

The syntax of CREATE

TABLE command is
CREATE TABLE <table-name>
NOTE
(<column name> <data type> [
(<size>)
<columnname> <data type> [ ( <size> ],
Before issuing a CREATE TABLE
) ...
]); command, make sure that its parent
To create an employee table whose schema is as database has been opened using
follows: USE <database
employee (ecode, ename, sex, grade, name command
gross)
the SQL command will be
CREATE TABLE employee
ecode
integer,
ename
char(20),
sex
char(1),
grade char(2),
gross decimal )
12.6 INSERTING DATA INTO TABLE
The rows
(tuples) added
are to relations using INSERT command of
INSERT takes the SQL. In its
simplest form,
following syntax
INSERT INTO <tablename> [ < column list> ]|
VALUES ( <value>,
<value>.. . )5
for
example, to enter a row into employee table (defined earlier), you could the
statement
use
following
INSERT INTO employee
VALUES (1001, Ravi', "M, 'E4', 4670.00) ;

ee the order of values matches the order of columns in the CREATE TABLE
command of
poye. The same can be done with an alternate command as shown below
INSERT INTO employee (ecode, ename, sex, grade, gross)
VALUES (10e1, °Ravi', M, °E4', 4670.00) ;

ne iNSERT statement adds a new row to employee giving a value for every column in the
to
. Note that the data values are in the same order as the column names in the table. Data
b e added only to some columns in a row by specifying the columns and their data.
For instance, if you want to insert only ecode, ename and sex columns, you use the command:
INSERT INTO employee (ecode, ename, seX)
VALUES (2014, Manju', "F°)
 COMPUTER SCIENCE WITH PYTHON - A

460
command will NOTE
listed in the INSERT
The columns that are not otherwise,
it is defined for them,
have their default value, if In an INSERT statement,
only
those columns can be omitted tha
NULL value.
value and is
does not have a default have either default value
defined
It any other column (that is or they allow NULL values.
defined NOT NULL) is skipped or
omitted, an error message

generated and the row is not added.

Inserting NULL values

can type NULL
without quotes and NULL will
ill
To insert value NULL in a specific column, you
be inserted in that column.

Consider the following statement:

Sal, Comm, Deptno)
INSERT INTO EMPL (Empno, Ename, Job, Mgr, Hiredate,
6000, NULL, 20)
VALUES (8100, YASH, ANALYST, NULL, 10-MAY-03',
inserted.
See, for Mgr and Comm columns, NULL values have been

Inserting Dates
Dates are by default entered in YYYY-MM-DD' format i.e., first four digits depicting year,
followed by a hyphen, followed by 2 digits of month, followed by a hyphen and a two digit day.

All this is enclosed in single quotes.

12.7 MAKING SIMPLE QUERIES THROUGH SELECT cOMMANDp

The SELECT statement is used to pull information from a table. The general form of the
statement is :
SELECT what_to_select
FROM which_table
WHERE Conditions_to_satisfy

12.7.1 Selecting All Data

The simplest form of SELECT retrieves everything from a table. You just need to specify asterisk
in the select-list(what_to_select), e.g,
mysql> SELECT * FROM p e t ;

12.7.2 Selecting Particular Rows

You can select particular rows from a table by specify filtering condition through WHERE
clause of the SELECT statement, e.g.,

1. Select all pets with gender(sex) as male("m").

mysql>SELECT FROM pet
WHERE sex = "m;

2. Select all pets that were born on or after Jan 1, 2019.

mysql>SELECT FROM pet

WHERE birth = "2019-1-1'; date in yyyy-mm-dd format
 12 MYSQI SQL REVISION IOUR
(hupter 461
3. Select all female-dogs.

mysql> SELECT FROM pet

WHE RE Species = 'dog' AND sex
="f';
4. Select all snakes or birds.

mysql> SELECT FROM pet

WHERE Species =
snake' OR species ="bird';
5. Select all male cats.
mysql> SELECT FROM pet
WHERE (species = "cat' AND sex = "m') ;

12.7.3 Selecling Particular Columns

You can select particular columns by specifying column-names (i.e., attributes) in the select-list
of the SELECT command, e.g,
1. Display names and birth-dates of all pets.
mysql> SELECT name, birth FROM pet;

2. Display owners of pets born after Dec 2018.

mysql> SELECT Owner FROM pet
date in yyww-mm-dd format
WHEREbirth >2018-12-31'
12.7.4 Eliminating Redundant Data (with Keyword DISTINCT)
By default, data is selected from all the rows of the table, even if the data appearing in the result
gets duplicated. The DISTINCT keyword eliminates duplicate rows from the results of a
SELECT statement. For example,
1. Display names of all pet-owners (non-redundant).
mysql> SELECT DISTINCT Owner FROM pet

table
2. Display distinct species of pets from pet
mysql> SELECT DISTINCT (species) FROM pet.

2.7.5 Selecting From All the Rows -

ALL Keyword
In place of keyword DISTINCT, you give keyword ALL then the result retains the duplicate
DISTINCT nor ALL ; ALL is
Oput rows. It is just the same as when you specify neither
nttally a clarifier rather than a functional argument. Thus if you give

SELECT ALL city FROM suppliers

of the table without considering the duplicate
8 v e values of city column from every row
entries.
12.76 Viewing Structure of a Table
If you want to knowthe structure of a table, you can use Describe or Desc command as per following

syntax
DESCRIBE | DESC <table name>
for pet will display the structure oftable pet.
1stance, the comm
mands: DESCRIBE pet o r DESC

mysql> DESC pet;

 COMPUTER SCIENCE WITH PYTHON
462 -

12.7.7 Performing Simple Calculations

the expression/tormula
to be calculated nev
lo simple calculations,
pertorm you can write to
keyword SELECT, eg.
1. To caleulate 3.14159*6*6
mysql> SELECT 3. 14159*6*6;

2. To obtain current system date

mysql> SELECT Curdate();

12.7.8 Using Column Aliases

Ihe columns that you select ina query can be given a different name 1.e., column alias name for

outputpurposes. As per following syntax

Select <columnname> AS [columnalias] [, <columnname> AS [columnalias]]

From <tablename>
For example,
mysql> SELECT date, type AS "EVvent Type"
-FROM event.
12.7.9 Condition Based on a Range
The BETWEEN operator defines a range of values that the column values must fall in to make
the condition true. The range includes both lower value and the upper value. For example, to
list the items whose QOH falls between 30 to 50 (both inclusive), the command would be :
SELECT icode, descp, QOH
FROM items
WHERE QOH BETWEEN 30 AND 50

12.7.10 Condition Based on a List

To specify a list of values, IN operator is used. The IN operator selects values that match any
value in a given list of values. For example, to display a list of members from 'DELHI,
'MUMBAI, 'CHENNAI or 'BANGALORE' cities, you may give
SELECT * FROM members

WHERE City IN ( "DELHT', MUMBAT', CHENNAI', "BANGALORE)

The NOT IN operator finds rows that do not match in the list. So if you write

SELECT* FROM members

WHERE city NOT IN ("DELHT, "MUMBAI', "CHENNAT");

it will list members not from the cities mentioned in the list.

12.7.11 Condifion Based on Pattern Matches

sQL also includes a string-matching operator, LIKE, for comparisons on character strings using
patterns. Patterns are described using two special wildcard characters
percent (%). The % character matches any substring.
underscore (). The _character matches any character.
Chapter 12 MYSGL SQL REVISION TOUR
463
The LIKE keyword is used to select rows
containing columns that match a wildcard pattern.
Examples:
1. To list members which are in areas with
SELECT
pin codes starting with 13, the command is
firstname, lastname, city
FROM members
WHERE pin LIKE 13%;
2. To list names of
pets who have names ending with
SELECT name 'y, the command would be
FROM emp
wWHERE name LIKE %y
12.7.12 Searching for NULL
The NULL value in column can be searched for in a
a
clause. (Relational table using IS NULL in the WHERE
operators like =, <> etc. can't be used with
details of all
employees whose departments contain NULL (i.e., NULL). For example, to list
command novalue), you use the
SELECT empno,
empname, job
FROM emp WHERE DeptNo IS NULL
Non-NULL values in a table can be listed
using IS NOT NULL.
LET US REVISE

The basic elements

of MySQL SQL are: literals, datatypes, nulls and comments.
Literals are fixed data values.
Data types of MysQL SQL include NUMERIC (INT, TINYINT, SMALLINT,
FLOAT(M, D), DOUBLE(M, D) AND DECIMAL(M, D), DATE and TIME MEDIUMINT, BIGINT,
(DATE, DATETIME, TIMESTAMP, TIME and
YEAR(M) and STRING (CHAR(M), VARCHAR(M), BLOB or TEXT, TINYBLOB or
MEDIUMTEXT and ENUM). TINYTEXT, MEDIUMBLOB or
Column having no value is said to have NULL value.
The SELECT command
of SQL lets you make queries on the database.
The
DISTINCT keyword eliminates redundant data.
Operform calculations, the expressions can be written along with SELECT without specifying any table name.
A Small work table Dummy can be used for making simple calculations.
Ine
curdate pseudo-column returns the current system date.
he WHERE
clause is used to select
ne
specific rows.
logical operators OR (I), AND (&&) and NOT () are used to
ne
connectsearch conditions in the WHERE clause.
BETWEEN operator is used for making range checks in queries.
TheIN oper
perator is used for selecting values from a list of values.
The E operator is used for making character comparisons using strings.
The null value in a column
d can be searched for in a table using IS NULL in the WHERE clause.
 cOMPUTER SCIENCE WITH PYTHON
464
pYTHON- X
CONSTRAINTS
12.8 CREATING TABLES WITH SQL
TABLE Command. The SQL S
You already know that a table is created using
CREATE syntax for
CREATE TABLE is
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
.)
"column2" "data_type_for_column_2", we would type in
specified as above,
So, if we are to create the customer table

CREATE TABLE customer

First_Name char (50),
Last_Name char(50),
Address char (50),
City char(50),
Country char (25),
Birth_Datedate )
columns eg., this column
certain conditions on
But while creating tables, may need to apply
we
constraints are used.
conditions on columns, SQL
must not be blank or NULL for any record. To apply

12.8.1 SQL Constraints

of constraints that are applied on table columns include the following:
Common types
No. Constraints Description
NULL value.
NOT NULL Ensures that a column cannot have
Provides a default value for a column when none is specified.
2. DEFAULT

*Ensures that all values in a column are different.

3. UNIQUE
that in column satisfy certain criteria.
CHECK Makes sure all values a

Used to uniquely identify a row in the table.

Primary Key
*Used to ensure referential integrity of the data.
6. Foreign Ke
12.8.1A SQL NOT NULL Constraint
column hold NULL. f you do not want to allow NULL value in a column, you
By default, a can
allowable
will want to place a constraint on this column specifying that NULL is now not an
value. For example, in the following statement,
CONSTRAINT
CREATE TABLE Customer A Constraint is a condition or
SID integer NOT NULL, check applicable on a field or set
Last_Name varchar (30) NOT NULL, of fields.
First_Name varchar (30) );
Columns SID and Last_Name cannot include NULL, while First Name can include NUL
An attempt to execute the following SQL statement,
INSERT INTO Customer (Last_Name, First_Name)
VALUES (Wang', "Pedro');
OT
will result in an error because this will lead to column SID being NULL, which violates t
NULL constraint on that column.
 MYSOL SQL REVISION TOUR
Chapter
12:
465
128.1B SGL
DEFAULT Constraint
The DEFAULT constraint provides a default value to a column when the INSERT INTO
statement does not provide a specific value.

For example, if we create a table as below

CREATE TABLE Student

Student_ID integer Unique,

Last_Name varchar (30),
First_Name varchar (30),
Score DEFAULT 80 );
and execute the following SQL statement,

INSERT INTO Student (Student_ID, Last_Name, First_Name)

VALUES (10, Qureshi', 'Zeeshan") ; Se, value has been provided for Score
no
field
After this SQL query, the table will look like the following:

Student_1D Last_Name First_Name Score

See, Score field has got the
10 Qureshi Zeeshan 80 default value

Even though we didn't specify a value for the Score column in the INSERT INTO statement, it
does get assigned the default value of 80 since we had already set 80 as the default value for this
column.

12.8.1C SQL UNIQUE Constraint

ne UNIQUE constraint ensures that all values in a column are distinct. In other words, no two

rOWS can hold the same value for a column with UNIQUE constraint.

ror
example, in the following CREATE TABLE statement,

CREATE TABLE Customer

SID integer Unique,
Last_Name varchar (30),
First_Name varchar (30) );
olu n
SID has a unique constraint, and hence cannot include duplicate values. Such
a i n t does not hold for columns Last_Name and First_Name. So, if the table already
Contains the following rows

SID Last_Name FirstName

Wang Pedro

Qureshi Zeeshan

Rastogi Rajiv
 cOMPUTER SCIENCEWITH PYTHON - XlL

466
NOTE
the following SQL statement,
Executing Please note that a column that is specif.
INSERT INTO Customer as a primary key must also be uniqus
At
VALUES (3','Cyrus', 'Grace); the same time, a column that's
may or may not be a primary
unique
key.
will result in an error because the value 3 already exists in In
row With
addition, multiple UNIQUE constraints
the SID column, thus trying to insert another
can be defined on a table but PRIMARY KEY
that value violates the UNIQUE constraint. constraint is defined only once in thetable

12.8.1D SQL CHECK Constraint

all values in a column satisty certain conditions. OncCe
The CHECK constraint ensures that row it the new
row or update an existing value
defined, the database will only insert a new
is
satisfies the CHECK constraint. The CHECK constraint NOTE
used to ensure data quality For example, in the following
CREATE TABLE statement, Please note that MySQL does not enforce

CREATE TABLE Customer

the CHECK constraint although MysQL
syntax allows the definition of CHECK
S I D integer CHECK (SID > 0), constraint. As per MysQL documentation,
Last_Name varchar (30), Check constraint gets parsed butIGNORED'
First_Name varchar (30) );
12.8.1E PRIMARY KEY Constraint
in table. It can either be part of the actual
primary key is used to uniquely identify each row
a
A
to do with the actual record). A
record itself, or it can be an artificial field (one that has nothing
are used as a
a table. When multiple fields
primary key can consist of one or more fields on
primary key, they are called a composite key.
table is created (using CREATE TABLE) or by
Primary keys can be specified either when the
ALTER TABLE).
changing the existing table structure (using
Command
Defining Primary Key through Create Table
command through keywords PRIMARY KEY.
You can define a primary key in CREATE TABLE
Below are examples for specifying a primary key when creating a table:
CREATE TABLE Customer
SID integer not null PRIMARY KEY,

Last_Name varchar(30),
First_Name varchar (30) )
CREATE TABLE Customer
or
SID integer not nul1,

Last_Name varchar (30),

First_Name varchar(30),
PRIMARY KEY (SID) );
The latter way is useful if you want to specity a composite primary key (i.e., having a group of fields) e8
CREATE TABLE Customer

Branch integer not null,

SID integer not nul11, Combination of fields Branch and SID is the
Last_Name varchar (30), composite primary key of the table.
First_Name varchar (30), /
PRIMARY KEY (Branch, SID) );
 Chapter
12: MYSOL SOL REVISION TOUR
467
Defining Primary Key through Alter Table Command
You can detine a primary key in ALTER TABLE command NOTE
through keywords Before using the ALTER TABLE
ADD PRIMARY KEY (<key-field>) command to add a primary key,
you'll need to make sure that
Below are examples for specifying a primary key by altering a table
the field is defined as 'NOOT
NULL- in other words, NULL
ALTERTABLE Customer cannot be an accepted value for
ADD PRIMARY KEY (SID) ; that field.

12.8.1F Foreign Key Constraint

In an RDBMS,tables reference one another
through common fields and to ensure validity of
references, referential integrity is enforced. Referential
integrity is a system of rules that a DBMS uses
to ensure that relationships between records in related tables are
valid, and that users don't accidentally
delete or change related data. Referential
integrity is ensured through FOREIGN KEY constraint.
This is implemented as
explained in the following paragraph.
Whenever two tables are related by a common column
(or set of columns), then the related
column(s) in the parent table (or primary table) should be either declared a PRIMARY KEY or
UNIQUE key and the related column(s) in the child table (or related table) should have
FOREIGN KEY constraint. For instance, if we have two tables
having structures as given below:
Table: CUSTOMER Table: ORDERS
column name characteristic column name
characteristic
SID Primary Key Order_ID Primary Key
Last_Namnme Order_Date
First_Name Customer_SID Foreign Key
Amount
In the above
example, the Customer_SID columrn in the ORDERS table is a foreign key pointing
to the SID column in the
CUSTOMER table.
Just like primary key, Foreign key can also be created in two ways
and ALTER TABLE : through CREATE TABLE
commands.
Defining Foreign key through Create Table
In Create Table command, you can add foreign key's definition through following syntax
Foreign Key (<column-to-be -designated-as-foreign- key>) references
Master-Table (<primary-key-of master-table> )
rOlowing example shows how to specify the foreign key when creating the ORDERS table
CREATE TABLE ORDERS
Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (OrderID),
Foreign Key (Customer_SID) references CUSTOMER (SID) );
 COMPUTER SCIENCE WITH PYTHONY
468
field of ORDERS
table as toreign key referenci
referencing
designate Customer SID
Ihe above code will
SID field of CUSTOMER table.

Defining Foreign key through Alter Table

following syntax
n Alter Table command, you can add foreign key's definition through
ALTER TABLE <table-name>
ADD FOREIGN KEY (<column-to-be-designated -as-foreign-key>)
master-table>)
references Master-Table (<primary-key-of
that the ORDERS
altering a table. This
assumes

Following example specifies a foreign key by in

table has been created, and the foreign key has not yet been put
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer sid) REFERENCES CUSTOMER (SID) ;

Foreign Key and Storage Engine

different features
different storage engines that offer
MysQL is capable of creating databases in many
The default storage engine is ISAM but ironically it does not support foreign
keys. So, if you find that
not implemented, you need to change
the storage engine
your foreign key constraint are being ignored or
of your database tables to InnoDB storage engine.
To do so you simply need to write following command for your connecting tables

ALTERTABLE <tablename> ENGINE =

innodb;
ALTER TABLE table1 ENGINE = innodb;
e.g
Once done, you can create and implement foreign keys for your tables

12.8.2 Applying Table Constraints

table
When a constraint is to be group of columns of the table, it is called
applied on a
constraint. The table constraints appear in the end of table definition. For instance, if you
combination of icode and descp of table items to be unique, you may write it as
follows
want

CREATE TABLE items

icode char (5) NOT NULL
descp char (20) NOT NULL
ROL integer,
QOH integer
CHECK (ROL QOH), these are a table constraints

UNIQUE (icode, descp)):

The above statement ensures that the combination of icode and desc in each row must be unique.
1s
A constraint applied on one column (e.g., as you define not null with a column definition)
known as column constraints.

12.9 VIEWING A TABLE STRUCTURE

to
Once you have created a table, you may want to view its structure. Anytime, if you waln
d
view an already created/existing table's structure, you may use DESC[RIBE] commai
of MySO
 12 MYSQL SQL REVISiON TOUR
Chapter

469
Vou can use DESYRIBE] Command as follows:
DESC[RIBE] <tablename;
For example,
DESCempl1
DESCRIBE empl

12.10 INSERTING DATA INTO ANOTHER TABLE

You already know that data is added to tables using INSERT INTO command
e.g,
INSERT INTO employee
VALUES (1001, "Rahman, "M', "E4', 14700.00) ;
Or
INSERT INTO employee (ecode, ename, sex)
VALUES (3005, Meera', "F*)
INSERT command can also be used to take or derive values
from one table and place them in
another by using it with a query. To do this,
simply replace the VALUES clause with an
appropriate query as shown in the following example
INSERT INTO branch1
SELECT*FROM branch2 This query will generate data to
WHERE gross > 700e.e0 be inserted into table

It will extract all those rows from branch2 that have gross more than 7000.00 and insert this
produced result into the table branch1. But for above command to work, table namely branch1
must be an
existing table of the database.

12.11 MODIFYING DATA IN TABLES

You can modify data in tables
using UPDATE command of SQL. The UPDATE command
Specities the rows to be changed using the WHERE clause, and the new data using the SET
Keyword. The new data can be a specified constant, an expression or data from other tables. For
example, to change the reorder level ROL of all items to 250, you would write
UPDATE items
SET ROL=250
To
update the ROL and QOH for items having icode less than 'IO40", we shall write
UPDATE items
SET ROL = 400, QOH = 700
WHERE icode <"I040
dOuble the gross pay of emplovees of grade 'E3 and 'E4, you use the command:

UPDATE employee
SET gross = gross * 2
HERE (grade = "E3' OR grade = E4°) ;
12.12 DELETING
DATA FROM TABLES
To delete some DELETE commands. The DELETE command
Temo Ome data from tables, you can use SQL rows, not individual field values, so no field
wS from a table. This removes the entire
lm
Sument is needed or
accepted.
 cOMPUTER SCIENCE WITH PYTHON.
470
form
The DELETE statement takes the following general
DELETE FROM <tablename>
[WHERE <predicate> ]s
For instance, to remove the tuples from employee table that have groSs (salary) less than 2200 00
,
the following command is used
DELETE FROM employee
WHERE gross < 2200.00;
If you specify no condition with WHERE, then all the rows of the table will be deleted, eg,
To remove all the contents of items table, you use the command:
DELETE FROM items

12.13 ALTERING TABLES

The ALTER TABLE command is used to change definitions of existing tables. Usually, it is used
to add columns to a table. Sometimes it is used to delete columns (depending on privileges) or
change their sizes. In general, in MySQL SQL, ALTER TABLE command is used
to add a column to add an integrity constraint
to redefine a column (datatype, size, default value).
You can use ALTER TABLE for adding columns as follows
ALTER TABLE <table name> ADD <column name> <data type><size> [<constraint name>];

For add column tel_number

instance, to a new
of type integer in table Empl you may give
ALTER TABLE Empl
ADD (tel_number integer) ;

To modijfy existing columns of table, ALTER TABLE command can be used according to following
syntax:
ALTER TABLE <tabllename>
MODIFY (columname newdatatype (newsize)) [FIRST|AFTER column] ;

To modify column Job of table Empl (given just before the Assignment at the end of this chapter)
to have new width of 30 characters, you may give:
ALTER TABLE Empl
MODIFY (Job char(30) );
Sometimes you may need to change the name of one of your columns. For this you can use
CHANGE clause of ALTER TABLE command
ALTER TABLE
as per following syntax
CHANGE [COLUMN] old_col_name new col_name
column_definition
For instance, to change the existing column namely First Name of table Student,
you may write:
to Fi'st
ALTER TABLE Customers See, complete column
description is
given along with the new name.
CHANGE First_Name FirstName VARCHAR (20);
 1 2 : MYSQL SGQL REVISION TOUR

12.14 DROPPING TABLES

471
The DROP TABLE command of SQL lets you drop
using a DROP TABLE command is: a table from the database.
The syntax for
DROP TABLE [IF EXISTS] <tablename
That is, to drop table
iteims, you need
a
to write:
DROP TABLE items
Once this command is
given, the table name is
can be given on that object.
no
longer recognized and no more commands
The IF EXISTS clause of DROP TABLE first checks
not. If it given whether the table exists in the database
or does, then it drops the mentioned table from the database. For instarnce, consider the
following query
DROP TABLE IF EXISTS players;
The above query will first check for
existence of players table in current database. If
it (table players) will be it exists, then
dropped from the database.
12.15 SQL JOINS
An SQL Join is
query that fetches data from two
a
or more tables whose records
one another based on a condition. The common
are
joined with
syntax for getting JOIN of two or more tables is
SELECT <field list>
FROM <table1>, <table2> [, <table3>. .
.]
WHERE <join condition for the tables>

For example,

mysql> SELECT ENAME, LOC

FROM EMPL, DEPT
WHERE ENAME =
"ANO0P
AND EMPL.DEPTNO = DEPT.DEPTNO; -see this is join condition

he above SQL statement will irst join the tables EMPL and DEPT on the basis of join condition
EPL.DEPTNO = DEPT.DEPTNO. That is a record from table EMPL will join with a record
rom DEPT table whose DEPTNO is equal to its DEPTNO.

e r e can be many types of JOINS possible. These are:

0) Cartesian Product. An SQL join query without any join condition returns all the records
of joined with all the records of the other table. It is known as a Cartesian Product, This
type of join is also known as a CROSS JON.
) Equi Join. An SQL join query that joins two or more tables based on a condition using
equality operator.
n n e r Join. An INNER JOIN implerments an equi join. In this join, only those rows are
condition. The Join conditions can
erurned from both the tables that satisfy the join
match records based on one or more columns.
(io) atural
Natural Join. A NATURAL JOIN is a type of equi join where the join condition

columns in both tables.

Ompares all the same names
472 COMPUTER SCIENCE WITH PYTHON X

) Left Join. The LEFT JOIN is a particular type of join that selects rows from both left and
right tables that are matched, plus all rows from the left table (Table A) even with ne
no
matching rows found
in the right table
(Table B).
A B A

(vi) Right Join. The RIGHT

JOIN is a particular SELECT <fields list> B SELECT <fields list>
FROM TableA T1 FROM TableA T1
type of join that selects RIGHT JOIN TableB T2
LEFT JOIN TableB T2
rows from both left ON T1.Key T2.Key
= ON T1.Key
T2.Key
=

SELECT<fields list>
and right tables that FROM TableA T1
are matched, plus all INNER JOIN TableB T2
B ON T1.Key = T2.Key B
rows from the right
table (Table B) even
with no matching rows SELECT<fields list> SELECT <fields list
FROM TableA T1 FROM TableA T1
found in the left table LEFT JOIN TableB T2 RIGHT JOIN TableB T2
ON T1.Key = T2.Key ON T1.Key = T2.Key
(Table A). WHERE T2.Key IS NULL; WHERE T1.Key IS NULL;

Figure 12.1 SQL Joins

12.16 INDEXES IN DATABASE

An index is a data structure maintained by a database that helps it find records within a table
more quickly. An index stores the sorted/ordered values within the index field and their
location in the actual table. An index in a database is also a table (a database structure) which
stores arranged values of one or more columns in a specific order.

Roll No. Location Pointer

101

102

103
Notice the Percentage
Roll No. Name
order of 104
records in 83
101 Nimish
each of
these index 102 Junaid 87
Percentage Location Pointer
tables
95
103 Gurjyot
83
Catherine
85
104
85
Table Student
87

95

Name Location Pointer

Catherine
Gurjyot
Junaid Inclex tables

Nimish

Figure 12.2 Multiples inde.xes of single data table

 12 MYSQL SQL REVISION TOUR 473
hopter

Indexes in MySQL
Creating
MySQL. also provides commands to create indexes in a database. You can create indexes
fn MvSQL using following commands. MySQL provides two ways to create indexes:
(Create index at the time of table creation i.e., along with CREATE TABLE command.
() Create index on an already existing table.

) Creating Index at Time of Table Creation

Check Point
i.e., with CREATE TABLE)
12.1
To create index on a field at the time of table creation, you
1. What is SQL ? What are the different can specify the index-fields with INDEX clause of CREATE
categories of sQL commands ? TABLE command, whose syntax is given below:

2.What is a datatype? Name some

CREATE TABLE table_name
datatypes available in MySQL.
3. How would you calculate 13 *15 in SQL ? Recall that in a
column1 datatype [ NULL | NOT NULL . syntax optional
4. Is NULL value the same as 0 (zero) ? parts are gnen in
Write the reason for your answer.
column2 datatype [ NULL | NOT NULL 1, square brackets I}

5. Write the UPDATE command to increase

thecommission (Column name: COMM) column_n datatype [ NULL | NOT NULL ],
by 500 of all the Salesmen who have
achieved Sales (Column name : SALES) INDEX index_name (index_coll [(length) ] [ASC | DESC],
more than 200000. The table's name is
COMPANY.
index_col2 [ (length)] [ASC | DESC]
6. While using SQL pattern matching
what is the difference between index_coln [(length)] [ASC | DESC])
(underscore) and% wildcard symbols?
. Wnte one similarity and one difference
Please note that a CREATE TABLE statement with INDEX
Detween CHAR and VARCHAR data
clause creates both the table as well as the index at the same
types. ICBSE OD 15]
8.Write SQL statement to display time. Consider the following example

Today, the date is <current date

CREATE TABLE PLAYERS
.Write a command to add a NOT NULL
PLAYERNO INTEGER NOT NULL,
constraint on FEES column of a student
table. NAME CHAR (15) NOT NULL,

What is a constraint ? Name some DOB DATE,

constraints that you can apply to SEX CHAR (1) NOT NULL,
enhance database integrity. ADDRESS VARCHAR (100) NOT NULL,
*Wnat is primary key ? What is PRIMARY PHONE CHAR(15),
KEY constraint ?
TEAMNO CHAR (4) NOT NULL,
4Whatis NOT NULL constraint ? What 1s PRIMARY KEY (PLAYERNO) ,
DEFAULT constraint ?
13. When INDEX Player_idx (TEAMNO)
a
column's value is skipped in an
NSERT Command, which value is
14.
Mserted in the database table by the name
The above SQL statement will create a
te MySql command to display the it will also create an index
ust Players and at the same time
15. Wrteof MySa
existing databases. ICBSE 121
command will be used namely Player_idx on the basis of field TeamNO. Since, we
it will take
open an have not specified any order ASC or DESC,
"CONTACTS". already existing database
ICBSE OD 11] default order ASC i.e', ascending for the
index field.
 COMPUTER SCIENCE WITH PYTHON
474 -

(i) Create Index on an Already Existing Table

Another way of creating index is to create it for already existing tables using following command .

CREATE INDEX <indexname> ON <tablename> (<column>[ASC DESC],

<column> [ASC DESC]

To enforce unique values, add the UNIQUE keyword

CREATE UNIQUE INDEX <indexname> ON <tablename> (<column>[ASC | DESCJ,
<column> [ASC | DESC]...)
For example,
CREATE UNIQUE INDEX Players_idx
ON Players (TeamNo) ;
The above statement will create a Unique index on the basis of field TeamNo on an existing
table namely Players.

LET US REVISE
CREATE TABLE command is used to create tables in database.
INSERT INIOcommand is used to insert data in the table.
Toinsert data from other tables, subquery can be used inside INSERT INTO command.
Existing data in tables can be changed with UPDATE command.
Tuples in a table can be deleted using DELETE command.
ALTER TABLE command is used to alter the
definition of already created tables.
WithALTER TABLE, new columns can be added, existing columns can be redefined.
DROP TABLE command drops a table from a database.
A join is an SQL query that fetches records from two or more tables based ona condition.
Anindex is a data structure maintained by a database, that stores the sorted values within the index field and their
location in actual data.
You can create indexes either by using INDEX clause
of CREATE TABLE command or by using command CREAlE
INDEX.

bjective Type Questions

OTQ5
Multiple Choice Ouestions
1 A relational database consists of a collection of
(a) Tables (b) Fields (c) Records (d) Keys
2 A relational database consists of collection of
a

(a) Tuples (b) Attributes (c) Relations (d) Keys

A(n) in a table represents a
logical relationship
(a) Attribute
among a set of values
(b) Key (c) Tuple (d) Entry