Silverlight

http://silverlight.net/learn/tutorials/controls-vb/

http://www.scriptol.com/silverlight/web-program.php

http://weblogs.asp.net/scottgu/pages/silverlight-tutorial-part-5-using-the-listbox-and-databinding-to-display-list-data.aspx

ASP.NET MVC 1.0

http://aspnetmvcbook.s3.amazonaws.com/aspnetmvc-nerdinner_v1.pdf

Application Domains

Process-->Application Domain(s)--->threads

http://aspalliance.com/951_Application_Domains_in_NET.all

Garbage Collection:Finalize vs Dispose

Finalize

The following rules outline the usage guidelines for the Finalize method:

• Only implement Finalize on objects that require finalization. There are performance costs associated with Finalize methods.
• If you require a Finalize method, you should consider implementing IDisposable to allow users of your class to avoid the cost of invoking the Finalize method.
• Do not make the Finalize method more visible. It should be protected, not public.
• An object's Finalize method should free any external resources that the object owns. Moreover, a Finalize method should release only resources that are held onto by the object. The Finalize method should not reference any other objects.
• Do not directly call a Finalize method on an object other than the object's base class. This is not a valid operation in the C# programming language.
• Call the base.Finalize method from an object's Finalize method.

  Note The base class's Finalize method is called automatically with the C# and the Managed Extensions for C++ destructor syntax.

Dispose

The following rules outline the usage guidelines for the Dispose method:

• Implement the dispose design pattern on a type that encapsulates resources that explicitly need to be freed. Users can free external resources by calling the public Disposemethod.
• Implement the dispose design pattern on a base type that commonly has derived types that hold on to resources, even if the base type does not. If the base type has a close method, often this indicates the need to implement Dispose. In such cases, do not implement a Finalize method on the base type. Finalize should be implemented in any derived types that introduce resources that require cleanup.
• Free any disposable resources a type owns in its Dispose method.
• After Dispose has been called on an instance, prevent the Finalize method from running by calling the GC.SuppressFinalize Method. The exception to this rule is the rare situation in which work must be done in Finalize that is not covered by Dispose.
• Call the base class's Dispose method if it implements IDisposable.
• Do not assume that Dispose will be called. Unmanaged resources owned by a type should also be released in a Finalize method in the event that Dispose is not called.
• Throw an ObjectDisposedException from instance methods on this type (other than Dispose) when resources are already disposed. This rule does not apply to theDispose method because it should be callable multiple times without throwing an exception.
• Propagate the calls to Dispose through the hierarchy of base types. The Dispose method should free all resources held by this object and any object owned by this object. For example, you can create an object like a TextReader that holds onto a Stream and an Encoding, both of which are created by the TextReader without the user's knowledge. Furthermore, both the Stream and the Encoding can acquire external resources. When you call the Dispose method on the TextReader, it should in turn callDispose on the Stream and the Encoding, causing them to release their external resources.
• You should consider not allowing an object to be usable after its Dispose method has been called. Recreating an object that has already been disposed is a difficult pattern to implement.
• Allow a Dispose method to be called more than once without throwing an exception. The method should do nothing after the first call.

http://msdn.microsoft.com/en-us/library/b1yfkh5e(VS.71).aspx

User Defined Functions

http://www.sqlteam.com/article/user-defined-functions

Sql Joins

Left

All rows from left with matching rows from right and if not matching then null on the left side

Right

All rows from right with matching rows from left and if not matching then null on the left side

http://www.sql-tutorial.net/SQL-JOIN.asp

Introduction to delegates

A delegate is a type-safe object that can point to another method (or possibly multiple methods) in the application, which can be invoked at later time.

Delegates also can invoke methods Asynchronously.

A delegate type maintains three important pices of information :

1. The name of the method on which it make calls.
   
2. Any argument (if any) of this method.
   
3. The return value (if any) of this method.

Defining a Delegate in C#

when you want to create a delegate in C# you make use of delegate keyword.

The name of your delegate can be whatever you desire. However, you must define the delegate to match the signature of the method it will point to. fo example the following delegate can point to any method taking two integers and returning an integer.

public delegate int DelegateName(int x, int y);

http://www.c-

sharpcorner.com/UploadFile/Ashush/Delegates02152008155757PM/Delegates.aspx

DataSet vs DataReader

DataSet vs DataReader

Points to be consider while choosing between the DataSet and DataReader objects.

1) DataSet Object has Read/Write access, While DataReader Object has Read-only access

2) DataSet Object Supports multiple tables from different databases, While DataReader Object Supports a single table based on a single SQL query of one database

3) DataSet Object works under Disconnected mode, While DataReader Object has Connected mode

4) DataSet Object is Bind to multiple controls, While DataReader Object is Bind to a single control

5) DataSet Object supports Forward and backward scanning of data, While DataReader Object supports Forward-only scanning of data

6) DataSet Object has Slower access to data, While DataReader Object has Faster access to data

7) DataSet Object has Greater overhead to enable additional features, While DataReader Object being Lightweight object with very little overhead

8) DataSet Object is Supported by Visual Studio .NET tools, While DataReader Object Must be manually coded

http://articles.sitepoint.com/article/dataset-datareader

Int32.Parse(), Convert.ToInt32(), and Int32.TryParse()

Int32.parse(string)
-------------------------
Int32.Parse (string s) method converts the string representation of a number to its 32-bit signed integer equivalent.
When s is null reference, it will throw ArgumentNullException.
If s is other than integer value, it will throw FormatException.
When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException.

Example:
------------------
string s1 = "1234";
string s2 = "1234.65";
string s3 = null;
string s4 = "123456789123456789123456789123456789123456789";

int result;
bool success;

result = Int32.Parse(s1); //-- 1234
result = Int32.Parse(s2); //-- FormatException
result = Int32.Parse(s3); //-- ArgumentNullException
result = Int32.Parse(s4); //-- OverflowException


Convert.ToInt32(string)
----------------------------------
Convert.ToInt32(string s) method converts the specified the string representation of 32-bit signed integer equivalent. This calls in turn Int32.Parse () method.
When s is null reference, it will return 0 rather than throw ArgumentNullException
If s is other than integer value, it will throw FormatException.
When s represents a number less than MinValue or greater than MaxValue, it will throw OverflowException

Example:
result = Convert.ToInt32(s1); //-- 1234
result = Convert.ToInt32(s2); //-- FormatException
result = Convert.ToInt32(s3); //-- 0
result = Convert.ToInt32(s4); //-- OverflowException


Int32.TryParse(string, out int)
---------------------------------------------
Int32.Parse(string, out int) method converts the specified the string representation of 32-bit signed integer equivalent to out variable, and returns true if it parsed successfully, false otherwise. This method is available in C# 2.0
When s is null reference, it will return 0 rather than throw ArgumentNullException.
If s is other than integer value, the out variable will have 0 rather than FormatException.
When s represents a number less than MinValue or greater than MaxValue, the out variable will have 0 rather than OverflowException.

Example:-
-------------
success = Int32.TryParse(s1, out result); //-- success => true; result => 1234
success = Int32.TryParse(s2, out result); //-- success => false; result => 0
success = Int32.TryParse(s3, out result); //-- success => false; result => 0
success = Int32.TryParse(s4, out result); //-- success => false; result => 0

Convert.ToInt32 is better than Int32.Parse, since it return 0 rather than exception. But, again according to the requirement this can be used. TryParse will be best since it handles exception itself always.

Basic Questions

Array vs ArrayList

The Array is static one we cannot alter the size after the declaration.
The ArrayList is the dynamic one, it can modify its size based on the elements.
The ArrayList Size are 0,16,32,64.... For example initially its have 0 size if u add one element in the ArrayList it will increase the ArrayList size to 16. when the total elements increase to 17 the size of the ArrayList will be increased to 32.like that its modify its size dynamically depend on the element size.

Immutable String

An object qualifies as being called immutable if its value cannot be modified once it has been created. For example, methods that appear to modify a String actually return a new String containing the modification. Developers are modifying strings all the time in their code. This may appear to the developer as mutable - but it is not. What actually happens is your string variable/object has been changed to reference a new string value containing the results of your new string value. For this very reason .NET has the System.Text.StringBuilder class. If you find it necessary to modify the actual contents of a string-like object heavily, such as in a for orforeach loop, use the System.Text.StringBuilder class.

DataSet vs DataReader

Points to be consider while choosing between the DataSet and DataReader objects.

1) DataSet Object has Read/Write access, While DataReader Object has Read-only access

2) DataSet Object Supports multiple tables from different databases, While DataReader Object Supports a single table based on a single SQL query of one database

3) DataSet Object works under Disconnected mode, While DataReader Object has Connected mode

4) DataSet Object is Bind to multiple controls, While DataReader Object is Bind to a single control

5) DataSet Object supports Forward and backward scanning of data, While DataReader Object supports Forward-only scanning of data

6) DataSet Object has Slower access to data, While DataReader Object has Faster access to data

7) DataSet Object has Greater overhead to enable additional features, While DataReader Object being Lightweight object with very little overhead

8) DataSet Object is Supported by Visual Studio .NET tools, While DataReader Object Must be manually coded

Linq

Linq

Well standard LINQ is a new addition to .NET (it adds more dlls basically) that allows the programmer to query inline data as they probably would be used to doing with standard SQL-type syntax.

So where they may have had a query in a database or a SQL query string something like:

Collapse

SELECT * from Books WHERE QuatityInStock > 50 AND Price > 50.00

we would now write the following into as a valid LINQ query (assuming we have the relevant in memory data structure to support the query)

Collapse

var result =   from b Books   where b.QuatityInStock > 50 AND Price > 50.00   select b;

public static void LinqDemo()

{

Item it = new Item();

List items = new List();

it.Id = 1;

it.Price = 111;

items.Add(it);

it = new Item();

it.Id = 2;

it.Price = 222;

items.Add(it);

var iList = from i1 in items where i1.Price > 120 select i1;

foreach (var item in iList)

{

Console.WriteLine(item.Price);

}

}

-----------------------

Imports System.Xml

Module Module1

Sub Main()

'Const path As New String=""

Dim s As New Stopwatch

s.Start()

Dim doc = XDocument.Load("C:\Documents and Settings\aakash\Desktop\Files\Customers.xml")

'LinqDisplayChildElements(doc)

LinqDistintOperator(doc)

'Dim xdoc As New XmlDocument()

'xdoc.Load("C:\Documents and Settings\aakash\Desktop\Files\Customers.xml")

'Dim nlist As XmlNodeList = xdoc.SelectNodes("Root/Customers/CompanyName")

'For Each Item As XmlNode In nlist

' Console.WriteLine(Item.InnerText)

'Next

s.Stop()

Console.WriteLine(s.ElapsedMilliseconds().ToString())

Console.ReadLine()

End Sub

Private Sub LinqDistintOperator(ByVal doc As XDocument)

Dim countries = From c In doc... Select name = c.Value Order By name Distinct

For Each c In countries

Console.WriteLine(c)

Next

'Dim faxes = From c In doc... Select fax = c.Value Order By fax

'For Each c In faxes

' Console.WriteLine(c)

'Next

End Sub

Private Sub LinqDisplayChildElements(ByVal doc As XDocument)

For Each Item As String In doc...

Console.WriteLine(Item.ToString())

Next

End Sub

End Module

http://msdn.microsoft.com/en-us/vbasic/bb688087.aspx

http://www.codeproject.com/KB/vista/LINQ_1.aspx

http://www.codeproject.com/KB/linq/LINQtoObjects_CS.aspx

http://msdn.microsoft.com/en-us/vbasic/bb738050.aspx#filtwhere -- this one is superb

== vs .Equals

http://blogs.msdn.com/csharpfaq/archive/2004/03/29/102224.aspx

DELETE vs TRUNCATE

TRUNCATE TABLE [TableName]
This method is fast, but should not be used on any table that is used
for replication, or if there are ON DELETE triggers present on the table.
This method will simply remove all the rows in the table in a single operation.

DELETE FROM [TableName] WHERE xxxxxx
This method works row by row, and fires triggers, and updates the transaction
logs. This method will be slower on large tables than TRUNCATE but should
be the only method you use if you have ON DELETE triggers, or the table is
included in replication.

http://www.dotnetspider.com/resources/2440-Delete-VS-Truncate.aspx

http://www.softstuff-consulting.com/kbase/showkb.asp?id=15

Sql Basics

Introduction to SQL

What is SQL?

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

RDBMS

RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables.

A table is a collections of related data entries and it consists of columns and rows.

SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables. The most important DDL statements in SQL are:

• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

SQL Select

The SELECT statement is probably the most commonly used in SQL. It simply retrieves data from the database.

Lets have a look at a simple SELECT statement:

SELECT * FROM Individual

This SQL SELECT statement is attempting to retrieve all columns from a table called individuals.

How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a quick way of selecting all columns - it's much easier than writing out the names of all columns (especially if there are a lot of columns).

Of course, this SQL SELECT statement assumes that there is a table called individuals. If there wasn't, an error would be generated.

Lets have a look at the table the statement is trying to select data from:

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Because our select statement asks to display all columns and all records, we would see the following:

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Select from Multiple Tables

You can select from more than one table at a time. To do this, simply separate each table with a comma. You should also qualify any references to columns by placing the table name in front, separated by a dot.

We have another table called "Occupation", which contains the individual's occupation.

OccupationId	IndividualId	JobTitle
1	1	Engineer
2	2	Accountant
3	3	Cleaner
4	4	Attorney
5	5	Sales Executive

SQL statement

We will select from both the "Individual" table and the "Occupation" table. We will qualify any column names by prefixing them with its table's name and a dot.

SELECT * FROM Individual, Occupation

WHERE Individual.FirstName = 'Homer'

Result

IndividualId	FirstName	LastName	UserName	OccupationId	IndividualId	JobTitle
1	Fred	Flinstone	freddo	1	1	Engineer
2	Homer	Simpson	homey	2	2	Accountant
3	Homer	Brown	notsofamous	3	3	Cleaner
4	Ozzy	Ozzbourne	sabbath	4	4	Attorney
5	Homer	Gain	noplacelike	5	5	Sales Executive

Displaying Less Columns

If you don't need every column to be displayed you can single out just the columns you're interested in. It's good programming practice to do this - the more columns your program has to return, the more it will impact its performance.

To only display those columns you're interested in, simply replace the asterisk (*) with a comma separated list of the column names.

SQL statement

SELECT IndividualId, LastName, UserName FROM Individual

WHERE FirstName = 'Homer'

Result

IndividualId	LastName	UserName
2	Simpson	homey
3	Brown	notsofamous
5	Gain	noplacelike

SQL Where

the previous lesson, we used a SQL SELECT statement to retrieve all records from a database table. This is fine if we want to see every record, but what if we were only interested in some records? For example, what if we were only interested in individuals whose first name is "Homer"?

We could use the WHERE clause.

Using the WHERE clause, you can filter out only those records that satisfy a given condition.

SQL WHERE Syntax

SELECT * FROM table_name

WHERE column_name = 'criteria'

Example

SQL WHERE Statement

SELECT * FROM Individual

WHERE FirstName = 'Homer'

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

Given there are 3 people with the first name of "Homer", the results will look like this:

IndividualId	FirstName	LastName	UserName
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
5	Homer	Gain	noplacelike

Multiple Conditions

You can filter records based on more than one condition using operators. Two common operators are the AND and OR operators.

AND Operator

The AND operator filters the query to only those records that satisfy both the first condition and the second condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'

AND LastName = 'Brown'

Result

IndividualId	FirstName	LastName	UserName
3	Homer	Brown	notsofamous

OR Operator

The OR operator filters the query to only those records that satisfy either one or the other condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'

OR LastName = 'Ozzbourne'

Result

IndividualId	FirstName	LastName	UserName
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
5	Homer	Gain	noplacelike
4	Ozzy	Ozzbourne	sabbath

SQL Order By

Using a SQL SELECT statement can retreive many hundreds or even thousands of records. In some cases you might find it useful to sort the records by a given column. For example, when selecting records from the Individual table, you might like to sort them by the LastName column.

SQL statement

SELECT * FROM Individual

ORDER BY LastName

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

IndividualId	FirstName	LastName	UserName
3	Homer	Brown	notsofamous
1	Fred	Flinstone	freddo
5	Homer	Gain	noplacelike
4	Ozzy	Ozzbourne	sabbath
2	Homer	Simpson	homey

Descending Order

By default, ORDER BY sorts the column in ascending order - that is, from lowest values to highest values. You could also explicitly state this using the ASC keyword, but it's not necessary.

If you want highest values to appear first, you can use the DESC keyword.

SQL statement

SELECT * FROM Individual

ORDER BY LastName DESC

Result

IndividualId	FirstName	LastName	UserName
2	Homer	Simpson	homey
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
1	Fred	Flinstone	freddo
3	Homer	Brown	notsofamous

Sorting By Multiple Columns

You can sort by multiple columns by stating each column in the ORDER BY clause, separating each column name with a comma. SQL will first order the results by the first column, then the second, and so on for as many columns that are included in the ORDER BY clause.

SQL statement

SELECT * FROM Individual

ORDER BY FirstName, LastName

Result

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
3	Homer	Brown	notsofamous
5	Homer	Gain	noplacelike
2	Homer	Simpson	homey
4	Ozzy	Ozzbourne	sabbath

SQL Top

n the preceeding lessons on the SELECT statement, the examples have returned all records that have matched our SELECT criteria. This is great if you want to look at every record, but, what if you only want to look at the first few records?

Sounds like you need the SQL TOP clause.

The TOP clause allows us to specify how many rows to return. This can be useful on very large tables when there are thousands of records. Returning thousands of records can impact on performance, and if you are working with a production database, this could have an adverse impact on the users.

Note: The SQL TOP clause is Transact-SQL, and not part of ANSI SQL. Therefore, depending on your database system, you may not be able to use this clause.

SQL statement

SELECT TOP 3 * FROM Individual

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous

Specifying a Percentage

You have the option of specifying a percentage of the result set instead of an absolute value. You do this with the PERCENT keyword.

SQL statement

SELECT TOP 40 PERCENT * FROM Individual

Result

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey

SQL TOP and the ORDER BY clause

If you are using the TOP clause along with the ORDER BY clause, the TOP clause is applied to the ordered result set.

Therefore, if we add an ORDER BY to the above query, we end up with something like this:

SQL statement

SELECT TOP 40 PERCENT * FROM Individual

ORDER BY LastName DESC

Result

IndividualId	FirstName	LastName	UserName
2	Homer	Simpson	homey
4	Ozzy	Ozzbourne	sabbath

SQL Distinct

Once a table starts getting a lot of data in it, some columns will contain duplicate values. For example, many Individuals share first names and surnames. Most of the time this isn't a problem. But sometimes you will want to find out how many unique values there are in a table. To do this you can use the DISTINCT keyword.

SQL statement

SELECT DISTINCT(FirstName) FROM Individual

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

Using the DISTINCT keyword, all customers with a name of "Homer" are counted as one.

FirstName

Fred

Homer

Ozzy

SQL In

The SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.

SQL Syntax

SELECT * FROM table_name

WHERE column_name IN (value1,value2,...)

Example

SQL statement

SELECT * FROM Individual

WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')

Source Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
4	Ozzy	Ozzbourne	sabbath

You might have noticed that this returns the same result as the following SQL statement:

Yes, we could just have easily used that statement but the SQL IN statement is more concise.

SELECT * FROM Individual

WHERE LastName = 'Simpson'

OR LastName = 'Ozzbourne'

OR LastName = 'Flinstone'

SQL IN and Subqueries

Now, where the SQL IN operator becomes really useful is when you need to compare a value against the result of another query.

For example, lets say we have another table called "Publisher". This table contains users who are allowed to contribute to the website via an administration console. All users in the Publisher table are also in the Individual table, but not all users in the Individual table are in the Publisher table.

Source Tables

Individual Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Publisher Table

IndividualId	AccessLevel
1	Administrator
2	Contributor
3	Contributor
4	Contributor

Our task is to return a list of usernames from all publishers who have an access level of "Contributor".

You may notice that the usernames are in the Individual table but the access level is stored in the Publisher table. Also, there could potentially be many contributors. This is a good candidate for the SQL IN operator!

SQL statement

SELECT UserName FROM Individual

WHERE IndividualId IN

(SELECT IndividualId

FROM Publisher

WHERE AccessLevel = 'Contributor')

Result

UserName

homey

notsofamous

sabbath

In this example there aren't many records in the Publisher table, but imagine if there were thousands - the IN statement is great for this sort of thing.

The LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

SQL LIKE Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern

---

LIKE Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons living in a city that starts with "s" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 's%'

The "%" sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%s'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "tav" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%tav%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
3	Pettersen	Kari	Storgt 20	Stavanger

It is also possible to select the persons living in a city that NOT contains the pattern "tav" from the "Persons" table, by using the NOT keyword.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City NOT LIKE '%tav%

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

---

SQL Wildcard Examples

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

---

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

---

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

---

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

The BETWEEN Operator

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

SQL BETWEEN Syntax

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

---

BETWEEN Operator Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to select the persons with a last name alphabetically between "Hansen" and "Pettersen" from the table above.

We use the following SELECT statement:

SELECT * FROM Persons

WHERE LastName

BETWEEN 'Hansen' AND 'Pettersen'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Note: The BETWEEN operator is treated differently in different databases.

In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, because the BETWEEN operator only selects fields that are between and excluding the test values).

In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, because the BETWEEN operator selects fields that are between and including the test values).

And in other databases, persons with the LastName of "Hansen" will be listed, but "Pettersen" will not be listed (like the example above), because the BETWEEN operator selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator.

---

Example 2

To display the persons outside the range in the previous example, use NOT BETWEEN:

SELECT * FROM Persons

WHERE LastName

NOT BETWEEN 'Hansen' AND 'Pettersen'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

SQL Alias

An alias is a name that you give a table. This can make it easier to work with table names - especially when they are long. You could name the alias anything, but usually you'd make it short.

You may be thinking "a table already has a name, why give it another one?". Well, there are some good reasons for creating an alias. The main reasons are:

• Queries can sometimes get very long. Aliases can make your query easier to read.
• You may find yourself referencing the same table name over and over again - this will occur if you're working with multiple tables and you need to refer to columns from those tables. It can be annoying to have to write the whole name all the time - especially if it's a long one.
• You may need to work with multiple instances of the same table, for example, a self join. If you're not familiar with joins, they are covered later in this tutorial.

As mentioned, an alias could be anything. For example, if you have a table called Individual you could give it an alias of i. Another table called IndividualProductPurchase could have an alias of, say, ipp

Alias Syntax

SELECT * FROM table_name AS alias_name

Example SQL Statement

SELECT o.JobTitle FROM Individual AS i, Occupation AS o

WHERE i.FirstName = 'Homer'

ORDER BY o.JobTitle

SQL Inner Join

As discussed in the previous lesson, you should use the SQL INNER JOIN when you only want to return records where there is at least one row in both tables that match the join condition.

Example SQL statement

SELECT * FROM Individual

INNER JOIN Publisher

ON Individual.IndividualId = Publisher.IndividualId

WHERE Individual.IndividualId = '2'

Source Tables

Left Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Right Table

IndividualId	AccessLevel
1	Administrator
2	Contributor
3	Contributor
4	Contributor
10	Administrator

Result

IndividualId	FirstName	LastName	UserName	IndividualId	AccessLevel
2	Homer	Simpson	homey	2	Contributor

SQL Outer Join

This lesson covers both the left outer join, the right outer join, and the full outer join, and explains the differences between them. There are some occasions where you would need to use a left outer join or a right outer join, and others where you would need a full outer join. The join type you use will depend on the situation and what data you need to return.

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

LEFT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Right Table

IndividualId	AccessLevel
1	Administrator
2	Contributor
3	Contributor
4	Contributor
10	Administrator

Result

IndividualId	FirstName	LastName	UserName	IndividualId	AccessLevel
1	Fred	Flinstone	freddo	1	Administrator
2	Homer	Simpson	homey	2	Contributor
3	Homer	Brown	notsofamous	3	Contributor
4	Ozzy	Osbourne	sabbath	4	Contributor
5	Homer	Gain	noplacelike	NULL	NULL

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS Ind

RIGHT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Right Table

IndividualId	AccessLevel
1	Administrator
2	Contributor
3	Contributor
4	Contributor
10	Administrator

Result

IndividualId	FirstName	LastName	UserName	IndividualId	AccessLevel
1	Fred	Flinstone	freddo	1	Administrator
2	Homer	Simpson	homey	2	Contributor
3	Homer	Brown	notsofamous	3	Contributor
4	Ozzy	Osbourne	sabbath	4	Contributor
NULL	NULL	NULL	NULL	10	Administrator

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

FULL JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Right Table

IndividualId	AccessLevel
1	Administrator
2	Contributor
3	Contributor
4	Contributor
10	Administrator

Result

IndividualId	FirstName	LastName	UserName	IndividualId	AccessLevel
1	Fred	Flinstone	freddo	1	Administrator
2	Homer	Simpson	homey	2	Contributor
3	Homer	Brown	notsofamous	3	Contributor
4	Ozzy	Osbourne	sabbath	4	Contributor
5	Homer	Gain	noplacelike	NULL	NULL
NULL	NULL	NULL	NULL	10	Administrator

SQL Insert

Up until now, this tutorial has covered the SELECT statement and variations on it. We are now about to learn a new statement - the INSERT statement.

The SQL INSERT command allows you to insert a record into a table in your database. As with the SELECT syntax, the INSERT syntax is quite straight forward.

SQL statement

INSERT INTO Individual

VALUES ( '6', 'Benny', 'Hill', 'hillbenny' )

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

Result

Now if we do a SELECT on the Individual table, we can see the new record added to the bottom of the result set.

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Benny	Hill	hillbenny

See - nothing to it!

Now, it's important to note that the INSERT statement is used only when you want to add a new record to the table. If you want to update an existing record, use an UPDATE statement. The UPDATE command is described in the next lesson.

SQL Update

The SQL UPDATE statement allows you to update an existing record in the database.

The UPDATE command uses a WHERE clause. If you don't use a WHERE clause, all rows will be updated. In fact, the syntax for a basic UPDATE statement is very similar to a SELECT statement.

SQL statement

UPDATE Individual

SET UserName = 'funnyman'

WHERE IndividualId = '6'

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Benny	Hill	funnyman

Result

Now if we select this record, we can see the updated value.

IndividualId	FirstName	LastName	UserName
6	Benny	Hill	funnyman

Updating Multiple Fields

To update multiple fields, separate each field assignment with a comma.

SQL statement

UPDATE Individual

SET UserName = 'getserious', FirstName = 'Onetree'

WHERE IndividualId = '6'

Result

IndividualId	FirstName	LastName	UserName
6	Onetree	Hill	getserious

Next lesson covers the DELETE statement.

The SQL DELETE statement allows you to delete a record from the database.

The DELETE command uses a WHERE clause. If you don't use a WHERE clause, all rows in the table will be deleted. Again, as with the UPDATE statement, the syntax for a basic DELETE statement is similar to a SELECT statement.

SQL statement

DELETE

FROM Individual

WHERE IndividualId = '6'

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Benny	Hill	funnyman

Result

Now if we select all records from the table, we see that record 6 has been deleted.

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike

SQL Count

A commonly used aggregate function in SQL is COUNT. COUNT returns the number of rows that match the given criteria.

COUNT(*)

If we only want to see how many records are in a table (but not actually view those records), we could use COUNT(*). COUNT(*) returns everything - including null values and duplicates.

SQL statement

SELECT COUNT(*) FROM Individual

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Bono		u2

Result

6

COUNT(column name)

If we want to see how many non-null values are in a given column, we use COUNT(column name) where column name is the name of the column we want to test.

SQL statement

SELECT COUNT(LastName) FROM Individual

Source Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Bono		u2

Result

5

Combining COUNT & DISTINCT

If we only want to see how many unique names are in the table, we could nest the DISTINCT inside a COUNT function.

SQL statement

SELECT COUNT(DISTINCT(FirstName)) FROM Individual

Result

4

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