Database Query Using SQL

Revision of database concepts and SQL command covered in class XI

DATABASE: DBMS is a collection of interrelated data in arranged form and set of

programs used to access those data.
Advantages of DBMS:
 Elimination of data redundancy
 Data consistency
 Sharing of data
 Data Integrity
 Data isolation
 Privacy and Security
 Improved Backup and Recovery
DATA MODELS:
Data models describe the structure of the database. There are four data models in DBMS:
1. Relational Data Model
2. Hierarchical Data Model
3. Network Model
4. Object Oriented Data Model
Relational Data Model: This database consists of a collection of table. These tables are
called relations. A row in a table represents a relationship among a set of values.
 Relation: A relation is a table with columns and rows.
 Tuple: A row of a relation OR each row of a table.

 Domain: A set of values for the columns.

 Attribute: Column name of a relation.
 Degree: Total number of attributes/Column in a table

 Cardinality: Total number of tuples OR Number of rows in a table.

 View: A virtual table that does not exists but it is derived from other table
PRIMARY KEY: A column or Group of Column, which uniquely identify a Tuple in a
Table, is called Primary Key. A Primary key cannot have null or duplicate values. A table can

1
have only ONE primary key but a Combination of columns can also act as primary key.
Example Roll No., PNR Number, Aadhaar etc.

CANDIDATE KEY: All Columns or group of Column, that can act as a primary key are
called Candidate key.
ALTERNATE KEY: A candidate key that is not the primary key or All candidate
key other than Primary key is known as alternate key. Primary key is also candidate key!!

FOREIGN KEY: is a non-key attribute, which helps to establish a relation with another
table. It is generally the Primary Key of another table.
SQL:
 SQL stands for Structured Query Language.
 This is non-procedural Language.
 This is the common language for relational database. Means this language is used in
MySQL.
MySQL:
 MySQL stands for My Structured Query Language.
 MySQL is freely available, Open source Relational Database Management System
(RDBMS).
 It provides features for creating, storing, manipulating and accessing data stored in the
form of database and their tables.

Advantages of MySQL:
1 Ease of Use: It is very easy to learn and use.
2 Performance: MySQL is fast and large volume of database can be handled.
3 Data Security: In has powerful mechanism for ensuring only authorized users have access
the data.
4 Flexibility: MySQL can be linked to most of the other high level languages.
5 Portable: it is compatible with other languages like MS Access, Oracle etc.
6 Case Sensitive: MySQL is not case sensitive language.
Data Types:- Data types are rules that define what type of data may be stored in a column
and how that data is actually stored. Data types used in MySQL categorized into following
categories:

(i) Numeric –Integer, Float, Decimal, Numeric

2
(ii) String – Char, Varchar

(iii) Date -Date

(iv) Time -Time

NULL Values: If a column in a row has no value, then column is said to be null. NULLs
can appear in a column if the column is not restricted by NOT NULL or Primary Key. You
should use a null value when the actual value is not known.
Null and Zero is not equivalent. Any arithmetic expression containing a null always evaluates
to null.
Example: 7 + null = null
7 + 0 = 7 Difference between null and zero.

Classification of SQL commands:

SQL commands are categorized into four sub languages:
(i) Data Definition Language (DDL)
(ii) Data Manipulation Language (DML)
(iii) Data Query Language (DQL)
(iv) Transaction Control Language (TCL)
(v) Data Control Language (DCL)
(i) Data Definition Language (DDL): It consist the commands for creating, manipulating,
altering and deleting the table.
COMMANDS: CREATE, ALTER, DROP, RENAME, TRUNCATE, USE, SHOW

(ii) Data Manipulation Language (DML): It is used to manipulates data and for queries.
e.g. retrieval, insertion, deletion, modification of data stored in database.
COMMANDS: SELECT, INSERT IN TO, UPDATE, DELETE
Data Definition Language (DDL):
1. CREATE DATABASE
To create a new database in the system.
Syntax: CREATE DATABASE <DATABASE NAME>;
e.g. CREATE DATABASE office;
2. SHOW
To display the names of all the databases in the database system
Syntax: SHOW DATABASES;
e.g. SHOW Databases;

3
3. USE
To open a database to work in it that database.
Syntax: USE <DATABASENAME>;
e.g. USE office;
4 DROP DATABASE
To remove or delete a database.
Syntax: DROP database <database name>;
e.g: DROP database Office;
5. ALTER TABLE
To modify the structure of the table. i.e. add a new attribute, delete an existing
attribute, change to size and data type of a new attribute, renaming an attribute.
ALTER TABLE EMP ADD DEPTID INT(5) //To add new column DEPT
ALTER TABLE EMP DROP DEPTID; // To delete column DEPT
ALTER TABLE EMP MODIFY ENAME VARCHAR(15); //to change data type of
a column
ALTER TABLE EMP CHANGE ECITY CITY VARCHAR(25); //to rename column
6. CREATE TABLE
To create a new table.
Eg. CREATE TABLE EMP (Ecode int(6), Ename varchar(30), Dept varchar(30), city
varchar(25), sex char(1), DOB Date, salary float(12,2) );

Ecode Ename Dept City Sex Dob Salary

7. SHOW TABLES
To display the names of all the databases in the database system
Syntax: SHOW TABLES;
e.g. SHOW TABLES;
8. DROP TABLE
To remove or delete a table permanently.
Syntax: DROP TABLE <table name>;
e.g: DROP TABLE school;
9. DESCRIBE /DESC
To view the structure of the table.
Eg. DESC EMP; or DESCRIBE EMP;
Data Manipulation Language (DML)
1. INSERT INTO
To add new record or records into a table 4
 e.g. INSERT INTO EMP VALUES(101,”KARAN”, “Sales”, :Delhi”, “M”, “11-04-
23”,12000);
2. DELETE
To delete rows/tuples from a table:
e.g. DELETE FROM EMP WHERE ENAME = ‟Vishal‟
3. UPDATE
To modify/update the values in the existing record.
Eg: UPDATE EMP set SALARY = SALARY+ 5000 where Ecode =102;
4. SELECT
To display all records or to retrieve a subset of rows and columns from one or more
tables present in a database.
SELECT * FROM EMP;
SELECT * FROM EMP WHERE SALARY>1000;
SELECT * FROM EMP WHERE SALARY BETWEEN 5000 AND 10000;
SELECT * FROM EMP WHERE ENAME LIKE “%A”;

SQL FUNCTIONS

A function is a special type of predefine command set that performs some operations and
return a single value. The values that are provided to the functions are called parameters or
arguments. Some common functions are as follows:-

Math Functions:
Mathematical functions perform operation over numeric value.

Function Syntax Description Examples

POWER() POW(A,B) Power () returns the value of a number Select POWER(2,5);
POW() raised to the power of another number Output: 32
mean return a power to B
ROUND() ROUND(N,D) ROUND () function rounds a number to Select round(134.29,1);
a specified number of decimal places Output: 134.3
Means Select round(135.375, 2)
Return number rounded to D place after Output: 135.38
decimals
MOD() MOD(Dividend Mod () returns the remainder of one Select MOD(16,5);
, Divisor) number divided by another. Output: 1
Select MOD(10.5, 3);
MOD(M,N) Return remainder M/N Output: 1.5
5
Text Functions:

FUNCTION Syntax Description Examples

UPPE UPPER(str) UCASE(str) Upper/Ucase() SELECT LCASE (“HELLO
R () function return string WORLD”);
UCA in small letters. Output:
SE() hello world
LOW LOWER(str) LCASE(str) Lower/Lcase() SELECT UPPER (“india”) ;
ER() function return string Output:
LCAS in upper letters. INDIA
E()
SUBS SUBSTR (str, pos, len) Return the substring SELECT SUBSTR
TR() MID(S,P,N) as specified (“HELLO WORLD”,1,5);
MID() Return N character Output: HELLO
of string S, SELECT MID
beginning from P (“COMPUTER”,4,3);
Output: PUT
LENGTH() LENGTH(“hello India”) Return the length of SELECT LENGTH (“hello
a string in bytes India”) ;
Output: 11
Include blank space also
LEFT() SELECT LEFT Will return n SELECT LEFT (“HELLO
(“string”,n); characters of a string WORLD”,5);
from left side Output: HELLO
RIGHT() SELECT RIGHT Will return n SELECT RIGHT (“HELLO
(“string”,n); characters of a string WORLD”,3);
from right side Output: RLD
INSTR() SELECT INSTR It returns the SELECT INSTR
(„string‟, ‟sub str‟); position of sub string (“CORPORATE
in main string. FLOOR”,‟OR‟);
Output: 2
LTRIM() LTRIM („ HELLO „); It will remove space SELECT LTRIM
from left side (“ HELLO “);
Output: „HELLO „

6
RTRIM() RTRIM („ HELLO „); It will remove space SELECT RTRIM
from right side (“ HELLO “);
Output: “ HELLO“
TRIM() TRIM („ large „) Removes leading SELECT LTRIM(“ Apple “) ;
and trailing space Output-„Apple‟

Date Functions:

FUNCTION Syntax Description Examples

NOW() NOW(); Will return the SELECT NOW();
current date and time 2023-10-04 01:05:33
DATE() DATE(„date time‟); Extract the date part SELECT DATE(“2023- 10-04
of a date or date time 01:05:33”);
expression Output: 2023-10-04
MONTH() MONTH(„date‟); Return the month SELECT MONTH (“2013-
from the date passed 11-04”);
Output: 11
MONTHNA MONTHNAME(„date‟) Returns the full SELECT MONTHNAME (“2008-
ME() ;
name of the month 02-03”);
for date Output: 'February„
YEAR() YEAR(„date‟); Return the year SELECT YEAR(“2023- 11-
04”);
Output: 2023
DAY() DAY(„date‟); Returns the day of SELECT DAY(“2023-07- 04”);
the month for date, Output: 4
in the range 1to 31
DAYNAME DAYNAME(„date‟); Returns the name of SELECT DAYNAME (“2023-
()
the weekday for date 10-10”);
Output: Tuesday

Aggregate Functions:
Aggregate functions are functions that take a collection of values as input and return a single
value. SQL offers five types of aggregate functions. Aggregate functions perform operation
over set of Values Consider a table Emp having following records.
Table: Emp
7
 Empno Name Dept Salary
1 Raman Purchase 35000
2 Satish Sales 42000
3 Sonam Finance 42000
4 Vijay Sales 53000
5 Naveen Finance 65000

Table: Emp1

Code Name Sal

E1 Mohak NULL
E2 Anuj 4500
E3 Vijay NULL
E4 Vishal 3500
E5 Anil 4000

FUNCT Syntax Description Examples

ION
MAX() MAX(column Returns the MAXIMUM SELECT MAX(salary) from emp;
name/expression); of the values under the Output: 65000
specified column/ SELECT MAX(salary) from emp
expression where dept=”sales”;
Output: 53000

8
 MIN() MIN(column Returns the MINIMUM SELECT MIN(salary) from
name/expression); of the values under the emp;
specified Output: 35000
column/expression. SELECT MIN(salary) from
emp where dept=”finance”;
Output: 42000
AVG() AVG(column Returns the SUM of the SELECT AVG(salary) from
name/expression); values under the emp;
specified Output: 47400.00
column/expression. SELECT AVG(salary) from
Null values are excluded emp1;
while avg Output: 4000.00
()aggregate
function is used
SUM() SUM(column Return the sum of the SELECT SUM(salary) from
name/expression); values in the emp;
given column Output: 237000
COUNT COUNT(column Returns the COUNT of SELECT COUNT(Dept)
()
name); the number of values from emp;
Count(Distinct under the specified Output: 5
column name) column/ Select count(Distinct Dept)
expression from emp;
. Null values not included Output: 3
COUNT COUNT(*); It count the number of SELECT COUNT(*) from
(*)
records in table emp emp;
Output: 5
COUNT() VS COUNT(*)
Count(<column>) – counts non-null values of the specified
column Count(*) – counts no. of records
NOTE: - The input to sum ( ) and avg( ) must be a collection of numbers, but the other
functions can operate on non-numeric data types e.g. string
Group By: The Group by clause can be used in a select statement to collect data across
multiple records and group the result one or more column. It group the rows on the basis of
the values present in one of the column and then aggregate functions are applied on any
column of these groups to obtains the result of the query.

In some circumstance, we would like to apply the aggregate function not only to a
single set of tuples, but also to a group of sets of tuples. We specify this wish in SQL
9
using the group by clause.
The attributes given in the group by clause are used to form groups.
GROUP BY = make groups.
Example: - Find the average salary at each department.
HAVING = filter those groups
Solution: - SELECT Dept, avg(salary) FROM EMP group by Dept; after grouping.

Dept Avg(salary) WHERE = filter rows before

grouping.
Purchase 35000.00
Sales 47500.00
Finance 53500.00

Having: The HAVING clause is used in the SELECT statement to specify filter conditions for a
group of rows or aggregates The HAVING clause is often used with the GROUP BY clause to filter
groups based on a specified condition To filter the groups returned by GROUP BY clause, we use a
HAVING clause

WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates) is
applied before GROUP BY, HAVING is applied after (and can filter on aggregates)

Example:
Select <column1>, <column2>,..<column n>, <aggregate function(expression)> from table
WHERE <condition> Group by[column 1, column2,… column n] Having[<condition 1, condition
2, condition n>];
SELECT DEPT, AVG(SALARY) FROM EMP GROUP BY DEPT
HAVING AVG(SALARY)<45000;

Dept Avg(salary)
Purchase 35000.00
Order By: the SQL order by clause is used to sort the result in a specific order using ORDER
BY clause. The sorting can be done either in ascending or descending order. The default order is
ascending.
Example:- Display the list of employees in descending order of employee code.
Example:
Select <column list> from <table name> [where <condition>] ORDER BY <column name>
[ASC| DESC];
SELECT NAME FROM EMP ORDER BY NAME;
Name
ORDER BY arranges the final result neatly in the orde

Naveen
You can sort the data:
Raman in ascending order (smallest to biggest or A–Z) using

Satish
in descending order (biggest to smallest or Z–A) usin

Sonam
If you don’t mention anything, SQL assumes ascendin
Vijay 10
 SELECT NAME, SALARY FROM EMP ORDER BY SALARY DESC;

Name Salary
Naveen 65000
Vijay 53000
Satish 42000
Sonam 42000
Raman 35000
Suppose that we wish to list the entire emp relation in descending order of salary. If
several employees have the same salary, we order them in ascending order by empno.
We express this query in SQL as follows:-
SELECT EMPNO,SALARY FROM EMP ORDER BY SALARY DESC, EMPNO ASC;
emp Salary
no
5 65000
4 53000
2 42000
3 42000
1 35000

• Joins: Join is query that combines rows from two or more tables, based on a

common field between them.

• For joining the tables more than one table is listed in the from
clause of Select Command.
What is a Join?
• E.g: SELECT * FROM EMP, DEPT;
Types of Joins: A join combines rows from two or more tables based

a common column (like a shared ID or department

1. Cartesian Product number).

2. Equi Join
3. Natural Join
• Cartesian Product
• It return all possible concatenation of all rows from both table i e one row of
First table is joined with all the rows of second table
• Cartesian product join each row of one table with each row of another table
• So if First table have 6 rows and second table have 4 rows then total number of rows
in output will be 6 x 4 =24.
Example:
11
Cartesian Product
TABLE: DEPT
This is the most chaotic kind of
join.
Deptno Dname LOC
It combines every row of the first
table with every row of the
10 SALES CHENNAI
second table.
20 RESEARCH KOLKATA
So if:
30 HR DELHI
Table 1 (EMP) has 2 rows

Table 2 (DEPT) has 3 rows TABLE: EMP it’s usually filtered later

Then total output = 2 × 3 = 6 with a WHERE clause to

rows
Eno Ename Deptno match related rows
which turns it into an
1 Suresh 10
Equi Join or Natural Join.

2 Anoop 20
SELECT * FROM EMP, DEPT;
Eno Ename Deptno Deptno Dname LOC
1 Suresh 10 10 SALES CHENNAI
2 Anoop 20 10 SALES CHENNAI
1 Suresh 10 20 RESEARCH KOLKATA
2 Anoop 20 20 RESEARCH KOLKATA
1 Suresh 10 30 HR DELHI
2 Anoop 20 30 HR DELHI

EQUI JOIN: When two tables are joined on the basis of equality.

Ex: Select <Column 1>, <Column2> FROM <Table1>, <table2> WHERE <table1.
Primary Key Column= Table2. Foreign Key Column>
Select Eno, Ename,, [Link], Dname, LOC from Empl, Dept WHERE
[Link]= [Link];
Eno Ename Deptno Dname LOC
1 Suresh 10 SALES CHENNAI
2 Anoop 20 RESEARCH KOLKAT
A

Natural Join: Only one of the identical columns exists.

** The equi join and Natural Join are equivalent except that duplicate
columns are eliminated in the Natural Join.
• The JOIN in which only one of the identical columns exists in
called Natural Join It is similar to Equi join except that duplicate columns
are eliminated in Natural join that would otherwise appear in Equi Join
• In natural join we specify the names of column to fetch in place of
which is responsible of appearing common column twice in

12