Connection with SQL and queries.

Introduction

Without the database, it is impossible to store any data. The database could be in various forms. I would like to walk through a few basics of SQL queries.

Scenario

We shall create two tables using queries, Insert records in those tables, View those records, and Join to show the results.

Objective

Database knowledge is important in Power BI which is in demand currently. So, we shall see how to get started and a few basic commands. First of all download Microsoft SQL Server Management Studio only from Microsoft portals. Not any other things are required to be downloaded like configuration manager and all.

Step 1. Connect Microsoft SQL Server Management Studio.

Connect with Microsoft SQL Server Management Studio, The Yellow highlighted part will be as per the user’s machine.

SQL Server Management

Step 2. Open New Query Editor.

Open the New Query Editor to start writing the queries.

Query Editor

Step 3. How to save SQL query files.

The extension to save the SQL query file will be with [FILENAME].sql, this also means we can save the query file and open it later and start using it instead of rewriting it.

We can choose either of the three values depending on our needs.

SQL query files

Step 4. How to open saved query files.

Using the below screen navigate to the location where the saved SQL extension file is stored. Pick that file and click open which gets loaded and looks like below.

Saved query files

Step 5. Create tables.

There are two ways we can create tables.

The first way is through UI

Create tables

The second way is through a query. In the screenshot, queries are provided to create the table.

Screenshot queries

How to insert items in the table.

Insert items

Step 6. How to reflect the change on tables.

There is a refresh button on the database, we can click that and that refreshes the database and tables.

Step 7. How to execute the query and see the results.

We can execute 1 query at a time or we can select multiple queries at the same time for comparison and see the results.

1 query at a time would look like.

Execute the query

Output of 1 query

Multiple queries would look like below, It is not mandatory to separate the query by semi-colon.

Semi-colon

Step 8. How to write the query.

When we start writing the query, intellisense gets activated, and a prompt appears to make the right selection.

Right selection

Step 9. How to use inner join and show all fields.

Returns records that have matching values in both tables.
Means all the columns from both the tables.
If column names are specified only those columns.
Finishes columns of the first table, then shows columns of the second table.

First table

Output

Step 10. How to show specific fields and not all fields in inner join.

In this screenshot, we can see three fields are only shown.

Specific fields

Output

Output2

Step 11. How to use left join and show all fields.

Returns all records from the left table and the matched records from the right table.
Where from the right table no common values are there, we will have a null value.

Left join

Output

Output3

Step 12. How to use right join and show all fields.

Returns all records from the right table and the matched records from the left table.
Where from the left table no common values are there, we will have a null value.

Right join

Output

Output4

Step 13. How to use full outer join and show all fields.

Returns all records when there is a match in either the left or right table.

Wherever values are not present, null will be shown.

Full outer join

Output

Output5

Conclusion

As we see in the image the result set is returned as per the blue highlighted section and empty fields return null. Only Inner Join returns only matching items. We would need to think and apply which join to be written. The keyword defines which join is applied. Along with this we also saw how to create a table, save items, and see items in the table.

Table