SQL & My SQL Tutorial
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
What is SQL?
A domain-specific language developed to manage the data and kept in a relational database management system is called SQL. SQL stands for Structured Query Language and is developed by ISO/IEC, and the typing discipline is made stronger. The statements are used to perform many operations on a database, such as retrieval of data, updating data, removing and renaming data, and deleting data. Oracle, Sybase, Microsoft SQL Server is some of the databases that use SQL language. We cannot call SQL a programming language, but it has some standards to create procedural extensions so that the functionalities can be extended to form a programming language.
Before understanding SQL, we need to understand RDBMS first. RDBMS or Relational Database Management System is simply a database that stores structured data, and there is a relation between the data. It stores data in tabular format. It has column and rows which contains related data entries. Columns are a vertical entity of a table. It contains the attribute of records, and rows are the horizontal entity that contains records/data. The intersection of rows and column contains the information of a record with respect to that attribute.
Given below is an example of a table:
It is a language used to query over tabular data. Unlike other languages, SQL is a declarative language; one just needs to specify the result that they want to see and submit the query to RDBMS. RDBMS executes the code at the backend and gives the desired output. Whereas in a procedural language, we have to tell a computer each and every step to perform in order to get the output. So if you want to select data from the above table, you just need to write the below query and execute it.
SELECT * FROM CustomerDetail;
A confusion with SQL is the syntax of SQL query. The elements are not executed in the order they are used in the query. Consider selecting data from the above CustomerDetail table.
SELECT ID, Name, Age FROM CustomerDetail
WHERE Age > 30
Order By ID DESC;
The above query will select all the records which have an age greater than 30 and display the result by order of ID.
The sequence of execution of elements are as follows:
In the query FROM, a clause is executed first. It selects the tables and joins tables to get the base data.
This clause filters the base data. So that there are fewer records in further processing.
Group By clause combines rows into groups to perform aggregation.
This clause is used to filter the aggregated data on the basis of the calculated column.
This clause returns the selected records in the format requested by the user.
This clause sorts the final data.
So the lexical order and logical order of clauses in a SQL query differ, but one needs to take care of these things when the performance comes into the picture. For smaller data retrieval user has to just mention the output he/she expects.
SQL make Working so Easy
The most important feature of SQL which makes it easy to work with is that it hides the complexity of processing. Since it is a declarative language, the programmer just needs to specify the format of output as per the requirement, and the server will take care of all the complexity of retrieval and aggregation. So the code to retrieve data from a table will be smaller if written in SQL as compared to code written in any other language.
It deals only with database objects. This is an advantage as well as a limitation of SQL. Because of this, it can be used to handle only structured data. with limited objects and structured data, working is easy in SQL. Even after dealing only with structural data, it has more importance than any other programming language, and it is easy to learn. This is based on basic relational algebra and tuple calculus. It takes just a few days to learn the basics of SQL. One can also learn this from an online tutorial. But becoming an expert and getting performance-related expertise is an altogether different thing in SQL. It will take some time and hands-on experience. It also supports all the mathematical and string functions to modify the data according to need. It has all the features provided in any other programming language. This makes it an easier language to work with.
Every programming language requires interaction with the back-end database, and this has the extensibility that it can be integrated into any language. Thus making it easy to work with any other programming language.
Various Subset of SQL
SQL queries can be categorized into 4 main category:
DDL (Data Definition Language)
As the name suggests, these types of queries are used to define the structure of data. Like the structure of a table, schema and modify it.
This command is used to create tables, database, schema etc.
This command is used to drop tables and other database objects.
This command is used to alter the definition of database objects.
This command is used to remove tables, procedures, views, and other database objects.
This command is used to add any column to the table schema.
This command is used to drop a column from any table structure.
DML (Data Manipulation Language)
This type of queries is used to manipulate data in the database.
This command is used to select data from one table and insert it into another table.
This command is used to insert data/records into a table.
This command is used to delete records from the table.
This command is used to update the value of any record in the database.
DCL (Data Control Language)
This category of SQL queries deals with the access rights and permission control of the database.
This command is used to grant access rights to database objects.
This command is used to withdraw permission from database objects.
TCL (Transaction Control Language)
The transaction is a set of commands that perform a specific task on objects in a single unit of execution. So TCL commands deals with transactions in a database.
This command is used to commits a transaction. Once committed, it cannot be rolled back. This means the previous image of the database before running this transaction cannot be retrieved.
Rollback is used to revert the steps in transactions if an error occurs.
This command sets a savepoint in the transaction to which steps can be rolled back.
This command is used to set the characteristics of the transaction.
What can you do with SQL?
It is mainly used in SQL SERVER MANAGEMENT STUDIO, a tool to manage databases and data. It was launched by Microsoft for configuring, managing, and administrating all the components of the database.
Given below are the main operations one can do with SQL:
It can be used to create a Database and its other objects. One can create a table to store data, stored procedures, functions to process data and views to view data. The user can also play around with joining data from different tables and get meaningful output.
A user can also manage the access rights on the database and its objects using SQL. One can check which user has executed which query and also the privileged user has. An administrator can grant and revoke access from a user.
Managing data is not an easy task. Especially when it’s important to business and has a huge size. So efficient storage and retrieval of data are important. SQL lets you do that without any hassle.
These commands help you manipulate your data. Insert data into tables, delete records, update records all can be done easily using SQL commands. A user can also join different tables and have a view of collective data.
This can also be used with the integration of another programming language. Every programming language has an extension to embed SQL in its code.
Working with SQL
As we know, this is a querying language, and it deals with the data stored in the back end. Hence the interface is not so interesting. One won’t get UI to play with colors and designs. There are just tables with columns and rows. But if data really interests you, then SQL is the language you must learn. Working with SQL, you get to play with data, join tables and perform tuning. You can write some procedures and transactions to perform analysis tasks and also schedule a job using SQL.
Advantages of SQL
Below are some of the advantages of SQL:
Requires no coding:
This is declarative language; one just needs to mention the output he/she wants. It has straightforward commands to perform actions like select, update, delete, etc. One does not need to write complex code to retrieve data from a database or manipulate the data.
Well defined standard:
It is an ANSI standard language. It has been established as a standard language for querying RDBMS.
It is used to communicate with a database and its objects. We can get the output of complex queries within seconds.
It’s easy to update records in SQL and maintain the integrity of data. The relationship can also be implemented between two tables.
It can be integrated with other languages to connect with the database. The SQL query can be embedded in any other programming language used for application development.
Required SQL Skills
Almost in every organization, there is a need for an SQL developer.
Below are the skills that are in demand:
Unlike front-end developer who managed the look and feels of a web app, back-end developer has to manage the data show to the user is proper and data updated in the database tables are correct.
A database administrator is someone who managed the database and its objects. DBA is the one who decides on the access right of users.
The data analyst is the one who analyzes the data for a meaningful output.