DataWitzz

SQL data types for Oracle/PLSQL

PLSQL datatypes

In this article, we will understand briefly PLSQL datatypes. Each value in PL/SQL such as constant, variable, or parameter has a data type that defines their storage and calculation behavior. PL/SQL has basically two types of data types e.g scalar and composite.

Types of sql data types

Scalar data types

In scalar data type are those data type that stores a single value. Scalar data type has further four data subtypes

Number
Boolean
Character
Datetime

Composite data types

Whereas composite data type stores multiple values. There are two data sub types of composite data types are available.

Record
Collection

In this article, we will be discussing only scalar data type

Oracle number data type

The numeric data … Continue Reading

Oracle dual table: what is dual table in oracle

Oracle dual table

Oracle dual table is a table automatically created by oracle database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users.

Why dual oracle table is required

In oracle, SELECT statement requires FROM clause, which fetches data from oracle table. But in some scenarios, we have constant values which is not available in any table and we need to refer to these values in our SQL queries as additional value. To use these we require SQL DUAL table.

select lower('I AM AN ANALYST') from dual;

In the above example, we wanted to use a constant value ‘I AM AN ANALYST’ in a small … Continue Reading

Oracle SQL developer download : How to install oracle sql developer

SQL developer download

In this article we will be learning sql developer download for windows 10. Oracle sql developer installation is very easy and I will be explaining it in step by step method. We will also be learning how to create connection in SQL developer with SQL database which we learned in our previous article.

how to install oracle sql developer

To install SQL developer we will first go this link the download on Windows 64-bit with JDK 8 included if we don’t have java installed on our system else we can download Windows 32-bit/64-bit.

We click on the download icon it will redirect to other page for Sign In. If you already have … Continue Reading

How to install Oracle database: Easy steps to install Oracle database in windows

1 Comment / SQL

How to install Oracle database

Types of Oracle database

Oracle is providing three types of databases to download.

Enterprise Edition – It is a common and expensive edition of oracle database. There is no limit on memory or database size, and it provides many features that are not available to other editions.
Standard Edition – It is a limited edition of enterprise edition, and it also has no limit on memory or database limit. It also provides many features but less than oracle enterprise edition.
Expression Edition – It is a free-to-use version of oracle database and has very limited features. Like it uses a maximum of 2GB of RAM and has 12GB of user data.

A Step By Step

Difference between Primary Key and Foreign Key

1 Comment / SQL

Difference between Primary Key and Foreign Key

A primary key is used to ensure data in the specific column is unique. It is a column that cannot have NULL values. It is either an existing table column or a column that is specifically generated by the SQL database according to a defined sequence.

What is Primary key:

Example: Refer to the image above:
STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

What is a Foriegn key:

Example: Refer to the above image
STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in … Continue Reading

SQL Sample Database

In the coming articles, we will be using our sample database which is an HR database. It has multiple seven tables which will be interlinked to get answered our question.

The description of tables are as mentioned below

The jobs table stores the job data including job title and salary range.

The dependents table stores the employee’s dependents.

The employees table stores the data of employees.

The departments table stores department data.

The regions table stores the data of regions such as Asia, Europe, America, and the Middle East and Africa. The countries are grouped into regions.

The locations table stores the location of the departments of the company.

The countries table stores the data of countries where … Continue Reading