1 / 53

Oracle Structured Query Language Examples

Oracle Structured Query Language Examples. Lloyd D. Brooks, Professor Management Information Systems MIS 3775. Basic SQL Commands. SELECT FROM WHERE Appendix. SQL Example. SELECT First, Last FROM Student WHERE Num = ’23’; Num Last First Phone

tass
Download Presentation

Oracle Structured Query Language Examples

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. Oracle Structured Query Language Examples Lloyd D. Brooks, Professor Management Information Systems MIS 3775

  2. Basic SQL Commands • SELECT • FROM • WHERE Appendix

  3. SQL Example • SELECT First, Last • FROM Student • WHERE Num = ’23’; • Num Last First Phone • 18 Doe John 2382 • 23 Brown Betty 4722 • 27 Carlson Daniel 4920 Appendix

  4. Creating a table • Create Table Inventory • (PackID Varchar2(4), • PackName Varchar2(20), • PackVer Number(3,2), • PackType Varchar2(15), • PackCost Number(5,2)); • Note: Mark there is no comma after the last field/Column description Appendix

  5. Text Data Defined • Note: Oracle defines text data as follows: • Packid Varchar2(4) Appendix

  6. Decimal Data Defined • Note: Oracle defines decimal data as follows: • Salary Number(8,2) Appendix

  7. Null Constraints • Note: Null constraints can be entered as follows: • Quantity INTEGER NOT NULL • Order_Date DATE NOT NULL Appendix

  8. Primary Key Definition Note: The primary key can be defined at table definition end as follows: • CONSTRAINT Inventory_PK PRIMARY KEY (PackID) • Notes: Inventory_PK is a arbitrary name assigned to the constraint. PackID is the name of the primary key field in the table. • Primary key can also be created as: • PackID varchar2[10] primary key Appendix

  9. Boolean Operators and Algebra • AND OR NOT • * Multiply • / Divide • + Add • - Subtract Appendix

  10. SQL Comparison Operators • = Equal to • > Greater than • >= Greater than or equal to • < Less than • <= Less than or equal to • <> Not equal to • != Not equal to Appendix

  11. Data Type Samples in SQL • Order_Date DATE • Last_Name VARCHAR2(18) • Quantity INTEGER • Amount Number(7,2) Appendix

  12. Data Query – No Restrictions • SELECT PackID, PackName, PackCost • FROM Inventory; Appendix

  13. Data Query – Wild Card • SELECT * • FROM Inventory; Appendix

  14. Data Query – Value Condition • SELECT EMP_Name • FROM Employee • WHERE Salary > 50000; Appendix

  15. Data Query - Not Equal • SELECT Compid, Mfgname • FROM Computer • WHERE Mfgname <> ‘Bantam’; • Note: Alternative for the criteria command: • WHERE Mfgname != ‘Bantam’ • Note : SQL data are case sensitive. Appendix

  16. Data Query – Text Condition • SELECT PackID, PackName • FROM Inventory • WHERE PackType = ‘Database’; Appendix

  17. Data Query – Compound Condition • SELECT PackName • FROM Inventory • WHERE PackType = ‘Database’ • and PackCost > 400; Appendix

  18. Data Query – NOT Command • SELECT PackName • FROM Package • WHERE NOT (PackType = ‘Database’); Appendix

  19. Sorting Records – Single Field • SELECT * • FROM Employee • ORDER BY Last_Name; • Note: The default order is ascending. Add DESC after the attribute to change the order to descending. Appendix

  20. Sorting Records – Compound Fields • SELECT * • FROM Employee ORDER BY Last_Name, First_Name; Note: Indicate the major sort field first and then the minor sort field next with a comma between the fields. Appendix

  21. Built-In Functions - Count • SELECT COUNT(PackID) • FROM Inventory; • Note: Oracle has numerous built-in functions for a variety of statistical, financial, and other applications. Appendix

  22. Count Function With a Condition • SELECT COUNT(Tagnum) • FROM PC • WHERE Location = ‘Home’; • Note: COUNT is a function. Do not space after the function name. Appendix

  23. Count and Sum Functions Combined • SELECT COUNT(PackID), Sum(PackCost) • FROM Package; • Note: Count is a function that determines how many. Sum is a function that determines how much. Appendix

  24. Average Function • SELECT AVG(Packcost) • FROM Package • WHERE Packtype = ‘Database’; • Note: AVG will initiate the function to compute the average for the values for the field indicated in parenthesis. Appendix

  25. Grouping by a Control Field • SELECT TagNum, Sum(SoftCost) • FROM Software • GROUP BY TagNum • ORDER BY TagNum; Appendix

  26. Grouping With a Condition • SELECT TagNum, Sum(SoftCost) • FROM Software • GROUP BY TagNum • HAVING Sum(SoftCost) > 600 • ORDER BY TagNum; • Note: The Having command takes the place of the Where command for groups. Appendix

  27. Joining Tables • SELECT Software.Packid, Package.Packname, Package.Packver, Software.Tagnum, Software.Instdate, Software.Softcost • FROM Software, Package • WHERE Software.Packid = Package.Packid; • Note: The WHERE command determines if a value exists in both tables and is used when tables are being joined. Appendix

  28. Alias in Oracle • SELECT pt_fname “First Name”, pt_lname “Last Name”, ptdob “BirthDate” • FROM Patient; • Note: Alias must include double quotes • Note: Omission of quotes will result in an all uppercase heading • Note: Quotes required, regardless, for headings requiring a space Appendix

  29. Null Value Criteria in Oracle • SELECT Doc_ID FROM Doctor WHERE Annual_Bonus IS NULL; • Note: Use “IS NULL” and not “=NULL” in the criteria expression. Appendix

  30. Like Command • SELECT * FROM Billing where Addr LIKE ‘%N. Allen%’; • Note: LIKE should be used for criteria comparisons whenever a wild card is used within the criteria. Appendix

  31. Table Name Designation in SQL • Note: When the same attribute is included in two or more tables, it is necessary to indicate both the table name and the attribute name • WHERE Adviser.AdvID = Student.AdvID • Note: The period separates the table and attribute names. Note that AdvID appears in both tables in this example. Appendix

  32. Updating Data in a Table • UPDATE Inventory • SET PackName = ‘Manta’ • WHERE PackID = ‘DB33’; • Note: Revise name for ID DB33 Package Appendix

  33. Adding a Record to a Table • INSERT INTO Employee • VALUES (‘402’, ‘Sanders, Robert’, ‘2056’); • Note: The order of values must match the table structure. If only data for certain fields are to be entered, list the fields in parenthesis between the table name and the word VALUES. Appendix

  34. Updating Selected Records • UPDATE Package • SET Packcost = Packcost + 10 • WHERE Packtype = ‘Word Processing’; • Note: Records that do not meet the criteria will not be included in the update process. Appendix

  35. SQL Wildcard Examples in Oracle • % Takes the place of all characters before or after it. • ‘%ville’ All cities ending in ville • ‘C%’ All last names beginning with C • _ (underline) takes the place of 1 character • ‘T_M’ Will match any 3-character values beginning with a “T” and ending with a” M” Appendix

  36. Date Comparisons and Like Values • WHERE Order_Date > ’24-OCT-99’ • Note: This will be a criteria match for order dates that occurred later than 10/24/99. Notice that the date has an apostrophe before and after it. • WHERE Last_Name LIKE ‘%son’ Appendix

  37. Deleting a Record • DELETE FROM Employee • WHERE EmpPhone = ‘8716’; • Note: Deletes record(s) matching the • condition. Values must be • unique or multiple records will • be deleted. Appendix

  38. Column Deletion • ALTER TABLE Employee • DROP Address; Appendix

  39. Column Width Revision • ALTER TABLE Employee • CHANGE Column Last_Name • TO VarChar2(22); Appendix

  40. Column Addition • ALTER TABLE Employee • ADD Address Varchar2(20); Appendix

  41. Table Deletion • DROP TABLE Employee; Appendix

  42. View Creation • CREATE VIEW Brown • AS SELECT EmpID, EmpName • FROM Employee; • Note: This view will permit the user to view data from the EmpID and EmpName columns in the Employee table. Appendix

  43. View Deletion • DROP VIEW Brown; Appendix

  44. Updating All Records – No Condition • UPDATE Employee • SET Salary = Salary * 1.05; Appendix

  45. Averaging With a Condition • SELECT AVG(Salary) • FROM Employee • WHERE Dept = ‘Accounting’; Appendix

  46. Averaging With Grouping • SELECT DeptName, Avg(Salary) • FROM Employee • GROUP BY DeptName • ORDER BY DeptName; Appendix

  47. Major SQL System Commands • Saving a script • Save a:\filename.ext • Saving a session to a file • Spool a:\filename.txt • Running a script stored as a .txt file • @a:\filename.txt • Exit from session • Exit • Access program text editor • Edit Appendix

  48. Null Values in a Field • UPDATE PC • SET EmpNum to Null • WHERE TagNum = ‘37691’; Appendix

  49. Commands – Oracle • DESCRIBE Student; • Note: Provides a listing of fields and attribute types for the Student table. • RUN • Note: Executes a program. • SELECT * FROM TABS; • Note: Gets a listing of tables in the database • SAVE C:\Filename.ext • Note: Saves a program Appendix

  50. Comments - Oracle • - - • Note: Place at the beginning of the comment line for one-line comments • /* */ • Note: Place multi-line comment between these notations. Appendix

More Related