SQL

DISTRIBUTION OF MARKS AND PERIODS

 BIG PICTURE
• FUNCTIONS IN MYSQL
• MATHEMATICAL/NUMERIC FUNCTIONS
• DATE/TIME FUNCTIONS
• AGGREGATE FUNCTIONS IN SQL
• SORTING IN SQL – ORDER BY
• GROUP BY
• SET OPERATORS ON RELATIONS
Introductio
n
Database :
• A database is an organized collection of data.
• Data is organized into rows, columns and
tables.

Database Management System (DBMS):

• An application software that enables users to
create, manage and maintain the databases.
• Examples of popular DBMSs are :
MySQL, Microsoft Access, Oracle, Microsoft
SQL Server, DB2 and Sybase.
 RDBMS
Relational Database:
• Once the tables have been set up, a
relationship can be created to link them
together. A database that stores data in
separate tables that are related through the
use of a common column is called a Relational
database.
• A DBMS used to manage Relational Databases
is called an RDBMS (Relational Data Base
Management System).
• Some popular RDBMS software available are:
Oracle, MySQL, Sybase, Ingress
 Tables
A table is a 2 dimensional representation of data
using rows & columns.
The horizontal subset of the Table is known as a
Row/Tuple/Record.
The vertical subset of the Table is known as a
Column/Attribute / field.

Degree and
Cardinality
Degree : Number of attributes/ columns in a
relation is the degree of that relation
Cardinality : Number of tuples/ rows in a relation
is the cardinality of that relation.
 SQL
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
We can retrieve data from a database , insert , update and
delete records in a database and can create new databases
and tables.
MySQL is an open source, free and powerful Relational
Database Management System (DBMS) that uses SQL.
Key features of MySQL
Speed
Free of Cost
Portability - It can be installed and run on different types of
Hardware and Operating System platform.
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 Keys
Primary Key -An attribute or column that uniquely
identifies each record in a table. It does not have
duplicate values or null values.
Candidate Key - An attribute or column that has the
ability to become a primary key. A Primary Key is one of
the candidate keys. A table may have more than one
candidate keys but definitely has one and only one
primary key.
Alternate Key - A Candidate Key that is not a Primary key
is called Alternate key.
Foreign Key – It is a column that creates a relationship
between two tables. It is a field in the table that is the
primary key of another table.
 Categories of SQL
commands
1. DDL [ Data Definition Language ] Commands
2. DML [ Data Manipulation Language ]
commands
DDL Commands – It is used for creation or
deletion of database tables.
Examples are : CREATE DATABASE, CREATE
TABLE, ALTER TABLE, DROP TABLE
DML Commands – It consists of the query and
update commands.
Examples are : SELECT, UPDATE, DELETE,
INSERT INTO
Categories of SQL
commands
 MySQL Data
Types
It indicates the type of data that you are storing in a
given table column.
Class Type
Text char[length] OR varchar[length]
Values must be enclosed in single quotes or
double quotes.
Numeric INT OR INTEGER [width upto 11 dig-
its ]
DECIMAL (size,d)
Date DATE
Time TIME
 Database Handling commands in
MySQL
Creating a Database.
mysql> CREATE DATABASE School;
Opening a database
mysql> USE school ;
To get a list of the available databases
mysql> SHOW DATABASES;
Deleting a Database
mysql> DROP DATABASE School;
To show the name of currently open
database
Select database();
 Table related commands in MySQL

Creating a Table.
CREATE TABLE < Table Name>
(<Col name1> <data
type>[(size)],….);
Eg: - mysql> CREATE TABLE Learner(RollNo
INTEGER, Name VARCHAR(25) );
Getting list of tables
mysql> SHOW TABLES;
Deleting a Table
mysql> DROP TABLE Student;
 Table related commands in MySQL

Inserting values to a table-

INSERT INTO <Table Name> VALUES (value1,
value2, ….);
Eg: -mysql> INSERT INTO Learner VALUES
(14,'Aruna Asaf Ali');
To view a table structure-
DESCRIBE <table name>;
OR
DESC <table name>;
 EXERCISE
• Suppose your school management has decided to conduct cricket matches between students of class XI and Class XII. Students of
each class are asked to join any one of the four teams — Team Titan, Team Rockers, Team Magnet and Team Hurricane. During
summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:

1. Create a database “Sports”.

2. Create a table “TEAM” with following considerations:
i) It should have a column TeamID for storing an integer value between 1 to 9,
which refers to unique identification of a team.
ii) Each TeamID should have its associated name (TeamName), which should be a
string of length not less than 10 characters.
3. Show the structure of the table TEAM using SQL command.
4. As per the preferences of the students four teams were formed as given below.
Insert these four rows in TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Hurricane)
5. Show the contents of the table TEAM.
 To retrieve the selected columns/attributes
from a table
SELECT * FROM table;
SELECT column from table;
Changing Structure of a
table
• To add a new column :
ALTER TABLE <table_name> ADD
<column>[datatype];
mysql> ALTER TABLE Student ADD Games
VARCHAR(20);
• To change the structure of a column:
ALTER TABLE <table_name>
MODIFY<column>[datatype];
mysql> ALTER TABLE Student MODIFY games
INTEGER;
• To delete a column:
ALTER TABLE <table_name> DROP<column>;
 To eliminate the duplicate values in the
result.
Select DISTINCT marks from Student;

To filter records based on a specified

criteria.
Select <column> from <table>
where<condition>;
Example:
Select name, marks from student where
marks > 80;

to modify the existing data in the table.

UPDATE table SET column=value WHERE
condition;
 To delete one or more rows from the table-
DELETE FROM tablename WHERE
condition;
Example :
Delete from emp where
empno=100;
To remove all the rows of the table
use,
DELETE from emp;

To sort the results-

select <column>where <condition>
order by column;
Eg:- Select * from student order by
marks;
 OPERATORS USED IN
MySQL
ARITHMETIC RELATIONAL LOGICAL
OPERATORS OPERATORS OPERATORS

+ > < AND

- <>
OR
*
/ >=
<= NOT
%
 BETWEEN OPERATOR -
The BETWEEN operator is used to define a
range of values within which the column
values must fall to satisfy the condition.
select name, rollno from students where
marks between 80 and 90;
IN OPERATOR -
The IN operator is used to select a value that
matches any value in a list.
select name, rollno from students where
marks IN (55, 75, 67 );
 Functions
A function is a predefined command set that
performs some operation and returns the single
value.
The values that are passed to functions are known as
Arguments or Parameters.
Types of Functions-
 Single row functions
 Multiple / Aggregate functions

Single row:
These operate on a single value to return a single
value.
Eg:- Round (x)
Classified as – NUMERIC , STRING , DATE & TIME
Multiple / Aggregate row:
Operates on a set of rows to return a single value.
 Numeric function

Numeric functions accept numeric input and

return numeric values.
The numeric functions are:
POWER (X,Y) or POW (X,Y)
ROUND(X,D) or ROUND (X)
TRUNCATE (X,D)

select POW(2,3); O/p : 8

select POW(4,2); O/p : 16
SELECT ROUND(125.315, 2);
O/p :125.32
Select TRUNCATE (234.56789,2) returns
'234.56'
 MOD
FUNCTION

The MOD() function returns the remainder of one number divided

by another.
Syntax- Mod (Dividend, divisor)

SELECT MOD(11, 3);

Output – 2

SELECT MOD( 13,0 );

Output - NULL
 String function

These functions:
Are used to extract, change, format string / char data
Will accept character values as input and give the o/p
either as character or numeric.
Length (string)
Concat
INSTR(str,substr)
LOWER (str)/ LCASE (str)
UPPER(str)/ UCASE (str)
LEFT (str, n)
RIGHT (str, n)
LTRIM (str) , RTRIM(str) , TRIM (str)
SUBSTRING(str,m,n)/ MID/ SUBSTR
ASCII(str)
 String function

Select length (“I love my country”); 17

SELECT CONCAT('w3resource','.','com'); --
w3resource.com
SELECT INSTR('Tech on the net', 'e'); -- 2
Select LOWER (‘INFORMATICS’); -- ‘informatics’
Select UPPER(‘Informatics’); -- ‘INFORMATICS’
Select LEFT(‘COMPUTER’,7); -- COMPUTE
Select RIGHT(‘MySQL’,2); --QL
SELECT LTRIM(‘ Tech on the net'); --'Tech on the
net'
SELECT RTRIM('tech.com '); --'tech.com‘
SELECT SUBSTRING('Quadratically',5); -> 'ratically‘
SELECT ASCII('2'); -> 50
 Date & Time function
CURDATE ()
NOW ()
SYSDATE ()
DATE(expr)
MONTH (date)
YEAR(date)
DAYNAME(date)
DAYOFMONTH(date)
DAYOFWEEK(date)
DAYOFYEAR(date)
Date & Time function

SELECT CURDATE();
SELECT NOW();
SELECT SYSDATE);
SELECT DATE('2003-12-31 01:02:03');
SELECT MONTH('2014-01-28');
SELECT YEAR('2014-01-28');
SELECT DAYNAME('2014-01-27');
SELECT DAYOFMONTH('2014-01-28');
SELECT DAYOFWEEK('2014-01-27);
SELECT DAYOFYEAR('2014-01-01');
Date & Time function

SELECT CURDATE(); -> '2020-03-25‘

SELECT NOW();
SELECT SYSDATE);
SELECT DATE('2003-12-31 01:02:03'); -
> '2003-12-31‘
SELECT MONTH('2014-01-28'); -- 1
SELECT YEAR('2014-01-28'); --
2014
SELECT DAYNAME('2014-01-27'); --
'Monday‘
SELECT DAYOFMONTH('2014-01-28'); -- 28
SELECT DAYOFWEEK('2014-01-27'); --2
SELECT DAYOFYEAR('2014-01-01'); -- 1
Date & Time function

SELECT CURDATE(); -> '2020-03-25‘

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();
SELECT DATE('2003-12-31 01:02:03'); -
> '2003-12-31‘
SELECT MONTH('2014-01-28'); -- 1
SELECT YEAR('2014-01-28'); --
2014
SELECT DAYNAME('2014-01-27'); --
'Monday‘
SELECT DAYOFMONTH('2014-01-28'); -- 28
SELECT DAYOFWEEK('2014-01-27'); --2
SELECT DAYOFYEAR('2014-01-01'); -- 1
 Multiple / Aggregate row Functions
• Operates on a set of rows to return a single
value.
• We apply the SELECT query on a group of
records rather than the entire table.
S.No Name of the Purpose
function
1. MAX() Returns the MAXIMUM of the values
2. MIN() Returns the MINIMUM of the values
3. AVG( ) Returns the AVERAGE of the values
4. SUM( ) Returns the SUM of the values
5. COUNT( ) Returns the COUNT of the number of values
 Multiple / Aggregate row Functions

• SQL provides a large collection of inbuilt

functions , also called library functions, that
can be used directly with SQL statements
for performing calculations on data. These
functions are called Aggregate functions.
• Aggregate functions helps to summarize
large volume of data.
• These functions results in a single value for
an entire group or table.

SYNTAX :
SELECT <function name>(column name) FROM
<table name>;
 Rules for Aggregate row Functions

• The name of the column on which function

has to be executed must enclosed within
parenthesis.
• Only one column can be specified within
one set of parenthesis.
• To use more than one function with the
same SELECT statement, functions are
written one after another separated by
comma.
Eg: SELECT AVG(marks), SUM(marks) FROM
student;

• if the same function is required to be

used for more than one column, then again
the function name has to be repeated in
the SELECT statement.
 AGGREGATE FUNCTIONS
Consider the table EMP having the
following records:
EMP
CODE NAME SAL
E1 Ram 4000
Kumar
E2 Suchitra 4500
E3 Yogendra 3000
E4 Sushil 3500
E5 Rajesh 4000
mysql> SELECT SUM(SAL) 19000
FROM EMP;
3000
mysql> SELECT MIN(SAL) 4500
FROM EMP;
3800
mysql> SELECT MAX(SAL)
FROM EMP;
 AGGREGATE FUNCTIONS AND NULL
VALUES
Consider the table EMP having the
following records:
EMP1
CODE NAME SAL Aggregate functions
ignores NULL values
E1 Ram NULL i.e. NULL values does
Kumar not play any role in
calculations.
E2 Suchitra 4500
E3 Yogendra NULL
E4 Sushil 3500
E5 Rajesh 4000
mysql> SELECT SUM(SAL) FROM EMP;

mysql> SELECT MIN(SAL) FROM EMP;

mysql> SELECT MAX(SAL) FROM EMP;

mysql> SELECT AVG(SAL) FROM EMP;

 DIFFERENCE BETWEEN COUNT(*) AND COUNT(COLUMN
NAME)
EMP1
CODE NAME SAL
E1 Ram Kumar NULL
E2 Suchitra 4500
E3 Yogendra NULL
E4 Sushil 3500
E5 Rajesh 4000

mysql> SELECT COUNT(*) FROM EMP; 5

mysql> SELECT COUNT(SAL) FROM 3
EMP;
NOTE  COUNT(column_name) returns the number of
non- NULL values in that column.
 If the argument is a *, then COUNT( ) counts
the total number of rows including null values.
 GROUP BY

The Group By clause can be used in a select

statement to collect data across multiple
records and group the results by one or
more columns.
GROUP BY clause is used in a SELECT
statement in conjunction with aggregate
functions to group the result based on
distinct values in a column.

Syntax:
SELECT <COLUMN NAME>,<AGGREGATE
FUNCTION>
FROM <TABLE NAME>
GROUP BY <COLUMN NAME>
 GROUP BY
Note:
Group By is always used in conjunction
with some aggregate function(s).
A SELECT command with GROUP BY clause
has a column name and one or more
aggregate functions which are applied on
that column and grouping is also done on
this column only.
Eg. SELECT DEPT,COUNT(*) FROM HOSPITAL
GROUP BY DEPT;
What will be the output of the above
command?
 GROUP BY

Practical applications:
The need for the following reports:
The management of the school may want to
know what is the total salary of teachers of
various departments. i.e., what is the total salary
of teachers in each department
The management may also want to know what is
the maximum, minimum salary of teachers in
each of these departments.
It may also be required to find the total number
of teachers in each department.
 Consider the table HOSPITAL:

Eg. SELECT dept, sum(charges) from HOSPITAL GROUP BY dept

having sum(charges)> 550;
Dept sum(charges)
Surgery 700
Orthopedic 400
ENT 550
Cardiology 1600
Gynaecology 500
Medicine 400
Consider the table EMP :

EMP
CODE NAME SAL DEPT

E1 Ram Kumar NULL Sales

E2 Suchitra 4500 Accounts
E3 Yogendra 2000 Sales
E4 Sushil 3500 Accounts
E5 Rajesh 7000 IT
E6 Ranbir 5000 Operations
E7 Suhas 4000 IT

Write queries :
 To display the minimum and maximum salary of
the employees department wise
 To display the total salary of employees in each
department
 To display the number of employees in each
PRACTICALS

Write the output for the queries given :

1.SELECT cust_code, SUM(order_qty) FROM
orders GROUP BY
cust_code;

2. SELECT shoe_code, SUM(order_qty) FROM

orders GROUP BY
shoe_code;
PRACTICALS
 PRACTICALS
1.

2.
Conditions on groups

Sometimes we do not use the whole

output produced by the GROUP BY
clause

Eg. SELECT type, SUM(qty) FROM shoes

GROUP BY type;
Output :
Conditions on groups
Now suppose we are interested in viewing
only those types of shoes whose total
quantity is more than 1500

Query :
SELECT type, SUM(qty) FROM shoes GROUP BY
type HAVING SUM(qty) > 1500;
The Having clause is used in combination with the
GROUP BY clause.
It can be used in a select statement to filter the
records, by specifying a condition, which a GROUP
WHERE & HAVING with GROUP
BY
Now the management of the shoe factory
may want to know what is the total
quantity of shoes, of sizes other than 6, of
various types. i.e., what is the total
quantity of shoes (of sizes other than 6) of
type School, Office, and Sports each whose
Query
total :
quantity greater than 1500.
SELECT type, SUM(qty) FROM
shoes Condition on individual rows /
Checks individual rows
where size<>6
GROUP BY type
HAVING SUM(qty) > 1500;
Condition on individual groups / checks
individual group
 WHERE Vs
HAVING
• Where clause works in respect of whole
table but Having works on Group only.
• If Where and Having both are used
then Where will be executed first.
• WHERE is used to put a condition on
individual row of a table whereas
HAVING is used to put condition on
individual group formed by GROUP BY
clause in a SELECT statement.
 Example

select class,avg(marks) from student group by class having count(*)<3;

 PRIMARY KEY

To set a primary key-

CREATE TABLE Shoes (Code CHAR(4)
PRIMARY KEY, Name VARCHAR(20), type
VARCHAR(10), size INT(2), cost
DECIMAL(6,2), margin DECIMAL(4,2), Qty
INT(4));
or as follows:
CREATE TABLE Shoes (Code CHAR(4),
Name VARCHAR(20), type VARCHAR(10),
size INT(2), cost DECIMAL(6,2), margin
DECIMAL(4,2), Qty INT(4), PRIMARY KEY
(Code));

To edit an already created column –

FOREIGN KEY

A Foreign key is a column in a table

whose value is derived from the
Primary key of some other table.
The property of a relational
database which ensures that no
entry in a foreign key column of a
table can be made unless it
matches a primary key value in the
corresponding related table is
called Referential Integrity.
This constraint requires two tables
–
 Reference table (having Primary
 FOREIGN KEY
Create table tablename(empno char(3) primary
key, deptno char(2), foreign key(deptno)
references department (deptno));
 FOREIGN KEY
Create table tablename(empno char(3) primary
key, deptno char(2), foreign key(deptno)
references department (deptno));

ALTER TABLE tablename

ADD FOREIGN KEY (column) REFERENCES
tablename(column);

ALTER TABLE tablename

DROP FOREIGN KEY columnname;
SQL JOINS
 JOIN
• An SQL join is a statement that is used to
combine data or rows from two or more tabled
based on a common field between them
• Ie: Data from one or more table in a single
table
• The main purpose is to establish a link with in
the database
• Joined by using = operator or JOIN keyword
 EQUI JOIN
• The join in which columns are compared for
equality is called EQUI JOIN.
• The column names do not need to be the
same.(values should be the same, at least one
value).
• The output table contains repeated column.
 Syntax
• select * FROM table1 JOIN table 2 ON
table1.col_name=table2.col_name;

• select * from table1, table2 where

table1.col_name=table2.col_name;
 Equi Join Example

select * from emp,e_misc_2 where emp.E_id=e_misc_2.Identity;

select*from emp JOIN e_misc_2 on emp.E_id=e_misc_2.Identity;