Hello friends! Today, in this post, we are going to discuss on the top most SQL Interview Questions on Queries! Here we have covered almost all interview questions on SQL ranging from the very basic and then move to more complex questions. This handy guide will be useful for Freshers as well as Experienced Candidates. You can go through this collection of SQL Interview Questions on queries for a quick revision before appearing for an interview.
Most of the SQL Interview Questions we’ve filtered out of interviews held by top IT MNC like Flipkart and Amazon. So, browse the collection of SQL Interview Questions on Queries and get started to hone your SQL Skills.
To get started with learning SQL on Queries, we recommend that you first try to form queries by yourself and then try to find out the answers on your own.
Question 1: What Are Tables and Fields?
A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.
Field: Emp ID, Emp Name, Date of Birth.
Data: 123456, John, 11/14/1983.
Question 2: What is Primary Key, Unique Key and Foreign Key?
Primary Key – A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.
Unique Key – A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns. A Primary key constraint has automatic unique constraint defined on it. But not, in the case of Unique Key; there can be much unique constraint defined per table, but only one Primary key constraint defined per table.
Foreign Key– A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.
Question 3: What are the different TCL commands in SQL?
TCL commands are used to manage the changes made by DML statements.
COMMIT: To write and store the changes to the database
ROLLBACK: To restore the database since the last commit
Question 4: What is an Index?
An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.
Question 5: What Are All The Different Types Of Indexes?
There are three types of indexes
1. Unique Index: Unique Indexes helps maintain data integrity by ensuring that no two rows of data in a table have identical key values. A unique index can be applied automatically when a primary key is defined. It ensures that the values in the index key columns are unique.
2. Clustered Index: Clustered Index reorders the physical order of the table and search based on the key values. There will be only one clustered index per table.
3. Non-Clustered Index: Non-Clustered 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 View?
A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.
Question 7: What is a Query?
A database query is a request for data or information from a database table or combination of tables. A database query can be either a select query or an action query.
Question 8: What is a Subquery?
A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.
Question 9: What Are The Types Of Subquery?
There are two types of Subquery:
1. Correlated: In a SQL database query, a correlated subquery is a subquery that uses values from the outer query in order to complete. Because a correlated subquery requires the outer query to be executed first, the correlated subquery must run once for every row in the outer query. It is also known as a synchronized subquery.
2. Non-Correlated: A Non-correlated subquery is a subquery in which both outer query and inner query are independent to each other.
Question 10: How to Avoid Duplicate Records in a Query?
The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
Question 11: What is a NULL Value?
A field with a NULL value is a field with no value. A NULL value is different from 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. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.
Question 12: How to Test NULL Values?
A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, It is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL and IS NOT NULL operators.
SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
Question 13: What Is The Largest Value That Can Be Stored In A BYTE Data Field?
The largest number that can be represented in a single byte is 11111111 or 255. The number of possible values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).
Question 14: Define the SELECT INTO statement.
The SELECT INTO statement copies data from one table into a new table. The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.
SELECT * INTO newtable FROM oldtable WHERE condition;
Question 15: What Are String Functions in SQL?
LEN () – It returns the length of the value in a text field
LOWER () – It converts character data to lower case
UPPER () – It converts character data to upper case
SUBSTRING () – It extracts characters from a text field
LTRIM () – It is to remove all whitespace from the beginning of the string
RTRIM () – It is to remove all whitespace at the end of the string
CONCAT () – Concatenate function combines multiple character strings together
REPLACE () – To update the content of a string.
Question 16: What Are the Different Types of Normalizations?
- First Normal Form (1NF):
This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns
- Second Normal Form (2NF):
Meet all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys
- Third Normal Form (3NF):
This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints
- Fourth Normal Form (3NF):
Meeting all the requirements of third normal form and it should not have multi- valued dependencies.
Question 17: Difference between DELETE and TRUNCATE Commands?
DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.
TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.
Question 18: What Is The Syntax To Add A Record To A Table?
To add a record in a table INSERT syntax is used.
Ex: INSERT into table_name VALUES (value1, value2…);
Question 19: Define SQL Delete Statement?
Delete is used to delete a row or rows from a table based on the specified condition.
The basic syntax is as follows:
DELETE FROM table_name
Question 20: Write a SQL SELECT Query That Only Returns Each Name Only Once From A Table?
To get the each name only once, we need to use the DISTINCT keyword.
SELECT DISTINCT name FROM table_name;
Practical SQL Interview Questions on Query
Question 21: Write An SQL Query To Fetch “FIRST_NAME” From Worker Table Using The Name As <WORKER_NAME>.
Select FIRST_NAME AS WORKER_NAME from Worker;
Question 22: Write An SQL Query To Fetch “FIRST_NAME” From Worker Table In Upper Case.
Select upper (FIRST_NAME) from Worker;
Question 23: Write An SQL Query To Print The FIRST_NAME And LAST_NAME From Worker Table Into A Single Column COMPLETE_NAME. A Space Char Should Separate Them.
Select CONCAT (FIRST_NAME, ‘ ‘, LAST_NAME) AS ‘COMPLETE_NAME’ from Worker;
Question 24: How to Add New Employee Details in an Employee_Details Table With The Following Details
Employee_Name: Ryan, Salary: 10000, Age: 25?
Answer: INSERT into Employee_Details (Employee_Name, Salary, Age) VALUES (‘Ryan, 10000, 25);
Question 25: How to add a column ‘Salary’ to a table Employee_Details?
ALTER TABLE Employee_Details ADD (Salary);
Question 26: How to Rename a Column in the Output of SQL Query?
SELECT column_name AS new_name FROM table_name;
Question 27: What is the Order of SQL SELECT?
Order of SQL SELECT statement is as follows:
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
Question 28: How to Display The Current Date in SQL?
In SQL, there is a built-in function called GetDate () which helps to return the current date.
Question 29: Write SQL SELECT query that returns the FirstName and LastName from Employee_Details table.
SELECT FirstName, LastName FROM Employee_Details;
Question 30: Describe SQL Comments?
Single Line Comments: Single line comments start with two consecutive hyphens (–) and ended by the end of the line
Multi-Line Comments: Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored.
Bookmark this post “SQL Interview Questions and Answers” for future reference. After reading this post “SQL Interview Questions”, if you find that we missed some important SQL Interview Questions, please comment below we would try to include those with answers. Stay tuned with Softwareguiders.com for more updates!