Day 3-5 MSSQLServer basic SQL Queries |

Let us create a data base by name ” Pubs“. Full Script for the db creation attach here.

The SELECT statement in SQL is used to retrieve data from one or more tables in a database. It allows you to specify which columns you want to retrieve, apply filtering conditions, join tables, aggregate data, and more. Let us discuss the basic syntax and components of a SELECT statement:

Syntax 1:

SELECT column1, column2, …
FROM table_name;

As you have seen the syntax above.
1. SELECT Keyword used to specify that you want to retrieve data.
2. column1, column2, …: Columns you want to retrieve data from. Use * to select all columns.
3. FROM table_name: Specifies the table or tables from which you are going to retrieve the data.

Syntax 2: [ using WHERE Clause]

SELECT column1, column2, …
FROM table_name
WHERE condition;

As you have seen the syntax above.
1. SELECT Keyword used to specify that you want to retrieve data.
2. column1, column2, …: Columns you want to retrieve data from. Use * to select all columns.
3. FROM table_name: Specifies the table or tables from which you are going to retrieve the data.
4. WHERE condition used to filter rows based on specified conditions.

Syntax 3: [ JOIN Clause ]

SELECT column1, column2, …
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

1. SELECT column1, column2, …: These are the columns you need to select from the combined result set of the tables table1 and table2.
2. FROM table1: This specifies the first table from which you want to retrieve data.
3. INNER JOIN – This is a type of join that combines rows from both table1 and table2 based on a specified condition. The table2 – This is the second table involved in the join operation.
4. ON table1.column_name = table2.column_name – This is the join condition that specifies how the rows from table1 and table2 should be matched.

Details explanation:
The INNER JOIN combines rows from table1 and table2 where the values in the specified columns (column_name) are equal. If there are matching rows between table1 and table2 based on the join condition, those rows will be included in the result set. If there are no matching rows based on the join condition, those rows will not be included in the result set. The result set will contain columns from both table1 and table2 based on the SELECT clause.

Syntax 4: [ GROUP BY Clause ]

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

1. SELECT :column1: This is the column you want to group by and is also included in the result set.
2. aggregate_function(column2): This is an aggregate function applied to column2. Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include SUM, COUNT, AVG, MIN, and MAX.
3. FROM clause:table_name: This specifies the table from which you want to retrieve data.
4. GROUP BY clause: This clause is used to group the result set by the values in column1. It creates groups of rows where the values in column1 are the same.

Details explanation:

When you use GROUP BY, the result set is divided into groups based on the distinct values of column1.The aggregate_function(column2) calculates an aggregate value for each group of rows. The result set will include one row for each group, where column1 represents the grouped value, and the aggregate function’s result represents the aggregated value of column2 for that group.

Syntax 5: [ ORDER BY Clause: ]

SELECT column1, column2, …
FROM table_name
ORDER BY column1 ASC|DESC;

1. SELECT clause: column1, column2, …: These are the columns you want to retrieve data from and display in the result set.
2. FROM clause: table_name: Specifies the table from which you are retrieving data.
3. ORDER BY column1 ASC|DESC: This clause is used to sort the result set based on the values in column1 in either ascending (ASC) or descending (DESC) order.
4. ASC: Ascending order (default if not specified explicitly). DESC: Descending order.

Details explanation:
When you use ORDER BY, the result set returned by the SELECT statement is sorted based on the specified column(s). If only one column is specified in the ORDER BY clause (as in the example), the result set is sorted based on that column’s values.
If multiple columns are specified in the ORDER BY clause, the sorting is performed sequentially based on the order of columns in the clause. Ascending order (ASC) sorts the result set in ascending order (from lowest to highest or A to Z depending on the data type), while descending order (DESC) sorts the result set in descending order (from highest to lowest or Z to A).

Syntax 6: [LIMIT/OFFSET Clause]

SELECT column1, column2, …
FROM table_name
LIMIT 10; — Limit to 10 rows

1. SELECT Column1, column2, …: These are the columns you want to retrieve data from and display in the result set.
2. FROM table_name: Specifies the table from which you are retrieving data.
3. LIMIT LIMIT 10: This clause limits the number of rows returned by the query to 10.

Details explanation:
When you use LIMIT, the query will retrieve the specified number of rows (in this case, 10 rows) from the result set. If the query would normally return more than 10 rows, LIMIT ensures that only the first 10 rows are included in the result set.
The order of rows returned is usually based on the database’s internal ordering unless specified explicitly using an ORDER BY clause.

Lets us see now some example of it. We will use only two table from the Pubs database for below example:

Considered you have installed MSSQL Server already and SSMS.

Create pubs database. copy the data from the txt file and run into SSMS script Pane or query window.

Insert dummy data into pubs database table

Let us practice SELECT statements using ” Authors” table from Pubs database. We will cover the scenario such as basic SELECT , WHERE clause, ORDER BY, LIMIT, GROUP BY

Lets us assume , we want to display all the authors data from the author table. So we will write the queries below. It will display all the record from Author table.

Authors table structure