What is database?

Collection of logically related data that has been recorded, organized and made
available for searching is called Database.

Use of database in real life applications

 Banking
 Online shopping
 Payroll
 Inventory management
 Loan and investment
 Reservation system And many more
What are the needs for a database?

 To manage large amount of data without redundancy (repetition).

 To manage large amount of data with lucidity (without complexity)
 To maintain data uniformly in different places or file
 Easy and convenient retrieval of data from any place or file
 Controlled data sharing at different user level
What is DBMS?
 DBMS stands for Database Management System.
 It is application software that can be used to create and manage database
efficiently.
 it enable user or application to create, store, update, delete and retrieve data
from database itself.
 It allows search and retrieve specific records from database
 It checks and ensures that only valid and accurate data is stored in database.
 Examples of DBMS are MySQL, Oracle, MongoDB, MS Access, MS SQL Server
etc.
What are the advantages of DBMS?

 Reduction in Redundancy: Data in a DBMS is more concise because of the

central repository of data. All the data is stored at one place. There is no
repetition of the same data. This also reduces the cost of storing data on hard
disks or other memory devices.
 Improved Consistency: The chances of data inconsistencies in a database are
also reduced as there is a single copy of data that is accessed or updated by
all the users.
 Improved Availability: Same information is made available to different users.
This helps sharing of information by various users of the database.
 Improved Security: Though there is improvement in the availability of
information to users, it may also be required to restrict the access to
confidential information. By making use of passwords and controlling users’
database access rights, the DBA can provide security to the database.
 User Friendly: Using a DBMS, it becomes very easy to access, modify and
delete data. It reduces the dependency of users on computer specialists to
 perform various data related operations in a DBMS because of its user
friendly interface.
What is RDBMS?
Stands for Relational Database Management System. It was developed by E.F. Codd
at IBM 1970. It is used to organize collection of data as a collection of relations
where each relation corresponds to a table of values. Each row in the table
corresponds to a unique instance of data and each column name is used to interpret
the meaning of that data in each row.

What are different RDBMS Terminologies? Explain.

Relation: a table is called relation. Another way we can say that collection of tuple
is called relation.

Tuple: A row of table is called tuple. In another way we can say that a single record
is called tuple

Attribute: a column of table is called attribute.

Domain: the data type of values in each column is called the Domain.

Keys | Candidate Key | Primary Key | Foreign Key | Alternate

Key
column or a combination of columns which can be used to identify one or more rows
(tuples) in a table is called a key of the table. There are 3 key constraints defined
below:

Candidate key

 A column (attribute) or a group of columns which can be used as the primary

key of a relation is called a Candidate key because it is one of the candidates
available to be the primary key of the relation.
 A Candidate key is an attribute (or set of attributes) that uniquely identifies a
row.
 A Primary Key is one of the candidate keys.
Primary Key

 One of the candidate key is designated as primary key.

 Primary key uniquely identifies tuples in relation.
 only one candidate key can become primary key.
Alternate Key
A candidate key of a table which is not selected as the primary key is called its
Alternate Key.

What is Foreign Key?

 The key which is Primary key in another related table (relation) is called
Foreign Key.
 A relation can have more than foreign keys.
What is MySQL? | Characteristics of MySQL
 MySQL is one of the most popular Relational Database Management System
(RDBMS).
 MySQL was originally founded and developed in Sweden by David Axmark,
Allan Larsson and Michael Widenius, who had worked together since the
1980s.
Characteristics of MySQL

 It is free and Open Source software which does not require to pay for its
usage.
 It is easy to use, quick and reliable.
 It is platform independent software which works on many operating systems
like
 Windows, UNIX, LINUX etc.
 It is compatible with many programming languages including JAVA, C++,
PHP, PERL, etc.
 It can handle large amount of data very efficiently and accurately.
What is SQL?

SQL stands for Structured Query Language. It is developed to manage database.

SQL enable us to create database, insert data, update data as per requirement and
retrieve data.

What are the Categories of SQL Commands ?

All SQL commands are categorized in two types :

 Data Definition Language (DDL)

 Data Manipulation language (DML)
Data Definition Language (DDL)
 DDL is used to define structure and constraints of data
 It also defines key constraints, relationships between tables, and other data
validation constraints.
 Example of DDL commands are:
 CREATE DATABASE – creates a new database
 CREATE TABLE – creates a new table
  ALTER TABLE – modifies a table
 DROP TABLE – deletes a table
Data Manipulation language (DML)
 DML is used to insert, modify and delete data in a database.
 Example of DML commands are:
 SELECT – extracts data from a table
 UPDATE – updates data in a table
 DELETE – deletes data from a table
 INSERT INTO – inserts new data into a table
What are the Datatypes used in MySQL?

Given table shows data types commonly used in SQL.

Data type Use Example

Fixed length character string. n denotes max no of

Char(n) Char(6) : sanjay, harish
characters.

Variable length character string. n denotes max no

Varchar(n) Varchar(10): ram, arjun
of characters.

Date Date as YYYY-MM-DD ‘2022-07-23’

Integer Whole number 45, 4539

Fixed point number. M denotes no of digits stored Decimal(3,2): 34.89

Decimal(m,d)
for values and d denotes no of decimal points. Decimal(4): 2813

Float Real number 456.23

Creating a Database

To create a database we will give CREATE DATABASE command.

Syntax

CREATE DATABASE <database name>;

Example:
Using a database

To open the database to work USE statements is used.

Syntax

USE <databasename>;

Example:

Viewing the current database

To view the name of current database we can use SELECT DATABASE(); command.

Syntax

SELECT DATABASE();

Example:

Create Table command

This command is used to create a new table or relation.

Syntax:

CREATE TABLE<table name>

(
<column 1><data type> [constraint] ,
<column 2><data type>[constraint],
<column 3><data type>[constraint]
);

Note: [Constraint] is optional

Example:

CREATE TABLE Item

(
Itemno char(5) primary key,
Itemname varchar(20),
Rate float NOT NULL,
Qty int
);

Screenshot:

Database Constraints | Not Null | Default | Primary Key |

Unique Key | Foreign Key
Database enforces 5 types of constraints mentioned hereunder:
Not Null Constraint

Sometimes an attribute may not be allowed to leave blank. Hence Not Null
constraints can be associated in this case.

Example:

CREATE TABLE TEACHER

(
Teacher_ID INTEGER primary key,
First_NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT,
);

Screenshot:

Default Constraint

To make a value bydefault for an attribute Default constraint can be used.

CREATE TABLE TEACHER

(
Teacher_ID INTEGER,
First_NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT DEFAULT 20000,
);

Screenshot:
Primary Key

it ensures followings when applied:

Duplicate data is not allowed in the attribute.

Attribute can not be left blank
A relation can have only primary key
Example:

CREATE TABLE EMPLOYEE

(
Emp_ID INTEGER PRIMARY KEY,
Emp_NameVARCHAR(20) NOT NULL,
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT,
Dept varchar(20)
);

Screenshot:

Foreign Key
it ensures relationship between relations. The main purpose of this constraint is to
check that data entered in one relation is consistent with the data entered in
another relation.

Example:

Consider the following tables:

Item (Itemno, Iname, rate)
Order (Ono, Odate, Ino, qty, total)

Here in this example Ino is the foreign key that references Ino of Item table which is
Primary key.

The SQL command for making Ino to foreign key in Order table is as follows:

Create Order
(
Ono int Primary Key,
Odate date,
Itemno int,
Qty int,
Total int,
FOREIGN KEY (Itemno) REFERENCES Item (Itemno)
);

Screen Shot:

Unique Key

it ensures followings when applied:

Duplicate data is not allowed in the attribute.

A relation can have multiple unique keys
Example:
CREATE TABLE EMPLOYEE
(
Emp_ID INTEGER PRIMARY KEY,
Emp_NameVARCHAR(20) NOT NULL,
Gender CHAR(1),
Date_of_Birth DATE,
Salary FLOAT,
Dept varchar(20) UNIQUE
);

Screenshot:

Viewing Structure of table | DESCRIBE command

 We can use DESCRIBE or DESC command to view the structure of a table as

mentioned in CREATE TABLE command.
 It display table structure including attributes, data types and constraints .
Syntax:

DESCRIBE <table name>;

Or
DESC <table name>;

Example:

DESCRIBE EMPLOYEE;

ScreenShot
Drop table

This command is used to delete a table completely from database. It deletes

structure and data both of table from database.

Syntax:

DROP TABLE <table name>;

Example:

DROP TABLE Orders;

How to modify structure of table? | ALTER TABLE Command

in SQL
Alter table

It is used to modify structure of a table. Following are the alteration can be done
using this command:

Adding a column

we can add a new column in existing table.

Example:

ALTER TABLE Item ADD date_of_purchase date NOT NULL;

Dropping a column

A column can be dropped using alter table command as given below:

Example:

ALTER TABLE Item DROP qty;

Modifying a table:

A column can also be modified using Alter Table command as given below:

ALTER TABLE item MODIFY qty SET DEFAULT 100;

Screenshot:
How to add Primary Key?

How to drop primary key?

ALTER TABLE Item DROP Primary key;

Data Manipulation Language | DML
 DML is used to insert, modify and delete data in a database.
 Example of DML commands are:
 SELECT – extracts data from a table
 UPDATE – updates data in a table
 DELETE – deletes data from a table
 INSERT INTO – inserts new data into a table
Insert command

This command is used to insert a tuple in a relation. We must specify the name of
the relation in which tuple is to be inserted and the values. The values must be in
the same order as specified during the Create Table command.

Syntax:

INSERT INTO <tablename> VALUES (<values >);

Example:

Inserting values in table Item (Ino, Iname, Rate, Qty)

INSERT INTO Item Values (1, ‘soap’,44,120);

Screenshot:
Update command

Update command is used to modify the attribute values of one or more tuples in a
table.

Syntax:

UPDATE <tablename>
SET <expression>
WHERE <criteria>

Example:

UPDATE Item
SET Rate = 30
WHERE itemno = ‘i0001’;

Screenshot:
Delete command

To delete one or more tuples (rows) in a realtion DELETE command is used.

Syntax:

DELETE FROM <tablename>

WHERE <criteria>;

Example:

DELETE FROM item WHERE itemno = ‘i0002’;

Screenshot:
Select command

Select command is used to access or retrieve specific or complete set of records

from database.

Syntax:

SELECT <attribute list> FROM <table name>;

Query1: Display all records from Item table.

Code:

Select * from item;

Screenshot:
Query2: Display itemname with its rate from item table.

Code:

Select itemname,rate from item;

Screenshot:

Query3: display name of items which rate is more than 100.

Code:

Select itemname from item where rate > 100;

Screenshot:

Query4: display item records which name begins with s.

Code:

Select * from item where itemname like ‘s%’;

Screenshot:

Query5: retrieve item details which rate is less than 100 and qty is more
than 100.

Code:

Select * from item where rate < 100 and qty > 100;

Screenshot:
DISTINCT Command

DISTINCT command is used to eliminate repeated data of a column when display

using SELECT command

Syntax:

SELECT DISTINCT(field that contains repeated data) FROM <table name>

WHERE <criteria>;

Example

Screenshot

WHERE clause

 WHERE clause act as a Filter while manipulate records in SQL.

 Where clause is used to define Criteria for the records to be retrieved.
 It can be used with any DML command such as SELECT, DELETE and UPDATE.
 To define criteria various operators like Arithmetic, Range or Comparison
operators are used in WHERE clause.
Syntax:

SELECT <field(s)> FROM <table name>

WHERE <criteria>;

Example:

Query5: retrieve item details which rate is less than 100 and qty is more
than 100.
Code:

Select * from item where rate < 100 and qty > 100;

Screenshot:

Example:

DELETE FROM item WHERE itemno = ‘i0002’;

Screenshot:

Operators used with WHERE clause

 Arithmetic operators
 Relation operators
 Logical operators
 BETWEEN operators
 IN operators
 LIKE operators
Arithmetic operators

 We can use arithmetic operators to define expressions in DML SQL

commands.
 Arithmetic operators are used with numeric values.
 Following table depicts various arithmetic operators:
Arithmetic Operator Description

+ For adding values

– For subtracting values

* For multiplying values

/ For dividing values

% For finding remainders

Example:

Relational operators

 We can use Relational operators to compare values in SQL.

 Mostly used to define criteria in WHERE clause to filter records.
 Following table depicts various Relational operators:
Relational Operator Description

= Equal to

> Greater than

< Lesser than

>= Greater than equal to

<= Lesser than equal to

!= or <> Not equal to

Example:

Logical operators

 Logical operators are used to combine multiple criteria/expression in DML

SQL commands
 Mostly used to combine criteria in WHERE clause to filter records.
 Following table depicts various Logical operators:
Logical Operator Description

AND Returns true when all criteria/expressions are true

OR Returns true when at least a criteria/expression is true

NOT Negates the result

BETWEEN operator
 It is used to define range of values for a particular field in WHERE clause.

It can be used to define range of numerical or chronological values

It includes upper and lower bound given in the range.

Example:

IN operator

It is used to set multiple text comparison for a particular field in WHERE


clause.
 It can only be used for text comparision.
Example:

LIKE operator

 It is used to define string expressions (pattern matching) in WHERE clause.

 It uses different wildcards (described in image given below) to define string
expression.
Wildcard Description

% Used to match the occurrence of set of characters of any length

_ Underscore used to match occurrence of any single character

Example:

Order by clause

 Used to display records in sequential manner in data resultset retrieved by

WHERE clause.
 Bydefault displays records in ascending order.
 Keyword ASC (Ascending) or DESC (descending) can be used with order by to
arrange the data resultset.