INTRODUCTION TO SQL
Overview : SQL is a language of database, it includes database creation, deletion, fetching rows and modifying rows etc.
What is SQL?
SQL is Structured/Syntax/Sequential Query Language, which is a language for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, and Oracle, Sysbase, Informatica and SQL Server use SQL as standard database language.
- Allows users to access data in relational database management systems.
- Allows users to describe the data.
- Allows users to define the data in database and manipulate that data.
- Allows users to create and drop databases and tables.
- Allows users to create view, stored procedure, functions in a database.
Why we say it’s a structured query language?
Structure: Is a set of rules or a predefined syntax
Sys >>SELECT <COLUMN_NAME> FROM <TABLE_NAME>;
Select means choosing
Column name – name of the column
From – pointing to the location
Table name – from this table
If I give from keyword in the place of select keyword and select keyword in the place of fromkeywordcan we get the query result? No!
We will get syntax error because Oracle cannot understand, which means all SQL statements are already defined by Oracle. So that’s why we call it is a Structured Query Language.
>>SELECT <COLUMN_NAME> FROM <TABLE_NAME>;
In above example users select the column from the table which means requesting the database to get the name of the column from the table.
Language: Channel of communication
If a user wants to interact/communicate with a database, can he directly interact with the Database? NO!
So for that we are using SQL. SQL act as an interface between a user and a database. So, SQL language is a channel of communication between a user and a database.
- It is English like language.
- ANSI standard language.
- Non-procedural language.
Why it is English like language?
The Keywords using in SQL is having general meaning in English language that why it is English like language.
Select – choosing something
Insert – appending or adding something
Update -modifying something
ANSI Standard language
ANSI stands for American National Standard Institute. It is a community of IT Professionals who tested SQL and approved as ANSI standardization.
Non Procedural language:
In Non-Procedural language a user has to give what to do but not how to do.
>>SELECT (10+20) FROM DUAL;
By firing a single query we will get the output.
What is Data?
Data is a collection of raw fact
What is a raw fact?
Raw fact is a collection of characters or numbers
RAM can be name or hardware component.
For this we are not getting any proper information. So what is information? If I write ‘RAM is a boy’ so everybody can understand RAM is the name of a boy. So here information is nothing but a processed data which has a meaning. So, where we are storing these data and information? It is going to store in a Database.
What is a database?
Database is a collection of organized data. Here organized means systematic manner.For example, phone book in Mobile phone. The records in the phone book are stored in systematic manner.If data is not in an organized manner then definitely it is going to decrease the performance.
What is DBMS?
DBMS stands for Database Management System. It is software which is used to manage Database.Before going to discuss about DBMS, I would like to discuss about Flat File because previously we used to store the data in flat files.
What is a Flat File?
Flat files are nothing but OS files. For example, Notepad in Windows In Notepad, we enter records line after line. If we want to retrieve nth record the search process starts from the beginning. So, it consumes a lot of time, with this performance is low. So this is one of the disadvantages of Flat File. Another disadvantage is security is low. Only at OS level security is possible. If we know the OS level password then we can access these files.
Now we will see the Advantages of DBMS
- Performance is high using Index concept.
For example Text book index page. I have 1000 pages book, I wanted to search for a particular topic. If I have an index page, then by seeing the index page, I can go directly to that particular topic. Likewise DBMS is also using an index concept to increase the performance.
Security is high.
There are 2 levels of securities namely.
- Operating system Level – Login ID/Password.
- Database Level – Privilege
In DBMS we are having some drawbacks.
- Does not support Join Concepts.
- Does not support Null Values.
Null! = Null and Null! = 0 or Space
- Redundancy is high.
Note: In order to overcome the drawbacks of DBMS, RDBMS (Relational Database Management System) comes into picture.