Top SQL interview questions and answers for freshers’ posts have been created for people who want to make a bright career in the IT and software industry. Structured Query Language is a trending process to create the database for a company/organization/website and how to manage the data.
Here you will find out the basic SQL programming interview questions and answers asked at the time of the face-to-face interview or sometimes on the telephone. You can also check out the SQL Certification Course if you are planning a career in this field.
List of Top SQL Interview Questions and Answers
We have added a list of Top SQL Interview Questions and Answers here to help you. You will get to know everything about SQL but to know about SQL vs SQLite you will have to go to another article.
1- What is RDBMS?
The DBMS, Data Base Management System, is a software application that 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. Software applications like Microsoft Access, Oracle, MySQL, and Microsoft SQL Server go under the DBMS classification.
2- Describe Different Types of SQL Keys
Super Key
A super key is a set of one or more than one key that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, and Alternate key are the subset of Super Keys.
Primary Key
The Primary Key of a relational table uniquely recognizes every record in the table. It can either be a typical credit that is ensured to be remarkable. You can comprehend it by school illustration. In a school, the school name ought to be the same as any understudy yet the roll number is never the 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 a Primary Key.
Alternate key
The alternate key is a key that can work as a primary key.
For 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’s name.
Composite/Compound Key
A composite Key is a combination of more than one column of a table. It can be a Candidate key or a 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
A 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 a WHERE clause. It is faster. TRUNCATE operation, not rollback.
The DELETE order is utilized to expel lines from a table in light of the 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 requests of the table records are in the same request.
Non-Clustered Index: It is a kind of file where the consistent and physical requests of the table records are NOT in the same request. This file ordinarily comprises particular segments characterized in the record creation, not at all like a 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 a 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 the WHERE provision with UPDATE and DELETE statements yet HAVING proviso must be utilized with the SELECT inquiry.
Whenever WHERE and HAVING statements 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 that pass the condition are incorporated for making bunches.
When the gathering is made, the HAVING provision is utilized to channel bunches based on the condition determined.
One syntax level difference between the WHERE and HAVING clauses is that the former is used before the GROUP BY clause, while the latter is used after the 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)
The third typical structure (3NF) goes substantially above and beyond:
- Meet every one of the prerequisites of the second ordinary structure.
- Remove segments that are not subordinate to 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 the 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.
So how are you learning from this post 20+ top SQL interview questions and answers?
8- What is the Difference between Function and Stored Procedure?
Systems can include, yield parameters for it though capacities can have just data parameters.
The methodology permits selection and additional DML(INSERT/UPDATE/DELETE) articulations in it while capacity permits just select explanations 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 placed 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:
- Snapshot replication
- Transactional replication (with immediate updating subscribers, with queued updating subscribers)
- Merge replication
10- What is the 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 in configuring the information to be recovered. Subqueries are utilized with the SELECT, INSERT, UPDATE, and DELETE explanations.
A sub-question is executed by walling it 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, however, 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 its creation it suggests we can utilize DDL explanations ALTER, CREATE, DROP.
Table Variables don’t bolster DDL articulations like ALTER, CREATE, DROP, and so forth, suggesting we can’t change the structure of the Table variable nor we can drop it unequivocally.
A standout amongst the most well-known MYTH about Temporary Tables and Table Variables is that: Temporary Tables are made in TempDB and Table Variables are made in In-Memory. Certainty is that both are made in TempDB, beneath Demos below demonstrate this reality.
Hope this answer will help you and if yes, you can comment on this post top SQL interview questions and answers, we will get back to you as soon as possible.
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, zip code, student_id # you can have a
# “Link back” if you need
One-to-many: Use a foreign key on the many sides 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 the 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 have 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 on 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 on 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. This additionally means 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 putting 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 functions and 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 “parameterized 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 are 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 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 are 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 execute 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 frameworks 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 in light of the CLR (Common Language Runtime) in the .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 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 the 3rd Highest salary of an Employee?
- Select TOP 1 Salary as ‘3rd Lowest Salary’
- from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC)
- an ORDER BY Salary DESC
Handling the SQL process is not an easy task, Should have proper knowledge about the execution. These 20+ top SQL interview questions and answers list will clear your doubts and will give you vast knowledge to face the challenges in this competitive industry.
