Introduction To SQL

Introduction To SQL

·      SQL basics

·      More advanced SQL queries

·      Relational queries in SQL

·      Modifying databases with SQL

·      Further learning in SQL

What is SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

SQL Commands

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature −

DDL - Data Definition Language

CREATE

Creates a new table, a view of a table, or other object in the database.

ALTER

Modifies an existing database object, such as a table.

DROP

Deletes an entire table, a view of a table or other objects in the database.

DML - Data Manipulation Language

SELECT

Retrieves certain records from one or more tables.

INSERT

Creates a record.

UPDATE

Modifies records.

DELETE

Deletes records.

DCL - Data Control Language

GRANT

Gives a privilege to user.

REVOKE

Takes back privileges granted from user.

What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

What is a table?

The data in an RDBMS is stored in database objects which are called as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows.

Remember, a table is the most common and simplest form of data storage in a relational database.

SQL Basics

SQL is followed by a unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax.All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

The most important point to be noted here is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database.

SQL | DDL, DQL, DML, DCL and TCL Commands

Structured Query Language(SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert etc. to carry out the required tasks.

These SQL commands are mainly categorized into four categories as:

1.    DDL – Data Definition Language

2.    DQl – Data Query Language

3.    DML – Data Manipulation Language

4.    DCL – Data Control Language

Though many resources claim there to be another category of SQL clauses TCL – Transaction Control Language. So we will see in detail about TCL as well.

1.    DDL(Data Definition Language) : DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL commands:

·      CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

·      DROP – is used to delete objects from the database.

·      ALTER-is used to alter the structure of the database.

·      TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.

·      COMMENT –is used to add comments to the data dictionary.

·      RENAME –is used to rename an object existing in the database.

2.    DQL (Data Query Language) :

DML statements are used for performing queries on the data within schema objects. The purpose of DQL Command is to get some schema relation based on the query passed to it.

Example of DQL:

·      SELECT – is used to retrieve data from the a database.

3.    DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

Examples of DML:

·      INSERT – is used to insert data into a table.

·      UPDATE – is used to update existing data within a table.

·      DELETE – is used to delete records from a database table.

4.    DCL(Data Control Language) : DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

Examples of DCL commands:

·      GRANT-gives user’s access privileges to database.

·      REVOKE-withdraw user’s access privileges given by using the GRANT command.

5.    TCL(transaction Control Language) : TCL commands deals with the transaction within the database.

Examples of TCL commands:

·      COMMIT– commits a Transaction.

·      ROLLBACK– rollbacks a transaction in case of any error occurs.

·      SAVEPOINT–sets a savepoint within a transaction.

·      SET TRANSACTION–specify characteristics for the transaction.

SQL | DROP, TRUNCATE

DROP

DROP is used to delete a whole database or just a table.The DROP statement destroys the objects like an existing database, table, index, or view.

A DROP statement in SQL removes a component from a relational database management system (RDBMS).

Syntax:

DROP object object_name

Examples:
DROP TABLE table_name;
table_name: Name of the table to be deleted.

DROP DATABASE database_name;
database_name: Name of the database to be deleted.

TRUNCATE

TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the SQL:2008 standard.

The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).

Syntax:

TRUNCATE TABLE  table_name;
table_name: Name of the table to be truncated.
DATABASE name - student_data

DROP vs TRUNCATE

·      Truncate is normally ultra-fast and its ideal for deleting data from a temporary table.

·      Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.

·      Table or Database deletion using DROP statement cannot be rolled back, so it must be used wisely.

 SQL | Aliases

Aliases are the temporary names given to table or column for the purpose of a particular SQL query. It is used when name of column or table is used other than their original names, but the modified name is only temporary.

·      Aliases are created to make table or column names more readable.

·      The renaming is just a temporary change and table name does not change in the original database.

·      Aliases are useful when table or column names are big or not very readable.

·      These are preferred when there are more than one table involved in a query.

Basic Syntax:

·      For column alias:

·       SELECT column as alias_name FROM table_name;
·       column: fields in the table
·       alias_name: temporary alias name to be used in replacement of original column name 
·       table_name: name of table

·      For table alias:

·       SELECT column FROM table_name as alias_name;
·       column: fields in the table 
·       table_name: name of table
·       alias_name: temporary alias name to be used in replacement of original table name

Queries for illustrating column alias

·      To fetch ROLL_NO from Student table using CODE as alias name.

·       SELECT ROLL_NO AS CODE FROM Student;

·      To fetch Branch using Stream as alias name and Grade as CGPA from table Student_Details.

·       SELECT Branch AS Stream,Grade as CGPA FROM Student_Details; 

SQL | ORDER BY

The ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns.

·      By default ORDER BY sorts the data in ascending order.

·      We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Syntax of all ways of using ORDER BY is shown below:

·      Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively.

Syntax:

·       SELECT * FROM table_name ORDER BY column_name ASC|DESC
·       
·       table_name: name of the table.
·       column_name: name of the column according to which the data is needed to be arranged.
·       ASC: to sort the data in ascending order.
·       DESC: to sort the data in descending order.
·       | : use either ASC or DESC to sort in ascending or descending order

·      Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by (,) operator.

Syntax:

·       SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC

Queries:

·      Sort according to single column: In this example we will fetch all data from the table Student and sort the result in descending order according to the column ROLL_NO.

·       SELECT * FROM Student ORDER BY ROLL_NO DESC;

In the above output you can see that first the result is sorted in ascending order according to Age.

There are multiple rows having same Age. Now, sorting further this result-set according to ROLL_NO will sort the rows with same Age according to ROLL_NO in descending order.

·      Note that: ASC is the default value for ORDER BY clause. So, if you don’t specify anything after column name in ORDER BY clause, the output will be sorted in ascending order by default.

Example: The following query will give similar output as the above:

SELECT * FROM Student ORDER BY Age , ROLL_NO DESC;

SQL | MERGE Statement

Prerequisite – INSERT, UPDATE, DELETE

The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE and DELETE. In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handle the large running databases. But unlike INSERT, UPDATE and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called as target table.

Now we know that the MERGE in SQL requires two tables : one the target table on which we want to perform INSERT, UPDATE and DELETE operations, and the other one is source table which contains the new modified and correct data for target table and is actually compared with the actual target table in order to modify it.

In other words, the MERGE statement in SQL basically merges data from a source result set to a target table based on a condition that is specified. The syntax of MERGE statement can be complex to understand at first but its very easy once you know what it means.So,not to get confused first let’s discuss some basics. Suppose you have two tables: source and target, now think if you want to make changes in the required target table with the help of provided source table which consists of latest details.

·      When will you need to insert the data in the target table?

Obviously when there is data in source table and not in target table i.e when data not matched with target table.

·      When will you need to update the data?

When the data in source table is matched with target table but any entry other than the primary key is not matched.

·      When will you need to delete the data?

When there is data in target table and not in source table i.e when data not matched with source table.