Live Chat Icon For mobile
Live Chat Icon
Popular Categories.NET  (156).NET Core  (27).NET MAUI  (27)Angular  (64)ASP.NET  (48)ASP.NET Core  (71)ASP.NET MVC  (88)Azure  (30)Blazor  (128)DocIO  (22)Essential JS 2  (76)Essential Studio  (168)File Formats  (25)Flutter  (121)JavaScript  (165)Microsoft  (107)PDF  (64)React  (43)Succinctly series  (126)Syncfusion  (533)TypeScript  (30)Uno Platform  (3)UWP  (4)Vue  (34)Webinar  (29)Windows Forms  (57)WinUI  (38)WPF  (131)Xamarin  (142)XlsIO  (25)Other CategoriesBarcode  (5)BI  (29)Bold BI  (4)Build conference  (6)Business intelligence  (53)Button  (4)C#  (115)Chart  (55)Cloud  (10)Company  (445)Dashboard  (6)Data Science  (3)Data Validation  (5)DataGrid  (53)Development  (319)Doc  (7)DockingManager  (1)eBook  (95)Enterprise  (22)Entity Framework  (5)Essential Tools  (14)Excel  (22)Extensions  (17)File Manager  (4)Gantt  (9)Gauge  (9)Git  (4)Grid  (27)HTML  (9)Installer  (2)Knockout  (2)Language  (1)LINQPad  (1)Linux  (2)M-Commerce  (1)Metro Studio  (11)Mobile  (267)Mobile MVC  (9)OLAP server  (1)Open source  (1)Orubase  (12)Partners  (21)PDF viewer  (28)Performance  (8)PHP  (1)PivotGrid  (4)Predictive Analytics  (6)Report Server  (3)Reporting  (10)Reporting / Back Office  (11)Rich Text Editor  (7)Road Map  (10)Scheduler  (33)SfDataGrid  (9)Silverlight  (21)Sneak Peek  (20)Solution Services  (2)Spreadsheet  (11)SQL  (8)Stock Chart  (1)Surface  (4)Tablets  (5)Theme  (11)Tips and Tricks  (87)UI  (181)Uncategorized  (68)Unix  (2)User interface  (68)Visual State Manager  (1)Visual Studio  (23)Visual Studio Code  (14)Web  (289)What's new  (176)Windows 8  (19)Windows App  (2)Windows Phone  (15)Windows Phone 7  (9)WinRT  (26)
Share on twitter
Share on facebook
Share on linkedin
SQL: A Complete Guide for Beginners

SQL – A Complete Guide for Beginners

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

  1. First, open the Microsoft SQL Server Management Studio.
  2. Then, navigate to File -> Connect Object Explorer. Refer to the following image.
    Connect Object Explorer in Microsoft SQL Server Management Studio
  3. 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.
    Connect to Server Windows AuthenticationA 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 Syntax 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:

Create keyword

Output:

CREATE Keyword 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:

Insert into keyword

Output:

Insert into Keyword 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:

Insert into keyword code example

Output: Insert into Keyword Output

#3: Update

Updates the existing records in a table.

Syntax:

Update <<table name>>>> set <<column name>> = <<value>> where <<column name>> = <<value>>

Example:

Update KeywordOutput:

Before Update:

Before Update

After 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:

distinct keywordRefer to the following image to retrieve particular distinct column values.

Syntax:

Select distinct <<column name>> from <<table name>>

Example:

distinct keyword example #5: top

 Use this keyword to get the top values in a table.

Syntax:

Select top Numeric Value * from <<table name>>

Example:

top keyword

#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:

order by keywordTo sort the records in descending order, use the Keyword desc.

 Syntax:

Select  * from <<table name>>  order by <<column name>> desc

Example:

Output of order by keyword

#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:
and keyword

#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:

or keyword

#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:

not keyword

#10: MIN

This keyword displays the smallest value in a column.

Syntax:

Select min(<<column name>>>>) from <<table name>>

Example:

min keyword#11: MAX

This keyword displays the largest value in a column.

Syntax:

Select max(<<column name>>) from <<table name>>

Example:

max keyword

#12: SUM

This keyword displays the total sum value for the numeric column.

Syntax:

Select sum(<<column name>>) from <<table name>>

Example:

sum keyword

#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:

in keyword

#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:

not in keyword

#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:

count keyword

#16: AVG

This keyword returns the average value of a column.

Syntax:

Select avg(<<column name>>) from <<table name>>

Example:

avg keyword

#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:

any keyword

#18: Like

This keyword retrieves a specified pattern in a column.

Syntax:

Select * from <<table name>> where <<column name>> like ‘%Value%’

Example:

like keyword

#19: UNION

This keyword is used to combine two or more select statements.

Syntax:

Select * from <<table name1>> 
union
Select * from <<table name2>>

Example:

union keyword

#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:

is null keyword

#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:

is not null keyword

#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:

WildCards 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:

group by keyword

#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:
Aliases

#25: Between

This keyword retrieves the values within the given range.

Syntax:

Select * from <<table name>> where <<column name>> between <<value>> and <<value>>

Example:

between keyword

#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

StudentsTable 2: StudentsMarkInformation

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:

Inner Join keyword

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:

Left Join Keyword

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:

Right Join Keyword

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:

Full Join Keyword

#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.

primary key keyword

#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:

foreign key keyword

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.

CommentsOther 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 WinFormsWPFWinUI.NET MAUI (Preview), ASP.NET (Web FormsMVCCore), UWPXamarinFlutterJavaScriptAngularBlazorVue, 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!

Related blogs

Tags:

Share this post:

Share on twitter
Share on facebook
Share on linkedin
Popular Now

Be the first to get updates

Subscribe RSS feed
Scroll To Top