sqlserver interview questions part 8

ON Person (LastName DESC) If you want to index more than one column you can list the column names within the parentheses, separated by commas:CREATE INDEX PersonIndex

ON Person (LastName, FirstName)

SQL Drop Index, Table and Database

Drop Index

You can delete an existing index in a table with the DROP statement.DROP INDEX table_name.index_name

 Delete a Table or Database

 To delete a table (the table structure, attributes, and indexes will also be deleted):DROP TABLE table_name To delete a database:DROP DATABASE database_name

 Truncate a Table

 What if we only want to get rid of the data inside a table, and not the table itself? Use the TRUNCATE TABLE command (deletes only the data inside the table):TRUNCATE TABLE table_name

 SQL ALTER TABLE

ALTER TABLE

 The ALTER TABLE statement is used to add or drop columns in an existing table.ALTER TABLE table_name

 ADD column_name datatypeALTER TABLE table_name

 DROP COLUMN column_name Note: Some database systems don't allow the dropping of a column in a database table (DROP COLUMN column_name).

 Person:LastName FirstName Address

 pettersen Kari Storgt 20

 Example

 To add a column named "City" in the "Person" table:ALTER TABLE Person ADD City varchar(30) Result:LastName FirstName Address City

 Pettersen Kari Storgt 20 Example

 To drop the "Address" column in the "Person" table:ALTER TABLE Person DROP COLUMN Address Result:LastName FirstName City

 Pettersen Kari

 SQL Functions

 SQL has a lot of built-in functions for counting and calculations.

 Function Syntax

 The syntax for built-in SQL functions is:SELECT function(column) FROM table

 Types of Functions

 There are several basic types and categories of functions in SQL. The basic types of functions are: Aggregate Functions

 Scalar functions Aggregate functions

 Aggregate functions operate against a collection of values, but return a single value.Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!"Persons" table (used in most examples)

 Name Age

 Hansen, Ola 34

 Svendson, Tove 45

 Pettersen, Kari 19 Aggregate functions in MS Access

 Function Description

 AVG(column) Returns the average value of a column

 COUNT(column) Returns the number of rows (without a NULL value) of a column

 COUNT(*) Returns the number of selected rows

 FIRST(column) Returns the value of the first record in the specified field

 LAST(column) Returns the value of the last record in the specified field

 MAX(column) Returns the highest value of a column

 MIN(column) Returns the lowest value of a column

 STDEV(column)

 STDEVP(column)

 SUM(column) Returns the total sum of a column

 VAR(column)

 VARP(column) Aggregate functions in SQL Server

 Function Description

 AVG(column) Returns the average value of a column

 BINARY_CHECKSUM

 CHECKSUM

 CHECKSUM_AGG

 COUNT(column) Returns the number of rows (without a NULL value) of a column

 COUNT(*) Returns the number of selected rows

 COUNT(DISTINCT column) Returns the number of distinct results

 FIRST(column) Returns the value of the first record in the specified field

 LAST(column) Returns the value of the last record in the specified field

 MAX(column) Returns the highest value of a column

 MIN(column) Returns the lowest value of a column

 STDEV(column)

 STDEVP(column)

 SUM(column) Returns the total sum of a column

 VAR(column)