Database Management System Queries

This assignment focuses on Database Management System concepts and requires solving twelve SQL queries. The queries involve various operations like selecting data based on conditions, joining tables, aggregating data, and using subqueries. Topics covered include retrieving invoice information, customer details, vendor information, and product data. The assignment aims to assess understanding of fundamental database query techniques.

1
Running Head: DATABASE MANAGEMENT SYSTEM
Database Management System
Student’s Name
Institution

2
DATABASE MANAGEMENT SYSTEM
Database Management System
Query 1
SELECT INV_NUMBER,SUM(LINE_TOTAL) FROM LINE GROUP BY INV_NUMBER
Query 2
SELECT * FROM VENDOR WHERE V_STATE IS null
Query 3
SELECT * FROM CUSTOMER WHERE CUS_BALANCE>200 AND CUS_BALANCE<300

3
DATABASE MANAGEMENT SYSTEM
Query 4
SELECT * FROM CUSTOMER JOIN INVOICE ON
CUSTOMER.CUS_CODE=INVOICE.CUS_CODE WHERE INV_TOTAL>200
Query 5
SELECT * FROM CUSTOMER JOIN INVOICE ON
CUSTOMER.CUS_CODE=INVOICE.CUS_CODE GROUP BY CUSTOMER.CUS_CODE
Query 6
SELECT CUSTOMER.CUS_CODE, CUS_LNAME, CUS_FNAME, INV_TOTAL,
CUS_PHONE FROM CUSTOMER JOIN INVOICE ON
CUSTOMER.CUS_CODE=INVOICE.CUS_CODE WHERE INV_TOTAL>(SELECT
AVG(INV_TOTAL) FROM INVOICE)

4
DATABASE MANAGEMENT SYSTEM
Query 7
SELECT CUSTOMER.CUS_CODE, CUS_LNAME, CUS_FNAME FROM CUSTOMER
WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE)
Query 8
SELECT CUSTOMER.CUS_CODE, CUS_LNAME, CUS_FNAME, INV_TOTAL,
CUS_PHONE FROM CUSTOMER JOIN INVOICE ON
CUSTOMER.CUS_CODE=INVOICE.CUS_CODE GROUP BY CUSTOMER.CUS_CODE
HAVING MAX(INV_TOTAL)

5
DATABASE MANAGEMENT SYSTEM
Query 9
SELECT CUS_LNAME, CUS_FNAME, COUNT(*) INVOICE_COUNT FROM CUSTOMER
JOIN INVOICE ON CUSTOMER.CUS_CODE=INVOICE.CUS_CODE GROUP BY
INVOICE.CUS_CODE
Query 10
SELECT PRODUCT.P_CODE, P_DESCRIPT FROM PRODUCT JOIN LINE ON
LINE.P_CODE=PRODUCT.P_CODE JOIN INVOICE ON
INVOICE.INV_NUMBER=LINE.INV_NUMBER WHERE CUS_CODE='10018'
Query 11
SELECT * FROM VENDOR WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT)

6
DATABASE MANAGEMENT SYSTEM
Query 12
SELECT * FROM PRODUCT INNER JOIN LINE ON PRODUCT.P_CODE=LINE.P_CODE
WHERE LINE.LINE_UNITS>10