DATABASE CONCEPTS AND SQL

Learning Objective:

After Having Studied this Chapter, You will be able to Understand : -

• DBMS and its related concepts.

• Relational Data Model and Terms used in it.
• Keys, Relational Algebra, Selection and Projection Operation.
• SQL Commands, DDL, DML Commands, SQL Data Types.
• Create Table Command, Insert into Command.
• Various facets of Select Command, Order by Clause.
• Alter Table, Update and Delete Commands and Aggregate Functions.

15.1 INTRODUCTION

Data is central building backbone of any organization. Ever since the old times, people
were using manual methods for storing and manipulating data. For example, in old times,
in schools, details like name, parent’s name, address and other details of students were
stored in notebooks. But this system had several flaws like there could be wrong and
mismatched information among various departments of school.
In order to computerize the manual record keeping system, DBMS was introduced.

15.2 DBMS

A database management system is basically a computer based record system. It is

collection of logically related data items stored together to be manipulated by multiple
applications. The intention of database is that all related data of any organization will be
stored in a central place and all applications will use the data for different purpose.

15.3 NEED FOR DATABASE MANAGEMENT SYSTEM

[Type here]
1. Databases reduce the data redundancy:
The database system do not maintain separate copies of the same data, rather all
the data are kept at one place and all application that require data refer to be the
centrally maintained database. If any change is to be made it will be made at just one
place and the changed information will be available to other applications. Duplication
of the data is known as data redundancy.

2. Database can control data inconsistency:

Incorrect data is inconsistent data.
When data redundancy is not controlled, there may be occasions on which the two
entries of the same data do not agree i.e. when one of them stores the updated
version and the other does not. In that case a database is said to be inconsistent. By
controlling the data redundancy, inconsistency is also controlled.

3. Database facilitates sharing of data:

In a database system all the users handling different related applications refer and
share the same data stored in a central location.

4. Database enforce standard

Database management system can ensure that all the data follow the applicable
standard laid by the organization. For example an organization can enforce the rule
that all its employees must be a graduate.

5. Integrity can be maintained:

When a database contains data to be used by many users it is important to keep
integrity to check to insure that data values satisfy to certain specified rules for
example and invalid dates should not to allowed to entered as 31/02/96

6. Database can ensure security:

A database ensures security and privacy by ensuring that only the authorized person
can access the database.

Database Schema:
Overall design of database is called schema. It is skeleton
structure of database. It is also
the logical view of database.
3 types of schema
1. Logical schema
2. Physical Schema
[Link] Schema

Logical Schema: This describes database design at logical level.

Physical schema: Database design at physical level
[Type here]
View schema : Database at view level. Example Queries.

15.4 FEATURES OF DBMS

DBMS is a set of programs which build and manage the database. A DBMS provides the
facilities of
- Defining structure of data storage i.e. creation of database
- Organize the database i.e. arranging it ascending/descending order
- Providing mechanism for data manipulation i.e. manipulation of database to retrieve
specified data according to a condition.
- Updating of database against all changes i.e. addition of new records, editing /
deleting data items etc.
- Providing data security against unauthorized access.

15.5 MAIN OBJECTIVES OF DBMS

- Provide an efficient and convenient environment for creation and manipulation of

database.
- Manage the database and the activities that the user can perform on the data.

15.6 RELATIONAL DATA MODEL

In Relational data model, the data is organized into tabular form i.e. in rows and columns
and is known as Table. Table is also known as relation. A row in a table represents a
relationship among a set of values. Since a table is a collection of such relationships, it is
also referred to as a Relation.
Eg. of a sample Database represented in a relational form can be
Relation: Student

Admissinno Rollno Name

S001 1 Ramesh
S002 2 Heena
S003 3 Ronak

A Relational database management system (RDBMS) can be defined as a set of

programs where all logically related data items are organized strictly as tables(relation)
and help the user to perform all database operations (creation, updating, organization etc.)
on these tables .

[Type here]
15.7 TERMS USED IN RELATIONAL DATA MODEL

15.7.1 Relation:
The organizing principle in a relational database is a table where data is arranged in rows
and columns. Each table in a database has a unique table name that identifies its
contents. Table is a relation.
15.7.2 Domain
A domain is a valid set of values from which one or more column draw their actual values.
For example for the above relation student the domain is -

1. Ramesh 2. Heena 3. Ronak

15.7.3 Tuple

The rows or records of tables are generally referred to as Tuples. For example the
following is a tuple from the student table:
S002 2 Ronak

Consider the table given below, row marked in red is 1 record.

15.7.4 Attributes

The columns or fields of tables are generally referred to as attributes. Every column
(attribute) in a table must have a unique name. In the above given student table rollno,
name are two attributes.

Consider the example given below.

[Type here]
15.7.5 Degree
The number of attributes (columns) in a relation is called the degree of a relation. A
relation having 3 attributes is said to be of degree [Link] the above given table Student, the
degree is 5.

15.7.6 Cardinality
The number of tuples (rows) in a relation is called cardinality of the relation. In table
Student the cardinality is 8.

15.7.7 Keys
These in general, refer to the column(s) which specify how rows of a relation are distinct
from each other. The different types of keys which are used in relational algebra are
explained below:
Admissinno Rollno FName Lname
S001 1 Ramesh Singh
S002 2 Heena Sufi
S003 3 Ramesh Kumar
S004 4 Ram Prasad

i) Primary Key:
A primary key is a set of one or more attributes that can uniquely identify the rows
within a table. For eg. in table student, the column rollno contains unique values 1
and 2 to identify the two rows. The primary key is non redundant i.e. it does not have
duplicate values in the same table.

ii) Composite Key:

When the combination of 2 or more attribute is used as a primary key, that is known
as the composite key.
Example firstname and second name together r used as primary key.
[Type here]
 In certain tables, a single attribute cannot be used to identify rows uniquely and
combination of two or more attributes is used as Primary Key. Such keys are called
Composite Keys.

iii) Candidate Key:

All attributes in a table that can become Primary Key are candidate keys. For e.g. in
the relation student both admission no and roll no can serve as primary key.
iv) Alternate Key:
A candidate key that is not the Primary key is called an Alternate key. For e.g. in a
student relation if rollno is chosen as primary key, the admissionno becomes the
alternate key.
v) Foreign Key:
A foreign key is a key which is a primary key in some other table. It is used to link
two tables together.
Consider the tables given below.

TableName : Persons

PersonID LastName FirstName Age

1 Gandotra Preeti 30

2 Sujoy Deepak 23

3 Singh Sohan 20

[Type here]
TableName : Orders

OrderID OrderNumber PersonID

1 77895 3

2 44678 3

3 22456 2

4 24562 1

• Notice that the "PersonID" column in the "Orders" table points to the "PersonID"
column in the "Persons" table.
• The "PersonID" column in the "Persons" table is the PRIMARY KEY in the
"Persons" table.
• The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders"
table.

15.8 SOLVED QUESTIONS

1. Identify Relation, Domain, Attribute, Degree, Cardinality, Tuples and Primary key in
below table named Product.

[Type here]
 Ans:
Relation: Product
Domain: Any set of valid values
Atribute:id,quantity
Degree:5
Cardianality:8
Tuple:1 record
Key: id ,product_id
2. Consider the table (Student) given below.

Rollno Name Class Section Gender Address

14 Rohan 12 D M Dubai

45 Arpita 11 A F Sharjah

23 Kush 12 G M Dubai

[Type here]
 What is degree and Cardinality of this table?

Ans :
Degree : 6
Cardinality : 3

3. Consider the table given below.

Roll no Name Address Mobileno Rfid no

12 Rahul V Dubai 050- s456

1234567

5 Rohan Dubai 050- s923

9562411

18 Rahul S Sharjah 055- s121

4598125

Identify Primary Key, Alternate Key and Candidate Key in the above table.

Ans:

Rollno , Mobileno , Rfidno – Candidate Keys

Rfidno - Primary Key
Mobileno , Rollno – Alternate key

[Type here]
15.9 THE RELATIONAL ALGEBRA
The relational algebra is a collection of operations on relations or tables. Each operation
takes one or more relations and produces another relation as its result. Some of the
operations include selection, projection, union, Cartesian Product etc.

15.9.1 SELECTION
The select operation selects rows (Horizontal subset) from a relation that satisfy a
given condition.

Relation – item
Item No Item-Name Price
I1 Milk 15
I2 Cake 5
I3 Bread 9
I4 Milk Bread 14
I5 Buiscuits 6

For a condition price > 14.00, the result could be

ItemNo Item-Name Price

I1 Milk 15

15.9.2 PROJECTION
The project operation lets the user select specified columns from given relation.

Relation Supplier

SuppNo Supp Name City

S1 Britannia Delhi
S2 New Bakers Mumbai
S3 Mother Dairy Delhi
S4 Kitkat Banglore

For specified attributes Suppno. and city, the result would be

SuppNo Supp Name

[Type here]
 S1 Britannia
S2 New Bakers
S3 Mother Dairy
S4 Banglore

15.10 SQL COMMANDS

SQL stands for structured query language. SQL is a non-procedural language that is used
to create, manipulate and process the databases (relations).

15.10.1 CHARACTERISTICS OF SQL

✓ It is very easy to learn and use.

✓ Large volume of databases can be handled quite easily.
✓ It is non procedural language. It means that we do not need to specify the
procedures to accomplish a task but just to give a command to perform the activity.
✓ SQL can be linked to most of other high level languages that makes it first choice
for the database programmers.

15.11 CATEGORIES OF SQL COMMANDS

The SQL commands are categorized into:

15.11.1 DATA DEFINITION LANGUAGES (DDL)

This is used to create database and define the physical characteristics of a database
like determining each field’s name, its data type and its length within a table and
modifications like changing the length of a field, adding a new field etc. Commands to
create table, delete table, alter table etc come under this category.
Example : create table, drop table, alter table.
15.11.2 DATA MANIPULATION LANGUAGES (DML)
This category of commands enables the users to perform operations like retrieval
insertion, deletion and modification of data.
Example: select, insert, update, delete.
Commands to retrieve data, insert and delete rows, update the details etc. come under this
category.

[Type here]
15.12 SQL DATA TYPE
Data types are declared to identify the type of data stored for a particular column or
variable.
Just like any other programming language, the facility of defining data of various types is
available in SQL also.

Following are the most common data types of SQL.

1. NUMBER(Integer, short int, long int, float)
2. CHAR (fixed length)
3. VARCHAR / VARCHAR2- (variable length, memo)
4. DATE-(date, date/time, time stamp)
5. RAW/LONG RAW

Note: SQL data types differ from one RDBMS to another. Here the idea is to brief
you about most common data types.

Let’s go through each data type in detail.

1. NUMBER
Used to store a numeric value in a field/column. It may be decimal, integer or a real value.

General syntax is:

number(n,d)

Where n specifies the number of digits and d specifies the number of digits to the right of
the decimal point.
e.g : marks number(3) declares marks to be of type number with maximum value 999.

pct number(5,2) declares pct to be of type number of 5 digits with two digits to the right of
decimal point.
However, please note that int, integer, float can also be used as datatypes. they belong
to numeric datatype.
Infact int and float are used most commonly in SQL when dealing with numeric values.

[Type here]
2. CHAR

Used to store character type data in a column.

General syntax is
Char (size)

Where size represents the maximum number of characters in a column. The CHAR type
data can hold at most 255 characters.
e.g. name char(25) declares a data item name of type character of upto 25 size long.
Remember char is a fixed length data type, this means that by declaring a field as char,
those many number of bytes will be reserved.
If data used is lesser than that, then the reserved bytes go waste.

3. VARCHAR/VARCHAR2

This data type is used to store variable length alphanumeric data.

General syntax is,

varchar(size) / varchar2(size)

Where size represents the maximum number of characters in a column. The maximum
allowed size in this data type is 65535 characters.

e.g address varchar(50);

address is of type varchar of upto 50 characters long.

Difference between char and varchar

1. VARCHAR is variable length, while CHAR is fixed length. CHAR is a fixed

length string data type, so any remaining space in the field is padded with
blanks. CHAR takes up 1 byte per character. ... VARCHAR is a variable
length string data type, so it holds only the characters you assign to it.

2. The length of a char variable can be of any value from 0 to 255, while the
length of varchar variable ranges from 0 to 65,535.

4. DATE

Date data type is used to store dates in columns. Standard date format is ‘yyyy-mm-dd’
e.g. dob date; declares dob to be of type date..

Date values are written using single quote.

5. LONG
This data type is used to store variable length strings of upto 2 GB size.
e.g . description long;

6. RAW/LONG RAW
[Type here]
To store binary data (images/pictures/animation/clips etc.) RAW or LONG RAW data type
is used. A column LONG RAW type can hold upto 2 GB of binary data.
e.g image raw(2000);

15.13 SQL QUERY LANGUAGE

15.13.1 CREATE TABLE COMMAND

Table in SQL are defined using the Create Table command. This command creates an
empty table i.e a table with no rows.
Syntax: Create Table <table-name>(<column name1> <data type> <size>,
<column name2> <data type> [(<size>]...);

Eg :
CREATE TABLE STUD(RNO INT(5) , NAME VARCHAR(20) , SECTION CHAR(1) , DOB
DATE , MARKS NUMERIC(5,2) );

Note : SQL commands are not case sensitive.

15.13.3. ENTERING VALUES USING INSERT INTO

Insert into command adds new rows (records) to a table.

Syntax Insert into <table name> values(<field name1>, <field name2, ....]);
Example Insert into salary values (‘1’, ‘Raju’, 12000, ,‘Acct’,’12-Jan-79’);

While providing data items for char and date fields, enclose the data item in single quotes.

15.13.4 DIFFERENT METHODS OF INSERTING VALUES

METHOD I – Inserting all values

INSERT INTO EMPLOYEE VALUES (1, ‘ROHAN’ , ‘M’, 6500, 150, ’SHARJAH’,
0501234567, ’MANAGER’, ’D001’ ,’CRICKET’, 9);

METHOD II – Inserting selected values

INSERT INTO EMPLOYEE (ENO,ENAME,GENDER,SAL,BONUS) VALUES

(2,’RATUL’,’M’,5500,125);

METHOD III -For inserting a row interactively (from keyboard) & operator can be
used.

INSERT INTO EMPLOYEE VALUES

(&ENO,’&ENAME’,’&GENDER’,&SAL,&BONUS,’&ADDRESS’,&MOBILENO,’&DESIGNAT
ION’,’&DEPTNO’,’&HOBBIES’,&WORKDUR);
[Type here]
In the above command the values for all the columns are read from keyboard and inserted
into the table student.

15.13.5 SELECT COMMAND

Select command retrieves selected data from tables on the basis of query.
A query is a command that produce specified information from the database.

15.13.5 DIFFERENT VARIATIONS OF SELECT COMMAND

[Link] the whole table

Syntax Select * From <table name>;

Example Select * from salary;
will display all the records from the table. This is the example of selection.
2. Selecting specific columns

Syntax Select <Column name>, <Column name>.. From <table name>;

Example Select Empno, Name, Basic from salary;

will display empno, name and basic columns from the table salary. This is the example of
projection.
3. Selecting specific rows using Where

Where clause is used output only those rows, which satisfy the specified condition. You
can use any relational operator (>,<,>=,<=,<>) to form a condition.

Syntax Select * from <table name> where <condition>;

Example Select * from salary where Dept =’Acct’;

Select empno, name from salary where basic>12000;
Select * from salary where dob>’01-Jan-79’;

The query can also be done using multiple conditions connected by Logical operators
(And, Or and Not)
Example Select * from salary where Dept =’Acct’ AND Basic>10000;
Select * from salary where Dept =’Acct’ OR Dept =’Sys’;
Select * from salary where NOT(Dept =’Acct)’;
4. Select with Distinct

The Distinct key word with Select statement is used to remove duplicate rows from the
result of a select statement. Distinct clause can be specified only once in a Select
statement.

Example Select distinct dept from salary;

It will display all distinct department names.

[Type here]
5. SELECT to specify condition based on a list - using IN operator

The IN operator is used to specify a list of values. This operator selects values that match
any value in a given list of values.
Example to display employee name, dept and basic whose dept is either accts, admn or
marketing from table salary, the command would be:

SELECT NAME, dept, basic

FROM salary
WHERE dept IN (‘accts’, ‘admn’, ‘marketing’);
[Link] to specify condition based on a RANGE using BETWEEN clause

The BETWEEN operator defines a range of values that the column value fall in ,to make
the condition True. The range includes both lower and upper values.
For example, To display names and basic of employees whose basic is greater than or
equal to1200 and less than or equal to 1500, the command would be:
SELECT name, basic
FROM salary
WHERE basic BETWEEN 1200 AND 1500;
The operator NOT BETWEEN is reverse of BETWEEN operator, that is the rows not
satisfying the BETWEEN condition are retrieved and the operator NOT IN finds rows that
do not match in the list.
Example1: SELECT * FROM salary
WHERE basic NOT BETWEEN 2000 AND 3000;
Example 2 : SELECT empno, name FROM salary
WHERE dept NOT IN (‘accts’,’marketing’) ;

7. Select with calculated result

Calculated result can also be displayed using expression with mathematical operator in
select statement
Example Select empno, 0.012*basic from salary;

will display calculated values(12% of basic) with empno from salary table.

8. Putting text in query

Select name ||‘is in ’|| dept ||’dept’|| from salary;

Will print each record in the format

raju is in acct dept.

9. Using Column Alias

[Type here]
When displaying the result of a query the name of the selected column is usually displayed
as column heading. To change the column heading for a more descriptive name column
alias can be used.
Example Select empno, 0.012*basic Bonus from salary;

In this case the calculated result will be displayed under the heading Bonus which is given
as Alias(Alternative Name). When alias has more than one word it has to be given in
double quotes.

Example Select empno, 0.012*basic “Calculated Bonus” from salary;

10. Using Like Operator

SQL provides two wild card characters that are used while comparing the strings with LIKE
operator.
a. percent ( % ) Matches any string
b. Underscore ( _ ) Matches any one character

e.g SELECT Roll_no, name, city FROM student WHERE Roll_no LIKE “%3”;
Above query will displays those records where last digit of Roll_no is 3 and may have any
number of characters in front.

e.g SELECT Roll_no, name, city FROM student WHERE Roll_no LIKE “1_3”;

displays those records whose Roll_no starts with 1 and second letter may be any letter but
ends with digit 3.

15.13.6 SORTING THE SELECTED RECORDS BY - ORDER BY

In general, the rows displayed from a query do not have any order ascending or
descending. The Order by clause allows sorting of query result by one or more columns.
In a SQL statement the ORDER BY clause has to appear at the last.

Syntax Select * from <table name> WHERE <condition> ORDER BY <the column to
sequence> <ASC/DESC>;
In this command, Order by will sort the rows in the resultant table on the column attribute
(values) you specify.

Example Select * from salary WHERE Basic > 2500 order by Name;

So Now all the records will be displayed where Basic is greater than 2500 and in
alphabetically increasing order of name.

Ordering of selected record can also be performed on multiple attributes.

Example Select * from salary order by dept asc, gross desc;

So now in this case, all records will be displayed from table salary in descending order of
gross. And within gross, It will be sorted on the basis of department in ascending order.

[Type here]
15.13.7 UNDERSTANDING VARIOUS SELECT COMMANDS WITH THE HELP
OF TABLE

Consider the table given below:

Roll_no Name Class Marks City

101 Rohan XI 400 Chennai

102 Aneeta XII 390 Bengaluru
103 Pawan Kumar IX 298 Mysore
104 Rohan IX 376 Mangalore
105 Sanjay VII 240 Mysore

113 Anju VIII 432 Delhi

Select count(name), city from student group by city;

Count(name ) city
1 chennai
2 mysore
1 bangluru
1 manglore
1 delhi

Query1 : SELECT * FROM student WHERE Roll_no <=102;

Result :

Roll_no Name Class Marks City

101 Rohan XI 400 Chennai

102 Aneeta XII 390 Bengaluru

Query 2 : SELECT name, class FROM student;

Result :

Name Class

Rohan XI
Aneeta XII
Pawan IX
Kumar

[Type here]
Rohan IX
Sanjay VII

Anju VIII

Query 3: SELECT DISTINCT name FROM student;

Name

Rohan
Aneeta
Pawan
Kumar
Sanjay

Anju

Query 4 : SELECT Roll_no, name FROM student WHERE Roll_no BETWEEN

100 AND 103;

Result:
Roll_no Name Class Marks City
101 Rohan XI 400 Chennai
102 Aneeta XII 390 Bengaluru
103 Pawan IX 298 Mysore
Kumar

Query 5: SELECT * FROM student WHERE city IN

‘Bengaluru’,’Delhi’,’Chennai’);

Result:

Roll_no Name Class Marks City

101 Rohan XI 400 Chennai
102 Aneeta XII 390 Bengaluru
113 Anju VIII 432 Delhi

Query 6: SELECT * FROM student WHERE NAME LIKE ‘A%’ ORDER BY

marks;

[Type here]
Result:

Roll_no Name Class Marks City

102 Aneeta XII 390 Bengaluru
113 Anju VIII 432 Delhi

Query 7: SELECT * FROM student ORDER BY City, Roll desc;

Result:

Records are arranged in increasing order of city and when two students have
same city then it is in order of roll no.

Roll_no Name Class Marks City

102 Aneeta XII 390 Bengaluru
101 Rohan XI 400 Chennai
113 Anju VIII 432 Delhi
104 Rohan IX 376 Mangalore
103 Pawan IX 298 Mysore
Kumar
105 Sanjay VII 240 Mysore

15.13. 8 ALTERING AN EXISTING TABLE

Alter Table command is used to modify the structure of an existing table. Alter table
command can be used to add, modify and remove column (attribute in an existing table).

Syntax Alter Table <table name>ADD (<column name> <data type> <size>,…);
will add new column to the existing table.

Alter Table <table name>modify (<column name> <data type> <size>,..);

Note: Data type can be changed only when the column is empty and the size
can only be increased.

Alter Table <table name>drop column <column name>;

will remove the specified column.

Example Alter Table salary add (dept char(10), gross decimal(14,2));

will add columns dept and gross in salary table.

[Type here]
 Alter Table salary modify (basic number(12,2));
will modify the size of basic to 12,2

Alter Table salary drop column gross;

will remove column gross

15.13.9 UPDATE COMMAND

Update is the SQL verb that changes or modifies data values in a table.

Syntax Update <tablename> SET <columnname> = < arithmetic expression>

WHERE <condition>;

Example UPDATE Emp SET gross = Basic + 2000 WHERE Department = ‘Acct’;

15.13.10 DELETE COMMAND

You can remove rows from table using delete command.

Syntax DELETE FROM <tablename> WHERE <condition>;
Example DELETE FROM salary WHERE dept=’Acct’;

15.13.11 DROPPING A TABLE

DROP is the SQL verb used to delete tables if the table is empty and is created earlier
using the SQL CREATE command.
Syntax DROP table<table-name>;
Example DROP table salary;

15.13.12 AGGREGATE FUNCTIONS

Aggregate functions produce a single value for an entire group of table entries. Aggregate
or column functions can be used with the SELECT command to calculate summary values
from the data in a particular column of a table. These functions are SUM(), AVG(), MAX(),
MIN() and COUNT().
1. Sum()

Sum() calculates the arithmetic SUM of all selected values of a given column holding
numeric entries.
Example Select SUM (Basic) from salary;
2. Avg()

Avg() calculates the average (mean) of all selected values of a given field holding numeric
entries.
Example Select avg (Basic) from salary where dept=’ACCTS’;
3. Max()

MAX function calculates the largest of all selected values of a given column.
Example Select max (Basic) from salary;

[Type here]
4. Min()

MIN function calculates the smallest of all selected values of a given column.
Example Select min(basic) from salary;
[Link]()
Count() counts the number of rows in the output table. Count()with the asterisk(*) counts
the number of rows in the result table.
Example select count(*) from salary;
Will count the number of records present in a table.

Select count(*) from salary where dept=’acct’;

Will count the number of employee records who are in acct dept.

Consider the above table student and answer the following queries.
FEW MORE COMMANDS
1. Altering THE STRUCTURE OF TABLE- ADDING A NEW
COLUMN;
ALTER TABLE STUDENT ADD REMARKS VARCHAR(20);
2. ALTERING THE STRUCURE OF TABLE, MODIFYING ALREADY EXISTING
COLUMN.
ALTER TABLE STUDENT MODIFY AVGMARKS FLOAT;
3. CHANGE THE STIPEND OF KAVITA TO 550
UPDATE STUDENT SET STIPEND = 550 WHERE NAME = ‘KAVITA’

4. ADD A NEW ROW WITH VALUES (2, “TEVIN”)

INSERT INTO STUDENT( ROLL, NAME) VALUES(2, “TEVIN”);

[Type here]
Points to Remember

1. A database management system is basically a computer based record

system.
2. The intention of database Management System is that all related data of
any organization will be stored in a central place and all applications
will use the data for different purpose.
3. DBMS reduces data redundancy.
4. In a database system all the users handling different related
applications refer and share the same data stored in a central location.
5. Database management system can ensure that all the data follow the
applicable standard laid by the organization.
6. DBMS maintains data integrity.
7. DBMS ensures security and privacy of data by ensuring that only the
authorized person can access the database.
8. In Relational data model, the data is organized into tabular form i.e. in
rows and columns and is known as Table.
9. RDBMS can be defined as a set of programs where all logically related
data items are organized strictly as tables.

[Type here]
 10.A domain is a set of values from which one or more attribute (column)
draw their actual values.
[Link] rows or records of tables are generally referred to as Tuples.
[Link] columns or fields of tables are generally referred to as attributes.
[Link] number of attributes (columns) in a relation is called the degree of
a relation.
[Link] number of tuples (rows) in a relation is called cardinality of the
relation.
[Link] in general, refer to the column(s) which specify how rows of a
relation are distinct from each other.
16.A primary key is a set of one or more attributes that can uniquely
identify the rows within a table.
[Link] certain tables, a single attribute cannot be used to identify rows
uniquely and combination of two or more attributes is used as Primary
Key.
[Link] attributes in a table that can become Primary Key are candidate
keys.
19.A candidate key that is not the Primary key is called an Alternate key.
20.A foreign key is a key which is a primary key in some other table.
[Link] select operation selects tuples (Horizontal subset) from a relation
that satisfy a given condition.
[Link] project operation lets the user select specified attributes or column
from given relation.
[Link] stands for structured query language. SQL is a non-procedural
language that is used to create, manipulate and process the databases
(relations).
[Link] is used to create database and define the physical characteristics
of a database like determining each field’s name, its data type and its
length within a table and modifications like changing the length of a
field, adding a new field etc.
[Link] enables the users to perform operations like retrieval of
information in the database, insertion of new information into the
database, deletion and modification of data stored in the database.
[Link] types are declared to identify the type of data stored for a
particular column or variable

[Type here]
 [Link] is used to store a numeric value in a field/column. It may be
decimal, integer or a real value.
[Link] is used to store character type data in a column.
[Link] is used to store variable length alphanumeric data.
[Link] is variable length, while CHAR is fixed length. CHAR is a
fixed length string data type, so any remaining space in the field is
padded with blanks. CHAR takes up 1 byte per character.
[Link] data type is used to store dates in columns.
[Link] store binary data (images/pictures/animation/clips etc.) RAW or
LONG RAW data type is used. A column LONG RAW type can hold
upto 2 GB of binary data.
[Link] in SQL is created using the Create Table command. This
command creates an empty table i.e a table with no rows.
[Link] are the conditions that can be enforced on the attributes of
a relation.
[Link] Null ensures that we cannot leave a column as null. A value has to
be supplied for that column.
[Link] constraint means that the values under that column are always
unique and cannot be repeated.
[Link] Key constraint is used to set a primary key. This constraint
means that a column cannot have duplicate values and not even a null
value.
[Link] Key constraint is used to enforce referential integrity and is
declared as a primary key in some other table.
[Link] constraint limits the values that can be inserted into a column of
a table.
[Link] constraint is used to specify a default value to a column of a
table automatically.
[Link] command retrieves selected data from tables on the basis of
query.
[Link] clause is used output only those rows, which satisfy the specified
condition.
[Link] Distinct key word with Select statement is used to remove duplicate
rows from the result of a select statement.
[Link] IN operator is used to specify a list of values. This operator selects

[Type here]
 values that match any value in a given list of values.
[Link] BETWEEN operator defines a range of values that the column
value fall into. The range includes both lower and upper values.
[Link] operator NOT BETWEEN is reverse of BETWEEN operator, that is
the rows not satisfying the BETWEEN condition are retrieved and the
operator NOT IN finds rows that do not match in the list.
[Link] displaying the result of a query the name of the selected column
is usually displayed as column heading.
[Link] Table command is used to modify the structure of an exist¬ing
table.
[Link] is the SQL verb that changes or modifies data values in a table.
[Link] is the SQL verb used to delete tables if the table is empty and is
created earlier using the SQL CREATE command.
[Link] functions produce a single value for an entire group of table
entries.
[Link]() calculates the arithmetic SUM of all selected values of a given
column holding numeric entries.
[Link]() calculates the average (mean) of all selected values of a given
field holding numeric entries.
[Link]() function calculates the largest of all selected values of a given
column.
[Link]() function calculates the smallest of all selected values of a given
column.
[Link]() counts the number of rows in the output table. Count()with the
asterisk(*) counts the number of rows in the result table.

[Type here]
Solved Examples
1.
Create a table Empdets with the following specification
Emp_No Char(4) (fixed datatypes)
Emp_name varchar(20)
Department varchar (10)
Salary float(10,2)
Experience int(2)
Doj Date
To create the table, command is -
Create table Empdets (Emp_No Char(4), Emp_name varchar(20), Department
varchar(10), Salary float(10,2), ,experience int(2), Doj date);
Table : Empdets
Emp_no Emp_name Department Salary Experience Doj
E01 Ahmed Accts 15000 3 2-Jan-05
Hussain

[Type here]
 E02 Ravinder System 35000 5 12-Feb-08
Singh
E03 Jully Bhatt System 20000 5 12-Jan-09
E04 Shiva Accts 15000 7 12-July-09
Mathur
E05 Sonia Jolly Accts 12000 3 1-Dec-06
E06 Sumita Purchase 18000 10 12-Nov-04
Singh

i) To display all employee names in descending order of salary.

Select emp_name,salary from emp_dets order by salary desc;
(ii) Display the no. of employee whose salary > 15000 and experience > 5 years.
Select count(*) from emp_dets where salary>15000 and experience>5;
(iii) Increase the salary of all employees of Accts Department by 1000.
Update emp_dets set salary= salary+1000 where department=’Accts’;

(iv) Delete the employee details whose experience is less than 3 yr.
Delete from emp_dets where experience<3;
(v) Insert a new row in empdets with details ‘E02,Ajay,Purchase,15000,3,12-Oct-
2005’.
Insert into empdets values (‘E02’,’Ajay’,’Purchase’,15000,3,’12-Oct-2005’);
(vi) Include a new column month_no (INT(2)) in empdets table.
Alter table empdets add(month_no int(2));
(vii) Count the number of employees who have joined after 1st January 1998
Select count(*) from emp_dets where doj>’1998-01-01’;.
(viii) Display the maximum and minimum salary of accts department.
Select max(salary), min(salary) from emp_dets where department=’Accts’;

Answer

a. Select Emp_name from empdets order by salary desc;

b. Select count(*) from empdets where salary>15000 and experience>5;
c. Update empdets set salary=salary+1000 where department=’Accts’;
d. Delete from empdets where experience<3;
e. Insert into empdets values (‘E02’,’Ajay’,’Purchase’,15000,3,’12-Oct-
2005’);
f. Alter table empdets add(month_no int(2));
g. Select count(*) from empdets where doj>’1998-01-01’;
h. Select max(salary),min(salary) from empdets where
department=’Accts’;

[Type here]
more Questions on empdets table
1. select the details of all people whose salary is between
15000 and 25000.

Select * from emp_dets where salary between 15000 and 25000;

[Link] the details of all people whose department is either

accts or purchase.

Select * from emp_dets where department in(‘accts’,’purchase’);

[Link] the annual salary of each employee.

select name, salary*12 as 'annual salary' from emp_dets;

4. display the name and salary in ascending order of salary.

Select name, salary from emp_dets order by salary;

5. select name and salary of people whose name is either ajay

or department is purchase;

select name, salary from emp_dets where name =’Ajay’ or department=’Purchase’;

6. select name and department of people whose name begins with A.

select name, department from emp_dets where name like 'A%';

DBMS – Database management system

RDBMS- Relational Database Management system

Relation means table.

Question: 2

create table student (rollno int, name varchar(20),stipend int, stream varchar(20),avgmarks
int, grade char(1),class int);

insert into student values(1,’Kavita’,450,’Medical’,78,’B’,12);

Relation: STUDENT
Rollno Name Stipend Stream Avgmarks Grade Class
1 Kavita 450 Medical 78 B 12
2 Diva 400 Commerce 45 D 11
3 Anir udh 299 Humanities 67 C 11
4 Vikash 550 Commerce 74 B 12
5 Mandeep 650 Medical 89 A 11
11 Dhruva 1200 Medical 99 A 12

[Type here]
1. Display list of all t he commerce stream students from the table.

Select * from STUDENT where stream=”Commerce”;

2. List the names of students who ar e in grade 12 sorted by stipend in
ascending order.

Select Name from STUDENT where class=12 orderby Stipend Asc;

[Link] the details of all students sorted by Avgmarks in descending order

Select * from STUDENT orderby Avgmarks Desc;

4. Display a report listing name, stream and stipend received in a year

assuming that st ipend is paid every mont h.

Select Name, Stream, Stipend*12 as ‘ANNUAL STIPEND’ from

STUDENT;

5. To display details of students whose name starts with ‘V’.

Select * from STUDENT where Name like ‘V%’;

6. To display the names that contain a or u.

Select Name from STUDENT where Name like ‘%a %’ or %u%’;

7. Display the details of students where 2 n d char acter is a , 4 t h character is

s and name has exactly 5 characters.

Select * from STUDENT where name like ‘_a_s_’;

8. Select the count of students who score grade C .

Select count(*) from STUDENT where score=’ C’;

[Link] sum of aver age marks of student s where gr ade is B .

Select sum(marks) from STUDENT where Grade =’B’;

[Link] the sum of stipend of all students;

Select sum(Stipend) from STUDENT;

11. display the recor ds of all students who are eit her in medical or
commerce field.

Select * from STUDENT where stream I N(‘Medical’,’Commerce’);

[Link] the recor ds of all students whose aver age marks r in the range
80 to 90.

Select * from STUDENT where marks BETWEEN 80 and 90;

[Type here]
 13. display the details of all students whose 3 r d character of the name is v.

Select * from student where name like ‘_ _ v%’;

14. update the recor d of student kavita. Set his marks as 99.

Update student set marks=99 where name=’kavita’;

15. add a new column remarks.

Alter table student add(remarks varchar(25));

16. Add a new row only in few columns name and roll number . Values are
‘ajay’,25

Insert into student( name,rollno) values(‘ajay’,25);

17.
Display the stream and total marks of each stream.

Select stream, sum( Avgmarks) as ‘TOTAL MARKS’ from STUDENT

group by stream.

Rollno Name Stipend Stream Avgmarks Grade Class

1 Kavita 450 Medical 78 B 12
2 Diva 400 Commerce 45 D 11
3 Anir udh 299 Humanities 67 C 11
4 Vikash 550 Commerce 74 B 12
5 Mandeep 650 Medical 89 A 11
11 Dhruva 1200 Medical 99 A 12

Stream TOTAL MARKS

Medical 266
Commerce 119
Humanities 67

Question: 3

Relation: MOBILE_PHONE

No Name Model SP Act_date Validity Amount

1 Seema Nokia Airtel 2019/12/13 365 3300
2 Ram Nokia JIO 2019/11/15 60 890

[Type here]
3 Reeti Ovo Idea 2018/3/4 180 560
4 Maithali LG Airtel 2019/10/29 30 600
5 Raman I Phone Reliance 2018/05/30 90 900
6 Karan LG Idea 2019/09/18 60 300

1) To display Cname and Amount of customers having “Nokia” model

and “JIO” SP.
Select name,amount from MOBILE_PHONE where model=’Nokia’
and sp=’JIO ’;

2) To display the different types of MSP available.

select distinct SP from MOBILE_PHONE;

To display the details of people with name starting with R.

select * from MOBILE_PHONE where name like 'r%';

3) To modify the record of LG Clients by increasing the amount by 100

and validity by 20 days.
update MOBILE_PHONE set
amount=amount+100,validity=validity+20 where model='lg';

4) To display the records in order of MSP and model in descending

order.
select * from MOBILE_PHNE order by sp,model desc;

5) To delete the record of Raman .

delete from MOBILE_PHONE where name='Raman';

Question: 4
Relation: INSURANCE
Company Type Years Name Coverage
CUF Car 15 Care safe 80000
LIT Life 40 Insured Life policy 200000

[Type here]
HC House 25 House Secure policy 550000
URFree Life 35 Life Insurance 750000
Care Child 25 Future Child 500000

1. Give the name and type of all companies whose coverage is more
than 100000 but less than 700000.
select name,type from insurence where coverage>10000 and
coverage<70000;

2. List the name, company and years of all those whose type is life
and years are more than 20.
select name,company,years from insurence where type='life'
and years>20;

3. List the different types of policies.

select distinct type from insurence;

4. List the name in descending order of coverage.

select name from insurence order by coverage desc;

5. Display the details of all companies whose insurance years range

from 20 to 30 years.
select * from insurence where years between 20 and 30;

[Type here]
Q1 Write an SQL command for the queries given from based on relation BOOK and
issued as shown below:
Table – Book

Book_Id Book_Name Publishers Price Type Quantity

F0001 The Tears First 450 Fiction 5
publishers
T0001 My First C++ EPB 600 Text 12
F0001 Thunder First 1200 Fiction 15
Bolts Publisher
T0001 Data TDH 230 Text 12
Structure
C0001 Fast Cook EPB 100 Cookery 13

(i) To show Book_name, price and val(=price*quantity) of first publisher from the table
book.
(ii) To count records of ‘Text’ type whose quantity is more than 10.
(iii) To display the names and price from books in ascending order of their price.
(iv) To increase the price of all books of EPB publishers by 10%.
(v) To insert a new row in the table issued having the following data ("F0003", 1, 10-
Dec-99).
(vii) To include a new column date_of_purchase of date type in table book.
(viii) Find the total quantity of books of each type.
(ix) Delete the data from the table where book name is ‘Fast Cook’
(x) Add a new column in to table named remarks
(xi) Delete the table.

Q2 Write an SQL command for the queries given from based on a relation CLUB
Mcode Mname Gender Age Fees Type
1 Ashish Male 35 7000 Monthly
2 Anannya Female 25 8000 Monthly
3 Gitika Female 42 24000 Yearly
4 Harshika Female 27 12000 Quarterly
5 Tarun Male 54 6000 Monthly
[Type here]
6 Krish Male 43 4500 Monthly
7 Farida Female 22 500 Guest
8 Jatin Male 51 24000 Yearly
9 Rakshit Male 44 100000 Life
10 Pratap Male 33 12000 Quarterly

(a) To display Mname, Age, Fees of those members of the CLUB whose Fee is
between 6000 to 10000.
(b) To display Mcode, Mname, Age of all female members of the CLUB with Age in
descending order.
(c) To display Mname, Fees of all those members of the CLUB whose Age < 40 and
are Monthly Type members of the CLUB.
(d) To find out the max and min fees of all the members whose age is more than 45.

(e) To change the column length of fees attribute to number (14,2)

Alter table CLUB alter fees(14,2);

(f) To update the fees of all female member to have 7.5% discount

update TRAIN set fees= fees-fees*7.5/100 where Gender='Female';

Q3 Write an SQL command for the queries for the questions given based on a relation
TRAIN shown below:
TrainNO Name Class SeatNo Age Fare
1 ANU I 11 16 700
2 SAGAR AC-CHAIR 34 43 1300
3 SAMIR AC-I 78 18 9000
4 GAURA AC-II 23 17 600
5 KRIPAL I 3 25 1700
6 ANKUR II 6 20 500
7 PRIYA AC-II 2 11 1200

(a) To display the Name and SeatNo. where Class is II and age is more than 20
(b) To display Train No. and Seat No. where the fare is between 900 and 1300
(c) To display list of passengers in ascending order of TrainNo.
[Type here]
(d) To display the highest and lowest amount paid as Fare .
(e) Insert a new row in the above table with the details (9, Ajay, II,19,20,500)
(f) Display all distinct classes
(g) describe the structure of the table.
(h) display all records where name begins with A and has exactly 5 characters.
(i) add a new column named DOB
(j) Delete the records where class is I.
(k) delete the table
(l) display all records where name begins with G and third character is U.

Q4 Write an SQL command for the queries for the questions relation SHOP shown
below:
Relation: SHOP

No. Shopname Sale Area C_perc Rating City

1 S.M. Sons 250000 West 68.6 C Delhi

2 Dharohar 500000 South 81.8 A Mumbai

3 Kriti Art 300000 North 79.8 B Kolkata
4 Ripple 380000 North 88.0 B Mumbai

[Type here]
5 Best 456000 East 92.0 A Delhi
Stores
6 Crystal 290000 South 66.7 A Kolkata

Show databases;
Use test;
Show tables;
a) Show the names of all shops which are in the South area and c_perc < 75
b) To display list of all the shops with sale > 300000 in ascending order of
ShopName.
c) To display Shopname, Area and Rating for only those shops whose sale is
between 350000 and 400000 (including both 350000 and 400000).
d) To count the no. shops whose rating is A
e) To show the sales of all shops whose shopname begin with ‘A’
f) Describe the structure of the table.
g) Update c_perc of all the people whose rating is A by 5%
h) Display the details of shops which have exactly 6 letters in their shopname and 4 th
character is ‘p’
i) add a new column named amount .
j) delete the table.
k) display sales*c_perc as “value” for all the records.

Q5 Write an SQL command for the queries for the questions given from based on a
relation STUDENT shown below:
Relation: STUDENT

No. Name Stipend Stream AvgMark Grade Class

1 Neha 450.00 Medical 89.2 A 11C
2 Damini 400.00 Commerce 78.5 B 12B
3 Gaurav 250.00 Humanities 64.4 C 11A
4 Anu 300.00 Commerce 67.5 C 12B
5 Vikas 500.00 Non- 92.0 A 12A
medical
6 Rubina 450.00 Non- 88.5 A 12A
medical

[Type here]
(a) To display the names of all students who are in Non-Medical Stream and grade is
A
(b) To display list of all the students with Stipend > 350.0 in ascending order of Name.
(c) To display details with Name, TotMarks for each student in the table. TotMarks are
calculated as AvgMark * 5.

Q6 Write SQL command for the queries based on a relation HOSPITAL shown below:
Relatoin : HOSPITAL
No. Name Age Department DateofAdm Charges Gender
1 Arpit 62 Surgery 21-10-98 300 M
2 Zarina 22 ENT 12-12-97 250 F
3 Kareem 32 Orthopedic 19-02-98 200 M
4 Arun 12 Surgery 11-01-98 300 M
5 Zubin 30 ENT 12-01-98 250 M
6 Ketaki 16 ENT 24-02-98 250 F
7 Ankita 29 Cardiology 20-02-98 800 F
8 Zareen 45 Gynecology 22-02-98 300 F
9 Kush 19 Cargiology 13-01-98 800 M
10 Shilpa 23 Medicine 21-02-98 400 F

(a) To select all the information of patients of surgery department.

(b) To count the number of female patients in each department.

(c ) To list names of all patients with their date of admission in ascending order.

[Type here]
(d) To display Patient's Name, Age for only female patients whose age is between 19
and 40.

(e) To count the number of patients with age < 30 and dateofadm is after 12-Jan-98

10. The method used to save graph

a) [Link]()

b) [Link]()

c) [Link]()

d) [Link]()

Unsolved Theory Questions

Q 1 Define the term data.

[Type here]
Q 2 What do you mean by DBMS.
Q 3 What is the need of database management system?
Q 4 What are the features of database management system?
Q 5 What are the objectives of DBMS?
Q 6 What do you know about relational database management system?
Q 7 Define the following terms with an example.
(a) Relation
(b) Domain
(c) Tuple
(d) Attributes
(e) Degree
(f) Cardinality
Q 8 Which are the different keys used in DBMS?
Q 9 Define the term Relational Algebra?
Q 10 Define the terms selection and Projection in DBMS?
Q 11 What are the characteristics of SQL?
Q 12 Define DDL, DML and DCL with example.
Q 13 Which are the most commonly used datatypes available in SQL?
Q14 Explain create table command with example.
Q 15 Which are various constraints available in SQL?
Q 16 What is select Command?
Q 17 What are the different variations of select Command?
Q18 What is the method of sorting the selected records?
Q 19 Explain the alter table command with the help of an example.
Q 20 Explain Update Command with example.
Q 21 Explain Delete Command with example.
Q 22 What is the method of dropping a table?
Q 23 What are aggregate functions? Explain with example.

[Type here]