of Excellence
Listen to this articleSQL means structured query language that is used to communicate with the database, retrieve information and manipulate data stored in the database. It is the most in demand skill that will help you throughout your career, irrespective of the changes in technology that take place every day. So if you have the passion to work with data and want to be a web developer, business analyst, data analyst, data administrator or a data scientist, you must master all SQL concepts. Proficiency in this database programming language along with an analytical bent of mind can take you to great heights and help you achieve your career goals. Recruiters are on the lookout for those professionals who have mastery over their subject. So if you want to crack the interview and get your dream job you will need to prepare yourself with all kinds of SQL interview questions that are usually asked to test your technical skills.
But before that, let’s look at some basic preparation that you need to do before facing any interview. Make sure that your resume is current and accurate. You may need to customize your resume to list down all the technical skills and experience you possess specially that is relevant to the job that you have applied. Read the job description carefully and find out what specific skills and knowledge you need to target for preparation. You must be able to convince the recruiter that you are a perfect fit for the job role that you have applied for. So in order to be able to face all kinds of SQL Interview questions, you must brush up your database management knowledge.
AceWebAcademy can be your partner in preparing for the SQL Jobs. It provides various courses in SQL Training and Web Development Training that can help you gain a strong foothold in the subject area. We even have Online Web Development Classes if the regular classroom timings do not suit you.
You will be prepared for all kinds of SQL queries for interview that are asked to test one’s basic knowledge. Our experts will help you out with all the fundamental Oracle SQL interview questions that may be asked to test your database skills.
You will get to know of hundreds of SQL basic interview questions on the internet that you can study to gain some basic knowledge. These SQL interview questions and answers for fresher’s will just help you start your preparation. But if you need to face any kind of job interview you need to take a step ahead. Here we present to you the top 10 toughest SQL developer interview questions along with their answers that will help you prepare for your SQL interview. These questions have been compiled by our SQL Training Experts at Ace Web Academy after a detailed research on the skills that recruiters seek for in the candidates. Prepare these answers and boost your interview preparation.
1.How to increase the speed of a stored procedure?
Insert SET NOCOUNT ON at the beginning of a stored procedure to suppress messages like 1 rows(s) affected whenever we execute statements like INSERT, UPDATE, DELETE. This reduces the network traffic and improves the performance of the stored procedure.
2. What is ACID property in a database?
ACID is a set of properties in the database that ensures that the transactions are processed reliably even in case of power failures or error events. ACID stands for Atomicity, Consistency, Isolation, and Durability. When a sequence of database operations satisfies the ACID properties, they form a single logical operation and are considered as a transaction. For example, transferring funds from one bank to another requires multiple changes but is considered as a single transaction.
Atomicity- Transaction follows all or nothing rule i.e. it either runs to completion or does not execute at all. If one part of the transaction fails due to crashes, errors or power failure, then the entire transaction fails and the database remains in its original state. A transaction is either committed or aborted, it does not occur partially. In case the transaction is committed changes are visible in the database. In case it is aborted no changes are made to the database.
Consistency- This property ensures the correctness of the database. The database remains consistent before and after the execution of the transaction. Integrity constraints are maintained and the changes made to the database are valid as per the defined rules.
Isolation- This property ensures database consistency during concurrent execution of multiple transactions. Transactions can occur independently in isolation without interfering in each other’s execution. When transactions execute concurrently the database will be in the same state as it would be if they were executed sequentially.
Durability- This property ensures that once a transaction is committed, the updates in the database will be stored on the disk permanently. It will persist even in case of system failure, crashes, errors or power loss.
The ACID properties basically provide a mechanism to ensure that the database is correct and consistent.
3. How to remove duplicate rows from the table?
We can delete duplicate rows from table using the concept of max (rowid) of table.
Step 1: Select rollno FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);
Step 2: Delete duplicate rows
Delete FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno)
4. If you are a SQL Developer, how can you delete duplicate records in a table with no primary key?
We can delete the duplicate records in the table with no primary key by using the SET ROWCOUNT command. It limits the number of records affected by a command. For example, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0
5. Write an SQL Query to check whether date passed to Query is the date of given format or not.
IsDate() function is used to check whether the date passed to the query is in the specific format or not. If the format is same, the function returns 1 i.e. true. If the date is not in the specific format the IsDate() function returns 0. The return value is based on the settings set by SET DATEFORMAT. This function is specific to MSSQL database
6. Write SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal 10000.
By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 5000and <=10000. This can be used as a subquery to find the Full name of Employees having those Ids from the EmployeeDetails table.
SELECT FullName
FROM EmployeeDetails
WHERE EmpId IN
(SELECT EmpId FROM EmployeeSalary
WHERE Salary BETWEEN 5000 AND 10000)
7. In which files does SQL Server actually store data?
The SQL server database has 2 kind of files *.MDF files are the actual physical database files where the data is stored. *.LDF Contain logging information for all transactions completed by the server.
8. What is the sixth normal form in SQL Server?
For a table to be in 6NF, it should first be in 5NF and then it requires that each table should satisfy only trivial join dependencies. It means that every relation consists of a candidate key plus no more than one other attribute. For example if an item is identified by ItemCode and 2 other attributes Description and Price. Then in 6NF we will have two relations – ItemDesc(ItemCode, Description) and ItemPrice(ItemCode, Price). Though it minimises dependencies, using 6NF leads to explosion of tables. Also it may not be possible to enforce multi-table constraints. Though 6NF is achievable, it may not necessarily lead to an optimum design. In many cases it may be practical to just stick to 5NF. Sixth normal form is being used in some data warehouses where its benefits exceed far more than its drawbacks. For eg. Using Anchor Modeling.
9. Write an SQL Query to find maximum salary in each department of an organisation.
By using the MAX() function and grouping on the basis of Department ID, we can calculate the maximum salary in each department. The following query will help us achieve the desired result.
SELECT DeptId, MAX(Salary) FROM Employee
GROUP BY DeptId
In case we need the department name too, we will need to join the Employee table with Department table using foreign key DepId. The following query can be used.
SELECT DeptName, MAX(Salary) FROM Employee e RIGHT JOIN Department d ON e.DeptId =d.DeptId GROUP BY DeptName
10. What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.
The difference between the results shown by these 2 functions occur in case there is a tie. When multiple values in the set have same ranking, then the RANK() function will assign non-consecutive ranks to the values resulting in gaps. When DENSE_RANK() is used consecutive ranks are assigned to values in the set.
For example in the set {525,525, 550, 575, 575, 600. RANK() will return {1, 1, 3, 4, 4, 6} . Since values 2 and 5 are skipped there are gaps. DENSE_RANK() on the other hand will return {1, 1, 2, 3, 3, 4}.Here ranks are not skipped.
These 10 toughest SQL interview questions and answers for experienced candidates will give you an idea of the level of preparation you need if you want to get your dream job. Once you brush up your technical knowledge, you must focus on other behavioural skills that you must possess to crack the interview. Your presentation and communication skills also create an impact on the mind of the recruiter. Listen to the question carefully, and analyse what exactly the recruiter wants to know. Your answers should be precise mentioning only the key points. Do convey this message that you are eager to work in this field, and the role that is being offered really interests you.
People Also Read: PHP and MySql is the heart and soul of developing websites- Know your basics well
If you want a one stop solution for all your interview preparation needs Ace Web Academy’s SQL Training and Placement support is the answer. Our Web Development Course in Hyderabad includes modules that will give you an indepth understanding of databases and related utilities and tools. You will gain experience in working on Procedures, Packages, triggers and query writing. Ace Web Academy’s Full stack development course in Hyderabad will help you gain knowledge on the entire software development lifecycle as well as backend development including data analysis, data modelling, design and implementation. Our experts share tips on preparing for job interviews relating to SQL and other database skills. So contact us right now and achieve your career goals!
