Students Mela: July 2010

Saturday, July 31, 2010

TCP Clients and Servers

TCP Clients and Servers

The general order of library calls for a TCP client is as follows:
socket()
connect()
send() and/or recv()
close()
The general order of library calls for a TCP server is as follows:
socket()
bind()
listen()
accept()
send() and/or recv()
close()

Friday, July 30, 2010

Unix Basic Interview questions and commands

Frequently used UNIX commands

Command	Result
ls	lists files in current working directory
ls *.out	lists all files in current directory that end in .out
ls -l	lists files giving details including file size
pwd	displays full-path name of your current working directory on screen (stands for “present working directory”)
cd dirname	changes directory to dirname
cd ..	changes directory to one above the current directory
cd	with no argument, takes you to your home directory
mkdir dirname	creates new directory dirname
rmdir dirname	removes empty directory dirname
cp filename newname	makes a copy of filename with the name newname
cp ../filename .	copies filename in directory one tier above to current directory giving it the same name
mv filename newname	renames filename to newname (mv stands for “move”)
cat filename	displays contents of filename on screen
more filename	displays filename contents one screen at a time (Enter key scrolls through file by line; space bar scrolls through by screen)
head filename	displays first 10 lines of filename on screen
tail filename	displays last 10 lines of filename on screen
rm filename	deletes filename without double check (rm stands for “remove”)
grep string filename	displays lines from filename containing string on screen
./ filename &	runs job filename in the background
qsub filename	sends job filename to queue (where qsub is a submission script file in your bin directory)
qstat	lists jobs running on queue
qstat -a	lists only your jobs
qdel job#	deletes job with number job# from queue
ps	lists processes you have running
ps -ef	lists all processes
kill pid#	kills process with ID number pid#
kill -9 pid#	kills (with the “sure kill” signal) process with ID number pid#
kill -kill 0	kills all processes you have running and logs you off
man commandname	displays manual page for command commandname
man -k keyword	lists manual pages for commands related to keyword
lp filename	prints filename on printer in room 4241
chmod ### filename	changes “read, write, execute” mode of filename Example: to set the privileges for filename so that the owner has the ability to read, overwrite and execute the file, the group has the ability to read and execute the file, and everyone else has no access to the file, use the command chmod 750 filename.
	owner	group	all
	rwx	rwx	rwx
	4 2 1	4 2 1	4 2 1
	sum numbers for each of the three categories of user

Ctrl+c	kills current operation
Ctrl+h	delete (always works even when delete key doesn’t)
Ctrl+d	logout

Frequently used Vi Editor commands

Command	Result
vi filename	opens file (vi with no argument opens new file)
Two modes in vi editor: insert and command (default is command mode when file is opened)
i	change to insert mode - in this mode you can add text
Esc	return to command mode
in command mode →
	:q	quit file
	:q!	quit file without saving any changes
	:w	save file (write)
	:wq	save and quit file (write and quit)
	x	delete character
	/string	search forward for string (n key finds next)
	?string	search backwards for string (n key finds previous)
	:g/string/s//string2/g	substitute string with string2 globally
	:line#	jump to line# in file
	:$	jump to last line of file
	:.,$d	delete from current line to end of file
	:.,.+20d	delete from current line through next 20

SQL interview Questions CSC

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

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

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

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

Tuesday, July 27, 2010

Hurix Recruiting Senior Programmer / Technical Lead

1. Senior Programmer / Technical Lead
Should have 5-8 years of industry experience including 2+ years in rich Internet applications
and database-driven applications and LMS-based projects.

Should possess a sound knowledge of AICC/SCORM implementation with Flash, and have an
excellent knowledge of Action Script 3.0 or 2.0.

Should possess a strong command over OOPs concepts.

Should also have an excellent command over Action Script 2.0 and 3.0 (preferably with three
years experience in this field).

Experience in relative technologies such as ASP/JSP and Macromedia technologies such as
communication server, flex, Director, Flash remoting and cold fusion will be an added
advantage.

Responsibilities:
Will be responsible to develop, troubleshoot, and test data players for AICC/SCORM-compliant
eLearning projects.

Will program interactivities and games for eLearning projects.

Will also mentor and lead junior visual programmers.

2. Senior Instructional Designer/ Lead ID
Should possess excellent reviewing skills, and have a minimum of 5 years of experience in a
corporation, educational institute, publication house, or journalism.

Prior e-Learning experience will be preferred.

Should also have sound knowledge of Instructional Design theories and practices.

Responsibilities :
Will manage a team of Instructional Writers and be responsible for deliverables within project
schedules.

Should have designed instructional and visual strategies for technical content, and should also
have done design and development reviews.

Exposure to technical content creation for Fortune 500 companies will be an added advantage.