A Comparative Study of Multiple Table Data Queries Based on SQL

Server 2008

Ruibin Xu

Quanzhou Institute of Information Engineering, Quanzhou, China

Keywords: SQL, Database, Data Query, View, Stored Procedure.

Abstract: Using SQL language to complete multi table queries in databases is a key learning point for database learners.

This article compares and studies three methods of implementing multi table queries: join query, view query,

and stored procedure query, exploring their respective advantages and disadvantages, in order to improve

learning efficiency.

1 INTRODUCTION

In the process of learning databases, the most

important knowledge point is to use SQL language to

complete data queries on basic tables in the database,

and the most difficult point of data queries is precisely

multi table queries, which often troubles database

learners, but it is unavoidable. In order to improve the

learning efficiency of multi table queries based on

SQL Server 2008, this article will take a common

student database as an example to design and

implement multi table data queries. By comparing

and studying the advantages and disadvantages of

connection queries, view queries, and stored

procedure queries, it will improve the efficiency of

students' learning this knowledge point.

2 CREATE A STUDENT

DATABASE AND BASIC

TABLES

2.1 Creating a Student Database Using

T-SQL Program

The student database is a data warehouse that stores

students' daily learning and life information. It is

designed as an information management tool to

facilitate the management of student data information

by teachers and counselors. Design the corresponding

program in SSMS as follows:

create database student

( name=student_data,

filename='d:\student_data.mdf',

size=3,

maxsize=300,

filegrowth=2)

log on

( name=student_log,

filename='d:\student_log.ldf',

size=4,

maxsize=400,

filegrowth=10%)

2.2 Creating Three Basic Tables Using

SSMS

Using SSMS, create three basic tables in the student

database student, namely the student information

table stuinfo, to store students' basic information.

Course information table, used to store course

information. The score information table is used to

store students' course grades.

The stuinfo structure of the student information

table is shown in Table 1.

Table 1. Student information table stuinfo structure.

column type length meaning Other

stuid varchar 10 Student ID PK

sname char 8 name Not null

sex char 2 gender

male or

female

phone char 11 Phone Not null

The procedure for creating the student

information table stuinfo table is as follows:

306

Xu, R.

A Comparative Study of Multiple Table Data Queries Based on SQL Server 2008.

DOI: 10.5220/0012282100003807

Paper published under CC license (CC BY-NC-ND 4.0)

In Proceedings of the 2nd International Seminar on Artiﬁcial Intelligence, Networking and Information Technology (ANIT 2023), pages 306-309

ISBN: 978-989-758-677-4

create table stuinfo

( stuid varchar(10) primary key,

sname char(8),

sex char(2) check(sex in(' male ',' female ')),

phone char(11)

)

The course structure of the course information

table is shown in Table 2.

Table 2. Course structure of student information table.

column type length meaning Other

cid varchar 10 Course ID PK

cname varchar 20 Course Name Not null

credit float credit Not null

The procedure for creating a student information

table course is as follows:

use student

create table course

( cid varchar(10) primary key,

cname varchar(20) not null,

credit float not null

)

The score structure of the grade information

table is shown in Table 3.

Table 3. Score structure of the score information table.

column type length meaning Other

stuid varchar 10 Student ID PK

cid varchar 10 Course ID PK

grade float achievement Not null

The procedure for creating a student information

table score is as follows:

use student

create table score1

( stuid varchar(10) primary key,

cid varchar(10) not null,

grade float not null

)

After the three tables are created, the relevant data

can be entered using the insert into statement in the

T-SQL program, forming a complete database for

subsequent data queries to provide a data source.

3 SQL STATEMENT

IMPLEMENTATION FOR

MULTI TABLE QUERIES

3.1 Connection Query

In the FROM clause, use commas to separate multiple

tables, and then provide a join condition through the

WHERE clause, and use this condition to join the

tables. For the multi table query of the three tables

created above, it is mainly applied to personal

information query and score summary statistics.

For example, by querying the student's student ID,

name, and grade for the course "Principle of Database

System", the program design is completed using a

linked query statement, and the results are shown in

Figure 1.

use student

select t1.stuid,sname,t2.cid,cname,grade

from stuinfo t1,course t2,score t3

where t1.stuid=t3.stuid and t2.cid=t3.cid and

cname='Principle of Database System'



Figure 1. Connection query result 1.

For example, using an aggregation function to

query the student ID and name of each student, as

well as the average score of the courses they have

studied, and ranking them in descending order of

average score. Complete the program design using

the connection query statement, and the results are

shown in Figure 2.

use student

select t1.stuid,sname,AVG(grade) as average

from stuinfo t1,score t3

where t1.stuid=t3.stuid

group by t1.stuid,sname

order by AVG(grade) desc



Figure 2 .Connection Query Result 2.

3.2 View Query

Multi table queries can be completed using other

database objects, such as views, in addition to

connection queries. A view is a virtual table exported

from a basic table that does not occupy database

memory space, but it also has all the functions of a

basic table and can be used just like a basic table.

Taking the query of students' course grades as an

example, the view query method is used to explore its

advantages and disadvantages. Firstly, create a view

A Comparative Study of Multiple Table Data Queries Based on SQL Server 2008

307



v_s1 using the command create view. Then, use this

view to query students' grades, and you can also

statistically summarize the query results.

Create view program as follows:

create view v_s1

select t1.stuid,sname,t2.cid,cname,grade

from stuinfo t1,course t2,score t3

where t1.stuid=t3.stuid and t2.cid=t3.cid

Then, using this view v_ s1 can query the student

ID, name, course ID, course name, and grades of each

student. The program code is as follows:

use student

select stuid,sname,cid,cname,grade

from v_s1



Figure 3. View query result 1.

For example, to query the average score, highest

score, and lowest score of each course that a student

has studied, you can directly use this view to query.

The program code is as follows:

use student

select cid,cname,AVG(grade) as average,MAX(grade)

as Maximum,

MIN(grade) as Minimum

from v_s1

group by cid,cname



Figure 4. View query result 2.

From the program code designed in the above

example, whether it is the number of lines to write the

program or the running efficiency of the program,

compared to the multi table query designed using the

join query method before, the view query is more

concise and easy to understand, and the program code

is also relatively easy to write.

As a special database object, a view is a virtual

table. Once defined, a view can be queried, modified,

deleted, and updated like a basic table. Moreover,

querying through a view involves relatively simple

program code, which can simplify operations. This is

highly recognized by database learners or users,

especially those unfamiliar with multi table queries.

Due to the fact that the view only stores the definition

of data and does not store the data itself, it is

beneficial for data security. However, it is also

necessary to have a clear place for the storage of the

data itself, which should be used in conjunction with

the basic table in a reasonable manner.

3.3 Stored Procedure Query

In addition to connection queries and view queries,

multi table queries can also be completed through

stored procedures. A stored procedure is a database

object composed of a set of T-SQL statements and

stored in the form of a storage unit on the server

(Mingyuan Zhao, 2023). Taking querying students'

course grades as an example, first create a stored

procedure, and then use the EXEC command to call

the stored procedure to complete the query of

students' course grades.

The program for creating stored procedure proc1

is as follows:

use student

create procedure proc1

select t1.stuid,sname,cname,grade

from stuinfo t1,course t2,score t3

where t1.stuid=t3.stuid and t2.cid=t3.cid

Then, if you want to query the score information

of each course that all students have studied, use the

program command EXEC to call the above stored

procedure proc1, that is, directly enter the command

EXEC proc1 in SSMS to achieve data query. The

query results are as follows:



Figure 5. Stored procedure query result 1.

If you want to query the score information of a

single student or course, you need to create a stored

procedure with parameters to implement it. The

program for creating a stored procedure with

parameters, proc2, is as follows:

use student

create procedure proc2(@sid varchar(10))

select t1.stuid,sname,cname,grade

from stuinfo t1,course t2,score t3

where t1.stuid=t3.stuid and t2.cid=t3.cid and

t1.stuid=@sid

Next, use the program command EXEC to call the

above stored procedure proc2, that is, directly enter

the command EXEC proc2 '200301' in SSMS to

query the data. The query results are as follows:

ANIT 2023 - The International Seminar on Artiﬁcial Intelligence, Networking and Information Technology

308



Figure 6. Stored procedure query result 2.

A stored procedure is a collection of T-SQL

statements executed on the database server side,

compiled and stored in the database server (

Jun Ma,

2021). Stored procedures are processed as a unit and

identified by a name. When querying data, users only

need to provide the name of the stored procedure and

necessary parameters to easily call them. The

processing of stored procedures is very flexible,

allowing users to use declared variables and can also

have input and output parameters to return single or

multiple result sets and processed result values.

3.4 Comparison of Data for Three

Types of Queries Corresponding to

Execution Plans

When using the SSMS platform to run connection

queries, view queries, and stored procedure queries,

check the parameters reflected in their corresponding

execution plans for data comparison, mainly

analyzing and comparing CPU overhead, I/O

overhead, and operator overhead (table scanning), as

shown in Table 4.

Table 4 .Data Comparison Table for Different Query

Methods.

Connection

Method/Cost

CPU Cost

(seconds)

I/O Cost

(seconds)

Operator Cost

(table scan)

(seconds)

Join Query

View Query

Stored Procedure

Query

0.0178693

0.0178064

0.0178078

0.003125

0.0036853

0.0021531

0.0021533

It is evident from the above table that these three

query methods are similar in terms of CPU and I/O

overhead, but in terms of operator overhead, that is,

when scanning the same three tables, the overhead of

connecting queries is significantly higher.

4 CONCLUSION

Data query is one of the more complex knowledge

points faced by database learners and users, and it is

also the most core knowledge for learning SQL

language, and multi table query is the most difficult

part to understand and master. This article explores

three methods that can achieve multi table queries to

help database learners and users increase their interest

in learning and improve the efficiency of learning and

using databases. From the comparison of three query

methods, it can be seen that connection queries are

more traditional and commonly used. However, the

reuse rate of the code written is too low, and the

memory space occupied will also increase with the

increase of data in the database, invisibly causing

additional burden on data storage (Yong Wang, 2014);

The query function of the view is consistent with the

basic table and does not require memory usage. The

code can be written repeatedly, which can reduce the

amount of code written. However, it must be used in

conjunction with the basic table, otherwise it is easy to

do useless work; Stored procedure query, which can be

modularized and encapsulated, has the structural

characteristics of face objects. Once created, stored

procedures can be called any number of times in future

programs, which can improve the maintainability of

applications, reduce network traffic, and speed up

operation efficiency. For hundreds of lines of

programs, this stored procedure query is suitable, but

for beginners or those with a small number of program

segments, this module design method is not very

suitable, too wasteful, and the flexibility is far less than

that of connection queries and view queries.

REFERENCES

Jun Ma, Bing Xu, Shiquan Qiao. SQL Server 2016 Database

Management and Development (M). Beijing: People's

Post and Telecommunications Press, 2021:141-146

Yong Wang. Research on the Application of Data Query

(Multi table Query) Using Relational Database Standard

Language SQL (J). Computer CD Software and

Application, 2014 (19): 66-67

Mingyuan Zhao. SQL Server Database Practical Tutorial

(M). Beijing: People's Post and Telecommunications

Press, 2023:12-30

A Comparative Study of Multiple Table Data Queries Based on SQL Server 2008

309