DBMS concepts and SQL

Relational Database
A relational database is a collection of data items organized as logically related tables.

Relational Database Management System

The software required to handle/manipulate these table/relations is known as Relational
Database Management System (RDBMS). Example - Oracle, Sybase, DB2, MSSQL, etc.
Table/Relation
A group of rows and columns from a table. The horizontal subset of the Table is known as
a Row/Tuple. The vertical subset of the Table is known as a Column/an Attribute.
A relation in a database has the following characteristics:
• Every value in a relation is a atomic-i.e. it cannot be further divided
• Names of columns are distinct and order of columns is immaterial
• The rows in the relation are not ordered
Degree
No. of columns of Table.
Cardinality
No. of Rows of Table
Key
An Attribute/group of attributes in a table that identifies a tuple uniquely is known as a key.
A table may have more than one such attribute/group that identifies a tuple uniquely, all such
attributes(s) are known as Candidate Keys. Out of Candidate keys, one is selected
as Primary key, and others become Alternate Keys.

1
A Foreign Key is defined in a second table, but it refers to the primary key in the first table.

Cartesian Product: It operates on two relations and is denoted by X. for example Cartesian
product of two relation R1 and R2 is represented by R=R1X R2. The degree of R is equal to
sum of degrees of R1 and R2. The cardinality of R is product of cardinality of R1 and
cardinality of R2
Example cartesian Product
The table R1

Empno Ename Dept

1 Bill A
2 Sarah C
3 John A

The table R2
Dno Dname
A Marketing
B Sales
C Legal

2
R1 X R2
Empno Ename Dept Dno Dname
1 Bill A A Marketing
1 Bill A B Sales
1 Bill A C Legal
2 Sarah C A Marketing
2 Sarah C B Sales
2 Sarah C C Legal
3 John A A Marketing
3 John A B Sales
3 John A C Legal

SQL- Structured Query Language

SQL commands classified by function:
Data definition language (DDL) - used to define or change database structure(s) (e.g.,
CREATE, ALTER, DROP)
Data manipulation language (DML) - used to select or change data (e.g., INSERT,
UPDATE, DELETE, SELECT)
Data control language (DCL) - used to control user access (e.g., GRANT, REVOKE)
Transactional Control language - used to control logical units of work (e.g., COMMIT,
ROLLBACK)
Creating a new table in the database
Syntax :
CREATE TABLE table_name
(column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....);
Example :
CREATE TABLE student
(
rno integer primary key,
name varchar(25),
fees integer,
dob date,
class char(3)
);

3
 Inserting a new row at the bottom of the table
Syntax :
INSERT INTO table_name
VALUES (value1,value2,value3,...);
You can also specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Examples
INSERT INTO student (rno, name, fees, dob, class)
values(11, 'Peter', 6700, '1997-11-15', 'K12');
Displaying the content from a table – SELECT
Example :
SELECT * FROM student;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
11 Peter 6700 1997-11-15 K12
12 Alisha 7800 1999-07-03 K11
13 John 6900 2000-12-13 K11

SELECT name FROM student;

name
Alex
Peter
Alisha
John

Relational Operator
=, <, >, <=, >=, <>
Logical Operator
AND, OR, NOT
SELECT * FROM student WHERE fees < 7000;
rno name fees dob class
11 Peter 6700 1997-11-15 K12
13 John 6900 2000-12-13 K11

SELECT * FROM student WHERE fess > 7000 AND fees < 8000;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
12 Alisha 7800 1999-07-03 K11

4
SELECT * FROM student WHERE fees > 7000 OR class = 'K12';
rno name fees dob class
10 Alex 7800 1998-10-03 K12
11 Peter 6700 1997-11-15 K12
12 Alisha 7800 1999-07-03 K11

SELECT name, fees FROM student WHERE NOT (class = 'K12');

name fees
Alisha 7800
John 6900

SELECT name, fees FROM student WHERE class <> 'K12';

name fees
Alisha 7800
John 6900

SELECT * FROM student WHERE rno IN(10, 12, 13);

rno name fees dob class
10 Alex 7800 1998-10-03 K12
12 Alisha 7800 1999-07-03 K11
13 John 6900 2000-12-13 K11

SELECT * FROM student WHERE rno BETWEEN 11 AND 13;

rno name fees dob class
11 Peter 6700 1997-11-15 K12
12 Alisha 7800 1999-07-03 K11
13 John 6900 2000-12-13 K11

SELECT name FROM student WHERE name LIKE 'A%';

name
Alex
Alisha

SELECT * name FROM student WHERE name LIKE '%a';

rno name fees dob class
12 Alisha 7800 1999-07-03 K11

SELECT name FROM student WHERE Name LIKE '%e%' ;

name
Alex
Peter

5
Displaying data in a range where both upper and lower limit is included.
BETWEEN
SELECT * from student where fees between 6700 and 6900;
rno name fees dob class
11 Peter 6700 1997-11-15 K12
12 Alisha 7800 1999-07-03 K11
13 John 6900 2000-12-13 K11

Modifying the existing content of the table

Syntax:
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Example
UPDATE student
SET fees = '7900'
WHERE rno = 12 ;
SELECT * FROM student;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
11 Peter 6700 1997-11-15 K12
12 Alisha 7900 1999-07-03 K11
13 John 6900 2000-12-13 K11
Arranging the data in ascending or descending order of one/multiple columns
(ORDER BY clause)
Syntax:
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
Example
SELECT * FROM student ORDER BY name;
rno name fees dob class
10 Alex 7800 1998-10-03 K12
12 Alisha 7900 1999-07-03 K11
13 John 6900 2000-12-13 K11
11 Peter 6700 1997-11-15 K12

6
SELECT * FROM student ORDER BY fees DESC;
rno name fees dob class
12 Alisha 7900 1999-07-03 K11
10 Alex 7800 1998-10-03 K12
13 John 6900 2000-12-13 K11
11 Peter 6700 1997-11-15 K12

SELECT class, name, dob, fees FROM student ORDER BY class, name DESC;
class name dob fees
K11 John 2000-12-13 6900
K11 Alisha 1999-07-03 7900
K12 Peter 1997-11-15 6700
K12 Alex 1998-10-03 7800

SELECT class, name, fees, fees*12 as ‘annualfees’ FROM student;

class name fees annualfees
K12 Alex 7800 93600
K12 Peter 6700 80400
K11 Alisha 7900 94800
K11 John 6900 82800

Using Aggregate Functions with SELECT

COUNT( ) To count the number of rows
SUM( ) To find the sum of values in the column
MAX( ) To find the maximum value in the column
MIN( ) To find the minimum value in the column
AVG( ) To find the average of values in the column

SELECT COUNT(*) FROM student;

COUNT(*)
4

SELECT COUNT(rno) FROM student;

COUNT(rno)
4

7
SELECT SUM(fees) FROM student;

SUM(fees)
29300

SELECT AVG(fees) FROM student;

AVG(fees)
7325.0000

SELECT MAX(fees), MIN(fees) FROM student;

MAX(fees) MIN(fees)
7900 6700

Grouping data under given Column- (GROUP BY)

SELECT class, SUM(fees) FROM student GROUP BY class;
class SUM(fees)
K11 14800
K12 14500

SELECT class, MAX(fees), MIN(fees) FROM student GROUP BY class;

class MAX(fees) MIN(fees)
K11 7900 6900
K12 7800 6700

SELECT class, MAX(dob) FROM student GROUP BY class HAVING COUNT(*)>1;

class MAX(dob)
K11 2000-12-13
K12 1998-10-03

Deleting a row/rows from a table

Syntax:
DELETE FROM table_name
WHERE some_column=some_value;
Example:
DELETE FROM Student WHERE rno = 13;

Adding a new column(s) in the table

Syntax :
ALTER TABLE table_name
ADD column_name datatype

8
Examples :
ALTER TABLE student ADD (grade CHAR(2));
Modifying the data type of a column
Syntax;
ALTER TABLE table_name
MODIFY column_name datatype
Example:
ALTER TABLE student MODIFY (grade CHAR(1));
Deleting a table
Syntax:
DROP TABLE table_name
Example:
DROP TABLE student;
Working with more than one table
Syntax:
SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;
Table - product

product_id product_name supplier_name unit_price

100 Camera Nikon 300
101 Television Onida 100
102 Refrigerator Videocon 150
103 Ipod Apple 75
104 Mobile Nokia 50

Table - order_items

order_id product_id total_units customer

5100 104 30 Infosys
5101 102 5 Satyam
5102 103 25 Wipro
5103 101 10 TCS

9
SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;
Different types of Inner Join are :
1) Equi Join
2) Natural Join
3) Cross Join
Explain Equi Join, Natural Join and Cross Join in sql.
Consider the below tables
Employee

EmpId EmpName

1 John

2 David

3 Peter

4 Eric

Area

AreaId AreaName EmpId

3 New York 1

4 Canada 3

5 Australia 3

6 England 4

Equi join
Equi join is the first type of Inner Join.
It joins two or more tables where the specified columns are equal.
In this type of join, you can only use '=' operator in comparing the columns.
Operators like '>', '<' are not allowed in this type of join.
Example query: Select * from Employee emp
INNER JOIN Area a where a.EmpId = emp.EmpId

10
Result:

EmpId EmpName AreaId AreaName EmpId

3 Peter 4 Canada 3

3 Peter 5 Australia 3

4 Eric 6 England 4

As shown above, EmpId column is appearing twice.

Natural join
It is same as equijoin but the difference is that in natural join, the common attribute appears only
once.
Example query: Select * from Employee emp
NATURAL JOIN Area a on a.EmpId = emp.EmpId

Result:
EmpName AreaId AreaName
EmpId

3 Peter 4 Canada

3 Peter 5 Australia

4 Eric 6 England

As shown above, EmpId column is appearing only once.

Cross join
A cross join that produces Cartesian product of the tables.. The size of a Cartesian product is the
number of the rows in first table multiplied by the number of rows in the second table.
Example query: Select * from Employee emp
CROSS JOIN Area a

11
Result:

EmpId EmpName AreaId AreaName EmpId

1 John 3 New York 1

1 John 4 Canada 3

1 John 5 Australia 3

1 John 6 England 4

2 David 3 New York 1

2 David 4 Canada 3

2 David 5 Australia 3

2 David 6 England 4

3 Peter 3 New York 1

3 Peter 4 Canada 3

3 Peter 5 Australia 3

3 Peter 6 England 4

4 Eric 3 New York 1

4 Eric 4 Canada 3

4 Eric 5 Australia 3

4 Eric 6 England 4

As shown in the results above, the number of rows returned in the result are 4 * 4 = 16.

12
GROUP BY Characteristics:
• The GROUP BY clause is used to form the groups of records.
• The GROUP BY clause must come after the WHERE clause if present and before the
HAVING clause.
• The GROUP BY clause can include one or more columns to form one or more groups based
on that columns.
• Only the GROUP BY columns can be included in the SELECT clause. To use other columns
in the SELECT clause, use the aggregate functions with them.

For the demo purpose, we will use the following Employee and Department tables in all
examples.
Employee Table
EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' 'john.king@abc.com' 33000 1
2 'James' 'Bond' 1
3 'Neena' 'Kochhar' 'neena@test.com' 17000 2
4 'Lex' 'De Haan' 'lex@test.com' 15000 1
5 'Amit' 'Patel' 18000 1
6 'Abdul' 'Kalam' 'abdul@test.com' 25000 2
Department Table
DeptId Name
1 'Finance'
2 'HR'

Consider the following GROUP BY query.

SELECT DeptId, COUNT(EmpId) as 'Number of Employees'

FROM Employee
GROUP BY DeptId;

--following query will return same data as above

SELECT DeptId, COUNT(*) as 'No of Employees'
FROM Employee
GROUP BY DeptId;

The above query includes the GROUP BY DeptId clause, so you can include only DeptId in the
SELECT clause. You need to use aggregate functions to include other columns in the SELECT

13
clause, so COUNT(EmpId) is included because we want to count the number of employees in the
same DeptId. The 'No of Employees' is an alias of the COUNT(EmpId) column. The query will
display the following result.

DeptId No of Employees
1 4
2 2
The following query gets the department name instead of DeptId in the result.
SELECT dept.Name, count(emp.empid)
FROM Employee emp, Department dept
WHERE emp.deptid = dept.DeptId
GROUP by dept.Name;
Department No of Employees
Finance 4
HR 2

In the same way, the following query gets the department-wise total salaries.

SELECT dept.Name, sum(emp.salary) as 'Total Salaries'

FROM Employee emp, Department dept
WHERE emp.deptid = dept.DeptId
GROUP by dept.Name
Department Total Salaries
Finance 66000
HR 42000

The following query would throw an error, because dept.Name is not included in the GROUP BY
clause, or no aggregate function is used.

SELECT dept.Name, sum(emp.salary) as 'Total Salaries'

FROM Employee emp, Department dept
WHERE emp.deptid = dept.DeptId
GROUP by dept.DeptId

The HAVING clause includes one or more conditions that should be TRUE for groups of records.
It is like the WHERE clause of the GROUP BY clause. The only difference is that the WHERE
clause cannot be used with aggregate functions, whereas the HAVING clause can use aggregate
functions.

14
The HAVING clause always comes after the GROUP BY clause and before the ORDER BY
clause.

Syntax:
SELECT column1, column2,...columnN
FROM table_name
[WHERE]
[GROUP BY column1, column2...columnN]
[HAVING conditions]
[ORDER BY]
HAVING Characteristics:
• The HAVING clause is used to filter out grouping records.
• The HAVING clause must come after the GROUP BY clause and before the ORDER BY
clause.
• The HAVING clause can include one or more conditions.
• The HAVING condition can only include columns that are used with the GROUP BY clause.
To use other columns in the HAVING condition, use the aggregate functions with them.

For the demo purpose, we will use the following Employee table in all examples here.

Employee Table
EmpId FirstName LastName Email PhoneNo Salary DeptId
1 'John' 'King' 'john.king@abc.com' '650.127.1834' 33000 1
2 'James' 'Bond' 1
3 'Neena' 'Kochhar' 'neena@test.com' '123.456.4568' 17000 2
4 'Lex' 'De Haan' 'lex@test.com' '123.000.4569' 15000 1
5 'Amit' 'Patel' 18000 1
6 'Abdul' 'Kalam' 'abdul@test.com' '123.123.0000' 25000 2

In the GROUP BY section, we used the following query to retrieve the no of employees in each
department, as shown below.

SELECT DeptId, COUNT(EmpId) as 'Number of Employees'

FROM Employee
GROUP BY DeptId;

15
 DeptId No of Employees
1 4
2 2

Now, to filter the result of the above GROUP BY query, use the HAVING clause with the
aggregate function, as shown below.

SELECT DeptId, COUNT(EmpId) as 'Number of Employees'

FROM Employee
GROUP BY DeptId;
HAVING COUNT(EmpId) > 2;

Notice that we used an aggregate function COUNT() in the HAVING clause because EmpId is
not included in the GROUP BY clause. The above query will display the following result.

DeptId No of Employees
1 4

The following query will throw an error because the Salary column is not included in the GROUP
BY clause and does not use an aggregate function.

SQL Script: GROUP BY

Copy
SELECT DeptId, COUNT(EmpId) as 'Number of Employees'
FROM Employee
GROUP BY DeptId;
HAVING Salary > 15000;
TYPES OF CONSTRAINT
• NOT NULL: This constraint does not allow to store a null value in a column. That is, if a
column is specified as NOT NULL then one cannot store null in the particular column.
• UNIQUE: If this constraint is specified with a column thent all the values in the column must
be unique. That is, the values in any row of a column must not be repeated.
• PRIMARY KEY: A primary key is a field which can uniquely identify each row in a table.
And this constraint is used to specify a field in a table as primary key.
• FOREIGN KEY: A Foreign key is a field which can uniquely identify each row in a another
table. And this constraint is used to specify a field as Foreign key.

16
 • CHECK: This constraint helps to validate the values of a column to meet a particular
condition. That is, it helps to ensure that the value stored in a column meets a specific
condition.
• DEFAULT: This constraint specifies a default value for the column when no value is
specified by the user.
How to specify constraints?
We can specify constraints at the time of creating the table using CREATE TABLE statement. We
can also specify the constraints after creating a table using ALTER TABLE statement.
Syntax of constraints
CREATE TABLE Student
( ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18));

CREATE TABLE Student

(ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18);

17