Before starting our speech about SQL and its Importance, we need to know some fundamentals about Database and Database system…
Difference between DATA and INFORMATION & why we need to save data ?
Data: simply it is the unprocessed information.
Information: is what we get after data has been processed -something meaningful-.
For example : 2230 this is just a number -raw data- but if I tell you that is the salary of a doctor in a hospital, it will become an information, it becomes meaningful, it also may help you build a report about these doctors.
The importance of saving data is to use it instead of thousands of paper records/Files.
We use DB systems(Computerized databases) to save time and efforts for storing, retrieving and modifying data.
What is DB, DBMS and RDBMS?
Database (DB) : A database is any collection of facts that are systematically organized (e.g. University ID-Card each card contains facts about student name, college, ID…etc that helps to find more information about the student through university DB system much easier)
Database Management System (DBMS) : it is also known as DB Engine , DB Server or DB Manager. It is software allows user to store, modify, retrieve and delete data from a database. There are many types of DBMS such as Network, navigational, object-oriented and Relational.
Relational Database Management System (RDBMS) : a DBMS is said to be relational if database consists of group of related tables/relations (e.g. Oracle, Sybase, MySQL…etc) and it is the most common type of DBMS.
Table = Relation
Row = Tuple = Record
Column Header = Attribute = Field
Schema = Table Definition = DB Structure
To deal with any one of DBMS you will find yourself in need of a Query Language and now the question is:
What is the Query Language?
Query languages: are computer languages used to make queries into databases and information systems.
For example :
OQL is a query language for Object-Oriented Databases.
D is a query language for Truly Relational Database Management Systems (TRDBMS).
SQL is a query and Data Manipulation Language for Relational Databases as the result of that RDBMS is the most common in use, the SQL gets its importance.
What is SQL?
Structured Query Language is a special purpose non-procedural programming language for managing data in RDBMS.
procedural programming language: is a language where there can be dependencies between sequential commands..
non-procedural programming language: is a language where there is no communication between different lines of programming code.
– SQL is used to retrieve, manipulate, control data in Database Systems (DBS)
– It is a standard of ANSI (American National Standards Institute) so different vendors (e.g. Oracle, MySQL…) follow the standard and also add their own extensions, support the major commands of data manipulation Language (DML) in the same manner.
For example: a SELECT statement is has the same syntax in all of DBMS but how DBMS retrieves data it depends on DBMS itself.
SELECT column/s FROM table/s WHERE condition/s ;
– Query is a request or question written in SQL:
Query about employees’ numbers, names and salaries
– It is divided into sub-elements as the following:
Data Manipulation Language – DML: is used to manage the content of the database (i.e. select, insert, update, delete…)
DML concerned with data in Schema Objects.
For example :
SELECT to retrieve data from database and the result is stored in a result table called Result-set
INSERT adding new data to database tables
UPDATE modify the existing data
DELETE remove table records but table still exist (e.g. delete table records and need to write COMMIT ORROLLBACK to save changes permanently to DB Storage )
Schema: A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.
Schema objects: can be created and manipulated with SQL and include the following types of objects (Tables, Views, Clusters, Sequences…etc)
COMMIT , ROLLBACK: are transaction control commands (TCL) which we will explain it later in our discussion.
Data Definition Language – DDL: is used for building database relations (tables) and schema’s and also is used to define security and authorization information of each Relation.
For example :
CREATE to create objects in the database
ALTER alters the structure of the database
DROP delete objects from the database (e.g. delete table itself and its records)
TRUNCATE remove all records from a table and table still exist (e.g. delete table records and commit – can not ROLLBACK)
so, we can say briefly that the difference between DML and DDL is what shown in figure:
Data Control Language – DCL: controlling access to data and tables stored in DB. It has an Implicit COMMIT, Automatically saves changes to DB and makes it permanent and visible to Other DB users.
GRANT gives users access privileges(Object or System Privileges) to database
REVOKE to drop a privilege from users
Transaction Control Language – TCL: to manage the changes by DML and only DML.
Transaction (ALL OR NOTHING) : group of SQL statements that must be done together, if any error occurs it must be rolled back (e.g. transfer 100$ from account X to account Y. X will be modified to X-100 and Y will be Y+100. Assume you decreased X but you don’t increase Y yet and suddenly your system fails, so now you have X-100 and Y -inconsistent data- you must roll back the last event(decreasing X by 100) to have X and Y and then try again the Transfer Process).
Notice that Transaction may be Single Command, Portion of Code or Entire Program.
COMMIT make changes made by current transaction permanent in database (Transaction has been done successfully)
ROLLBACK is the inverse of COMMIT. it ignores/undoes changes and back to the state of DB at the last commit. (It is used when transaction has problems or transaction fails)
SAVEPOINT point in a Transaction that you can ROLLBACK to it without affecting the work has done by the transaction before this SAVEPOINT (This is Important for DB recovery)
And finally in brief any DB system can be considered as the following: