Area Verification Report(AVR)

Description The SQL Server (Transact-SQL) ALTER TABLE statement is used to add, modify, or drop columns in a table. Add column in table You can use the ALTER TABLE statement in SQL Server to add a column to a table. Syntax The syntax to add a column in a table in SQL Server (Transact-SQL) is: ALTER TABLE table_name ADD column_name column_definition; Example Let's look at an example that shows how to add a column in an SQL Server table using the ALTER TABLE statement. For example: ALTER TABLE employees ADD last_name VARCHAR(50); This SQL Server ALTER TABLE example will add a column to the  employees  table called  last_name . Add multiple columns in table You can use the ALTER TABLE statement in SQL Server to add multiple columns to a table. Syntax The syntax to add multiple columns to an existing table in SQL Server (Transact-SQL) is: ALTER TABLE table_name ADD column_1 column_definition, column_2 column_definition, ...

CREATE TABLE Description The SQL Server (Transact-SQL) CREATE TABLE statement allows you to create and define a table. Syntax The syntax for the CREATE TABLE statement in SQL Server (Transact-SQL) is: CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], ... ); Parameters or Arguments table_name The name of the table that you wish to create. column1, column2 The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default. Example Let's look at an example of how to use the CREATE TABLE statement in SQL Server (Transact-SQL). For example: CREATE TABLE employees ( employee_id INT NOT NULL, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50), salary MONEY ); This SQL Server CREATE TABLE example creates a table called  employees  which h...

Count function Description In SQL Server (Transact-SQL), the COUNT function returns the count of an expression. Syntax The syntax for the COUNT function in SQL Server (Transact-SQL) is: SELECT COUNT(aggregate_expression) FROM tables [WHERE conditions]; OR the syntax for the COUNT function when grouping the results by one or more columns is: SELECT expression1, expression2, ... expression_n, COUNT(aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n; Parameters or Arguments expression1, expression2, ... expression_n Expressions that are not encapsulated within the COUNT function and must be included in the GROUP BY clause at the end of the SQL statement. aggregate_expression This is the column or expression whose non-null values will be counted. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are ...

Description The SQL Server (Transact-SQL) INTERSECT operator is used to return the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. It is the intersection of the two SELECT statements. Intersect Query Explanation:  The INTERSECT query will return the records in the blue shaded area. These are the records that exist in both Dataset1 and Dataset2. Each SELECT statement within the SQL Server INTERSECT must have the same number of columns in the result sets with similar data types. Syntax The syntax for the NTERSECT operator in SQL Server (Transact-SQL) is: SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] INTERSECT SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; Parameters or Arguments expressions The columns or calculations that you wish to compare between the two SELECT ...

Description The SQL Server UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements. Each SELECT statement within the SQL Server UNION ALL operator must have the same number of fields in the result sets with similar data types. Syntax The syntax for the UNION ALL operator in SQL Server (Transact-SQL) is: SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION ALL SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; Parameters or Arguments expression1, expression2, ... expression_n The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the records to be selected. Note There must be...

Description The SQL Server UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of columns in the result sets with similar data types. Syntax The syntax for the UNION operator in SQL Server (Transact-SQL) is: SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]; Parameters or Arguments expression1, expression2, ... expression_n The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the records to be selected. Note There must be same number of expressions in both SELECT statements. The corresponding...