Basic Statements in SQL

In the previous article, we studied about the downloading and installation of the oracle database, and connection to the pluggable database. Now let us study the SQL statements.

SQL statements can be used to search the database, creating tables, adding data to tables, modifying data, and dropping tables. SQL statements are not case sensitive unless they are indicated. Let us understand some basic SQL statements to work with SQL.

Even though the SQL statements are not case sensitive, but still we use UPPER CASE for SQL keywords and lower case for user-defined words.

Before understanding the Statements, let us study about constraints and the most commonly used Datatypes in SQL.

Constraints in SQL

The constraints are the restrictions that the value should not be repeated in the same table. This states the rules for the data in the given table. The accuracy and reliability of the data in the database are seen by using Constraints. The following are the commonly used constraints.

  • NOT NULL : If a column is created with NOT NULL constraint, then the user cannot insert the null values.
  • UNIQUE : If a column is created with UNIQUE, then the user cannot insert the duplicate values into that column.
  • PRIMARY KEY : It is a combination of UNIQUE and NOT NULL. The PRIMARY KEY identifies the data uniquely.
  • FOREIGN KEY : Any column of a table is derived from the external table is called a FOREIGN KEY. This identifies the record of another table.
  • CHECK : It is used to CHECK the condition while inserting the values.
  • DEFAULT : It is used to insert the DEFAULT values into the columns.

Datatypes in SQL

SQL Data Types is the type of data that defines the value which can be stored in a table column. This is broadly divided into many types. Among those datatypes, the following are the most commonly used datatypes.

    • Numeric data types.
    • Date and Time data types.
    • Character and String data types.

    Now let us understand the above datatype.

    Numeric datatypes:

    The numbers stored in the database columns are numeric datatypes. This contains some numeric types. They are INTEGER, FLOAT, REAL, and etc.

    Datatype

    From

    To

    Bit

    0

    1

    tinyint

    0

    255

    smallint

    -32,768

    32,767

    int

    -2,147,483,648

    2,147,483,647

    bigint

    -9,223,372,036, 854,775,808

    9,223,372,036, 854,775,807

    decimal

    -10^38 +1

    10^38 -1

    numeric

    -10^38 +1

    10^38 -1

    Float

    -1.79E + 308

    1.79E + 308

    real

    -3.40E + 38

    3.40E + 38

    Date and time datatypes:

    This datatype is used for values that contain both date and time which is stored in the database.

    Datatype

    Description

    DATE

    Stores the date in YYYY-MM-DD format

    TIME

    Stores the time in HH:MI:SS format

    DATETIME

    Stores the date and the time information in YYYY-MM-DD HH:MI:SS format

    TIMESTAMP

    Stores the number of seconds passed

    YEAR

    Stores the year in 2 digit or 4 digit format.

    String Datatypes

    These datatypes store any kind of data in the table. In string data types, users can store either the fixed length of characters or huge length data based on their requirements.

    Datatype

    Description

    CHAR

    Fixed length with a maximum length of 8,000 characters.

    VARCHAR

    Storage of Variable-length with a maximum length of 8,000 characters.

    VARCHAR(max)

    Storage of Variable-length with provided max characters.

    TEXT

    Variable-length storage with a maximum size of 2GB data.

    CREATE TABLE statement

    Let us understand how to create a table. Creating a table is the initial point to write a query. We need three things to create a table.

    • Name of the table: The table which contains all the details in the database. Give the relevant name for the created table according to the details of the table.
    • Columns: This defines the set of the data value of a specific type. When the table contains multiple columns, then the columns are separated by using commas (,). That may be sl no, first_name, last_name and etc.
    • Datatypes: Datatype is the type of data. The details are given to the database according to the datatypes of the columns.
    • Constraints: Use them when the values are needed to be restricted.

    The syntax to create a table is as follows :

    CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype,....);

    Example of creating a table :

    CREATE TABLE Persons_table (
        Person_ID INT PRIMARY KEY,
        First_Name VARCHAR(255),
        Last_Name VARCHAR(255),
        Gender CHAR(1),
        age INT,
        City VARCHAR(255)
    );

    This query creates a table called Persons_table.
    table-creating

    INSERT INTO Statement in SQL

    The INSERT INTO Statement is used to insert one or more related records into the table. It is used to insert a new row in the table.

    The syntax of the INSERT INTO statement :

    INSERT INTO table_name (column1, column2, ... ) VALUES (expression1, expression2, ... );

    First, mention the INSERT INTO command. Next, specify the name of the table and enter the details of the table.

    Example of the INSERT INTO statement to enter the related data :

    INSERT INTO Persons_table VALUES (1,'Rama', 'Shetty', 'm', 28, 'Bangalore');
    INSERT INTO Persons_table VALUES (2,'Soma', 'Shetty', 'm', 29, 'Hassan');
    INSERT INTO Persons_table VALUES (3,'Ramya','Gowda', 'f', 45, 'Sakleshpur');

    This query inserts the values into the table.
    insert-statement

    SELECT and FROM statements in SQL

    SELECT and FROM statements are commonly used in the SQL. The SELECT and FROM statements are used to retrieve the data from a database. The data returned is stored in a result table and called the result-set.
    In a SQL SELECT statement only SELECT and FROM statements are mandatory. Other statements like WHERE, ORDER BY, GROUP BY, HAVING are used as per the given condition. Without SELECT and FROM statements, no queries can be created.


    The syntax for Select and From statements :

    SELECT column_1, coloumn_2
    FROM Table_name;

    Here the SELECT is used to select the columns and the FROM is used to select the name of the table from which we are going to pull the data.

    Example of SELECT and FROM statements to retrieve the whole table.

    SELECT * FROM Persons_table;

    In this example, the '*' represents all the columns that are present in the table. So, the instruction says, select all the details of the columns from the persons_table table.
    data-of-table

    Example of SELECT and FROM statements to retrieve the specific columns :

    SELECT Person_ID, Gender
    FROM Persons_table;

    retrieved-data
    select-column
    In the above example, we have to retrieve only the given columns i.e, select the columns Person_ID and Gender from the persons_table table.
    The SELECT statement contains some other statements. They are as follows.

    SELECT DISTINCT statement in SQL:

    SELECT DISTINCT is a statement that retrieves only the different data.

    Syntax of the SELECT DISTINCT statement :

    SELECT DISTINCT column1, column2, ...
    FROM table_name;

    Example of the SELECT DISTINCT statement :

    SELECT DISTINCT city
    FROM persons_table;

    retrieved-data

    In this statement, only the different cities are retrieved. The SELECT DISTINCT keyword does not retrieve the same data of the given column of the table.
    distinct

    ROWNUM statement in SQL:

    ROWNUM is a statement which retrieves the specified number of the record.

    Syntax of the ROWNUM :

    SELECT column_1, column_2....
    FROM table_name
    WHERE ROWNUM <= number;

    Example of the ROWNUM :

    SELECT * FROM Persons_table
    WHERE ROWNUM <= 3;

    retrieved-data

    In this statement, the ROWNUM selects the first three details of the Persons_table.

    rownum-statement

    ALTER TABLE statement in SQL

    The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns and is also used to rename a table.

    ADD is used to add a column or the details in a table. Mention the respective datatype of the column while adding the column in a table.

    Syntax of adding a single column to the table.

    ALTER TABLE table_name
    ADD column_name column_definition;

    Example of adding a single column to the table.

    ALTER TABLE persons_table
    ADD email_id varchar(50);

    retrieved-data

    In this query, the table has been altered using the ADD statement.

    alter-table

    The output is seen after adding the email_id column to the persons_table.

    add-table

    We can rename the column_name and the table_name by using the RENAME keyword.

    The Syntax to RENAME the table name is as follows.

    RENAME table_name TO new_table_name;

    Example to RENAME the table name.

    RENAME Presons_table TO persons_info;

    In this query, the table is renamed from Persons_table to persons_info.
    rename-table

    The table persons_table is renamed to persons_info and the data of persons_info is retrieved.
    rename-table_name

    RENAME COLUMN statement in SQL:

    In the SQL, the column name can also be renamed by using the RENAME COLUMN statement.

    Syntax to rename the column of a table is as follow :

    ALTER TABLE table_name
    RENAME COLUMN old_column_name TO new_column_name

    Example to rename the column of a table.

    ALTER TABLE persons_table
    RENAME COLUMN city TO street; 

    In this query, the column name city is renamed to the street.
    alter-table-query

    In the below image, the column name CITY is used before using the RENAME COLUMN statement.
    retrieved-data

    In the below query, the column_name 'CITY' has been renamed to the 'STREET'.
    rename-column-name

    The MODIFY keyword is used to modify the column in the existing table.

    Syntax to MODIFY the column :

    ALTER TABLE table_name
    MODIFY column_name column_type;

    Example to MODIFY the column :

    ALTER TABLE Persons_table
    MODIFY city char(10);

    According to this query, only 10 characters can be entered in the city column.
    modified

    DROP TABLE in SQL:

    The DROP TABLE is used to remove the data inside the table permanently. If you retrieve the data from the dropped table, you will never get any data from the table and you will find an error.

    The syntax of the DROP TABLE :

    DROP TABLE table_name;

    Example of the DROP TABLE :

    DROP TABLE persons_table;

    According to this query, the persons_table has been completely dropped along with its data.
    drop-table

    DROP the columns in SQL:

    The DROP can also use to delete the number of columns from the existing table.

    Syntax to DROP the multiple columns from the table:

    ALTER TABLE table_name
    DROP (column_1, column_2.....); 

    Example to DROP the multiple columns from the table:

    ALTER TABLE persons_table
    DROP (city); 

    retrieved-data

    In this query, the city column is dropped using the DROP statement.

    alter-column-name

    In the below table, all the data has been retrieved from the persons_table after dropping the city column.
    drop-columns

    WHERE Statement in SQL

    When the SELECT statement is used in a query, all the rows of that table is seen as an output. But we need only some data as per our conditions. To get the data from the table that satisfy one or more conditions, the WHERE statement is used.

    WHERE Statement is used to retrieve the particular data which satisfies the given condition. This helps to filter the data to meet certain conditions.

    The syntax for the WHERE statement :

    SELECT column1, column2, ... 
    FROM table_name
    WHERE condition;

    Example of WHERE statement to retrieve the particular data :

    SELECT * FROM Persons_table
    WHERE City='Bangalore';

    retrieved-data

    The 'CITY' column is selected where the 'CITY' column contains only 'Bangalore' as per the given condition.
    where-statement

    UPDATE Statement in SQL

    The UPDATE keyword is used to change the existing records in a table according to the condition.

    (multiple rows)
    Syntax of the UPDATE keyword :

    UPDATE table_name
    SET Column_name=Value
    WHERE Condition;

    Example of UPDATE keyword :

    UPDATE persons_table
    SET Last_name='Sharma'
    WHERE Person_id=2;

    retrieved-data

    The below image updates the last_name of the person whose Person_id belongs to 2 in the persons_table.

    update-statement


    The below table has been updated on the last_name from 'Shetty' to 'Sharma'.

    updated-data


    DELETE Statement in SQL

    This statement is used to delete the records in the table. We can delete single or multiple records according to the given condition.

    Syntax of the DELETE statement for a single column:

    DELETE FROM table_name WHERE condition;

    Example of the DELETE statement for a single column :

    DELETE FROM persons_table WHERE person_ID=5;

    retrieved-data

    In the below image, the entire row is deleted where person_id belongs to the 5.
    dalete-statement

    In the below table, all the details are retrieved from the Persons_table but, after deleting the column where person_id belongs to the 5.
    deleted-column

    ORDER BY statement in SQL

    The ORDER BY statement is used to retrieve the data in ascending or descending order. We use the ASC keyword to retrieve the data in ascending order and the DESC keyword to retrieve the data in descending order.

    Syntax to retrieve the data in ascending order :

    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... ASC;

    Example to retrieve the data in ascending order :

    SELECT *
    FROM persons_table 
    ORDER BY city ASC;

    retrieved-data

    In the below output, the column 'CITY' is arranged in alphabetical order. And all the columns which are present in the table are changed according to the column 'CITY' in ascending order.
    asc-order

    Syntax to retrieve the data in descending order :

    SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... DESC;

    Example to retrieve the data in descending order :

    SELECT *
    FROM persons_table 
    ORDER BY city DESC;

    In the below output, the column 'CITY' is arranged in alphabetical reverse order. And all the columns which are present in the table are changed according to the column 'CITY' in descending order.
    desc-order

    GROUP BY statement in Oracle SQL

    The GROUP BY function is used to group similar data by using some statements.

    Syntax of the GROUP BY statement :

    SELECT column_name(s)
    FROM table_name
    GROUP BY column_name(s);

    Example of GROUP BY statement :

    SELECT COUNT(last_name), city
    FROM persons_table
    GROUP BY city;
    

    retrieved-data

    In the below output, the SQL statement lists the number of last_name in each 'city column of the persons_table.
    group-by-statement

    HAVING Statement in Oracle SQL

    HAVING statement uses the GROUP BY statement. The HAVING statement is used to filter the data which are retrieved by the GROUP BY statement. When the GROUP BY statement is not used by the HAVING statement, the HAVING statement is applied to the entire result as a single group.

    Syntax of the HAVING keyword :

    SELECT column_name(s)
    FROM table_name
    GROUP BY column_name(s)
    HAVING condition;

    Example of the HAVING statement :

    SELECT COUNT(age), Gender
    FROM Persons_table
    GROUP BY Gender
    HAVING COUNT(age)>1;

    retrieved-data

    This SQL query lists the number of age of every gender. Here each gender is having more than 1 person.
    having-statement-1

    SELECT COUNT(age)
    FROM Persons_table
    HAVING COUNT(age)>1;

    This query has no GROUP BY statement. So the condition of the HAVING statement is applied to all the rows.

    having-without-group-by

    0 results
    Comment / Suggestion Section
    Point our Mistakes and Post Your Suggestions