sql_quickref

SQL Quick Reference

SQL Statement	Syntax
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND|OR condition
ALTER TABLE	ALTER TABLE table_name  ADD column_name datatype or ALTER TABLE table_name  DROP COLUMN column_name
AS (alias)	SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name  AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... )
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE	DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name  (Note: Deletes the entire table!!) DELETE * FROM table_name  (Note: Deletes the entire table!!)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL)
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
HAVING	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)
INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2  ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2  ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2  ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2  ON table_name1.column_name=table_name2.column_name
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO	SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP	SELECT TOP number|percent column_name(s) FROM table_name
TRUNCATE TABLE	TRUNCATE TABLE table_name
UNION	SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL	SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE	UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value

You can also this quick ref Query from this link..

http://www.w3schools.com/sql/default.asp

Sql Function

SQL has many built-in functions for performing calculations on data.

---

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

---

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

The AVG() Function

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

---

SQL COUNT(column_name) Syntax

The COUNT() function returns the number of rows that matches a specified criteria.

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

The FIRST() Function

The FIRST() function returns the first value of the selected column.

SQL FIRST() Syntax

SELECT FIRST(column_name) FROM table_name;

The LAST() Function

The LAST() function returns the last value of the selected column.

SQL LAST() Syntax

SELECT LAST(column_name) FROM table_name;

The MAX() Function

The MAX() function returns the largest value of the selected column.

SQL MAX() Syntax

SELECT MAX(column_name) FROM table_name;

The MIN() Function

The MIN() function returns the smallest value of the selected column.

SQL MIN() Syntax

SELECT MIN(column_name) FROM table_name;

The SUM() Function

The SUM() function returns the total sum of a numeric column.

SQL SUM() Syntax

SELECT SUM(column_name) FROM table_name;

The GROUP BY Statement

Aggregate functions often need an added GROUP BY statement.

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

The UCASE() Function

The UCASE() function converts the value of a field to uppercase.

SQL UCASE() Syntax

SELECT UCASE(column_name) FROM table_name;

Syntax for SQL Server

SELECT UPPER(column_name) FROM table_name;

The LCASE() Function

The LCASE() function converts the value of a field to lowercase.

SQL LCASE() Syntax

SELECT LCASE(column_name) FROM table_name;

Syntax for SQL Server

SELECT LOWER(column_name) FROM table_name;

The MID() Function

The MID() function is used to extract characters from a text field.

SQL MID() Syntax

SELECT MID(column_name,start[,length]) AS some_name FROM table_name;

The LEN() Function

The LEN() function returns the length of the value in a text field.

SQL LEN() Syntax

SELECT LEN(column_name) FROM table_name;

The ROUND() Function

The ROUND() function is used to round a numeric field to the number of decimals specified.

SQL ROUND() Syntax

SELECT ROUND(column_name,decimals) FROM table_name;

The NOW() Function

The NOW() function returns the current system date and time.

SQL NOW() Syntax

SELECT NOW() FROM table_name;

The FORMAT() Function

The FORMAT() function is used to format how a field is to be displayed.

SQL FORMAT() Syntax

SELECT FORMAT(column_name,format) FROM table_name;

Difference

1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
TRUNCATE 
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.
TRUNCATE 
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.
Deletes the data as well as structure.
The difference b/w DROP and DELETE table is that, after executing DELETE statement the contents of table are removed but the structure remains same, but in case of DROP statement both the contents and structure are removed.
How ASP.NET different from ASP?

Difference b/w Delete, Truncate, Drop Click here :-

DELETE 

Drop

·                     Asp.Net forms have a code behind file which contains all event handling code But in Asp it doesn't have such facility to separate programming logic form design.

·                     Asp.Net inherits the class written in code behind But in Asp does not have concept of inheritance

·                     Asp.Net use full fledged programming language But Asp it use scripting language.

·                     Asp.Net web application are configuration(web.config) But in Asp there are not.  

Difference B/w Primary key and Unique key.

·                     Primary key is only one in a table but unique key can be more then one.

·                     primary key does not have null value but unique can have one null value.

·                     primary key is by default clustered index but unique is by default non-clustered index.

·                     primary key has reference key in another table and unique key also has reference key in another table.

Clustered index and non clustered index

clustered index is index in with row set are stored in particular format but in non clustered index then is not format.  

Difference B/w Primary key and Foreign key.

·                     Primary key is a unique in table this is only one in a table but primary can be define more then one.

·                     primary key does not have null value but Foreign key have null value.

·                     primary key is by default clustered index. 

Difference B/w ADO and ADO.NET

§     In ADO we have record set and in ADO.NET we have Dataset.

§     In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset.

§     Dataset can have multiple tables.

§     All data persist in XML as compared to classic ADO where data persisted in Binary format also.

§     In ADO we have record set and in ADO.NET we have Dataset.

§     In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset.

§     Dataset can have multiple tables.

§     All data persist in XML as compared to classic ADO where data persisted in Binary format also.

The difference b/w Data Set  and Data Reader

§     Data Reader provides forward only and read only access to data, while the Data set objects can hold more than one table from the same data source as well as the relationships between them.

§     Dataset is a disconnected architecture while data reader is connected architecture.

§     Dataset can persist contents while data reader cannot persist contents they are forward only.

Difference B/w Data Set & Record set

There are two main differences are :

§     Dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset.

§     Recordset this is not possible

§     All representation of Dataset is using XML while recordset uses COM.

§     Recordset can not be transmitted on HTTP while Dataset can be.

Difference B/w DataAdepter and Datareader.

SqlDataReader:

·      Holds the connection open until you are finished (don't forget to close it!).

·      Can typically only be iterated over once

·      Is not as useful for updating back to the database

On the other hand, it:

·      Only has one record in memory at a time rather than an entire result set (this can be huge)

·      Is about as fast as it you can get for that one iteration

·      Allows you start processing results sooner

SqlDataAdapter/DataSet

·      Lets you close the connection as soon it's done loading data, and may even close it for you automatically

·      All of the results are available in memory

·      You can iterate over it as many times as you need, or even look up a specific record by index

·      Has some built-in faculties for updating back to the database

At the cost of:

·      Much higher memory use

·      You wait until all the data is loaded before using any of it

Abstract Class V/s Interface 

·                     Abstract class can have implementation for some of its method. But the interface can't have implementation for any of its method.

·                      Abstract class can have field but interface does not have fields.

·                     An Interface can inherit from another interface only can't inherit from another abstract class but an abstract class can inherit from another abstract class and another interface.

·                     Abstract class members can have access modifiers where interface member doesn't have access modifiers.

Structure V/s Class

·                     Class is a reference type so it is stored in Heap. Structure is value type so it is stored in stack.

·                     class has inheritance but structure does not has inheritance.

·                     In class there is constructor But in structure there may be or may not be constructor.

·                     By default class has private access modifier and structure has public modifier.