SQL Database: DDL, DCL Commands, Data Manipulation, and Testing

Desklib provides past papers and solved assignments for students. This assignment covers SQL, database design, and testing.

Table of Contents
Introduction................................................................................................................................2
Assignment No. 2.......................................................................................................................3
Part No. 1 – SQL (Structured Query Language)....................................................................3
Part No. 2- Data Manipulation...............................................................................................6
Part No. 3- Database Testing...............................................................................................10
Conclusion................................................................................................................................13
List of Figures
Figure 1: Create Command in DDL...........................................................................................4
Figure 2: Alter Command in DDL.............................................................................................4
Figure 3: Create User Command in DCL..................................................................................5
Figure 4: Grant Command in DCL............................................................................................5
Figure 5: Revoke Command in DCL.........................................................................................5
Figure 6: 1st Query.....................................................................................................................6
Figure 7: 2nd Query...................................................................................................................6
Figure 8: 3rd Query....................................................................................................................6
Figure 9: 4th Query....................................................................................................................7
Figure 10: 5th Query..................................................................................................................7
Figure 11: 6th (a) Query.............................................................................................................7
Figure 12: 6th (b) Query............................................................................................................8
Figure 13: 7th Query..................................................................................................................8
Figure 14: 8th Query..................................................................................................................8
Figure 15: 9th Query..................................................................................................................8
Figure 16: 10th (a) Query...........................................................................................................8
Figure 17: 10th (b) Query..........................................................................................................9
Figure 18: Types of Testing.....................................................................................................10
Figure 19: Constraint 1.............................................................................................................11
Figure 20: Constraint 2(a)........................................................................................................11
Figure 21: Constraint 2(b)........................................................................................................11

Figure 22: Constraint 3.............................................................................................................12
Figure 23: Constraint 4.............................................................................................................12

Introduction
The report is made on the topic of Data Modelling & SQL Language. This contains three
parts in it where different parts cover different aspects of the assignment. Starting with the
first Part, SQL is clearly explained and its commands i.e. DDL and DCL are properly
explained with proper examples. Next part is the Manipulation of the Database where the
queries are performed on Xamp server and PHPMyAdmin software. At last, the testing of the
database is explained and also why it is important is being also reflected theoretically. Few of
the given constraints are being mentioned on which the testing process is implemented.

Assignment No. 2
Part No. 1 – SQL (Structured Query Language)
The database is part of each and every company or department. Everything has its database
where the bulk of data is stored. To access the required data, the whole database cannot check
one by one (Kamara, S. and Moataz, T., 2018). So, Structured Query Language that is
abbreviated as SQL is used to query the database and extract the required data. SQL is the
standard language that further contains the following commands such as:
 Data Manipulation Language (DML Commands)
 Data Control Language (DCL Commands)
 Data Definition Language (DDL Commands)
1. Data Definition Language (DDL Commands):
Data Definition Language commands are the command that further contains Create, Alter,
Drop, Truncate, Command and Rename. This basically helps in residing the data in the
database and also helps in creating the schemas for the creation of the database. The two
commands that are needed to be explained in this report are Create and Alter.
The explanation is as:
CREATE COMMAND IN DDL-
The first to be explained is the Create Command in DDL. Create Command is used to create
the databases as well as helps in creating the table in the database. Create Command is the
first step in creating the whole database system. The Syntax of the creation command
contains two parts-
SYNTAX-
 Creating the Database-
Create Database nameofdatabase;
 Creating the Table-

CREATE TABLE nameoftable
(
column1 data_type(size),
column2 data_type(size),
column3 data_type(size),
....
);
EXAMPLE QUERY-
Figure 1: Create Command in DDL
ALTER COMMAND IN DDL-
The next command that comes under DDL commands is the Alter command. Alter command
is mainly being used in order to modify the structure of the database. The database created
needs to modified at a few points, that can be easily done through Alter Command. Alter
commands also contains multiple options. One of which is to add the new column. The
Syntax is-
SYNTAX-
 Adding new column-
Alter table table_name

Add newcolumn_name datatype;
EXAMPLE QUERY-
Figure 2: Alter Command in DDL
2. Data Control Language (DCL Commands):
The other command that comes under SQL is the Data Control Language. DCL commands
further contain Grant as well as Revoke commands. These commands are basically used to
provide different access to the users by the administrator. Also, the permissions are being
revoked through these commands. Grant and Revoke are further explained as:
GRANT COMMAND IN DCL-
The first command that comes under DCL is the Grant Command. Grant Command is mainly
used to provide the various privileges to the users by the administrator. The privileges
provided can only be used by the user to whom the permissions are being granted. The
Syntax for the command is-
SYNTAX-
Creating the new user-
CREATE USER 'newuser'@'localhost'
IDENTIFIED BY 'password';
Granting Permission-
GRANT type_of_permission

ON database_name.table_name
TO ‘username’@'localhost’;
EXAMPLE QUERY-
Figure 3: Create User Command in DCL
Figure 4: Grant Command in DCL
REVOKE COMMAND IN DCL-
The next command that comes under Data Control Language is the Revoke Command. The
Revoke command is the command that takes away the privileges provided by the
administrator to the user through Grant Command. The revoke command is mainly used to
snatch away the permissions. The Revoke command is further explained as-
SYNTAX-
REVOKE type_of_permission
ON database_name.table_name
FROM ‘username’@‘localhost’;
EXAMPLE QUERY-
Figure 5: Revoke Command in DCL

Part No. 2- Data Manipulation
Queries Performed:
1.
Figure 6: 1st Query
2.
Figure 7: 2nd Query
3.
Figure 8: 3rd Query

4.
Figure 9: 4th Query
5.
Figure 10: 5th Query
6.

Figure 11: 6th (a) Query
Figure 12: 6th (b) Query
7.
Figure 13: 7th Query
8.
Figure 14: 8th Query
9.
Figure 15: 9th Query

10.
Figure 16: 10th (a) Query
Figure 17: 10th (b) Query

Part No. 3- Database Testing
1. Testing of the Database is the major step for creating the effective and most efficient
database. For the testing process requirement of the tester is must in order to check the tables,
for writing the queries as well for the various procedures. Database testing acts as a platform
that is being performed on the web application or the desktop, also the databases can be used
in the applications such as SQL or we can say Oracle. Database testing is mainly used to
check the schemas or structures, triggers, tables and many more. It also includes the
involvement of the creation of the queries in order to check the load on the database and to
check the response of the Database. Data integrity, as well as the Consistency of the
Database, is maintained through Database testing. Database testing is basically done in order
to extract out the relevant data or user data from the bulk of data that is collected from a lot of
different sources. Database Testing contains three parts named:
 Structured Database Testing
 Functional Database Testing
 Non-Functional Database Testing
Figure 18: Types of Testing
(Source: Wisdom Jobs, 2019)
Database testing is very important because-
Database testing is considered to be the most important part of creating the most effective and
efficient database. It is considered very important as a lot of data in bulk gets collected from
various sources. Collection of such large bulk of data definitely contains a lot of useless data
along with it that has to be removed. Database testing is the process of removing that useless
data from the bulk of data in order to make the database effective (Nicosia et al., 2019).