1 / 15

Introduction to Views and Reports

Introduction to Views and Reports. Please use speaker notes for additional information!. SQL> SELECT * FROM course00 ; COURS COURSENAME CREDITS ----- ------------------------------ --------- CIS11 Intro to Computer Info Systems 3

shana
Download Presentation

Introduction to Views and Reports

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to Views and Reports Please use speaker notes for additional information!

  2. SQL> SELECT * FROM course00; COURS COURSENAME CREDITS ----- ------------------------------ --------- CIS11 Intro to Computer Info Systems 3 CIS44 Internet User/Developer 3 CIS50 Oracle and SQL 3 CIS56 Visual Basic 3 MAN11 Intro to Management 3 MAR11 Marketing Principles 3 SQL> SELECT * FROM stucourse00; STUD COURS SEMTA GR ---- ----- ----- -- 1111 CIS11 F2000 A- 1111 MAR11 F2000 A 1111 CIS44 S2000 A 1212 CIS44 S2000 A 2222 CIS44 S2000 A 2222 MAN11 F2000 A- 3333 CIS44 F2000 B 3333 CIS44 F2000 B 3333 CIS50 F2000 B+ 3333 CIS56 S2000 A- 2345 CIS50 F2000 I Views SQL> CREATE VIEW stucrswithcr 2 AS 3 SELECT studentidno, stucourse00.coursecd, credits, grade 4 FROM stucourse00, course00 5 WHERE stucourse00.coursecd = course00.coursecd; View created. SQL> SELECT * FROM stucrswithcr; STUD COURS CREDITS GR ---- ----- --------- -- 1111 CIS11 3 A- 1111 CIS44 3 A 1212 CIS44 3 A 2222 CIS44 3 A 3333 CIS44 3 B 3333 CIS44 3 B 3333 CIS50 3 B+ 2345 CIS50 3 I 3333 CIS56 3 A- 2222 MAN11 3 A- 1111 MAR11 3 A 11 rows selected. A view which contains information from stucourse00 and course00 was created. To see the output of the view, do a SELECT just as you would for a table.

  3. View SQL> SELECT * FROM stucrswithcr; STUD COURS CREDITS GR ---- ----- --------- -- 1111 CIS11 3 A- 1111 CIS44 3 A 1212 CIS44 3 A 2222 CIS44 3 A 3333 CIS44 3 B 3333 CIS44 3 B 3333 CIS50 3 B+ 2345 CIS50 3 I 3333 CIS56 3 A- 2222 MAN11 3 A- 1111 MAR11 3 A 11 rows selected. SQL> SELECT * 2 FROM stucrswithcr 3 WHERE grade IN ('A+','A','A-'); STUD COURS CREDITS GR ---- ----- --------- -- 1111 CIS11 3 A- 1111 CIS44 3 A 1212 CIS44 3 A 2222 CIS44 3 A 3333 CIS56 3 A- 2222 MAN11 3 A- 1111 MAR11 3 A 7 rows selected. This SELECT uses the view created on the previous slide and shown above. The SELECT is taking records that meet specific criteria from the view just as it would from at table.

  4. SQL> SELECT * FROM stucourse00; STUD COURS SEMTA GR ---- ----- ----- -- 1111 CIS11 F2000 A- 1111 MAR11 F2000 A 1111 CIS44 S2000 A 1212 CIS44 S2000 A 2222 CIS44 S2000 A 2222 MAN11 F2000 A- 3333 CIS44 F2000 B 3333 CIS44 F2000 B 3333 CIS50 F2000 B+ 3333 CIS56 S2000 A- 2345 CIS50 F2000 I SQL> INSERT INTO stucourse00 2 VALUES ('1111','CIS50','S2000','A+'); 1 row created. SQL> SELECT * FROM stucourse00; STUD COURS SEMTA GR ---- ----- ----- -- 1111 CIS11 F2000 A- 1111 MAR11 F2000 A 1111 CIS44 S2000 A 1212 CIS44 S2000 A 2222 CIS44 S2000 A 2222 MAN11 F2000 A- 3333 CIS44 F2000 B 3333 CIS44 F2000 B 3333 CIS50 F2000 B+ 3333 CIS56 S2000 A- 2345 CIS50 F2000 I 1111 CIS50 S2000 A+ 12 rows selected. Views Insert new record into stucourse00. Stucoure00 prior to insert Stucourse00 after the insert. SQL> SELECT * FROM stucrswithcr; STUD COURS CREDITS GR ---- ----- --------- -- 1111 CIS11 3 A- 1111 CIS44 3 A 1212 CIS44 3 A 2222 CIS44 3 A 3333 CIS44 3 B 3333 CIS44 3 B 3333 CIS50 3 B+ 2345 CIS50 3 I 1111 CIS50 3 A+ 3333 CIS56 3 A- 2222 MAN11 3 A- 1111 MAR11 3 A 12 rows selected. The new record has now been added to the stucrswithcr view.

  5. Create view SQL> CREATE VIEW stucrswithname 2 AS 3 SELECT studentidno, stucourse00.coursecd, coursename, credits, grade 4 FROM stucourse00, course00 5 WHERE stucourse00.coursecd = course00.coursecd; SQL> SELECT * FROM stucrswithname; STUD COURS COURSENAME CREDITS GR ---- ----- ------------------------------ --------- -- 1111 CIS11 Intro to Computer Info Systems 3 A- 1111 CIS44 Internet User/Developer 3 A 1212 CIS44 Internet User/Developer 3 A 2222 CIS44 Internet User/Developer 3 A 3333 CIS44 Internet User/Developer 3 B 3333 CIS44 Internet User/Developer 3 B 3333 CIS50 Oracle and SQL 3 B+ 2345 CIS50 Oracle and SQL 3 I 1111 CIS50 Oracle and SQL 3 A+ 3333 CIS56 Visual Basic 3 A- 2222 MAN11 Intro to Management 3 A- 1111 MAR11 Marketing Principles 3 A 12 rows selected.

  6. view SQL> DROP VIEW stucrswithname; View dropped. DROP VIEW allows me to eliminate a view. Notice that neither DESC or SELECT can find the view once it has been dropped. SQL> DESC stucrswithname; Object does not exist. SQL> SELECT * FROM stucrswithname; SELECT * FROM stucrswithname * ERROR at line 1: ORA-00942: table or view does not exist

  7. view SQL> CREATE VIEW majorinfo 2 AS 3 SELECT name, student00.majorcode, majorname, chair 4 FROM student00, major00 5 WHERE student00.majorcode = major00.majorcode; SQL> / View created. The select below shows the information in the view that was created using information from student00 and major00. SQL> SELECT * FROM majorinfo; NAME MA MAJORNAME CHAIR -------------------- -- ------------------------------ ---------- Stephen Daniels BU Business Administration Adams Carl Hersey BU Business Administration Adams Jennifer Ames CI Computer Information Systems Grocer Mary Stanton CI Computer Information Systems Grocer John Richards CI Computer Information Systems Grocer

  8. view SQL> SELECT * FROM majorinfo 2 WHERE chair = 'Grocer'; NAME MA MAJORNAME CHAIR -------------------- -- ------------------------------ ---------- Jennifer Ames CI Computer Information Systems Grocer Mary Stanton CI Computer Information Systems Grocer John Richards CI Computer Information Systems Grocer SQL> SELECT * FROM majorinfo 2 WHERE name > 'J' AND majorcode = 'CI'; NAME MA MAJORNAME CHAIR -------------------- -- ------------------------------ ----------- Jennifer Ames CI Computer Information Systems Grocer Mary Stanton CI Computer Information Systems Grocer John Richards CI Computer Information Systems Grocer SQL> SELECT * FROM majorinfo 2 WHERE name > 'L' OR majorcode = 'BU'; NAME MA MAJORNAME CHAIR -------------------- -- ------------------------------ ---------- Stephen Daniels BU Business Administration Adams Carl Hersey BU Business Administration Adams Mary Stanton CI Computer Information Systems Grocer

  9. view SQL> CREATE VIEW stucrsinfo 2 AS 3 SELECT student00.studentidno, name, stucourse00.coursecd, coursename, grade 4 FROM student00, stucourse00, course00 5 WHERE student00.studentidno = stucourse00.studentidno 6 AND stucourse00.coursecd = course00.coursecd; View created. SQL> SELECT * FROM stucrsinfo; STUD NAME COURS COURSENAME GR ---- -------------------- ----- ------------------------------ -- 1111 Stephen Daniels CIS11 Intro to Computer Info Systems A- 1111 Stephen Daniels CIS44 Internet User/Developer A 1111 Stephen Daniels CIS50 Oracle and SQL A+ 1111 Stephen Daniels MAR11 Marketing Principles A 1212 Jennifer Ames CIS44 Internet User/Developer A 2222 Carl Hersey CIS44 Internet User/Developer A 2222 Carl Hersey MAN11 Intro to Management A- 2345 Mary Stanton CIS50 Oracle and SQL I 3333 John Richards CIS44 Internet User/Developer B 3333 John Richards CIS44 Internet User/Developer B 3333 John Richards CIS56 Visual Basic A- 3333 John Richards CIS50 Oracle and SQL B+ 12 rows selected.

  10. Correcting my errors SQL> CREATE VIEW crsinfoA 2 AS 3 SELECT student00.studentidno, name stucourseoo.coursecd, coursename, grade 4 FROM student00, stucourse00, course00 5 WHERE student00.studentidno = stucourse00.studentidno 6 AND stucourse00.coursecd = course00.coursecd 7 AND SUBSTR(coursecd,1,3) = 'CIS'; SELECT student00.studentidno, name stucourseoo.coursecd, coursename, grade * ERROR at line 3: ORA-00923: FROM keyword not found where expected My first error needed to have line 3 changed so that stucourseoo became stucourse00. SQL> 3 3* SELECT student00.studentidno, name stucourseoo.coursecd, coursename, grade SQL> c/stucourseoo/stucourse00 3* SELECT student00.studentidno, name stucourse00.coursecd, coursename, grade The / will attempt execution. SQL> / SELECT student00.studentidno, name stucourse00.coursecd, coursename, grade * ERROR at line 3: ORA-00923: FROM keyword not found where expected SQL> 3 3* SELECT student00.studentidno, name stucourse00.coursecd, coursename, grade SQL> c/name/name, 3* SELECT student00.studentidno, name, stucourse00.coursecd, coursename, grade My second error was omitting the comma after name. Again it was on line 3 and I did the change of name to name,

  11. Correcting my errors continued Being optimistic, I used the ; to see the changed code and then used the / to attempt to execute. SQL> ; 1 CREATE VIEW crsinfoA 2 AS 3 SELECT student00.studentidno, name, stucourse00.coursecd, coursename, grade 4 FROM student00, stucourse00, course00 5 WHERE student00.studentidno = stucourse00.studentidno 6 AND stucourse00.coursecd = course00.coursecd 7* AND SUBSTR(coursecd,1,3) = 'CIS' SQL> / AND SUBSTR(coursecd,1,3) = 'CIS' * ERROR at line 7: ORA-00918: column ambiguously defined SQL> 7 7* AND SUBSTR(coursecd,1,3) = 'CIS' SQL> c/coursecd/stucourse00.coursecd 7* AND SUBSTR(stucourse00.coursecd,1,3) = 'CIS' SQL> / View created. The next error happened because coursecd is on two tables - I have to specify which one. I brought up line 7 and made the change. This time when I used the /, the view was created.

  12. Code after the corrections were made The ; shows the code for the current SQL command. SQL> ; 1 CREATE VIEW crsinfoA 2 AS 3 SELECT student00.studentidno, name, stucourse00.coursecd, coursename, grade 4 FROM student00, stucourse00, course00 5 WHERE student00.studentidno = stucourse00.studentidno 6 AND stucourse00.coursecd = course00.coursecd 7* AND SUBSTR(stucourse00.coursecd,1,3) = 'CIS' Note the * after the current line. Once the view has been created, I can see the results by doing the select from the view. Remember the information comes from three tables. SQL> SELECT * FROM crsinfoA; STUD NAME COURS COURSENAME GR ---- -------------------- ----- ------------------------------ -- 1111 Stephen Daniels CIS11 Intro to Computer Info Systems A- 1111 Stephen Daniels CIS44 Internet User/Developer A 1111 Stephen Daniels CIS50 Oracle and SQL A+ 1212 Jennifer Ames CIS44 Internet User/Developer A 2222 Carl Hersey CIS44 Internet User/Developer A 2345 Mary Stanton CIS50 Oracle and SQL I 3333 John Richards CIS44 Internet User/Developer B 3333 John Richards CIS44 Internet User/Developer B 3333 John Richards CIS56 Visual Basic A- 3333 John Richards CIS50 Oracle and SQL B+ 10 rows selected.

  13. View This SELECT takes only those records with some kind of A as the grade from the crsinfoA VIEW. SQL> SELECT studentidno, name, coursecd, coursename, grade 2 FROM crsinfoA 3 WHERE grade IN ('A+','A','A-'); STUD NAME COURS COURSENAME GR ---- -------------------- ----- ------------------------------ -- 1111 Stephen Daniels CIS11 Intro to Computer Info Systems A- 1111 Stephen Daniels CIS44 Internet User/Developer A 1111 Stephen Daniels CIS50 Oracle and SQL A+ 1212 Jennifer Ames CIS44 Internet User/Developer A 2222 Carl Hersey CIS44 Internet User/Developer A 3333 John Richards CIS56 Visual Basic A- 6 rows selected. This is the description of the crsinfoA VIEW. Note that the names used in the select above are the names defined in the view. SQL> DESC crsinfoA; Name Null? Type ------------------------------- -------- ---- STUDENTIDNO VARCHAR2(4) NAME VARCHAR2(20) COURSECD VARCHAR2(5) COURSENAME VARCHAR2(30) GRADE VARCHAR2(2)

  14. View SQL> CREATE VIEW first_pay_2000 (idno, name, jobcd, sal, bonus) 2 AS 3 SELECT pay_id, name, jobcode, salary, bonus 4 FROM first_pay 5 WHERE bonus = 2000; View created. SQL> DESC first_pay_2000; Name Null? Type ------------------------------- -------- ---- IDNO VARCHAR2(4) NAME VARCHAR2(20) JOBCD CHAR(2) SAL NUMBER(9,2) BONUS NUMBER(5) SQL> SELECT * FROM first_pay_2000; IDNO NAME JO SAL BONUS ---- -------------------- -- --------- --------- 4444 Stephen York CM 42000 2000 5555 Richard Jones CI 50000 2000 6666 Joanne Brown IN 48000 2000 8888 Paula Adams IN 45000 2000 In this view I am using unique names for some of the columns/fields. Notice that the description of the view contains these names as does the SELECT.

  15. Summary View SQL> CREATE VIEW jobview (jobcode, countjob, sumsal, sumbonus) AS 2 SELECT jobcode, COUNT(jobcode), SUM(salary), SUM(bonus) 3 FROM first_pay 4 GROUP BY jobcode; View created. SQL> DESC jobview; Name Null? Type ------------------------------- -------- ---- JOBCODE CHAR(2) COUNTJOB NUMBER SUMSAL NUMBER SUMBONUS NUMBER SQL> SELECT * FROM jobview; JOCOUNTJOB SUMSALSUMBONUS -- --------- --------- --------- AP 1 25000 500 CI 3 140000 3000 CM 1 42000 2000 IN 3 133000 5500 This view contains totals by jobcode for the number of rows in each jobcode, the sum of the salary in each jobcode and the sum of the bonus in each jobcode. Original jobcode, salary and bonus data. SQL> SELECT jobcode, salary, bonus 2 FROM first_pay; JO SALARY BONUS -- --------- --------- CI 45000 1000 IN 40000 1500 AP 25000 500 CM 42000 2000 CI 50000 2000 IN 48000 2000 CI 45000 IN 45000 2000

More Related