A relational database is a collection of data items organized as logically related tables.
The software required to handle/manipulate these table/relations is know as Relational Database Management System (RDBMS). Example - Oracle , Sybase, DB2, MSSQL, etc.
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:
No.of columns of Table.
No. of Rows of Table
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 is a formal system for manipulating relations. Set of operations that can be carried out on a relation:
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 |
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 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)
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)
);
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');
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 |
=, <, >, <=, >=, <>
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 |
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 |
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 |
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 |
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 |
DELETE FROM table_name
WHERE some_column=some_value;
Example:
DELETE FROM Student WHERE rno = 13;
Syntax :
ALTER TABLE table_name
ADD column_name datatype
Examples :
ALTER TABLE student ADD (grade CHAR(2));
Syntax;
ALTER TABLE table_name
MODIFY column_name datatype
Example:
ALTER TABLE student MODIFY (grade CHAR(1));
Syntax:
DROP TABLE table_name
Example:
DROP TABLE student;
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;