SQLite Database Queries and NoSQL Database Concepts Solution

This assignment solution provides a comprehensive overview of SQLite queries and NoSQL database concepts. The SQLite portion includes a series of queries designed to extract and manipulate data from a database, covering tasks such as joining tables, calculating totals, and updating records....Read More

Running head: SQLITE ASSIGNMENT
SQLite Assignment
Name of the Student
Name of the University
Author’s note:

1SQLITE ASSIGNMENT
Part A:
Query 1: select vendor.V_CODE, V_NAME, COUNT(P_CODE) AS
NumberOfProducts
From vendor
Inner Join product
On vendor.V_CODE = product.V_CODE
Group By vendor.V_CODE;
Query 2: Select CUS_LNAME, ROUND(SUM(INV_TOTAL), 2) as
Total_invoice_value
From customer
inner join invoice
on customer.CUS_CODE = invoice.CUS_CODE

2SQLITE ASSIGNMENT
group by customer.CUS_CODE;
Query 3: Select CUS_LNAME, Count(INV_TOTAL) as Num_Of_Invoices
From customer
inner join invoice
on customer.CUS_CODE = invoice.CUS_CODE
group by customer.CUS_CODE;
Query 4: Select LINE_UNITS, ROUND(SUM(LINE_PRICE), 2) as 'Total unit price'
from line
Group by LINE_UNITS;

3SQLITE ASSIGNMENT
Query 5: select * from invoice
where INV_SUBTOTAL = (select Min(i.INV_SUBTOTAL) from invoice i);
Query 6: select P_DESCRIPT from customer
inner join invoice
on customer.CUS_CODE = invoice.CUS_CODE
inner join line
on invoice.INV_NUMBER = line.INV_NUMBER
inner join product
on line.P_CODE = product.P_CODE
Where CUS_LNAME = 'Orlando';

4SQLITE ASSIGNMENT
Query 7: Insert into vendor values (25579, 'Fast Systems', 'Dr. Sarwar Tapan', 904, '245-
677', 'GA', 'Y')
Query 8: insert into customer values (11636604, 'Shukla', 'Hemang', 'H', 615, '922-677',
455.80)
Query 9: ALTER TABLE vendor
ADD COLUMN Customer_rating VARCHAR(14);
Query 10: ALTER TABLE vendor
ADD COLUMN CUS_CODE INT(11);

5SQLITE ASSIGNMENT
Update vendor Set CUS_CODE = 11636604 AND Customer_rating = 'Point 2' WHERE
V_CODE ='25579';
Update vendor Set Customer_rating = 'Great' Where CUS_CODE = 11636604;
Part B:
The NoSQL is a type of non-relational database management system. The NoSQL
database does not need any particular schema and preventing joining conventions and easy to
scale data (Lourenço et al., 2015). The NoSQL database can be used in the distributed data stores
with the extreme amount of data. The NoSQL is the basis of real-time website applications and
big data.
The NoSQL concept became popular because of the big internet enterprises like
Facebook, Google, Amazon and many more (Liao et al., 2016). These organizations handle a
huge chunk of data. The system response time reduces extremely in comparison with the
relational database management system in terms of huge data volume. In order to resolve the

6SQLITE ASSIGNMENT
issue, the organization can scale up the system through updating the existing hardware (Gessert
et al., 2017). The NoSQL database implementation is expensive process.
The features of the NoSQL are as following. The NoSQL databases can never follow the
relational database model (Fowler, Godin & Geddy, 2016). The NoSQL database does not offer
tables with the with the column records that are not fixed. The NoSQL database is able to work
with the independent BLOBs and aggregates. The NoSQL does not need data normalization and
the object relational mapping (Lourenço et al., 2015). The NoSQL does not have features like the
query planners and query languages, ACID and referential integrity. The NoSQL database are
having relaxed schemas or schema-free. The NoSQL does not need any kind of definition of the
data schema. The NoSQL provides the opportunity of heterogeneous frameworks of data in the
same domain (Fowler, Godin & Geddy, 2016). The NoSQL provides the opportunity for using
interfaces to store and query the provided data. The APIs permits low-level data selection and
manipulation methods. It also supports the text-based protocols which has been mainly utilized
with the JSON and HTTP REST. It does support the no standard based query language. Web-
enabled database runs as the internet-facing services (Gessert et al., 2017).
The NoSQL database can be categorized as the Key-value stores, Wide-column stores,
document databases and graph databases. The Key-value store is the simplest form of NoSQL
database (Fowler, Godin & Geddy, 2016). Attribute name is used for storing the database in
combination with its value. The most well-known in this category are Riak, Voldemort, and
Redis. The Wide-column stores is used for storing the data in combination with the columns
rather than the rows as well as supports query optimization (Liao et al., 2016). Cassandra and
HBase are the most popular NoSQL Wide-column stores type. The Document databases is used
by pairing every key with the complex data structure which is known as the document. The

7SQLITE ASSIGNMENT
documents can be containing various key-array pairs or key-value pairs or nested documents.
The MongoDB is the most popular document database (Fowler, Godin & Geddy, 2016). The
graph databases are utilized for storing the information regarding the networks. The networks
can be social connections. Neo4J and HyperGraphDB are typical examples of graph database.

8SQLITE ASSIGNMENT
References:
Fowler, B., Godin, J., & Geddy, M. (2016). Teaching Case Introduction to NoSQL in a
Traditional Database Course. Journal of Information Systems Education, 27(2), 99-103.
Gessert, F., Wingerath, W., Friedrich, S., & Ritter, N. (2017). NoSQL database systems: a survey
and decision guidance. Computer Science-Research and Development, 32(3-4), 353-365.
Liao, Y. T., Zhou, J., Lu, C. H., Chen, S. C., Hsu, C. H., Chen, W., ... & Chung, Y. C. (2016).
Data adapter for querying and transformation between SQL and NoSQL database. Future
Generation Computer Systems, 65, 111-121.
Lourenço, J. R., Cabral, B., Carreiro, P., Vieira, M., & Bernardino, J. (2015). Choosing the right
NoSQL database for the job: a quality attribute evaluation. Journal of Big Data, 2(1), 18.