PostgreSQL

Database Section

What is a database?

Database is the collection of data and information . It is organized in such a way that it can be easliy accessed, managed and updated.

What is DBMS?

DBMS mainly stores data in the files. There are tables but there is no relation between the tables. It stored in the hierarchical form. For eg a tree structure.

What is RDBMS?

RDBMS stored data in the tabular form. Tables having relations between each other. It bases on the relational model. For eg foreign keys  structure.

What are different types of databases?

SQL is an common language which is used in all the databases. There are different types of databases like MySQL,MySQLi, Postgres , NoSQL.

How internally database works ?

Database uses B+ trees. B+ tree is a special data structure allowing to efficiently store , manipulate data in a large sorted dictionary on a block storage device.More data in table more physical space will be required.

B+ tree is an algorithm (uses in all type of databases) allowing not just quickly find the entries in phone book, but also:

Manipulation(Add,edit and delete ) on these entries..

Your main database is some root folder (tree like hierarchy), tables are its sub folder and indexes are files in the sub folder.

Database systems typically store data in fixed size blocks in the memory called pages.

What are different type of statements ?

1) DDL it is used to define data structures.

CREATE – to create objects in the database

ALTER – alters the structure of the database

DROP – delete objects from the database

TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed

COMMENT – add comments to the data dictionary

RENAME – rename an object

 

2) DML  it is used to manipulate data itself.

1. SELECT – retrieve data from the a database  

2. INSERT – insert data into a table.

3. UPDATE – updates existing data within a table.

4. DELETE – deletes all records from a table, the space for the records remain.

5. MERGE – UPSERT operation (insert or update).

6. CALL – call a PL/SQL or Java subprogram     

7. EXPLAIN PLAN – explain access path to data 

8. LOCK TABLE – control concurrency

 

3) DCL

1) GRANT – allow users access privileges to database

2) REVOKE – withdraw users access privileges given by using the GRANT   command.

 

4) TCL

COMMIT – commits a Transaction

ROLLBACK – rollback a transaction in case of any error occurs

SAVEPOINT – to rollback the transaction making points within groups

SET TRANSACTION – specify characteristics for the transaction.

How select internally works ?

Firstly it gets complied , and after followed by the generation of expression tree. At first stage it checks whether the elements in query exist or not in the tables. If any error in query then it raise exception in first phase

Then next A SQL query is different from a program in that it does not say HOW to do something, just WHAT THE RESULT . So you get a query analyzer in to decide how best to approach this result. It tries several approaches to check which query is fast on the cost based

And after it get executed and internally runs a implicit cursor and give whole data

What is cursor and types of it?

Cursor is a custom data type in the database which help to iterate over the data.

It is of two types:

Implicit cursor – Implicit cursor cannot be controlled manually. It automatically opens and closes with the select statement

Explicit cursor – We open explicit cursor, fetches data and close it. If some exception occurred we can handle it

How can we write select SQL different types?

Let’s take a example of person table and user table

Person table would be like

primarykey name
   

User table would be like

primarykey person_id username
     

1st way

2nd way

3rd way

Please follow and like us:

Post Author: Ambrish Rajput

Leave a Reply

Your email address will not be published. Required fields are marked *