SQLite3 Queries: Database Implementation Assignment - Fall Semester

This document presents solutions to a database assignment focused on SQLite3 queries and database implementation. The assignment covers both Data Manipulation Language (DML) and Data Definition Language (DDL) queries. DML solutions include queries to retrieve data based on specific criteria, such as product codes, vendor details, customer balances, and invoice information. DDL solutions involve adding a new attribute to a table and updating existing records. The assignment also demonstrates how to delete records. The document includes a bibliography with relevant sources. Desklib provides this and other resources for students to aid in their studies.

Running head: SQLITE3 QUERY
SQLite3 Based Database Implementation
Name of the Student
Name of the University

1SQLITE3 QUERY
PART A: DML
Answer to Query 1: Show the details of the products where the product code starts with
'22'.
SELECT * FROM PRODUCT WHERE P_CODE LIKE '22%';
Answer to Query 2: Display the vendor details from areacode 615.
SELECT * FROM VENDOR WHERE V_AREACODE = 615;
Answer to Query 3: Find the balance for customer 'George Williams'.
SELECT CUS_BALANCE FROM CUSTOMER WHERE CUS_LNAME = 'Williams'
AND CUS_FNAME = 'George';
Answer to Query 4: Show the number of products from each vendor and display the
vendor code if there are more than 2 products from the same vendor.

2SQLITE3 QUERY
SELECT V_CODE, COUNT(P_CODE) AS 'Number of PRoducts' FROM PRODUCT
WHERE V_CODE!='' GROUP BY V_CODE;
Answer to Query 5: Display the vendor code, name, phone number and the number of
products from each vendor.
SELECT VENDOR.V_CODE, VENDOR.V_NAME, VENDOR.V_PHONE,
COUNT(PRODUCT.P_CODE) AS 'Product Quantity' FROM VENDOR Left Join PRODUCT
ON VENDOR.V_CODE=. PRODUCT.V_CODE GROUP BY PRODUCT.V_CODE;
Answer to Query 6: Find the customers whose balance is zero.
SELECT * FROM CUSTOMER WHERE CUS_BALANCE = 0;

3SQLITE3 QUERY
Answer to Query 7: List the name of the customers for whom we have invoices.
SELECT DISTINCT CONCAT(CUS_FNAME, ' ', CUS_LNAME) AS ' Name of the
Customer' FROM CUSTOMER Cu INNER JOIN INVOICE Inv ON Cu.CUS_CODE =
Inv.CUS_CODE;
Answer to Query 8: Show the name and cus_code of the customers for whom we DO
NOT have invoices.
SELECT DISTINCT CUS_CODE, CONCAT(CUS_FNAME, ' ', CUS_LNAME) AS '
Name of the Customer' FROM CUSTOMER WHERE NOT EXISTS (SELECT * FROM
INVOICE WHERE CUSTOMER.CUS_CODE=INVOICE.CUS_CODE);
Answer to Question 9: Find out how many times a customer generated an invoice.
Select Cus_Code, Count(Cus_Code) As 'Number of Invoices' From INVOICE Group By
Cus_Code;

4SQLITE3 QUERY
PART B: DDL
Answer to Query 1: Add a new attribute email_address varchar(30) to the customer table.
ALTER TABLE CUSTOMER ADD email_address varchar(30);
Answer to Query 2: Add email_address = '[e-mail]' for customer '10016'.
UPDATE CUSTOMER SET email_address='[e-mail]' WHERE
CUS_CODE=10016;
Answer to Query 3: Delete the record for employee Edward Johnson from EMP table.
DELETE FROM EMP WHERE EMP_FNAME='Edward' AND
EMP_LNAME='Johnson';

5SQLITE3 QUERY
Bibliography:
de Roos, A. (2015). A Relational Database of WHO Mortality Data Prepared to Facilitate Global
Mortality Research. Open Health Data, 3(1).
Raza, S. M., & Rajamanickam, L. (2015). A Proposed Solution for Crime Reporting and Crime
Updates on Maps in Android Mobile Application. International Journal of Computer
Applications, 124(1).