60 High SQL Interview Questions and Answers

Interview
Question & Answers
Tutorials

Lotusamaze-SQL Interview questions and answers60 SQL Interview questions and answers  help job seekers and freshers to have basic and advance knowledge of SQL . Experienced also can take the advantage of  SQL Interview questions and answers post.  I got the feedback from many developers that they found SQL Interview questions and answers post very helpful for succeeding in freelancers.com and upwork.com exam SQL  test.

The initials SQL interview questions let you know basic concepts of SQL which are helpful to get stronger in the SQL specially for beginners. The list of SQL questions answers are like a SQL tutorial which makes the reader strong technically in SQL language.

1. What is SQL?

SQL stands for structured query language. It is a database language which can perform following activities on database.

  • Creation
  • Deletion
  • Fetching
  • Updating

2. Define Tables in SQL.

Tables are database object which contains data in rows and column format.

3. Define Subsets of  SQL.

  • DDL - Data Definition Language
  • DML - Data Manipulation Language
  • DCL - Data Control Language101 High SQL Interview Questions and Answers

4. Define Primary Key.

To Identify each row/record in a database table SQL supports Primary Key concept. Primary key is a combination of field(s).  Primary key values cannot be NULL. SQL database allow only one  primary in each table.

5. Define Foreign Key

A FOREIGN KEY is a key which establishes a relation between two tables like Parent-Child relationship.  In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In other words, second table has foreign key referring primary key or unique key in parent or first  table.

6. Define constraints in SQL.

Constraints  set the rules for all records to be inserted  in the table. Constraints  validates the data before inserting into tables.

  • NOT NULL: The column does not accept NULL value.
  • UNIQUE: This constraint does not allow duplicate value in each row and column.
  • PRIMARY KEY: This constraint is combination of NOT NULL and UNIQUE constraints.
  • FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key.
  • CHECK: It is used to ensure whether the value in columns fulfills the specified condition.

7. Explain Transactions and its Controls.

Transaction means a group of SQL queries executes or fails as a group on database records.

Lotusamaze-SQL Interview Questions and Answers For Freelancers SQL Transaction and Control8. Define Triggers.

A trigger is a special type of stored procedure that responds automatically based on  event occurs in the database server. Change in data  through a data manipulation language (DML) event causes triggers to fire.  INSERT, UPDATE, or DELETE statements on a table or view are DML events.

9. Define pattern matching operator.

LIKE operator is used for pattern matching, and it can be used as -.

% - Matches zero or more characters.

_(Underscore) – Matching exactly one character.

Example -
Select * from MyTable where FirstName like 'a%'
Select * from MyTable where FirstName like 'ami_'

10. How to fetch odd and even rowNo(Alternate Records)

  • Even numbers-
  • Select Id from (Select rowno, id from student) where mod(rowno,2)=0
  • Odd numbers-
  • Select Id from (Select rowno, id from student) where mod(rowno,2)=1

11. Define TRUNCATE and DROP statements.

  • TRUNCATE : It does not log when removes all the rows from the table i.e rolled back not possible.
  • DROP : It deletes a table along with rows from the database i.e rolled back not possible.

12.  Define Nested Subquery and Correlated Subquery.

In SQL database Subquery within another subquery is called as Nested Subquery.  If the output of a subquery uses column values of the parent query table then the query is called Correlated Subquery(also known as a synchronized subquery). Because the subquery's execution happens for each row processed by the outer query so it might be slow and inefficient.

13. What is Normalization?

Normalization is a organised approach for dividing a table into further more tables to remove data redundancy(repetition) and undesirable characteristics like Insertion, Update and Deletion Anomalies.

  • First Normal Form (1NF): Existence  of single(atomic) valued attributes/columns is the condition. Values stored in a column should be of the same domain. All the columns in a table should have unique names.Lotusamze-1st Normalisation
  • To bring this table to first normal form, we split the table into two tables and now we have the resulting tables:Lotusamaze-1st Normalisation
  • First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines relationship between tables using primary key.Lotusamaze 2nd Normalisation
    Customer ID and Store ID are the  composite primary key in this table and "Purchase Location" is the non-key attribute and StoreID is the dependent key for "Purchase Location" which is only part of the primary key. Therefore, this table does not satisfy second normal form.
    To bring this table to second normal form, we break the table into two tables, and now we have the following:Lotusamaze-2nd Normalisation
  • Third Normal Form (3NF): It is in the Second Normal form. And, it doesn't have Transitive DependencyLotusamaze-3rd Nomalisation
    In the table able, [Item ID] determines [Item Code], and [Item Code] determines [Item]. Therefore, [Item ID] determines [Item] via [Item ID] and we have transitive functional dependency, and this structure does not satisfy third normal form.To bring this table to third normal form, we split the table into two as follows:
  • Lotusamaze-3rd Normalisation
  • Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

14. Define between Local and Global temporary table.

A global temporary table exists in the database permanently, but the rows exist only duration of that given connection. Rows disappear when the connection is closed. Only SQL server connection currently in use, can access temporary table.

15. Define CHAR and VARCHAR2 datatype in SQL.

Varchar2 is of character strings of variable length whereas Char is used for strings of fixed length.

16. Define clustered and non clustered index in SQL.

18. What is an Index?

An index is used to improve the performance of queries by reducing the number of database data pages that have to be visited/scanned.

  • Unique Index: Ensures the uniqueness of each value in the indexed column.
  • Clustered Index: This index reorders the physical order of the table and searches based on the basis of key values. Each table can only have one clustered index.
  • Non-Clustered Index: Non Clustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 non clustered indexes.

19. What is ACID property in a database?

20. Do View contain Data?

No , View is just mirror of table.

21. What is schema?

A schema is a collection of database objects of a User.

22.  List the various privileges that a user can grant to another user?

  • SELECT
  • CONNECT
  • RESOURCES

23. What is a View?

A view is a mirror table which contains data from one or more tables. Views are useful when you want to restrict data access  other users.

24. Define Rename and Alias?

Rename changes the name of object permanently whereas Alias is a temporary name given to a table or column.

25.  How to  avoid duplicating records in a query?

By using DISTINCT keyword.

26. What is Case Function?

Case function evaluate the condition like if-then-else.

27. List the Scalar Functions in SQL.

  • FORMAT(): Specifies the display format.
  • LEN(): Specifies the length of text field.
  • ROUND(): Rounds up the decimal field value to a number.
  • UCASE(): Converts the specified field in upper case.
  • LCASE(): Converts the specified field in lower case.
  • MID(): Extracts and returns character from text field.

28. List the Aggregate functions in SQL.

  • AVG – calculates the average of a set of values.
  • COUNT – counts rows in a specified table or view.
  • MIN – gets the minimum value in a set of values.
  • MAX – gets the maximum value in a set of values.
  • SUM – calculates the sum of values.

29. What is SQL Privileges?

Only SQL Admin is allowed to Grant or Revoke privileges.  The Admin of database is authorized to  grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.

  • Syntax: GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION];
    Syntax: REVOKE privilege_name ON object_name FROM {user_name|PUBLIC|role_name};

30. What is the difference between SQL and PL/SQL?

SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

31. Define NVL functions.

It convert the null value to its actual value.

32. List types of Privileges available in SQL.

  • System Privilege: System privileges are applicable on  ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW for an object.
  • Object Privilege: These are applicable on EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES on database object.

33. Explain Stored Procedures in SQL?

A stored procedure is a set of SQL statements which can be used as a group to perform the activity on database. Stored procedure also supports conditions, loops, cursors etc.Stored procedures are also used to reduce network traffic and improve the performance.

34. What are Nested Triggers?

Some time also called child trigger. Trigger calling other trigger is called nested trigger.

35. Define Cursor.

For processing of data of each row SQL supports cursor. A cursor is a temporary work area created in the system memory. Cursor takes the data by using SELECT statement. Cursors have performance issues because of memory taken by cursor.

36. Which TCP/IP port does SQL Server run?

By default SQL Server runs on port 1433.

37. Define UNION, MINUS, UNION ALL, INTERSECT?

  • MINUS – returns all distinct rows selected by the first query but not by the second.
  • UNION – returns all distinct rows selected by either query
  • UNION ALL – returns all rows selected by either query, including all duplicates.
  • INTERSECT – returns all distinct rows selected by both queries.

 38. What is a composite primary key?

Primary key created on more than one column is called composite primary key.

39. What is query optimization?

Database system compares different query strategies and choose the best one. This is called query optimization.

40. Define a temp table?

A temp table is a temporary storage structure to store the data temporarily.

41. What is schema?

A schema is a collection of database objects of a User.

42. What is the difference between Having clause and Where clause?

WHERE Clause  filters out records based on the data available in a Database table and HAVING Clause on the other hand filters records on the basis of results of Aggregation Functions (i.e. Group By clause) 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 absent then Having works like WHERE clause only.

43. What is execution plan?

An execution plan is the result of the query optimizer’s attempt to calculate the most efficient way to implement the request represented by the T-SQL query submitted. Execution plans can generate report suggesting you how  how a query will be executed, or how a query was executed.

44. What is difference between NVL and NVL2.

  • NVL(exp1, exp2)
  • NVL2(exp1, exp2, exp3)
  • In both case exp1 is checked if it is NUll.
  • With the NVL(exp1, exp2) function, if exp1 is null value of exp2 is returned otherwise value of exp1 is returned.
    With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

45. How to Insert a value in Identity column?

  • SET IDENTITY_INSERT TABLE1 ON INSERT INTO TABLE1 (ID,FNAME) VALUES(2,'Johnh') SET IDENTITY_INSERT OFF

46. How to copy data of one table to another table?

Insert Into Table1(col1,col2,col3) Select Col1,Col2,Col3 From Table2

47. How to find duplicate record in table?

  • Find duplicate MembershipNo SELECT MembershipNo, COUNT(MembershipNo) AS NumOccurrences FROM users GROUP BY MembershipNo HAVING ( COUNT(email) > 1 )

48. How to disable a trigger?

  • A. Disabling a DML trigger on a table
    • DISABLE TRIGGER Person.uAddress ON Person.Address;
  • B. Disabling a DDL trigger
    • DISABLE TRIGGER safety ON DATABASE;

49. What is bulk Insert?

Imports a data file into a database table or view in a user-specified format in SQL Server.

50. How to change date format in sql stored procedure?

Set Dateformat DMY

51. What is SQL Profiler?

SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.(By Microsoft).

52. What is Deadlock in Sql?

When more than two database sessions try to read same data which is already locked by another session then each session wait for other session to release lock and waiting goes on indefinitely  then this scenario is called deadlock.

53. Explain User Defined Function.

Users can define the custom  functions in Sql. UDF can accepts parameters and can return result in scalar or table form. UDF commonly are used in select statement.

54. How to call user defined function in sql server select statement?

UDF Name: FindLocation(LocationID)
SELECT dbo.FindLocation('2'))

55. What is function of RAISERROR?

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.(Micosoft Doc).
RAISERROR (15600,-1,-1, 'mysp_CreateCustomer');

Conclusion

In SQL  Interview questions and answers I have tried to cover almost all topics of SQL language. However in interview question on SQL could be random but my experience says generally interviewer starts from basic questions and moves on advance questions. I believe  initials SQL interview questions helped you to have  basic concepts of SQL and advanced questions helped  to get stronger in the SQL specially for beginners. The list of SQL questions answers are like a SQL tutorial which makes the reader strong technically in SQL language.

 

See Also: C# Interview Questions And Answers