1 / 121

ACCEL FRONTLINE LTD

ACCEL FRONTLINE LTD. Wishes & Welcomes all its participants . Features of DBMS. Ø      Simplicity Ø      Query Language is non-procedural Ø      Physical impln storage depends on OS Ø      Recovery depends on OS. Features Of Good DBMS.

craig
Download Presentation

ACCEL FRONTLINE LTD

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. ACCEL FRONTLINE LTD Wishes & Welcomes all its participants

  2. Features of DBMS • Ø     Simplicity • Ø     Query Language is non-procedural • Ø     Physical impln storage depends on OS • Ø     Recovery depends on OS

  3. Features Of Good DBMS • Ø  The facility of retrieving and manipulating data irrespective of no of tables used. • Ø  The language should be easy • Ø  Should be powerful enough to have minimal code and so less maintenance • Ø  Resources like memory and secondary storages should be handled by DBMS • Ø  It should have Multi-user support

  4. Concept of Relation • ØRelation is nothing but Set • Ø   A Database is said to be relational if data is stored in the form of relation

  5. Advantages of RDBMS • Ø  Information representation is simple in table form • Ø  Non-Procedural language is easily operated • Ø  Set Operations are used for data operations • Ø  Data from multiple tables is retrieved in simple way by forming Cartesian product

  6. Features Of Oracle • ØAll operations are done using non-procedural language • Ø   Multi-user Environment • Ø   Security • Ø   Concurrent Operations • Ø   Query Optimization • Ø   Transparent Indexing, Backup and Recovery

  7. Components Of Oracle • Ø     SQL PLUS • Ø     PL/SQL • Ø     SQL FORMS • Ø     SQL REPORT WRITER

  8. Introduction to SQL • ØThe language used to access data within Oracle databases • Ø   Non-Procedural Language. • Ø   Unified Language • Ø   Common Language for all relational databases

  9. Sql is made of three sub-languages such as Data Definition Language. Data Manipulation Language. Data Control Language.

  10. DEFINITIONS • Data Definition Language ( DDL) Consists of commands to create the objects, such as tables, views, Indexes etc. Syntax : CREATE TABLE <table-name> (<column1 –name> datatype, <column2_name> datatype, . . . <column-name> dataype);

  11. Data Definition Language • Example : CREATE TABLE General_Account_Master_Table ( acct_name varchar2(30) acid varchar2(11), foracid varchar2(16), clr_bal_amt number(17,2), schm_code varchar2(5) acct_opn_date date ) ;

  12. Definitions • Data Manipulation Language ( DML ) Used for query, insertion ,deletion and updating of information stored in the database. Syntax : INSERT INTO <table-name> VALUES ( ‘value1’,’value2’,’value3’,value4,’value5’,’value6’ ) ;

  13. Data Manipulation Language • Example: INSERT INSERT INTO General_Account_Master_Table VALUES (‘RAJKUMAR’,‘DL123’,’SB123’, 10000,’SBGEN’,’12-02-2002’);  INSERT INTO General_Account_Master_Table VALUES(‘HINDUSTAN’,‘DL124’,’SB124’, 11000,’CDGEN’,’12-02-2002’ );

  14. Data Manipulation Language • Example: UPDATE Update General_Account_Master_Table set clr_bal_amt = 20000 where ltrim(foracid)=’SB123’;

  15. Data Manipulation Language • Example : DELETE  • Syntax: DELETE <table-name>WHERE <conditions> Example : • DELETE general_account_master_table WHERE acid =’DL123’

  16. DATA CONTROL LANGUAGE • ØCOMMIT Will commit the changes that has been made to the table. Considering the above eg for UPDATE ,when the statement is committed the clr_bal_amt for that foracid will be Rs 20000. • Ø  ROLLBACK Will rollback all the changes that has been made to the table,ie in the above case the value of clr_bal_Amt will be Rs 10000

  17. Querying Database Tables The select statement instructs the database to retrieve information from a table   • Syntax: SELECT < column-list > FROM <table-name> WHERE <condition> • Example: SELECT foracid from General_Account_Master_Table WHERE acid =’DL123’;

  18. Conditional Retrieval of Rows • The WHERE clause is used along with the SELECT statement to specify the condition, based on which the rows will be extracted from a table with SELECT. • Operators used to specify the conditions:

  19. Relational operators: • v= :Equal to • v    > : Greater than • v< : Less than • v    >= : Greater than or equal to • v    <= : Less than or equal to • v    <>, != : Not equal to

  20. Examples: SELECT foracid , acid FROM General_Account_Master_Table WHERE clr_bal_amt = 10000; SELECT foracid , acid FROM General_Account_Master_Table WHERE clr_bal_amt <= 10000;

  21. Logical operators • Ø     AND :Logical AND • Ø     OR : Logical OR • Ø     NOT : Logical NOT

  22. Examples- Logical Operators • SELECT foracid FROM General_Account_Master_Table WHERE acid =’DL123’OR acid = ‘DL124’ ; • SELECT foracid FROM General_Account_Master_Table WHERE acid =’DL123’ AND clr_bal_amt >= 10000;

  23. Special Operators • Ø     IN : Checking a value in a set • Ø     BETWEEN : checking a value within a range • Ø     LIKE : Matching a patter from a column

  24. Examples – Special Operators SELECT foracid , cust_id , acct_name from General_Account_Master_Table WHERE Schm_code IN (‘SBGEN’,’CDGEN’ ); SELECT acct_opn_date , acid FROM General_Account_Master_Table WHERE acct_name LIKE ‘RAJ%’;

  25. Special operators….., Union Operator Eliminates duplicate rows Example : Select acid, tran_amt, tran_date, tran_particulars, tran_id from dtd Where acid = (select acid from gam where foracid = ‘&acid’) Union Select acid, tran_amt, tran_date, tran_particular, tran_id from ctd where acid = (Select acid from gam where foracid = ‘&acid’);

  26. Special Operator.., Union All It does not eliminates duplicate rows . Example : Select acid, Tran_amt, Tran_date, Tran_particular, Tran_id from dtd where acid = (select acid from gam where foracid = ‘&acid’) UNION ALL select acid, Tran_amt ,Tran_date, Tran_particular, Tran_id from ctd where acid = (select acid from gam where foracid = ‘&acid’);

  27. Special Operator…, Intersect Operator This returns only those rows returned by both Queries Example : Select fxd_crncy_code,var_crncy_code from rtl intersect select fxd_crncy_code,var_crncy_code from rth;

  28. Special Operator…, Minus Operator This returns only those rows returned by the first query but not in the second. Example : Select cust_id from cmg minus select cust_id from gam;

  29. SQL Plus functions • Dual Table : It is created along with the data dictionary .It is schema of the user sys but accessible to all the users. Useful for computing a constant expression with the select statement .Dual has only one row ,the constant is only returned once. Example : Select sysdate from dual;

  30. Pseudo Columns • It is a column that yields a value when selected but which is not an actual column of a table . Example SYSDATE. Some Oracle pseudo columns: Null : This is used to assign in place of missing values. Rownum : Returns the sequence number in which a row was returned when selected from a table. Sysdate: Returns current date and time

  31. Number functions.., 1)ABS: It returns the absolute value of N Select abs (-15) “ ABSOLUTE “ from dual;   Result : 15 2) CEIL: It returns smallest integer greater that or equal to N Select ceil (15.1) “ CEILING “ from dual;   Result : 16  3) FLOOR: Returns largest integer equal to or less than N Select floor (15.9) “ FLOOR “ from dual; Result is 15

  32. Numeric Functions.., 4) MOD: • Returns remainder of m divided by n • Select mod (12,3) from dual; • Result is 0 5) ROUND: • Round (n [, m]) • Returns n rounded to m places right of the decimal point m can be negative to round off digits left to the decimal point .m must be an integer Select round (55.1234,1) from dual; • Result is 55.1 • Select round (56.1234, -2) from dual; • Result: 100 • Select round (45.12434, -2) from dual • Result: 0

  33. Numeric Functions.., 6) INITCAP: Select initcap (‘ram’) from dual; Result: Ram 7)      Lower: Select LOWER (‘ M/S HINDUSTAN LEVER LTD ‘) from dual; Result: m/s Hindustan lever ltd 2) 8)  LPAD: Left padded to length n with the sequence of characters.   Select lpad (‘PAGE 1’, 15,’*’) from dual; Result: *********PAGE 1

  34. Numeric Functions.., 9)     RPAD: Right paded to length n with characters Select rpad (‘PAGE 1’, 15,’*’) from dual; • Result: PAGE 1********* • 10)      LTRIM: Select ltrim (‘1234ALPHA’,’12’) from dual;   Result: 34ALPHA • 11)    RTRIM: Select rtrim (‘BROWINGXYXY’,’XY’) from dual; Result: BROWING

  35. Numeric functions.., • 12)      SUBSTR Select substr (‘ABCDEFG’, 3,4) from dual; Result: CDEF Select substr (‘ABCDEFG’, -5) from dual; Result: CDEFG • 13)     UPPER Select upper (‘Large’) from dual; Result: LARGE

  36. AGGREGATE FUNCTIONS Aggregate functions are used to produce summary data using tables ØAVG(column-name) –Returns the average of the values in the column ØSUM(column-name) – Returns the sum of values specified in the column ØMIN(column-name) – Returns the minimum of value specified in the column ØMAX(column-name) - Returns the minimum of value specified in the column  ØØCOUNT(*) - Returns the total no of records specified in the table

  37. Examples-Aggregate Functions SELECT count(*),schm_code FROM general_account_master_table GROUP BY schm_code; SELECT Avg(clr_bal_amt) FROM general_account_master_table WHERE schm_code =’SBGEN’;

  38. Examples – Aggregate Functions SELECT Max(clr_bal_amt),schm_code FROM general_account_master_table GROUP BY schm_code ORDER BY schm_code ; SELECT SUM(clr_bal_amt) FROM general_account_master_table WHERE schm_code =’SBGEN’;

  39. JOINS • SQL server provides a method of retrieving data from more than one table using joins . A join can be defined as an operation that includes retrieval of   data from more than one table at a time . • The various types of joins are Ø     Equii join Ø     Self join Ø     Outer join

  40. Example : Equi Join • Equi Join : • It selects data from both the tables having a common column. • It selects data based on the equality joining condition. Example : • Select gam.foracid,cmg.cust_id ,cmg.cust_name from cmg,gam where • Gam.cust_id = cmg.cust_id

  41. Self join: In a self-join, rows from a table are correlated to rows in the same table. Since the same table is used twice for comparison, an alias name is given which differentiates the two copies of the table. Select c1.cust_id,c1.cust_name,c1.introd_cust_id,c2.cust_name From cmg c1,cmg c2 where C1.introd_cust_id = c2.cust_id

  42. Outer join: • An outer join displays data from one table and only matching join data from the second table .In the rows selected for non-matching data NULL will be displayed in the non-matching columns of the second table.  • Select cmg.cust_name,gam.foracid from gam ,cmg where • Cmg.cust_id = gam.cust_id(+)

  43. NESTED QUERIES SQL has an ability to nest queries within one another . A sub query is a SELECT statement that is nested within another SELECT statement and which returns intermediate results . SQL first evaluates the inner query (or sub query) within the WHERE clause. The inner query generates values that are tested in the product of the outer query, determining when it will be true. The return value of inner query is then substituted in the condition of the outer query.

  44. Advantages ØSub queries allows a developer to build powerful commands out of simple ones. Ø     The nested sub query is very useful when you need to select rows from a table with a condition that depends on the data in the table itself.

  45. Example • SELECT gsp.schm_type , gam.acct_name , sanct_lim sanctioned_limit, • (adhoc_lim+clean_adhoc_lim+emer_advn+clean_emer_advn+ • single_tran_lim+clean_single_tran_lim) total_tod_allowed, • (sanct_lim+adhoc_lim+clean_adhoc_lim+emer_advn+clean_emer_advn+single_tran_lim+clean_single_tran_lim) aggregate_limit, • drwng_power advance_value, (clr_bal_amt+un_clr_bal_amt) balance • FROM gam,gsp • WHERE acct_ownership != 'O' and • gam.del_flg != 'Y' and • gam.schm_code = gsp.schm_code and • ((gsp.schm_type = 'CCA' and • (gam.acct_rpt_code = '020100' OR • gam.acct_rpt_code = 2000')) OR • (gsp.schm_type = 'CAA' and • (gam.acct_rpt_code = '425100' or gam.acct_rpt_code = '425200' or • gam.acct_rpt_code = '425300') and (clr_bal_amt+un_clr_bal_amt) < 0)) • ORDER BY schm_type;

  46. USING AGGREGATE FUNCTIONS IN SUB QUERIES SELECT foracid , clr_bal_amt , acid , schm_code FROM gam   WHERE Clr_bal_amt > (SELECT Avg (clr_bal_amt) FROM gam WHERE Schm_code IN (‘SBGEN’,’CDGEN’’));

  47. CORRELATED SUB QUERIES • A correlated sub query can be defined as a query that depends on the outer query for its evaluation .In a correlated sub query, the WHERE clause references a table in the FROM clause of the outer query .In the case of correlated sub queries, the inner query is evaluated for each row of the table specified in the outer query

  48. Examples • SELECT count(distinct lht.acid),Sum(lht.sanct_lim),sum(tran_date_bal) • FROM lht, eab , gam , gac , ach • WHERE lht.acid=eab.acid and • lht.acid = gam.acid • and gam.acid=gac.acid • and gam.acid=ach.b2k_id • and schm_type='laa' • and sector_code='prior' Contd . . .

  49. Example – Contd… • and sub_sector_code='agrcr' • and main_classification_user='1' • and sub_classification_user='01' • and type_of_advn='terml' • and applicable_date=(select max(applicable_date) from lht • WHERE l.acid=lht.acid • and applicable_date<='&1') • and eod_date<='&1' • and end_eod_date>='&1';

  50. PL/SQL • ØSupport for SQL • Ø     Block Structure • Ø     Higher Productivity • Ø     Better Performance • Ø     Integration • Ø     Control structures • Ø     Modularity & Portability

More Related