Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

CREATE TABLE Statement in Oracle SQL – PLSQL Using Already Existing Tables

by

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Here we will learn how to use CREATE TABLE statement to create new tables using already existing tables in the database.

Syntax for using CREATE TABLE statement to create a new table by already existing table(s) is:

CREATE TABLE new_table_name
AS
(SELECT *
FROM
old_table_name
);

Let’s take an example for understanding:

Suppose we already have a table named “Employee” as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

We want to create a new table named “employee_copy” which should be copy of “Employee” table. For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT *
FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’. Also all the data from the employee table is copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 2:

We want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) . For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
 FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 3 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table. Also all the data from the employee table of the aforementioned 3 columns will be copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result:

Employee_ID Employee_Name Salary
101 Emp A 10000
102 Emp B 20000
103 Emp C 28000
104 Emp D 30000
105 Emp E 32000

Scenario 3:

Suppose we also have a department table in our database which has the list of departments as shown below:

Department_Id Department
1000 Sales
2000 IT
3000 Support

And we want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) and 1 column from the “Department” table e.g. (department). For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
        ,dept.department
 FROM employee emp
      ,department dept
 WHERE emp.department = dept.department);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 4 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table and ‘department’ from department table.

The employee_copy table will have the data as shown below:

Employee_ID Employee_Name Salary Department
101 Emp A 10000 Sales
102 Emp B 20000 IT
103 Emp C 28000 IT
104 Emp D 30000 Support
105 Emp E 32000 Sales

CREATE TABLE Statement in Oracle SQL – PLSQL

by

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Syntax for the CREATE TABLE statement is:

CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
);

Each column must have a data type. Also every column has to be defined as NULL or NOT NULL, if nothing is specified in NULL or NOT NULL place then the default value will be assumed as NULL.

Let’s take an example for understanding:

Suppose we want to create a table named “Employee” as shown below (without data).

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see how we can use CREATE STATEMENT to create or define a new table in data base.

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    NUMBER(20)
);

The above CREATE TABLE statement will create a new table named as employee in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’.

SQL SELECT Statement

by

SQL SELECT StatementThe SQL SELECT Statement allows us to retrieve or fetch records from one or more (by using Oracle SQL Joins) tables from our database.

SQL SELECT Statement Syntax

SELECT column(s)
FROM table_name(s)
WHERE conditions;

SQL SELECT Statement Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see what the SQL SELECT Statement does when used in different scenarios

SQL SELECT Statement – Using ‘*’ to view all records from a table

The SQL SELECT Statement can be used with the ‘*’ wildcard character to fetch all the columns and rows from a table.

For example, the below SQL SELECT Statement, once executed, will return all the data from the employee table.

SELECT *
FROM employee;

The result of the above SQL SELECT Statement query will be:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Again, in the above SQL SELECT Statement query, we have used the ‘*’ wildcard character to view all the records from the employee table.

SQL SELECT Statement – Selecting individual fields from a table

We can use the SQL SELECT Statement to select and view only certain fields from a table.

For example, the below SQL SELECT Statement query will return “employee_id”, “employee_name” and “department” fields only from the employee table.

SELECT employee_id
       ,employee_name
       ,department
FROM employee;

The result of the above SQL SELECT Statement query will be;

Employee_ID Employee_Name Department
101 Emp A Sales
102 Emp B IT
103 Emp C IT
104 Emp D Support
105 Emp E Sales

Here we can see that in the SQL SELECT Statement we have explicitly mentioned that we wish to see only the “employee_id”, “employee_name” and “department” columns from the employee table.

SQL SELECT Statement – Using WHERE Clause

The SQL SELECT Statement can be used with the SQL WHERE Clause to filter records based on requirements.

For example, the below SQL SELECT Statement will return “employee_name” and “Salary” of employees working in “Sales” department from the employee table.

SELECT employee_name
       ,salary
FROM employee
WHERE department='Sales';

The data returned by the above SQL SELECT Statement will be:

Employee_Name Salary
EMP A 10000
EMP E 32000

The column names after the SQL SELECT Statement mentions that we want to view the data only for “Employee_Name” and “Salary” columns for “Sales” department only from employee table.

SQL SELECT Statement – Selecting fields from several tables

The SQL SELECT Statement can fetch columns from many tables at once.

For example,

SELECT orders.order_id
       ,suppliers.supplier_name
FROM suppliers
    ,orders
WHERE suppliers.supplier_id = orders.supplier_id;

The above SQL SELECT Statement joins two database tables namely “Orders” and “Suppliers” and returns us the result based in condition that the value for “Supplier_Id” field is available in both the joined tables.

Learn more about Oracle SQL Joins.

SQL MAX Function

by

SQL MAX FunctionSQL MAX function returns the highest or maximum value out of the available records/values. Oracle MAX Function is opposite of SQL MIN Function.

SQL MAX Function Syntax

SELECT MAX(expression)
FROM table_name
WHERE conditions;

SQL MAX Function Examples

Let’s take an example for understanding the SQL MAX Function:

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see usage of SQL MAX Function in different scenarios

SQL MAX Function – SQL SELECT MAX Usage

The simplest way of using the SQL MAX Function is to fetch the maximum value from a column of table

For example, with the below Oracle MAX Function query will fetch maximum value of commission.

SELECT MAX(commission) as "MAX Commission"
FROM employee;

The above SQL SELECT MAX query will return result as “20” as that’s the highest commission available in the “Employee” table.

Note: We have aliased the MAX(commission) field as “MAX Commission”, hence once the query is run “MAX Commission” is displayed as field name of result from the Oracle MAX Function query.

NULL Values are not considered by the SQL MAX Function.

SQL MAX Function – Using SQL WHERE Clause Example

SQL MAX Function can be used in SQL SELECT Statement having WHERE Clause.

For example, below SQL SELECT MAX query will return maximum salary in ‘Sales’ department.

SELECT MAX(salary) as "Maximum Salary"
FROM employee
WHERE department  = 'Sales';

The above Oracle MAX Function query returns “32000” as maximum salary for ‘Sales’ department.
Also note that we have aliased the MAX(salary) field as “Maximum Salary”.

SQL MAX Function – Using SQL GROUP BY Clause Example

SQL MAX Function can be used in SQL SELECT Statement having SQL GROUP BY Clause.

For example, the Oracle MAX Function query below will return department name and the maximum salary being given in each department.

SELECT department as "Department Name"
       ,MAX(salary) "Maximum Salary"
FROM employee
GROUP BY department;

The data returned by the above SQL SELECT MAX query will be:

DEPARTMENT NAME MAXIMUM SALARY
Sales 32000
IT 28000
Support 30000

Here notice that by using the SQL SELECT MAX query with SQL GROUP BY Clause we have retrieved the maximum salaries of every unique department along with the department name.

Oracle/SQL MIN Function

by

SQL MIN FunctionSQL MIN function returns least or minimum value out of available records/values. Oracle MIN Function is opposite of the SQL MAX Function.

SQL MIN Function Syntax

SELECT MIN(expression)
FROM table_name
WHERE conditions;

SQL MIN Function Examples

Let’s take an example for understanding the Oracle MIN Function

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see what the SQL MIN Function does when used in different scenarios

SQL MIN Function – Simple Usage

The simplest way to use SQL MIN Function is to fetch the minimum value from a column of a table

For example, with the below Oracle MIN Function query we will be able to fetch the minimum value of commission.

SELECT MIN(commission) as "MIN Commission"
FROM employee;

The above MIN SQL Function query will return result as “10” as that’s the lowest commission available in the “Employee” table.

Also note that we have aliased the MIN(commission) field as “MIN Commission”, hence once the query is run “MIN Commission”  will be displayed as the field name of the result from the Oracle MIN Function query.

NULL values are not considered by the MIN SQL Function.

SQL MIN Function – SQL WHERE Clause Example

SQL MIN Function can be used in SQL SELECT Statement having SQL WHERE Clause.

For example, below SQL MIN query returns minimum salary in ‘Sales’ department.

SELECT MIN(salary) as "Minimum Salary"
FROM employee
WHERE department  = 'Sales';

The above Oracle MIN Function query returns ‘10000’ as minimum salary in ‘Sales’ department.

Also note that we have aliased the MIN(salary) field as “Minimum Salary”.

SQL MIN Function – SQL GROUP BY Clause Example

SQL MIN Function can be used in SQL SELECT Statement having SQL GROUP BY Clause.

For example, Oracle MIN Function query below, shows department name and minimum salary of each department.

SELECT department "Department Name"
,MIN(salary) "Minimum Salary"
FROM employee
GROUP BY department;

The data returned by above MIN SQL Function query will be:

Departement Name Minimum Salary
Sales 10000
IT 20000
Support 30000

Here notice that by using Oracle MIN Function with SQL GROUP BY Clause we have retrieved minimum salaries of every unique department along with the department name.