What is SQL? A Detailed Guide for Beginners

Posted in /  

What is SQL? A Detailed Guide for Beginners

Ramya Shankar
Last updated on December 8, 2022

    A database is a well-structured collection of data, and it stores and manipulates data electronically. The most notable advantage of a database is that it makes the storage, retrieval, and management of vast volumes of data effortless. Moreover, there are different types of databases available based on the way they store data.

    A relational database is a type of database that stores data in the form of tables, i.e., rows and columns. To query data stored in relational databases, a domain-specific language named SQL is used. SQL stands for Structured Query Language, which is specially designed to manage data stored in relational databases.

    This blog post will help you become acquainted with SQL, its elements, and different types of statements. Also, we will discuss the evaluation of an SQL query.

    So, let us begin our discussion!

    What is SQL?

    Structured Query Language is a domain-specific language that is specifically designed for managing data in relational database management systems (RDBMS). In simple terms, it is a language that facilitates communication between end-users or applications and databases.

    Generally, we pronounce the term ‘SQL’ as ‘ess-qew-ell’ or sequel. It is a de-facto standard language for all relational database management systems (RDBMS), such as MySQL, SQL Server, Oracle, Sybase, PostgreSQL, and Ingres.

    In addition, it is widely used for stream processing in a relational data stream management system (RDSMS). It is capable of performing multiple operations, along with the maintenance and optimization of relational databases.

    Basically, the structured query language is based on tuple relational calculus and relational algebra. It was one of the first languages that leveraged E.F. Codd’s relational model. Though this language does not completely adhere to the relational model of E.F. Codd, it is one of the most widespread languages for relational databases.

    Furthermore, SQL became a standard of the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) in 1986 and 1987, respectively.

    History

    In the early 1970s, Donald D. Chamberlin and Raymond F. Boyce initially developed SQL at IBM after learning E.F. Codd’s relational model. Initially, they named it SEQUEL, which stood for Structured English Query Language. Both the developers designed this language particularly for retrieving and manipulating data in IBM’s quasi relational database management system, System R.

    Even before SEQUEL, Boyce and Chamberlin, designed SQUARE (Specifying Queries in A Relational Environment) as a relational database language. However, it was difficult to use because of the subscript/superscript notation.

    A few years after the release of SEQUEL, the developers renamed it to SQL because ‘SEQUEL’ was the trademark of the Hawker Siddeley Dynamics Engineering Limited company. Later, SQL became an acronym for Structured Query Language.

    Why SQL?

    The following are the reasons why structured query language is popular:

    • It enables users to access data in relational databases.
    • This language assists you in describing data.
    • You can define data in a relational database and manipulate it easily.
    • It helps you create and drop databases and tables.
    • You can also use it to create views and stored procedures.
    • It lets you set permissions on tables, views, and stored procedures.

    Syntax

    The structured query language consists of various language elements that are as follows:

    • Keywords: Keywords are pre-defined words in the structured query language. They can be either reserved or non-reserved.
    • Identifiers: Identifiers are the names of database objects, such as tables, views, columns, database schema, etc.
    • Clauses: Clauses are the part of statements or queries.
    • Expressions: Expressions produce scalar values or tables consisting of data in the form of rows and columns.
    • Predicates: Predicates specify conditions whose value evaluates to the Boolean truth values or three-valued logic (true/false/unknown).
    • Queries: Queries help users to retrieve data from a database based on specific conditions.
    • Statements: A statement is any set of instructions consisting of identifiers, variables, parameters, names, data types, and reserved words.

    Advantages and Disadvantages of SQL

    Advantages

    The following are the remarkable benefits of the Structured Query Language:

    • Quick Query Processing: With the Structured Query Language, you can retrieve large amounts of data quickly without any hassle. Also, it lets you insert, delete, and update data in a database within a fraction of the time.
    • Easy To Learn: This query language is simple and easy to learn. Unlike other programming languages, there is no need to remember the complex syntax. Instead, this language uses simple English constructs that are easy to remember.
    • Large User Community: Structured Query Language is one of the oldest and most popular query languages, and hence, has a large user community. Also, the community has many experts willing to share the best practices for using this language.
    • Portable: It is a portable language since we can use it on PCs, servers, laptops, and even some mobile devices.
    • Multiple Data Views: You can use this language to create multiple views for a single database.

    Disadvantages

    Here are some of the major downsides of the Structured Query Language:

    • Complex Interface: This query language has a pretty complex interface. As a result, some users may find it difficult to use while working with databases.
    • High Cost: Some versions of this language are quite expensive. Therefore, some people may not afford it.
    • Partial Control: This language provides partial control over a database because of the hidden business rules.

    Types of SQL Statements

    There are five different categories of SQL statements, namely data definition language (DDL), data manipulation language (DML), data control language (DCL), data query language (DQL), and Transaction Control Language (TCL).

    Let us discuss each of these kinds of SQL statements in detail below.

    1. Data Definition Language (DDL)

    Data definition language (DDL) statements define the structure and schema of a database or table. There are four different types of DDL statements, mentioned as follows:

    • CREATE

    This statement creates a new database or table. Also, it helps us in creating other database objects, such as views and stored procedures.

    Syntax: The following is the syntax for creating a new database:

    CREATE DATABASE database_name;

    The following is the syntax for creating a new table in an existing database:

    CREATE TABLE table_name (COLUMN_NAME1 DATATYPE, COLUMN_NAME2 DATATYPE, ...COLUMN_NAMEn DATATYPE);

    Here is the syntax for creating a view:

    CREATE VIEW view_name AS
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
    • ALTER

    This command enables us to alter or modify the structure of a table, i.e., we can add, delete, modify, and rename any column of a database table.

    Syntax:

    • Add Column

    You can add a new column to an existing database table using the following syntax:

    ALTER TABLE table_name 
    ADD COLUMN column_name datatype;
    • Drop Column

    If you want to delete any column from an existing database table, use the following syntax:

    ALTER TABLE table_name
    DROP COLUMN column_name;
    • Modify Column

    Suppose that you want to change the data type of a particular column in a table. The following syntax will help you to do so:

    ALTER TABLE table_name
    MODIFY COLUMN column_name datatype;
    • Rename Table or Column

    If you want to rename the name of a particular table, use the following syntax:

    ALTER TABLE table_name 
    RENAME TO NEW table_name;

    The following syntax will help you rename a column:

    ALTER TABLE table_name
    RENAME COLUMN old_column_name to new_column_name;
    • DROP

    The DROP statement removes the tables and databases from the relational database management systems.

    Syntax: Here is the basic syntax for deleting a table from a database:

    DROP TABLE table_name;
    • TRUNCATE

    The TRUNCATE command frees up the space from a table by removing all the rows and columns.

    Syntax:

    TRUNCATE TABLE table_name;

    2. Data Manipulation Language (DML)

    Data Manipulation Language (DML) statements enable us to insert, delete, and update data in a database. There are three basic DML statements that are as follows:

    • INSERT

    This statement lets you insert new data or records into a table. The following syntax enables you to insert multiple values into a particular table:

    Syntax:

    INSERT INTO table_name (COL1, COL2, ...,COLn)
    VALUES (VALUE1, VALUE2, ...,VALUEn);

    or

    INSERT INTO table_name
    VALUES (VALUE1, VALUE2, ...,VALUEn);
    • DELETE

    This statement deletes one or more records from the specified table.

    Syntax:

    DELETE FROM table_name
    WHERE condition;
    • UPDATE

    If you want to modify or update any existing values in a table, you can use the UPDATE statement.

    Syntax:

    UPDATE table_name 
    SET [COLUMN_NAME1 = VALUE1, COLUMN_NAME2 = VALUE2, …,COLUMN_NAMEn = VALUEn]
    WHERE condition;

    3. Data Control Language (DCL)

    Data Control Language (DCL) statements allow you to have control over the data in a database. You can grant or revoke rights of accessing a database by a particular user with DCL commands. There are two DCL commands, namely GRANT and REVOKE.

    • GRANT

    Using this command, you can give a particular user the access privilege to a database.

    Syntax:

    GRANT privilege_name
    ON object_name
    TO {user_name |PUBLIC |role_name}
    [WITH GRANT OPTION];
    • REVOKE

    The REVOKE statement takes away the previously granted permissions from a specific user.

    Syntax:

    REVOKE privilege_name
    ON object_name
    FROM {user_name |PUBLIC |role_name}

    4. Transaction Control Language (TCL)

    Transaction Control Language (TCL) statements help you in managing transactions taking place in a database. They manage all the changes that you make to a database using DML commands.

    The following are the three TCL statements in SQL:

    • COMMIT

    This statement allows you to permanently save all the transactions you perform in a database.

    Syntax:

    COMMIT;
    • ROLLBACK

    The ROLLBACK statement lets you undo transactions that have not been saved to a database. In other words, it helps you restore the database from the last committed state.

    Syntax:

    ROLLBACK;
    • SAVEPOINT

    When you want to temporarily store a transaction in a database so that you can roll back to that point as and when required, you can use the SAVEPOINT statement.

    Syntax:

    SAVEPOINT savepoint_name;

    5. Data Query Language (DQL)

    There is only one command in DQL called SELECT, which is used to retrieve data from a database.

    Syntax: The following syntax retrieves the entire data from the specified table:

    SELECT * FROM table_name;

    If you want to retrieve specific records from a table, use the following syntax:

    SELECT expressions
    
    FROM table_name
    WHERE conditions;

    SQL Query Evaluation

    Whenever we provide a SQL query to any relational database, it immediately responds to us with the required data. This seems so easy, right? But, have you ever wondered how the database does it?

    Well, it uses a SQL query execution engine that is present on the top of the database, executes all the user queries against the data in that database, and provides the desired output.

    Let us understand the evaluation of a SQL query which consists of four different phases, as explained below:

    • Parser/Translator

    As soon as we submit a query to a database, the parser is responsible for verifying its syntax. It checks whether the query is syntactically correct or not. Also, sometimes, it checks the semantics of the query.

    However, the SQL query we provide is basically a high-level language. As a result, the translator converts it into a low-level language or machine language. More specifically, it converts the query into a relational algebraic expression.

    • Optimizer

    Once the translator converts the query into a relational algebraic expression, multiple query execution plans are created to run the query. However, it is essential to choose the best and optimal plan for executing the query. This is where the role of the optimizer comes into play. It determines the best and low-cost plan for query execution.

    • Execution Plan

    During the phase of the execution plan, the database decides the order of execution of the instructions the query contains. For instance, consider the following query:

    SELECT * FROM Employee
    WHERE emp_age>35;

    In the above query, the database will first execute the ‘FROM’ statement, later ‘WHERE’, and finally the ‘SELECT’ statement. So, the execution order becomes

    FROM -> WHERE -> SELECT

    • Query Execution Engine

    Finally, the query execution engine takes the query evaluation plan from the database, executes it by retrieving data from the database, and responds to you with the desired data. In short, the query execution engine interprets the SQL commands , accesses the relational database, and returns the requested data.

    Conclusion

    SQL or structured query language is a standard language for querying data in relational databases. It helps us create various database objects, such as tables, views, and stored procedures. Also, it lets us insert new data, delete existing data, and update data in any relational database.

    Through this blog post, we have covered different types of SQL statements that are useful in performing various operations on a database. Also, we have discussed how a SQL query evaluates and provides us with the desired data.

    People are also reading:

    FAQs


    The primary purpose of a structured query language is to communicate with relational databases, such as MySQL, SQL Server, Oracle, etc. It performs various operations on relational databases, such as adding data, deleting data, and modifying data.

    A structured query language is capable of processing large volumes of data quickly and efficiently. Unlike Excel, it can handle millions of data fields with ease because SQL queries are more robust than Excel formulas. A single SQL query can retrieve multiple data records.

    You can learn the structured query language by practicing it. Just install an open-source relational database management system, such as MySQL or SQLite, and start practicing SQL queries on it using your own data.

    Leave a Comment on this Post

    0 Comments