tag:blogger.com,1999:blog-9646923288674044022024-03-08T16:16:38.147+08:00oracle2uOracle Information Sharing To YouUnknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-964692328867404402.post-62929118982035072552009-08-04T23:57:00.001+08:002009-08-04T23:57:22.344+08:00Oracle – Retrieve Data Using SQL SELECT Statement<p>To retrieve the data from Oracle database, you need to use the SQL SELECT statement. There are 3 basic steps to retrieve the data using SQL SELECT statement.</p> <ul> <li>Choose the columns</li> <li>Choose the rows</li> <li>Join the tables</li> </ul> <p>Below is the sample basic SQL statement using <strong>SELECT</strong> command:-</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400"> SELECT * FROM table;</td> </tr> </tbody></table> <p><strong>SELECT</strong> and <strong>FROM</strong> are the keywords. (*) mean select all columns.</p> <p>If you need to retrieve selected columns from a table, then as below:-</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1, column2, … column10 FROM table;</td> </tr> </tbody></table> <p><em>(column1, column2, …)</em> is a clause for a SQL statement. If you are not sure the column names for a specific table, then you can use the <strong>DESCRIBE</strong> command to display the structure.</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">DESC tablename;</td> </tr> </tbody></table> <p>You need to take note few things to write a SQL statements:-</p> <ul> <li>Not a case-sensitive.</li> <li>Can be more than one lines.</li> <li>Keywords cannot be abbreviated or split across lines.</li> <li>Clauses can put in separate lines.</li> <li>SQL statement end with <strong>semicolons</strong> (<strong>;</strong>) to terminate it before write another SQL statement. This is compulsory to use in SQL plus, and after (<strong>;</strong>) press ENTER key to run the command.</li> </ul> <p>You can use the arithmetic operators<em> (+, –, *, /) </em>in SQL statement.</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1, column2, column3 + 100 FROM table;</td> </tr> </tbody></table> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1, 50*column2+500 FROM table;</td> </tr> </tbody></table> <p><strong>To understand a <em>Null</em> value:-</strong></p> <p>If a row of a column without any data, mean the value is <em>null</em>. <em>Null</em> value is not same as zero or a space, <em>null</em> value is mean nothing at all. Zero is a number, and a space is a character. In order to prevent the <em>null</em> value for that column, then we can use the ‘NOT NULL’ and ‘PRIMARY KEY’ to ensure no <em>null</em> value.</p> <p>If you are using the arithmetic expression for the <em>null</em> value column, then your result is <em>null</em>.</p> <p><strong>To use Column Aliases:-</strong></p> <p>The purpose to use the Column Aliases in your SQL statement, is to rename a column heading to ease for understanding. </p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1 AS “First Name”, column2 “Last Name”, 50*column2+500 “Monthly Profit” FROM table; </td> </tr> </tbody></table> <p>‘AS’ is an optional keyword, but if you have spaces or special characters (i.e. # or $), or need to be case-sensitive for the alias, then you need to use the double quotation marks (“ “) in your SQL statement.</p> <p><strong>To use Concatenation Operator:-</strong></p> <p>You can join the columns data value into one line result using the concatenation operator (||) in your SQL statement.</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1||column2 AS “Full Name” FROM table;</td> </tr> </tbody></table> <p>If you need to put a space between two columns as your result, then use the (‘ ‘) as below:-</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1||’ ‘||column2 AS “Full Name” FROM table;</td> </tr> </tbody></table> <p><strong>To use Literal Character Strings:-</strong></p> <p>You need to add the number or characters or date to make it like a full sentence in your result, then you can use the single quotation mark (‘ ‘) in your SQL statement.</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1 || ‘ is the First Name of ‘ || column2 FROM table;</td> </tr> </tbody></table> <p><strong>To use Alternative Quote (q) Operator:-</strong></p> <p>If you need to use a quote (q) in your Literal character String, then you can refer below example:-</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT column1 || q'[ isn’t his First Name!]‘ || column2 FROM table;</td> </tr> </tbody></table> <p><strong>To remove the Duplicate Rows:-</strong></p> <p>When you retrieve more than one duplicate rows in your SQL SELECT statement, then you can use the DISTINCT command with your SQL SELECT statement to make sure display the unique records only.</p> <table border="1" cellspacing="0" cellpadding="2" width="400"><tbody> <tr> <td valign="top" width="400">SELECT DISTINCT column1, column2 FROM table;</td> </tr> </tbody></table> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-964692328867404402.post-82050779826652263792009-08-02T23:49:00.002+08:002009-08-03T18:29:13.377+08:00Oracle Provide A Flexible RDBMS<p>Oracle provide a flexible relational database management system (RDBMS) called <em>Oracle Database</em> and has below key features.</p> <ul> <li>Store and manage data</li> <li>Support relational structure and PL/SQL</li> <li>Ability to store and execute program units</li> <li>Support Java and XML</li> <li>Optimize technique to retrieve data</li> <li>Secure on accessing and using the database</li> <li>Locking mechanism to protect the data in consistent way</li> <li>Integrated, comprehensive and open methodology to manage the information</li> </ul> <p>Oracle server consists of Oracle database and Oracle server instance. A system global area (SGA) will allocate the memory to share the database information to users and Oracle backend processes start running when Oracle database is started, and whole integration is called Oracle Instance.</p> <p>Oracle server supports American National Standards Institute (ANSI) and International Standards Organization (ISO) standard SQL for relational database. SQL (structured query language) is the programming language used to communicate with the database server to access, control, manipulate, and define the data. </p> <p>A relational database contain one or more tables, and <em>table</em> is the basic storage structure of an RDBMS, and usually has the primary keys and foreign keys to link the multiple tables for data consistency. </p> <p>In Summary, the below SQL statements are commonly used to communicate with an RDMBS, Oracle database.</p> <table width="463" border="1" cellpadding="2" cellspacing="0"><tbody> <tr> <td width="107" valign="top"><strong>SQL Statements</strong></td> <td width="354" valign="top"><strong>Remarks</strong></td> </tr> <tr> <td width="107" valign="top">SELECT <br />INSERT <br />UPDATE <br />DELETE <br />MERGE</td> <td width="354" valign="top">This is called data manipulation language (DML). To use for data retrieving, put in new data, change the data value, remove the data, and sync the data.</td> </tr> <tr> <td width="107" valign="top">CREATE <br />ALTER <br />RENAME <br />TRUNCATE <br />COMMENT</td> <td width="354" valign="top">This is called data definition language (DDL). To setup, change, remove, and describe the data structures for the tables.</td> </tr> <tr> <td width="107" valign="top">GRANT <br />REVOKE</td> <td width="354" valign="top">This is called data control language (DCL). For administration used to control the access right for the database and structures.</td> </tr> <tr> <td width="107" valign="top">COMMIT <br />ROLLBACK <br />SAVEPOINT</td> <td width="354" valign="top">To be used together with DML statement to confirm or manage in group of logical transactions.</td> </tr> </tbody></table> <p><span style="color:#ffffff;">wvkctbxipg</span></p>Unknownnoreply@blogger.comtag:blogger.com,1999:blog-964692328867404402.post-90327980346788413982009-08-02T22:45:00.001+08:002009-08-02T22:45:18.749+08:00Oracle Server Support Relational and Object Relational Models<p>The Oracle server has capability to support an object relational database model as below and fully integrated with the relational model.</p> <ul> <li>Support object-oriented programming</li> <li>Support complex data types</li> <li>Support complex business objects</li> </ul> <p>With the Oracle architecture, it support multitier application design and improve the performance and functionality for data warehouse and OLTP application. It apply to client / server based application or web-based application.</p> <p><strong>Benefits for Data warehouse:-</strong></p> <ul> <li>Simultaneously happen for insert, update, delete activities.</li> <li>Database partitioning (usually by date)</li> <li>Database archiving</li> </ul> <p><strong>Benefits for OLTP (Online Transaction Processing) Application:-</strong></p> <ul> <li>Real-time data sharing</li> <li>Huge buffer cache transmitting</li> <li>Logical constraints integration</li> </ul> <p> </p> <p><u><strong>Example:- </strong><strong>Develop an Internet business application</strong></u></p> <ol> <li>A product to store and manage the data</li> <li>A platform can support real-time transaction with robust engine to support complicated business logic and multi-users concurrent transactions</li> <li>A product can integrate to application and database level for monitoring and diagnostic purpose</li> <li>A product can support huge data archiving for complex reporting and matrix generation</li> </ol> <p>With the Oracle Internet Platform, it includes everything that you needed for above requirements. It has the advance graphical user interface (GUI) to provide users for the development, deploying, testing, monitoring, migration, administration and many more as one.</p> <p>With the Oracle Developer Suite, it provides users to develop the forms and reports through data warehouse using SQL, PL/SQL, Java to write the store procedure, functions and packages.</p> Unknownnoreply@blogger.comtag:blogger.com,1999:blog-964692328867404402.post-34465478225888413192009-07-29T23:52:00.001+08:002009-07-29T23:52:34.311+08:00Oracle 10g Key Features and Products<p><strong>Oracle 10g release has three key grid-infrastructure products:-</strong></p> <ul> <li>Oracle Database 10g</li> <li>Oracle Application Server 10g</li> <li>Oracle Enterprise Manager 10g Grid Control</li> </ul> <p><strong>Oracle 10g key features:-</strong></p> <ul> <li>It is suitable from small business to enterprise business</li> <li>It provide scalability, reliable, robust, and in a secure architecture</li> <li>It has a single development model to ease the developers</li> <li>It allow the current users with common skill set to use this platform based on SQL, PL/SQL, Java and XML</li> <li>It provide a centralize management interface</li> <li>It contain the grid architecture to lower the cost of computing with high quality</li> </ul> <p> </p> <p><u><strong>Oracle Database 10g</strong></u></p> <p>Oracle Database 10g is a database used to store and manager the information using SQL. In Oracle Database 10g, it is not only manage the object relational data, it even can support the unstructured data like below and does not need to be in the database (i.e. stored in file system):-</p> <ul> <li>Multimedia format – mp3, mp4, video, graphics, and more</li> <li>Word or PowerPoint or Spreadsheet documents</li> <li>XML or HTML files</li> </ul> <p> </p> <p><u><strong>Oracle Application Server 10g</strong></u></p> <p>Oracle Application Service 10g is a platform for us to run the applications, even developed in J2EE. It is  a application server to cover many services that you need to use as below:-</p> <ul> <li>Business intelligence features</li> <li>Java platform functionality</li> <li>Web site or web portal capability</li> </ul> <p> </p> <p><strong><u>Oracle Enterprise Manager 10g Grid Control</u> </strong></p> <p>Oracle Enterprise Manager 10g Grid Control is a UI Interface for us to manage and automate administration jobs for all the Oracle related hardware nodes or applications or databases in a grid environment. With below two key functions that simplified for IT administrators.</p> <ul> <li>Software provisioning – With Grid Control, it automate installation, configuration, deploying, and even clone for the new set of application servers or databases if needed.</li> <li>Application service level monitoring – With Grid Control, the administrators can monitor all as one view rather than previous method to check the servers or application or database one by one.</li> </ul> Unknownnoreply@blogger.com