An example of creating a query in the MS SQL Server database. The database is located in the local *.mdf file
Contents
Search other websites:
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
- An example of creating a view in a database. Creating a calculated field
- An example of creating/connecting local Microsoft SQL Server database located in the “*.mdf” file
- An example of creating the “*.mdf” file of local database Microsoft SQL Server in MS Visual Studio