• Introduction to SQL

Introduction to SQL

SQL stands for Structured Query Language. This is a computer language used for storing, manipulating, and retrieving data that is stored in a relational database. It is used to interact with a database.

SQL is a standard language for relational database management systems like MySQL, SQL Server, and Oracle according to ANSI (American National Standards Institute). Some features of the SQL standard are implemented differently in different database systems.

History of SQL

SQL was developed by IBM in the early 1970s. SQL became an ISO ( International Standard for Organization) standard in 1987. Initially, it was called SEQUEL (Structured English Query Language) and later, changed to SQL (Structured Query Language).

Before knowing the SQL, let us have some important information about the basic terms of SQL.

Basic SQL terms

  • Data : Data is the set of information stored by the computer.
  • Database : A database is a grouped collection of data that makes data management easy. It supports electronic storage and manipulation of data. It is developed using formal design and modeling techniques.
  • DBMS : Stands for Database Management System. This is a software system that uses a standard method of storing, retrieving, defining, and managing the data in a database.
  • Tables : It is a collection of the related data which are organized in the rows and columns.
    Example : (reducing white frame)
    table
  • Data models : Data models define how data is connected and how they are processed and stored inside the system.
    The data model contains ERM. ERM stands for Entity Relational Model. It is a theoretical and conceptual way of showing data relationships in software development. ERM contains three components. they are,
    • Entity
    • Attributes
    • Relationships
      database_3

  • Entity : It is a real-world object which can be easily identifiable. For example, in a company database table, employee, location, and department columns can be said as an entity.
    company_database_2
  • Attributes : This defines the information of the entity. For example, in the employee_database table, employee id, employee name, date of joining can be considered as attributes.
    attribute_1
  • Relationships: It is a situation that exists between two associational database tables. It helps to minimize redundant data. There are three types of relationships. they are,
    • one-to-one relationship
    • one-to-many relationship
    • many-to-many relationship

one-to-one relationship:

Where the single record in the first table can be linked to the only one record of another table is called as one-to-one relationship.

Example : The employee_id 003 in the employee table is related to the employee_id 003 of the department table.
one_to_one_table_1

One-to-many relationship:

Where the single record in the first table can be related to one or more records of another table is called a one-to-many relationship.

Example :
one_to_many_table

Many-to-many relationship:

Where one or more records in the first table can be related to one or more records of another table is called as many-to-many relationship.

Example:
many_to_many

Let us understand how to create, populate, and query databases using the Oracle database. Before that, let us understand how to install the Oracle Database.


Without Installation of oracle database

SQL can be run in two ways.

  • Without installation of Oracle Database
  • With installing Oracle Database

Let us understand how to work in the oracle database online or without installation of Oracle Database. Before that, create an account in the Oracle.

  • Go to https://livesql.oracle.com/apex/livesql/file/index.html
  • You will find this page. Click on start coding now.
    sql-online
  • you will get space for work.
    sql-worksheet
  • Click on Schema and select on My Schema.sql-a4
  • Select Human Resource (HR) in My Schema. And you will find a page as given below.(theory)sql-a5
  • Go to the SQL worksheet and start to write the queries. and click on the Run button.
    sql-worksheet_run_button
  • you will get an output as follow.
    sql-output-of-querry

  • Go to https://www.oracle.com/index.html
  • Enter the email address and the password of your oracle account.
    sql-oracle-account
  • You will get a page like below. Go to Winx64_12201_database.zip.
    1-sql-download-1
  • Now click on i reviewed and accept the Oracle License Agreement. Again click on Download Winx64_12201_database.zip.
    sql-download-19

    After this, the Oracle Database will get downloaded. Let us know some installation process of this Oracle Database.
  • Select Winx64_12201_database. And extract the file.
    a1-sql-3a
  • when you extract this file, you will find a file name database. Select this folder.
    a1-sql-4a
  • click on setup.
    1-sql-3
  • You find a page like this. Wait for a few seconds after this page.
    1-sql-download-5
  • Tou will get a page like this after a few seconds. Uncheck the button of I wish to receive security updates via My Oracle Support. And click Next.
    1-sql-download-6
  • Select Create and configure a database and click on next.
    1-sql-download-7
  • Click on Desktop class and click on next.
    1-sql-download-8
  • Browse the Oracle base, You can change the C drive to F drive if there is no space in C drive.
    • set Database edition to enterprise edition(6.0G3),
    • set Character set Unicode(AL32UTF8),
    • set the password, for example, system. And click next. The password of Typical installation is the most important in the upcoming step.sql-installation-10
  • You will get a page like this, click on the Yes button and click on the Next button.
    sql-installation-11
  • Soon after the previous step, the installation starts. It may take some time.
    sql-installation-12
  • Click on the Install button.
    sql-summary-3
  • Now the installation process begins. Wait for a few minutes.
    sql-installation-13
  • Now the installation has been completed and click on the Close button.
    sql-installation-completed
  • Now go to start menu and search for SQL plus. Enter the user-name as sys as sysdba the password should be entered which is used in the typical installation. Now it has been connected to Oracle Database. Now click enter.
    sql-installation-15
  • Type show con_name and click the enter button.
    sql-plus-1
  • Now go to start menu and search for SQL developer. And wait for loading.
    sql-installation-17
  • You can see the page like this. Click on the + symbol and click on the connections.
    sql-developer
  • Now Enter the connection name, user name, and password which are used in the typical installation. Save the password. In the connection type, select the TNS. Set the Role as default. Click on Network alias and set the name of the container database. And finally, click the connection.
    sql-connetion
  • After the previous step, the connection to the database has been completed.
    sql-installation-24

Unlocking HR Account

Before unlocking the HR account, let us know the meaning of the Container Database and Pluggable Database and schema.

Container database (CDB) : The database which is created when that database supports Oracle's multitenant option is known as container database (CDB). It's also called the ROOT container and is the CDB$ROOT is available within the data dictionary views of the CDB. A CDB can contain up to 252 of the pluggable databases (PDBs).

Multitenancy is the new feature introduced in Oracle 12c and it is the concept of combining the Pluggable Databases (PDB) into a single database server.

Pluggable Database (PDB): This is a portable collection of schemas, schema objects, and non-schema objects which appear to an Oracle Net client as a non-container Database (non-CDB). As the CDB contains many PDBs, they occur on the network as a separate database.

Schema: It a collection of database objects which includes tables, views, triggers, stored procedures, indexes, etc. A schema always belongs to only one database.

Now let us understand how to unlock the HR account.

  • There will be an attachment script called steps to unlock hr.sql. Search for steps to unlock hr.sql in your pc that will open in the notepad as followed. Copy all those commands and open the SQL developer.
    sql-hr-unlock-9
  • Double click on the connection sys. Now the connection will start.
    sql-hr-unlock-10
  • Paste the commands in the sys' worksheet which are copied in the notepad. Select the show con_name command. This command shows the name of the current database which we are using and click the run button to see the name of the database in the output.

1-hr-unlock

  • Select the show name, con_id from v$pdbs; in this command, v$pdbs stores the pluggable database available in the container database. v$pdbs is a dictionary view. The dictionary is a table and views that contain a lot of information for the database.
    Now click the run button to get an output.
    2-hr-unlock
  • Select the Alter session set container=orclepdb, this command is used to move the sys user from the container database to the pluggable database. Now click the run button to get an output.
    3-hr-unlock
  • Select the show con_name command, to make sure that the sys user has been moved from the container database to the pluggable database. And click the run button. See the output at the bottom. The con_name has been changed from CDB$ROOT to ORCLEPDB.
    4-hr-unlock
  • Select the select name, open_mode from v$pdbs; this command helps us to know the name and the open mode of the pluggable database. Now click the run button.
    5-hr-unlock
  • Select the alter PLUGGABLE DATABASE open; this command is used to open the pluggable database. Now click the run button.
    6-hr-unlock
  • Select the select * from all_users; this command is used to find out the users available in the pluggable database. Now click the run button.
    sql-hr-unlock-7
  • Select the ALTER USER hr identified by hr account unlock; this command is used to alter the user HR which contains the HR schema. Here, the password is hr. Now click the run button.
    sql-hr-unlock-8
  • Now, search in your pc for tnsnames.ora file, that opens in the notepad. Copy the given data.
    tnsname-11
  • Paste and edit the data as ORCLE to ORCLEPDB and orcle to orclepdb in the pasted part of the data. We add the pdb because just to know that is a pluggable database. Now save that file.
    tnsname-13
  • Go to New connection which is showing in the + symbol.
    10-hr-unlock
  • Enter the connection name as hr. Username as hr and password as hr. After this, click on the save password.
    Give the connection type as TNS. Network alias as the ORCLEPDB option which is created by us in the tnsnames.ora file. This gives the connection to the pluggable database. And finally, give the connection.
    11-hr-unlock
  • Now the hr table has been unlocked. And click on the + symbol of hr.
    12-hr-unlock

  • After this, click on the + symbol of Table.
    13-hr-unlock
  • You will find the tables available in the hr schema as shown below.
    14-hr-unlock
  • The pluggable database loses its connection when the system is restarted so the error will occur as below.
    pluggable-database
  • So, the SQL plus is used to overcome this error by giving the alter pluggable database all open; command and the output are seen as given below.
    pluggable-database-3
    Now, the database is opened. You can start working in the SQL Developer. Let us study about queries in the next article.



Recommended Readings

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