SQL based interview-1

Hi Friends,

In this post, I'm sharing SQL interview questions/Queries that are asked in Java interviews.

These are some SQL queries and questions that are frequently asked in Java interviews:


Question 1:

What is the difference between MySQL and SQL Server?

Answer:

Difference between MySQL and SQL server are as follows:

• MySQL server is owned by Oracle while SQL server is owned and managed by Microsoft
• MySQL supports multiple languages like Perl, Scheme, Tcl, Eiffel etc than SQL server.
• MySQL supports multiple storage engines [InnoDB, MYISAM] which makes MySQL server more flexible than SQL server.
• MySQL blocks the database while backing up the data.  And the data restoration is time consuming due to execution of multiple SQL statements. Unlike MySQL, SQL server doesn't block database while backing up the data.
• SQL Server is more secure than MySQL. MySQL allows database files to be accessed and manipulated by other processes at runtime. But SQL server doesn't allow any process to access or manipulate it's database files or binaries.
• MySQL doesn't allow to cancel a query mid-execution. On the other hand, SQL server allows us to cancel a query execution mid-way in the process.

Question 2:

What is a Primary key ? How is it different from Unique key?

Answer:

Primary key is a column or set of columns that uniquely identifies each row in the table. There will be only 1 primary key per table.

Important points about Primary key:

• Null values are not allowed.
• It must contain unique values. Duplicates are not allowed.
• If the primary key contains multiple columns , the combination of values of these columns must be unique.
• When we define a primary key for a table, MySQL automatically creates an index named primary.

Difference between Primary key and Unique key:

• Primary can be only 1 per table. Unique can be many per table.
• Primary key doesn't allow null value. While Unique key allows null value.
• Primary key can be made foreign key in another table. While Unique can not be made foreign key in MySQL but it can be made foreign key in SQL server.
• By default primary key is clustered index and data in the database table is physically organized in the sequence of clustered index. By default, unique key is a unique non-clustered index.

Question 3:

What is a join and why to use it?

Answer:

A join clause is used to access/retrieve data from multiple tables based on the relationship between the fields of the tables.
Keys play a major role when Joins are used.



Question  4:

What are different normalization forms? Explain.

Answer:

There are multiple normalization forms available in SQL:

• 1NF
• 2NF
• 3NF
• BCNF: Boyce Codd Normal Form
• 4NF

Explanation of each normalized form:

1NF Rules:

• Each table cell should contain a single value.
• Each record needs to be unique 

2NF Rules:

• Be in 1NF
• Single column primary key

3NF Rules:

• Be in 2NF
• Has no transitive functional dependencies

Question 5:

What is an index and types of indexes?

Answer:


An index is a performance tuning method of faster accessing the records from a table. An index creates an entry for each value and hence it will be faster to retrieve data.

There are following types of indexes:

• Normal Index
• Unique Index
• Clustered Index
• Non-Clustered Index
• BitMap Index
• Composite Index
• B-Tree Index
• Function based index

Unique Index:

    This index doesn't allow the field to have duplicate value if the column is unique indexed.  If a  primary key is defined , Unique index can be applied automatically.

Clustered Index:  

    This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.

Non-Clustered Index:

    This index doesn't alter the physical order of the table and maintains a logical order of the data. 

    Each table can have many non-clustered indexes.

Question 6:

What is a subquery and what are types of subqueries?

Answer:


A query within a query is called a subquery. The outer is called an main query  and inner query os called a subquery. Subquery is always executed first and the result of subquery is passed to the main query.

There are two types of subqueries:

Correlated Query: It can't be considered as independent query but it can refer the column in a table listed in the FROM of the main query.


NonCorrelated Query:   It can be considered as an independent query and the output of subquery are substituted in the main query.




Question 7:

What is the difference between char and varchar2?

Answer:

Both char and varchar2 are used to display character datatype but varchar2 is used for character strings of variable length whereas char is used for strings of fixed length.

e.g.: char(10) can store only 10 characters  and will not be able to store a string of any other length whereas varchar(10) can store any length till 10 characters.


Question 8:

What is the use of UNION clause?

Answer:

UNION clause is used to remove duplicate records from the result of a query.


Question 9:

Which aggregate functions are there in SQL?

Answer:

There are multiple aggregate functions in SQL:

• MIN()
• MAX()
• SUM()
• COUNT()
• FIRST()
• LAST()
• AVG()

Question 10:

What is a View in SQL and how to create and execute a View ?

Answer:

A View can be defined as a virtual table that consists of rows and columns from one or more tables.

Data in the virtual table is not stored permanently.

Note: Views are stored in system tables : sys.sysobjvalues

Use case or benefits of View:

• Views can hide complexity: If we have a query that requires joining several tables  or have complex logic or calculations , we can code all that logic into a view, then select from the View just like you would a table.
• Views can be used as a security mechanism:  View can select certain columns and/or rows from a table (or tables) and permissions set on the view instead of the underlying tables. This allows surfacing only the data that a user needs to see.

Creating View:

Create View view_name AS
SELECT column1 , column2, ...
FROM table_name
WHERE condition;


Create View [Items Above Average Price] AS
SELECT ItemName, Price
From Orders
where Price > (Select AVG(Price) from Orders);

Note: "Items Above Average Price" is the view name.


Querying the above created View:

Select * from [Items Above Average Price]



That's all from this post.

Thanks for reading.