Database Assignment: SQL Queries, NoSQL Database Types and Examples

This database assignment solution comprises two main parts. Part A provides solutions to several SQL queries, demonstrating database interactions such as joining tables (vendor and product, customer and invoice), aggregating data (counting products per vendor, calculating invoice totals), and filte...Read More

Database Assignment
May 22
2019
Student ID:
Student name:

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

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

Query 3
select concat(cus_fname,' ' , cus_lname) as CustomerName , count(invoice.cus_code) as
'Num_Of_Invoices' 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 total_unit_price 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','SARWAR','904','456-0092','FL','Y');
Query 8
INSERT INTO CUSTOMER VALUES('11692222','THEEPIREDDY','HIMABINDU','A','615','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='11692222';

Part B
NoSQL Database
NoSQL database stands for the Not Only SQL Database that works with different types of data
models like key-value, document, columnar and graph. It is the alternate of traditional relational
database management system in which the data can be stored in tables and columns only.
NoSQL database is used for large and big data. It is used for real time applications. Large scale
organizations like Google, Amazon etc. use the NoSQL which is used to focus on operations in
the database. (TechTarget. 2019)
Basically there are four types of NoSQL data types-
- Key-Value Store
- Document-Based Store
- Column-Based Store
- Graph-Based Store
Key-Value Store
Key-value store NoSQL database is a hash table which contains keys and values. It is a schema
less format. The key may be auto generated for entered value and the value may be string, blob,
JSON etc. The format of key-value hash table is like-
Key Value
Shelf 1 Book 1
Shelf 2 Book 2
Shelf 3 Book 3
The key is the shelf and value is the book on specific shelf. This key-value data type allows
reading and writing the value by using the key. Riak, Amazon etc. are using the Key-value store
NoSQL database.
Document-Based Store
Document-based store NoSQL database is a collection of key-value based data in a compressed
way in the form of document. The basic difference between the key-value and document-based
store is that the value is in a specific structure like XML, JSON, and BSON etc. XML, JSON,
BSON are the form of managed data.
The document in the document-store NoSQL database is made up of Meta tags. The Meta data is
connected with stored data which helps in querying the data.

CouchDB of apace used the Document-based store NoSQL database to store the data. It uses
JSON for data storage and JavaScript language to query the database.
Column-Based Store
In Column-based store NoSQL database, the data is stored in the form of columns in spite of
rows. Columns are grouped logically and can be increased according to need dynamically. In
RDBMS, the data is stored in the form of rows while in Column-based store NoSQL database,
the data is stored in the form of columns. There are different benefits of storing the data into
column format-
- It makes fast access of data.
- It makes fast data aggregation.
Big table of Google and HBASE used to store the database in Column-based store NoSQL
database. Big table of Google is a very high in speed and the data is in compressed format.
Graph-Based Store
In Graph-based store NoSQL database, the data is stored in the form of graphs instead of tables,
columns etc. the data from one model to another can be easily translated by the Graph-based
store NoSQL database. It stores the data in the form of nodes and edges.
It is mostly used in network applications which sore the data in the form of modes and edges for
data storage. Neo4J use the Graph-based store NoSQL database. (3pillarglobal.com n.d.).
References
TechTarget. (2019). NoSQL (Not Only SQL database). Retrieved from:
https://searchdatamanagement.techtarget.com/definition/NoSQL-Not-Only-SQL
3pillarglobal.com (n.d.). EXPLORING THE DIFFERENT TYPES OF NOSQL DATABASES
PART II. Retrieved from: https://www.3pillarglobal.com/insights/exploring-the-different-
types-of-nosql-databases