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. You can download it from their website. We shall use The BikeStore Database downloaded from sqlservertutorial.
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.
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.
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.
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.
The query returns all the customers from NY state.
We use the key words AND, OR when we have multiple conditions to be met.
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.
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.
The query above retrieves customers with first_name starting with "De".
BETWEEN: Checks if a value is within a specified range (inclusive).
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.
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).
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.
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.
The query above retrieves all staff ordered by their first_name in descending order.
Top comments (0)