What is SQL Developer?
SQL Developer is an IDE (Integrated Development Environment) for working with SQL Databases. It is a GUI helping DBA and developers to work efficiently and easily. It enhances productivity and helps users to run SQL commands, export data to the desired format, browse database objects, manage databases, debug and execute the database and many more related features. Oracle provides SQL developer to work on for both developers and database administrators. Oracle SQL Developer provides Oracle SQL Developer Data Modeler (SDDM) which supports Data Definition Language Scripting (DDL), Data Flow Diagrams (DFD’s), a reporting repository, comparing models and many more. SQL Developer has provided a set of interfaces for Database Administrators for their important tasks. SQL Developer’s Database Administrator panel supports storage management, role management, user management, resource management, recovery management, data pumping, Oracle auditing and many more.
If you are a beginner then using Oracle SQL Developer would be more efficient and easy. SQL Developer is developed in Java and runs on Windows, Mac OS X, and Linux. There are many versions of SQL developer. You can use anyone according to your requirements. It is not only easy to use but also to install.
Steps to install the SQL Developer:
- You can install it from Oracle website but you need to have an account on it, to create an account.
- Install the required version of the Oracle SQL Developer.
- Install Oracle Database 10g and Sample Schema.
- Unlock the HR user and log in as the SYS user and type command: alter user hr identified by hr account unlock;
- Download and unzip the files.zip that contains all required files to a local folder on your system.
The first task after installation is to create a connection to the Oracle Database using Oracle SQL Developer.
Steps to Create Database Connection:
1. Open Oracle SQL Developer.
2. Now under Connections, right click on the Connections and the Connection menu appears. Click on New Connection.
3. The New/Select Database Connection Dialog Box will appear.
4. Enter the following details in the fields of the above-appeared dialog box.
- Connection Name: Name of Cloud Connection.
- User Name: Database Username
- Password: Your choice (then check the checkbox Save Password)
- Hostname: Your local hostname
- SID: Your own SID
- Then click on Test.
5. You can check the status of testing on the left bottom side. Then click connect and save.
6. The connection would be saved and the new connection would appear under
After creating a connection you can use existing objects of Oracle SQL Developer or can create new also. Now let us see how to browse already existing objects.
Browsing Objects in Oracle SQL Developer:
1. Expand the newly added connection.
You can use any object like tables, views, indexes, packages, triggers, sequences, synonyms, directories, types, materialized views, functions and many more.
But we will see in brief how we can use tables.
2. Expand Tables.
3. You can select any table from the list to view the table definition and click the Data tab.
In the figure below we have selected DEPARTMENTS table.
You can then see the data present in the table.
You can apply various operations on a table like sorting, filtering data, applying various constraints and many more. You can apply sorting by clicking the arrow icon next to the name of the column you want to sort.
Creating Objects in Oracle SQL Developer:
You can create dialogs for each supported object type. Oracle SQL Developer also supports Temporary tables, External tables, list, Partitioned tables, Index Organised tables and many more object types.
1. Right click on
2. Select a New Table.
3. Enter the Table Name and check the Advanced
4. Enter information for the first column as mentioned below:
- Column Name: Performance_id
- Type: VARCHAR
- Size: 3
- Not Null: Select it
- Primary Key: Select it
5. Enter information for the second column as below:
- Column Name: Name
- Type: Varchar
- Size: 40
- Not Null: Select it
- Primary Key: Leave it
6. Similarly, enter information for as many columns you require.
7. Click OK.
8. The new table would be created under TABLES.
9. To see it, you can expand the TABLES and then use it to perform SQL commands.
The Not Null and Primary Key is the integrity constraints which states what type of data can be inserted in each column. There are more integrity constraints that can be applied to restrict the type of data that is valid for a respective column.
Let us briefly see what these integrity constraints do. There are basically five integrity constraints:
- NOT NULL:
This constraint ensures that data must be present in that column. A null value cannot be inserted in that column.
This constraint ensures that each value in the column must be unique i.e. no repetition of values. This constraint can be applied on multiple columns together as a group called as Composite Unique It does not say anything about Null value.
- Primary Key:
This constraint ensures the properties of both NOT NULL and UNIQUE constraint i.e. multiple rows cannot have the same value and also prevents null value at the same time.
- Foreign Key:
This constraint states that for each value in the column on which the constraint is applied, there must be the same or matching values in other specified table and column. It is basically called referencing other tables.
This constraint is not used frequently but it ensures that values must satisfy the specified condition. The condition can be the logical expression or comparison expression depending upon the need for the restrictions to be applied.
This has been a guide to What is SQL Developer. Here we discussed how to install and to create a new database of SQL Developer. You can also go through our other suggested articles-