Top 100+ Common Sql Queries Interview Questions And Answers
Question 1. What Is The Difference Between Oracle, Sql And Sql Server ?
Oracle is primarily based on RDBMS.
SQL is Structured Query Language.
SQL Server is every other tool for RDBMS provided through MicroSoft.
Question 2. Why You Need Indexing? Where That Is Stored And What You Mean By Schema Object? For What Purpose We Are Using View?
We can not create an Index on Index.. Index is stoed in user_index desk. Every item that has been created on Schema is Schema Object like Table, View and so on. If we want to share the precise statistics to various customers we must use the digital table for the Base desk. So that is a view.
Indexing is used for faster seek or to retrieve statistics quicker from numerous table. Schema containing set of tables, essentially schema approach logical separation of the database. View is crated for faster retrieval of records. It's custom designed digital desk. We will create a single view of a couple of tables. Only the disadvantage is..View desires to be get refreshed for retrieving up to date statistics.
PL/SQL Interview Questions
Question three. Difference Between Stored Procedure And Trigger?
we will call stored method explicitly.
But cause is automatically invoked when the motion described in trigger is done.
Ex: create cause after Insert on
this trigger invoked after we insert something on that desk.
Stored procedure can not be inactive however trigger may be Inactive.
Triggers are used to provoke a specific hobby after fulfilling positive circumstance.It want to outline and may be permit and disable in step with need.
Question four. What Is The Advantage To Use Trigger In Your Pl?
A trigger is a database object immediately related to a particular desk. It fires on every occasion a selected declaration/kind of declaration is issued in opposition to that desk. The sorts of statements are insert,update,delete and question statements. Basically, cause is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For example:
A database column cannot carry PSEUDO columns as standards in which a trigger can.
A database constraint can't refer vintage and new values for a row wherein a cause can.
Triggers are fired implicitly on the tables/views on which they're created. There are numerous blessings of the use of a trigger. Some of them are:
Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a desk then we will write a trigger on the table that gets fired implicitly each time DML assertion is done on that desk.
Another purpose of using triggers may be for automatic updation of 1 or more tables on every occasion a DML/DDL announcement is performed for the table on which the cause is created.
Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements ought to now not be allowed on a selected table after office hours. For enforcing this constraint Triggers ought to be used.
Triggers can be used to submit statistics approximately database occasions to subscribers. Database event may be a device occasion like Database startup or shutdown or it is able to be a person even like User loggin in or consumer logoff.
Question 5. What The Difference Between Union And Union All?
Union will take away the reproduction rows from the result set whilst Union all does'nt.
C++ Interview Questions
Question 6. What Is The Difference Between Truncate And Delete Commands?
Both will bring about deleting all the rows in the table .TRUNCATE name can't be rolled again as it's miles a DDL command and all memory space for that desk is released lower back to the server. TRUNCATE is an awful lot quicker.Whereas DELETE call is an DML command and can be rolled returned.
Question 7. Which System Table Contains Information On Constraints On All The Tables Created ?
device table contains data on constraints on all the tables created
SQL Database Tutorial SQL Database Interview Questions
Question 8. Explain Normalization ?
Normalisation method refining the redundancy and maintain stabilization. There are Four forms of Normalization :first regular forms, 2nd normal forms, third normal forms and fourth Normal bureaucracy.
Question 9. How To Find Out The Database Name From Sql*plus Command Prompt?
Select * from global_name;
This will deliver the database name which u r currently linked to.
MySQL Interview Questions
Question 10. What Is The Difference Between Sql And Sql Server ?
SQLServer is an RDBMS similar to oracle,DB2 from Microsoft
Structured Query Language (SQL), pronounced "sequel", is a language that offers an interface to relational database systems. It was advanced by way of IBM in the 1970s to be used in System R. SQL is a de facto fashionable, in addition to an ISO and ANSI general. SQL is used to carry out numerous operations on RDBMS.
Question eleven. What Is Difference Between Co-associated Sub Query And Nested Sub Query?
Correlated subquery runs as soon as for every row decided on with the aid of the outer question. It incorporates a reference to a cost from the row selected by way of the outer question.
Nested subquery runs handiest as soon as for the complete nesting (outer) question. It does no longer comprise any reference to the outer question row.
select e1.Empname, e1.Basicsal, e1.Deptno from emp e1 wherein e1.Basicsal = (pick max(basicsal) from emp e2 in which e2.Deptno = e1.Deptno)
pick empname, basicsal, deptno from emp in which (deptno, basicsal) in (pick deptno, max(basicsal) from emp group through deptno)
DB2 Using SQL Interview Questions
Question 12. What Operator Performs Pattern Matching?
Pattern matching operator is LIKE and it has to used with two attributes:
% means matches 0 or more characters and
_ ( underscore ) method matching precisely one character
PL/SQL Interview Questions
Question thirteen. What Is Cluster.Cluster Index And Non Cluster Index ?
Clustered Index:- A Clustered index is a unique sort of index that reorders the way records in the desk are bodily stored. Therefore table may additionally have only one clustered index.Non-Clustered Index:- A Non-Clustered index is a special form of index wherein the logical order of the index does not match the bodily saved order of the rows inside the disk. The leaf nodes of a non-clustered index does now not includes the facts pages. As a substitute the leaf node incorporates index rows.
DB2 Using SQL Tutorial
Question 14. What Is The Difference Between A "in which" Clause And A "having" Clause?
"Where" is a kind of limit statement. You use where clause to limit all the information from DB. Where clause is using before result retrieving. But Having clause is the usage of after retrieving the facts. Having clause is a form of filtering command.
Question 15. What Structure Can You Implement For The Database To Speed Up Table Reads?
Follow the rules of DB tuning we ought to:
properly use indexes ( extraordinary sorts of indexes)
properly locate one of a kind DB items throughout distinct tablespaces, documents and so forth.
Create a unique space (tablespace) to find a number of the facts with special datatype ( as an instance CLOB, LOB and …)
PostgreSQL Interview Questions
Question 16. What Are The Tradeoffs With Having Indexes?
Faster selects, slower updates.
Extra garage space to store indexes. Updates are slower due to the fact similarly to updating the table you need to replace the index.
DocumentDB SQL Tutorial
Question 17. What Is "normalization"? "denormalization"? Why Do You Sometimes Want To Denormalize?
Normalizing facts method doing away with redundant statistics from a table and organizing the statistics in order that destiny modifications to the table are simpler.
Denormalization manner permitting redundancy in a table. The major advantage of denormalization is advanced performance with simplified facts retrieval and manipulation. This is accomplished by means of discount within the quantity of joins wanted for records processing.
SQL DBA Interview Questions
Question 18. What Is A "constraint"?
A constraint allows you to use simple referential integrity checks to a desk.
There are 4 primary varieties of constraints:
PRIMARY/UNIQUE - enforces specialty of a specific table column. But by default number one key creates a clustered index on the column, where are unique creates a non-clustered index with the aid of default. Another predominant distinction is that, primary key would not permit NULLs, but unique key allows one NULL handiest.
DEFAULT - specifies a default cost for a column in case an insert operation does not provide one.
FOREIGN KEY - validates that each fee in a column exists in a column of some other table.
CHECK - tests that every value stored in a column is in some precise list. Each kind of constraint performs a specific sort of movement. Default isn't always a constraint. NOT NULL is one more constraint which does no longer permit values in the specific column to be null. And also it the only constraint which isn't always a table level constraint.
C++ Interview Questions
Question 19. What Types Of Index Data Structures Can You Have?
An index allows to faster seek values in tables. The three maximum commonly used index-types are: -
B-Tree: builds a tree of possible values with a list of row IDs that have the leaf cost. Needs quite a few space and is the default index kind for most databases.
Bitmap: string of bits for every viable value of the column. Each bit string has one bit for every row. Needs simplest few area and could be very fast.(but, area of value can not be huge, e.G. SEX(m,f); diploma(BS,MS,PHD).
Hash: A hashing algorithm is used to assign a hard and fast of characters to symbolize a text string consisting of a composite of keys or partial keys, and compresses the underlying statistics. Takes longer to construct and is supported with the aid of highly few databases.
Question 20. Why Can A "institution By" Or "order By" Clause Be Expensive To Process?
Processing of "organization through" or "order via" clause regularly requires introduction of Temporary tables to process the effects of the question. Which depending of the end result set can be very pricey.
SQL Interview Questions
Question 21. What Is A Sql View?
An output of a query can be saved as a view. View acts like small desk which meets our criterion. View is a pre-complied SQL query which is used to select statistics from one or more tables. A view is sort of a table however it would not physically take any space. View is a superb way to provide data in a specific layout in case you use that question pretty regularly. View also can be used to restrict customers from gaining access to the tables without delay.
Question 22. What Is Group By?
The GROUP BY keywords has been delivered to SQL because combination functions (like SUM) return the mixture of all column values whenever they are called. Without the GROUP BY functionality, finding the sum for every character group of column values turned into not viable.
Question 23. What Are Defaults? Is There A Column To Which A Default Can't Be Bound?
A default is a cost with a purpose to be utilized by a column, if no cost is provided to that column while putting information. IDENTITY columns and timestamp columns can't have defaults certain to them.
Explain one of a kind isolation levels:
An isolation stage determines the degree of isolation of information between concurrent transactions. The default SQL Server isolation stage is Read Committed. Here are the other isolation stages (within the ascending order of isolation):
MYSQL DBA Interview Questions
Question 24. What Type Of Index Will Get Created After Executing The Below Statement?
CREATE INDEX myIndex ON myTable(myColumn)
Non-clustered index. By default a clustered index receives created on the primary key, until targeted in any other case.
SQL Database Interview Questions
Question 25. What's The Maximum Size Of A Row?