SQL Database Queries and NoSQL Database Types: A Comprehensive Report

This report provides an overview of SQL queries and NoSQL database types. The SQL section includes examples of queries for data retrieval, aggregation, and manipulation, such as selecting vendors and their product counts, calculating total invoice values for customers, and inserting and updating data. The NoSQL section categorizes databases into key-value, document-based, column-based, and graph-based models, explaining their characteristics, use cases, and examples like Amazon's key-value storage, Twitter's document-based approach, Google's BigTable using column-based storage, and graph-based databases like OrientDB. The report highlights the advantages of NoSQL in handling large volumes of unstructured and semi-structured data, contrasting it with traditional RDBMS.

Report
Part A
Query 1
select vendr.v_code, vendr.v_name, count(prodct.p_code) as NumberOfProducts from vendor vendr
inner join product prodct on vendr.v_code=prodct.v_code group by vendr.v_code, vendr.v_name;

Query 2
select custmr.cus_lname, sum(invoic.inv_total) as 'Total_invoice_value' from customer custmr inner join
invoice invoic on custmr.cus_code=invoic.cus_code group by cus_lname;

Query 3
select concat(custmr.cus_fname,' ' , custmr.cus_lname) as CustomerName , count(invoic.cus_code) as
'NumberOfInvoices' from customer custmr inner join invoice invoic on custmr.cus_code=invoic.cus_code
group by cus_fname, cus_lname;

Query 4
select line_units, line_price, round(sum(line_total),2) as UnitPrice from line group by line_units,
line_price;
Query 5
select * from invoice order by inv_subtotal desc limit 1;

Query 6
select cus_code, cus_fname, cus_lname, inv_number, inv_date, inv_subtotal, inv_tax, inv_total from
invoice natural join customer having cus_lname='Orlando';
Query 7
INSERT INTO VENDOR VALUES('25596','Brett','Brett','915','400-1191','FL','Y');

Query 8
INSERT INTO CUSTOMER VALUES('11694259','Kumar','Jangili','A','922','999-5331','0');
Query 9
ALTER TABLE CUSTOMER ADD Customer_rating VARCHAR(14);

Query 10
SET SQL_SAFE_UPDATES=0;
Update CUSTOMER SET Customer_rating='GREAT' where CUS_CODE='11694259';

Part B
NoSQL Database
NoSQL (Not Only SQL) is used to store and access the Big data which is being used in almost
all social networking websites, e-commerce websites etc. These websites access over the
terabytes of data on daily basis and RDBMS cannot handle the same amount of data efficiently.
NoSQL is developed specially for schema less data which is not acceptable in RDBMS.
NoSQL works for all types of data - structured, semi structured and unstructured data. The
NoSQL can be categorized into following categories-
- Key-Value
- Document-based
- Column-based
- Graph-based
All are different and have different features. There is some difference in all but all manage the
structured, semi structured and unstructured data. All are being described below-
Key-Value
The key-value stores the data along with keys. The keys are unique and the associated value may be any
of data type- BLOB, JSON, String etc. the key identifies the respected value . The key-value design of
NoSQL is very fast database which shows the data in a very short time. The access time is very less
because the database is very fast.
The key-value NoSQL database is stored in the form of hash table. Different e-commerce websites like
Amazon use the key-value NoSQL database. The key-value pair is stored in the form of table like below-
Key Value
Website Website data

Document-Based
In document-based NoSQL database, the data is stored in form of XML, JSON etc. documents.
The documents are uniquely identified and the data is stored in structured format that the query
can fetch the specified data. From the performance point of view it gives very high performance.
It works the same as key-value but the different is in the data storage. In document-based the
data is stored in the form of documents.
Blogging sites etc. use this type of NoSQL database. Twitter uses the document-based NoSQL
database. CouchBase database use the document-based NoSQL database. It helps the users to
store the data in the form of documents e.g. complete book and allow them to query into the
database with ease. (Amazon Web Services 2019)
Column-Based format
In column-based NoSQL database, the data is stored in a cell which is a group of columns and
each column works separately. Unlike RDBMS, the data is stored in the form of columns in spite
of rows that is why the queries are fetched with high speed. The aggregation functions queries
perform well in column-based NoSQL database.
The data compression is better in column-based NoSQL database in comparison to row-based
database. BigTable of Google is based upon the column-based NoSQL database.
(cloudxlab.com n.d.)
Graph-Based format
In graph-based NoSQL database, the data is stored in the form of nodes and edges. The nodes
represent the entity and edges represent the relationship among entities. The graph-based NoSQL
database works similar to the entities but does not require any specific schema. It is schema less
and works according to the real time applications.
It can add more databases in future very easily. It is the highest flexible database. it is very cost
effective.
OrientDB and HyperGraphDB are based upon the graph-based NoSQL database.

References
Amazon Web Services, ‘What Is a Document Database?’, (2019)
<https://aws.amazon.com/nosql/document/ > accessed 26 May 2019
cloudxlab.com, ‘NoSQL - Column Oriented Databases’, (n.d.)
<https://cloudxlab.com/assessment/slide/11/nosql/347/nosql-column-oriented-databases>
accessed 26 May 2019