ULOG: Learning oracle programming language (Class-4)

aaarif (54)in #ulog • 6 years ago

Structured Query Language
Using SQL, you can communicate with the Oracle server. SQL has the following advantages:
• Efficient
• Easy to learn and use
• Functionally complete (With SQL, you can define, retrieve, and manipulate data in the tables.)

Oracle’s Relational Database Management System
Oracle provides a flexible RDBMS called Oracle Database 10g. Using its features, you can store and manage data with all the advantages of a relational structure plus PL/SQL, an engine that provides you with the ability to store and execute program units. Oracle Database 10g also supports Java and XML. The Oracle server offers the options of retrieving data based on optimization techniques. It includes security features that control how a database is accessed and used. Other features include consistency and protection of data through locking mechanisms.
The Oracle10g release provides an open, comprehensive, and integrated approach to information management. An Oracle server consists of an Oracle Database and an Oracle server instance. Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The SGA is an area of memory that is used for database information shared by the database users. The combination of the background processes and memory buffers is called an Oracle instance.

SQL Statement*
Oracle SQL complies with industry-accepted standards. Oracle Corporation ensures future compliance with evolving standards by actively involving key personnel in SQL standards committees. Industry-accepted committees are American National Standards Institute (ANSI) and International Standards Organization (ISO). Both ANSI and ISO have accepted SQL as the standard language for relational databases.

Tables Used in the Course
The following main tables are used in this course:
• EMPLOYEES table: Gives details of all the employees
• DEPARTMENTS table: Gives details of all the departments
• JOB_GRADES table: Gives details of salaries for various grades
Note: The structure and data for all the tables are provided in Appendix B.

Summary

Relational database management systems are composed of objects or relations. They are managed by operations and governed by data integrity constraints.
Oracle Corporation produces products and services to meet your RDBMS needs. The main products are the following:
• Oracle Database 10g, with which you store and manage information by using SQL
• Oracle Application Server 10g, with which you run all of your applications
• Oracle Enterprise Manager 10g Grid Control, which you use to manage and automate administrative tasks across sets of systems in a grid environment
SQL
The Oracle server supports ANSI-standard SQL and contains extensions. SQL is the language that is used to communicate with the server to access, manipulate, and control data.

Retrieving Data Using the SQL SELECT Statement

Objectives
To extract data from the database, you need to use the structured query language (SQL) SELECT statement. You may need to restrict the columns that are displayed. This lesson describes all the SQL statements that are needed to perform these actions. You may want to create SELECT statements that can be used more than once.
This lesson also covers the SQL Developer environment in which you execute SQL statements.

Capabilities of SQL SELECT Statements
A SELECT statement retrieves information from the database. With a SELECT statement, you can use the following capabilities:
• Projection: Choose the columns in a table that are returned by a query. Choose as few or as many of the columns as needed.
• Selection: Choose the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved.
• Joining: Bring together data that is stored in different tables by specifying the link between them. SQL joins are covered in more detail in the lesson titled “Displaying Data
from Multiple Tables.”

Basic SELECT Statement
In its simplest form, a SELECT statement must include the following:
• A SELECT clause, which specifies the columns to be displayed
• A FROM clause, which identifies the table containing the columns that are listed in the SELECT clause In the syntax:
SELECT is a list of one or more columns

Selects all columns
DISTINCT suppresses duplicates column|expression selects the named column or the expression alias gives selected columns different headings
FROM table specifies the table containing the columns
Note: Throughout this course, the words keyword, clause, and statement are used as follows:
• A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords.
• A clause is a part of a SQL statement.
For example, SELECT employee_id, last_name, ... is a clause.
• A statement is a combination of two or more clauses.
For example, SELECT * FROM employees is a SQL statement.

Selecting All Columns of All Rows
You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). In the example in the slide, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains eight rows, one for each department.
You can also display all columns in the table by listing all the columns after the SELECT keyword. For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the DEPARTMENTS table:
SELECT department_id, department_name, manager_id, location_id FROM departments;

Selecting Specific Columns of All Rows
You can use the SELECT statement to display specific columns of the table by specifying the column names, separated by commas. The example in the slide displays all the department numbers and location numbers from the DEPARTMENTS table.
In the SELECT clause, specify the columns that you want, in the order in which you want them to appear in the output. For example, to display location before department number going from left to right, you use the following statement:

Writing SQL Statements

Using the following simple rules and guidelines, you can construct valid statements that are both easy to read and easy to edit:
• SQL statements are not case-sensitive (unless indicated).
• SQL statements can be entered on one or many lines.
• Keywords cannot be split across lines or abbreviated.
• Clauses are usually placed on separate lines for readability and ease of editing.
• Indents should be used to make code more readable.
• Keywords typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase.
Executing SQL Statements
Using SQL Developer, click the Execute button to run the command or commands in the editing window.
Using SQL*Plus, terminate the SQL statement with a semicolon and then press the Enter key to run the command.

Column Heading Defaults
In SQL Developer, column headings are displayed in uppercase and centered.
SELECT last_name, hire_date, salary FROM employees;

You can override the column heading display with an alias. Column aliases are covered later in this lesson.

Next part start with - Arithmetic Expressions