SQL Query Practice with SQLite

This assignment focuses on practical application of SQL queries using the SQLite database. It comprises eight SQL queries in Part A, covering tasks such as retrieving vendor information, customer balance ranges, distinct customer names, maximum balances, customers without invoices, and invoice generation statistics. Part B delves into data manipulation with SQL, including inserting new customer records, adding a 'credit_history' column to the 'customer' table, updating credit history for a specific customer, deleting an employee record, and adding an 'email_address' column to the 'emp' table. The assignment also includes a brief bibliography.

Running head: SQL QUERY USING SQLITE
SQL Query Using SQLite
Name of the Student
Student ID
Subject Code

1SQL QUERY USING SQLITE
Part A:
Query 1:
SELECT * FROM vendor WHERE v_state = '';
Query 2:
SELECT * FROM Customer WHERE CUS_BALANCE>300 AND
CUS_BALANCE<400;
Query 3:
SELECT DISTINCT (cus .CUS_FNAME || ' ' || cus .CUS_LNAME) As 'Customer Name'
FROM Customer cus INNER JOIN Invoice i ON cus.CUS_CODE = i.CUS_CODE;
Query 4:
SELECT MAX(CUS_BALANCE) AS ‘Maximum Balance’ FROM Customer;

2SQL QUERY USING SQLITE
Query 5:
SELECT DISTINCT CUS_CODE FROM Customer cus WHERE NOT EXISTS
(SELECT * FROM Invoice i WHERE cus.CUS_CODE = i.CUS_CODE);
Query 6:
SELECT DISTINCT (cus .CUS_FNAME || ' ' || cus .CUS_LNAME) As 'Customer Name'
FROM CUSTOMER cus WHERE NOT EXISTS (SELECT * FROM Invoice i WHERE
cus.CUS_CODE = i.CUS_CODE);
Query 7:
SELECT cu.CUS_CODE, COUNT(l.INV_NUMBER) AS 'Invoices_generated' FROM
Customer cu INNER JOIN invoice i ON cu.CUS_CODE = i.CUS_CODE INNER JOIN Line l
ON i.INV_NUMBER=l.INV_NUMBER GROUP BY cu.CUS_CODE;

3SQL QUERY USING SQLITE
Query 8:
SELECT cu.CUS_CODE, (cu.CUS_FNAME || ' ' ||cu. CUS_LNAME) As 'Customer
Name', COUNT(l.INV_NUMBER) as 'N_Of_Invoices' FROM Customer cu INNER JOIN
INVOICE i ON cu.CUS_CODE = i.CUS_CODE INNER JOIN Line l ON
i.INV_NUMBER=l.INV_NUMBER GROUP BY cu.CUS_CODE;
Part B:
Query 1:
INSERT INTO CUSTOMER VALUES('11649523', 'Bhattarai', 'Aayush, 'A', '615', '844-
2573', ' 234.0');
Query 2:
ALTER TABLE CUSTOMER ADD credit_history VARCHAR(6);

4SQL QUERY USING SQLITE
Query 3:
UPDATE CUSTOMER SET credit_history = 'BAD' WHERE CUS_CODE = 10011;
Query 4:
DELETE FROM EMP WHERE EMP_LNAME = 'Genkazi' AND EMP_FNAME =
'Leighla';
Query 5:
ALTER TABLE EMP ADD email_address VARCHAR(20);

5SQL QUERY USING SQLITE
Bibliography:
Bhosale, S., Patil, M. T., & Patil, M. P. (2015). SQLite: Light Database System.
Vaidya, R., & Pundkar, S. (2015). Large Data migration within Cloud Environments using
Compression and Encryption Technique. migration, 2(2).