Demystifying SQL: An Introduction to SQL Using SQL Server
Tags: sql, database, datascience, dataanalysis
Published: 2023-07-13 09:50:45

## Introduction to SQL using Microsoft SQL Server
SQL stands for Structured Query Language. This is a native programming language used for managing and manipulating relational database. MS SQL server is a relational database management system developed by [Microsoft](https://www.microsoft.com/en-us/). You can download it from their [website](https://www.microsoft.com/en-us/sql-server/sql-server-downloads). We shall use The BikeStore Database downloaded from [sqlservertutorial](http://www.sqlservertutorial.net/load-sample-database/).
We use a query, a set of command(s), to interact with the database to retrieve or manipulate the data in the database.
### SQL Queries clauses
#### 1. SELECT
We use this keyword before we define the column(s) we would like to retrieve from a table(s). To limit the query results we use the keyword **TOP()** after the **SELECT** keyword. **FROM** keyword is used to define the table that we want to get our results from. **DISTINCT** is used to retrieve only unique values. Retrieved column(s) can be aliased using the **AS** keyword. Asterisks **(*)** symbol is used to retrieve all columns from a table.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5ketf3bj2ydincyk5jue.png)
The above image shows an example of how the clauses can be used. The **USE** keyword is used to define the database that we would like to work on. The query retrieves 5 brands from the brands table and aliases the results as brands.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b2hgr7s16rvjoe8wybck.png)
The query above retrieves all records from the customers table with all the columns.
#### 2. WHERE
Filtering is retrieving results that meet specified condition(s). We use the **WHERE** clause to achieve this in SQL. Once we have defined the column(s) and the table that we want to retrieve data from, we define the condition(s) that a column(s) should meet. The operators used in conjunction with WHERE clause are **=, <>, > ,<, BETWEEN,LIKE, IS NULL, IS NOT NULL.**

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vmbbv2ubypsbfvnheo2l.png)
The query above returns the record of the customer whose customer_id is 10. We use quotation marks(' ') when the data type of column that the criteria need to be met is a **string** or **date.**

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oaml5wf1ow57xnqlschb.png)
The query returns all the customers from NY state.
We use the key words **AND, OR** when we have multiple conditions to be met.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ro3xr5fr7we8idj4bz7.png)
The query retrieves the email of staff that fall under **store id 3** and the ID of their manager is **7**. When **AND** is used, the results must meet all the defined conditions in the **WHERE** clause while at least one condition must be met when **OR** is used. 
#### Operators 
-    Equal to: "**=**"
-    Not equal to: "**<>**", "**!=**"
-    Greater than: "**>**"
-    Less than: "**<**"
-    Greater than or equal to: "**>=**"
-    Less than or equal to: "**<=**"
-    **NOT**: Negates a condition
-    **IN**: Checks if a value matches any value in a list.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rgk5m2qijs4z435a9x8j.png)
The above query retrieves customers from the following states: NY, CA, TX.
**LIKE**: Performs pattern matching against character data using wildcard characters:
   "**%**" represents zero or more characters.
   "**_**" represents a single character.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7fwz3tl3n7glmyox6stb.png)
The query above retrieves customers with first_name starting with "**De**".
**BETWEEN**: Checks if a value is within a specified range (inclusive).

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xprrabmru1wh457ilhfm.png)
The query above retrieves orders with order_date between **2016-01-01** and **2016-01-07**
**IS NULL / IS NOT NULL**: Checks for the presence or absence of NULL values in a column.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ten3vao9zvmu3khpaw8t.png)
The query above retrieves customers who do not have a phone number.
#### 3. GROUP BY
This clause is used to group rows based on a column(s). You can summarize data using this clause. It is commonly used alongside aggregate functions (SUM, COUNT, MIN, MAX, AVERAGE).

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ez7zz6bwfkk6ok38ivcz.png)
The query above retrieves the total number of customers in each state.
#### 4. HAVING
This clause is used in conjunction with the **GROUP BY** clause to filter grouped rows based on specific conditions. You can use comparison operators (**=, <, >**), logical operators (**AND, OR**), and aggregate functions within the **HAVING** clause.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xzfk1iks5da0jf1yue4k.png)
The query above retrieves any state that has more than 500 customers.
#### 5. ORDER BY
Sorting is arranging the retrieved results in either ascending or descending order. SQL uses **ORDER BY** to sort results. The column(s) are defined after the **ORDER BY**. If you are sorting your column in descending order, you add the keyword **DESC** after the column(s) name(s). Otherwise you leave it that way for ascending order.

![SQL Query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mjq6wm3lj8g1mwccu0b6.png)
The query above retrieves all staff ordered by their first_name in descending order.