Database System: Query Solutions and Outputs - [University Name]

This document presents a comprehensive solution to a database design assignment, focusing on various SQL queries. The assignment includes twelve different queries, each designed to test different aspects of database manipulation and retrieval. The queries cover a range of database operati...Read More

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