Database Design Queries and Solutions for Database System

This assignment showcases solutions to various SQL queries designed for a database system. The document includes 12 distinct queries, each addressing different aspects of database interaction, such as data retrieval, filtering, aggregation, and joining multiple tables. The queries cover a range of functionalities, from simple selections and filtering based on specific criteria to more complex operations involving joins, subqueries, and aggregate functions like COUNT, AVG, and MAX. The queries demonstrate how to extract specific information from tables like 'invoice,' 'customer,' 'vendor,' 'product,' and 'line,' providing insights into data analysis and database design. This assignment serves as a valuable resource for students learning about database management and SQL programming, offering practical examples of how to formulate and execute queries to extract meaningful information from a relational database.

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 6
Query 8 7
Query 9 8
Query 10 9
Query 11 9
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
Screenshot

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
Screen shot
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
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

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

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

7DATABASE SYSTEM
customer.CUS_PHONE,
customer.CUS_BALANCE
FROM
customer
NATURAL RIGHT JOIN invoice
Query 8
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,

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