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
1) GRANT – allow users access privileges to database
2) REVOKE – withdraw users access privileges given by using the GRANT command.
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
User table would be like
SELECT name , username from person p INNER JOIN user u on (p.primarykey = u.person_id);
Select name , username from person p INNER JOIN user name , username from person p ,user u
where p.primarykey = u.person_id;
With p as (select name , primarykey as pk from person),
u as (select person_id as pk , username from user)
Select name , username from person p INNER JOIN user * from p,u where p.pk = u.pk;