Introduction to Databases
Concepts in Focus
• Data
• Database
• Database Management System (DBMS)
Advantages
• Types of Databases
o Relational Database
o Non-Relational Database
Data:
Any sort of information that
is stored is called data.
Examples:
•Messages & multimedia
on WhatsApp
•Products and orders on
Amazon
•Contact details in
telephone directory, etc.
Database:
An organized collection of data is called a database.
Database Management System (DBMS)
A software that is used to easily store and access data from the
database in a secure way.
Advantages:
• Security: Data is stored & maintained securely.
• Ease of Use: Provides simpler ways to create & update data at
the rate it is generated and updated respectively.
• Durability and Availability: Durable and provides access to all
the clients at any point in time.
• Performance: Quickly accessible to all the clients
(applications and stakeholders).
Types of Databases
There are different types of databases based on how we
organize the data.
Relational Database
These four types are
commonly referred
as non-relational
databases.
NOTE:
• Choice of database depends on our requirements.
• Relational database is the most commonly used database.
Relational DBMS:
A Relational DBMS is a DBMS designed specifically for relational
databases. Relational databases organize the data in the form of
tables.
Examples: Oracle, PostgreSQL, MySQL, SQLite, SQL Server, IBM
DB2, etc.
Non-Relational DBMS:
A Non-relational DBMS is a DBMS designed specifically for non-
relational databases. Non-relational databases store the data in a
non-tabular form.
Examples: Elasticsearch, CouchDB, DynamoDB, MongoDB,
Cassandra, Redis, etc.
Introduction to SQL
We have already learnt that databases and DBMS are key to
organizing and analyzing data for business uses. From here on, let’s
get busy working around with databases using SQL!
• SQL stands for
Structured Query Language
• SQL is used to perform operations on Relational DBMS.
• SQL is declarative. Hence, easy to learn.
SQL provides multiple clauses (commands) to perform various
operations like create, retrieve, update and delete the data.
The first step towards working with the database would be creating a
table.
Data Types
Following data types are frequently used in SQL.
Data Type Syntax
Integer INTEGER / INT
Float FLOAT
String VARCHAR
Text TEXT
Date DATE
Time TIME
Datetime DATETIME
Boolean BOOLEAN
Note
1. Boolean values are stored as integers 0 (FALSE) and 1 (TRUE).
2. Date object is represented as: ‘YYYY-MM-DD’
3. Datetime object is represented as: ‘YYYY-MM-DD HH:MM: SS’
Create Table:
Creates a new table in the database.
Syntax
SQL:
CREATE TABLE table_name (
column1 type1,
column2 type2,
...
);
Here, type1and type2 in the syntax are the datatypes of column1
And column2 respectively.
Example:
Create a player table to store the following details of players.
column_name data_type
name VARCHAR (200)
age INT/INTEGER
score INT/INTEGER
SQL:
CREATE TABLE player (
name VARCHAR (200),
age INTEGER,
score INTEGER
);
We can check the details of the created table at any point in time
using the PRAGMA command.
Try it Yourself!
Assume that we have to build a database that stores all the
information about the students in a school, subjects, exam
schedules, etc. Let’s build a few tables to store the data!
1. Create a student table to store the following details of students.
details data_type
name VARCHAR (200)
date_of_birth DATE
address TEXT
Check if the tables are created in the database after you run the
query
2. Create an exam_schedule table to store the information
about exams.
details data_type
name VARCHAR (200)
details data_type
course VARCHAR (200)
exam_date_time DATETIME
duration_in_sec INT
pass_percentage FLOAT
Check if the tables are created in the database after you run the
query
PRAGMA:
PRAGMA TABLE_INFO command returns the information about a
specific table in a database.
Syntax
SQL:
PRAGMA TABLE_INFO (table_name);
Example
Let's find out the information of the employee table that's
present in the database.
SQL
PRAGMA TABLE_INFO (employee);
Output:
cid name type notnull dflt_value pk
0 employee_id INTEGER 0 0
VARCHAR
1 name 0 0
(200)
2 salary INTEGER 0 0
Note:
dflt_value is the default value of the column. If a column does not
have a default value specified by the user while creating, the
dflt_value column will be NULL.
Note:
If the given table name does not exist, PRAGMA TABLE_INFO
doesn’t give any result.
Inserting Rows
INSERT clause is used to insert new rows in a table.
Syntax
SQL:
INSERT INTO
table_name (column1, column2,..., columnN)
VALUES (
(value11, value12,……, value1N),(value21, value22,….., value2N),
...);
Any number of rows from 1 to n can be inserted into a specified table
using the above syntax.
Database
Let's learn more about the INSERT clause by going hands-on on the
player and match_details tables that store the details of players and
matches in a tournament respectively.
• Player table stores the name, age and score of players.
• match_details table stores name of team, opponent team
name, place, date and match result
Examples
1. Insert name, age and score of 2 players in the player table.
SQL:
INSERT INTO
player (name, age, score)
VALUES
("Rakesh", 39, 35),
("Sai", 47, 30);
Upon executing the above code, both the entries would be added to
the player table.
Let's view the added data!
We can retrieve the inserted data by using the following
command.
SQL:
SELECT *FROM player;
We shall know more about retrieving data in further cheat sheets.
2. Similarly, let's insert the details of 2 matches in the
match_details table.
SQL:
INSERT INTO
match_details (team_name, played_with, venue, date, is_won)
VALUES
("CSK", "MI", "Chennai", "2020-04-21", true),
("SRH", "RR", "Hyderabad", "2020-04-23", true);
Note
1. Boolean values can be either given as (TRUE or FALSE) or (1 or
0). But in the database, the values are stored as 1 or 0.
2. Date object is represented as: ‘YYYY-MM-DD’
3. Datetime object is represented as: ‘YYYY-MM-DD HH:MM: SS’
Possible Mistakes
Mistake 1
The number of values that we're inserting must match with the
number of column names that are specified in the query.
SQL
INSERT INTO
player (name, age, score)
VALUES
("Virat", 31);
Error: 2 values for 3 columns
Mistake 2
We have to specify only the existing tables in the database.
SQL:
INSERT INTO
players_information (name, age, score)
VALUES
("Virat", 31, 30);
Error: no such table: players_information
Mistake 3
Do not add additional parenthesis () post VALUES keyword in the
code.
SQL
INSERT INTO
player (name, age, score)
VALUES
(("Rakesh", 39, 35), ("Sai", 47, 30));
Error: 2 values for 3 columns
Mistake 4
While inserting data, be careful with the datatypes of the input
values. Input value datatype should be same as the column
datatype.
SQL:
INSERT INTO
player (name, age, score)
VALUES
("Virat", 31, "Hundred");
Warning
If the datatype of the input value doesn't match with the datatype
of column, SQLite doesn't raise an error.
Try it Yourself!
• Three new players have joined the tournament. Try inserting
the players' data in the
player
table.
name age score
Ram 28 70
Sita 25 30
Ravi 30 53
Check if the data is inserted in the Player table after you run the
query