Flavors of SQL and its differences

Flavors of SQL and its differences

To become a SQL expert, what are all that you need to learn? What are all the flavors in SQL and the differences between each? What is a database and what are the benefits and all sorts of details?

Starting-Up:

In this session /exercise you will prepare your computer to run SQL*plus

To Start Up:

• Make sure that the ORACLE RDBMS has been installed on your computer.
• When you see one or more characters on the left side of the screen, the Operating System’s prompt; which it uses to signal that it is ready to accept a command. In many Operating Systems, prompt will be represented by a dollar sign ($). Your computer’s Operating System prompt may be different.
• Enter the command “SQL*Plus” and press [Return]. This is an Operating System command that starts SQL*Plus. SQL*Plus will start running.
• Enter your user name and press [Return].
• SQL*Plus will display the prompt “Enter Password”. Enter your password and press [Return] again. For your protection, your password will not appear on the screen.
• SQL*Plus should display its prompt, ‘SQL>’:

If SQL*Plus fails to start, you should see a message meant to help you correct the problem. For further information, refer to the ORACLE Error Message and Codes for ORACLE RDBMS error messages.

Shortcuts to Starting Up:

When you start SQL*Plus you may enter your user name and password, separated by a slash (/), on the command line. For example, if your user name is SCOTT and your password is TIGER, you may enter.

$SQLPLUS SCOTT / TIGER

And press [Return]. You may also arrange to be logged onto SQL*Plus automatically when you log onto your host Operating System.

The Command Line:

• The SQL*Plus prompt (SQL>) means that SQL*Plus is ready for you to enter a command.
• The blinking underline or rectangular block after the SQL> is your computer’s cursor or pointer. The cursor indicates the place where the next input you enter will appear on your screen.
• The line on which the SQL> prompt appears is called the command line. To tell SQL*Plus what to do, you enter commands to the right of the SQL>. At the end of the each line, press [Return]. If you have finished the command, SQL*Plus will process it, then display a new SQL> prompt, indicating that it is ready for another command.
• If you press [Return] before finishing a command, SQL*Plus will move the cursor to the next line and prompt you with the line number 2, 3, 4, and so on. Continue entering the command on the new lines.

There are two kinds of commands you can enter on the command line:

• SQL commands; for working with information in the database.
• SQL*Plus commands; for formatting results, setting options, and editing and storing SQL commands.

The commands you use to work with our Database are part of the SQL command language.

Entering SQL Commands:

To Display the EMP Table

1. On the command line, enter the first line of the command:

SQL> SELECT *;

If you make a mistake, use [Backspace] to erase it and re-enter. When you are done, press [Return] to move to the next line.

1. SQL*Plus will display a ‘2’, its prompt for the second line. Enter the second line of the command: ========è2 FROM EMP;

The semicolon means that this is the end of the command. Press [Return].

1. SQL*Plus will process the command and display the results on the screen.

====èSQL> SELECT *

2 FROM EMP;

1. After displaying the results, SQL*Plus will display its prompt SQL>, again.

The SELECT command is the most important single SQL command because it is your chief means of requesting a display of information from tables. A SELECT command is often called a query.

1. All SQL commands must end with a semicolon (;)
2. SQL doesn’t care how the words compose a command sine they are divided into lines. You may enter your command on a single line or as many lines, as you want.
3. When you enter a SQL command, it is stored in a part of memory called the SQL buffer. It remains there until you enter a new command. This means that if you want to edit or re-run the current command, you may do so without re-entering it.
4. When you enter the semicolon, it indicates the end of a SQL command. If not, it is not stored in the buffer as a command.

Entering SQL * Plus Commands:

You can enter SQL*Plus commands, as well as SQL commands, on the command line. Among other things, SQL*Plus commands allow you to:

1. Edit the command in the SQL buffer.
2. Control the way your query results are displayed.
3. Store SQL commands to a disk and retrieve them to be run.
4. Set options that influence the behavior of SQL*Plus.

To change the Format of the Column EMP:

1. On the command line, enter this SQL*Plus command:

SQL> COLUMN EMP FORMAT $99,999 HEADING SALARY:

If you make a mistake, use (Backspace) to erase it and re-enter. When you have entered the line, press (Return). SQL*Plus will note the new format and display the SQL> prompt again ready for a new command.

1. SQL*Plus commands syntax have a slight difference from SQL commands. You normally must enter a SQL*Plus command on a single line. If you reach the end of a line, the cursor will move automatically to the next line. A SQL*Plus command need not to end with a semicolon. When you are finished, you may just press return.
2. If you make a mistake while entering a command, use backspace to go back; then re-enter.
3. SQL*Plus can offer you help while you are using it. If you are not certain what commands are available or how to use them, enter the help command to display a panel of helpful information.
4. If SQL*Plus detects an error in a command, it will try to help you out by displaying an error message. For example, if you misspell the name of the table, you will see the table or it will say, “view doesn’t exist”.
5. Suppose you have displayed the 1st page of a 50-page report, and now you decide you are not interested with the rest of it. Press (Interrupt), SQL*Plus will stop the display and return to the command prompt. Note that pressing (Interrupt) will not stop a printing operation in progress.
6. When done working with SQL*Plus and you wish to return to the Operating System, enter the EXIT command.
7. ORACLE objects such as tables are stored in the ORACLE DB on your host computer, and each object has  a name, like EMP for the table of employees.
8. If another user owns the object you want, you must tell SQL*Plus who owns it. A simple way to do this is to prefix the table’s name with the owner’s name and a period:

=èSQL> SELECT FROM SCOTT>EMP;

Backing Up Data:

A BD stored on your computer’s disk is vulnerable to damage. That is why a backup copy of your DB is important, use ORACLE EXP utility program.

Sharing a Database Among several Users:

Even if you share your computer with other users, you own the tables and other database objects that you create. No one else can read or change them unless you give your approval. If you wish to allow other users to access your information, give them permission using the GRANT command. If you have access to other users’ tables, remember to prefix them with the owner’s name and the ORACLE RDBMS maintains the integrity of your tables even when you share them with other users. To prevent different users of the same tables from making conflicting changes, ORACLE doesn’t permit two users to insert, update, or delete the same rows at the same time.

Running Other Programs:

You can execute a host Operating System command from the SQL*Plus command line. This is useful when you want to perform some tasks such as displaying the contents of a host Operating System file. To run a host Operating System command, enter the SQL*Plus command HOST followed by the host command. For example, this SQL*Plus command runs the host command ‘DIR*. SQL’.

==èSQL> HOST DIR *.SQL;

When the host command has completed running, the SQL*Plus prompt will appear again.

What is a Database?

“A Database is an organized collection of information”. An ORACLE database consists of tables, views, queries, and reports which are based on the tables. In an ORACLE database, you enter the information you might otherwise put in a list, table, or forms. The entries may be words, number, dates, or pieces of text. Thus, you can use an ORACLE database for organizing:

• Mailing lists
• Customer, client, and supplier lists
• Personal information
• Inventories
• Accounts
• Payable and receivable
• A variety of other information

In a relational database system like ORACLE RDBMS, information is organized in table categories and information are listed across the top of the each table. Individual cases are listed down the left side. With this, you can readily visualize, understand, and use the information.

Benefits/disadvantages of the Database:

Integrated DB approach has several advantages, let us state some of them.