Nowadays, every organization needs data to run its business. Databases are the best option to store organized collection of data. And, SQL (Structured Query Language) is the most widely used programming language for organizing and retrieving the data in a database. It allows us to perform all the CRUD (create, read, update and delete) operations in the database.
In this article, we will discuss the basic concepts of SQL that every developer should know to effectively manage data in a database.
The main purpose of SQL is to operate and retrieve information from the relational database. It allows us to create new databases, views, tables, stored procedures, and functions.
We should have some knowledge of the following terms before we get started with SQL.
A table is a database object that presents data in columns and rows.
Rows are described as records. The columns are described as fields that represent the category of the records. For example, a table of student details contains a row for each student and a column for each detail such as age, height, and so on.
The basic things required to create a column are names and data types. SQL supports the following three data types:
SQL is a case-insensitive language. The major three clauses in SQL are Select, from, where.
Select is the most important clause in SQL. It helps us retrieve data from the table. Simply, it answers What data should we show?
This retrieves data from a specific table in a database. Simply, it answers Where do we get the data from?
This retrieves specific data records in a table. Simply, it answers Which category data should we show?
Syntax:
Select * from <<Table Name>> where <<Column Name>> = <<Value>>
Example:
There are five categories in SQL:
As a developer, we have to know the following commonly used keywords and their usage in SQL to get started with it:
This keyword enables us to create a new table.
Syntax:
Create table <<table name>> ( <<column name1>> datatype, <<column name2>> datatype )
Example:
Output:
This keyword is used to insert new records (rows) in a table.
Syntax:
Insert into <<table name>> values (value1, value2, ….)
Example:
Output:
Similarly, you can add multiple entries at a time like in the following image.
Syntax:
Insert into <<table name>>
values (value1, value2, ….),
(value1, value2, ….),
(value1, value2, ….), Example:
Output:
Updates the existing records in a table.
Syntax:
Update <<table name>>>> set <<column name>> = <<value>> where <<column name>> = <<value>>
Example:
Before Update:
After Update:
This keyword removes duplicate records and gets the unique records from a table.
Syntax:
Select distinct * from <<table name>>
Example:
Syntax:
Select distinct <<column name>> from <<table name>>
Example:
Use this keyword to get the top values in a table.
Syntax:
Select top Numeric Value * from <<table name>>
Example:
We can use this keyword to sort the records in ascending or descending order.
Syntax:
Select * from <<table name>> order by <<column name>> asc
To sort the records in ascending order, use the keyword asc.
Example:
Syntax:
Select * from <<table name>> order by <<column name>> desc
Example:
This keyword is used to display the records that satisfy all the conditions in the where clause.
Syntax:
Select * from <<table name>> where <<column name>> = <<value>> and <<column name>> = <<value>>
Example:
This keyword displays the records that satisfy any one of the conditions in the where clause.
Syntax:
Select * from <<table name>> where <<column name>> = <<value>> or <<column name>> = <<value>>
Example:
This keyword displays the records that don’t satisfy the provided condition.
Syntax:
Select * from <<table name>> where not <<column name>> = <<value>>
Example:
This keyword displays the smallest value in a column.
Syntax:
Select min(<<column name>>>>) from <<table name>>
Example:
This keyword displays the largest value in a column.
Syntax:
Select max(<<column name>>) from <<table name>>
Example:
This keyword displays the total sum value for the numeric column.
Syntax:
Select sum(<<column name>>) from <<table name>>
Example:
This keyword retrieves multiple values that satisfy the condition in the where clause.
Syntax:
Select * from <<table name>> where <<column name>> in (<<value1>>, <<value2>>, ….)
Example:
This keyword retrieves multiple values that don’t satisfy the condition in the where clause.
Syntax:
Select * from <<table name>> where <<column name>> not in (<<value1>>, <<value2>>, ….)
Example:
Use the Count keyword to return the total number of rows in a table. We can use this to return the number of rows that satisfy the specified condition.
Syntax:
Select count(*) from <<table name>>
Example:
This keyword returns the average value of a column.
Syntax:
Select avg(<<column name>>) from <<table name>>
Example:
This keyword is used to check whether the required records exist or not in a table.
Syntax:
Select * from <<table name>> where <<column name>> = any(Select * from <<table name>> where <<column name>> = <<value>>)
Example:
This keyword retrieves a specified pattern in a column.
Syntax:
Select * from <<table name>> where <<column name>> like ‘%Value%’
Example:
This keyword is used to combine two or more select statements.
Syntax:
Select * from <<table name1>> union Select * from <<table name2>>
Example:
This keyword retrieves the rows that satisfy the null value in a specific column.
Syntax:
Select * from <<table name>> where <<column name>> is null
Example:
This keyword retrieves the rows that satisfy the not null value in a specific column.
Syntax:
Select * from <<table name>> where <<column name>> is not null
Example:
This keyword is used instead of a particular character in a string to retrieve all possible values. Some common wildcard values in SQL are: _ * ? [] ! –
Syntax:
Select * from <<table name>> where <<column name>> like ‘<<value with WildCardValue>>’
Example:
This keyword is used to group records. The main purpose of this statement is to find how many records have the same values in a table.
Syntax:
Select count(<<column name>>), <<column name>> from <<table name>> group by <<column name>>
Example:
We can give a temporary name for a column in the table. Here, the CustomerID column is named Id, and the CustomerName column is named Name.
Syntax:
Select <<column name>> as <<temporary name>> from <<table name>>
Example:
This keyword retrieves the values within the given range.
Syntax:
Select * from <<table name>> where <<column name>> between <<value>> and <<value>>
Example:
This keyword combines records from two or more tables using the common field in them. There are four types of join in SQL:
Let’s look at these Join keywords with example data.
Table 1: Students
Inner Join:
The Inner Join returns the records that match the values in both tables. Inner Join is commonly referred to as just Join.
Syntax:
Select <<column name>> from <<table name1>> Inner join <<table name2>> on <<table name1>>.<<column name>> = <<table name2>>.<<column name>>
Example:
Left Join:
Left join returns all the records from the left-side table and the matching records from the right-side table of the Join keyword.
Syntax:
Select <<column name>> from <<table name1>> Left join <<table name2>> on <<table name1>>.<<column name>> = <<table name2>>.<<column name>>
Example:
Right Join:
Right join returns all the records from the right-side table and the matching records from the left-side table of the Join keyword.
Syntax:
Select <<column name>> From <<table name1>> Right join <<table name2>> on <<table name1>>.<<column name>> = <<table name2>>.<<column name>>
Example:
Full Join:
Full join finds the matching records from both the Left Join and Right Join tables and returns all the records for comparision. If there is no match found, then it will return a NULL value for those records. It is also referred as Full Outer Join.
Syntax:
Select <<column name>> from <<table name1>> Full join <<table name2>> on <<table name1>>.<<column name>> = <<table name2>>.<<column name>>
Example:
Primary Key ensures that a value in a record is unique. It never contains NULL values.
Syntax:
Create table <<table name>> ( <<column name1>> datatype not null primary key, <<column name2>> datatype )
Example: Here, the RollNo column is marked as Primary Key, as each student has a unique roll number.
Foreign Key is a field in one table that points to the primary key in another table.
Syntax:
Create table <<table name>> ( <<column name1>> datatype not null primary key, <<column name2>> datatype foreign key references <<existing table name>>(<<existing column name>>), <<column name3>> datatype, <<column name4>> datatype )
Example:
To comment on a particular line in SQL, use the double hyphen symbol (- – ). The main purpose of the comments is to explain the process.
Thanks for reading! In this blog, we have covered the basic concepts of SQL that every developer should know. Try out the keywords discussed in this blog post and effectively handle your data.
Syncfusion has over 1,700 components and frameworks for WinForms, WPF, WinUI, .NET MAUI (Preview), ASP.NET (Web Forms, MVC, Core), UWP, Xamarin, Flutter, JavaScript, Angular, Blazor, Vue, and React. Use them to boost your application development speed.
For existing customers, the new Essential Studio® version is available for download from the License and Downloads page. If you are not yet a Syncfusion customer, you can try our 30-day free trial to check out our newest features.
If you have questions, you can reach us through our support forums, support portal, or feedback portal. As always, we are happy to assist you!