RELATIONAL DATABASES AND STRUCTURED QUERY LANGUAGE (SQL)

DATABASE:
A database is a collection of interrelated data stored in a centralized location to serve multiple applications. A
database can be considered as a repository of information or data stored in an organized manner.

DBMS:
DBMS stands for Data Base Management System. DBMS is a computer software which helps us to store,
manipulate, and retrieve data from the data base in an organized manner. Example of DBMS are: MySQL Server,
Oracle, MS-Access, MS-SQL Server, SQLite, PostGreSQL, Sybase, Informix etc.

Data Redundancy:
Duplication of data is called Data Redundancy.

Data Security:
Data security is the protection of data from accidental or intentional disclosure to unauthorized persons.

Advantages of DBMS:

1. Helps us to store data in a structured format.

2. Sorts and manipulate data in the database.
3. Reduces data redundancy and data inconsistency.
4. Facilitates serach of data from the database by issuing a query
5. Provides data integrity and data validates and thus prohibits invalid data.
6. Allows to share data to multiple applications.
7. Protects data in the database and ensures data privacy.
8. Enforces database standard.
9. Provides facilities for back up and data recovery system.
10. Reduces programming effort –allows easy storing, manipulating, deletion and retrieval of data without
programming effeciency.

Components of Relational Database:

A Database in DBMS is a combination of multiple tables. Tables are also known as Entity.

a) Entity:
An Entity or Table or Relation is a tabular collection of organized information in rows and
columns.
b) Fields/Attributes:
The columns or fields of an entity is referred to as Attributes.
c) Degree:
The total number of attributes in an entity is known as Degree.
d) Rows/Tuples:
The rows or records of an entity is known as Tuples.
e) Cardinality:
The total number of tuples in an entity is known as Cardinality.
f) View:
View is a virtual table which does not exist in its own right but is derived from one or more
underlying base table. The difference between Table and View is that Table occupies a disk
space where as view does not.
 e.g. Table Name: PERSON

ID NAME AGE GENDER

1 BILL 27 M
2 MARY 25 F

As per the above:

Entity/ Table/Relation- PERSON

Fields/ Attributes - ID, NAME, AGE, GENDER

Degree - 4

Tuples/ Rows -
1 BILL 27 M
2 MARY 25 F

Cardinality - 2

Terminologies of DBMS:

1. Primary Key:
A Primary Key is an attribute or a combination of attributes that can uniquely identify a tuple within
an entity. The attribute assigned as Primary key can not store NULL values or duplicate values.
2. Candidate Key:
All attribute combinations that can serve as a primary key is known as Candidate Key. Candidate Keys
are eligible or the candidates of becoming Primary Key
3. Alternate Key:
A Candidate key which is not a Primary Key is known as Alternate Key.
4. Domain:
A domain is defined as the set of all unique values permitted for an attribute. For example, a domain
of date is the set of all possible valid dates, a domain of integer is all possible whole numbers.

Structured Query Language(SQL):

SQL is a computer program that helps us to store, retrieve, modify and remove information in a database.
SQL is a language that is composed of several commands to manipulate information in a database. SQL
was developed in 1970s by International Business Machine (IBM). SQL works with a DBMS to perform the
following:

1. Create/modify/delete database objects.

2. Inserting Records
3. Querying a table i.e. searching records from a table.
4. Modifying / Deleting records in a table.
5. Changing security settings and allowing users to work on database.

The two types of SQL Commands are : DDL and DML

Data Definition Language (DDL):
The DDL is a set of SQL Commands which are responsible for creating, modifying and deleting data
base objects. Example of DDL are : CREATE, ALTER, USE, DROP etc.
 Data Manipulation Language (DML): The DML is a set of SQL Commands which are responsible
for inserting, updating, displaying and deleting records of a table. Example of DML are : INSERT,
DELETE, UPDATE and SELECT.

MySQL :

MySQL is a freely available, open source, multiuser Relational Data Base Managnagent System(RDBMS) that
uses the standard SQL to work on databases. It can be freely downloaded from www.mysql.org
A database is collection is a collection of one/multiple entity/ entities. In My SQL, an entity/table should be
created inside a specific database. MySQL database system refers to the combination of a MySQL server
instance and a MySQL database. MySQL operates using client/server architecture in which the server runs on
the machine containing the databases and client connects to the server over a network.

Features of MySQL:

1. Speed: If the server server hardware is optimal, MySQL runs very fast.
2. Ease of Use: MySQL is a hgh performance relatively simple database system. MySQL commands
can be managed from the command line interface.
3. Cost: Being a free software, MySQL is available free of cost.
4. Query Language Support: MySQL understands and supports the simple SQL which is very easy
to use.
5. Portability: MySQL is platform independent i.e can be used in any platform.
6. Data Types: MySQL provides many data types to supoort different types of data.
7. Security: MySQL offers the system to protect information of databases through passwrds. Without
the proper password, users can’t log on to MySQL
8. Scalability and Limits: MySQL can handle large databases. A typical MySQL database can contain
about 60000 tables and about 50 million records.

How to Open My SQL:

Start - > All Programs -> My SQL - > My SQL Server - > My SQL Command Line Client - >
Supply the Password -> Press Enter

Creating Database:
CREATE DATABASE DATABASENAME;
e.g. CREATE DATABASE SCHOOL;

To Display the name all database in a system:

SHOW DATABASES;

To Open a database/ To Load a database: -

USE DATABASENAME;
e.g. USE SCHOOL;

To check which database is currently selected/opened: -

SELECT DATABASE();
The above command will display the name of the currently opened database.

To Remove/Delete a database:
DROP DATABASE DATABASENAME;
e.g. DROP DATABASE SCHOOL;
The above command will delete the database SCHOOL from the system along with all the tables within it.
Component elements of SQL:
The components element of SQL are:-
a) Literal b) Data Types c) NULL

a) Literal:
Literals are something which refer to a fixed data value. Data inserted into a column of a table is a literal.
For e.g.
5, 18.5 , -20 are Numeric literals
Among these 5 & -20 are integer literals where as 18.5 is a floating / real literal.
A real literal in SQL can store a maximum of 53 digits of precision.
“A” , ‘A’ , “APS” etc. are text or string literal . String literals can have a maximum length of 4000 bytes (as
per MySQL Server 5.1).
String literals are case-sensitive and are enclosed within single or double quotes.

b) Data Type :
My SQL uses three different categories of data types.
They are :-
Numeric Type
Data and Time Type
String Type

(i) Numeric type:-

MySQL uses the following numeric data types.

INT/INTEGER – can store positive or negative whole numbers Maximum 11 digits are supported.
SMALLINT - can store positive or negative whole numbers Maximum 5 digits are supported.
DECIMAL(M,D) or FLOAT (M,D) or DOUBLE (M,D)- can store positive or negative real numbers with
maximum “M” no of digits in the integer part and maximum “D” no of digits in the decimal or fractional part.

(ii) Date and Time type :-

DATE – can store any valid date in the format of YYYY-MM-DD

TIME – can store any valid time in the format of HH:MM:SS

(iii) String type: –

CHAR(M) – A fixed length string can be stored between 0 to 255 characters. The space occupied by
a char attribute- if not in use then the excess space is consumed by SQL.

VARCHAR(M) – A variable length string can be stored between 0 to 255 characters. The space
occupied by a varchar attribute- if not in use then the excess space is released by SQL. VARCHAR
can store up to 2000 bytes of characters.

VARCHAR2(M)- A variable length string can be stored between 1 to 32767 characters. The space
occupied by a varchar2 attribute- if not in use then the excess space is released by SQL. VARCHAR2
can store up to 4000 bytes of characters.

c) NULL:
In a table if a column in a row has no value then column is said to store NULL. In MySQL NULL represents
nothing. An arithmetic expression containing a NULL always evaluates to NULL. NULL and 0 are not same.
NULL occupies 0 byte. For e.g.- NULL values added to 10 is always NULL.

To create Tables:
In order to create a table, a database should be created first and it should be opened using “USE”
command. In SQL tables can be created in two ways:-
i) Creating Tables without constraints.
ii) Creating Tables with constraints
 Creating Tables without constraints: -

When a table is created, its columns are named, data types & size are supplied for each column. Each
table must have at least 1 column.

e.g.
CREATE TABLE STUDENT (
ADM_NO INT(6),
NAME VARCHAR(20),
CLASS INT(2),
SECTION VARCHAR(2)
);

Creating Tables with constraints: -

A constraints or Integrity constraint refers to a condition or a check that is applied to a column or set of
columns. Once an integrity constraint is enabled all data for that particular column should abide by the
constraint. Violating the integrity constraint while entering data may result into SQL error. The different
constraint in SQL are:-
a) Primary Key constraints
b) Not NULL constraints
c) Unique constraints
d) Default constraints
e) Check constraints

Primary Key Constraints:

This constraint declares a column/attribute to be the primary key. A primary key column can neither store
NULL values nor can store duplicate values. A column designated as a primary key must follow the NOT
NULL & unique constraints.
e.g. CREATE TABLE EMPLOYEE (
EMP_NO INT (6) PRIMARY KEY,
_____
_____
);
Not NULL Constraints: -
The NOT NULL constraint ensures that an attribute declared as NOT NULL cant store NULL values i.e. can’t
be kept emptied but can store duplicate values.
e.g. CREATE TABLE EMPLOYEE (
NAME VARCHAR (60) NOT NULL,
_
_
);
Unique Constraints:
This constraint ensures that a column declared as UNIQUE can store NULL values but cannot store duplicate
values. No two rows can have the same value for a unique column.
e.g.

CREATE TABLE EMPLOYEE (

EMP_NO INT (6) PRIMARY KEY ,
NAME VARCHAR (40) NOT NULL ,
CONTACT _NO INT (10) UNIQUE,
BANK _ACC _ NO INT (7) NOT NULL UNIQUE
);
 Although NOT NULL and UNIQUE serve the purpose of PRIMARY KEY combinedly but Primary Key Constraint
can be used only once in an entity while UNIQUE and NOT NULL can be used for multiple times.

Default Constraints:
A default value can be specified for a column using the default constraint. When the user does not enter
value for that particular column, the default value is automatically inserted in that column.
eg. CREATE TABLE EMPLOYEE (
EMP _ NO INT (5) PRIMARY KEY ,
DEPT VARCHAR (10) DEFAULT” ACCOUNTS” );
According to the above table if the value of DEPT is kept empty during insertion of records, by default
“ACCOUNTS” will be inserted. A column /attribute can have only 1 default value.

Check Constraints:
This constraint limits or checks the values that can be inserted into the column of a table. Check constraint
prohibits invalid data entry.

e.g. 1- To declare a COLUMN CLASS which will have values between 1 to 12.
CLASS INT (2) CHECK (CLASS BETWEEN 1 AND 12)
e.g. 2- To declare a column STREAM which can store either “SCIENCE” or “COMMERCE” or “HUMANITIES”
STREAM VARCHAR (20) CHECK (STREAM IN (“SCIENCE” , “COMMERCE” , “HUMANITIES” ) )
e.g. 3- To declare a column AGE which should be greater than 15
AGE INT (2) CHECK (AGE > 15)

To display the list of tables inside a particular database:

First open the database using the “USE” command.
Then write the following Statement.
SHOW TABLES;

To view the structure of an existing Table :

First open the database using the “USE” command.
Then apply the following syntax:
DESC TABLENAME;
OR
SHOW CREATE TABLE TABLENAME;
e.g. DESC STUDENT;

OR
SHOW CREATE TABLE STUDENT;

To insert records in a Table:

To insert record/data in a table we have to use the command INSERT The syntax is :
INSERT INTO TABLENAME VALUES (VALUE 1, VALUE 2, ………);
OR
INSERT INTO TABLENAME (COLUMN 1, COLUMN 2, ……………) VALUES (VALUE 1, VALUE 2, …………);
Assume a table student has the following attributes:
ADM_NO, NAME, CLASS, SECTION, HOUSE

To insert the values: 1400, KARAN, 12, A, RED

We should use the command
INSERT INTO STUDENT VALUES (1400, “KARAN”, 12, “A”, “RED” );
To insert values for particular columns, say ADM_NO, NAME & CLASS We
should use the following:
INSERT INTO STUDENT VALUES (1600, “RONIT”, 10, NULL, NULL);
OR
INSERT INTO STUDENT (ADM_NO, NAME, CLASS ) VALUES (1000, “SAM”, 8);

Notes:
a) The above two commands can work only if the columns kept emptied are not designated as NOT NULL.
b) If default value is applied for a particular column and if we keep it empty while inserting record then
the default value will be taken into consideration.
c) Dates are by-default entered in the format: ’ YYYY-MM-DD’ . The Date to be entered should be either
enclosed within quotes or to be enclosed within a pair of curly brace ({ }).
d) The order of values must match with the order of columns while inserting a record in the table.

SIMPLE SQL QUERIES USING SELECT COMMAND:

The DML command SELECT is used to search or retrieve data from a table. Consider a table/entity STUDENT
having the following attributes:
ADM_ NO of INT(4) type
NAME of VARCHAR (20) type
SUBJECT of VARCHAR(20) type
GENDER of CHAR (1) type
CLASS of INT (2) type
MARKS of INT (3) type
DOB of DATE type

1. Write SQL code to display all the records of the students table.
SELECT * FROM STUDENT;

2. Write SQL code to display the Name, Class & Marks of the student.
SELECT NAME, CLASS, MARKS FROM STUDENT;

3. Write SQL code to display the class from the student table. The duplicate or the redundant class should
be eliminated.
SELECT DISTINCT CLASS FROM STUDENT;
The distinct keyword eliminates duplicate entries in a specific column. Only one DISTINCT clause can
be used in an SQL query.

4. Write SQL code to display the details of class 12 students.

SELECT * FROM STUDENT WHERE CLASS = 12;

5. Write SQL command to display the details of all students except the students of class 12
SELECT * FROM STUDENT WHERE CLASS <>12;

6. Write SQL code to display the marks by adding 5 in COMPUTER SCIENCE. The value to be displayed
should be under be under a label CHANGE MARKS.
SELECT MARKS+5 AS “CHANGE MARKS” FROM STUDENT WHERE SUBJECT = “COMPUTER SCIENCE”;

7. Write SQL code to display the name of the students whose DOB is not entered.
SELECT NAME FROM STUDENT WHERE DOB IS NULL;

8. Write SQL code to display the Name & Adm_no of the students whose DOB is entered.
SELECT ADM_NO, NAME FROM STUDENT WHERE DOB IS NOT NULL;

9.Write SQL code to display the details of the male students who are born after 1995

SELECT * FROM STUDENT WHERE GENDER =’M’ AND DOB>’1995-12-31’;

10. Write SQL code to display the name of the female students who got above 90 in Maths.
SELECT NAME FROM STUDENT WHERE GENDER =’F’ AND SUBJECT =’Maths’ AND MARKS>90;

11. Write SQL code to display the adm_no and name of the students who study in 11 or 12.
 SELECT ADM_NO, NAME FROM STUDENT WHERE CLASS=11 OR CLASS=12;

12. Write SQL code to display the details of the students who are either studying in in class 10 or DOB is less
than 31st Aug 2000.
SELECT * FROM STUDENT WHERE CLASS =10 OR DOB < {2000-08-31};

13. Write SQL code to display the name of all students except in class 12.
SELECT NAME FROM STUDENT WHERE NOT CLASS =12;

14. Write SQL code to display the details of the students whose marks is between 60-80 (including
both values) in Physics.
SELECT * FROM STUDENT WHERE SUBJECT=’PHYSICS’ AND MARKS BETWEEN 60 AND 80;

OR

SELECT * FROM STUDENT WHERE SUBJECT =’PHYSICS’ AND (MARKS>=60 AND MARKS<=80);

15. Write SQL Code to display the name & class of the students whose admission number is not
between 4000 to 6000 :
SELECT NAME, CLASS FROM STUDENT WHERE ADM_NO NOT BETWEEN 4000 AND 6000;
OR
SELECT NAME, CLASS FROM STUDENT WHERE ADM_NO <4000 OR ADM_NO>6000;

16. Write SQL Code to display the details of the students who are in 9, 10, 11 or 12 :
SELECT * FROM STUDENT WHERE CLASS=9 OR CLASS=10 OR CLASS=11 OR CLASS=12;
OR
SELECT * FROM STUDENT WHERE CLASS IN (9, 10, 11, 12);

17. Write SQL Code to display the details of the students who are neither in 9, nor in 10, nor in 11,
nor in 12 :
SELECT * FROM STUDENT WHERE CLASS NOT IN (9, 10, 11, 12);
OR
SELECT * FROM STUDENT WHERE CLASS<>9 AND CLASS <>10 AND CLASS<>11 AND CLASS<>12;

18. Write SQL Code to display the details of the students in the descending order of their DOB .

SELECT * FROM STUDENT ORDER BY DOB DESC;

19. Write SQL Code to display the name of the students in the ascending order of marks in Maths.

SELECT NAME FROM STUDENT WHERE SUBJECT= ‘Maths’ ORDER BY MARKS ASC;

20. Write SQL Code to display adm_no and name of all female students in the alphabetical order of name:
SELECT ADM_NO, NAME FROM STUDENT WHERE GENDER=’F’ ORDER BY NAME ASC;

21. Write SQL Code to display the name of the students whose name starts with ‘P’ :
SELECT NAME FROM STUDENT WHERE NAME LIKE ‘P%’;

22. Write SQL Code to display the details of the students whose name ends with ‘I’

SELECT * FROM STUDENT WHERE NAME LIKE ‘%i’;

23. Write SQL Code to display Adm_no & DOB of those students whose name does not start with ‘A’ or ‘B’ :

SELECT ADM_NO, DOB FROM STUDENT WHERE NAME NOT LIKE ‘A%’ AND NAME NOT LIKE ‘B%’;

24. Write SQL Code to display the details of the students whose name has “Kumar” within it:
 SELECT * FROM STUDENT WHERE NAME LIKE '%Kumar%';

25. Write SQL Code to display the DOB of those students whose name's 2nd character is 'a'

SELECT DOB FROM STUDENT WHERE NAME LIKE '_a%';

26. Write SQL Code to display adm_no and name of those students whose name 3rd character is 'i' & 5th
character
is 't' :
SELECT ADM_NO, NAME FROM STUDENT WHERE NAME LIKE '_ _i_t%';

27.Write SQL Code to display the details of the students whose name is 5 lettered word.

SELECT * FROM STUDENT WHERE NAME LIKE '_ _ _ _ _';

The LIKE Operator is used for comparisons on character strings using Pattern Matching. SQL allows two
pattern matching characters. They are:
% (Percentage)
_ (Underscore).
% is used for any number of unknown character i.e. sub-string. _ is used for only one unknown character.

Modifying Table Data using Update Command:

UPDATE TABLENAME SET COLUMNNAME=NEWVALUE WHERE CONDITION;

a) Write SQL Code to assign the marks of all students of class 10 to 60 in English.
UPDATE STUDENT SET MARKS=60 WHERE CLASS=10 AND SUBJECT=’English’;
b) Write SQL Code to assign DOB to NULL and increase marks by 5 of all Class 12 students in
Maths.
UPDATE STUDENT SET DOB=NULL, MARKS=MARKS+5 WHERE CLASS=12 AND SUBJECT
=’MATHS’;
c) Write SQL Code to increase the marks of all students by 10% in all the subjects.

UPDATE STUDENT SET MARKS=MARKS + MARKS * 10/100;

Deleting Table Data using Delete Command:

DELETE FROM TABLENAME WHERE CONDITION;

The delete command removes the records of the table but does not delete the table or its structure.

a) Write SQL Code to delete all the records from Student table.

DELETE FROM STUDENT;

b) Write SQL Code to delete the records of all male students of class 8.

DELETE FROM STUDENT WHERE GENDER=’M’ AND CLASS=8;

The Truncate command is used to delete all the rows from the table and free the space containing the table.
TRUNCATE TABLE TABLENAME;

Use of Alter Command:

The DDL command Alter is used to modify the structure of an entity. Alter Command is used for the following
purposes:

(a) To add a new column

(b) To modify the data type of a column
(c) To rename a column
(d) To delete a column
(e) To add a Primary key Constraint
(f) To delete a Primary key Constraint

(a) To add a new column: -

ALTER TABLE TABLENAME ADD (COLUMN1 DATATYPE (SIZE), COLUMN2 DATATYPE (SIZE)………..);

e.g.

ALTER TABLE STUDENT ADD (ADDRESS VARCHAR (20));

State – Why the following SQL Code will produce an error?

ALTER TABLE STUDENT ADD (ADDRESS VARCHAR (20) NOT NULL);

(b) To modify the data type of a column: -

ALTER TABLE TABLENAME MODIFY COLUMNNAME DATATYPE (SIZE);

Write SQL Code to modify the data type of column ADDRESS from VARCHAR (20) to VARCHAR (40)
ALTER TABLE STUDENT MODIFY ADDRESS VARCHAR (40);

(c) To rename a column:

ALTER TABLE TABLENAME CHANGE OLDNAME NEWNAME DATATYPE (SIZE);

Write SQL Code to rename the column GENDER in the student table to ST_GENDER
ALTER TABLE STUDENT CHANGE GENDER ST_GENDER CHAR(1);

(d) To delete a column:

ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;

Write SQL Code to remove the column ST_GENDER in the student table including all its information.
ALTER TABLE STUDENT DROP COLUMN ST_GENDER;

(e) To add a Primary Key Constraint:

Adding a primary key constraint does not add a new column but attaches the constraint with an existing
column. Before adding the primary key constraint make sure that the column designated as primary key
should not contain NULL or duplicated values.
ALTER TABLE TABLENAME ADD PRIMARY KEY (COLUMNNAME);
Write SQL Code to add primary key constraint to the REG_NO column of student table
ALTER TABLE STUDENT ADD PRIMARY KEY (REG_NO);

(f) To delete a Primary Key Constraint:

Deleting a primary key constraint does not delete the column but remove the constraint primary key, after
which the column can store NULL or duplicated values.
ALTER TABLE TABLENAME DROP PRIMARY KEY;
e.g. Write SQL code to remove the primary key constraint from student table.
ALTER TABLE STUDENT DROP PRIMARY KEY;
Difference between Update and Alter:

Update is DML and is used to modify the records in the table. Alter is DDL and is used to modify, add or remove
the components of a table.

Difference between Update and Alter:

Delete is DML and is used to delete records from a table. Drop is DDL and is used to delete column, constraint
or even the table.

How to remove a table?

Removing a table deletes all the records in the table including the columns and constraints.
DROP TABLE TABLENAME;
Write SQL Code to delete the table STUDENT :
DROP TABLE STUDENT;

How to remove a database?

Removing a database deletes all the tables inside the database including all the records in those tables.
DROP DATABASE DATABASENAME;
Write SQL Code to delete the database SCHOOL :
DROP DATABASE SCHOOL;

SQL Functions:

A function is a special type of pre-defined command set that performs some operation and returns a single
value. The values that are provided to the functions are called parameters or arguments. MySQL offers two
typws of functions. They are – i) Single Row Functions ii) Multiple Row Functions or Group Functions.

Single Row Functions:

The SQL Function which operates on a single row or single data at a time and returns a single value is known
as Single Row Function. Single Row Functions in MySQL are categorised into various categories like Math
Functions, Text/String Functions and Date/Time Functions.
Some Single row functions are listed below:
Category SL.NO Function Name Purpose Example
This functions takes two e.g.1
1. power() parameters say a and b and Select Power (3,4);
or returns ab. Here a is the base Ans: 81
pow() and b is the exponent. If the e.g.2
base is 0, the exponent can’t Select Power(-2,-3);
be negative. Ans: -0.125
2. Sqrt() This function takes a positive e.g.
Math parameter and returns its Select Sqrt(121);
Functions square root. Ans: 11
Or 3. Mod() This function takes two e.g.1
Numeric parameters a and b and Select Mod(16,5);
Functions returns the remainder while Ans: 1
dividing a by b. If the e.g.2
denominator i.e. b is 0, it Select Mod(28,5);
returns NULL. Ans: 3
e.g.3
Select Mod(30,5);
Ans: 0
4. Round() This function takes two e.g.1
parameters and returns the Select Round(34.675,2);
first parameter rounded off as Ans: 34.68
per the value of the second e.g.2
parameter. Select Round(57.43362,4);
Ans:57.4336
e.g.3
Select Round(23.999,1);
Ans:24.0
e.g.4
Select Round(45.667);
Ans: 46
e.g.5
Select Round(28.67,-1);
Ans: 30
e.g.6
Select Round(125.44,-2);
Ans: 100
5. Truncate() This function takes two e.g.1
parameters and returns the 1st Select Truncate(34.675,2);
parameter with some digits Ans: 34.67
truncated as per the second e.g.2
parameter. Select Truncate(57.43362,4);
Ans:57.4336
e.g.3
Select Truncate(23.999,1);
Ans:23.9
e.g.5
Select Truncate(28.67,-1);
Ans: 20
e.g.5
Select Truncate(125.44,-2);
Ans: 100
1. Ucase() This function takes a string as e.g.
Or argument/parameter and Select Ucase(‘friend’);
Text Upper() returns it in UpperCase. Ans:
Functions FRIEND
Or 2 LCase() This function takes a string as e.g.
String Or argument/parameter and Select LCase(‘FRIEND’);
Functions Lower() returns it in LowerCase. Ans:
friend
 3. Ltrim() This functions takes a string as e.g.
argument and returns the Select Ltrim(‘ Computer Book ‘);
string by removing the leading Ans:
spaces i.e. spaces from the ‘Computer Book ‘
left side of the string.
4. Rtrim() This functions takes a string as e.g.
argument and returns the Select Rtrim(‘ Computer Book ‘);
string by removing the trailing Ans:
spaces i.e. spaces from the ‘ Computer Book‘
right side of the string.
5. Trim() This functions takes a string as e.g.
argument and returns the Select Trim(‘ Computer Book ‘);
string by removing both the Ans:
leading and trailing spaces i.e. ‘Computer Book’
spaces from both the left and
right side of the string.
6. Length() This function takes a string as e.g.
argument and returns the Select Length(‘MySQL Function’);
number of characters in the Ans:
string. 14
7. Left() The function takes a string e.g.
and a number(say x) as Select Left(‘Informatics’,6);
argument and returns x no of Ans:
characters from the left side of Inform
the string.
8. Right() The function takes a string e.g.
and an integer (say x) as Select Right(‘Informatics’,4);
argument and returns x no of Ans:
characters from the right side tics
of the string.
9. Mid() This function takes a string e.g.1
Or and two integers(say x and y) Select Mid(‘Technology’,3,4);
Substr() as argument and returns a Ans:
Or substring from the string by chno
Substring() extracting y no of characters e.g.2
from the position x. Select Substr(‘Decision’,5);
Ans:
sion
e.g.3
Select
upper(substring(‘electronics’,-4,4);
Ans:
NICS
10. Instr() This function takes two strings e.g.
as arguments and returns the Select Instr(‘Corporate floor’,’or’);
position of the first occurrence Ans:
of the 2nd string in the 1st 2
one.If the 2nd string is not
found then 0 is returned.
This function returns the e.g.
current date in the format Select Now();
yyyy-mm-dd and the current Ans:
1. Now() time in the format hh:mm:ss. 2020-04-03 17:39:26
Date Now() returns the time at
Functions which the statement began to
execute.
2. Sysdate() The function returns the e.g.
current date and the particular Select Sysdate();
 time at which the function Ans:
executes. 2020-04-03 17:42:33
This function returns the date e.g.
part from a date expression. Select Date(Now());
3. Date() Ans:
2020-04-03
This function returns the e.g.
month from a date expression. Select Month(Now());
4. Month() Ans:
4
This function returns the e.g.
month name from a date Select Month(Now());
5. MonthName() expression. Ans:
April
e.g.
This function returns the year Select Year(Now());
6. Year() part from a date expression. Ans:
2020
e.g.
This function returns the day Select Day(Now());
7. Day() number of the date part from Ans:
a date expression. 3
This functions returns the e.g.
8. DayName() name of the weekday from the Select DayName(‘1947-08-15’);
date expression. Ans:
Friday

Multiple Row / Group Functions:

Multiple row or Group functions work on one of more rows instead of a single row. These functions operate on
multiple rows and produce summarized information. The different group functions are : sum(), avg(), max() ,
min() and count()

(i) sum() : This function works with numeric column and returns the sum of values in a particular column.

(ii) avg(): This function works with numeric column and returns the average of values in a particular
column.
(iii) max() : This function works with numeric column and returns the maximum value in a particular
column.

(iv) min() : This function works with numeric column and returns the minimum value in a particular column.

(v) count () : This function works with numeric and non-numeric column and counts the values in a
particular column that is matching with a specific condition.

Consider the following table EMPLOYEE:

 EMP_NO NAME DEPT DOB GENDER SALARY
6362 Shankar Sales 1987-03-15 M 18000
8568 Mohan Testing 1982-08-14 M 21000
2884 Vivek Testing 1990-02-23 M 16700
9694 Rupali Accounts 1984-10-01 F 23600
2846 Soniyaa Accounts 1982-11-30 F 25600
2578 Reetam Finance 1981-09-18 M 31700
7922 Jaydeep Finance 1979-09-09 M 35000
1193 Pallavi Finance 1991-06-07 F 19700
3294 Deeksha Sales 1980-04-18 F 25700
9991 Jatinder Testing 1988-03-09 M 17600

a) Write SQL Code to display the sum of salaries of all employees in Accounts department.
SELECT SUM (SALARY) FROM EMPLOYEE WHERE DEPT=’Accounts’;
Output:
SUM (SALARY)
49200
b) Write SQL Code to display the average salaries of all Male employees in Finance department
SELECT AVG (SALARY) FROM EMPLOYEE WHERE GENDER=’M’ AND DEPT=’Finance’;
Output:
AVG (SALARY)
33350
c) Write SQL Code to display the maximum salary of all female employees.
SELECT MAX (SALARY) FROM EMPLOYEE WHERE GENDER=’F’;
Output:
MAX (SALARY)
25700
d) Write SQL Code to display the maximum and minimum DOB
SELECT MAX (DOB), MIN(DOB) FROM EMPLOYEE;
Output:
MAX(DOB) MIN(DOB)
1991-06-07 1979-09-09
e) Write SQL Code to display the number of records in the employee table
SELECT COUNT (*) FROM EMPLOYEE;
Output:
COUNT(*)
10
f) Write SQL Code to display the number of unique departments
SELECT COUNT (DISTINCT DEPT) FROM EMPLOYEE;
Output:
COUNT (DISTINCT DEPT)
4
g) Write SQL Code to count the number of male employees drawing salary of more than 25000
SELECT COUNT (*) FROM EMPLOYEE WHERE GENDER=’M’ AND SALARY> 25000;
Output:
COUNT(*)
2
h) Write SQL Code to count the number of employee whose DOB is after 1989.
SELECT COUNT(*) FROM EMPLOYEE WHERE DOB> { 1989-12-31}; Output:
 COUNT(*)
2

The Group By Clause is used in SQL to produce summarized information by grouping a particular column.
Group By clause is used to produce aggregate information, a particular column wise.

i) Write SQL Code to count the number of male and female employees separately
SELECT GENDER , COUNT(*) FROM EMPLOYEE GROUP BY GENDER;
Output:
GENDER COUNT(*)
M 6
F 4
j) Write SQL Code to display the maximum salary department wise
SELECT MAX(SALARY), DEPT FROM EMPLOYEE GROUP BY DEPT;
Output:
MAX(SALARY) DEPT
25700 SALES
21000 TESTING
25600 ACCOUNTS
35000 FINANCE

The Having Clause in SQL is used to specify a condition when Group By Clause is used. The Where clause
cannot be used with Group By. So instead of that the Having clause is used.

k) Write SQL Code to count the number of employees’ department wise provided the number of
employees is more than 2
SELECT COUNT (*), DEPT FROM EMPLOYEE GROUP BY DEPT HAVING COUNT(*)>2;
Output:

Count(*) DEPT
3 TESTING
3 FINANCE

The Relational Algebra:

A Relational database is a combination of two or more tables which are joined on the basis of a common
column/attribute to ensure data validation and data integrity. The Relational Algebra is a collection of
operations on entities /relations. Different operations on relational algebra are listed below:
a) The Select Operation:
The Select Operation or Selection selects or extracts tuples from an entity which satisfies a given
condition or predicate. It is mathematically denoted by Greek letter Sigma.
b) The Project Operation:
The Project Operation or Projection lets us to display the values of some specified attributes in specified
order. It is denoted by the Greek letter Pi

c) The Cartesian Product Operation/Cross Join:

The Cartesian Product between two entities produces an output with all possible combination of the
tuples of the two relations operated upon. It is denoted by ×. If the degree of the 1st Table is D1 and
Cardinality is C1 and the degree and Cardinality of the 2nd Table is D2 and C2 respectively then on
performing Cartesian product between the two tables, the no of columns in the output=( D1+D2) and
the no of rows in the output=(C1×C2)
 e.g.

d) The Union Operation:

The Union Operation between two entities involves all tuples from the first entity, all tuples from the second
entity and the common tuples are represented only once. The Union Operation is denoted by U
e.g. If the first entity A contains R1 tuples, the second entity contains R2 tuples and C number of common
tuples are there, then on performing Union Operation between A and B the number of tuples in the output
is :
(R1+R2-C)

e) The Intersection Operation:

The Intersection Operation between two entities involves only the common tuples between the entities. It
is denoted by n.
For e.g. If the first entity A contains R1 tuples, the second entity contains R2 tuples and C number of
common tuples are there, then on performing Intersection Operation between A and B the number of tuples
in the output is: C

f) The Set Difference/Minus Operation:

The Set difference/Minus Operation between two entities involve the tuples which are present on the first
entity but not in the second. It is denoted by –
For e.g. If the first entity A contains R1 tuples , the second entity contains R2 tuples and C number of
common tuples are there , then on performing Set Difference Operation between A and B the number of
tuples in the output is : (R1-C)

Concept of Foreign Key:

A Foreign Key is used to represent the relationship between two tables. A foreign key is a non-key attribute
whose values are derived from the primary key of some other table. The primary key of the master table
becomes the foreign key of the dependent table. Foreign key has the following features:
  Foreign Key in dependent table derives its value from the Primary key of the master table.
 A Foreign Key can store duplicate value in an entity where as a Primary key cannot.
 Both the primary key and the foreign key should be of same data type.
 The primary key of the master table and the foreign key of the dependent table must be linked in
order to enforce Referential Integrity.
 Referential Integrity is a system of rules that a DBMS uses to ensure that relationship between
records in related tables are valid i.e. a foreign key cannot store a value which is not present in the
primary key of the master table.
 It is not possible to delete/update a value of a field from a master table if the matching record
exists in the dependent table. An attempt to do so will lead to the violation of referential integrity.

Joining of Tables:

A Join is a technique to combine tuples from more than one entity. Sometimes it may happen that listing
tuples from one entity is not enough-rather tuples from more than one entity is required. In that case we
have to join the required tables.
There are many types of Joins. They are Inner Join, Left Outer Join, Right Outer Join, Cartesian Product
or Cross Join, Natural Join, Equi-Join etc.

 Inner Join:
This is the most common type of Join. Inner Join is also known as Equi Join. An Inner Join
returns records that have matching values in both the tables. Inner Join or Equi-Join can be
performed in SQL based on the equality of the common columns i.e. in SQL in order to join
two tables both the tables should have a common column.

i.e.

e.g.1
Let us consider a Database called Ex. Inside the database there are 2 tables. They are Tab1
and Tab2.

Contents of Tab1:

ID NAME
1 A
3 B

Contents of Tab2:

ID CLASS
1 12
2 10

Write SQL Code to display the NAME and CLASS for matching records in both the tables.
Technique-1:
Select Name, Class from Tab1 T1, Tab2 T2 where T1.ID=T2.ID;
 NAME CLASS
A 12

Technique-2:
Select Name, Class from Tab1 T1 Join Tab2 T2 on T1. ID=T2.ID;

NAME CLASS
A 12

e.g.2
Consider the following two tables:

TABLE NAME: APPLICANT

A_NO NAME QUALIFICATION RANK

10 ISMAIL M.SC ZOOLOGY 6
20 SHIVAM M.COM 8
30 JYOTI M.COM 12
40 MUSKAN M.SC PHYSICS 2
50 KAMAL M.A ECONOMICS 4

TABLE NAME: JOB

A_NO POST SALARY

10 PGT BIOLOGY 32000
30 PGT COMMERCE 33000
40 TGT SCIENCE 26000
50 PGT ECONOMICS 34000

a)
Write SQL Code to display the Name, Post and Salary of all applicants whose salary is more than
32500.
Ans:
SELECT NAME, POST, SALARY FROM APPLICANT A, JOB J WHERE SALARY >32500 AND
A.A_NO=J.A_NO;
Output:
NAME POST SALARY
JYOTI PGT COMMERCE 33000
KAMAL PGT ECONOMICS 34000
 b)
Write SQL Code to display the Name and Post of those students whose records are present on both
the tables.
Ans:
SELECT NAME, POST FROM APPLICANT A, JOB J WHERE A.A_NO=J.A_NO;
Output:
NAME POST
ISMAIL PGT BIOLOGY
JYOTI PGT COMMERCE
KAMAL PGT ECONOMICS
MUSKAN TGT SCIENCE

c)
Write SQL Code to display A_NO , NAME and SALARY in the descending order of RANK.
Ans:
SELECT A.A_NO, NAME, SALARY FROM APPLICANT A, JOB J WHERE A.A_NO=J.A_NO ORDER BY
RANK;
Output:
A_NO NAME SALARY
30 JYOTI 33000
10 ISMAIL 32000
50 KAMAL 34000
40 MUSKAN 26000

 Left Outer Join:

It is a type of Join which returns all records from the left table, and the matched records from the right
table.
i.e.

 Right Outer Join:

It is a type of Join which returns all records from the right table, and the matched records from the left
table.
i.e.
 Natural Join:
In MySQL, the Natural Join is such a join that performs the same task as an INNER or LEFT JOIN, in
which the On or Where clause refers to all the common columns of the tables. The Mysql Natural Join
is structured in such a way that, columns with the same name of associated tables will appear once
only.
e.g
Let us consider a Database called Ex. Inside the database there are 2 tables. They are Tab1 and Tab2.
Contents of Tab1:
ID NAME
1 A
3 B

Contents of Tab2:

ID CLASS
1 12
2 10

Write SQL Code to perform Natural Join of the tables Tab1 and Tab2.
Ans:
Select * From Tab1 Natural Join Tab2;
Output:
ID NAME CLASS
1 A 12

-------------------------------