What is self-join and what is the requirement of self-join?
A self-join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.
What is ACID property in a database?
ACID property is used to ensure that the data transactions are processed reliably in a database system.
A single logical operation of a data is called transaction.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.
Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.
Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.
When SQL appeared?
It appeared in 1974. SQL is one of the often used languages for maintaining the relational database. SQL. In 1986 SQL become the standard of American National Standards Institute (ANSI) and ISO(International Organization for Standardization) in 1987.
What are the disadvantages of not performing Database Normalization?
The major disadvantages are:
- The occurrence of redundant terms in the database which causes the waste of the space in the disk.
- Due to redundant terms inconsistency may also occur id any change will be made in the data of one table but not made in the same data of another table then inconsistency will take place, which will lead to the maintenance problem and effects the ACID properties as well.
What is Data Control Language?
Data control language allows you to control access to the database. DCL is the only subset of the database which decides that what part of the database should be accessed by which user at what point of time. It includes two commands GRANT and REVOKE.
GRANT: to grant the specific user to perform a particular task
REVOKE: to cancel previously denied or granted permissions.
What is an Index in SQL?
SQL indexes are the medium of reducing the cost of the query as the high cost of the query will lead to the fall in the performance of the query. An index is used to increase the performance and allow faster retrieval of records from the table. Indexing reduces the number of data pages we need to visit to find a particular data page. Indexing also has a unique value that means that the index cannot be duplicated. An index creates an entry for each value, and it will be faster to retrieve data. For example, suppose you have a book which carries the details of the countries, and you want to find out the information about India than why you will go through every page of that book you could directly go to the index, and then from index you can go to that particular page where all the information about India is given.
What is a Database?
A Database is an organized form of data. The database is the electronic system which makes data access, data manipulation, data retrieval, data storing and data management very easy and structured. Almost every organization uses the database for storing the data due to its easily accessible and high operational ease. The database provides perfect access to data and lets us perform required tasks.
The Database is also called a structured form of data. Due to this structured format, you can access data very easily.
What is the difference between SQL and PL/SQL?
SQL or Structured Query Language is a language which is used to communicate with a relational database. It provides a way to manipulate and create databases. On the other hand, PL/SQL is a dialect of SQL which is used to enhance the capabilities of SQL. It was developed by Oracle Corporation in the early 90’s. It adds procedural features of programming languages in SQL.
In SQL single query is being executed at once whereas in PL/SQL a whole block of code is executed at once.
SQL is like the source of data that we need to display on the other hand PL/SQL provides a platform where the SQL the SQL data will be shown.
SQL statement can be embedded in PL/SQL, but PL/SQL statement cannot be embedded in SQL as SQL do not support any programming language and keywords.
What is the difference between NULL value, zero and blank space?
A NULL value is not the same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable.’ On the other hand, zero is a number, and a blank space is treated as a character.
The NULL value can be treated as unknown and missing value as well, but zero and blank spaces are different from the NULL value.
What is Inner Join in SQL?
Inner join returns rows when there is at least one match of rows between the tables. INNER JOIN keyword joins the matching records from two tables.