Top 20 SQL Interview Questions and Answers

0
169

Top SQL interview questions for fresher’s post has been created for the people want to make a bright career in IT & Software industry. Structured Query Language is a trending process to create the database for a company/organizations/websites and how to manage the data. Here you will find out the basic SQL interview questions asked at the time of face-to-face interview or sometimes on telephonic.

Structured Query Language (SQL) Interview Questions and Answers

1-What is RDBMS?

The DBMS, Data Base Management System, is a software application which stores your information in information records, for the effective administration of the information. The information is all around organized and all around recorded for simple control in DBMS. The software applications like Microsoft Access, Oracle, MySQL, Microsoft SQL Server goes under DBMS classification.

2-Describe Different Types of SQL Keys

Super Key

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, Alternate key are the subset of Super Keys.

Primary Key

Primary Key of a relational table uniquely recognizes every record in the table. It can be either be a typical credit that is ensured to be remarkable. You can comprehend it by school illustration. In a school, school name ought to be same of any understudy yet roll number never same as an understudy in a school.

Candidate Key

A candidate is a subset of a super key. A candidate key is a solitary field or minimal mix of fields that particularly distinguishes every record in the table. There can be different Candidate Keys in one table. Every Candidate Key can act as Primary Key.

Alternate key

The alternate key is a key that can be work as a primary key.
Example: for a student table, the primary key would be the student number, and an alternate key might be the student name. The alternate key would allow us to retrieve the records in alphabetic order by the student name.

Composite/Compound Key

Composite Key is a combination of more than one columns of a table. It can be a Candidate key, Primary key.

Unique Key

A unique key is a key of some secondary index having a unique constraint. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

Foreign Key

Foreign Key is a column or a mix of sections whose values match a Primary Key in a different table. The foreign key can be used to cross-reference tables.

3 – What’s the difference between DELETE TABLE and TRUNCATE TABLE commands

SQL TRUNCATE TABLE statement used to completely remove all table records. Not supporting to a WHERE clause. It is faster. TRUNCATE operation, not rollback.

The DELETE order is utilized to expel lines from a table in light of WHERE condition. The DELETE explanation uproots lines each one in turn and records a passage in the exchange log for each erased line.

The DROP charge expels a table from the database. Every one of the tables’ lines, lists and benefits will likewise be evacuated. No DML triggers will be terminated. The operation can’t be moved back. DROP and TRUNCATE are DDL summons, while DELETE is a DML charge.

4- What is an index? What are the types of indexes?

The list is a strategy utilized for speedier recovery of records, Just like a book list helps us in finding the section and page number, a prophet file speeds up access time to the lines.

Bunched index: It is a kind of list where the intelligent and physical request of the table records are in the same request.

Non-Clustered Index: It is a kind of file where the consistent and physical request of the table records are NOT in the same request. This file ordinarily comprises of particular segments characterized in the record creation, not at all like grouped file which applies to the whole base table.

There must be one grouped file and up to 249 non bunched lists on the table. All non grouped records will likewise incorporate either bunch file key or ROWID relying upon whether the table is grouped or stack that goes about as pointer back to the base table. Question enhancer utilizes records and performs either output or look for operation on them to bring the information.

5- What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Aside from SELECT inquiries, you can utilize WHERE provision with UPDATE and DELETE statement yet HAVING proviso must be utilized with the SELECT inquiry. Whenever WHERE and HAVING statement are utilized together as a part of a SELECT question with a total capacity, WHERE provision is connected first on individual columns and just lines which pass the condition is incorporated for making bunches. When the gathering is made, HAVING provision is utilized to channel bunches based upon condition determined.

One syntax level difference between WHERE and HAVING clause is that former is used before GROUP BY clause, while later is used after GROUP BY clause.

6- What is Normalization?

Database standardization is an informal outline and association process connected to information structures in view of tenets that help to build social databases. In a social database plan, the procedure of sorting out information to minimize repetition is called standardization. Standardization generally includes separating a database into two or more tables and characterizing connections between the tables.

To begin with Normal Form (1NF)

To begin with, ordinary structure (1NF) sets the exceptionally essential standards for a sorted out database:

  • Eliminate duplicative sections from the same table.
  • Create separate tables for every gathering of related information and recognize every line with an interesting section or set of segments (the primary key).

Second Normal Form (2NF)

  • Meet every one of the prerequisites of the main ordinary structure.
  • Remove subsets of information that apply to various columns of a table and place them in independent tables.

Third Normal Form (3NF)

Third typical structure (3NF) goes one substantial above and beyond:

  • Meet every one of the prerequisites of the second ordinary structure.
  • Remove segments that are not subordinate upon the essential key.

Fourth Normal Form (4NF)

At last, the fourth ordinary structure (4NF) has one extra prerequisite:

  • Meet every one of the necessities of the third ordinary structure.
  • A connection is in 4NF in the event that it has no multi-esteemed conditions.

7- What is Stored Procedure?

A system running in the database that can take complex activities in light of the inputs you send it. Utilizing a put-away technique is speedier than doing likewise take a shot at a customer, in light of the fact that the system runs right inside the database server. Put away methodology is normally composed in PL/SQL or Java. For instance, operations on a representative database (enlist, fire, advance, lookup) could be coded as put away systems executed by application code. Put away techniques can be assembled and executed with various parameters and results, and they might have any mix of info, yield, and data/yield parameters.

8- What is the Difference between Function and Stored Procedure?

Systems can have include, yield parameters for it though capacities can have just data parameters.

Methodology permits select and additionally DML(INSERT/UPDATE/DELETE) articulations in it while capacity permits just select explanation in it.

Capacities can be called from technique while strategies can’t be called from capacity.

Capacities are utilized as a part of the SQL articulations anyplace in the WHERE/HAVING/SELECT. Put away Procedures can’t be utilized as a part of the SQL proclamations anyplace in the WHERE/HAVING/SELECT

Put away Procedures can’t be utilized as a part of the SQL articulations anyplace in the WHERE/HAVING/SELECT. Capacities are utilized as a part of the SQL explanations anyplace in the WHERE/HAVING/SELECT

8- What is the STUFF function and how does it differ from the REPLACE function?

STUFF – Delete a specified length of characters and insert
another set of characters at a specified starting point.
For example: SELECT STUFF(‘asdfghjkl’, 3, 5, ‘XYZ’)
Go
here the result set is:

asXYZkl

REPLACE – Replace all occurrences of the second given string
expression in the first string expression with a third
expression.
For Example: SELECT REPLACE(‘Abhay’, ‘a’, ‘LLT’)
Here the result set is:

LLTbhLLTy
9- What is database replication? What are the different types of replication you can set up in SQL Server?

Replication is the procedure of duplicating/moving information between databases on the same or diverse servers. SQL Server underpins the accompanying sorts of replication situations:

  1. Snapshot replication
  2. Transactional replication (with immediate updating subscribers, with queued updating subscribers)
  3. Merge replication

10- What is sub-query?

A subquery is utilized to return information that will be utilized as a part of the primary inquiry as a condition to assist confine the information to be recovered. Subqueries are utilized with the SELECT, INSERT, UPDATE, and DELETE explanations. A sub-question is executed by walling it in inside another inquiry. Sub inquiries are utilized for returning a single line as a nuclear worth. A sub-question is settled in DML articulations and encased in the enclosure.

  • A subquery can incorporate discretionary WHERE, GROUP BY, and HAVING statements.
  • A subquery cannot include COMPUTE or FOR BROWSE statements.
  • You can incorporate an ORDER BY statement just when a TOP condition is incorporated.

11- What is the difference between a Local and a Global temporary table?

A neighborhood provisional table exists just for the term of an association or, if characterized inside a compound articulation, for the span of the compound explanation.

A worldwide provisional table stays in the database for all time, how, ever the lines exist just inside of a given association. At the point when an association is shut, the information in the worldwide brief table vanishes

Provisional Table structure can be changed after it’s creation it suggests we can utilize DDL explanations ALTER, CREATE, DROP. Table Variables doesn’t bolster DDL articulations like ALTER, CREATE, DROP and so forth, suggests we can’t change the structure of Table variable nor we can drop it unequivocally.

A standout amongst the most well-known MYTH about Temporary Table and Table Variable is that: Temporary Tables are made in TempDB and Table Variables are made In-Memory. Certainty is that both are made in TempDB, beneath Demos demonstrate this reality.

12- How to implement one-to-one, one-to-many and many-to-many relationships?

One-to-one: Use a foreign key to the referenced table:
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
# “link back” if you need

One-to-many: Use a foreign key on the many side of the relationship linking back to the “one” side:

teachers: teacher_id, first_name, last_name # the “one” side

classes: class_id, class_name, teacher_id # the “many” side

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name

classes: class_id, name, teacher_id

student_classes: class_id, student_id # the junction table

13- What are different Types of Join?

Internal Join

The internal join is the most well-known of all the join sorts. Most DBAs use them when they’re searching for information that matches up between two tables. The inward join associates two tables on the qualities that are coordinating.

External Join

The external join is like the internal join in that it incorporates every one of the lines that match. Where the external join varies is that it likewise incorporates columns that exist in one table, however, don’t have a match in the other table. The lines that don’t match are appeared as nulls.

Left join

The LEFT JOIN catchphrase gives back all columns from the left table (table1), with the coordinating lines in the right table (table2). The outcome is NULL in the right side when there is no match.

RIGHT JOIN

The RIGHT JOIN watchword gives back all lines from the right table (table2), with the coordinating lines in the left table (table1). The outcome is NULL in the left side when there is no match.

CROSS JOIN

SQL CROSS JOIN will give back all records where every line from the primary table is consolidated with every line from the second table. Which additionally mean CROSS JOIN gives back the Cartesian result of the arrangements of columns from the joined tables.

SELF JOIN

The SQL SELF JOIN is utilized to join a table to itself as though the table were two tables, briefly renaming no less than one table in the SQL explanation.

14-Describe User-defined functions?

Client characterized capacities are made for a database and can be utilized as a part of scripts, put away techniques, triggers and other client characterized capacities that are characterized inside of the database. Similarly, as with put away systems, they help to modularise your Transact-SQL (T-SQL) code and enhance practicality by permitting you to bring together a rationale. Client characterized capacities can be isolated into two primary classifications. These are scalar functionsand table-esteemed capacities.

Scalar Functions

Scalar capacities are like the capacities we have utilized as a part of prior articles. They can alternatively acknowledge one or more parameters and return a solitary worth.

Table-Valued Functions

The second sort of client characterized capacity is the table-esteemed capacity. As the name proposes, a table-esteemed capacity gives back a table variable. Table-esteemed capacities are frequently utilized as “parameterised perspectives” as they can be incorporated into a spot of tables in a question and can acknowledge contentions.

15- What is the system function to get the current user’s user id?

USER_ID().Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

16-What is views in sql?

SQL VIEWS are information questions, and like SQL Tables, they can be questioned, overhauled, and dropped. A SQL VIEW is a virtual table containing segments and columns with the exception of that the information contained inside a perspective is produced powerfully from SQL tables and does not physically exist inside the perspective itself. Despite the fact that a SQL VIEW is dealt with like an information object in SQL, no information is really put away within the perspective itself.

17- what is Triggers?

Triggers are a unique sort of Stored Procedure that executes naturally when a client tries to change a database. Triggers are worked for SELECT, UPDATE, INSERT and DELETE proclamations; at whatever point a client tries to executes these questions to perform an exchange, triggers stop him and keep our database secure.

DDL Triggers

In SQL Server we can make triggers on DDL articulations (like CREATE, ALTER, and DROP) and certain framework characterized put away techniques that perform DDL-like operations.

DML Triggers

In SQL Server we can make triggers on DML explanations (like INSERT, UPDATE, and DELETE) and put away methods that perform DML-like operations. DML Triggers are of two sorts

CLR Triggers

CLR triggers are the unique sort of triggers that in light of the CLR (Common Language Runtime) in .net structure.

18- What is a “constraint”?

A requirement permits you to apply basic referential respectability checks to a table. There are four essential sorts of requirements that are right now upheld by SQL Server: PRIMARY/UNIQUE – authorizes the uniqueness of a specific table segment. DEFAULT – indicates a default esteem for a section in the event that a supplement operation does not give one.

19-What is a CTE?

A typical table expression (CTE) is a provisional named result set that can be utilized inside different proclamations such as SELECT, INSERT, UPDATE, and DELETE. It is not put away as an item and its lifetime is restricted to the question.

20- How can find 3rd Highest salary of Employee?

  1. Select TOP 1 Salary as ‘3rd Lowest Salary’
  2. from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC)
  3. a ORDER BY Salary DESC

Handling SQL process is not an easy task, Should have proper knowledge about the execution. These top 20 SQL interview questions and answers list will clear your doubts and will give you a vast knowledge to face the challenges in this competitive industry.

LEAVE A REPLY

Please enter your comment!
Please enter your name here