Basic Nested Queries and Views

1. Basic Nested Queries and Views Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

2. Objectives • Learn what is nested query and how to create basic nested SQL queries • Learn what is database views and how/why to create views

3. Subqueries and Their Uses • Subquery – a query nested inside another query • Used when a query is based on an unknown value • Requires SELECT and FROM clauses • Must be enclosed in parentheses • Place on right side of comparison operator

4. Creating Nested Queries • Used to select results based on the result of a query • Consists of a main query and one or more subqueries. • Main/outer query: first query that appears in the SELECT command • Subqueryretrieves values that the main query’s search condition must match that return one value that returns one value

5. ( ) Subquery Returns Single Value Query: List all students who have the same S_CLASS value as student Jorge Perez. SELECT s_last, s_first FROM student WHERE s_class = You need to run the following command to make the example work: @ c:\oradata\NW_CW\northwoods.sql SELECT s_class FROM student WHERE s_last = 'Perez' AND s_first = 'Jorge‘ ; S_LAST S_FIRST ---------- --------- Jones Tammy Perez Jorge

6. Nested Query WhereSubquery Returns Multiple Values • Syntax: SELECT column1, column2, … FROM table1, table2, … WHERE join conditions AND search_column1 IN (SELECT column1 FROM table1, table2, … WHERE search and join conditions) Subquery that returns multiple values

7. Nested Query WhereSubquery Returns Multiple Values • Display the names of all students who have enrolled in the same course sections as Jorge Perez. SELECT DISTINCT s_last, s_first FROM student, enrollment WHERE student.s_id = enrollment.s_id AND c_sec_id IN (SELECT c_sec_id FROM student, enrollment WHERE student.s_id = enrollment.s_id AND s_last = 'Perez' AND s_first = 'Jorge'); S_LAST S_FIRST --------------- ------------- Johnson Lisa Jones Tammy Marsh John Perez Jorge

8. A database view is a logical (virtual) table based on a query. Database Views Table-1 Table-2 Table-N It does not store data, but presents it in a format different from the one in which it is stored in the underlying tables. With the database view, you can view database records, but you can’t insert new records or modify or delete exiting records. database Single view table Database view Query Output: Report, Graphs

9. Views • Permanent objects (logical or virtual table) that store queries but no data • Based on a source query that: • can specify a subset of a single table’s fields or records • can join multiple tables • Two purposes • Reduce complex query requirements • Restrict users’ access to sensitive data (enforce security; user has access to view but not underlying table)

10. Creating and Using Database Views • Views can be updateable if: • SELECT clause contains only fieldnames, no functions or calculations • cannot contain the ORDER BY, DISTINCT, or GROUP BY clauses, group functions, or set operators • search condition cannot contain a nested query • Views are used like tables for selecting, inserting, updating and deleting data (only updatable views can be modified)

11. Database View (cont.) • SELECT view_name FROM user_views; • SELECT view_name FROM ALL_VIEWS WHERE owner=‘SYSTEM’; • DROP VIEW<view_name>; • CREATE ORREPLACE VIEW <view_name> AS <view query specification>;

12. Database View (cont.) • CREATE VIEW <view_name> AS <view query specification>; e.g., SELECT view_name FROM user_views; CREATEOR REPLACE VIEWcustomers_vu AS SELECT customer#, lastname, firstname, region FROM customers; What are gains? • DELETING VIEWS DROP VIEW viewname; DROP VIEW customers_vu; --EXTRA for VIEW SELECT firstname || ' ' || lastname AS Name, order#, orderdate FROM customers_vu, orders WHERE customers_vu.customer# = orders.order# ORDER BY Name;

13. Creating a Simple View • Only references one table – no group functions, GROUP BY clause, or expressions -- chapter 13, Figure 13-4; p.477 CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY; SELECT * FROM inventory; -- chapter 13, Figure 13-4(b); p.477 CREATE VIEW books_inventory_vu AS SELECT isbn, title, retail price FROM books WITH READ ONLY; SELECT * FROM books_inventory_vu; Figure 13-4 Selecting all records from the INVENTORY view What happen to “Database” after “DROP” view? DROP VIEW inventory; SELECT * FROM inventory; SELECT * FROM books; -- REcreate inventory view

14. Database View (continued) • CREATE VIEW <view_name> AS <view query specification>; e.g., SELECT view_name FROM user_views; CREATE OR REPLACEVIEW customers_view AS SELECT customer#, Lastname, Firstname FROM customers; SELECT view_name FROM user_views; SELECT * FROM customers_view; What are gains? • DELETING VIEWS DROP VIEW viewname; DROP VIEW customers_view; --more example for VIEW SELECT Lastname, Firstname, order# FROM customers_view cv, orders WHERE cv.customer# = orders.customer# AND cv.customer#=1001;

15. Database View (continued) • CREATE VIEW <view_name> AS <view query specification>; e.g., SELECT view_name FROM user_views; CREATE OR REPLACEVIEW faculty_view AS SELECT f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank FROM faculty; SELECT view_name FROM user_views; SELECT * FROM faculty_view; What are gains? • DELETING VIEWS DROP VIEW viewname; DROP VIEW faculty_view; --EXTRA for VIEW SELECT f_last, f_first, bldg_code, room FROM faculty_view, location WHERE faculty_view.loc_id = location.loc_id;

16. Can We Create a View with Multiple Tables? • CREATE OR REPLACEVIEWstudent_faculty_vu AS • SELECT s_id, s_last, s_first, student.f_id, f_last • FROM student, faculty • WHERE student.f_id = faculty.f_id; SELECT * FROM student_faculty_vu;

17. More to go … • More Nested Queries and Subqueries will be studied in chapter 12. • More views will be studied in chapter 13. • You will apply the “view” [an efficient way to produce the solution] to the mini case (MVC mini-case) that will be assigned later.