MIS 325 Lecture Notes - Lecture 25: Sql, Semicolon
24 May 2018
School
Department
Course
Professor
MIS 325 – Intro to PL/SQL – 3.2
• What does a correlated query do
• How to identify a correlated query
o Correlated queries can refer to different tables
o Subquery refers to a column in a table belonging to the outer query
▪ i.e. outer query table = vendors
• subquery table = invoices
o but within subquery, we refer to columns in the vendor
table
o if subquery is part of select statement, it is probably a correlated query
o can you write a correlated subquery where you can write it outside of
select statement?
▪ Yes – ex: can put it in where clause
• If subquery is in where clause, subquery will be repeated for
each row in the table
• Key takeaways
o Correlated queries can be written as part of SELECT clause and WHERE
clause
o Correlated queries is going to repeat subquery for each of the rows in the
outer query
o To identify whether there is a correlated query, check references in
subquery and outer query
▪ If refers to different tables, it is correlated
PL/SQL
• 3 main parts of code
o declare
o begin
▪ outlines logic you want to create
o exception
▪ part where errors are handled
• end code with semicolon and keyword END;
• Sample code slide 5 ch 13
o 1. Declare/define sum_balance_due
o 2. Calculate sum for vendor 95 and assign it to sum_balance_due
o 3. If sum_balance_due > 0: Print “balance due: $” + sum_balance_due
▪ else print “balance paid in full”
• Exercise 1: slide 14
o Pseudo code
▪ 1. Declare/define num_invoices
▪ 2. calculate the colunt of balance_due > 5000 from invoices and
assign it to num_invoices
▪ 3. Print num_invoices + ‘invoices exceed $5000’
o actual code
▪ SET SERVEROUTPUT ON;
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
If refers to different tables, it is correlated. Pl/sql: 3 main parts of code, declare, begin, outlines logic you want to create, exception, part where errors are handled, end code with semicolon and keyword end, sample code slide 5 ch 13, 1. Calculate sum for vendor 95 and assign it to sum_balance_due: 3. If sum_balance_due > 0: print balance due: $ + sum_balance_due: else print balance paid in full , exercise 1: slide 14, pseudo code, 1. Declare/define num_invoices: 2. calculate the colunt of balance_due > 5000 from invoices and assign it to num_invoices, 3. Print num_invoices + invoices exceed ": actual code, set serveroutput on, declare invoice_count number, begin, select count(*) Into invoice_count: from invoices, where (invoice_total payment_total credit_total >, dbms_output_line (invoice_count || invoices exceed. ): end, exercise 2: slide 14, psuedo code, 1. Calculate num_invoices with total_balance_due > 0 and sum of invoice amount and assign them into the respective variables: 3.
