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.
Purpose of SQL
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.
Prerequisite
Getting started
- First, open the Microsoft SQL Server Management Studio.
- Then, navigate to File -> Connect Object Explorer. Refer to the following image.
- Now, the SQL Server dialog box will appear. Your machine name will appear in the Server Name field. Make sure that Windows Authentication is chosen as the Authentication type. Then, select the Connect option.
A new window will appear, where you can execute the SQL queries explained in the following sections.
Basics of SQL
We should have some knowledge of the following terms before we get started with SQL.
Table
A table is a database object that presents data in columns and rows.
Records and fields
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.
SQL data types
The basic things required to create a column are names and data types. SQL supports the following three data types:
- String data type
- Numeric data type
- Date data type
SQL clauses
SQL is a case-insensitive language. The major three clauses in SQL are Select, from, where.
Select
Select is the most important clause in SQL. It helps us retrieve data from the table. Simply, it answers What data should we show?
from
This retrieves data from a specific table in a database. Simply, it answers Where do we get the data from?
where
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:
SQL categories
There are five categories in SQL:
- Data definition language: It performs Create, Alter, Drop operations.
- Data manipulation language: It performs Insert, Update, Delete operations.
- Data control language: It performs Grant and Revoke operations.
- Data query language: It performs the Select operation.
- Transaction control language: It performs Commit and Rollback operations.
Commonly used keywords and their behavior in SQL
As a developer, we have to know the following commonly used keywords and their usage in SQL to get started with it:
#1: CREATE
This keyword enables us to create a new table.
Syntax:
Create table <<table name>> ( <<column name1>> datatype, <<column name2>> datatype )
Example:
Output:
#2: Insert into
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: 
#3: Update
Updates the existing records in a table.
Syntax:
Update <<table name>>>> set <<column name>> = <<value>> where <<column name>> = <<value>>
Example:
Output:
Before Update:
After Update:
#4: Distinct
This keyword removes duplicate records and gets the unique records from a table.
Syntax:
Select distinct * from <<table name>>
Example:
Refer to the following image to retrieve particular distinct column values.
Syntax:
Select distinct <<column name>> from <<table name>>
Example:
#5: top
Use this keyword to get the top values in a table.
Syntax:
Select top Numeric Value * from <<table name>>
Example:
#6: order by
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:
To sort the records in descending order, use the Keyword desc.
Syntax:
Select * from <<table name>> order by <<column name>> desc
Example:
#7: and
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:
#8: or
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:
#9: NOT
This keyword displays the records that don’t satisfy the provided condition.
Syntax:
Select * from <<table name>> where not <<column name>> = <<value>>
Example:
#10: MIN
This keyword displays the smallest value in a column.
Syntax:
Select min(<<column name>>>>) from <<table name>>
Example:
#11: MAX
This keyword displays the largest value in a column.
Syntax:
Select max(<<column name>>) from <<table name>>
Example:
#12: SUM
This keyword displays the total sum value for the numeric column.
Syntax:
Select sum(<<column name>>) from <<table name>>
Example:
#13: in
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:
#14: not in
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:
#15: Count
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:
#16: AVG
This keyword returns the average value of a column.
Syntax:
Select avg(<<column name>>) from <<table name>>
Example:
#17: ANY
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:
#18: Like
This keyword retrieves a specified pattern in a column.
Syntax:
Select * from <<table name>> where <<column name>> like ‘%Value%’
Example:
#19: UNION
This keyword is used to combine two or more select statements.
Syntax:
Select * from <<table name1>> union Select * from <<table name2>>
Example:
#20: Is NULL
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:
#21: IS NOT NULL
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:
#22: WILDCARDS
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:
#23: GROUP BY
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:
#24: Aliases
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:
#25: Between
This keyword retrieves the values within the given range.
Syntax:
Select * from <<table name>> where <<column name>> between <<value>> and <<value>>
Example:
#26: Join
This keyword combines records from two or more tables using the common field in them. There are four types of join in SQL:
- Inner Join
- Left Join
- Right Join
- Full Join
Let’s look at these Join keywords with example data.
Table 1: Students
Table 2: StudentsMarkInformation
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:
#27: Primary Key
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.
#28: Foreign Key
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:
SQL comments
To comment on a particular line in SQL, use the double hyphen symbol (- – ). The main purpose of the comments is to explain the process.
Other interesting topics like stored procedures, views, and functions will be discussed in our upcoming blogs.
Summary
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!
