Database System Homework: SQL Queries, Code, and Output Analysis

This document provides a detailed analysis of a database system assignment, focusing on SQL queries and their corresponding outputs. The assignment covers a range of SQL commands, including SELECT statements with various clauses such as DISTINCT, WHERE, BETWEEN, NATURAL JOIN, RIGHT JOIN, INNER JOIN, and GROUP BY. The queries address tasks like retrieving specific customer and vendor information, filtering data based on conditions (e.g., balance ranges, invoice totals), joining multiple tables to extract combined data, and calculating aggregate values like counts and maximums. Each query is presented with its SQL code and the resulting output, demonstrating the practical application of SQL in database management. The document serves as a valuable resource for understanding and learning SQL syntax and database query optimization. This assignment is contributed by a student to be published on the website Desklib, a platform which provides all the necessary AI based study tools for students.

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

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

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

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,

5DATABASE SYSTEM
vendor.V_ORDER
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,

6DATABASE SYSTEM
customer.CUS_BALANCE
FROM
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,

7DATABASE SYSTEM
customer.CUS_BALANCE,
invoice.INV_TOTAL
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,

8DATABASE SYSTEM
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:

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

10DATABASE SYSTEM
Output:
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

11DATABASE SYSTEM
Output:
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

12DATABASE SYSTEM
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
SQL Code:
SELECT
customer.CUS_LNAME,
customer.CUS_FNAME,

13DATABASE SYSTEM
Count(invoice.INV_NUMBER) AS `Number of Times`
FROM
customer
NATURAL JOIN invoice
GROUP BY
customer.CUS_LNAME,
customer.CUS_FNAME
Output:
Query 10
SQL Code:
SELECT
invoice.INV_NUMBER,
invoice.CUS_CODE,
invoice.INV_DATE,

14DATABASE SYSTEM
invoice.INV_SUBTOTAL,
invoice.INV_TAX,
invoice.INV_TOTAL
FROM
invoice
WHERE
invoice.CUS_CODE = 10018
Output:
Query 11
SQL Code:
SELECT DISTINCT
vendor.V_NAME
FROM
vendor
NATURAL RIGHT JOIN product

15DATABASE SYSTEM
Output:
Query 12
SQL Code:
SELECT DISTINCT
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

16DATABASE SYSTEM
product
INNER JOIN line
WHERE
line.LINE_UNITS > 10
Output: