11. Database (Introduction, SQLPUS, Login, Objects, DQL (Select), Pipeline Operator)

DBMS :-

Data:- Any meaningful information is called data.

Any place where we can store data is called database.

We can call Excel or Word or even paint a database as it can stores data. But they are unstructured database. Like in word we store data in documents. There to search for a specific data we would have to search all the paragraph. So they are not widely used for the purpose of storing data.

In a structured database data is stored in rows and columns.

Each row in a structured database is records. Each column is a field.

MS is management system which can manage the database. Management system in something through which we can manage the data, we can edit the data, we can update the data, we can delete the data within a fraction of a second. For that we need a language called SQL.

With a dbms we can't form relation between multiple database. So there came the concept of rdbms.

Any application that follows 12 rules of cordds can be called a rdbms. As excel or words is not following them we can't call them rdbms.

In excel we can directly change the data. But we can't in a database. For that we would need SQL.

The things we do in a database like entering some values, retrieving some values, changing the data are called transaction.

All transaction follows property of ACID :-

A :- Atomicity:- Any transaction that begins must begin. In between if its interrupted is not a transaction.

C :- Consistency :- The volume of data before the transaction and after the transaction is the same.

I :- Isolation:- All transactions are Isolated to each other.

D :- Durability :- When I saved data into the database it will remain the same unless someone changes it.

ps -ef | grep pmon

We can use a tool called SQLPLUS to connect to a database.

SQL :- 5 types

To know the databases in the system we need to read a file.

This file reside under etc directory. This file is not inbuilt. We we install oracle inside linux it will create automatically.

Now we see there are 3 databases dev, prod, orcl. Now lets try to open orcl. To open a database we need to use . oraenv.

This is showing because the orcl database is not running.

In linux every thing is a process. A database running is also a process. Lets see which database is running.

pmon is process monitor. We see that only prod is running.

Lets open it.

hr is the username and oracle is the password.

SQL is not case sensitive.

Objects in database are:

Table

View

Procedure

Trigger

Function

We only have to deal with Table and View.

Select * from tab; :- tab is a table which will store all the object or tables information of the user. ';' explain to the system that the query is completed.

The answer is in an unstructured format. Because the first column tname is taking so much space. For that

col tname for a20 :- col is the short form of column. tname is the name of the column. for a20 will only take 20 characters for the column tname.

Designing:

r :- r means repeat execute last query.

l :- Display the last queries used.

set pagesize 150 :- set the page size over 150.

set line 100 :- set that the screen will display 100 characters in a single line so that there are no overlap.

desc tablename :- Description of the table. Like the name of its fields (columns), their types, and whether the values of them can be null or not.

.........................................................................................................................................................

Note: In real world applications sql commands is written within the business logic layer.

.........................................................................................................................................................

Select employee_id, salary from employees:

Select employee_id as empno, salary as "Montly Salary" from employees;

Display these 2 couluns but with these new names as the heading. When using any special character like (" ", "*" etc) as an alias put it inside double quote. Also without double quotes it will only display in capital letter format. Inside double quotes any format we give will display.

Select employee_id empno, first_name as "First Name", last_name "Last name", salary as "Montly Salary", (salary *10)/100 "PF" from employees;

Select employee_id empno, first_name || last_name "Full name", salary as "Montly Salary", (salary *10)/100 "PF", salary * 4 "Queterly Salary" from employees where salary > 10000;

Select employee_id as empno, first_name || last_name "Full Name", salary "Montly Salary" from employees;

|| (double pipeline ) is called concatenate operator.

This one wil not have a space between first name and last name. So,

Select employee_id empno, first_name || ' ' || last_name "Full name", salary as "Montly Salary", (salary *10)/100 "PF", salary * 4 "Queterly Salary" from employees where salary > 10000;