SQL or Structured Query Language is an extensively used database management system. Analysing and accessing data is the need of the hour, and SQL allows you to do just that. Attending an interview, more often than not, seems like an intimidating situation to be in. But, if you prepare well and are aware of the questions that may be asked during the interview process, it becomes easier for you to do well. There is a high demand for individuals who possess SQL skills. Thus, it becomes essential that you prepare well. Here are some of the top sql interview questions that are asked to a developer with SQL skills.
Top SQL Interview Questions for Developers:
1. How do you find duplicate records in SQL?
There are several ways to find duplicate records in SQL. One method is
to use the groupby function, and another way is to use the rank function. Let
us understand this with the help of an example.
While using Groupby:
SELECT
a,
b,
COUNT(*) occurrences
FROM w1
GROUP BY
a,
b
HAVING
COUNT(*) > 1;
While using rank:
SELECT * FROM (
SELECTstudentid, name, age, Row_Number() OVER(PARTITION BY name, age ORDER By
name) AS Rank FROM students ) AS X WHERE Rank>1
2.
What do you mean by case WHEN in SQL?
Have
you learnt any other programming languages? If yes, you will be aware of if-else
statements. The WHEN case in SQL can be considered analogous to the if-else
statement. In this case, multiple conditions are available to us, and we need
to choose a particular thing based on these conditions. The syntax used for the
case WHEN is:
CASE
WHEN
condition3 THEN result3
WHEN
condition4 THEN result4
WHEN
conditionA THEN resultA
ELSE
result
END;
You must first start
by entering the CASE keyword that is followed by multiple WHEN, THEN
statements.
3.
How do you create an index in SQL?
To
create an index in SQL, you must use the following command.
CREATE INDEX
index_name
ON table_name
(column3, column4, column5 ...);
You
will start by using the keyword CREATE INDEX, followed by the name of the
index, and then, use the ON Keyword. After the ON Keyword, we will mention the
name of the table where we want to create this index. Finally, we must list the
columns that will be present in the index. Let us understand this with the help
of an example:
CREATE INDEX marks
ON Students (marks);
In
this example, we created an index known as marks on top of the ‘marks’ column
of the table ‘students’. Let us now understand how we can create a unique
index. This is the syntax to be followed:
CREATE UNIQUE INDEX
index_name
ON table_name
(column4, column5,column6 ...);
You will start by
using the keywords CREATE UNIQUE INDEX. Followed by the name of the index.
After that, we will write the ON keyword followed by the name of the table.
Lastly, in parenthesis, we will write the list of the columns on which we would
want this unique index.
4. What is the main
difference between SQL and NoSQL?
SQL deals with
relational databases. It stands for structured query language. SQL is mainly
used for querying data from relational databases. Whereas, NoSQL works with
non-relational databases.
5. How would you find
the second highest marks in a table in SQL?
The syntax followed to
find the second highest marks in an SQL table, is as follows.
SELECT name,
MAX(marks)
FROM
students
WHERE marks
< (SELECT MAX(marks)
FROM
students);
6. What do you mean by
cursor in SQL?
A cursor in SQL is
used to store the database tables. The two main types of cursors available in
SQL are: Implicit Cursor and Explicit Cursor. Let us understand these in
detail.
Implicit Cursor- It is
the type of cursos that is default by nature. It is automatically created, and
the user has no way to create this cursor.
Explicit Cursor- These
are user-defined. An explicit cursor can be created with the help of the
following syntax:
DECLARE cursor_name
CURSOR FOR SELECT * FROM table_name
First, we must use the
keyword DECLARE. After this, we can write the name of the cursor, followed by
the keyword CURSOR FOR SELECT*FROM, followed by the table name.
Conclusion
This brings us to the end of the
blog on Top SQL Interview Questions for Developers. We hope that you found this
helpful and are now better-equipped to ace your upcoming interviews. Happy
Learning, and good luck!
0 Comments