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 operations, including selecting distinct values, filtering data based on conditions, joining tables, using aggregate functions, and handling null values. The solutions provide the SQL code for each query along with the corresponding output, allowing students to understand how to formulate and execute queries to extract specific information from a database. The queries involve tables like 'invoice', 'vendor', 'customer', 'product', and 'line', showcasing practical applications of SQL in managing and analyzing data. This resource is ideal for students studying database systems and looking for examples and solutions to enhance their understanding of SQL and database design principles. Desklib offers this and other resources to help students with their studies.

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