SQL IMPORTANT NOTES FOR STUDENTS :
DBMS : Database is a collection of data/information organised for easy access, management and maintenance.
Types of Data model:
- Object based logical model
1. Entity Relationship Model :: This type of DBMS uses a structure which allows the users to access data in relation to another piece of data in the database. In this type of DBMS data is mostly stored in tabular form.
- Record based logical model
1. Hierarchical Data Model :: This type of DBMS has a structure similar to that of a tree where in the nodes represent the records and the branches of the tree represent the fields.
2. Network Data Model :: This type of DBMS supports many to many relations where multiple member records can be linked.
3. Relational Data Model :: An object database is a DBMS in which information is represented in the form of an object as used in OOPS.
OPERATIONS ::
→ Adding new files
→ Inserting data
→ Retrieving data
→ Modifying data
→ Removing Data
→ Removing files
ADVANTAGE OF DBMS ::
→ Sharing of data across applications
→ Enhanced security mechanism
→ Enforce integrity constraints
→ Better transaction support
→ Backup and recovery features
RDBMS :: A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easier to locate and access specifier values within the database.
** It is “RELATIONAL” because the values within each table are related to each other.Table may also be related to other tables(referencing table, referenced table).
FEATURES OR ADVANTAGES OF RDBMS ::
→ Every piece of information is stored in the form of tables
→ Has primary keys for unique identification of rows
→ Has foreign key to ensure data integrity
→ Provide SQL for data access
→ Uses indexes for faster data retrieval
→ Gives access privileges to ensure data security
RDBMS vs TRADITIONAL APPROACH ::
→ The key difference is that RDBMS applications store data in a tabular form, whereas in the traditional approach, applications store data as files.
→ There can be, but there will be no “relation” between the tables, like in a RDBMS. In the traditional approach, data is generally stored in either a hierarchical form/navigational form. This means that a single data unit will have 0,1 or more children nodes and 1 parent node.
** NORMALISATION ::
→ Decompose larger, complex table into simpler and smaller ones
→ Moves from lower normal forms to higher normal forms
NORMAL FORMS ::
→ First Normal form(1NF) ::
→ All attributes in the relation are atomic(indivisible value)
→ There are no repeating elements or groups of elements.
→ Second Normal Form(2NF) ::
→ It is in 1st Normal Form.
→ No partial dependency exists between non-key attributes.
→ Third Normal Form(3NF) ::
→ It is 2nd NF
→ No transitive dependency exists between non-key attributes and key attributes through another non-key attribute.
→ Higher Normal Form(BCNF, 4NF, 5NF…..)
NEED FOR NORMALISATION ::
→ In order to produce good database design
→ To ensure all database operations to be efficiently performed
→ Avoid any expensive DBMS operations
→ Avoid unnecessary replication of information
FUNCTIONAL DEPENDENCIES ::
P ————-> Q
In a given relation R,P, and Q are attributes. Attribute Q is functionally dependent on attribute P, if each value of P determines exactly one value of Q.
TYPES ::
→ Partial Functional Dependency ::
Attribute Q is partially dependent on attribute P, if and only if it is dependent on the subset of attribute P.
→ Transitive Dependency ::
X → Y , Y → Z , then X → Z
STUDENTS MARKS TABLE ::
| Students_Details | Course_Details | Pre-requisites | Result_details |
| 101 Jack 11/04/75 | M1 Advanced Maths17 | Basic Maths | 03/11/2015 82 A |
| 102 Rock 10/04/76 | P4 Advanced Physics 18 | Basic Physics | 21/11/2015 83 A |
| 103 Mary 11/07/75 | B3 Advanced Biology 10 | Basic Biology | 12/11/2015 68 B |
| 104 Roby10/04/76 | H6 Advanced History 19 | Basic History | 21/11/2015 83 A |
| 105 Jim 03/08/78 | C3 Advanced Chemistry 12 | Basic Biology | 12/11/2015 50 C |
STUDENT MARKS TABLE IN 1st NF ::
| Stu | Stu_name | DOB | Course | C_name | Pre-requis | Duration | Date of Ex | Marks | Grade |
| 101 | Jack | 11/04/1975 | M1 | Advanced MATHS | Basic Maths | 17 | 02/11/2015 | 82 | A |
| 102 | Rob | 10/04/1976 | P4 | Advanced Physics | Basic Physics | 18 | 21/11/2015 | 83 | A |
| .. | |||||||||
| .. |
SECOND NORMAL FORM –(2NF) ::
Student#, Course# → Marks
Student#, Course# → Grade
Marks → Grade
student# → StudentName, DON
Course# → CourseName, Prerequisite
DurationDays, Date of Exam
——-> Partial Dependency with the key attribute ——> Split/Decompose the tables to remove partial dependencies.
STUDENT TABLE
| STUDENT | S_NAME | DOB |
| 101 | JACK | 11/04/1975 |
| 102 | ROBY | 10/04/1976 |
| .. |
RESULT TABLE
| STUDENT | COURSE | MARKS | GRADE |
| 101 | M1 | 82 | A |
| 102 | P4 | 83 | A |
| 103 | B3 | 68 | B |
COURSE TABLE
| COURSE | C_NAME | PREREQUISITE | DURATION | DATE OF EXAM |
| M1 | ADV MATHS | BAS MATHS | 17 | 02/11/2015 |
| P4 | ADV PHY | BAS PHY | 18 | 21/11/2015 |
3rd NORMAL FORM –3NF ::
Result_Table
| Student | Course | Marks | Grade |
| 101 | M1 | 82 | A |
| …. | |||
Student, Course —> Marks
Student, Course —> Grade
Marks → Grade
Now, Student, course → Marks → Grade : TD —-> Remove
SO,
Result Table
| Student | Course | Marks |
| 101 | M1 | 82 |
| 102 | P4 | 83 |
| 103 | B3 | 68 |
Marks Grade Table
| Marks | Grade |
| 82 | A |
| 83 | A |
| 68 | B |
SQL ::
Programming Language specifically designed for working with databases to >> Create >> Manipulate >> Share/Access.
ADVANTAGES ::
SQL is popular because it offers the following advantages :
→ Allows users to communicate i.e, access and manipulate the database.
→ Allows users to retrieve data from a database.
→ Allows users to create, update, modify and delete the database.
SQL TERMS ::
→ DATA : Data is defined as facts or figures, or information that’s stored in or used by a computer.
→ DATABASE : A database is an organised collection of data/information so that it can be easily accessed, managed and updated.
SQL DATA TYPES ::
→ Numeric : bit, tinyint, smallint, int, bigint, decimal, numeric, float,real.
→ Character/String → Char, Varchar, text
→ Date/Time → Date, Time, Datetime, Timestamp, Year
→ Miscellaneous → Json.xml
SQL CONSTRAINTS ::
| Constraint | Description |
| Not Null | Ensures that a column does not have a NULL value |
| Default | Provides a default value for a column when none is specified |
| Unique | Ensures that all the values in a column are different |
| Primary | Identifies each row/record in a database table uniquely |
| Check | Ensures that all values in a column satisfy certain conditions |
| Index | Creates and retrieves data from the database very quickly |
SQL COMMAND GROUPS ::
→ DDL(Data Definition Language) : creation of objects
| Command | Description |
| Create | Creates objects in the database/ database objects |
| ALter | Alters the structures of the database/database objects |
| Drop | Delete objects from the database |
| Truncate | Removes all records from a table permanently |
| Rename | Rename an object |
CREATE COMMAND ::
Create table employees(
Emp_id int not null,
First_name varchar(20),
Lat_name varchar(20),
Salary int,
Primary key(emp_id)
);
Select * from employees; → for view the table
Describe employees; → for view the Structure of the table
ALTER COMMAND ::
Alter table employees add column int; → new column added
Select * from employees;
Describe employees;
RENAME COMMAND ::
Alter table employees rename column contact to job_code; → Rename the column name job_code to contact
Describe employees;
TRUNCATE COMMAND ::
Truncate table employees; → all the records will be deleted, only column names will be present there.
Select * from employees;
DROP COMMAND ::
Drop table employees; → table will deleted(no data no column name will present there)
Select * from employees;
→ DML(Data Manipulation Language) : manipulation of data
| Command | Description |
| Insert | Insert Data into a table |
| Update | Update existing data within a table |
| Delete | Delete specified/all records from a table |
INSERT COMMAND ::
Insert into employees (emp_id, first_name, last_name, salary) values (101, ‘Steven’, ‘King’, 10000);
Insert into employees (emp_id, first_name, last_name, salary) values (102, ‘Edwin’, ‘Thomas’, 15000);
Insert into employees (emp_id, first_name, last_name, salary) values (103, ‘Harry’, ‘Potter’, 20000);
Select * from employees;
UPDATE COMMAND ::
Update employees set last_name = ‘Cohen’ where emp_id = 101;
→ update the last name ‘cohen’ from king.
DELETE COMMAND ::
Delete from employees where emp_id in (101,103); → delete the recording data of emp_id (101,103)
** DDL will dill with the create a structure of a table. And DML commands manipulate the data into the table.
→ DCL(Data Control Language) : assignment and removal of permissions. This will be used for security purposes.
| Command | Description |
| Grant | Gives access privileges to database |
| Revoke | Withdraw access privileges given with the grant command. |
GRANT COMMAND ::
Grant <Privilege list> on <relation name> to <user>
REVOKE COMMAND ::
Revoke <Privilege list> on <relation name> to <user>
Privilege list : beneficiary list
Relation name : column name
→ TCL(Transaction Control Language) : saving and restoring changes to a database.
| Command | Description |
| Commit | Saves the work done |
| Rollback | Restores database to origin state since the last commit |
| SavePoint | Identify a point a transaction to which you can roll back later |
SQL OPERATORS — FILTER
WHERE Clause :
→ used to specify a condition while fetching the data from a single table or by joining with multiple tables.
→ Not only used in the SELECT statement, but it is also used in the UPDATE,DELETE statement, etc.
E.g.— select * from employees where emp_id = 101;
select * from employees where salary = 10000;
- The example mentioned above extracts all the columns from the table ‘employees’ whose emp_id = 101
SQL OPERATORS — Logical
| Operator | illustrativeExample | Result |
| AND | (5<2) AND (5>3) | FALSE |
| OR | (5<2) OR (5>3) | TRUE |
| NOT | NOT(5<2) | TRUE |
Select * from employees where first_name = ‘Steven’ and salary = 15000;
Select * from employees where first_name = ‘steven’ or salary = 15000;
Select * from employees where first_name = ‘steven’ and salary != 10000;
SQL OPERATORS == COMPARISON
| SYMBOL |
| = |
| > |
| >= |
| < |
| <= |
| <> OR != |
Select * from employee where first_name = ‘Steven’ and salary <=10000;
Select * from employees where first_name = ‘Steven’ or salary >=10000;
SQL OPERATORS — SPECIAL
| BETWEEN | Check an attribute value within range |
| LIKE | Checks an attributes value matches a given string pattern |
| IS NULL | Checks an attribute value is NULL |
| IN | Check an attribute value matches any value within a value list |
| DISTINCT | Limits value to unique values |
Select * from employees where salary between 10000 and 20000;
Select * from employees where first_name like ‘Steven’;
Select * from employees where last_name like ‘l%’;
Select * from employees where salary is null;
Select * from employees where salary in(10000,12000,20000);
Select distinct (first_name) from employees;
SQL OPERATORS — AGGREGATION
| Avg() | Returns the average value from specified columns.Select svg(salary) from employees; |
| Count() | Return number of table rows.Select count(*) from employees; |
| Max() | Return largest value among the records.Select max(salary) from employees; |
| Min() | Return smallest value among the records.Select min(salary) from employees; |
| Sum() | Return the sum of specified column values.Select sum(salary) from employees; |
SQL GROUP BY Clause
→ Arrange identical data into groups. It is used to group rows that have the same values.
E.g. → select max(salary), dept_id from employees group by dept_id;
SQL HAVING Clause
→ used with aggregate functions due to its non-performance in the where clause.
→ Must follow the group by clause in a query and must also precede the order by clause if used.
E.g. → select avg(salary), dept_id from employees group by dept_id having count (dept_id) >=2;
SQL ORDER BY Clause
→ used to sort output of select statement
→ Default is to sort in ASC(Ascending)
→ Can sort in Reverse (Descending) order with “DESC” after the column name
E.g. → select * from employees order by salary DESC;
SQL UNION
→ Used to combine the result-set of two or more SELECT statements removing duplicates
→ Each SELECT statements within the UNION must have the same number of columns
→ The selected columns must be of similar data types and must be in the same order in each SELECT statement.
E.g. → select product_name from product1 union select product_name from product2; → here product1 and product2 are two different tables.
SELECT UNION ALL
→ used to combine the results of two SELECT statements including duplicate rows.
→ The same rules that apply to the UNION clause will apply to the UNION ALL operator.
E.g. → select col1,col2…..from table1 union all select col1,col2….from table2;
→ duplicate values will be printed here.
SELECT INTERSECT
E.g. → select product_name from product1 intersect select product_name from product2; → intersect will print only the common elements from two tables.
SQL JOINS ::
Combines rows/columns from two or more tables, based on a related column between them in a database.
→ INNER JOIN :: Returns rows when there is a match in both tables. (A⋃B)
The inner join creates a new result table by combining column values of two tables (table1 and table2) based upon the join predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join predicate.
SYNTAX :
Select table1.col1,table2.col2,……,table1.coln
From table1
Inner join table2
On table1.commonfield = table2.commonfield;
TABLE1–EMPLOYEE TABLE
| emp_id | first_name | last_name | salary | dept_id |
| 103 | Harry | Potter | 20000 | 12 |
| 102 | Edwin | Thomas | 15000 | 11 |
| 101 | Steven | Cohen | 10000 | 10 |
| 100 | Erik | John | 10000 | 12 |
TABLE 2 — DEPT TABLE
| dept_id | dept_name | manager_id | location_id |
| 10 | IT | 200 | 1700 |
| 11 | Marketing | 201 | 1800 |
| 13 | Resource | 203 | 2400 |
| 14 | Shipping | 121 | 1500 |
Select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from employees e
inner join departments d
on e.dept_id=d.dept_id;
| emp_id | first_name | last_name | dept_id | dept_name |
| 101 | Steven | Cohen | 10 | IT |
| 102 | Edwin | Thomas | 11 | Marketing |
→ LEFT JOIN :: Returns all rows from the left table, even if there are no matches in the right table.(A⋂B)⋃A
The LEFT JOIN returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
SYNTAX :
Select table1.col1,table2.col2,…,table1.coln
From table1
Left join table2
On table1.commonfield = table2.commonfield
TABLE1–EMPLOYEE TABLE
| emp_id | first_name | last_name | salary | dept_id |
| 103 | Harry | Potter | 20000 | 12 |
| 102 | Edwin | Thomas | 15000 | 11 |
| 101 | Steven | Cohen | 10000 | 10 |
| 100 | Erik | John | 10000 | 12 |
TABLE 2 — DEPT TABLE
| dept_id | dept_name | manager_id | location_id |
| 10 | IT | 200 | 1700 |
| 11 | Marketing | 201 | 1800 |
| 13 | Resource | 203 | 2400 |
| 14 | Shipping | 121 | 1500 |
Select e.employees_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from employees e
left outer join departments d
on e.dept_id = d.dept_id;
| emp_id | first_name | last_name | dept_id | dept_name |
| 101 | Steven | Cohen | 10 | IT |
| 102 | Edwin | Thomas | 11 | Marketing |
| 103 | Harry | Potter | Null | Null |
→ RIGHT JOIN :: Returns all rows from the right table, even if there are no matches in the left table. (A⋂B)⋃B
The RIGHT JOIN returns all the values from the right table, plus matched values from the left table or null in case of no matching join predicate.
SYNTAX:
Select table1.col1,table2.col2,…,table1.coln
From table1
Right join table2
On table1.commonfield = table2.commonfield
TABLE1–EMPLOYEE TABLE
| emp_id | first_name | last_name | salary | dept_id |
| 103 | Harry | Potter | 20000 | 12 |
| 102 | Edwin | Thomas | 15000 | 11 |
| 101 | Steven | Cohen | 10000 | 10 |
| 100 | Erik | John | 10000 | 12 |
TABLE 2 — DEPT TABLE
| dept_id | dept_name | manager_id | location_id |
| 10 | IT | 200 | 1700 |
| 11 | Marketing | 201 | 1800 |
| 13 | Resource | 203 | 2400 |
| 14 | Shipping | 121 | 1500 |
Select e.employees_id, e.first_name, e.last_name, d.dept_id, d.dept_name
from employees e
right join departments d
on e.dept_id = d.dept_id;
| emp_id | first_name | last_name | dept_id | dept_name |
| 101 | Steven | Cohen | 10 | IT |
| 102 | Edwin | Thomas | 11 | Marketing |
| Null | Null | Null | 13 | Resources |
| Null | Null | Null | 14 | Shipping |
→ FULL OUTER JOIN :: Returns rows when there is a match in one of the tables. (A∩B)⋃(A-B)⋃(B-A)
The FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
SYNTAX :
Select table.col1,table2.col2,….,table1.coln
From table1
Left join table2
On table1.commonfield = table2.commonfield;
Union
Select table1.col1,table2.col2,….,table1.coln
From table1
Right join table2
On table1.commonfield = table2.commonfield;
TABLE1–EMPLOYEE TABLE
| emp_id | first_name | last_name | salary | dept_id |
| 103 | Harry | Potter | 20000 | 12 |
| 102 | Edwin | Thomas | 15000 | 11 |
| 101 | Steven | Cohen | 10000 | 10 |
| 100 | Erik | John | 10000 | 12 |
TABLE 2 — DEPT TABLE
| dept_id | dept_name | manager_id | location_id |
| 10 | IT | 200 | 1700 |
| 11 | Marketing | 201 | 1800 |
| 13 | Resource | 203 | 2400 |
| 14 | Shipping | 121 | 1500 |
Select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
From employees e
Left join departments d
On e.dept_id = d.dept_id
Union
Select e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name
From employees e
Right join departments d
On e.dept_id = d.dept_id;
| emp_id | first_name | last_name | dept_id | dept_name |
| 101 | Steven | Cohen | 10 | IT |
| 102 | Edwin | Thomas | 11 | Marketing |
| 100 | Harry | Potter | NULL | null |
| 100 | Erik | John | Null | Null |
| Null | Null | Null | 13 | Resources |
→ SELF JOIN :: Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
→ CARTESIAN JOIN(CROSS JOIN) :: Returns the Cartesian product of the sets of records from the two or more joined tables.
The CROSS JOIN produces a result set with the number of rows in the first table multiplied by the number of rows in the second.
SYNTAX:
Select table1.col1,table2.col2,….,table1.coln
From table1
Cross join table2;
Assume there are 4 records in table1 and 3 records in table 2 ..after cross join there will be 4*3=12 records.
TABLE-1 EMPLOYEES
| alpha |
| A |
| B |
| C |
| D |
TABLE-2 DEPARTMENT
| Num |
| 1 |
| 2 |
| 3 |
Select * from employees cross join department;
| A | 1 |
| A | 2 |
| A | 3 |
| B | 1 |
| B | 2 |
| B | 3 |
| C | 1 |
| C | 2 |
| C | 3 |
| D | 1 |
| D | 2 |
| D | 3 |
ACID Properties
A transaction is a very small unit of a program and it may contain several low level tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.
- Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.
- Consistency − The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.
- Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.
- Isolation − In a database system where more than one transaction is being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.
What is the storage procedure in dbms ??
A stored procedure in SQL is a type of pre-written code that can be stored for later execution and then used many times hence, saving time. It is a group of SQL statements that performs the task. The stored procedure can be invoked explicitly whenever required. It may accept some inputs in the form of parameters, these may be one parameter or multiple parameters.
Syntax:
CREATE PROCEDURE stored_procedure_name
AS
sql_statement
GO;