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;