SQL Queries and NoSQL Databases: A Comparative Analysis

Desklib provides solved assignments and past papers to help students succeed.

Contents
Assessment A...................................................................................................................................2
Assessment B.................................................................................................................................11
References......................................................................................................................................15

Assessment A
Queries executions:
Query 1:
Select VENDOR.V_CODE as CODE_OF_VENDOR, VENDOR.V_NAME as
NAME_OF_VENDOR, count (PRODUCT.V_CODE) as SUPPLY_NUMBERS
From PRODUCT, VENDOR
Where VENDOR.V_CODE=PRODUCT.V_CODE
Group by VENDOR.V_CODE, VENDOR.V_NAME;
Figure 1: Query 1
Query 2:
Select CUSTOMER.CUS_LNAME as CUS_LASTNAME, SUM (INVOICE.INV_TOTAL) as
INVOICE_TOTAL

From CUSTOMER, INVOICE
Where CUSTOMER.CUS_CODE=INVOICE.CUS_CODE
Group by CUSTOMER.CUS_LNAME, INVOICE.CUS_CODE;
Figure 2: Query 2
Query 3:
Select concat(CUSTOMER.CUS_FNAME,' ',CUSTOMER.CUS_LNAME) as NAME_OF_CUS
, COUNT(INVOICE.INV_NUMBER) AS INVOICE_NUMBER
From CUSTOMER, INVOICE
Where CUSTOMER.CUS_CODE=INVOICE.CUS_CODE
Group by CUSTOMER.CUS_LNAME, CUSTOMER.CUS_FNAME, INVOICE.CUS_CODE;

Figure 3: Query 3
Query 4:
Select INV_NUMBER as NUM_INV, SUM (LINE_UNITS*LINE_PRICE) AS
UNIT_TOTAL_PRICE
From LINE
Group by INV_NUMBER;

Figure 4: Query 4
Query 5:
Select * FROM INVOICE WHERE INV_SUBTOTAL= (SELECT MIN (INV_SUBTOTAL)
FROM INVOICE);

Figure 5: Query 5
Query 6:
Select CUSTOMER.CUS_LNAME as CUS_LASTNAME, INVOICE.INV_NUMBER as
NUM_INV
From CUSTOMER, INVOICE
Where CUSTOMER.CUS_CODE=INVOICE.CUS_CODE AND
CUSTOMER.CUS_LNAME='Orlando';

Figure 6: Query 6
Query 7:
Insert Into Vendor Values (56984,'Smith','Stuart','785','715-5698','TN','Y');
Select * from vendor;

Figure 7: Query 7
Query 8:
Insert into Customer Values (11694267,'Nageshwar','Raghu','A','633','555-1073', 0);
Select * from Customer;

Figure 8: Query 8
Query 9:
Alter Table Vendor
Add Customer_rating VARCHAR (14);

Figure 9: Query 9
Query 10:
Update vendor
Set Customer_rating = 'GREAT'
Where V_NAME = ' Smith';

Figure 10: Query 10
Assessment B
There are four different categories of NoSQL databases that are Key-value store, Document
store, Column store, and Graph base store NoSQL database (Guagliardo & Libkin, (2017)).

These databases are discussed in detail along with the example and their use cases in the given
table:
Key-Value store Document store Column store Graph base
Explanation The key value
database is the
simplest database in
which the user gets the
value for the key,
assign the value for
key or it can delete the
key from the data
store. This database
stores the unique keys
as well as pointers in
the hash tables. In this
database, column
types of relations are
not present which
makes the
implementation
process easier. This
database provides
good performance and
a great scale for
This is similar to the
key-value database
where the data get
stored in the form of
values and the key is
associated with every
value as the unique
identifier. But in this
database, the value
includes the data that
can be either structured
or semi-structured
which is referred to as
the document and it can
be present in any
format like XML,
BSON, etc.
In this database the
data get stored in
the form of
columns, there are
no rows in this
database. There are
unlimited columns
that can be created
during the run time
or when the schema
is defined (Gessert
et al., (2017)).
These columns help
in fast accessing of
the data as well as
the data
aggregation. The
process of reading
as well as writing is
performed by the
These databases are
built on the entity-
attribute-value
models in which the
entities are referred
to as nodes that
contain some
properties (Li, et
al., (2015,
September)). This
is the most flexible
way so as to
describe the data
relations with other
data. The nodes
present in this
database stores the
data of each entity
that are present in
the database, the
relationships help