A group of rows and columns form a table.
The horizontal subset of the Table is known as a Row/Tuple.
- Cardinality: No. of Rows of Table
The vertical subset of the Table is known as a Column/an Attribute.
- Degree: No.of columns of Table.
Key in RDBMS
To maintain data integrity (that is data should be correct and in well formed) we use concept of keys. There are five types of keys in database which is as follows -
- Candidate key
- Primary Key
- Foreign Key
- Alternate Key
- Composite Key
Candidate Key
Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.
Primary Key
Such type of candidate key which is chosen as a primary key for table is known as primary key. Primary keys are used to identify tables. There is only one primary key per table. In SQL Server when we create primary key to any table then a clustered index is automatically created to that column.
Foreign Key
Foreign key are those keys which is used to define relationship between two tables. When we want to implement relationship between two tables then we use concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table. foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.
Alternate Key
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as alternate key.
Composite Key
When we create keys on more than one column then that key is known as composite key. Like here we can take an example to understand this feature. I have a table Student which has two columns Sid and SrefNo and we make primary key on these two column. Then this key is known as composite key.
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)
- Transnational 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';
note: here <> is denote not equal.
| 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%';
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%' ;
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;
SELECT COUNT(rno) FROM student;
SELECT SUM(fees) FROM student;
SELECT AVG(fees) FROM student;
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;