SQL Server Accessing data

SQL Server data integrity 

Explain the full syntax of Select Statement with examples.

Answer
SELECT is used to select a specific or ALL columns / rows from a table.

Syntax:
SELECT list_columns FROM table_name

List_columns are the columns to be selected to retrieve the rows. While table_name is the table from which these columns needs to be selected. Many clauses can be added to this SELECT statement.

Example 1: Displays rows with customers balance between 10 and 10000
SELECT first_name FROM Customer WHERE cust_bal BETEEN 10 and 10000;

Example 2: Sort the names in an ascending order
SELECT first_name FROM Customer ORDER BY first_name;

Explain some of the keywords of Select Statement like Distinct Keyword, Top n Keyword with an example for each of them.

Answer
DISTINCT: Distinct clause helps to remove duplicates and returns a result set.

Syntax: DISTINCT (set_expression)

Example:

Select DISTINCT company_name FROM Company;

TOP N: returns the top “n” records from a table.

Syntax : TOP N

Example:
Select TOP 10 Cust_name from Customer;

Describe the use of Into and From clause with examples.

Answer
INTO: The INTO keyword inserts data from one table into another. It is commonly used in SELECT statement. It is useful in creating back ups.

SYNTAX:
SELECT * INTO new_table FROM old_table

Example: Copy all columns from table customer into customer_backup
SELECT * INTO Customer_backup FROM customer

FROM: the FROM clause selects rows from the table specified.

Syntax:
SELECT * FROM table_name

Example: Select all columns from a table customer

SELECT * FROM Customer

Describe where, group by and having clause with examples for each of them.

Answer
WHERE: Where clause in a SELECT query is used to retrieve specific records that meet the specified criteria.

SYNTAX:
SELECT column_name FROM Table_name WHERE predicate

Example: to display customers with first name is john
Select first_name From customer Where first_name =’john’;

GROUP BY: Group By clause in select statement is used to group or collect data of multiple records of one ore more columns.

SYNTAX: SELECT column_name FROM table_name WHERE predicate GROUP BY column1, column2..;

Example: To Group records by city SELECT city_name FROM Customer GROUP BY city;

HAVING: The HAVING clause is used to filter records that have been grouped using GROUP BY clause. Hence HAVING clause is used with GROUP BY.

Syntax: SELECT column_name FROM table_name WHERE predicates GROUP BY column1,.. HAVING condition1…

Example: To Group records by employee with salary > 10000 SELECT emp_name FROM employee GROUP BY emp_name HAVING salary > 1000;