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)
- 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 : 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.
- 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.
- 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
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.
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.
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.
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.
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.
- Double click on the connection sys. Now the connection will start.
- 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.
- 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.
- 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.
- 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.
- 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.
- Select the alter PLUGGABLE DATABASE open; this command is used to open the pluggable database. Now click the run button.
- 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.
- 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.
- Now, search in your pc for tnsnames.ora file, that opens in the notepad. Copy the given data.
- 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.
- Go to New connection which is showing in the + symbol.
- 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.
- Now the hr table has been unlocked. And click on the + symbol of hr.
- After this, click on the + symbol of Table.
- You will find the tables available in the hr schema as shown below.
- The pluggable database loses its connection when the system is restarted so the error will occur as below.
- 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.
Now, the database is opened. You can start working in the SQL Developer. Let us study about queries in the next article.