SQL Interview Questions – Part-6

What is an alias in SQL Server Agent?
Jobs can be scheduled and carried out using SQL Server Agent. Here, each job has one or more steps, and each of those steps has a task within it. So that a job may be scheduled to run, the Server Agent stores job information in the SQL Server.

Jobs, Schedules, Operators, and Alerts are the SQL Server Agent’s primary components.

What are the authentication modes in SQL Server?
Windows Authentication: In this mode, a Windows account is utilized to connect to the server. For authentication, the server in this case collects the username and password from the computers. Additionally, this setting disables the SQL server authentication mode.

Mixed Mode: You can connect to a SQL Server instance using either Windows authentication or SQL Server authentication by using the Mixed mode. For the database in this mode, the user sets a username and password.

What is the difference between local and global temporary tables?
The local temporary tables only exist for the duration of a connection or the duration of that statement. The global temporary tables exist permanently in the database and only the rows get deleted when the connection gets closed.

What TCP/IP port does SQL Server use?
The TCP/IP port 1433 is used by SQL Server.

What is the difference between SUBSTR and CHARINDEX?
SUBSTR function is used to extract a specific section of a string from another string.
Example: SUBSTRING(‘BigDataNSQL’,1,7)
Returns: BigData

CHARINDEX function is used to return the position of a character within a provided string.
Example: CHARINDEX(‘D’,’BigDataNSQL’,1)
Returns: 4

What is a relationship in DBMS? And how many types of relationships are there?
In a DBMS, a relationship is a case in which two entities are connected to one another. In this case, the foreign key table has a reference to the primary key of the other table.

The following is the list of relationships in DBMS:

One-to-One Relationship: This term is used to describe a relationship between one row in Table A and one row in Table B.
One-to-Many Relationship: Used when there is a relationship between one row in Table A and many rows in Table B.
Many-to-Many Relationship: When many rows in table A can be related to many rows in table B then it is called a many-to-many relationship.
Self-referencing Relationship: When a record in table A is associated with a record in the same table then it is called a self-referencing relationship.

What is OLTP?
Online transaction processing, or OLTP, ensures data integrity by adhering to data normalization requirements. By following these guidelines, complex information can be reduced to its most basic form.

What is OLAP?
A computing technique called OLAP (online analytical processing) enables users to quickly and selectively extract and query data in order to examine it from many angles. In other words, it is a technology that lets corporate data be extracted and seen from many perspectives by analysts. OLAP business intelligence queries are useful for a variety of planning tasks, including trend analysis, financial reporting, sales forecasting, and budgeting.

What is RDBMS?
Relational database management systems (RDBMS) are database management systems that maintain data as tables. We can establish connections between the tables. An RDBMS offers strong tools for data consumption by recombining the data elements from various files.

What are the properties of the relational table?
Relational tables have six properties:

  • Values are atomic. This means If there is a change, it will be applied to the entire set of rows you requested, or if there is an error, it won’t apply to any rows.
  • Values in columns are all of the same types.
  • Every row is distinct.
  • The order of the columns is unimportant.
  • The rows’ order doesn’t matter much.
  • Every column needs to have a unique name.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s