SQL Queries Assignment - Database Module, Semester 1, University Name

This assignment focuses on practical SQL queries using SQLite. The student is required to execute a4.txt script, write and run SQL statements to complete various database tasks. The tasks include showing sums of line units for each invoice, details of products without a v_code, invoices within a specific subtotal range, the minimum subtotal, vendors, and products with specific criteria. The student is also tasked to use the command prompt for the execution of the queries. The assignment also includes a bibliography of resources used. This assignment is designed to test the student's understanding and application of SQL queries in a practical setting, using the SQLite database system. The student had to provide the SQL statements and the screenshot of the output.

Running head: SQLITE BASED SQL QUERY
SQLite based SQL Query
Name of the Student
Name of the University

1SQLITE BASED SQL QUERY
Query 1: SELECT line.INV_NUMBER, SUM(line.P_CODE) AS 'Total Line of Units'
FROM line Group By line.INV_NUMBER;
Query 2: SELECT * FROM product WHERE product.V_CODE = '';
Query 3: SELECT * FROM invoice WHERE invoice.INV_SUBTOTAL>24 AND
invoice.INV_SUBTOTAL<76;
Query 4: SELECT * FROM invoice WHERE invoice.INV_SUBTOTAL = (SELECT
MIN(i.INV_SUBTOTAL) FROM invoice i);
Query 5: SELECT DISTINCT vendor.V_CODE, vendor.V_NAME FROM vendor INNER
JOIN product ON vendor.V_CODE=product.V_CODE;

2SQLITE BASED SQL QUERY
Query 6: SELECT vendor.V_CODE FROM vendor EXCEPT SELECT product.V_CODE
FROM product;
Query 7: SELECT vendor.V_CODE, vendor.V_NAME FROM vendor WHERE
vendor.V_CODE NOT IN (SELECT product.V_CODE FROM product);
Query 8: SELECT product.V_CODE, COUNT(product.P_CODE) AS 'Number of products'
FROM product WHERE product.V_CODE <> '' GROUP BY product.V_CODE;
Query 9: SELECT * FROM emp WHERE emp.EMP_AREACODE = 615;

3SQLITE BASED SQL QUERY
Query 10: SELECT DISTINCT product.* FROM product INNER JOIN line ON
product.P_CODE = line.P_CODE WHERE line.LINE_PRICE >99;

4SQLITE BASED SQL QUERY
Bibliography:
Bhosale, S. T., Patil, T., & Patil, P. (2015). SQLite: Light Database System. International
Journal of Computer Science and Mobile Computing, 882-885.
Nemetz, S., Schmitt, S., & Freiling, F. (2018). A standardized corpus for SQLite database
forensics. Digital Investigation, 24, S121-S130.