SQL(Structured Query Language)

SQL(Structured Query Language)

SQL stands for Structured Query Language. Using SQL one can create and maintain data manipulation objects such as tables, views, sequences etc. These data manipulation objects will be created and stored on the server’s hard disk drive, in a tablespace, to which the user has been assigned.
Once these data manipulation objects are created, they are used extensively in commercial applications.
In addition to the creation of data manipulation objects, the actual manipulation of data within these objects is done using SQL.
The SQL sentences that are used to create objects are DDL(Data Definition Language). The SQL sentences used to manipulate data within these objects are called DML(Data Manipulation Language). The SQL sentences, which are used to control the behaviour of these objects, are called DCL(Data Control Language).
Oracle provides an Interactive SQL tool called SQL*Plus, which allows the user to enter SQL sentences and pass them to the oracle engine for execution. Hence, once access to the SQL*Plus tool is available and SQL syntax is known, the creation of data storage and the manipulation of data within the storage system, required by commercial applications, is possible.
The syntax provided by SQL is shared by many RDBMS(Relational Database Managemant System) languages like SQL Server, MySQL, Oracle etc.
Note:-SQL is not case-sensative. Data and message are always case sensative. For e.g.- ‘RAM’,’Ram’,’ram’,’rAm’ etc, all are considered as different values.

Basic terminology used for creation and manipulation of data in SQL:-

• Table: In a DBMS a group of similar information or data which is of interest to an organization is called an Entity. Entity information stored in an object called Table. A table is a basic data storage structure. It is made up of rows and columns which contains the same kind of values. The table must have a unique name with which it can be referred to, after its creation. For e.g: a worker is considered as an entity. Information about worker can be stored in ‘worker_master’ table.
• Field: In a table field is the information stored in the intersection of a row and a column. It is the smallest unit of data which has the meaning to its user’s. It is better known as Attributes in SQL. It is also known as data elements or elementary data. In other words, it is a specific category of information in a database. For e.g: a worker_master table can have attributes like worker_id, worker_name, salary, phone etc.
• Record: A record is a synonym for a row. It can be defined as a set of fields in a table. Basically, it is a collection of multiple related fields that can be treated as a unit. For e.g: The field representing all the data about one worker in the ‘Workers_master’ table. In database terminology, sometimes records are also known as tuples.

Datatypes which attributes of a table can hold:-

• NUMBER(P,S)-The NUMBER data type is used to store numbers(fixed or floating point). It can store upto 38 digits of precision. The precision, P, determines the maximum length of data, whereas the scale, S, determines the number of places to the right of the decimal. If precision is omitted, values are stored with their original precision upto themaximum of 38 digits. If scale is omitted then the default is zero.
• DATE-This data type is used to represent date and time. The standard format is DD-MON-YY. To enter dates in other than standard format, the approprite functions can be used. By default, the time in a date field is 12:00:00 am, if no time portion is specified.
• CHAR(size)-This data type is used to store character string values of fixed length. The ‘size’ in bracket determines the number of characters the cell can hold. The maximum number of characters this data type can hold in 255 characters. If a value that is inserted in a call of CHAR datatype is shorter than the size it is defined for then it is padded with spaces on the right until it reaches the size characters in length.
• VARCHAR(size)-It stands for ‘variable character’.This data type is used to store variable length alphanumeric data. The maximum value this datatype can hold is 2000 characters and also occupy space for NULL value. Here, the inserted value will not be padded with spaces.
• VARCHAR2(size)-VARCHAR2 is same as VARCHAR but there is a difference that it can store upto 4000 bytes of characters and do not occupy space for NULL value. For e.g.- There is a field called ‘Name’. If we have specified its data type as char(20), then it is fixed that it will allocate 20 memory spaces. But any value is small, say, ‘Ramesh’, it is only of 6 charcters, so, rest 14 memory spaces are wasted. Therefore, it better to allot varchar(20) or varchar2(20) to ‘Name’ attribute. Now, it will only allocate 6 memory spaces for value ‘Ramesh’.
• LONG-This data type is used to store variable length character strings containing upto 2GB. LONG data can be used to store arrays of binary data in ASCII format. Long values cannot be indexed, and the normal character functions can’t be applied to LONG values.
• RAW/LONG RAW-The RAW/LONG RAW data types is used to store binary data, such as digitized picture or image. Data loaded into columns of these data types are stored without any further conversion. RAW can have maximum length of 255 bytes. LONG RAW can contain upto 2 GB. Values stored in columns having LONG RAW data type can’t be indexed. Indexing is discussed further.

Author Name: Praveen Kumar

Department: MCA
Email-id: Praveen.mca@patmawomenscollege.in