SQL Queries and NoSQL Databases: ITC556 Semester 1

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

ITC556
ASSESSMENT – 4

Table of Contents
Part-A...............................................................................................................................................3
Part-B.............................................................................................................................................11
References......................................................................................................................................12
2

Part-A
Q1.
select VND.V_CODE, VND.V_NAME, count(PRDCT.V_CODE) as SUPPLIED
from PRODUCT PRDCT, VENDOR VND
where VND.V_CODE=PRDCT.V_CODE
group by VND.V_CODE, VND.V_NAME;
Screenshot:
Figure 1 screenshot of query1
Q2.
SELECT CU.CUS_LNAME, SUM(INC.INV_TOTAL) AS Total_invoice_value
FROM CUSTOMER CU, INVOICE INC
WHERE CU.CUS_CODE=INC.CUS_CODE
group by CU.CUS_LNAME,INC.CUS_CODE;
Screenshot:
3

Figure 2 screenshot of query2
Q3.
SELECT CONCAT(CU.CUS_FNAME,' ',CU.CUS_LNAME) as Name_of_cust ,
COUNT(INC.INV_NUMBER) AS Num_of_Invoices
FROM CUSTOMER CU, INVOICE INC
WHERE CU.CUS_CODE=INC.CUS_CODE
group by CU.CUS_LNAME,INC.CUS_CODE, CU.CUS_FNAME;
Screenshot:
4

Figure 3 screenshot of query3
Q4.
SELECT inv_number, SUM(LINE_UNITS*LINE_PRICE) AS PRICE
FROM LINE
GROUP BY INV_NUMBER;
Screenshot:
5

Figure 4 screenshot of query4
Q5.
select * from invoice where inv_subtotal= (select min(inv_subtotal) from invoice);
Screenshot:
Figure 5 screenshot of query5
Q6.
select cu.cus_lname, ine.inv_number
6

from customer cu, invoice ine
where cu.cus_code=ine.cus_code and cu.cus_lname='orlando';
Screenshot:
Figure 6 screenshot of query6
Q7.
insert into vendor values (11225,'Sam, Inc.','Robert','885','639-2365','TN','N');
7

Screenshot:
Figure 7 screenshot of query7
Q8.
insert into customer values (11693685,'Goli', ‘Vishal Kumar ','A','789','154-1569', 0);
Screenshot:
8

Figure 8 screenshot of query8
Q9.
alter table vendor
add customer_rating varchar(14);
Screenshot:
9

Figure 9 screenshot of query9
Q10.
UPDATE vendor
SET Customer_rating = 'GREAT'
WHERE V_NAME = 'Sam, Inc.';
Screenshot:
10

Figure 10 screenshot of query10
11

Part-B
The data can be stored in different ways in the NoSQL database. On the basis of the data storage
the NoSQL database divided into four types. These databases are
Key-Value database:
In this database data store in the key-value pairs. Each key-value is the type of arbitrary string
like hash value. The value in the database stored as a BLOB. BLOB improve the performance of
the database by removing the need to index the data. This type of key-pair storage does not have
any query language. Key-Value database used for storing the user session data. It is also useful
for storing user preferences. Key-Value database is used when a user has to perform the query by
the specific data value. It is also useful when the user has to operate on the multiple unique keys.
Examples: Aerospike, Redis. Memcached, Redis, etc.
Document database:
Document database stores the data like a key-value database. The data store in the key-value
format. The only difference between them is that the values stored in the form of JSON, XML,
and BSON. It allows the complex data to store in the database. In this database every document
is standalone. It solves the data sharing problem across the multiple nodes by not allow the joins
in the database. A document database is useful for platforms like analytics, blogging, and E-
commerce. It is also useful for content management systems.
Example: MongoDB, Terrastore, RavenDB, CouchDB etc.
Column NoSQL database:
Column database stores the data in the columns. This database includes the columns families.
The column families consist of an unlimited virtual number of columns which is created during
the defining of the schema. The read and write functions are performed on columns rather than
rows. The data stored in the particular columns are particular of the same type. The benefit of
this type of store is that data can be searched fast. The column database is used in content
management systems. It is also used in expiry usage services. It is beneficial for the services
which have heavy write requests.
12