An example of creating a query in the MS SQL Server database. The database is located in the local * .mdf file

An example of creating a query in the MS SQL Server database. The database is located in the local *.mdf file

 


Content


Task

The database, which is located in Education.mdf file, is given. The database contains two linked Student and Session tables.

The tables are related to one another by field ID_Book.

Using the tools of Microsoft Visual Studio, you need to create a query (view) with the name Query1, which will have the following structure

Field name

Table

Num_Book Student
Name Student
Mathematics Session
Informatics Session
Philosophy Session
Average Calculated field

 

Istruction

1. Run Microsoft Visual Studio

 

2. Connect the Education.mdf database to the databases list of the Server Explorer utility

In order not to waste time developing and linking the tables of the Education.mdf database, you can download the finished database here. After loading and saving in a certain folder, the database archive must be unpacked and connected to the databases list of the Server Explorer utility.

Database connection is realized in one of several standard ways:

  • selecting “Connect to Database …” from the Tools menu;
  • selecting “Connect to Database …” button in the Server Explorer.

As a result, the wizard window opens, in which you need to configure the database connection using several steps (windows).

Figure 1. Ways of adding / connecting a database

For a detailed description of how to connect a Microsoft SQL Server-type database to Microsoft Visual Studio in described in the topic:

After connecting, the Server Explorer utility window will look like the one shown in Figure 2.

Figure 2. The “Server Explorer” utility with the connected Education.mdf database

 

3. Adding a new query. The command “New Query”

You can create queries to a database. In our case, we need to create a query in accordance with the condition of the task.

The query is created using the command “New Query”, which is called from the context menu (Figure 3). To call a command, it is enough to make a click with the right mouse button in the field area, which is selected for displaying the Elements of the Education.mdb database. It should be noted that queries are not saved by the system. To display saved (complex) queries, the views are used.

Figure 3 shows the context menu that is called when you click the Views tab. In this menu, select the command “New Query”. This command is in the list of context menus of other components of the database (tables, diagrams, etc.).

Figure 3. Command “New Query”

As a result, the “Add Table” window opens, in which you need to select the tables whose data will be used in the query (Figure 4).

Figure 4. Selecting the tables that will be used in the query

For our case, we need to select both tables.

As a result, the Microsoft Visual Studio window will be opened as shown in Figure 5.

Figure 5. MS Visual Studio window after query creation

In the tables, you need to select the fields that will be used in the query. The order of fields selection should correspond to their display in the query in according with the condition of the task. This means that the fields of the Student table (NumBook, Name) are first selected, and then the fields of the Session table (Mathematics, Informatics, Philosophy) are selected.

For our case, the choice of fields is shown in Figure 6.

Figure 6. Selecting fields for a query

As you can see in Figure 6, at the bottom of the window you will see a query in the SQL language generated by the system

SELECT  Student.Num_Book, Student.Name, Session.Mathematics,
        Session.Informatics, Session.Philosophy
FROM       Session INNER JOIN
        Student ON Session.ID_Book = Student.ID_Book

 

4. Adding calculated field Average

To create a calculated Average field, you need in the SQL-query window, change the text of this query. For example:

SELECT  Student.Num_Book, Student.Name, Session.Mathematics, Session.Informatics, Session.Philosophy,
        (Session.Mathematics + Session.Informatics + Session.Philosophy) / 3.0 AS Average
FROM  Session INNER JOIN
        Student ON Session.ID_Book = Student.ID_Book

The calculated field Average is added (Figure 7).

Figure 7. Adding the calculated field Average

 

5. Running the query. The command “Execute SQL”

To run the query, use the “Execute SQL” button (Figure 8).

At the bottom of the window, Figure 8 shows the result of the query.

Figure 8. The “Execute SQL” button to run the execution request and the result of the query execution

Another way to run the execution request is to use the “Execute SQL” command from the “Query Designer” menu.

 


Related topics