SQL Queries and Database Testing: A Comprehensive Report

Desklib provides past papers and solved assignments for students. This report covers SQL queries and database testing.

Contents
Introduction................................................................................................................................2
TASK NUMBER ONE..............................................................................................................3
SQL........................................................................................................................................3
Data Control Language..........................................................................................................3
Data Definition Language......................................................................................................4
DDL command for Altering...................................................................................................4
TASK NUMBER TWO.............................................................................................................5
QUERIES...............................................................................................................................5
TASK NUMBER THREE.........................................................................................................9
Importance of database..........................................................................................................9
Size validation....................................................................................................................9
Checking Constraint.........................................................................................................10
Referential Integrity.........................................................................................................10
Conclusion................................................................................................................................11
References................................................................................................................................12

Table of Figures
Figure 1: 1st Query.....................................................................................................................6
Figure 2: 2nd Query...................................................................................................................6
Figure 3: 3rd Query....................................................................................................................6
Figure 4: 4th Query....................................................................................................................7
Figure 5: 5th Query....................................................................................................................7
Figure 6: 6th Query part a..........................................................................................................7
Figure 7: 6th Query part b..........................................................................................................7
Figure 8: 7th Query....................................................................................................................8
Figure 9: 8th Query....................................................................................................................8
Figure 10: 9th Query part a........................................................................................................8
Figure 11 9th Query part b.........................................................................................................8
Figure 12: 10th Query................................................................................................................9
Figure 13: (i) Testing constraint...............................................................................................10
Figure 14: (ii) Testing Constraint.............................................................................................11
Figure 15: (i) Null value testing...............................................................................................11
Figure 16: (ii) Null value testing..............................................................................................11
Figure 17: Checking Constraint...............................................................................................11
Figure 18: Referential Integrity................................................................................................11

Introduction
This report consists of three parts in which SQL queries will be through explained along with
their examples. The next part consist of queries which are implemented on PHP myAdmin
and respective screenshots of running queries are to be placed along queries. in the last part
brief introduction of the database, testing is to be provided with its benefits and types of the
test case are be to be mentioned.

TASK NUMBER ONE
SQL
SQL stands for Structured Query Language which helps to retrieve, add, delete or modify
database through SQL Queries. this was created by IBM developers named Raymond Boyce,
and Donald Chamberlin in 1970. This is the standard language for RDBMS/ database
MySQL, MS Access, Oracle, etc. this is used for structured data where the relationship
between different entities is used. (www.javatpoint.com, 2019) SQL Queries have 3 main
features such as
⦁ Data Control Language
⦁ Data Manipulation Language
⦁ Data Definition Language
Data Control Language
this command is also used to play with a database which contains Revoke and Grant
commands. these are used to block the database for user accessibility and give permission to
use for specific tasks only respectively. this is further divided into 2 categories which are
System and Object. in this creating tables, creating a database or task related to these type of
activities are provided the authentication from the system and operation which are to be
performed can be done in object phase. (GeeksforGeeks, 2019)
Syntax used in DCL is " GRANT name ON object_name TO{ name_of_user | PUBLIC |
name_of_role } [WITH GRANT OPTION]; "
where name consists of the user who is allowed to alter the database and nae_of_object
consist of table, sequence, and provide identities to objects
user name consists of the user who has the permission to access the database
name_of_role is combined permission which forms role
WITH GRANT OPTION helps to provide usability to other users.
DCL Command(REVOKE)
The syntax used for this is "REVOKE privilege_name ON name_of_object
FROM{ name_of_user | PUBLIC | name_of_role } "
where privilege_name refers to user allowed to alter or modify the database

name_of_object refers to the database name
PUBLIC refers to the permissions given to the user
Data Definition Language
This part is used for the structured database where the syntax is moreover similar to the
programming language in which queries are written to access the data which already exits in
the database. this includes features like the update, insert, connect, execute, and delete in the
database.
The syntax used is "Create Database <Name of databse>", this query helps to create a
database and the name is given by the user.
to create tables in the database the query is " Create Table <Table name> (Column name one
Datatype one, column name two datatype two)", where "Create Table" is standard command
and table name is entered by the user.
DDL command for Altering
this command is used for modifying the data such as adding a foreign key. this command is
executed when the database is to be modified.
the syntax used for adding new column is " ALTER TABLE table_name
ADD( column_name column_datatype);
the syntax for editing preexisting table is " ALTER TABLE table_name
MODIFY(table_name table_datatype)"

TASK NUMBER TWO
QUERIES
1.
Figure 1: 1st Query
2.
Figure 2: 2nd Query
3.
Figure 3: 3rd Query
4.

Figure 4: 4th Query
5.
Figure 5: 5th Query
6(a).
Figure 6: 6th Query part a
6(b).
Figure 7: 6th Query part b
7.

Figure 8: 7th Query
8.
Figure 9: 8th Query
9(a).
Figure 10: 9th Query part a
9(b).
Figure 11 9th Query part b
10.

Figure 12: 10th Query

TASK NUMBER THREE
1. Testing Database
For the database to be secured, safe and valuable database testing is performed to ensure
these points. these are mainly performed for the database to ensure that the database is in a
structured format, triggers are checked, tables are created, etc. These are performed through
complex queries which are used for testing. There are several operations performed on the
basis of data testing, the integrity of the database, and consistency of the database. Database
Testing has 3 main subcategories which are Non-functional Testing, Structural Testing, and
Functional Testing.
⦁ Non-functional Testing refers to risk which is present in the database, loads are tested,
stresses are tested, analyzing the needs for the database or in-short the performance of the
database is tested.
⦁ Structural Testing refers to the structure of the tables are tested, columns are to be in a
structures format and mainly the format in which the data is stored is verified.
⦁ Functional Testing refers to testing on the bases of the functionality of the database. in this
testing is performed in two parts. first part is White Box testing in which testing is done on
database schema and second testing is Black Box testing in which functionality of the
database is tested. (The Official 360logica Blog, 2019)
Importance of database
in order to keep the database safe and secure different type of testing is to be performed so
that the database is efficient and consistent throughout. Several complexities occur in the
database which is solved while testing. the database consists of data or information stored in a
structured and unstructured format which are collected from various sources. This data has
higher chances of being harmful in nature. This decreases the consistency of the data and
these problems are to be resolved. (Inc, 2019)
Some of the testings are done on the database provided by the user.
Size validation
In this the size of the validation is performed. in this the operation are performed on the size
of column names and sizes are changed.

Figure 13: (i) Testing constraint
Figure 14: (ii) Testing Constraint
Null Values
In this, the null values are given errors when found means if the spaces are left blank then the
user would be shown an error.
Figure 15: (i) Null value testing
Figure 16: (ii) Null value testing
Checking Constraint
in this, the constraints are the primary key, foreign key, etc so if the value is duplicated the
error would be shown.
Figure 17: Checking Constraint
Referential Integrity
In this primary key assigned to one table becomes a foreign key in another table so changes
are to be made they would be made in both the tables.

Figure 18: Referential Integrity
Conclusion
this report is compiled of three parts, the first part contains a brief introduction of database
and SQL queries and their examples. the second part contains queries written with respect to
PHP MyAdmin and respect screenshots of running queries are placed. the last part contains
an overview of database testing and its advantage with different types of test cases.