compsci-notes-spring-2024/notes/database-prog/sql-scripts/2024-02-05.sql

---- CHAPTER 4 ----

-- Start-of-class problems --
USE AP
-- Get vendors who have default terms of greater than 1 months
SELECT VendorName, VendorID, TermsDueDays
FROM Vendors v
    JOIN Terms t ON t.TermsID = v.DefaultTermsID
WHERE TermsDueDays > 30

-- Get the contacts and contact updates for vendors 44, 75, and 94
    SELECT 'Current' TYPE, VendorID, VendorName, VendorContactLName, VendorContactFName
    FROM Vendors
    WHERE VendorID IN (44, 75, 94)
    -- UNION combines it as if it was one table, it can "update" the columns rather than add more
UNION
    SELECT 'Updates' TYPE, VendorID, '', LastName, FirstName
    FROM ContactUpdates
    WHERE VendorID IN (44, 75, 94)
-- Note that ORDER BY cannot be used on UNION queries, it's applied to the final query
ORDER BY VendorID, TYPE

-- Get invoices for all vendors except those in TX
    SELECT InvoiceID
    FROM Invoices
    -- Returns the query except for the stuff the matches the EXCEPT's query
EXCEPT
    SELECT VendorID
    FROM Vendors
    WHERE VendorState = 'TX'
------------------------------------------------------

-- Temporary holiday table
-- Common use for UNIONs, can act like a makeshift table
    SELECT '1/1/2024' DateValue, 'New Years Day' DateName
UNION
    SELECT '1/15/2024', 'MLK Day'
UNION
    SELECT '2/19/2024', 'Presidents Day'
UNION
    SELECT '7/4/2024', 'Fourth of July'

-- stuff
USE WideWorldImporters
SELECT CustomerID, CustomerName, c.BuyingGroupID
FROM Sales.Customers c
    JOIN Sales.BuyingGroups bg ON c.BuyingGroupID = bg.BuyingGroupID
ORDER BY CustomerName

--------------------------------------------------------------------------------------------

---- CHAPTER 5 ----

USE AP
-- Aggregates
-- A function that's applied to all rows

-- Count the number of invoices vendor 123 has
-- it doesn't matter which column, so * is fine
SELECT COUNT(*)
FROM Invoices
WHERE VendorID = 123

-- How many invoices in 2019
SELECT COUNT(*) TotalInvoices
FROM Invoices
WHERE YEAR(InvoiceDueDate) = 2019

-- MIN, MAX
-- returns the smallest and largest things
-- can't mix and match aggregate functions with other stuff, this doesn't work
SELECT VendorID, MIN(InvoiceTotal) SmallestInvoice, MAX(InvoiceTotal) LargestInvoice
FROM Invoices
WHERE YEAR(InvoiceDueDate) = 2020

-- sum, average, min, and max invoice totals
SELECT SUM(InvoiceTotal) SumOfInvoices, AVG(InvoiceTotal) AvgInvoice, MIN(InvoiceTotal) SmallestInvoice, MAX(InvoiceTotal) LargestInvoice
FROM Invoices

----------------
-- 2024-02-07 --
----------------

-- Get vendors with 3 or more invoices
SELECT i.VendorID, v.VendorName, COUNT(*)
FROM Invoices i
    JOIN Vendors v ON i.VendorID = v.VendorID
GROUP BY i.VendorID, v.VendorName
HAVING COUNT(*) >= 3
ORDER BY i.VendorID

-- CHAPTER 6 --

-- Get vendors with no invoices
SELECT VendorID
FROM Vendors
WHERE VendorID NOT IN (SELECT VendorID
FROM Invoices)
ORDER BY VendorID

-- Sub-queries aren't ideal, they perform worse than JOINs. With a JOIN:
SELECT v.VendorID
FROM Vendors v
    LEFT JOIN Invoices i ON v.VendorID = i.VendorID
WHERE InvoiceID IS NULL


-- Get all invoices > average invoice total
-- Usually can't get rid of the sub-query for aggregate stuff like this
SELECT InvoiceID
FROM Invoices
WHERE InvoiceTotal > (SELECT AVG(InvoiceTotal)
FROM Invoices)
ORDER BY InvoiceTotal

-- Get all invoices > average invoice total by vendor
-- It's got a sub-query, but it only runs once so the performance isn't *that* bad
SELECT TOP 10 InvoiceID, InvoiceTotal, i.VendorID, AvgTotal
FROM Invoices i
    JOIN (SELECT VendorID, AVG(InvoiceTotal) AvgTotal
    FROM Invoices
    GROUP BY VendorID) AvgTotal ON i.VendorID = AvgTotal.VendorID
WHERE InvoiceTotal >
(SELECT AVG(InvoiceTotal)
FROM Invoices)
ORDER BY i.VendorID, InvoiceTotal