DATABASE MANAGEMENT – SQL
Relational Database
show databases;
create database sports;
use sports;
create table players
(
pcode int(10),
name varchar(20),
gcode int(10)
);
insert into players values
(1,'aaa',101),
(2,'bbb',102),
(3,'ccc',103);
select * from players;
Database Management System (DBMS)
A Database Management System (DBMS) is a software that can be used to create and
manage databases. DBMS allows users to create a database, store, manage, update,
modify and retrieve data from that database by users or application programs.
Some examples of open-source and commercial DBMS are MySQL, Oracle, PostgreSQL,
Microsoft Access and MongoDB.
Key Concepts in DBMS
Database Schema
A database schema is the design of a database. It is the logical structure of the database
that represents the tables, fields/columns, data types, constraints and relationships
among tables. It is also called the logical architecture of the database.
Data Constraints
Sometimes we put certain limits or restrictions on the type of data that can be inserted into
one or more columns of a table. This is done by specifying one or more constraints on the
columns while creating the table.
Example: NOT NULL
Metadata (Data Dictionary)
The database schema along with various constraints on the data is stored by the DBMS in a
database catalog or data dictionary called metadata.
Metadata is data about data.
Database Instance
When a database schema is defined, the database is initially empty. After data is loaded,
the state of the database at any given time is called a database instance.
Query
A query is a request made to a database to retrieve information in a desired form. Queries
can retrieve data from one table or from multiple tables.
Data Manipulation
Data manipulation refers to modification of data in a database.
It involves three operations:
1. Insert
2. Update
3. Delete
Database Engine
A database engine is the underlying component or set of programs used by a DBMS to
create databases and handle various queries for data retrieval and data manipulation.
Relational Data Model
A data model describes the structure of a database including how data is defined,
represented, related and constrained.
In the relational model, tables are called relations. Each relation consists of rows and
columns. Column names must be unique within a relation.
Attributes (Columns)
Attributes are the parameters for which data is stored in a relation. The columns of a
relation are called attributes or fields.
Tuple (Row)
Each row of data in a relation is called a tuple. A tuple represents a single record in a table.
Domain
A domain is the set of values that an attribute can take.
Usually, a data type is used to specify the domain of an attribute.
Example:
Roll Number → Integer values
Name → Character values
Degree
The number of attributes (columns) in a relation is called the degree of the relation.
Cardinality
The number of tuples (rows) in a relation is called the cardinality of the relation.
Keys in a Relational Database – refer Sumita Arora Book
Candidate Key
A candidate key is an attribute or set of attributes that can uniquely identify a record in a
table.
Primary Key
A candidate key selected to uniquely identify each record in a table.
Composite Primary Key
A primary key made up of more than one attribute.
Foreign Key
An attribute that refers to the primary key of another table.
Structured Query Language (SQL)
Structured Query Language (SQL) is the most popular query language used by major
Relational Database Management Systems such as MySQL, Oracle and SQL Server.
SQL is easy to learn and is not case-sensitive.
SQL for Data Definition (DDL)
Defining a schema includes:
Creating relations (tables)
Naming relations
Defining attributes
Assigning data types
Specifying constraints
SQL statements used for defining, modifying and deleting relation schemas are called Data
Definition Language (DDL) statements.
SQL for Data Manipulation (DML)
When a table is created, only its structure is created. To populate records in a table,
INSERT statements are used.
Records can also be modified and deleted using UPDATE and DELETE statements.
These statements form the Data Manipulation Language (DML).
Datatype of Attribute – refer Sumita Arora Book
The datatype of an attribute indicates the type of data value that an attribute can store.
It also determines the operations that can be performed on the data.
CONSTRAINTS – refer Sumita Arora Book
Constraints are restrictions placed on the values that an attribute can contain.
Commonly Used SQL Constraints
1. NOT NULL
Ensures that a column cannot contain NULL values.
2. UNIQUE
Ensures that all values in a column are distinct.
3. DEFAULT
Assigns a default value to a column if no value is provided.
4. PRIMARY KEY
Uniquely identifies each record in a table. NULL values are not allowed.
5. FOREIGN KEY
Refers to the primary key of another table.
Syntax to Create a Database
create database <database_name>;
Syntax to Use a Database
use <database_name>;
Syntax to View Existing Databases
show databases;
Syntax to View Tables in a Database
show tables;
Syntax to Describe a Table
desc <table_name>;
or
describe <table_name>;
ALTER TABLE - #Also Refer the material shared
Add Primary Key
alter table <table_name>
add primary key(<attribute_name>);
Add Composite Primary Key
alter table <table_name>
add primary key(<attribute1>, <attribute2>);
Add Foreign Key
Notes
Referenced table must already exist.
Referenced attribute must be part of the referenced relation.
Data types and sizes of referenced and referencing attributes must match.
alter table <table_name>
add foreign key(<attribute_name>)
references <referenced_table>(<attribute_name>);
Note
When a table is created, attributes can contain NULL values by default unless constraints
(eg. primary key) are specified.
Remove an Attribute
alter table <table_name>
drop <attribute_name>;
Remove Primary Key
alter table <table_name>
drop primary key;
Remove a Table
drop table <table_name>;
Remove a Database
drop database <database_name>;
Insertion of Records
Insert Values into All Columns
insert into <table_name>
values(value1, value2, value3);
Insert Values into Selected Columns
insert into <table_name>
(column1, column2)
values(value1, value2);
SQL for Data Query
SELECT Statement
The SELECT statement is used to retrieve data from a database and is also called query
statement.
Syntax
select attribute1, attribute2
from table_name
where condition;
Display All Columns
select * from table_name;
Example
select name, dob
from student
where rollno = 1;
Renaming Columns
select attribute_name as new_name
from table_name;
DISTINCT Clause
Used to display unique values.
select distinct attribute_name
from table_name;
BETWEEN Operator
Used to retrieve values within a range.
select *
from table_name
where attribute_name between value1 and value2;
Example
select *
from employee
where empno between 101 and 110;
Membership Operator (IN / NOT IN)
The IN operator compares a value with a set of values.
Syntax
select *
from table_name
where attribute_name IN(value1, value2, value3);
Example
select *
from employee
where EmpID IN(103,104,107);
ORDER BY Clause
Note: Default sorting order is Ascending.
Ascending Order
select *
from table_name
order by attribute_name;
Descending Order
select *
from table_name
order by attribute_name desc;
Handling NULL Values
NULL Values
select *
from table_name
where attribute_name IS NULL;
NOT NULL Values
select *
from table_name
where attribute_name IS NOT NULL;
Substring Pattern Matching
SQL provides the LIKE operator to search for patterns in a column.
% (Percent)
Represents zero, one or multiple characters.
_ (Underscore)
Represents exactly one character.
Syntax
select *
from table_name
where attribute_name like 'A%';
Examples
'A%'
'%A'
'%A%'
'_ANYA'
Data Updation
update table_name
set attribute_name = value
where condition;
Deletion
delete from table_name
where condition;
Functions in SQL - (also refer NCERT book)
Functions are useful while writing SQL queries. They can operate on a single row or
multiple rows.
Depending on their application in one or multiple rows, SQL functions are classified into:
1. Single Row Functions
2. Aggregate Functions
GROUP BY Clause
At times we are in need to fetch a group of rows on the basis of column values in a column. This can
be done by using a group by clause. It groups the rows together that contains the same values in a
specified column. We can use the aggregate functions to work on the grouped values.
The GROUP BY clause groups rows having the same values in a specified column.
Aggregate functions can be used on grouped data.
Example
select grade, count(marks)
from students
group by grade;
HAVING Clause
The HAVING clause is used to specify conditions on groups created using GROUP BY.
Example
select custID, count(*)
from sales
group by custID
having count(*) > 1;