Research and SQL Queries

This report discusses research and SQL queries. It also provides an overview of NoSQL databases and their different formats, including key-value, document-based, column-based, and graph-based. The report explores the applications of each format and their unique features.

Assessment Item 4
Research and SQL Queries
Student ID:
Student Name:
5/26/2019

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

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

Query 3
select concat(custm.cus_fname,' ' , custm.cus_lname) as CustomerName , count(invc.cus_code) as
'NumberOfInvoices' from customer custm inner join invoice invc on custm.cus_code=invc.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','Sarwar Tapan','Tapan','905','412-0091','FL','Y');

Query 8
INSERT INTO CUSTOMER VALUES('11638837','Hossain','Tanvir','B','612','111-2233','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='11638837';

Part B
NoSQL Database
NoSQL stands for Not Only SQL which is used to manage the large or big data. It is very high
speed database management system which store the structured and unstructured both types of
data. Semi structured data can also be managed by the NoSQL database. (Saravanan N. n.d.)
There are different databases that support the NoSQL like HBase, BigTable, and Lotus Notes
etc. It is totally different from RDBMS because RDBMS supports only the structured data while
it supports the structured, semi structured and unstructured data. There are different categories or
storage of NoSQL database as mentioned below-
- Key-Value format
- Document based format
- Column based format
- Graph based format
All formats have different features and different usages. Different technologies use different data
storage formats of NoSQL according to their requirements. This is the difference between the
types of NoSQL database-
Key-Value format
The key-value format of NoSQL stores the data in the form of keys and values. Key is the unique
key that plays the role to uniquely identify the data in the NoSQL database. The data in the key-
value format may be string, JSON, BLOB etc. In RDBMS, it is very difficult to manage the
BLOB, JSON data but in Key-Value NoSQL database, the BLOB, JSON data is managed
effectively.
Online shopping systems use the key-value format of NoSQL. It is very fast database that shows
the large volume of data in a second. Different e-commerce website Amazon etc. use this key-
value format of NoSQL.
The key-value format of NoSQL is stored in following format-
Key Value
Amazon Data

Document-Based format
The document-based format is very high performance data storage technique in NoSQL. It stores
the data in the form of document. Each document is identified uniquely. The data in the
document is stored in the form of XML and JSON which is in the structured form. Because of
structured firm, the database can be queried. It is like Kay-Value format where the key uniquely
identify the data.
The twitter website use this document based format of NoSQL because it is very good in
blogging types of website. The document based format of NoSQL is supported by the MangoDB,
Lotus Notes etc. databases.
Column-Based format
The column-based format is very high performance data storage technique in NoSQL which is
very effective in aggregate functions queries.. It stores the data in the form of columns. Each
column is treated separately. Inventory management systems, Customer Care systems etc use the
column-based format.
HBase, BigTable databases use the column-based format of NoSQL.
Graph-Based format
The graph-based format is very high performance data storage technique in NoSQL that works
like entity relationship form. The data is stored in the form of nodes and edges. The node works
as entity and edge works as relationship between entities.
Social networking websites use this graph-based format of NoSQL becuas eit is very effective in
data traversal and the Cypher, Neo4J are the database that are based on graph-based format of
NoSQL. (Neo4j Inc. 2019)

References
Neo4j Inc. (2019). Graph Databases vs Relational Databases. Retrieved
https://neo4j.com/developer/graph-db-vs-rdbms/
Saravanan N. (n.d.). The basics of NoSQL databases — and why we need them. Retrieved from:
https://medium.freecodecamp.org/nosql-databases-5f6639ed9574