UNIT 4 DATABASE QUERY USING SQL

 SQL  is a unified, non-procedural language used for creating, accessing, handling and mnaging data in relational databases.

FUNCTIONS IN SQL

A function can be defined as a pre-defined set of commands that helps in performing certain operations to obtain either a single value or a result set.

SQL provides a large collection of in-built functions, also called library functions, that can be used directly with SQL statementsfor performing calculations on data.

The functions used in SQL can be categorised into two categories namely single row or scalar functions and multiple row or group or aggregate functions.

SINGLE ROW  FUNCTIONS:

Single Row Functions operate  on a single row or single value to return one value per row as the outout. they can accept one or more arguments but return only one result per row. These are  also known as Scalar Functions.  They can be used with SELECT,  WHERE and ORDER by clause.

Numeric Functions:

ROUND () :

syntax: Select Round(x,d);

i. If d is not specified:   if d is not specified then x is rouded upto 0 digits of decimal place and the result is an integer.  If the first digit adter the decimal place is 5 or >5  then the integer number is increased by 1. Default decimal place is 0 if nothing is specified.

for ex. 

Select Round(1.58) ;   gives 2

Select round(-1.23); Gives -1

ii) if d is 0: if d is 0 then x is rounded upto 0 digits of decimal place and the result is an integer.

for ex. 

Select Round(1.298 , 0);  gives 1

Select Round(-4.898,0);  gives -5

in short i) and ii) both are same.

iii) if d is a positive integer:  If d is positive integer then x is rounded upto d digits after the decimal by checking (d+1) digit. if it is 5 or >5 then the d (th) digit is increased by 1 and the result is a float value.

for ex.

Select round(3.79867,3);  gives 3.799

Select round (23.294,2); gives 23.29

Select round(3.79443,3) ; gives  3.794

iv.) if d is a negative integer:  If d is negative integer then x is rounded upto d digits before the decimal by checking the d(th) digit. if it is 5 or ?5 then the (d-1)th digit is  increased by 1, a;; training dth digits are converted to zero and the result is an integer value. So if the second aregument is negative i.e. -1,-2,-3  then it rounds off to the neared 10,100,1000.

for ex.

Select round(23.298,-1);   gives 20

Select round(36567.78,-4);    gives 40000

short cut : put 0s equivalent to d digits before the decimal places and check the dth digit before the decimal place if it is 5 or >5 then increse  d-1 digit by 1. 

STRING FUNCTIONS : SQL string functions help manipulate and format text data efficiently. They are widely used for cleaning, comparing, and extracting meaningful information from textual fields.

• Useful for handling names, addresses, and other text-based data.
• Essential for organizing, analyzing, and improving data quality.

Case Conversion and Trimming

• LOWER(): Converts a string to all lower-case characters.
• UPPER(): Converts a string to all upper-case characters.
• TRIM(): Removes leading and trailing spaces (or other specified characters) from a string.
• LTRIM(): Removes leading spaces from the left side of a string.
• RTRIM(): Removes trailing spaces from the right side of a string. 

Length and Position

• LENGTH() (or LEN() in SQL Server): Returns the number of characters in a string.
• INSTR() : Returns the position of the first occurrence of a substring within a given
•  string.
• LEFT(): Extracts a specified number of characters from the beginning (left side) of a string.
• RIGHT(): Extracts a specified number of characters from the end (right side) of a string
• SUBSTRING() (or SUBSTR())/ MID ( ): Extracts a portion of a string starting from a specified position and with a specific length.
• 
  

MySQL Date and Time Functions

MySQL provides built-in date and time functions that allow users to work efficiently with date-related data in databases. These functions help in managing timestamps, scheduling events, and performing calculations on dates, which are essential for applications that rely on time-based information. They are used to:

• Store and handle timestamps and scheduling data.
• Perform date calculations such as adding or subtracting days.
• Extract parts of a date like year, month, or day.
• Format date output for better readability.

1. NOW()

The NOW() function retrieves the current date and time in YYYY-MM-DD HH:MI:SS format.

Query:

SELECT NOW();

2. SYSDATE()

Returns the time at which the function is executed.

Ex. Select sysdate();

3. DATE()

The DATE() function extracts only the date part from a DATETIME or TIMESTAMP value, discarding the time.

4. MONTH() :  Returns the month from the given date passed as an argument to it.

Ex. Select Month('2026-03-28');

result: 03

5. YEAR(): Returns the year from the given date passed as an argument to it.

Ex. Select Month('2026-03-28');

result: 2026

6. DAYNAME() : Returns the name of the weekday.

Ex. Select dayname('2026-03-28');

result: Saturday

7.DAYOFMONTH() : Returns the day of month.

Ex. Select dayofmonth('2026-03-28');

result: 28

8.MONTHNAME():  Returns the name of the month.

Ex. Select monthname('2026-03-28');

result: March

9. DAYOFWEEK() : Returns the weekday index of the argument. Sunday is counted as 1.

Ex. Select dayofweek('2026-03-28');

result: 7

10.DAYOFYEAR() : Returns the day of the year.

Ex. Select dayofyear('2026-03-28');

result: 87

AGGREGATE / MULTIPLE ROW FUNCTIONS

Aggregate functions are used to implement calculations based upon a particular column. These functions always return a single value.

 

1. SUM(): Returns the total sum of  a numeric Column.

Ex. Select Sum(Marks) from student;

2.  AVG(): Returns the Average Marks of  a numeric Column.

Ex. Select Avg(Marks) from student;

3. MAX(): Returns the MAXIMUM VALUE  of  a numeric Column.

Ex. Select MAX(Marks) from student;

4. MIN(): Returns the MINIMUM VALUE  of  a numeric Column.

Ex. Select MAX(Marks) from student;

6. COUNT(): Returns the COUNTS  of  a numeric Column.

Ex. Select MAX(Marks) from student;

6. COUNT DISTINCT(): Returns the COUNTS   of  a numeric Column.

Ex. Select MAX(Marks) from student;

SORTING IN SQL -ORDER BY

The SQL ORDER BY clause is used to sort data in ascending or decending order based on one or more columns. 

Syntax :

SELECT <column_name> FRO <tablename> where [<condition>] ORDER BY <column name> [ASC/DESC];

Ex. 

SELECT rollno, name, marks FROM student order by name;

Sorting data on Multiple columns : 

 

Ex. 

SELECT rollno, name, marks FROM student order by marks desc, name;

Sorting data on column Alias : 

 

Ex. 

SELECT rollno, name, marks as "Final_Marks"  FROM student order by final_marks;

GROUP BY: 

Group By clause can be used in a select statement  to collect data across multiple records and group the results byone or more columns.

Ex. 

SELECT rollno, name, marks as "Final_Marks"  FROM student where marks>= 90 Group by stream;

Having Clause:

HAVING  clause used in combination with GROUP BY clause. It can be used in a SELECT statement to filter the records by specifying a condition which GROUP BY returns.

Syntax : 

SELECT <column_name> FRO <tablename> where [<condition>] ORDER BY <column name> [ASC/DESC] HAVING [<condition>];

Ex. SELECT stream, SUM (Marks) FROM  Student GROUP BY stream HAVING Max (Marks)<85;

Note:  SELECT statement can contain only those attributes which are already  present in GROUP BY clause.

AGRREGATE FUNCTIONS AND CONDITIONS ON GROUPS (HAVING CLAUSE)

SELECT job, SUM (pay) FROM EMP  GROUP BY job HAVING sum (pay)>=10000;

SELECT job, SUM (pay) FROM EMP  GROUP BY job HAVING Count (*)>=5;

SELECT job, MIN (pay), MAX (pay)  FROM EMP  GROUP BY job HAVING SUM (pay)>=10000;

SELECT job, SUM (pay) FROM EMP  WHERE city="Delhi"  GROUP BY job HAVING Count (*)>=5;

Comparing WHERE and HAVING

Some more examples

SELECT sum (salary) from Emp;

SELECT sum (Distinct salary) from Emp;
SELECT  city , sum (salary) from Emp Group By city;

SELECT  city , MAX (salary) from Emp Group By city;

SELECT  city , count (salary) from Emp Group By city;

SQL JOINS

SQL join clause is used to combine rows from two or more tables based on a common field between them

Here are the different types of JOINs in SQL:

1. Cartesian Product(Cross join)

A Cartesian Join or Cross Join returns the Cartesian product of two tables, meaning each row from the first table is combined with every row from the second table. This type of join does not require any specific condition or matching column between the two tables.

Syntax:

SELECT table1.column1 , table1.column2, table2.column1...
FROM table1
CROSS JOIN table2;

In the absence of a WHERE condition the CARTESIAN JOIN will behave like a Cartesian Product i.e., the number of rows in the result-set is the product of the number of rows of the two tables. In the presence of WHERE condition this JOIN will function like a INNER JOIN.

Consider the following two tables Student and StudentCourse:

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	Ron	Vikas Puri	XXXXXXXXXX	17
2	Evin	JanakPuri	XXXXXXXXXX	17
3	Varun	Tilak Nagar	XXXXXXXXXX	17
4	Harsh	Uttam Nagar	XXXXXXXXXX	17

In a Cartesian Join, the number of rows in the result set is equal to the product of the number of rows in both tables. For instance, if Table A has 4 rows and Table B has 4 rows, the result set will contain 16 rows (4 * 4).

Query:

SELECT Student.NAME, Student.AGE, StudentCourse.COURSE_ID
FROM Student
CROSS JOIN StudentCourse;

Output:

NAME	AGE	COURSE_ID
Ron	17	1
Ron	17	2
Ron	17	2
Ron	17	3
Evin	17	1
Evin	17	2
Evin	17	2
Evin	17	3
Varun	17	1
Varun	20	2
Varun	20	2
Varun	20	3
Harsh	18	1
Harsh	18	2
Harsh	18	2
Harsh	18	3

Explanation:

In this example, each row from the Student table is joined with every row from the StudentCourse table, resulting in 16 rows (4 students * 4 courses). This is the Cartesian product of the two tables.

2. Equi Join:

An Equi Join in SQL is a type of join that combines rows from two or more tables based on a common column or set of columns, using the equality operator = to compare column values

syntax:

SELECT <col1>, <col2> from <table1><table2> where <table1.primary key col>=<table2.Foreign Key Col>;

Ex.: 

select A.rollno, A.name, B.course_id  from student A , studentcourse B where  A.roll_no=B.roll_no;

A) INNER JOIN

The INNER JOIN returns only rows that have matching values in both tables.

Tip: You can use just JOIN instead of INNER JOIN, as INNER is the default join type.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

b) OUTER JOIN:  It is categorised as RIGHT  and LEFT Outer join.

i) The LEFT JOIN returns all rows from the left table (table1), and only the matched rows from the right table (table2).

If there is no match in the right table, the result for the columns from the right table will be NULL.

The LEFT JOIN and LEFT OUTER JOIN keywords are equal - the OUTER keyword is optional.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

ii) RIGHT JOIN

The RIGHT JOIN returns all rows from the right table (table2), and only the matched rows from the left table (table1).

If there is no match in the left table, the result for the columns from the left table will be NULL.

The RIGHT JOIN and RIGHT OUTER JOIN keywords are equal - the OUTER keyword is optional.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table.

Ex. 

Select A.rollno, A.name, B.course_id  from student A  RIGHT OUTER JOIN studentcourse B where  A.roll_no=B.roll_no ORDER BY B.COURSE_ID DESC; 

3. SQL Natural Join

A Natural Join is a type of INNER JOIN that automatically joins two tables based on columns with the same name and data type. It returns only the rows where the values in the common columns match.

• It joins tables using common columns with the same name.
• It returns only rows where values in those columns match.
• The common column appears only once in the result.

Example: Look at the two tables below:

Employee Table:

Department Table:

Example: Find all Employees and their respective departments.

SELECT 
    Emp_name,
    Dept_name
FROM Employee
NATURAL JOIN Department;

Output: