SQL Tutorial – Introduction6 min read

Introduction

SQL stands for structured query language. It enables you to create and operate a relational database by storing the information in the form of a table. MySQL is based on Structured Query Language is commonly termed a relational database management system (RDBMS). MySQL is primarily used for the purpose of creating & manipulating a web database. It is also used to query data from the database storage structure such as tables and views.

SQL is usually used in 2 modes:

  1. Interactive mode: Here users type the SQL command and see the response on the same terminal and hence known as WYSWYG (What You See is What You Get). Thus it helps to create, access, and maintain the data by means of tables, views, indexes, etc.
  2. Programming Language Interface or Embedded SQL: Here SQL command may be embedded in an application program written in a traditional programming language such as COBOL, PASCAL, C, or PL/SQL.

Features

  1. SQL is an Interactive Query Language that allows users to use SQL statements to retrieve data and display it on screen.
  2. SQL is a Database Administration Language that defines the structure of the database and controls the access of users over data.
  3. SQL is a Client-Server Language that allows application programs from the client-side to access/share data stored on Server.

Limitations

It is able to execute only one SQL command at a time. It is not possible to express all sorts of user queries using SQL as it does not
support the full flagged capability of procedural language.

Using SQL, it is not possible to perform the following set of actions:

  • Printing a customized report.
  • Formatting query results in GUI (Graphical User Interface) format.

Components of SQL

In order to work with database, SQL supports following categories of language :

1. Data Definition Language (DDL): It is used to create, remove or alter the structure of tables and database objects. It includes the following processes :

  • Creation of table
  • Altering table
  • Dropping table
  • Truncating table
  • Granting & Revoking Privileges

Note: In Oracle SQL, each DDL statement performs an automatic commit operation, which ends the current transaction and saves all changes to the database.

2) Data Retrieval Language (DRL): It enables the user to retrieve the data which is used to query the existing database. It includes the SELECT statement on a table(s) or view(s).

3) Data Manipulation Language (DML): It enables users to retrieve, insert, update, and delete the data. DML is used to query and manipulate a database that already exists. It includes the following processing statements/operations on a table(s) or view(s) :

  • Insert – Inserting a record into the table.
  • Update – Editing or modifying column(s) value of record(s).
  • Delete – Deleting unnecessary record(s).

4) Data Control Language (DCI): It enables the user to control the way data can be accessed or shared. It includes the following two statements/operations :

  • Grant – Allows other users to share tables.
  • Revoke – Denying other users to share table after granted

5) Transaction Control Language (TCL) : It enables the user to save or undo the DML operation on the database. It includes the following statements/operations :

  • Commit – Saving all DML operations permanently.
  • Rollback – To undo all DML operations up to the last save.
  • Savepoint – Point at the transaction.

SQL Data Types

A data type helps the user to know what kind of data the column or field of table or cluster or variable can hold. When we create a procedure or function, we must specify a data type for each of its arguments. Oracle supports following internal data types.

SQL datatypes
SQL datatypes

NUMBER (P.S)

Variable length numeric having precision (P) in the range 1-38 and scale (S) in the range -84 to 127. Specification of precision and scale is optional as Oracle assumes the maximum range for both values. It is also used to store signed integers, fixed-point numbers, and floating-point numbers.

Example:

  1. empno number(2)
  2. sal number(7.2)

Points to remember while working with number data type:

  1. If precision and scale are not specified then it assumes maximum range for both the values i.e. for P and S.
  2. Scale (S) rounds the value after the decimal point to S digits.
  3. If the Scale is negative, S numbers of zeros are placed left of the decimal.
  4. If Scale value is greater than precision value, (S-P) zeros are required after decimal
  5. If the value exceeds the precision an error is generated.

Number Subtypes:

Following sub-types can be used during defining a number of data types :

  • INTEGER, SMALLINT, INT is used to insert integer values.
  • FLOAT, REAL is used to insert floating values.

DATE

This data type is used to store date and time information. Oracle software is the year 2000 (Y2K) compliant i.e. when a new row is inserted into a table for a date column, the century information is picked up from the SYSDATE function. Oracle stores Date data as a number in an internal numeric format, representing the Century, Year, Month Day, Hours, Minutes, and Seconds and stores the date in a fixed length of 7 bytes. The default format of date is DD/MM/YY and it can be modified by changing the DATE_FORMAT parameter in init.ora file. Using date conversion function users can view dates in different formats.

Valid dates are between 1-JAN-4712 B.C. to 31-DEC-9999 A.D

Example:

A date 5-JUN-07 at 9.30 p.m. is stored internally as follows:

CenturyYearMonthDayHourMinuteSecond
20 07 06 5 09 30 45
It stores system time automatically during insertion of a record

E.g. DOB date

RAW (size)

It is useful to store binary data and is used to store a graphic or digitized picture. Maximum 2000 bytes can be allotted for RAW-type columns. One must specify the size. Only storage and removal of data are possible using this data type and one can not manipulate the data but it can be indexed.

LONG RAW

It is same as RAW data type but it can store a raw binary data type up to 2 gigabytes.

ROWID and UROWID

It is a binary representation of the physical address of a row. which is displayed in hexadecimal format. UROWID Universal Rowid datatype can store physical, logical, and foreign (Non-Oracle) required.

LONG

It is used to store a variable-length character data type up to gigabytes. Restrictions on use of LONG datatype:

  • Tables can have only one column having long data types.
  • It can not be indexed.
  • It can’t appear in the group by an order by clause or select statement.
  • Procedure and stored procedure cannot accept the long datatype as arguments

CHAR (size)

It is a fixed-length character data type of length size bytes. The maximum size is 2000 bytes. Default and the minimum size is 1 byte when size is not specified. A value ram is not the same as ‘RAM’ or ‘Ram” data/values are case-sensitive in Oracle. If entered value size is larger than the defined size, an error is generated, and if the user enters a value shorter than the specified length then the database blank pads to the fixed length.

VARCHAR2 (Size)

It is a variable-length character string data type of length Size bytes. The maximum size is 4000 bytes while the minimum size is the. One must specify the size. The value ‘ram’ is not the same as ‘RAM’ or ‘Ram’. Data are case-sensitive. Varchar datatype is a synonym of Varchar2 data type.


Share:

Leave a Reply