Learning Sql using Sql developer on OEL.


You use SQL*PLUS as a command line interface used to query data from your database, one of the reasons it is widely used instead of graphical user interface such as isql,sql developer is because it comes bundled with every version of Oracle database software , Also most time as a Database Adminstrator(DBA) you will be using SQL*PLUS a lot to connect to remote database without any graphic user interface,while iSQL*PLUS,SQL DEVELOPER is/are a browser based version of SQL*PLUS.

SQL statements are written on the SQL*PLUS prompt. and also SQL DEVELOPER. To startup SQL DEVELOPER on OEL(Oracle Enterprise Linux) , login as oracle user on command prompt as show below with image.

Type the following accordingly to startup sql developer:(Note startup your listener if you have not).

          lsnrctl start /*starts up your listener*/
          sqlplus / as sysdba /* To startup your DB instance orcl,input password.*/
          startup /*Makes your idle instance open*/
          alter user hr identified by hr account unlock; /*unlocks and sets user hr password*/
          alter user scott identified by tiger account unlock; /*unlocks and sets user scott password*/
          cd /u01/app/oracle/product/11.2.0/db_1/sqldeveloper/
          sh sqldeveloper.sh /*a GUI starts up*/

Sqldeveloper GUI can be seen now on your screen, to connect a user follow the following guide below:

- Click on file or Ctrl N to create a new connection, double-click to select database connection.
- Connection Name: Give the connection a name of your choice.
- Username: The user we want to connect is the sys user,so sys remains the username.
- password: input password for sys user.
- Role: select sysdba from dropdownlist.
- Hostname:localhost
- Port: default port=1521
- SID:orcl /*instance name*/
-Click on test,if its a success then connect.

sqldeveloper and sql statement

We can also connect multiple users, go ahead and connect users HR,Scott with passwords hr,tiger respectively.

Writing SQL Statements

SQL statements are not case-sensitive (unless indicated), SQL statements can be entered on one or many lines.
Keywords can not be split accross lines or abbreviated. Clauses are usually placed on seperate 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.

We have categories of SQL statements which are:

  • - Data Manipulation Statements
  • - Data Defination statements
  • - Data Control Statement
  • - Transaction Control Statements

1. Data Manipulation Languages(DML): Retrives data,enters new rows,changes existing rows and removes unwanted rows from tables in the database respectively,and they are:SELECT,DELETE,INSERT,UPDATE and MERGE.

Using Select Statements:

A SELECT retrieves information from the database with a select statement, basic SELECT statements are of the syntax: 

SELECT * | { [DISTINCT ] column | expression [ alias ], . . . } 

Where SELECT identifies the columns to be displayed can be a list of one or more columns.
SELECT  *  identifies by selecting all columns from the table.
FROM Table  specifies the column containing the columns DISTINCT suppresses duplicate.
Column/expression selects the named column or the expression, alias gives selected columns different heading.
WHERE  restricts the query to rows that meets a condition while CONDITION is composed of column names, expressions, constants and a comparison operator.

Now on Sql Developer, as Hr user lets run so queries to check tables tha belong to the user hr.

DESC user_tables; /* Note to execute, click f9 or right-click to execute statements*/ 

Image below shows execution of the sql statements. SQL developer and basic sql statements

In the next sql query we would select all table name from tables that belongs to user hr.

select table_name from user_tables;

The image below shows all tables user hr has. you should get result similar as shown in image.SQL developer and basic sql statements

Now that we have known all tablenames belonging to user hr, we can also look into structures of each tables. i.e, columns contained in hr.employees table. to do that we run a DESCRIBE statement(shortened DESC).

DESC employees;

Image below shows the output of the Describe statement.SQL developer and basic sql statements

Finally, select all columns from employees table to view all data inside,the query is written below:

SELECT * FROM employees;

Some more examples on using basic SELECT statements.

In this example suppose that we want to display all employees where first name starts with alphabet M, the query is shown below.

SELECT * from employees where first_name like 'M%';

Image below shows employees whose first name starts with M.SQL developer and basic sql statements

Mrre examples are in the next page...Learn about Character strings and Date