Database System Queries and Solutions

This assignment focuses on database system querying and problem-solving. It presents twelve SQL queries designed to retrieve specific information from tables related to customers, invoices, vendors, and products. Each query is accompanied by its expected output, demonstrating the retrieval of diverse data points such as customer names, invoice details, vendor information, product descriptions, and quantities. The assignment requires a thorough understanding of SQL syntax and database relationships to accurately solve each query.

Running Head: DATABASE SYSTEM
Database Design
[Student Name]
[University Name]

1DATABASE SYSTEM
Table of Contents
Query 1 2
Query 2 3
Query 3 3
Query 4 4
Query 5 5
Query 6 6
Query 7 7
Query 8 7
Query 9 8
Query 10 9
Query 11 10
Query 12 10

2DATABASE SYSTEM
Query 1
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,
invoice.INV_TAX,
invoice.INV_TOTAL,
invoice.CUS_CODE
Output

3DATABASE SYSTEM
Query 2
SELECT
vendor.V_CODE,
vendor.V_NAME,
vendor.V_CONTACT,
vendor.V_AREACODE,
vendor.V_PHONE,
vendor.V_STATE,
vendor.V_ORDER
FROM
vendor
WHERE
vendor.V_STATE IS NULL
Output
Query 3
SELECT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,

4DATABASE SYSTEM
customer.CUS_BALANCE
FROM
customer
WHERE
customer.CUS_BALANCE BETWEEN 200 AND 300
Output
Query 4
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
FROM
customer
NATURAL JOIN invoice
HAVING
invoice.INV_TOTAL > 200
Output

5DATABASE SYSTEM
Query 5
SELECT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
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

6DATABASE SYSTEM
Query 6
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
FROM
customer
NATURAL JOIN invoice
HAVING
invoice.INV_TOTAL > (SELECT Avg(invoice.INV_TOTAL) FROM invoice)
Output

7DATABASE SYSTEM
Query 7
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
Query 8
SELECT DISTINCT
customer.CUS_CODE,
customer.CUS_LNAME,
customer.CUS_FNAME,
customer.CUS_INITIAL,
customer.CUS_AREACODE,

8DATABASE SYSTEM
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,
customer.CUS_INITIAL,
customer.CUS_AREACODE,
customer.CUS_PHONE,
customer.CUS_BALANCE
LIMIT 1
Output
Query 9
SELECT
customer.CUS_LNAME,
customer.CUS_FNAME,
Count(invoice.INV_NUMBER) AS `Number of Times`
FROM
customer
NATURAL JOIN invoice