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 know 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 can not 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 of identifies 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.

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


Relational algebra

Relational algebra is a formal system for manipulating relations.  Set of operations that can be carried out on a relation:

  • Selection : To select a horizontal subset of a relation
  • Projection : To select vertical subset of a relation
  • 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

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 language - used to control logical units of work (e.g., COMMIT)

 

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 int,
name char(25),
fees int,
dob date,
class char(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 
VALUES(10, 'Alex', 7800, '1998-10-03','K12');
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

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
 

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

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 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

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

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
SELECT order_id, product_name, unit_price, supplier_name, total_units 
FROM product, order_items 
WHERE order_items.product_id = product.product_id;