MIS 325 Lecture Notes - Lecture 24: Sql, Oracle Database, Sql*Plus
24 May 2018
School
Department
Course
Professor
MIS 325 – Intro to Unit 3 – 3.1
Answer to in class exercise 1:
SELECT vendor_id, sum(invoice_total) AS "Total Sum"
FROM Invoices
GROUP BY vendor_id
ORDER BY vendor_id
Exercise #2:
SELECT vendor_name, SUM(payment_total) AS payment_total_sum
FROM Vendors JOIN invoices
ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name
ORDER BY payment_total_sum DESC
Chapter 6 – Coding subqueries
• Subquery: select statement that is coded within another sql statement
• Four ways to introduce subquery in SELECT statement
o In a WHERE clause as a search condition
o In a HAVING clause as a search condition
o In a FROM clause as a table specification
o In a SELECT clause as a column specification
• SELECT statement returns either a table (rows and columns), vector (just a
column), or value (single value)
• How to use the ALL keyword
• Types of subqueries
o Correlated
▪ Divide problem into smaller problems
• Smallest problem = subquery
• Outer layer/larger problem = outer query
▪ Example in class: Subquery won’t run on their own
• Search condition refers to a join condition but the second
table is not in the subquery’s FROM statement
• It’s okay when subquery is embedded in larger shell, but
independently subquery cannot stand
• This subquery within an outer query → shows the idea of a
correlated subquery
• Join and subquery can be interchangeable – both do the same thing
Chapter 7 – how to insert, update, delete data
• Updating
o Does all but nothing
• Insert
find more resources at oneclass.com
find more resources at oneclass.com
