Basic SQL Interview Questions and answers

In this article I am sharing SQL Interview Questions For freshers and experienced developers. i have been sharing all types technology interview Questions keep follow me for updated Interview Questions. In this page we will learn SQL Interview Questions as follows

1) What is SQL?
Ans: SQL means Structured Query Language(SQL) is a language designed specially for communicating with databases.After stored data in the database we can easily update records,deleting records,creating and modifying tables. Using SQL it is easier to manage database systems without having to write substantial amount of code.

2)  Why SQL is important?

Ans:  SQL is a Query processing language and is the most important data processing language in use today. It is not a complete programming language like JAVA or C# but a data sub language used for creating and processing database data and metadata. All DBMS products today use SQL.

3)  What are the Advantages of SQL?

Ans: SQL is easy to learn.SQL is actually a very powerful query language and by using its language elements you can perform very complex database operations. This is the Query language will enable programmers to interact with any database like ORACLE,SQL,MYSQL etc..

4) What is difference between IN and BETWEEN that are used inside a WHERE clause?

Ans: The BETWEEN clause is used to fetch a range of values,whereas the IN clause fetches data from a list of specified values.

5) What are Wildcard characters in SQL?

Ans: The symbol '%'(for a string of any character) and '_'(for any single character) are the two wild card characters used in SQL.

6) What is primary key?

Ans: A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table,its primary key may not be assigned to any new rows in the future. No two rows can have the same primary key value.

7) What is Composite Key?

Ans:  A composite primary key is a type of candidate key,which represents a set of columns whose values uniquely identify every row in a table. For example: if "employe_id" and "employee_name" in a table is combined to uniquely identify a row its called a composite key.

8) What is Foreign Key?

Ans: When a one table's primary key filed is added to a related many table in order to create the common filed which relates the two tables,it is called a foreign key in the many table.

9) What is Unique Key?

Ans: Unique key is same as primary with the difference being the existence of null. Unique key filed allows one value as NULL value.

10) What is Candidate Key?

Ans: A table may have more than one combination of columns that could uniquely identify the rows in a table,each combination is a candidate key.

11) What is sqLite?

Ans: SQLite is an relational database management system contained in a relatively small C library. SQLite is a public domain project developed.

12) What is the function of truncate?

Ans: With the Truncate statement,you can delete all the content in the table,but keep the actual table intact and ready for further use.

13) What is the function of ALTER?

Ans: The ALTER Statement permits the user to modify an existing object in various ways. For example, by adding a column to an existing table.

14) What is SQL insert statement?

Ans: SQL INSERT statement is used to add rows to a table.

15) What is Join?

Ans: In order to avoid data duplication,data is stored in related tables. Join keyword is used to fetch data from related tables. Join return rows when there is at least one match in both table.

16) What is Stored Procedure?

Ans: Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs,process them and send back output.

17) What are the advantages of Stored Procedure?

Ans:  Stored Procedures are pre compiled and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure,round trip time to execute multiple queries from source code to database and back is avoided.

18) What is view?

Ans: The views are virtual tables. Unlike tables that contain data,views simply contain queries that dynamically retrieve data when used.

19) What is difference between cluster and non cluster index?

Ans:  A cluster index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.

A non cluster index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.

20) What is difference between SQL and PL/SQL?

Ans: SQL is a structured Query Language whereas PL/SQL is a procedural Query Language. PL/SQL is a collection of queries.

21) What are the ACID Rules?

Ans: Some of the ACID rules stated are described as follows:

Atomicity: Either transaction should be done or they should be stopped
Consistency: Data should never be in a contradictory state
Isolation: Two simulaneous or preceding results should never interfere with each other.
Durability: Results or Data shouldn't be discarded even during a crash of the system.

22) What is Transaction?

Ans: Database Transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through

23)  What the advantages of views?

Ans: The view can be used to hide some of the columns from a table. Views can provide access restriction,since data insertion,update and deletion is not possible with the view.Views don't store data in physical location

24) What is Union,minus and intersect commands?

Ans: Minus operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return by both the queries. 


25) What is use of GROUP BY and HAVING clause?

Ans: The GROUP BY partitions the selected rows on the distinct values of the column on which the group by has been done. The Having selects groups which match the criteria specified.


Let me know your comments on this post and keep follow me for updated interview questions and share this post to your friends.

Watch Video: Fundamental SQL Interview Questions for freshers

Introduction to Hadoop

Hadoop is an open source,java-based programming framework that supports the processing of large data sets in a distributed computing environment. It is part of the Apache project sponsored by the Apache Software Foundation.

Hadoop is designed to be robust,in that your Big Data applications will continue to run even when individual servers or clusters fail. It is also designed to scale up from a single server to thousands of machines,with a very high degree of fault tolerance.

Hadoop an efficient distributed file system and not a database.It is designed specially for information that comes in many forms,such as server log files or personal productivity documents. Anything that can be stored as a file can be placed in a Hadoop repository.

Big Data means really a big data, it is a collection of large datasets that can not be processed using traditional computing techniques. Big data is not merely subject,which involves various tools,techniques and frameworks.

Hadoop can provide accurate analysis,which may lead to more concentrate decision-making resulting in greater operational efficiencies,cost reductions and reduced risks for the business.

Recommended to Read:  Prerequisites for Learning Hadoop

History of Hadoop:

Hadoop was inspired by Google's MapReduce,a software framework in which an application is broken down into numerous small parts. Any of these blocks or parts can be run on any node in the cluster.

What type of problems we can achieve using Hadoop?

The Hadoop platform was designed to solve problems where you have a lot of data. Perhaps a mixture of complex and structured data and it does not fit nicely into tables. It is for situations where you want to run analytics that are deep and computationally extensive,like clustering and targeting. That is exactly what Google was doing when it was indexing the web and examining user behavior to improve performance algorithm.

Hadoop applies to a bunch of markets. In finance,if you want to do accurate portfolio evaluation and risk analysis,you can build sophisticated models that are hard to jam into a database engine. But Hadoop can handle it. In online retail,if you want to deliver better search answers to your customers so they are more likely to buy thing you show them,that sort of problem is well addressed by the platform Google built.

Where exactly Hadoop is used in Real time:

1.  Search   -  Yahoo,Amazing,Z vents
2.  Log Processing -  Facebook, Yahoo
3.  Data Warehouse - Facebook, AOL
4. Video and image Analysis - New York times, Eyealike

Advantage of Hadoop:

It's scalable:

New nodes can be added as needed and added without needing to change data formats,how data is loaded,how jobs are written,or the applications on top.

It's cost effective:

Hadoop brings massively parllel computing to commodity servers. The result is a sizeable decreases in the cost per terabyte of storage,which in turn makes it affordable to model all your data.

It's Flexible:

Hadoop is schema less,and can absorb any type of data,structured or not,from any number of resources. Data from multiple sources can be joined and aggregated in arbitrary ways enabling deeper analysis than any one system can provide.

It's Fault tolerant:

When you lose a node,the system redirects work to another processing without missing a beat.

Difference between Web Server and Application Server

In this post we will know and understand difference between web server and application server. First let we know what is web server? A web server is a computer System that runs a web site using HTTP protocol . The web server delivers the content or web pages to end users and some other data files web based applications. Example: Apache Tomcat server.
Now let we know what is application server?  A application server is server program in a computer in a distributed network that provides the business logic for an application program. Examples: JBOSS, Oracle Web Logic server

Difference between Web server Vs application server:

• If we want to run web applications we have to use web server whereas to run enterprise applications then application server is required
• Web server can support only web related technologies whereas application server can support any technology from Java,J2EE.
• Web Servers have plugins to support scripting languages like perl,php,jsp,ASP etc..
• Most of the application servers have web server have integral part of them. So what ever application server can do what ever web server capable of. In addition to these features application server also provides features like connection pooling,object pooling,transaction support, Messaging services etc..
•  A web servers mostly used for static content whereas Application servers for dynamic content
• The web server main objective is to display content whereas application server is provides the business logic the interaction between user and displayed content
• J2EE is compatible server is application server

Every application server contains inbuilt web server to provide support for web related technologies such as servlet, jsp etc..

Watch Video :  Main Differences Between Web Server and Application Server

I hope you get knowledge on difference between web server and application server let me know your comments on this post and keep follow me for latest updates

JSP Life Cycle

In this post we will learn JSP flow of execution. Before going to learn JSP flow of execution first we will know the importance of JSP technology and where we used JSP in the IT projects. JSP is a server side technology,it can be used to prepare presentation/view part in the web application development. JSP pages can be deploy at any place in the web application directory structure. we can deploy JSP pages under the application folder or we can deploy under classes folder. But it highly recommendable deploy jsp pages under application folder,if we deploy jsp pages under application folder we can access that JSP page directly with its name just like html page,if we deploy jsp page under WEB-INF folder then we have to configure jsp page in web.xml file with a particular URL pattern. By using that url pattern only we can access jsp page.

Now let us discuss the flow of JSP Life cycle of execution.

JSP server page is slightly different life cycle than servlets. Servlets are fully compiled with java classes whereas JSP must be compiled into servlets before they can be accessed. This compilation step is handled by the JSP container at run time. 

Life Cycle methods of JSP:
1) jspInit()
2) jspService()
3)jspDestroy()

The three life cycles of jsp internally uses the 3 life cycles of servlet,since jsp container is developed based on servlet container.

JSP container never calls JSP life cycle methods directly. Actually jsp container internally activates servlet container and this servlet container calls 3 servlet life cycle methods and these life cycle methods internally calls jsp life cycle methods as shown below.

Recommended to Read: Life cycle of Servlet

With respect to tomcat 6.0:

public class First.jsp extends org.apache.jasper.runtime.HttpJspBase
{
public void -jspInit()
{
..........
.........
}
public void -jspService()
{
..............
.............
}
public void -jspDestroy()
{
.............
.............
}
}
//super class of jsp equivalent servlet program
public class HttpJspBase extends HttpServlet
{
public void init(ServletConfig cg)
{
super.init(cg);
-jspInit();
}
public void destroy()
{
-jspDestroy();
}
public void service(HttpServletRequest req,HttpServletResponse res)
{
-jspService(req,res);
}
.............
..............// other methods
}

The above code proves two concepts. They are
a) Every JSP equivalent servlet is HttpServlet, JSP container internally uses Servlet Container.
b) JSP life cycle methods will be executed by servlet container through Servlet life cycle methods. 

When a JSP page is requested, the container looks for a Servlet class. If the class does not exist, or if the servlet compile date is older than the last access date on the JSP, the JSP compiled. When a JSP file is accessed, the container converts it into a java class that implements the javax.servlet.jsp.HttpJspPage interface. The various JSP building blocks are translated into java code and compiled into a servlet.

What are the Prerequisites For Learning Hadoop

In this post we will know and understand the prerequisites to learn Hadoop. If you want to learn and make your career in Hadoop but do not know from where to start then you will get more questions like as follows.

From where should i start learning Hadoop?
Is java required to learn Hadoop?
What Technologies should know to learn Hadoop?
What is the importance of SQL knowledge in Hadoop?
How would be career growth in Hadoop?

like this you will get many questions before start to learn Hadoop. You will get answers for all questions regarding Hadoop.

What are the prerequisites to learn Hadoop:

If you want to become an expert in Hadoop and make an excellent career, you should have at least basic knowledge of java and Linux. If you don't have any idea on Java and Linux no worries, you still can start learning Hadoop. But Basic Knowledge on Java and Linux is required so spend some time to learn Basics of Java and Linux parallel.

Is Java required to learn Hadoop:

It is added advantage if you have java knowledge. Since you can write your map and reduce functions in the language of your choice. There are some advanced features via the Java API. So knowledge of java comes handy here.

Sometimes,java is not strictly a prerequisites for working with Hadoop. Because there are tools like Hive and Pig that are built on top of Hadoop offer their own High-level languages for working with data on your cluster. If you want to write your own MapReduce code,you can do so in any language like perl,python,c,Ruby etc..

Finally i can conclude with few words,Hadoop projects comes with a lot of different roles like Architect,Developer,Tester,Linux/Network/Hardware Adminstrator and some of which require explicit knowledge of Java and some Don't. If you want become expert in Hadoop then you should have knowledge on Java and Linux.

What are the importance of SQL knowledge in Hadoop:

Now a days, Hadoop is hot new technology,if you are already comfortable with SQL,then learning how to use Hive will feel very familiar as it's syntax and commands are very SQL-like. Hadoop is not replacement for RDBMS and other DataBase.

I know C/C++ is this benefit to learn Hadoop:

Defiantly, Because there are several parts of the Hadoop system that are written in C and C++. If you want write map-reduce code in C++ then Hadoop also has pipes. Here pipes is library which allows C++ source code to be used for Mapper and Reducer code.

If you know C and C++ programming Language then to learn Java is very easy,it is not difficult at all. So learn Java and start learn Hadoop.

How would be Career Growth in Hadoop:

Hadoop is one of the top 10 job trends right now. Microsoft,Oracle and IBM,Amazon,Facebook are also looking for Hadoop Prefessionals. So Your feature must be bright with Hadoop.

I hope you enjoy this post and keep follow me for latest updates on Hadoop and share this post to your friends.