Ch 7 Continued Substrings Returns substrings Format Substrstringvalue

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: ” Abc 1234 cdf 2345 etc. . Get the first and last part of the order Select substr (order_numb, 1, 3), substr (order_numb, 4, 4) From order;

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

Search for first “/” in p_code SQL> select p_code, instr(p_code, '/') from product ; • • • • P_CODE INSTR(P_CODE, '/') ------------11 QER/31 6 13 -Q 2/P 2 6 14 -Q 1/L 3 6 1546 -QQ 2 0 1558 -QW 1 0 2232/QTY 5 2232/QWE 5 2238/QPD 5 23109 -HB 0 23114 -AA 0 54778 -2 T 0 • • P_CODE INSTR(P_CODE, '/') ------------89 -WRE-Q 0 PVC 23 DRT 0 SM-18277 0 SW-23116 0 WR 3/TT 3 4 • 16 rows selected. ;

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

Rules FOR joining WHERE attribute 1 condition attribute 2 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

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

Types of joins (see page 294) • Outer join – Left – Right – Full Format: – Select From table 1 LEFT [outer] join table 2 ON Joint condition – Select from table 1 RIGHT join table 2 ON joincondition

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 on salesperson. sales_numb=. customer. salse_numb;

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

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

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 ? ? ? = ? ? ? ));

INDEX (p 181. . B&F) An index creates a separate file, that has two columns • 1 st col contains the value and • 2 nd 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.

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;

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

GRANT/REVOKE Privileges: System 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

Student XYZ (ordb 022)grants access to student YYZ (acct: ordb 021) • GRANT SELECT, UPDATE ON STUDENT TO ORDB 021; This will allow ORDB 021 TO ACCESS STUDENT table of ORDB 022. Privilege is granted by ORDB 022 from his/her account. • However, when ORDB 021 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?

Problem is table is not on user ordb 021 account Must qualify it like in the trial exercise. Easier to create synonyms Create synonym STU FOR ORDB 022. STUDENT;

Revoke • REVOKE object privileges ON object FROM user; REVOKE select on STUDENT from ORDB 021; • Will take select privileges from ORDB 021 related to table STUDENT of ORDB 222

Procedural 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

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

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; /

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; /

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

Format: (SEE PAGE 330 -333) Create or replace 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;

Ch 9: DBD

SDLC Plan Analysis Design Implement maintenance

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)

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

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

design • Top down vs bottom up • Centralized vs decentralized