1 / 32

Ch 7 Continued

Ch 7 Continued. Substrings. Returns substrings Format: Substr(stringvalue, m, n) Where m is the starting value and n is the length of characters (count). Assume orders have the format:”. Abc1234 cdf2345 etc.. Get the first and last part of the order

torie
Download Presentation

Ch 7 Continued

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. Ch 7 Continued

  2. Substrings Returns substrings Format: Substr(stringvalue, m, n) Where m is the starting value and n is the length of characters (count)

  3. Assume orders have the format:” Abc1234 cdf2345etc.. Get the first and last part of the order Select substr (order_numb, 1,3),substr (order_numb,4,4) From order;

  4. INSTR function Allows searching for a string of characters, gives the position of the string but does Not cut off anything Format: Instr(string, set, start,occurrence) Start is the start of the search set in the string Occurrence is the position of occurrence that you want to search

  5. Search for first “/” in p_codeSQL> select p_code, instr(p_code,'/') from product;; • P_CODE INSTR(P_CODE,'/') • -------- ----------------- • 11QER/31 6 • 13-Q2/P2 6 • 14-Q1/L3 6 • 1546-QQ2 0 • 1558-QW1 0 • 2232/QTY 5 • 2232/QWE 5 • 2238/QPD 5 • 23109-HB 0 • 23114-AA 0 • 54778-2T 0 • P_CODE INSTR(P_CODE,'/') • -------- ----------------- • 89-WRE-Q 0 • PVC23DRT 0 • SM-18277 0 • SW-23116 0 • WR3/TT3 4 • 16 rows selected.

  6. JOINING TABLES when information needed is in more than one table, we need to join tables; WHERE clause in the select SQL statement creates a join. Note some queries can also be answered using sub query

  7. Rules FOR joining WHERE attribute1 condition attribute2 Ex: where employee.ssn=student.ssn Value(s) from one table are matched value(s) from other tables all matching values are attached allows joining of tables based on common attribute domains without the WHERE clause it will produce a Cartesian product also

  8. Give the names of salesperson and their customers in maryland SQL>Select cust_name, Sales_name from Customer C, salesperson S where c.sales_numb= s. sales_numb’ And Upper(c.cust_st) =‘MD’; C & S are aliases for tables Customer and Salesperson respectively

  9. Types of joins (see page 294) • Outer join • Left • Right • Full Format: • Select From table1 LEFT [outer] join table 2 ON Joint condition • Select from table1 RIGHT join table2 ON join-condition

  10. Give the customer order information of all customers also include customers that do not have orders Select * From customer left join order on customer.cust_numb=order.cust_numb; Give the salesperson’s information of all that have customers also include salesperson’s that do not have orders Select * From customer right join salesperson onsalesperson.sales_numb=.customer.salse_numb;

  11. Correlated subqueries • It executes once for each row in the outer query • Like a nested loop Normal subquery For X = 1 TO 2 PRINT “X = “X END

  12. Normal sub query For X = 1 TO 2 PRINT “X = “X END CORRELATED SUB QUERY: FOR X = 1 TO 2 FOR Y = 1 TO 3 PRINT “X= ‘, “Y= ‘Y END END

  13. Get the names of salespeople that have earned more commission than average commission of all salespeople Select sales_name From salesperson Where commission >(select avg (commission) from salesperson); Get the names of salespeople that have earned more commission than average commission of all salespeople from that state Select sales_name From salesperson sa Where commission > (select avg (commission) from Salesperson where ????? = ?????));

  14. INDEX (p 181..B&F) An index creates a separate file, that has two columns • 1st col contains the value and • 2nd col contain primary key or record# index are used to optimize query access • unique vs non-unique • take space • automatically updated every time some changes are made to affected data.

  15. Format: • CREATE [UNIQUE] INDEX index-name ON table-name(col name) Ex: Create index sales_name_index on slaesperson (sales_name) • DROP INDEX index-name • Drop index sales_name_index;

  16. Security two ways to control access internally: • VIEWS • GRANT • VIEWS.. already discussed (session 4)

  17. GRANT/REVOKE Privileges: System User User Privileges: • A user can grant privileges to anything that is in their account or they own Format: • GRANT object privilege [cols,] ON object to user [WITH GRANT OPTION]; • Table name MUST be preceded by the owner's user name

  18. Student XYZ (ordb022)grants access to student YYZ (acct: ordb021) • GRANT SELECT, UPDATE ON STUDENT TO ORDB021; This will allow ORDB021 TO ACCESS STUDENT table of ORDB022. Privilege is granted by ORDB022 from his/her account. • However, when ORDB021 attempts to access it from his/her account as: • SELECT * FROM STUDENT; (assuming no STUDENT table in his/her account) will give an error message. • Why?

  19. Problem is table is not on user ordb021 account Must qualify it like in the trial exercise. Easier to create synonyms Create synonym STU FOR ORDB022.STUDENT;

  20. Revoke • REVOKE object privileges ON object FROM user; REVOKE select on STUDENT from ORDB021; • Will take select privileges from ORDB021 related to table STUDENT of ORDB222

  21. Procedural SQL SQL does NOT support IF – THEN –ELSE or DO WHILE – END Use persistent Stored module (PSM) • A block of code that has sql and extension • This can be shared by multiple users • Like a module

  22. ORACLE implementation Through PL/SQL Can be invoked by users Format: Declare <dec section> BEGIN <execuable section> END; Declare: defines/initialize variables Executable commands: IF_THEN_ELSE, LOOPS

  23. example: DECLARE pi constant NUMBER (9,7) :=3.1415926;//declares a variable pi of constant value of size (9,7) radius INTEGER (5); //variable radius of integer type area NUMBER (14,2); BEGIN radius :=3 //assigns an initial value to variable radius area = :=pi*power(radius,2); Insert into AREAS values (radius, area);//inserts value in table AREAS END; /

  24. Cursor can be used for multiple entries (get data from another table)BF(ch 21) & An Introduction to PL/SQL by Loney & koch) DECLARE pi constant NUMBER (9,7) :=3.1415926;//declares a variable pi of area NUMBER (14,2); Cursor rad_cursor is select * from RADIUS_VAL; Rad_val rad_cursor%ROWTYPE; BEGIN Open rad_cursor; Fetch rad_cursor into rad-valradius :=3 area = :=pi*power(radval.radius,2); Insert into AREAS values (radval.radius, area); Close rad_cursor; END; /

  25. triggers Like an alarm clock It is automatically invoked by an event, i.e. Add Delete Modify Can be used to enforce rules that can not be enforced in design, generate derived columns

  26. Format:(SEE PAGE 330-333) Create or replace trigger trigger_name [before/after] [delete/insert/update of col_name] on table_name [for each rwo] [declare] [var name data type etc..] BEGIN PL/SQL INTERFACE END;

  27. Ch 9: DBD

  28. SDLC Plan Analysis Design Implement maintenance

  29. DBLC (page 365) • Initial study Define objectives/scope • Database design.. Data Analysis & Requirements: Develop conceptual model (ERD)..DBMS independent Create logical (translate ERD into tables,views etc)..DBMS dependent Physical design (storage structure, access method, optimization..) Hardware dependent (page 383)

  30. Implementation Create DBMS Load data Security Back ups and recovery standards • Test & evaluation robust • Operation Is it doing what it is supposed to do?

  31. Maintenance • Audits • Back ups • disaster planning • Usage statistics

  32. design • Top down vs bottom up • Centralized vs decentralized

More Related