In SQL, which type of JOIN returns all rows from at least one of the tables mentioned from the FROM clause, providing those rows meet any WHERE or HAVING search conditions?

In SQL, which type of JOIN returns all rows from at least one of the tables mentioned from the FROM clause, providing those rows meet any WHERE or HAVING search conditions?

• Table Join
• Outer Join
• Comparison Join
• Inner Join 

 

EXPLANATION

Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in
the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.

SOURCE

https://technet.microsoft.com/en-us/library/ms187518(v=sql.105).aspx

Read More

When called without an argument, which of the following returns the SQL Server login of the current security context.

When called without an argument, which of the following returns the SQL Server login of the current security context.

• SUSER_SNAME()
• SUSER_SID()
• ORIGINAL_LOGIN()
• USER_NAME() 

 

EXPLANATION

SUSER_SNAME() - returns the login of the current security context.

ORIGINAL_LOGIN() - returns login of original connection context. It is not affected by context-switching.

USER_NAME() - returns the database user name of the current security context.

SUSER_SID() - Returns the Security Identifier (SID) of the current security context.

SQL Server has system-level logins and database users. While they often have the same username, they are distinct from one another. Logins are needed to gain access and set system-level permissions to SQL Server, while Users are needed for access and permissions to specific databases.

The current security context in SQL Server can be changed, known as context-switching or impersonation, with the use of the EXECUTE AS and REVERT statements in a script or batch.

Additional reading:

SQL Server Security Principals - https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine?view=sql-server-2017

Context-switching - https://sqlity.net/en/1783/changing-security-context-execute-revert/

SOURCE

https://docs.microsoft.com/en-us/sql/t-sql/functions/security-functions-transact-sql?view=sql-server-2017

Read More

On MS SQL Server, which column property lets you indicate an auto increment column?

On MS SQL Server, which column property lets you indicate an auto increment column?

• incremental
• identity
• incremental_identity
• increment 

 

EXPLANATION

Identity let's you say that column have an auto increment value, from X by Z, for example: identity(1,1), says start on 1 and sum 1 by 1 (1,2,3,4,5,6......). identity(55,10) says start on 55 and go 10 by 10 (55,65,75,85,95,.....).
This is fine when you have a registry table (like a log, for example), and you need a primary key that always its unique. For that you can use the GetDate() function (that is unique too XD).

SOURCE

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

Read More

In T-SQL, which of the following is NOT a requirement to define a table expression?

In T-SQL, which of the following is NOT a requirement to define a table expression?

• All columns must have a name
• Column names must be unique
• Must be preceded by WITH
• Cannot use ORDER BY to guarantee presentation order 

 

 

EXPLANATION

Table expressions in SQL Server are named query expressions that represent a valid relational table. SQL Server supports four types of table expressions: Common Table Expressions (CTEs), Derived Tables, Views and In-line Table-Valued Functions. Only a CTE definition needs to be preceded by WITH.

T-SQL allows the ORDER BY clause in conjunction with TOP, OFFSET or FOR XML for processing purposes, but this does not guarantee presentation order in an outer query that references the table expression.

SOURCE

https://www.microsoftpressstore.com/articles/article.aspx?p=2233323

Read More

What command would you use to create a row in SQL?

What command would you use to create a row in SQL?

• INSERT
• MAKE
• ADD
• CREATE 

 

EXPLANATION

To create a row in a SQL database, you use the command INSERT.

If we want to insert a single row in a table emp.

Query:
insert into emp values(101,’hari’);

It will add a new row has employee id 101 and employee name ‘hari’ into th emp table.

Read More

What does the Transact-SQL server RAND function do?

What does the Transact-SQL server RAND function do?

• Aggregates a subset of random numbers
• Generate a random number
• Requests a range of numbers from a subset
• Selects an random number from subset 

 

EXPLANATION

 SQL Server: RAND Function

This SQL Server tutorial explains how to use the RAND function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the RAND function can be used to return a random number or a random number within a range.

Syntax

The syntax for the RAND function in SQL Server (Transact-SQL) is:

RAND( [seed] )

Parameters or Arguments

seed

Optional. If specified, it will produce a repeatable sequence of random numbers each time that seed value is provided.

Note

• The RAND function will return a value between 0 and 1 (not inclusive), so value > 0 and value < 1.
• The RAND function will return a completely random number if no seed is provided.
• The RAND function will return a repeatable sequence of random numbers each time a particular seed value is used.

Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:

SELECT RAND()*(b-a)+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT RAND()*(25-10)+10;

The formula above would generate a random decimal number between 10 and 25, not inclusive.

TIP: This formula would generate a random decimal number that is > 10 and < 25 but it would never return exactly 10 or 25.

Random Integer Range

To create a random integer number between two values (range), you can use the following formula:

SELECT FLOOR(RAND()*(b-a+1))+a;

Where a is the smallest number and b is the largest number that you want to generate a random number for.

SELECT FLOOR(RAND()*(25-10+1))+10;

The formula above would generate a random integer number between 10 and 25, inclusive.

TIP: This formula would generate a random integer number that is >= 10 and <= 25.

Applies To

The RAND function can be used in the following versions of SQL Server (Transact-SQL):

• SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example of Random Number

Let's explore how to use the RAND function in SQL Server (Transact-SQL) to generate a random number between 0 and 1, not inclusive.
For example:

SELECT RAND();
Result:  0.143811355073783     (no seed value, so your answer will vary)

SELECT RAND(9);
Result:  0.713741056982989     (with seed value of 9)

SELECT RAND(-5);
Result:  0.713666525097956     (with seed value of -5)

Example of Random Decimal Range

Let's explore how to use the RAND function in SQL Server (Transact-SQL) to generate a random decimal number between two numbers (ie: range).
For example, the following would generate a random decimal value between 1 and 10, not inclusive (random number would be greater than 1 and less than 10):

SELECT RAND()*(10-1)+1;
Result:  5.09104269717813      (no seed value, so your answer will vary)

SELECT RAND(9)*(10-1)+1;
Result:  7.4236695128469       (with seed value of 9)

SELECT RAND(-5)*(10-1)+1;
Result:  7.42299872588161      (with seed value of -5)

Example of Random Integer Range

Let's explore how to use the RAND function in SQL Server (Transact-SQL) to generate a random integer number between two numbers (ie: range).
For example, the following would generate a random integer value between 10 and 20, inclusive:

SELECT FLOOR(RAND()*(20-10+1))+10;
Result:  19                   (no seed value, so your answer will vary)

SELECT FLOOR(RAND(9)*(20-10+1))+10;
Result:  17                   (with seed value of 9)

SELECT FLOOR(RAND(123456)*(20-10+1))+10;
Result:  10                   (with seed value of 123456)

The SQL Server (Transact-SQL) RAND function can be used to return a random number or a random number within a range.

Read More

What is the default TCP port used by Microsoft SQL server?

What is the default TCP port used by Microsoft SQL server?

• 1433
• 3389
• 1723
• 987

EXPLANATION

The default (and IANA official port) for Microsoft SQL Server is TCP 1433.
Port 3389 is the default port for Microsoft RDP.
Port 1723 is the default port for PPTP VPN.
Port 987 is used by the Companyweb sharepoint site on Microsoft Small Business Server 2008 and later.

SOURCE

https://msdn.microsoft.com/en-us/library/cc646023.aspx

Read More

In Microsoft SQL Server, why does SELECT 1/2 return 0?

In Microsoft SQL Server, why does SELECT 1/2 return 0?

• Numerator and denominator are both integers, so the result will be an integer
• It doesn't - it returns 0.5
• A bug in the code performing the division
• A bug in the display in SSMS 

 

EXPLANATION

Due to the data type of the numerator and denominator being an "int", the return type is also assigned as an integer. While internally, the engine performs the calculation as 0.5, because the data type is an integer, the value gets converted to 0 and is returned as such.
Another way to say this is that the mathematical answer is 0.5 but because the system is only able to return an integer (whole number) the answer becomes 0 (the "whole number" in the answer).

Read More

What does the ALTER TABLE clause do ?

What does the ALTER TABLE clause do ?

• The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table column
• The SQL ALTER TABLE clause is used to insert data into database table
• The SQL ALTER TABLE deletes data from database table
• The SQL ALTER TABLE clause is used to delete a database table 

 

EXPLANATION

Answer is A. SQL ALTER TABLE:  Modifies a table definition by altering, adding, or dropping columns and constraints, reassigning and rebuilding partitions, or disabling or enabling constraints and triggers.

SOURCE

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

Read More

Which of the following are built-in functions in SQL?

Which of the following are built-in functions in SQL?

• SUM, AVG, MIN, MAX, MULT
• SUM, AVG, MULT, DIV, MIN
• SUM, AVG, MIN, MAX, NAME
• COUNT, SUM, AVG, MAX, MIN 

 

 

EXPLANATION

COUNT, SUM, AVG, MAX, and MIN are all built-in functions in SQL.

Read More

In SQL, how do you combine the contents of 2 (or more) queries, into a single, combined table without omitting duplicates?

In SQL, how do you combine the contents of 2 (or more) queries, into a single, combined table without omitting duplicates?

• UNION
• UNION ALL
• FULL JOIN
• JOIN 

 

EXPLANATION

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. A UNION statement effectively does a SELECT DISTINCT on the results set.
Here's a good, quick resource: http://www.codefari.com/2015/10/what-is-difference-between-union-and.html

SOURCE

http://www.codefari.com/2015/10/what-is-difference-between-union-and.html

Read More

For every unique constraint on a column, SQL server will create which of the following?

For every unique constraint on a column, SQL server will create which of the following?

• An entry for that column
• A non-clustered index for that column
• A clustered index for that column
• A filtered index 

 

EXPLANATION

For every unique constraint on a column, SQL server creates a non-clustered index. A non-clustered index has a structure separate from the data rows. It contains non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.

Read More

Which of the following is a requirement of setting up SQL Server AlwaysOn High Availability?

Which of the following is a requirement of setting up SQL Server AlwaysOn High Availability?

• Windows Server Failover Clustering (WSFC)
• Network Load Balancing (NLB)
• Windows Internal Database
• Internet Information Services (IIS) 

EXPLANATION

AlwaysOn requires that failover clustering is installed on each node and that the nodes are joined to the cluster prior to enabling AlwaysOn High Availability on the SQL server. AlwaysOn has to be enabled through the SQL Server Configuration Manager prior to being configured in SQL Server Management Studio (SSMS).

Read More

What is the default port that Microsoft SQL Server Database Engine communicates on?

What is the default port that Microsoft SQL Server Database Engine communicates on?

• 25
• 43
• 443
• 1433

EXPLANATION

By default, Microsoft SQL Server communicates out of the box on port 1433.  You can change the port in the SQL Configuration Manager.

Read More