SQL Interview Questions and Answers, SQL Interview Questions and Answers Freshers, SQL Interview Questions and Answers, SQL Interview Questions
Before getting on to the SQL interview questions, the student must know that the SQL is a continuously varying field which needs the students as well as professionals to upgrade their skills with the new features and knowledge, to get fit for the jobs associated with SQL. This post related to SQL Interview Questions and Answers, SQL Interview Questions and Answers Freshers, SQL Interview Questions and Answers, SQL Interview Questions will help you let out find all the solutions that are frequently asked in you upcoming SQL interview.
Over thousands of vacancies available for the SQL developers, experts must be acquaintance with all the component of SQL technologies. This is necessary for the students in order to have in-depth knowledge of the subject so that they can have best employment opportunities in the future. Knowing every little detail about SQL is the best approach to solve the problems linked with problem.
APTRON has spent hours and hours in researching about the SQL Interview Questions and Answers, SQL Interview Questions and Answers Freshers, SQL Interview Questions and Answers, SQL Interview Questions that you might encounter in your upcoming interview. This post related to SQL interview questions and answers will help you let out find all the solutions that are frequently asked in you upcoming SQL interview. All these questions will alone help you to crack the interview and make you the best among all your competitors.
First of all, let us tell you about how the SQL technology is evolving in today’s world and how demanding it is in the upcoming years. In fact, according to one study, most of the companies and businesses have moved to the SQL. Now, you cannot predict how huge the future is going to be for the people experienced in the related technologies.
Hence, if you are looking for boosting up your profile and securing your future, SQL will help you in reaching the zenith of your career. Apart from this, you would also have a lot of opportunities as a fresher.
These questions alone are omnipotent. Read and re-read the questions and their solutions to get accustomed to what you will be asked in the interview. These SQL interview questions and answers will also help you on your way to mastering the skills and will take you to the giant world where worldwide and local businesses, huge or medium, are picking up the best and quality SQL professionals.
This ultimate list of best SQL interview questions will ride you through the quick knowledge of the subject and topics like Schema, Protecting data integrity, constraints. This SQL interview questions and answers can be your next gateway to your next job as a SQL expert.
These are very Basic SQL Interview Questions and Answers for freshers and experienced both.
Q1: Compare SQL & PL/SQL
A1:
| Criteria | SQL | PL/SQL |
| What it is | Single query or command execution | Full programming language |
| What it comprises | Data source for reports, web pages | Application language to build, format and display report, web pages |
| Characteristic | Declarative in nature | Procedural in nature |
| Used for | Manipulating data | Creating applications |
Q2: What is BCP? When is it used?
A2: It is a tool used to duplicate enormous quantity of information from tables and views. It does not facsimile the structures same as foundation to target.
BULK INSERT command helps to bring in a data folder into a record, table or view in a user-specific arrangement.
Q3: When is the UPDATE_STATISTICS command used?
A3: This command is used, ones the processing of large data is done.
When we delete a large number of files, alteration or reproduction takes place in the tables, to be concerned of these changes we need to restructure the indexes This is done UPDATE_STATISTICS.
Q4: Explain the steps needed to Create the scheduled job?
A4: Steps to create a Scheduled Job :
- Connect to the database of SQL server in SQL Server Management Studio. On the SQL Server Agent, we will find a Jobs folder.
- Right click on jobs and choose Add New.
- A New Job window will come into view. Give an associated name for the same.
- Click next on the “Steps” in the left list of options. An SQL job can have multiple steps either in the form of SQL declaration or a stored practice call.
- Click on the “Schedules” in the left list of options. An SQL job can comprise of one or supplementary schedules. It is basically the instance at which SQL job will jog itself. We can spell out returning schedules also.
Q5: When are we going to use truncate and delete?
A5:
- TRUNCATE is a DDL command, whereas DELETE is a DML command.
- We can’t execute a trigger in case of TRUNCATE whilst with DELETE, we can accomplish a trigger.
- TRUNCATE is quicker than DELETE, for the reason that when we use DELETE to delete the data, at that time it store the whole statistics in the rollback gap on or after where we can get the data back after removal. In case of TRUNCATE, it will not store data in rollback gap and will unswervingly rub it out. TRUNCATE do not recover the deleted data.
- We can use any condition in WHERE clause using DELETE but it is not possible with TRUNCATE.5.If a table is referenced by any foreign key constraints, then TRUNCATE won’t work.
Q6: Explain correlated query work?
A6: It’s most important to be attentive of the arrange of operations in an interrelated subquery.
First, a row is processed in the outer doubt.
Then, for that exacting row, the subquery is executed – as a result for each row processed by the outer query, the subquery will also be processed. In correlated subquery, each time a line is worked for Emp1, the subquery will also make a decision on the exacting row’s value for Emp1.Salary and run. And the outer query will move on to the next row, and the subquery will execute for that row’s value of Emp1.Salary.
It will persist in anticipation of the “WHERE (1) = (… )” state is pleased.
Q7: When is the Explicit Cursor Used ?
A7: If the developer needs to perform the row by row operations for the result set containing more than one row, then he unambiguously declares a pointer with a name. They are managed by OPEN, FETCH and CLOSE.%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN characteristics are used in all types of pointers.
Q8: What are the different types of SQL commands?
A8: SQL commands are segregated into following types:
• DDL – Data Definition Language
• DML – Data Manipulation Language
• DQL – Data Query Language
• DCL – Data Control Language
• TCL – Transaction Control Language
Q9: What are the different DDL commands in SQL?
A9: DDL commands are used to define or alter the structure of the database.
• CREATE: To create databases and database objects
• ALTER: To alter existing database objects
• DROP: To drop databases and databases objects
• TRUNCATE: To remove all records from a table but not its database structure
• RENAME: To rename database objects
Q10: What are the different DML commands in SQL?
A10: DML commands are used for managing data present in the database.
• SELECT: To select specific data from a database
• INSERT: To insert new records into a table
• UPDATE: To update existing records
• DELETE: To delete existing records from a table
Q11: What are the different DCL commands in SQL?
A11: DCL commands are used to create roles, grant permission and control access to the database objects.
• GRANT: To provide user access
• DENY: To deny permissions to users
• REVOKE: To remove user access
Q12: What are the different TCL commands in SQL?
A12: 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
Q13: What is an Index?
A13: 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.
Q14: What is a join?
A14: This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.
Q15: What are the types of join and explain each?
A15: There are various types of join which can be used to retrieve data and it depends on the relationship between tables.
Inner join.
Inner join return rows when there is at least one match of rows between the tables.
Right Join.
Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.
Left Join.
Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Full Join.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
Q16: What is normalization?
A16: Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.
Q17: What is Denormalization.
A17: DeNormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.
Q18: What are all the different normalizations?
A18: The normal forms can be divided into 5 forms, and they are explained below -.
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):.
Meeting 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.
Q19: What are all the different types of indexes?
A19: There are three types of indexes -.
Unique Index.
This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.
Clustered Index.
This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.
NonClustered Index.
NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.
Q20: What is a relationship and what are they?
A20: Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.
• One to One Relationship.
• One to Many Relationship.
• Many to One Relationship.
• Self-Referencing Relationship.
Q21: What is a query?
A21: A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.
Q22: What is subquery?
A22: A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.
Q23: List the various privileges that a user can grant to another user?
A23: SELECT, CONNECT, RESOURCES.
Q24: What is schema?
A24: A schema is a collection of database objects of a User.
Q25: What is Table?
A25: A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.
Q26: Do View contain Data?
A26: No, Views are virtual structure.
Q27: Can a View based on another View?
A27: Yes, A View is based on another View.
Q28: What is the difference between Having clause and Where clause?
A28: Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.
If GROUP BY clause is not used then Having behaves like WHERE clause only.
Q29: What is the difference between Local and Global temporary table?
A29: If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed.
Q30: What is CTE?
A30: A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.
Q31: List and explain the different types of JOIN clauses supported in ANSI-standard SQL.
A31: ANSI-standard SQL specifies five types of JOIN clauses as follows:
INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOINcombines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.
CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHEREclause to supply join criteria (“implicit join notation”).
Q32: What is ACID property in a database?
A32: ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure that the data transactions are processed reliably in a database system.
Atomicity: Atomicity refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
Isolation: The main goal of isolation is concurrency control.
Durability: Durability means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
Q33: What do you mean by “Trigger” in SQL?
A33: Trigger in SQL is are a special type of stored procedures that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table.
Q34: What are the different operators available in SQL?
A34: There are three operators available in SQL, namely:
1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators
Apart from this SQL Interview Questions blog, if you want to get trained from professionals on this technology, you can opt for a structured training from edureka! Click below to know more.
Q35: Are NULL values same as that of zero or a blank space?
A35: A NULL value is not at all same as that of zero or a blank space. NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character.
Q36: What is the difference between cross join and natural join?
A36: The cross join produces the cross product or Cartesian product of two tables whereas the natural join is based on all the columns having the same name and data types in both the tables.
Q37: What is the usage of NVL function?
A37: The NVL function is used to convert NULL value to a actual value.
Q38: Which function is used to return remainder in a division operator in SQL?
A38: The MOD function returns the remainder in a division operation.
Q39: What is the syntax and use of the COALESCE function?
A39: The syntax of COALESCE function:
COALESCE(exp1, exp2, … expn)
The COALESCE function is used to return the first non-null expression given in the parameter list.
Q40: What is the usage of DISTINCT keyword?
A40: The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value.
SQL Conclusion Interview FAQs
We know the list of SQL Interview Questions and Answers, SQL Interview Questions and Answers Freshers, SQL Interview Questions and Answers, SQL Interview Questions is overwhelming but the advantages of reading all the questions will maximize your potential and help you crack the interview. The surprising fact is that this SQL interview questions and answers post covers all the basic of the SQL technology and you have to check out the FAQs of different components of SQL too.
However, you will be asked with the questions in the interview related to the above mentioned questions. Preparing and understanding all the concept of SQL technology will help you strengthen the other little information around the topic.
After preparing these interview questions, we recommend you to go for a mock interview before facing the real one. You can take the help of your friend or a SQL expert to find the loop holes in your skills and knowledge. Moreover, this will also allow you in practicing and improving the communication skill which plays a vital role in getting placed and grabbing high salaries.
Remember, in the interview, the company or the business or you can say the examiner often checks your basic knowledge of the subject. If your basics is covered and strengthened, you can have the job of your dream. The industry experts understand that if the foundation of the student is already made up, it is easy for the company to educate the employ towards advance skills. If there are no basics, there is no meaning of having learnt the subject.
Therefore, it’s never too late to edge all the basics of any technology. If you think that you’ve not acquired the enough skills, you can join our upcoming batch of SQL Training in Noida. We are one of the best institute for SQL in noida which provide advance learning in the field of SQL Course. We’ve highly qualified professionals working with us and promise top quality education to the students.
We hope that you enjoyed reading SQL Interview Questions and Answers, SQL Interview Questions and Answers Freshers, SQL Interview Questions and Answers, SQL Interview Questions and all the FAQs associated with the interview. Do not forget to revise all the SQL interview questions and answers before going for the SQL interview. In addition to this, if you’ve any doubt or query associated with SQL, you can contact us anytime. We will be happy to help you out at our earliest convenience. At last, we wish you all the best for your upcoming interview on SQL Technology.
