What are SQL Basic Interview Questions ? SQL Basic interview questions

I have written so many articles for different SQL Interview questions. I have also explained the SQL interview questions for freshers as well. In this article i would like to give you some most important and SQL Basic Interview questions with answers.The SQL Basic Interview questions not only contains the theoretical questions as well as practical queries.These questions are really important for SQL Interviews.

Question 1.What are Set operators in SQL?

Answer:

Set operators are nothing but the operators which are used to connect two tables and fetch the records from the two tables.We need to follow one condition that the table set 1 columns and table set 2 columns are same and its datatype must be same.SQL Set Operators combines the result of 2 queries or components on to the single result.

Following are Set Operators in SQL:

1. Union
2. Unionall
3. Intersect
4. Minus

Question 2. What is Null in SQL?

Answer:

A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation.

Question 3. What is SQL?[100 % asked  SQL Basic Interview Questions ]

Answer: 

SQL Stands for Structured Query Language which is specially designed to communicate with databases.SQL pronounced as Sequel is very widely used language in most of the database management systems like Oracle,MySQL,PostgreSQL etc.SQL provides us  a simple and efficient way of reading,writing,executing the data from the system.this is one of the SQL Interview Question ever asked in interviews

Question 4.What is difference between varchar and varchar2 datatype?

Answer:

Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes of memory space. Varchar will occupy the space for null values whereas varchar2 can not occupy the space for null values.So varchar2 is good to use not to  face performance related problems. varchar2 is faster than varchar datatype.

Question 5. What is Self join?

Answer :

Self join is nothing but the table joins with itself. There are lot of tables which contains more than one functionality at that time the concept of self join comes to the picture.

Syntax:

SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Real Example:

If one table contains Employee and its position.If we need to fetch the Employees and its managers then self join is used.

Question 6 .What are views in SQL?Explain types of Views in SQL?(Asked in almost every SQL Basic Interview Questions)

Answer:

Views:

Views are nothing but the logical structure of the table where we can fetch the data from different tables or same table.

There are 2 types of views in Oracle:

1.Simple View:Simple view has been created on only a single table.

2.Complex view:Views which are created using more than 1 table which has joins clauses are known as complex views.

[Click here to get more information on views]

Question 7 .What is mean by TCL? Explain with examples.

Answer:

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

• COMMIT: It is used to save all changes made through the transaction
• ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before
• SET TRANSACTION: Set the name of transaction
• SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

Question 8.What are different types of indexes?( 80 % asked in SQL Basic Interview Questions )

Answer:

There are following types of indexes:

1.Normal Indexes

2.Bit map indexes

3.B-tree Indexes

4.Unique Indexes

5.Function Based Indexes.

Question 9.How to display following using query?

*

**

***

Answer:

We cannot use dual table to display output given above. To display output use any table. I am using Student table.

SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4;

Question 10.Explain About DDL Statements of SQL?

Answer:

DDL – DDL stands for Data Definition Language:

Statement	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.

Question 11.What is the use of NVL function in Oracle?[80% asked SQL Basic Interview Question ]

Answer:

NVL function is most important function to replace null value with another value.

Example: select NVL(null,’Rahul’) from dual; which will give you output as Rahul.

Question 12.What is difference between Unique Key Constraint and Primary Key Constraint?[80% asked in SQL Basic Interview Question ]

Answer:

Primary Key constraint:

1.Primary key will not accept the null values in the table column.

2.Primary is  basically used to identify the unique records in the table.

3.We have only one primary key per table.

Unique Key Constraint:

1.Unique key accepts the null values in the table.

2.The main task of unique key is it is used to remove duplicate values from the table with exception of null entry.

3.We will have more than 1 unique keys on a single table.

Question 13.What is Materialized View in SQL?(90 % asked in SQL Basic Interview Questions )

Answer:

Materialized view is also logical structure of one or more table in which data is stored physically in the view.Data has been stored physically in materialized view so data retrieval is faster as compare to simple view.

Click here to get more information on materialized views..

Question 14.What is difference between NVL,NVL2 and Nullif?( 80 % asked in SQL Basic Interview Question )

Answer:

1.NVL :

NVL function substitutes a value when a null value is encountered.

2.NVL2 :

NVL2 substitutes a value when a null value is encountered as well as when a non-null value is encountered.

3.NULLIF:

NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

Question 15.What is query to calculate second highest salary of the employee using analytical function?

Answer:

We need to use Dense_Rank function to calculate second highest salary:

select * from (Select Dense_Rank() over ( order by  salary desc) as Rnk,E.* from Employee E) where Rnk=2;

Question 16.How to Show the Max marks and min marks together from student table?

Answer:

Select max (marks) from Student

Union

Select min (marks) from Student;

Question 17.Write query to find the repeated characters from your name?

Answer:

Select regexp_count (‘AmitA’,’A’) as Repeated_character from dual;

Question 18. What is query to display odd rows from the Employees table?

Answer:

We can achieve this using Mod function,

Select * from(Select rownum as rno,E.* from Employees E) where Mod(rno,2)=1;

Question 19. How to get distinct records from the Employees table without using distinct keyword.

Answer:

Select * from Employees a where  rowid = (select max(rowid) from Employees b where  a.Employee_no=b.Employee_no);

Question 20. How to represent comments in oracle?

Answer:

There are following 2 ways for commenting in oracle:

1.Single Line comment: Two dashes (–) before begining of the line

2. Multi-line comment/Block comment:When user wants to comment multiple line /* */ operators are used.

Question 21.What is mean by Aggregate functions in SQL? Tell me with examples. ( 90% asked in SQL Basic Interview Questions )

Answer :

SQL aggregate functions are the functions where user can give multiple values as input but function always returns single value as output.

Aggregate Functions are the Functions where the value of input is grouped together and fetches the output as a single value

Following is single line Explanation of the Aggregate functions:

• AVG() – Returns the average value
• COUNT() – Returns the number of rows
• MAX() – Returns the largest value
• MIN() – Returns the smallest value
• SUM() – Returns the sum

Question 22.Why to use SQL? [90% asked in SQL Basic Interview Questions ]

Answer:

SQL is structured query language which is used for manipulation of data.There are following reasons why to use SQL:

• Allows users to access data in relational database management systems.
• Allows users to define the data in database and manipulate that data.
• Allows users to create and drop databases and tables.
• Allows users to create view, stored procedure, functions in a database.
• Allows users to set permissions on tables, procedures, and views.

Question 23. What is the difference between SQL and MySQL?( 80 % asked in SQL Developer Interview Questions )

Answer :

SQL is a standard language which stands for Structured Query Language based on the English language whereas MySQL is a database management system. SQL is the core of relational database which is used for accessing and managing database, MySQL is an RDMS (Relational Database Management System) such as SQL Server, Informix etc.

CLICK HERE TO CHECK DIFFERENCE BETWEEN SQL AND TSQL

Question 24. Explain different Joins in SQL?

Answer:

Join is nothing but connecting 2 tables to fetch the records from 2 or more different tables.There are following types of joins in SQL:

1.Inner join:

Inner join retreives the records which are common between 2 or more tables.

2.Outer join:

Outer join retrieves the common records from the table as well as uncommon records from Left or right table.

2.1.Left outer join:

When user needs to fetch all data from left table and common records from left and right table then the join is called as left outer join.

2.2.Left outer join:

When user needs to fetch all data from right table and common records from left and right table then the join is called as right outer join.

2.3.Full Outer Join:

When user needs to fetch the data from both the tables and common records from both of the tables.

3.Cross join/Cartesian join:

When each and every record is connected to each and every record from other table then it is called as cross join or Cartesian join.

Question 25.  Write a query to validate Email of Employee.

Answer :

To achieve this user needs to use Regular Expression function,

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

These are some most important SQL Basic Interview questions with answers. Hope this article helps you in cracking SQL Interview. If you like this article or if you have any issues with the same kindly comment in comments section.