SQL (Structured Query Language)

Updated: Aug 12

SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e. data incorporating relations among entities and variables.


SQL offers three main advantages over older read–write APIs such as ISAM or VSAM.

  1. Firstly, it introduced the concept of accessing many records with one single command.

  2. Secondly, it eliminates the need to specify how to reach a record, e.g. with or without an index.

  3. Finally, SQL uses a human-readable syntax that allows users to be quickly productive without a requirement for long-term, technical training.


SQL data types



An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.


STRING DATA TYPE



NUMERIC DATA TYPE



DATE AND TIME DATA TYPE





SQL SERVER DATA TYPE


STRING DATA TYPE



NUMERIC DATA TYPE



DATE AND TIME DATA TYPE



OTHER DATA TYPE




MICROSOFT ACCESS DATA TYPE




What Can SQL do?

  • SQL can execute queries against a database

  • SQL can retrieve data from a database

  • SQL can insert records in a database

  • SQL can update records in a database

  • SQL can delete records from a database

  • SQL can create new databases

  • SQL can create new tables in a database

  • SQL can create stored procedures in a database

  • SQL can create views in a database

  • SQL can set permissions on tables, procedures, and views


Using SQL in Your Web Site

To build a web site that shows data from a database, you will need:

  • An RDBMS database program (i.e. MS Access, SQL Server, MySQL)

  • To use a server-side scripting language, like PHP or ASP

  • To use SQL to get the data you want

  • To use HTML / CSS to style the page


RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.


Some of The Most Important SQL Commands

  • SELECT - extracts data from a database

  • UPDATE - updates data in a database

  • DELETE - deletes data from a database

  • INSERT INTO - inserts new data into a database

  • CREATE DATABASE - creates a new database

  • ALTER DATABASE - modifies a database

  • CREATE TABLE - creates a new table

  • ALTER TABLE - modifies a table

  • DROP TABLE - deletes a table

  • CREATE INDEX - creates an index (search key)

  • DROP INDEX - deletes an index


Advantages of SQL :

SQL has many advantages which makes it popular and highly demanded. It is a reliable and efficient language used for communicating with the database. Some advantages of SQL are as follows:

  1. Faster Query Processing – Large amount of data is retrieved quickly and efficiently. Operations like Insertion, deletion, manipulation of data is also done in almost no time.

  2. No Coding Skills – For data retrieval, large number of lines of code is not required. All basic keywords such as SELECT, INSERT INTO, UPDATE, etc are used and also the syntactical rules are not complex in SQL, which makes it a user-friendly language.

  3. Standardised Language – Due to documentation and long establishment over years, it provides a uniform platform worldwide to all its users.

  4. Portable – It can be used in programs in PCs, server, laptops independent of any platform (Operating System, etc). Also, it can be embedded with other applications as per need/requirement/use.

  5. Interactive Language – Easy to learn and understand, answers to complex queries can be received in seconds.

  6. Multiple data views –


Disadvantages of SQL :

Although SQL has many advantages, still there are a few disadvantages. Various Disadvantages of SQL are as follows:

  1. Complex Interface – SQL has a difficult interface that makes few users uncomfortable while dealing with the database.

  2. Cost – Some versions are costly and hence, programmers cannot access it.

  3. Partial Control – Due to hidden business rules, complete control is not given to the database.


Applications of SQL :

  • SQL is used by developers and DBAs (Database Administrators) in writing Data Integration Scripts.

  • It is used to deal with analytical queries to analyze the data and get instincts from it.

  • Retrieving Information

  • Modification/Manipulation of data and database table such as Insertion, Deletion and Updation.