UNIT – 3

DATABASE MANAGEMENT

MYSQL

It is freely available open source Relational Database Management

System (RDBMS) that uses Structured Query Language(SQL). In
MySQL database, information is stored in Tables. A single MySQL database
can contain many tables.

SQL (Structured Query Language)

SQL is a language that enables you to create and operate on relational
databases, which are sets of related information stored in tables.

ADVANTAGES OF USING DBMS

 Reducing data redundancy – This ensures a consistent database and

efficient use of storage space.
 Aiding multiple views of the data: Database management system
enables different users to have a view of the data from their own
perspective
 Data sharing and security – DBMS limits the accessibility of the data
to the authorized users only. This makes data sharing safer. Moreover
shared data makes it possible to fulfill data requirements with minimal
modifications.
 Centralized data ensures a more effective backup and recovery
process than the conventional file processing systems.
 Data Abstraction: It ensures that the users get an abstract view of the
data without showing implementation details and the physical storage
of the data.

RELATIONAL MODEL TERMINOLOGY

1
1. Relation: A table storing logically related data is called a Relation.
2. Tuple: A row of a relation is generally referred to as a tuple.

3. Attribute: A column of a relation is generally referred to as an attribute.

4. Degree: This refers to the number of attributes in a relation.

5. Cardinality: This refers to the number of tuples in a relation.

6. Primary Key: This refers to a set of one or more attributes that can uniquely identify tuples
within the relation.
7. Candidate Key : All attribute combinations inside a relation that can serve as primary
key are candidate keys as these are candidates for primary key position.
8. Alternate Key: A candidate key that is not primary key, is called an alternate key.

9. Foreign Key : A non-key attribute, whose values are derived from the primary key of
some other table, is known as foreign key in its current table.
CLASSIFICATION OF SQL STATEMENTS
SQL commands can be mainly divided into following categoryes:

DATA TYPES
Data types are means to identify the type of data and associated operations for handling it.
MySQL data types are divided into three categories:
 Numeric
 Date and time
 String types

Numeric Datatype
1. int– used for number without decimal.
2
2. decimal(m,d) – used for floating/real numbers. m denotes the total length of number
and d is number of decimal digits.
Date and Time Datatype
1. date–used to store date in YYYY-MM-DD format.
2. time–used to store time in HH:MM:SS format.
String Datatype
1. char(m)–used to store a fixed length string, m denotes max. number of characters.
2. varchar(m)–used to store a variable length string, m denotes max. no. of characters.
DATABASE COMMANDS
1. VIEW EXISTING DATABASE
To view existing database names, the command is:

SHOW DATABASES;

2. CREATING DATABASE IN MYSQL

For creating the database in MySQL, we write the following command:
CREATE DATABASE <databasename>;
e.g.In order to create a database Student, command is:
CREATE DATABASE Student;

3. ACCESSING A DATABASE
For accessing already existing database,we write:
USE<databasename>;
e.g.to access a database named Student, we write command as:
USE Student;

4. DELETING DATABASE
For deleting any existing database,the command is:
DROP DATABASE <databasename>;

e.g.to delete a database, say student, we write command as:

DROP DATABASE Student;

5. VIEWING TABLE IN DATABASE

In order to view tables present in currently accessed database, command is:
SHOW TABLES;
CREATING TABLES IN MYSQL
Syntax of CREATE TABLE command is:
CREATE TABLE <table-name>(<colname> datatype,<colname> datatype,…);

3
E.g. In order to create table EMPLOYEE given below:
ECODE ENAME GENDER GRADE GROSS
Create table employee (ecode integer, ename varchar(20),gender char(1),grade char(2),gross
integer);
Inserting Data into Table:
Syntax:
insert into <tablename> values(<v1>,<v2>,…);
Or
insert into <tablename>(<column list> )values(<values list>);

Eg: insert into employee values(1001,‘Ravi’,‘M’,‘E4’,50000);

Or
insert into employee(ecode,ename) values (1002,’Meena’);
the left out columns will be filled with null values.

Select Command:

It helps to display the records as per our requirement.

Different forms of select command:

1. Select * from employee;
It displays all rows and columns from the table.

2. Select ecode, ename from employee;

It displays selected columns from the table.

3. For displaying particular rows.

Syntax: select * from <tablename> where <cond>;
Eg. Select * from employee where gender=’M’;

4. ELIMINATING REDUNDANT DATA

The distinct keyword is used to eliminate duplicate records from the table.

Eg. Select distinct (gender) from employee;

5. USING COLUMN ALIASES

The columns that we select in a query can be given a different name, i.e. column alias name for
output purpose.
Syntax: SELECT <columnname>AS column alias ,<columnname>AS column alias
…..FROM<tablename>;
Eg.select ecode as “EMP_Code” from employee;
4
CONDITION BASED ON A RANGE
The BETWEEN operator defines a range of values that the column values must fall into make the
condition true. The range include both lower value and upper value.

e.g.To display ECODE,ENAME and GRADE of those employees whose salary is between 40000
and 50000,command is:
SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE
WHERE GROSS BETWEEN 40000 AND 50000;

NOTE: For displaying records not in the specified range, we have to use not between operator.
CONDITION BASED ON A LIST
The in operator is used to display records based on a list of values.
Eg. To display details of employees who have scored A,B and C grades.
Select * from employee where grade in(‘A’,’B’,’C’);
Note: For displaying records that do not match in the list, we have to use not in operator.

CONDITION BASED ON PATTERN MATCHES

LIKE operator is used for pattern matching in SQL. Patterns are described using two special
wildcard characters: % and _ (underscore)
1. percent(%)– The % character matches any substring.
2. underscore(_)– The _ character matches any single character.
e.g. To display names of employee whose name starts with R in EMPLOYEE table, the command is:
select ename from employee where ename like “R%”;

e.g. To display details of employee whose second character in name is:

select * from employee where ename like ‘_e%’;

SEARCHING FOR NULL

The NULL value in a column can be searched for in a table using IS NULL in the WHERE
clause. E.g. to list employee details whose salary contain NULL, we use the command:
Select * from employee where gross is null;
Note: For listing employees who earn salary, then it is:
Select * from employee where gross is not null;

5
Relational Operators
• To compare two values, a relational operator is used. The result of the comparison is true or
false. Relational Operators recognized by SQL: =, >, <, <=, >=, <> (not equal or !=)

Eg. Select * from employee where ecode <> 1001;

Above query will not display those employee details whose ecode column value is 1001.

Logical Operators- (OR, AND, NOT)

1) To list the employee details having grades E2 or E3.

Select ecode, ename, grade, gross from employee where (grade=‘E2’ OR grade=‘E3’);

2) To list all the employees’ details having grades as ‘E4’ but with gross < 9000.
Select ecode, ename, grade, gross from employee where grade=‘E4’ and gross< 9000;

3) To list all the employees’ details whose grades are other than ‘G1’.
Select ecode, ename, grade, gross from employee where (NOT grade= ‘G1’);

Sorting Results- ORDER BY clause

Results of SQL query can be sorted in a specific order using ORDER BY clause.
The ORDER BY clause allows sorting of query results by one or more columns. The sorting can be
done either in ascending or descending order.

Eg. Select * from emp order by ename;

Above query arranges the records in alphabetical order of ename value. By default order by clause
arranges in ascending order.

TO DISPLAY RECORDS IN DESCENDING ORDER

❖ Select * from employee order by ename desc;

Above query gives output in descending order of ename.
❖ Select * from employee ORDER BY grade DESC, ename ASC;
Above query displays records first in the descending order of grade and within the
same grade,employees are displayed in the ascending order of Ename.

6
SQL AGGREGATE FUNCTIONS:
All the aggregate functions ignore null values except count(*).

Avg – to compute average value

Min – to find minimum value
Max – to find maximum value
Sum – to find total value
Count – to count non-null values in a column
Count( *) – to count total number of rows in a table including null values.

Examples:
Select avg(gross) from employee;
Select min(gross) from employee where deptno= 10;
Select count(*) from emp where gross> 10000;
Select count (DISTINCT gender) from employee;

GROUP BY Clause

GROUP BY clause is used in SELECT statements to display the table contents based on similar
values in a column into groups.

Eg: To calculate the number of employees in each grade, the query is:
SELECT grade, count(*) from employee group by grade;

Placing conditions on Groups HAVING Clause

❖ The HAVING clause places conditions on groups in contrast to WHERE clause that places
conditions on individual rows.
❖ WHERE conditions cannot include aggregate functions but HAVING conditions can do so.

Eg: SELECT avg(gross), sum(gross) from employee GROUP BY grade HAVING grade= ‘E4’ ;

DELETE Command

This command removes rows from a table.

Syntax: DELETE FROM <tablename> [WHERE <cond>];

Eg: To remove all the contents of items table, the query is:
DELETE from items;
Eg: To remove the tuples from employee that have gross less than 20000 is :
DELETE from employee WHERE gross<20000;

7
UPDATE Command

Update Command allows to change some or all the values in an existing rows. Update command
specifies the rows to be changed using the WHERE clause and the new data using the SET
keyword.
Eg. UPDATE employee SET gross= 25000;
The above query sets the gross of all records as 25000.

UPDATE employee SET gross=40000, grade=’A’ WHERE ecode=1001;

The above query changes the gross and grade values for the record with ecode 1001.

ALTER TABLE

ALTER TABLE command is used to change the structure of the existing table. It can be used to add
or drop new columns or modify the existing columns of table.
Eg. 1. Alter table Employee Add comm int;
2. ALTER TABLE Emp MODIFY (ename varchar(60));
3. Alter table emp drop comm;

DROP TABLE:

DROP TABLE command allows to remove a table from database. Once the DROP command is
issued, the table will no longer be available in the database.

Eg. DROP TABLE employee;

INTEGRITY CONSTRAINTS
A constraint is a condition or check applicable on a field or set of fields.
Common types of constraints include:

S.No.Constraints Description
1 NOT NULL Ensures that a column cannot have NULL value
2 DEFAULT Provides a default value for a column when none is
specified
3 UNIQUE Ensures that all values in a column are different
4 PRIMARY KEY Used to uniquely identify a row in the table
5 FOREIGN KEY Used to ensure referential integrity of the data

ADDING CONSTRAINT TO A TABLE

8
ALTER TABLE statement can be used to add constraints to your existing table by using it in
following manner:

Eg: alter table employee add primary key(ecode);

REMOVING CONSTRAINTS FROM A TABLE

Eg: alter table employee drop primary key;

Setting primary and foreign key constraint:

Eg: CREATE TABLE STUDENT(ROLL_NO integer PRIMARY KEY ,NAME

VARCHAR(30),CLASSVARCHAR(3));

CREATE TABLE SCORE(ROLL_NO integer ,MARKS integer, FOREIGN KEY(ROLL_NO)

REFERENCES STUDENT(ROLL_NO));

SQL JOINS

SQL Joins are essential to display data from more than one table. SQL JOIN clause is used to
combine rows from two or more tables, based on a common field between them. SQL provides
various types of joins:
1. Cartesian Product or Cross Join
2. Equi-Join
3. Natural Join.

Cartesian Product (Cross Join)

Cartesian product of two tables is obtained by pairing up each row of one table with each row of
the other table.

❖ The number of columns in the Cartesian product is the sum of the number of columns in
both the tables.
❖ The number of rows in the Cartesian product is the product of rows of the tables.

9
Equi-Join

A join which is obtained by putting a condition of equality on cross join is called an 'equi join'.
We can extract meaningful information from the Cartesian product by placing some conditions in the
statement.
The join in which columns are compared for equality is called equi-join.
In this type of join we put * in the select list therefore the common column will appear twice in the
output.
Example: Consider the 2 tables emp and dept.

On performing equi-join, the result is as follows:

10
Note: We see that deptno column appears twice in output.

Natural Join

• The join in which only one of the identical columns exists is 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.

Example:

Note: We see that deptno column appears only once in output.

11