Database System SQL Queries, Outputs and Analysis - [University Name]

This document presents a detailed solution to a database system assignment. It includes twelve SQL queries designed to retrieve and manipulate data from various tables such as 'invoice', 'customer', 'vendor', 'product', and 'line'. Each query is accompanied by its SQL code and the expected output, demonstrating how to extract specific information, filter data based on conditions, and perform aggregations. The queries cover a range of SQL functionalities, including JOIN operations, subqueries, aggregate functions, and conditional statements, providing a comprehensive overview of database querying techniques. The solution showcases the application of SQL in retrieving specific data, filtering records, and performing calculations, offering a valuable resource for students studying database systems. The assignment covers a wide range of SQL functions, including SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY clauses, along with the use of JOIN operations to combine data from multiple tables. The solutions provided are designed to help students understand and apply SQL effectively in practical database scenarios.

Running Head: DATABASE SYSTEM
Database System
[Name of the student]
[Name of the university]

1DATABASE SYSTEM
Table of Contents
Query 1 2
SQL Code: 2
Output: 3
Query 2 3
SQL Code: 3
Output: 4
Query 3 4
SQL Code: 4
Output: 5
Query 4 5
SQL Code: 5
Output: 6
Query 5 6
SQL Code: 6
Output: 7
Query 6 7
SQL Code: 7
Output: 8
Query 7 8

2DATABASE SYSTEM
SQL Code: 8
Output: 9
Query 8 9
SQL Code: 9
Output: 11
Query 9 11
SQL Code: 11
Output: 12
Query 10 12
SQL Code: 12
Output: 13
Query 11 13
SQL Code: 13
Output: 13
Query 12 13
SQL Code: 13
Output: 15

3DATABASE SYSTEM
Query 1
SQL Code:
SELECT DISTINCT
invoice.INV_NUMBER,
invoice.INV_DATE,
invoice.INV_SUBTOTAL,
invoice.INV_TAX,
invoice.INV_TOTAL,
invoice.CUS_CODE,
Count(line.INV_NUMBER)
FROM
invoice
NATURAL JOIN line
GROUP BY
invoice.INV_NUMBER,
invoice.INV_DATE,
invoice.INV_SUBTOTAL,

4DATABASE SYSTEM
invoice.INV_TAX,
invoice.INV_TOTAL,
invoice.CUS_CODE
Output:
Query 2
SQL Code:
SELECT
vendor.V_CODE,
vendor.V_NAME,
vendor.V_CONTACT,
vendor.V_AREACODE,
vendor.V_PHONE,
vendor.V_STATE,
vendor.V_ORDER

5DATABASE SYSTEM
FROM
vendor
WHERE
vendor.V_STATE IS NULL
Output:
Query 3
SQL Code:
SELECT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE
FROM

6DATABASE SYSTEM
customer
WHERE
customer.CUS_BALANCE BETWEEN 200 AND 300
Output:
Query 4
SQL Code:
SELECT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE,
invoice.INV_TOTAL

7DATABASE SYSTEM
FROM
customer
NATURAL JOIN invoice
HAVING
invoice.INV_TOTAL > 200
Output:
Query 5
SQL Code:
SELECT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,

8DATABASE SYSTEM
customer.CUS_BALANCE,
invoice.INV_DATE,
invoice.INV_SUBTOTAL,
invoice.INV_TAX,
invoice.INV_TOTAL,
invoice.INV_NUMBER
FROM
customer
NATURAL RIGHT JOIN invoice
Output:
Query 6
SQL Code:
SELECT
customer.CUS_CODE,

9DATABASE SYSTEM
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE,
invoice.INV_TOTAL
FROM
customer
NATURAL JOIN invoice
HAVING
invoice.INV_TOTAL > (SELECT Avg(invoice.INV_TOTAL) FROM invoice)
Output:

10DATABASE SYSTEM
Query 7
SQL Code:
SELECT DISTINCT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE
FROM
customer
NATURAL RIGHT JOIN invoice
Output:

11DATABASE SYSTEM
Query 8
SQL Code:
SELECT DISTINCT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE,
Max(invoice.INV_TOTAL)
FROM
customer
NATURAL JOIN invoice
GROUP BY
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,

12DATABASE SYSTEM
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE
LIMIT 1
Output:
Query 9
SQL Code:
SELECT
customer.CUS_LNAME,
customer.CUS_FNAME,
Count(invoice.INV_NUMBER) AS `Number of Times`
FROM
customer
NATURAL JOIN invoice
GROUP BY

13DATABASE SYSTEM
customer.CUS_LNAME,
customer.CUS_FNAME
Output:
Query 10
SQL Code:
SELECT
invoice.INV_NUMBER,
invoice.CUS_CODE,
invoice.INV_DATE,
invoice.INV_SUBTOTAL,
invoice.INV_TAX,
invoice.INV_TOTAL
FROM
invoice
WHERE

14DATABASE SYSTEM
invoice.CUS_CODE = 10018
Output:
Query 11
SQL Code:
SELECT DISTINCT
vendor.V_NAME
FROM
vendor
NATURAL RIGHT JOIN product
Output:
Query 12
SQL Code:
SELECT DISTINCT

15DATABASE SYSTEM
product.P_CODE,
product.P_DESCRIPT,
product.P_INDATE,
product.P_QOH,
product.P_MIN,
product.P_PRICE,
product.P_DISCOUNT,
product.V_CODE,
line.LINE_UNITS
FROM
product
INNER JOIN line
WHERE
line.LINE_UNITS > 10

16DATABASE SYSTEM
Output: