SQL Syntax

Question 1

What does it mean? “SET ANSI_NULLS ON”

Answer 1

Handling Nulls:
Equality comparison: NULL value will be evaluated to UNKNOWN.
Inequality comparison: NULL value will result in UNKNOWN.
Aggregate functions: NULL values are ignored.

ANSI NULLS is ON by Default

Question 2

What does “SET QUOTED_IDENTIFIER ON” mean?

Answer 2

String literals: Double quotation marks (“”) are used to enclose string literals.
Identifiers: Double quotation marks (“”) are used to enclose identifiers such as table names, column names, and aliases that contain special characters or reserved keywords.

Question 3

Stored Procedure General Format

Answer 3

CREATE PROCEDURE Procedure_Name
@Parameter DATA_TYPE
AS
BEGIN
xxxxxxxxxxx
END
GO

Question 4

What are the three types of Functions?

Answer 4

1-Inline (Table Value)
2-Multi Statement (Table Value)
3-Scalar

Question 5

Inline Function General Format

Answer 5

CREATE FUNCTION Function_Name (@Parameter DATA_TYPE)
RETURNS TABLE
AS
RETURN
xxxxxxxxxxxxxx

Question 6

Multi Statement Function General Format

Answer 6

CREATE FUNCTION Function_Name (@Parameter DATA_TYPE)
RETURNS Table_Name TABLE ([Column_Name] DATA_TYPE)
<@Table_Variable_Name, sysname, @Table_Var> TABLE
AS
BEGIN
xxxxxxxxxxxxxxxxxx
RETURN
END

Question 7

Scalar Function General Format

Answer 7

CREATE FUNCTION Function_Name (@Parameter DATA_TYPE)
RETURNS DATA_TYPE
AS
BEGIN

DECLARE @Variable_Name DATA_TYPE
RETURN @Variable_Name

END

Question 8

Create Database

Answer 8

CREATE DATABASE database_name;

Question 9

Create Table

Answer 9

CREATE TABLE table_name (
ID INT IDENTITY PRIMARY KEY,
column_name_1 DATA_TYPE NOT NULL,
column_name_2 DATA_TYPE NULL,
);

Question 10

Bulk Insert from CSV file

Answer 10

BULK INSERT Employees
FROM ‘file_address.csv’
WITH (
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRSTROW = 2
);