SQL Queries Assignment: Database Management Fundamentals, Semester 1

This document presents solutions to a SQL queries assignment, focusing on database management using SQLite. The assignment covers a range of SQL queries, including selecting data, joining tables, filtering results based on conditions, and using aggregate functions. Part A includes queries for selecting data from the 'vendor', 'customer', and 'invoice' tables, and also demonstrates how to use joins, distinct clauses, and aggregate functions to retrieve and manipulate data. Part B focuses on data modification, including inserting new records, altering table structures (adding columns), updating existing data, and deleting records. The assignment provides a practical demonstration of SQL syntax and database operations, using SQLite as the database system. The bibliography includes references to relevant database and data migration resources.

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).