Chapter 5:

MySQL – Revision Tour

Informatics Practices Revised as per

CBSE
Class XII (CBSE Board) Curriculum
2022-23

Presented By :Susmita Cholkar

Learning Objective
 Introduction
 What is DATA
 Relational Database Model
 MYSQL
 Accessing Database in MySQL
 Creating Tables in My SQL
 Making Simple Queries
 SQL Constraints
 Viewing Table Structure
 Modifying
 Deleting
 Altering
 Dropping
Introduction
 A database system is basically a computer-based record keeping system.
 Collection of data usually refer to as database contains information about
one particular enterprise .
 In a typical file processing system permanent records are stored in various
files
 But this system has major limitations like redundancy in consistency
unstandardized data insecure data incorrect data .
 So database systems reduce data redundancy
 Database systems control data inconsistency
 Database facilitates sharing of data
 Centralized databases can ensure data security
 Integrity can be maintained through databases
What is DBMS
What is the Database?
A database is an organized collection of interrelated data
stored together to serve applications. It work like a
container which may contains the various database objects.

Most of the databases stores data in the form of Relations

(also called Tables). Such Database are known as Relational
Database.

A Software used to manage Relational database is called

RDBMS (Relational Database Management System).

Database • Tables
• Queries
Some Commonly used RDBMS • Views
software are- Oracle, • Index

MySQL, MS SQL Server,

SyBase and Ingress etc.
Why Database System is used? (Advantages)

 Databases reduces Redundancy

It removes duplication of data because data are kept at one
place and all the application refers to the centrally maintained
database.
 Database facilitate Sharing of Data
Data stored in the database can be shared among several
users.
 Database ensures Security
Data are protected against accidental or intentional disclosure to
unauthorized person or unauthorized modification.
 Database maintains Integrity
It enforces certain integrity rules to insure the validity or
correctness of data.For ex. A date can’t be like 25/25/2000.
Data Model
Data model describes ‘How data is organized or
stored’ in the database. It may be-
 Relational Data Model
In this model data is organized into Relations or
Tables (i.e. Rows and Columns). A row in a table
represents a relationship of data to each other and also
called a Tuple or Record. A column is called Attribute
or Field.
Data Models
Attribute (Field) Table (Relation)
Entity
Name Address DOB City Phone
(Record)

Amar 2/3 Chowk 01.04.1990 Kanpur 12345

Kailash 22 Katra 23.10.1992 Lucknow 67890

Relational Model
RDBMS Terminology
 Relation (Table)
A Relation or Table is Two-Dimensional (Matrix) like structure
arranged in Rows and Columns. It has the following properties-
 Column homogeneous - All items in a column are of
same data type.
 Each column assigned a unique name and must have atomic
(indivisible) value.
 All rows of a relation are distinct i.e. no two identical rows
(records) can exist in the Relation.
 Ordering or Rows (Records) or Columns (fields) are
immaterial.
 Domain
It is collection (set) of possible values from which the value for a
column is derived.
 Tuple/ Entity/ Record :
A Row of a table is called Tuple or Record.
 Attribute/ Field:
Column of a table is called Attribute or Field.
 Degree : Number of columns (attributes) in a table.
 Cardinality : Number of Records in a table.
Concept of Keys
In a Relation, each record must be unique i.e. no two identical
records are allowed in the Database. A column or combination of
column which identifies a record called Key of the Table. A key
attribute must have unique (non-repeatable ) value.
 Primary Key
A set of one or more column that can uniquely identify a record in
the relation is called Primary Key.
 Candidate Key
A Column or group of columns which can be used as primary key
are called Candidate keys, as they are candidate to become as
Primary key.
 Alternate Key
A Candidate Key that is not a Primary key is called Alternate key.
 Foreign Key
A non-key column whose values are derived from the primary key
of some other table is called Foreign key.
Introduction to MySQL
MySQL is an Open Source, Fast and Reliable Relational Database
Management System (RDBMS) . It is alternative to many of the
commercial RDBMS. The main features of MySQL are-
 Open Source & Free of Cost:
It is Open Source and available free of cost. It is part of LAMP
(Linux, Apache, MySQL, PHP/ Perl/ Python) Open Source group.
 Portability:
It can be installed and run on any types of Hardware and OS like
Linux, MS Windows or Mac etc.
 Security :
It offers privilege and password system for authorization.
 Connectivity
It may connect various types of client using different protocols and
Programming Languages .
 Query Language
It uses SQL (Structured Query Language) as query language,
which is standardized by ANSI.
Types of SQL Commands
MySQL follows SQL specifications for its commands . These
SQL commands can be categorized as -
 Data Definition Language (DDL)
These SQL commands are used to create, alter and delete
database objects like table, views, index etc.
Example : CREATE , ALTER , DROP etc.
 Data Manipulation Language (DML)
These commands are used to insert, delete, update and retrieve
the stored records from the table.
Ex. SELECT…., INSERT…, DELETE…, UPDATE…. etc.
 Transaction Control Language (TCL)
These commands are used to control the transaction.
Ex. COMMIT, ROLLBACK, SAVEPOINT etc.
 Data Control Language (DCL)
These commands are used to manipulate permissions or access
rights to the tables etc.
Ex. GRANT , REVOKE etc.
Database Handling commands in MySQL

 Creating a Database.
The following command will create School database in MySQL.
mysql> CREATE DATABASE School;
 Opening a database
To open an existing database, following command is used.
mysql> USE school ;
 Getting listings of database and tables
mysql> SHOW DATABASES;
mysql> SHOW TABLES;
 Deleting a Database and Table
mysql> DROP DATABASE School;
mysql> DROP TABLE Student;
 Viewing Table Structure
S
mysql> DESCRIBE Student; e
Shows the name of
l
currently
e open database
c
t
Data type in MySQL
 Numeric Data Types:
 INTEGER or INT – up to 11 digit number without decimal.
 SMALLINT – up to 5 digit number without
decimal.
 FLOAT (M,D) or DECIMAL(M,D) or
NUMERIC(M,D)
Stores Real numbers upto M digit length
(including .) with D
decimal places.
e.g. Float (10,2) can store 1234567.89
 Date & Time Data Types:
 DATE - Stores date in YYYY-MM-DD format.
 TIME - Stores time in HH:MM:SS format.
 String or Text Data Type:
 CHAR(Size)
A fixed length string up to 255 characters. (default is 1)
Char,Varchar, Date and Time values should be enclosed with single (‘ ‘) or
VARCHAR(Size)
double ( “”) quotes in MySQL.
A variable length string up to 255 characters.
Creating Tables
 Creating Simple Tables:
CREATE TABLE < Table Name>
(<Col name1><data type>[(size)][Constraints],….);
Data types- INTEGER, NUMERIC(P,D), CHAR(n), VARCHAR(n), DATE etc.

mysql> CREATE TABLE Emp

(empID integer, Emp
ename char(30),
empID ename city pay
city char(25),
pay decimal(10,2));

 Creating Table from Existing Table:

CREATE TABLE <Table name> [AS] (<Select Query>);

CREATE TABLE Staff ( Select empID, ename, pay From

Emp); CREATE TABLE Staff AS ( Select * From Emp);

Staff table will be identical to Emp table.

Making Simple Queries Using SELECT
The SELECT command of SQL, empower you to make a
request (queries) to retrieve stored records from the
database.
The syntax of SQL is given below-
SELECT < [Distinct | ALL] *| column name(s)>
FROM <table(s)>
WHERE <condition>
ORDER BY <column name> [ASC | DESC] ;
Consider the table Student having some records as –

StID Name Fname DOB City Class

S1 Amitabh Harivansh Rai 1948-11-10 Allahabad 12
S2 Sharukh Firoz 1970-05-10 Delhi 11
S3 Irphan Akbar 1970-10-05 Jaipur 11
S4 Salman Salim Javed 1972-04-10 Mumbai 10
S5 Abhishek Amitabh 1975-03-12 Mumbai 10
Making Simple Queries – Cont..
 Selecting all columns
If you want to view all columns of the student table, then you
should give the following command-
mysql> SELECT * FROM Student ;

MySQL will display the all records with all columns in the Student
table.
* Is used to represent all columns.
StID Name Fname DOB City Class
S1 Amitabh Harivansh Rai 1948-11-10 Allahabad 12
S2 Sharukh Firoz 1970-05-10 Delhi 11
S3 Irphan Akbar 1970-10-05 Jaipur 11
S4 Salman Salim Javed 1972-04-10 Mumbai 10
S5 Abhishek Amitabh 1975-03-12 Mumbai 10
Making Simple Queries – Cont..
 Selecting columns
If you want to view only Name and City columns of the student table
mysql> SELECT Name, City FROM Student ;
Name City
Amitabh Allahabad
Sharukh Delhi
Irphan Jaipur
Salman Mumbai
Abhishek Mumbai

mysql> SELECT City, Name FROM Student ;

City Name
Allahabad Amitabh
Delhi Sharukh
Jaipur Irphan
Mumbai Salman
Mumbai Abhishek
Making Simple Queries – Cont..
 Eliminating Duplicate values in a column -
DISTINCT
mysql> SELECT City FROM Student ;

City
Mumbai is repeated
Allahabad
Delhi
Jaipur
Mumbai
Mumbai

mysql> SELECT DISTINCT City FROM Student ;

City
Only Unique Cities
Allahabad
are displayed
Delhi
Jaipur
Mumbai
Making Simple Queries – Cont..
 Doing simple calculations
 We can also perform simple calculations with SQL Select command. SQL provide a dummy table named
which can be used for this purpose.
 mysql> SELECT 4*3 ;
 We can also extend this idea with a columns of the existing table. mysql> SELECT Name, Sal
*12 FROM EMP ;
Using Column Aliases
We can give a different name to a column or expression (Alias) in the
output of a query. Alias for Sal*12

mysql> SELECT Name, Sal*12 AS ‘Annual Salary’ FROM EMP;

mysql> SELECT Name, DOB AS ‘Date of Birth’ FROM
Student; mysql> SELECT 22/7 AS PI FROM Dual;

When Alias name is a single word then ‘ ‘ is not required.

Selecting Specific Rows – WHERE clause
 WHERE <Condition>
We can select specific records by specifying condition with
WHERE clause.
mysql> SELECT * FROM Student WHERE City=‘Mumbai’;
StID Name Fname DOB City Class
S4 Salman Salim Javed 1972-04-10 Mumbai 10
S5 Abhishek Amitabh 1975-03-12 Mumbai 10

mysql> SELECT Name, Fname, City from Student

WHERE Class >10;
Condition

Name Fname City Class

Amitabh Harivansh Rai Allahabad 12
Sharukh Firoz Delhi 11
Irphan Akbar Jaipur 11
Selecting Specific Rows – WHERE clause

 Relational Operators
We can use the following Relational operators in condition.
=, > , < , >=, <=, <>, IS , LIKE, IN,
BETWEEN
 Logical Operators
We can use the following Logical Operators to connect two conditions.
OR , AND , NOT (!)

mysql> SELECT Name, City from Student

WHERE City <> ‘Mumbai’ AND Class>10;

mysql> SELECT * FROM Emp

WHERE Sal >10000 OR Job
=‘Manager’;

mysql> SELECT * FROM Student

Selecting Specific Rows – WHERE clause

 Specifying Range of Values – BETWEEN

Operator
mysql> SELECT * FROM Emp
WHERE Sal BETWEEN 5000 AND 10000 ;
The same query can also be written as -
mysql> SELECT * FROM Emp
WHERE Sal >= 5000 AND Sal<=10000 ;
Other Logical operators also can be applied-
mysql> SELECT * FROM Emp
WHERE
NOT Sal BETWEEN 5000
AND 10000 ;

 Specifying List – IN
Operator
mysql> SELECT * FROM Emp
WHERE Sal IN (5000, 10000) ;
The same query can also be written as
-
mysql> SELECT * FROM Emp
Selecting Specific Rows – WHERE clause

 Pattern Matching – LIKE Operator

A string pattern can be used in SQL using the following wild card
 % Represents a substring in any
 _ length Represents a single
Example. character
‘A%’ represents any string starting with ‘A’ character.
‘_ _A’ represents any 3 character string ending with ‘A’.
‘_B%’ represents any string having second character ‘B’
‘_ _ _’ represents any 3 letter string.

A pattern is case sensitive and can be used with LIKE operator.

mysql> SELECT * FROM Student WHERE Name LIKE ‘A%’;

mysql> SELECT * FROM Student WHERE Name LIKE ‘%Singh%’;
mysql> SELECT Name, City FROM Student
WHERE Class>=9 AND Name LIKE ‘%Kumar%’ ;
Selecting Specific Rows – WHERE clause

 Searching NULL Values – IS Operator

mysql> SELECT * FROM Student WHERE City IS NULL ;
The NOT Operator can also be applied -
mysql> SELECT * FROM Student WHERE City IS NOT NULL;

 Ordering Query Result – ORDER BY Clause

A query result can be orders in ascending (A-Z) or descending (Z-A)
order as per any column. Default is Ascending order.
mysql> SELECT * FROM Student ORDER BY City;

To get descending order use DESC key word.

mysql> SELECT * FROM Student ORDER BY City DESC;

mysql> SELECT Name, Fname, City FROM Student

Where Name LIKE ‘R%’ ORDER BY
Class;
Inserting Records in a Table
You can insert record in the table by using by using the
following DML command.
INSERT INTO <Table Name> [<Column list>]
VALUES <list of values>
Suppose a table named STUDENT has been created with
the following structure.
StID NAME FNAME DOB CITY CLASS

We can insert a record as follows-

mysql> INSERT INTO Student VALUES
(‘s1’,’Amitabh’, ‘Harivansh’,’1955-10-25’, ‘Mumbai’, 12);
mysql> INSERT INTO Student VALUES
(‘s2’,’Sharukh Khan’, NULL,’1972-5-25’, ‘Delhi’,
10);
mysql> INSERT INTO Student (StID, FName, Name, Class)
VALUES (‘s3’,’Amitabh’, ’Abhishek’, 10);
Inserting Records from Other Table
 You can insert all or selected record(s) in the table from another table by using Select … command in
place of Values.
 Suppose a table named NEWSTUDENT has been created and records to be inserted from OLDSTUDENT
table having the same structure of columns.

mysql> INSERT INTO Newstudent VALUES Both tables must have

(SELECET * FROM Oldstudent); same structure

mysql>INSERT INTO Newstudent VALUES

(SELECT * FROM Oldstudent WHERE City=‘Mumbai’);
mysql> INSERT INTO Newstudent (StID, Name, Class)
VALUES (Select StID, Name,Class FROM
Oldstudent WHERE Class>=11);
Deleting Records from the Table
You can delete all or selected record(s) from the table by using
the following DML command.
DELETE FROM <Table Name> [WHERE <Condition>]
This command will
mysql> DELETE FROM Student ; delete all records…

mysql> DELETE FROM Student WHERE

City=‘Mumbai’ ; mysql> DELETE FROM Student WHERE
Class >=11 ;

 You can recall (Undelete) records by giving ROLLBACK

command.
mysql> ROLLBACK ;
 You can issue COMMIT command to record the changes
permanently.
mysql> COMMIT;
Modifying Records in the Table
You can modify the values of columns of all or selected
records in the table by using the following DML
command.

UPDATE <Table Name> SET <Column> =

<Expression> [WHERE <Condition>]
mysql> UPDATE Student SET Class =10 ;
mysql> UPDATE Student SET FName= CONACT(‘Mr.’, FName’) ;

mysql> UPDATE Emp SET Sal = Sal+(Sal*10/100);

mysql> UPDATE Emp SET Sal = Sal+(Sal*10/100)

WHERE Sal <=10000;
mysql> UPDATE Emp SET City = ‘Dehradun’
WHERE CITY IS NULL;
SQL Constraints
Not Null
 by default a column can hold null if you do not want to allow NULL.Values in a
column you will want to place a constraint on this column specifying that null
is now not and an allowable value .

column_name data_type NOT NULL;

CREATE TABLE tasks (

id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
start_date DATE NOT NULL,
end_date DATE
);
SQL Default constraint
 the default constraint provides a default value to the column when the insert
into statement does not provide a specific value

column_name data_type DEFAULT default_value;

CREATE TABLE cart_items

(
item_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
quantity INT NOT NULL,
price DEC(5,2) NOT NULL,
sales_tax DEC(5,2) NOT NULL DEFAULT 0.1,
CHECK(quantity > 0),
CHECK(sales_tax >= 0)
);
SQL Unique constraint
 the unique constraint ensures that all values in a column are distinct. In other
words no two crows can hold the same value for a column with unique
constraint.
CREATE TABLE table_name(
...,
column_name data_type UNIQUE,
...
);

CREATE TABLE suppliers (

supplier_id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL UNIQUE,
address VARCHAR(255) NOT NULL,
PRIMARY KEY (supplier_id),
CONSTRAINT uc_name_address UNIQUE (name , address)
);
SQL Check constraint
 the check constraint ensures that all values in a column satisfies certain
condition. Once defined the database will only insert a new row or update an
existing row if the new value satisfies the check constraint. The check
constraint is used to ensure data quality for example

Create Table Student(Stdid integer Unique Check (stdid>0),

Lname varchar(20),Fname varchar(200 , age int Check (age>18) ,
score int );
primary key constraint
 a primary key is used to uniquely identify each row in a table. It can either be
part of the actual record itself or it can be an artificial field a primary key
can consist of one or more fields on a table. Then multiple fields are used as
a primary key they are called a composite key .
 primary keys can be specified either when the table is created using create
table or by changing the existing table structure by using alter table .

Create Table Customer( Create Table Customer(

Custid integer, Custid integer PRIMARY Key,
Lastname varchar(20) not null, Lastname varchar(20) not null,
FirstName varchar(20) ,PRIMARY Key(Custid)); FirstName varchar(20) );

Alter Table Customer Add Primary Key (Custid);

Foreign key constraint
 in an RDMS 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 the
relationship between records in a
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 it follows
In this diagram, each customer can have zero or
many orders and each order belongs to one
customer.

The relationship between customers table and

orders table is one-to-many. And this relationship
is established by the foreign key in the orders
table specified by the customerNumber column.

The customerNumber column in the orders table

links to the customerNumber primary key column
in the customers table.

The customers table is called the parent table or

referenced table, and the orders table is known as
the child table or referencing table.

Typically, the foreign key columns of the child

table often refer to the primary key columns of
the parent table.
CREATE TABLE categories(
categoryId INT PRIMARY KEY,
categoryName VARCHAR(100) NOT NULL
);
ON UPDATE CASCADE
CREATE TABLE products( ON DELETE CASCADE

productId INT PRIMARY KEY,

productName varchar(100) not null,
categoryId INT,
CONSTRAINT fk_category
FOREIGN KEY (categoryId)
REFERENCES categories(categoryId)
);
To Drop a Foreign Key Constraint
To drop a foreign key constraint, you use the ALTER TABLE statement:

ALTER TABLE table_name

DROP FOREIGN KEY constraint_name;

In this syntax:
•First, specify the name of the table from which you want to drop the
foreign key after the ALTER TABLE keywords.
•Second, specify the constraint name after the DROP FOREIGN
KEY keywords.
Notice that constraint_name is the name of the foreign key constraint
specified when you created or added the foreign key constraint to the
table.
Working with Functions

 What is Function?
A function is a special types of command that
performs some operation and returns a single
value as a result.
It is similar to method or function in JAVA, which
can be called by giving some argument.
 Types of Functions:
 Numeric Functions
 String Functions
 Date & Time Function
 Aggregate Functions
Numeric Functions
These functions may accept some numeric values and
performing required operation, returns numeric values as result.

Name Purpose Example

MOD (M, N) Returns remainder of M divide Select MOD(11,4) ;
by N 3
POWER (M, N) Returns MN Select POWER(3,2);
POW (M, N) 9
ROUND (N [,M]) Returns a number rounded off Select ROUND(15.193,1);
up to M place. If M is -1, it 15.2
rounds nearest 10. Select ROUND(15.193);
If M is not given, the N is 15
rounded to the nearest Integer.
SQRT (N) Returns square root of N Select SQRT(25);  5

TRUNCATE(N,M) Returns number after Select TRUNCATE(15.79,1)

truncating M decimal place.  15.7
Round Function

ROUND() – the round() function returns a

number rounded to a certain number of
decimal places.
Syntax – ROUND(column_name,decimals)
column_name -Required. The field to
round.decimals –Required, Specifies the
number of decimals to be returned.
Decimal places position value is rounded
to next integer ,if its next right side
number is>=5
Default decimal place is 0 position if we
not specify
String Functions
Name Purpose Example
CONCAT(str1,str2) Returns concatenated string i.e. Select CONCAT(Name, City)
str1+str2. from Student;
LOWER(str) / Returns the given string in lower Select LOWER(‘ABC’);  abc
LCASE(str) case.

UPPER(str) / Returns the given String in upper Select UPPER(‘abc’);  ABC

UCASE(str) case.

LTRIM(str) Removes Leading/Trailing/both Select TRIM(‘ ABC ‘);

RTRIM(str) spaces from given string.  ‘ABC’
TRIM(str)
LEFT(str, N) Returns the (N) characters from Select LEFT(‘Computer’,4);
RIGHT(str,N left/right from the given string.  Comp
)
SUBSTR(str,P,[N]) / Returns the substring for given Select
MID (str,P,N) position(P) and length (N). If M is (- SUBSTR(‘Computer’,3,2);
ve) then backward position counted.  mp
INSTR(str1,str2) Returns the index of first occurrence Select INSTR(‘Common’, ’m’);
of str2 in str1. 3
LENGTH(str) Returns the length of given string Select LENGTH(‘Common’);
6
CHAR(value1, Returns the character for each integer Select CHAR(70,65,67,69)
value2) passed  FACE
REPEAT The REPEAT() function repeats a string as many times as
specified.
REVERSE SELECT REVERSE(“Informatics");
The REVERSE() function reverses a string and returns the
result.
INSERT The INSERT() function inserts a string within a string at
the specified position and for a certain number of
characters.
RPAD Pad the string from right side
Space(N) SELECT SPACE(10);
The SPACE() function returns a string of the specified
number of space characters.
INITCAP() Returns the first letter in upper case
Date & Time Functions
Name Purpose Example
CURDATE() / Returns the current date in Select CURDATE();
CURRENT_DATE() YYYY-MM-DD format.  2013-10-02

NOW() Returns the current date & Select NOW();

Time as YYYY-MM-DD  2013-10-02 11:30:02
HH:MM:SS
SYSDATE() Returns the current date & Select SYSDATE();
Time as YYYY-MM-DD  2013-10-02 11:30:10
HH:MM:SS
DATE() Returns the date part of a date- Select DATE(SYSDATE());
time expression.  2013-10-02

MONTH() Returns the Month/Year from Select MONTH(‘2012-10-02’);

YEAR() given date argument.  10

DAYNAME() Returns the name of the Select DAYNAME(CURDATE());

weekday  SUNDAY

DAYOFMONTH() Returns the day of month Select

(1-31). DAYOFMONTH(CURDATE())
;
DAYOFWEEK() Returns the day of week (1-7). Select DAYOFWEEK(CURDATE());

DAYOFYEAR() Returns the day of year(1-366). Select DAYOFYEAR(CURDATE());

MONTHNAME() Returns the name of the month Select MONTHNAME(date)

Function Description
CURDATE Returns the current date.
DATEDIFF Calculates the number of days between two DATE values.
DAY Gets the day of the month of a specified date.
DATE_ADD Adds a time value to date value.
DATE_SUB Subtracts a time value from a date value.
DATE_FORMAT Formats a date value based on a specified date format.
DAYNAME Gets the name of a weekday for a specified date.
DAYOFWEEK Returns the weekday index for a date.
EXTRACT Extracts a part of a date.
LAST_DAY Returns the last day of the month of a specified date
NOW Returns the current date and time at which the statement executed.

MONTH Returns an integer that represents a month of a specified date.

STR_TO_DATE Converts a string into a date and time value based on a specified
format.
SYSDATE Returns the current date.
TIMEDIFF Calculates the difference between two TIME or DATETIME values.

TIMESTAMPDIFF Calculates the difference between two DATE or DATETIME values.

WEEK Returns a week number of a date.

WEEKDAY Returns a weekday index for a date.
YEAR Return the year for a specified date
Sysdate() and now()
Modifying Table Structure
You can alter (modify) the structure of existing table by the
using ALTER TABLE…. Command of MySQL.
You can do the following with the help of ALTER TABLE..
Command.
 Add a new Column or Constraints
 Modifying existing column (name, data type, size
etc.)
 Delete an existing column or Constraints
 Changing Column Name
ALTER TABLE <Table Name>
ADD|MODIFY|DROP|
CHANGE <Column
Definition(s)>
You can add/Delete/Modify multiple columns with single ALTER
Command.
Quick Revision

1. A _________ is pre-defined set of commands that is used to perform a specific task and return values.
2. The values that are provided to function is called __________ or __________.
3. The ___________ functions allows to manipulate the text from table or string passed as parameter to
the function.
4. The ___________ function returns the character for the passed integer number.
5. To display a specific word from a text, __________ function is used.
6. The ____________ function removes leading extra spaced from the text.
7. To know the length of specified text, the __________ function is used.
8. The ___________ function accepts numeric values and returns numerical values as well.
9. The ___________ function returns remainder of specified two numbers.
10.A _________ MySQL function returns square of the given number.
11.Mr. Kalpesh wants to ignore the fractional part of the number. A ___________ function will help him to
serve his purpose.
12.To know the square root of the given number in MySQL ___________ function will be used.
Answers

1.function
2.parameters, arguments
3.text/string
4.char()
5.substr() or substring() or mid()
6.ltrim()
7.length()
8.Numeric
9.mod()
10.pow()/power()
11.round()
12.sqrt()
13.truncate()
Aggregate Functions
Name Purpose Example
SUM() Returns the sum of given Select SUM(Pay) from Emp;
column. Select Sum(Pay),
Sum(Net) from Emp;
MIN() Returns the minimum value Select MIN(Pay) from Emp;
in the given column.
MAX() Returns the maximum value Select MAX(Pay) from Emp;
in the given column.
AVG() Returns the Average value of Select AVG(Pay) from Emp;
the given column.
COUNT() Returns the total number of Select COUNT(Name) from Emp;
values/ records in given Select COUNT(*) from Emp;
column.

Aggregate Functions should not be used with other columns which may
have multiple values in the table. The following query is illogical and
wrong. Why? Think yourself….
Select sum(pay), name from Employee;
The MySQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows,
like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
If you want to total salary for all employees then we used
the SUM function and you will get a sum of the total salary.

Select sum(Salary) from Employee;

Now you have also used the SUM function and using GROUP BY then you
will get the department-wise total salary.
You can see all D1 and D2 columns combine in one row.

Select Dept_ID,Sum(Salary) from Employee

Group BY Dept_ID;
Activity

List the number of customers in each country.

List the number of customers in each country,

ordered by the country with the most customers
first.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

COUNT(CustomerID) Country
3 Argentina
2 Austria
2 Belgium
9 Brazil
3 Canada
2 Denmark
2 Finland
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

COUNT(CustomerID) Country
13 USA
11 Germany
11 France
9 Brazil
7 UK
5 Mexico
5 Spain
MySQL HAVING Clause

The HAVING clause was added to SQL because the WHERE

keyword cannot be used with aggregate functions.

HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Lists the number of customers in each country. Only
include countries with more than 5 customers:

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
A) Calculate and display the amount to be paid each month (in multiples of 1000) which is to be calculated
after dividing the FinalPrice of the car into 10 instalments.

B) After dividing the amount into EMIs, find out the remaining amount to be paid immediately, by performing
modular division. Following SQL query can be used to solve the above-mentioned problems.
Items_Ordered

customerid order_date item quantity price

10330 30-Jun-1999 Pogo stick 1 28.00
10101 30-Jun-1999 Raft 1 58.00
10298 01-Jul-1999 Skateboard 1 33.00
10101 01-Jul-1999 Life Vest 4 125.00
10299 06-Jul-1999 Parachute 1 1250.00

Customers

customerid firstname lastname city state

10101 John Gray Lynden Washington

10298 Leroy Brown Pinetop Arizona
10299 Elroy Keller Snoqualmie Washington
10315 Lisa Jones Oshkosh Wisconsin
How many people are in each unique state in the customers table? Select
the state and display the number of people in each. Hint: count is used
to count rows in a column, sum works on numeric data only.

From the items_ordered table, select the item, maximum price, and
minimum price for each specific item in the table. Hint: The items will
need to be broken up into separate groups.

How many orders did each customer make? Use the items_ordered
table. Select the customerid, number of orders they made, and the sum
of their orders. Click the Group By answers link below if you have any
problems.
SELECT state, count(state)
FROM customers
GROUP BY state;

SELECT item, max(price), min(price)

FROM items_ordered
GROUP BY item;

SELECT customerid,count(customerid), sum(price)

FROM items_ordered
GROUP BY customerid;
How many people are in each unique state in the customers table that
have more than one person in the state? Select the state and display the
number of how many people are in each if it’s greater than 1.
From the items_ordered table, select the item, maximum price, and
minimum price for each specific item in the table. Only display the
results if the maximum price for one of the items is greater than
190.00.
How many orders did each customer make? Use the items_ordered
table. Select the customerid, number of orders they made, and the sum
of their orders if they purchased more than 1 item.
SELECT state, count(state) FROM
customers GROUP BY state HAVING
count(state) > 1;

SELECT item, max(price), min(price)

FROM items_ordered GROUP BY item
HAVING max(price) > 190.00;

SELECT customerid, count(customerid),

sum(price) FROM items_ordered GROUP BY
customerid HAVING count(customerid) > 1;
Display the number of cars purchased by each customer from the SALE table.

Display the customer Id and number of cars purchased if the customer purchased more
than 1 car from SALE table

Display the number of people in each category of payment mode from the table SALE

Display the PaymentMode and number of payments made using that mode more than once.
We can perform certain operations on relations like

Union, Intersection, and Set Difference to merge

the tuples of two tables.
Operations
These three operations are binary
on Relations
operations as they work upon two tables.

these operations can only be applied if both the

relations have the same number of attributes

and corresponding attributes in both tables have

the same domain.
UNION (U)

This operation is used to

combine the selected rows of two
tables at a time.
 If some rows are the same in
both the tables, then the result
of the Union operation will show
those rows only once.
 Figure 1.3 shows union of two
sets
If we need the list of students participating in either of events, then we have to apply UNION operation
(represented by symbol U) on relations DANCE and MUSIC.
The output of UNION operation is shown in Table 1.12.
INTERSECT (∩)

Intersect operation is used to get the common tuples from two tables and is represented by the symbol ∩.
Figure 1.4 shows intersection of two sets

Music Dance Figure 1.4: Intersection of two sets Suppose we have to display the list of students who are
participating in both the events (DANCE and MUSIC), then intersection operation is to be applied on these two
tables.
The output of INTERSECT operation is shown in Table 1.13. Table 1.13 DANCE ∩ MUSI
MINUS (-)
This operation is used to get tuples/rows which are in the first table but not in the second table, and
the operation is represented by the symbol - (minus). Figure 1.5 shows minus operation (also called set
difference) between two sets

Suppose, we want the list of students who

are only participating in MUSIC and not in
DANCE event. Then, we will use the MINUS
operation, whose output is given
SQL Joins

Cartesian Product

Inner Join

Right Outer Join

Self Join

Natural Join

Non-Equal Join
Cartesian Product
(Unrestricted Join)

Cartesian product operation combines tuples

from two relations. It results in all pairs of
rows from the two input relations, regardless
of whether or not they have the same values
on common attributes
Display all possible
combinations of tuples
of relations DANCE
and MUSIC
mysql> SELECT * FROM
DANCE, MUSIC

Mysql>SELECT * FROM
MUSIC CROSS JOIN
DANCE
Select * from Student Cross Join Games;
Student Games
Roll No Name Game no Game
1 Rohan 10 Football
2 Jay 11 Tennis
3 Tina

Roll No Name Game no Game

1 Rohan 10 Football
1 Rohan 11 Tennis
2 Jay 10 Football
2 Jay 11 Tennis
3 Tina 10 Football
3 Tina 11 Tennis
JOIN on two tables

• JOIN operation combines tuples from two tables on

specified conditions.
• This is unlike cartesian product, which make all possible
combinations of tuples.
• While using the JOIN clause of SQL, we specify
conditions on the related attributes of two tables within
the FROM clause.
• Usually, such an attribute is the primary key in one table
and foreign key in another table.
MY SQL Joins

MySQL JOINS are used with SELECT statement. It is used to

retrieve data from multiple tables. It is performed whenever
you need to fetch records from two or more tables.

There are three types of MySQL joins:

MySQL INNER JOIN (or sometimes called simple join)

MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
MySQL Inner JOIN (Simple Join)
The MySQL INNER JOIN is used to return all rows from multiple tables where the join condition is
satisfied. It is the most common type of join.
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
SELECT officers.officer_name, officers.address, students.course
_name
FROM officers
INNER JOIN students
ON officers.officer_id = students.student_id;
MySQL Left Outer Join

The LEFT OUTER JOIN returns all rows from

the left hand table specified in the ON condition
and only those rows from the other table where
the join condition is fulfilled.

Syntax:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
SELECT officers.officer_name, officers.address, students.course_name
FROM officers
LEFT JOIN students
ON officers.officer_id = students.student_id;
MySQL Right Outer Join

 The MySQL Right Outer Join returns all rows from the RIGHT-hand table specified in
the ON condition and only those rows from the other table where he join condition is
fulfilled.
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;