With the advent of a monumental shift towards using database management systems, the need for individuals who can control those databases have arisen. This has given a huge impetus to the demand for the SQL Server professionals in the job market. Many companies are on a constant lookout for SQL Server professionals to meet the needs of their database management system. In this blog, we have tried to help all the aspirants of SQL server jobs as well as SQL server enthusiasts to sharpen up their knowledge of SQL server.
Here are some of the most frequently asked questions in the field of SQL Server. The best part is that we have even categorized them into various popular sub-divisions in the field of SQL such as MySQL, SQL PL, Oracle, etc. Enjoy reading!
Q1). Which is the latest version of SQL Server and when it is released?
SQL Server 2017 and the release date of its CU was 25.03.2019
Q2). What is it that the SQL Server is mainly used for?
SQL Server is one of the most prominent Relational Database Management Systems. This is a product from Microsoft to store and deal with the data in a database.
Q3). Which language does the SQL Server support?
SQL Server depends on the execution of the SQL which is also called Structured Query Language to work with the information inside the database.
Q4). Which TCP/IP port does the SQL Server primarily runs on?
By default, the SQL Server runs on port 1433.
Q5). Name the different types of index configurations possible for a SQL table?
In SQL a table can have any one of the following index configurations-
Q6). What is the recovery model?
Recovery model fundamentally discloses to SQL Server what information ought to be kept in the exchange log record and for to what extent.
Q7). What is a FULL Back up in SQL environment?
A full backup is the most widely recognized kind of reinforcement in SQL Server. This is the finished backup of the database. It additionally contains some portion of exchange log so it very well may be recovered. Q8). When is the UPDATE_STATISTICS command used?
As the name infers UPDATE_STATISTICS command refreshes the measurements utilized by the file to make the search simpler.
Q9). What is Mirroring?
Mirroring is a high accessibility arrangement. It is intended to keep up a hot reserve server which is predictable with the essential server as far as an exchange. Exchange Log records are sent legitimately from a central server to an auxiliary server which stays up with the latest with the primary server.
Q10). Is it possible to take the full database backup in Log shipping?
Truly, we can take the full database reinforcement. It won't influence the log shipping.
Q11). How can you check the SQL Server version?
SQL Server version can be checked by running the following command- SELECT @@Version
Q12). What do you understand the PRIMARY KEY?
The primary key is a section whose qualities extraordinarily distinguish each column in a table. Primary key qualities can never be reused.
Q13). What is a heap?
A heap is a table that does not contain any clustered file or non-clustered index.
Q14). List the different normalization forms?
Q15). Why is replication required on the SQL Server?
This is majorly used to expand the limit of the perusing and to give an alternative to its clients to choose among different distinctive servers to play out the read/compose operations.
Q16). What is a Database?
The database is an organized form of information for simple access, putting away, recovery and overseeing of information. This is otherwise called organized type of information which can be gotten to from multiple points of view.
Q17). What are the tables and Fields?
A table is a set of information that is sorted out in a model with Columns and Rows. Columns can be sorted as vertical, and Rows are horizontal. A table has indicated a number of sections that are called fields yet can have any number of columns which is called record.
Q18). What is RDBMS?
RDBMS represents Relational Database Management System. RDBMS store the information into the gathering of tables, which is connected by normal fields between the sections of the table. It likewise gives social administrators to control the information put away into the tables.
Q19). What are joins in SQL?
A JOIN clause is generally utilized to consolidate rows from at least two tables, in light of a related section between them. It is utilized to blend two tables or recover information from there.
Q20). What are Constraints?
Constraints are utilized to determine the point of confinement on the information kind of the table. It very well may be indicated while making or changing the table statement.
Q21). Illustrate the difference between SQL and MySQL.
SQL is a standard language which represents Structured Query Language dependent on the English language though MySQL is a database management framework. SQL is the center of the social database which is utilized for getting to and overseeing database, MySQL is an RDMS (Relational Database Management System, for example, SQL Server, Informix and so on.
Q22). What do you understand by the term data integrity?
Data Integrity characterizes the precision just as the consistency of the data put away in a database. It additionally characterizes integrity limitations to authorize business controls on the information when it is gone into an application or a database.
Q23). Write a SQL query which is used to display the current date?
In SQL, there is a work in capacity called GetDate() which restores the current timestamp/date.
Q24). What do you mean by Denormalization?
Denormalization refers to a strategy which is utilized to get to information from higher to bring down types of a database. It causes the database supervisors to expand the execution of the whole framework as it brings repetition into a table. It includes the excess information into a table by joining database questions that consolidate information from different tables into a solitary table.
Q25). Illustrate the difference between DROP and TRUNCATE commands?
DROP command evacuates a table and it can't be moved over from the database though TRUNCATE command expels every one of the rows from the table.
Q26). Do you think that NULL values are same as that of zero or a blank space?
No, A NULL value isn't at all equivalent as that of zero or a blank space. Null value speaks to a value which is inaccessible, obscure, allotted or not pertinent though zero is a number and blank space is a character.
Q27). Illustrate the difference between cross join and natural join?
The cross join delivers the cross product or Cartesian result of two tables while the regular join depends on every one of the columns having a similar name and information types in both the tables.
Q28). What do you understand by a subquery in SQL?
A subquery is an inquiry inside another question where a query is characterized to recover information or data again from the database. In a subquery, the external question is called as the main query through the inner query is called subquery. Subqueries are constantly executed first and the consequence of the subquery is passed on to the principal inquiry. It very well may be settled inside a SELECT, UPDATE or some other question. A subquery can likewise utilize any correlation administrators, for example, >,< or =.
Q29). What are the different operators available in SQL?
There are three kinds of operators that are available in SQL, namely:
Q30). Why do you need group functions in SQL?
Group functions work with the set of rows and return one outcome for every given group. A portion of the generally utilized group functions is AVG, COUNT, MAX, MIN, SUM, VARIANCE.
Q31). What is normalization?
Standardization is the way toward limiting redundancy and dependency by sorting out fields and table of a database. The fundamental point of Normalization is to include, erase or adjust field that can be made in a solitary table.
Q32). What is Index?
An index is acting tuning strategy for permitting quicker recovery of records from the table. A record makes a section for each esteem and it will be quicker to recover information.
Q33). What is a stored procedure?
Stored Procedure is a function comprises of numerous SQL explanation to get to the database framework. A few SQL proclamations are merged into a putaway strategy and execute them at whatever point and wherever required.
Q34). What is Datawarehouse?
The data warehouse is a focal vault of information from numerous wellsprings of data. That information is solidified, changed and made accessible for the mining and web-based handling. Stockroom information has a subset of information called Data Marts.
Q35). What are all types of user-defined functions?
Three types of user-defined functions are.
Q36). What is the command to fetch common records from two tables?
Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam
Q37). List out the ACID properties.
Following are the four properties of the ACID.
Q38). What are string functions used for in SQL?
In SQL, string capacities are utilized basically for the purpose of string manipulation
Q39). What is Collation?
Collation is characterized as a lot of standards that decide how character information can be arranged just as thought about. Character information is arranged utilizing decides that characterize the right character grouping alongside choices for determining case-affectability, character width, highlight marks, kana character types.
Q40). What is a CLAUSE in SQL?
A SQL clause helps to restrain the outcome set by giving a condition to the question. A clause helps the columns from the whole arrangement of records.
Q41). What do you infer by an ALIAS command?
ALIAS can be given to any table or a segment. This alias name can be alluded in WHERE condition to distinguish a specific table or a column.
Q42). What are the local variables?
These variables can be utilized or exist just inside the function. These variables are not utilized or referred by some other function.
Q43). What are the global variables?
These factors are the factors which can be gotten to all throughout the program. Global variables can't be made at whatever point that function is called.
Q44). What is the syntax of STUFF function?
STUFF(string_expression,start, length, replacement_characters)
Q45). What is the need of the MERGE statement?
This statement allows conditional update or insertion of data into a table. It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
Q46). What do you mean by a recursive stored procedure?
Restrictive stored procedure refers to a stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps the programmers to use the same set of code n number of times.
Q47). List the ways in which a Dynamic SQL can be executed?
A dynamic SQL can be executed in the following ways-
Q48). What are the different types of joins in SQL?
The different kinds of joins are-
Q49). What is identity in SQL?
Q50). Why MySQL is used?
MySQL database server is reliable, quick and simple to utilize. This product can be downloaded as freeware and can be downloaded from the web.
Q51). What is the default port for MySQL Server?
Q52). How to represent ENUMs and SETs internally?
ENUMs and SETs are utilized to signify controls of two because of storage optimizations.
Q53). What storage engines are used in MySQL?
Storage engines are also called the table types. The data is stored in various files by way of several techniques.
Q54). What are the drivers in MySQL?
The drivers available in MySQL are:
Q55). What does myisamchk do?
t compresses the MyISAM tables, which in return decreases their disk or you can say the memory usage.
Q56). What are federated tables?
Federated tables are the tables that permit access to the tables located on the other databases located on other servers.
Q57). What, if a table has one column defined as TIMESTAMP?
Timestamp field is used to get the latest timestamp whenever a row is altered.
Q58). What is BLOB?
A BLOB in MySQL is a binary large object that can contain a variable amount of data in it.
Q59). How many types of BLON objects are there?
There are four types of BLOB –
Q60). What is PL SQL?
PL SQL is a procedural language which has intelligent SQL, just as procedural programming language builds like conditional branching and iteration.
Q61). Explain the uses of a cursor.
The cursor is a named private territory in SQL from which data can be gotten to. They are required to process each column independently for questions which return various lines.
Q62). What are the two types of exceptions?
The error handling part of PL/SQL square is called Exception. They have two sorts: user_defined and predefined.
Q63). Explain Raise_application_error.
It is a procedure of package DBMS_STANDARD that permits issuing of user_defined blunder messages from database trigger or put away sub-program.
Q64). Define Implicit Cursor. A cursor is implicit by default. The client can't control or process the data in this cursor.
Q65). What are Explicit Cursors?
If a query returns numerous columns of information, the program characterizes an express cursor. This enables the application to process each column consecutively as the cursor returns it.
Q66). What is a mutating table error? It happens when a trigger endeavors to refresh a column that it is as of now utilizing. It is fixed by utilizing perspectives or brief tables, so the database chooses one and updates the other.
Q67). When is a declare statement required? DECLARE statement is utilized by PL SQL mysterious squares, for example, non-put away methods. In the event that it is utilized, it must start things out in an independent document.
Q68). How many triggers can be applied to a table? 12 Q69). List the 3 basic parts of a trigger.
Q70). What is an Intersect?
is the result of two tables and it records just coordinating rows.
Q71). What is the use of SYSDATE keyword?
SYSDATE alludes to the present server framework date. It is a pseudo column.
Q72). What does fetching a cursor do?
Fetching a cursor peruses ResultSet column by row.
Q73). What does closing a cursor do?
Closing a cursor clears the private SQL zone just as de-allots memory.
Q74). What is Consistency?
Consistency demonstrates that information won't be reflected by different clients until the information is submitted, with the goal that consistency is maintained.
Q75). What is an autonomous transaction?
An autonomous transaction is an independent exchange of the primary or parent exchange. It isn't settled in the event that it is begun by another exchange.
Q76). Where are the Pre_defined_functions located?
They are located and stored in the standard package called "Functions, Procedures, and Packages"
Q77). Can you execute 2 queries simultaneously in a Distributed Database System?
Yes, they can be executed simultaneously as the queries are always independent of each other
Q78). What is the function that is used to transfer a PL/SQL table log to a database table?
In order to transmit a PL/SQL table log a database log table function called "PROCEDURE ps2db" is utilized.
Q79). Elucidate the use of function "module procedure" in PL/SQL?
The "module procedure" allows changing all the lines of code in a certain program unit with a single procedure call.
Q80). What is Oracle?
Oracle is one of the prevalent databases given by Oracle Corporation, which deals with social administration ideas and subsequently it alludes as Oracle RDBMS also.
Q81). Difference between varchar and varchar2 data types?
Varchar can store up to 2000 bytes and varchar2 can store up to 4000 bytes. Varchar will consume space for NULL qualities and Varchar2 won't consume any space. Both are varied with respect to space.
Q82). In which language Oracle has been developed?
Oracle has been primarily developed using the language C.
Q83). What is RAW datatype?
The RAW datatype is utilized to store values in the paired information group. The most extreme size for raw in a table in 32767 bytes.
Q84). What is the use of NVL function?
The NVL work is utilized to supplant NULL values with another or given value. Example is – NVL(Value, replace value)
Q85). What are nested tables?
A nested table is a data type in Oracle which is utilized to help sections containing multi esteemed properties. It additionally holds the whole subtable.
Q86). What is DML?
Information Manipulation Language (DML) is utilized to get to and control information in the current articles. DML statements are embedded, select, update and erase and it won't certainly submit the current transaction.
Q87). What is the key-preserved table?
A table is set to be a key saved table if each key of the table can likewise be the key to the consequence of the join. It assurances to return just a single duplicate of each column from the base table.
Q88). What do you mean by GROUP BY Clause?
A GROUP BY condition can be utilized in the select explanation where it will gather information over numerous records and gathering the outcomes by at least one sections. Q89). How can we create privileges in Oracle?
A benefit is only ideal to execute a SQL inquiry or to get to another client object. The benefit can be given as framework benefit or client benefit. [sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql]
Q90). What is VArray?
VArray is Oracle information typically used to have sections containing multivalued qualities and it can hold a limited cluster of qualities.
Q91). How do we get field details of a table?
Describe <Table_Name> is utilized to get the required field details of a specified table.
Q92). What are the SET operators?
SET operators are utilized with at least two inquiries and those administrators are Union, Union All, Intersect and Minus.
Q93). How can we delete duplicate rows in a table?
Duplicate rows in the table can be erased by utilizing ROWID
Q94). What is an integrity constraint?
An integrity constraint is a presentation characterized a business rule for a table segment. Respectability requirements are utilized to guarantee exactness and consistency of information in a database.
Q95). What is an ALERT?
An alert is a window which shows up in the focal point of the screen overlaying a segment of the present showcase.
Q96). What is a hash cluster?
Hash Cluster is a procedure used to store the table for quicker recovery. Apply hash an incentive on the table to recover the rows from the table.
Q97). What is the parameter mode that can be passed to a procedure?
IN, OUT and INOUT are the methods of parameters that can be passed to a strategy.
Q98). What are the privileges?
Privileges are the rights to execute SQL proclamations – implies Right to interface and associate.
Q99). What is the data type of DUAL table?
The DUAL table is a one-section table present in prophet database. The table has a solitary VARCHAR2(1) segment called DUMMY which has a value of 'X'.
Q100). What is the quickest way to fetch the data from a table?
The snappiest method to bring the information is to use ROWID in the SQL Query.
Once you have read all these questions before going for your interview, I am sure that you will feel inert confidence in yourself. Also, take note that in case you do not know an answer, do not lose hope or start thinking negatively outright. It is neither possible nor expected from you to know everything under the sun about the topic. Best of Luck!
JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.
Receive Latest Materials and Offers on SQL Server Course