Database Query

using SQL
 Database

 A Database may be defined as a collection

of interrelated data stored together to
serve multiple applications.
What is SQL
 SQL stands for Structured Query Language
 SQL lets you access and manipulate
database
 SQL became a standard of the American
National Standards Institute (ANSI) in
1986, and of the International
Organization (ISO) in 1987.
What can SQL do?
 SQL can do the followings:-

1) Retrieve data from a database

2) Insert records in to a database
3) Update records in a database
4) Delete records from a database
5) Create new Databases and etc…
SQL Terminology
 SQL (Structure Query Language)
is used for querying upon relational database
 DDL (Data Definition Language)
is used for creation of tables
 DML (Data Manipulation Language)
is used for manipulations (addition, deletion,
modification)
SQL Functions

A function is a special type of

predefined command set that
performs some operation and
returns a single value
Functions are Classified into
2 types
1. Single row function (Scalar function)
“Single row function operates on a single
value to return a single value.”
It is divided into:
1. Text Functions
2. Math Functions
3. Date/Time Functions
1. Text functions

 UCASE() / UPPER()
 LCASE() / LOWER()
 MID() / SUBSTRING() / SUBSTR()
 LENGTH()
 LEFT() ,RIGHT()
 INSTR()
 LTRIM(),RTRIM()
 TRIM()
 UCASE() / UPPER()
it converts to upper case
Examples:
1. SELECT UPPER(„Large‟);
2. SELECT UCASE(„Large‟);
 LOWER() / LCASE()
it converts to LOWER case
Examples:
1. SELECT LOWER(„LOWER‟);
2. SELECT LCASE(„LOWER‟);
 MID() / SUBSTRING() / SUBSTR()
Returns a substring starting from the
specified position.
Examples
1. SELECT
SUBSTRING(„QUADRATICALLY‟,5,6);
2. SELECT SUBSTR(„QUADRATICALLY‟,5,6);
3. SELECT MID(„QUADRATICALLY‟,5,6);
 LENGTH()
Returns the length of the string in bytes
Example:
1.SELECT LENGTH(„CANDLE‟);
 LEFT()
Returns the LEFT most number of characters as
specified
Example:
1. SELECT LEFT(„USS/234/78‟,3);
 RIGHT()
Returns the RIGHT most number of characters as
specified
Example:
1. SELECT RIGHT(„USS/234/78‟,2);
 INSTR()
returns the index of the first occurrence of
substring
Example:
1.SELECT INSTR(„CORPORATE FLOOR‟,‟OR‟);
 LTRIM()
removes leading spaces
Example:
SELECT LTRIM(„ RDBMS MYSQL‟);
 RTRIM
removes trailing spaces
Example:
SELECT RTRIM(„ RDBMS MYSQL „);
1. MATH FUNCTIONS/
MATHEMATICAL/NUMERIC
FUNCTIONS
 Mathematical functions perform
mathematical operations on numeric values.
 The most commonly used mathematical
functions are:-
 POW()/POWER(), MOD(), ROUND().
a) POW()/ POWER()
 It returns the argument raised to the
specified power.
 E.g.:- select power(2,2); 4
 select power(6,3); 72
 select power(2,-2); 0.25
b. ROUND()
 ROUND(x) rounds the argument to the 0 decimal
place where as ROUND(x,d) rounds the argument
to d decimal places. The round() function returns
a number rounded to a certain number of
decimal places.
 Syntax:- SELECT ROUND(x,d)
 x-Required (It is the field or value to be rounded
off.)
 Y-Required(Specifies the number of decimals to
be returned.)
Values to be rounded off
454.352
-2 -1 0 1 2 3 Decimal places
c. MOD()
 The MOD() function returns the modulus (reminder) of one
number divided by another.
 Syntax:- select MOD(dividend, divisor)
 Dividend – Is a number or a numeric expression to divide.
 Divisor- Is a number or a numeric expression by which the dividend is
to be divided.

 Ex:-select mod(11,3); 2

 select mod(10.5,3); 1.5

d. TRUNCATE
Truncates the argument to specified number of decimal places.

Syntax:- select truncate(x,d)

x-required(it is the field or value to be truncated.)

d-required(Specifies the number of decimals to be returned.)

The situations given below exhibit the behavior of truncate() for

different sets of values for x and d.
 If d is 0 , it removes all the decimal values and returns only the integer.
 If d is a positive number , the function truncates the number x to d digits
right to the decimal points.
 If d is a negative number , the function truncates the number x to d digits
left of the decimal point.
1. select truncate(7.29,0) 7
2. select truncate(27.59,1) 27.5
3. select truncate(389.23,-2) 300
Date / Time function

1. CURDATE()
Returns the current system date.
e.g:- select curdate(); „2023-03-12‟

2. NOW()
Returns the current date and time.
e.g:- select now(); „2023-03-12 13:58:11‟

3. sysdate()
Returns the time at which the function executes.
E,g:-:- select sysdate(); „2023-03-12 13:59:23‟
Date / Time function
4. date()
It extracts the date part of a date or date time expression.
e.g:- select date(„2021-02-03 01:02:03‟);
„2021-02-03 „
5. month()
Returns the month from the date passed.
e.g:- select month(„2021-02-03‟); 2

6. year()
Returns the year from the inputted date.
e,.g:- select year(„2021-02-03‟); 2021

7. dayname()
Returns the name of the weekday.
e.g:- select dayname(„2023-03-12‟); Sunday
Date / Time function
8. dayofmonth()
Returns the day of the month(0-31)
e.g:- select dayofmonth(„2021-02-03‟); 3

9. monthname()
Returns the name of the month (jan,feb,mar…)
e.g:- select monthname(„2021-02-03‟); february

10. dayofyear()
Returns the day of the year(1-366)
e.g:- select dayofyear(„2021-02-03‟); 34
Aggregate functions
Aggregate functions summarize the result of a query and return a single value
calculated from values in a column instead of providing listing of all the rows.

Syntax:- select<function>(column_name) from <table_name>

While working with standard library function, the following

points are kept in your mind.
The name of the column on which the function is to be
executed must be enclosed with in the parenthesis.
Only one column can be specified with in one set of
parenthesis.
To use more than one function with in the same select
statement , functions are written by one after the another
separated by comma.
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.
SQL Aggregate Functions

SUM()
This function is used to find the total value of a particular column.
e.g:- select sum(Marks ) from student;
e.g:- select sum(marks) from student where marks>80;

AVG()
This function is used to find the average value of a particular column.
e.g:- select avg(marks) from student;
e.g:- select avg(marks) from student where DOB<„1999/04/12‟;

MAX()
This function is used to find the maximum value of a particular column.
e.g:-select max(marks) from student where Stream=„Science‟;
e.g:- select min(Marks),max(M arks) from student;

MIN()
Is used to find the minimum value of a particular column.
SQL Aggregate Functions

COUNT()
This function is used to find the number of values
(i.e., number of rows) of a particular column.it does
not count null values in that column.

e.g:- select count(marks) from student;

COUNT(*)
This function returns the total number of records of
rows from the table column, it also count null values.

e.g:-Select count(*) from student;

SQL Aggregate Functions

COUNT-DISTINCT
The keywords distinct and count can be used together
to count the number of records excluding duplicate
values.
e.g:- select distinct stream from student;
e.g:- select count(distinct Stream) from student;
1. To show all the information about the student Ankit Sharma.
2. Display the sum of all the marks whose roll number is greater than 5.
3. Display the maximum and minimum marks from students.
4. Display all the details of students whose name ends with „a‟.
5. Display the count of marks whose name starts with „D‟.
6. Display average of marks.
7. Count distinct gender from student table.
8. Display details of students in ascending order of their marks.
9. To display details of studentsbwhose marks is in the range of 80 to 90.(both
values included)
10.Display total number of rows present in the table student;
11.Display the details of youngest student.
12.Display the name of eldest student.

13.(Predict the output)

>>> Select name from student where roll_no=5;
>>> Select count(roll_no) from student where name like „%ee%‟;
>>> select avg(marks) from student where gender=„m‟;
>>> select name from student where instr(name,sha)=0;
>>> select count(gender) from student;
>>> select count(distinct gender from student);
>>> select count(*) from student;
SORTING IN SQL– ORDER BY
The SQL order by clause is used to sort data in ascending or
descending order based on one or more columns.
This clause sorts the records in ascending order(ASC) by
default.
In order to sort the records in descending order (DESC)
keyword is used.
Sorting using order by clause is done on multiple columns,
separated by comma.

e.g.:- select rollno,name,marks from student order by name;

The above query display the roll number, name and marks of
students on the basis of their marks in ascending order.
SORTING IN SQL– ORDER BY

The SQL order by clause is used to sort data in ascending or

descending order based on one or more columns.

Sorting using order by clause is done on multiple columns,

separated by comma.

e.g.:- select rollno,name,marks from student order by name;

The above query display the roll number, name and marks of
students on the basis of their marks in ascending order.
SORTING IN SQL– ORDER BY

Sorting data on multiple columns

e.g.:- to display the roll number, name and marks of all the
students in descending order of their marks and ascending
order of their names.

>>> select roll number,name,marks from student order by

marks desc,name;
SORTING IN SQL– ORDER BY

Sorting on column ALIAS

If a column alias is defined for a column, it can be used for
displaying rows in ascending or descending order using order by
clause.

e.g.:- select rollno,Name Marks as “Marks_Obtained” from

student order by Marks_Obtained;

Alias name/naming
If we need to fetch a group of rows on the
basis of common values in a column, this
can be done by using group by clause.
It groups the rows together that contain the
same value in a specified column.
We can use the aggregate
functions(count(),max(),min(),avg(),sum())
to work on the grouped values.
Having clause in SQL is used to specify
conditions on the rows with group by clause.
 SQL Joins
 Join clause is used to combine rows from two or more
tables based on the common field between them.
 While querying for a join, more than one table is
considered in from clause..
 The process/function of combining data from multiple
tables called a Join.
 Types of Join
 Cartesian Product(Cross Product)
 Equi Join
 Inner Join
 Outer Join
 Self Join
 Non-Equi Join
 Natural Join
 Equi Join

 It is the simplest SQL Join method.

 Join the condition that uses the equal to sign(=) as
comparison operator for defining a relationship between
two tables on the basis of a common field,
i.e, Primary key and Foreign key

Suytax:- select <column 1>, <column 2>….. From <table1>,<table 2> where
<table1.Primary key column> = <table2.Foreign key column>
mysql>
create table teacher(t_id int primary key,T_name
varchar(20),stud_id integer,foreign key(stud_id) references
stud(stud_id));
Query OK, 0 rows affected (1.07 sec)

mysql> insert into teacher values('200','Anu',103);

Query OK, 1 row affected (0.32 sec)
mysql> insert into teacher values('300','joy',103);
Query OK, 1 row affected (0.15 sec)

mysql> insert into teacher values('202','Bindu',10103);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`pri`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`stud_id`)
REFERENCES `stud` (`stud_id`))
mysql> select * from stud;
+---------+---------------+ Alias naming of
| stud_id | stud_name | teacher table
+---------+---------------+
| 101 | Ankur Mehta | Alias
| 102 | Deepika Gupta | name of
| 103 | Arnav Bansal | stud table
+---------+---------------+ mysql>
3 rows in set (0.00 sec) select
mysql> select * from teacher; s.stud_id,s.stud_name,t.t_id,t.T_name
+------+--------+---------+ from stud s,teacher t where
| t_id | T_name | stud_id | s.stud_id=t.stud_id;
+------+--------+---------+
| 200 | Anu | 103 |
Equi
| 300 | joy | 103 | join
| 400 | joy | 102 |
+------+--------+---------+ +---------+---------------+------+--------+
3 rows in set (0.00 sec) | stud_id | stud_name | t_id | T_name |
+---------+---------------+------+--------+
| 102 | Deepika Gupta | 400 | joy |
| 103 | Arnav Bansal | 200 | Anu |
| 103 | Arnav Bansal | 300 | joy |
+---------+---------------+------+--------+
3 rows in set (0.00 sec)