Database Report: SQL Queries and NoSQL Database for ITC556 Course

This report presents a comprehensive analysis of SQL queries and NoSQL databases. Part A focuses on executing SQL queries to retrieve and manipulate data from a relational database, including tasks such as retrieving vendor and product information, calculating invoice values, and customer data. Part B delves into NoSQL databases, exploring their characteristics, advantages, and different types, including key-value pair, document-oriented, column-based, and graph-based databases. The report highlights the differences between relational and non-relational databases, emphasizing the suitability of NoSQL databases for real-time applications and big data scenarios. The document provides a detailed overview of each NoSQL database type, including their specific use cases and functionalities. Overall, this report provides a valuable resource for understanding the fundamentals of both SQL and NoSQL databases.

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

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

Query 2
select c.cus_lname, sum(i.inv_total) as 'Totalinvoicevalue' from customer c inner join invoice i on
c.cus_code=i.cus_code group by cus_lname;

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

select line_units, line_price, round(sum(line_total),2) as TotalUnitPrice from line group by line_units,
line_price;
Query 5
select * from invoice order by inv_subtotal desc limit 1;
Query 6
select c.cus_code, c.cus_fname, c.cus_lname, i.inv_number, i.inv_date, i.inv_subtotal, i.inv_tax,
i.inv_total from invoice i natural join customer c having cus_lname='Orlando';

Query 7
INSERT INTO VENDOR VALUES('25596','Brett','Brett','615','890-3529','TN','Y');

Query 8
INSERT INTO CUSTOMER VALUES('11692223', Reddy','Jangam','W','713','844-2573','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='11692223';

Part B
NoSQL (Not Only SQL) Database
NoSQL database is a non relational database which does not require any relational schema, any
join. There is very easy access in the NoSQL database. it is used for real time applications and
big data. The real time applications like Google, Twitter, and Amazon etc. use the NoSQL
because they collect data in terabytes in a single day and it is very difficult to manage it in the
relational database. NoSQL supports the different range of database like structured, unstructured,
semi-structured, polymorphic database etc. These are the special features of NoSQL database-
1. It does not follow the relational database.
2. It works on BLOBs.
3. It does not require the normalization or object relational mapping.
4. It does not provide the fixed column tables.
There are four types of NoSQL databases-
1. Key-Value Pair
2. Document-Oriented
3. Column-Based
4. Graph-Based
(NoSQL.org n.d.)
(Guru99 2019)

Key-Value Pair
Key-value pair stores the data in the form of key and value. The key is unique and the value may
be text, BLOB, JSON etc. The key value data is stored in hash table is built in the way that can
handle loads of data with a ease.
The key-value is stored in the following way in hash table-
Website Value
Microsoft Data
Amazon Data
Twitter Data
The key is unique and the value is containing the data for the specified keys. It helps in storing
the schema less data and fetches the results in a very short time. It is mostly used in shopping
cart type websites.
Document- Oriented
The document-oriented NoSQL database works like Key-value database but the difference is that
the value in the document-oriented NoSQL database is in the structured format. The user can
query into the database. The data is stored in the XML format or JSON format.
It is generally used in the CMS project, blogging project, shopping websites etc. Lotus Notes,
MangoDB, CouchDB etc. are the databases which supports the document-oriented NoSQL data.
Column-Based
The column-based NoSQL database works on column and based upon the BigTable of Google.
Every column works separately. The performance of column based data type is very high in
aggregate functions queries.
It is generally used to manage data warehouse, library systems etc. HBase, HyperTable etc. are
the databases that are based upon the column-based NoSQL data.
Graph-Based
The graph-based NoSQL database works on the concept of entities and relationships among
them. The entity is stored as a node and edge works as a relationship between entities. All nodes
and edges contain the unique identifiers which differentiate them in the entire database.
Unlike relational database, the graph-based NoSQL database is a multi relational database. The
data traversal is very fast in this. It works best in social networking sites, spatial data etc.
Infinite Graph, FlockDB etc. are the databases that are based upon the graph-based NoSQL data.

References
Guru99. (2019). NoSQL Tutorial: Learn NoSQL Features, Types, What is, Advantage. Retrieved
from: https://www.guru99.com/nosql-tutorial.html
NoSQL.org (n.d.). NoDQL. Retrieved from: http://nosql-database.org/