SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, update, delete) and database creation.
Almost all modern Relational Database Management Systems like MS SQL Server, Microsoft Access, MSDE, Oracle, DB2, Sybase, MySQL, Postgres and Informix use SQL as standard database language. Now a word of warning here, although all those RDBMS use SQL, they use different SQL dialects. For example MS SQL Server specific version of the SQL is called T-SQL, Oracle version of SQL is called PL/SQL, MS Access version of SQL is called JET SQL, etc.
Our SQL tutorial will teach you how to use commonly used SQL commands and you will be able to apply most of the knowledge gathered from this SQL tutorial to any of the databases above. SQL Tutorial Table of Contents
SQL Tutorial Learn what SQL (Structured Query Language) is, and where and how it is used.
SQL Table SQL Database Tables are the foundation of every RDBMS (Relational Database Management System). Learn more about SQL tables here.
SQL SELECT Learn how to use the SELECT SQL statement to retrieve data from a SQL database table.
SQL SELECT INTO Learn how to use the SQL SELECT INTO statement to copy data between database tables.
SQL DISTINCT Learn how to use the SQL DISTINCT clause together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column.
SQL WHERE The SQL WHERE command is used to specify selection criteria, thus restricting the result of a SQL query.
SQL LIKE The SQL LIKE clause is used along with the SQL WHERE clause and specifies criteria based on a string pattern.
SQL INSERT INTO Learn how to use the SQL INSERT INTO clause to insert data into a SQL database table.
SQL UPDATE Learn how to use the SQL UPDATE statement to update data in a SQL database table.
SQL DELETE Learn how to use the SQL DELETE statement to delete data from a SQL database table.
SQL ORDER BY Learn how to use the SQL ORDER BY statement to sort the data retrieved in your SQL query.
SQL OR & AND Learn how to use the SQL OR & AND keywords together with the SQL WHERE clause to add several conditions to your SQL statement.
SQL IN The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria.
SQL BETWEEN The SQL BETWEEN & AND keywords define a range of data between 2 values.
SQL Aliases SQL aliases can be used with database tables and/or with database table columns, depending on task you are performing.
SQL COUNT The SQL COUNT aggregate function is used to count the number of rows in a database table.
SQL MAX The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
SQL MIN The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
SQL AVG The SQL AVG aggregate function selects the average value for a certain table column.
SQL SUM The SQL SUM aggregate function allows selecting the total for a numeric column.
SQL GROUP BY The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
SQL HAVING The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.
SQL JOIN The SQL JOIN clause is used whenever we have to select data from 2 or more tables.
The foundation of every Relational Database Management System is a database object called table. Every database consists of one or more tables, which store the database’s data/information. Each table has its own unique name and consists of columns and rows.
The database table columns (called also table fields) have their own unique names and have a pre-defined data types. Table columns can have various attributes defining the column functionality (the column is a primary key, there is an index defined on the column, the column has certain default value, etc.).
While table columns describe the data types, the table rows contain the actual data for the columns.
Here is an example of a simple database table, containing customers data. The first row, listed in bold, contains the names of the table columns:
Table: Customers
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
Now that we’ve learned what is a database table, we can continue with our sql tutorial and learn how to manipulate the data within the database tables.
The SQL SELECT statement is used to select data from a SQL database table. This is usually the very first SQL command every SQL newbie learns and this is because the SELECT SQL statement is one of the most used SQL commands.
Please have a look at the general SQL SELECT syntax:
SELECT Column1, Column2, Column3, FROM Table1
The list of column names after the SQL SELECT command determines which columns you want to be returned in your result set. If you want to select all columns from a database table, you can use the following SQL statement:
SELECT * FROM Table1;
When the list of columns following the SELECT SQL command is replaced with asterix (*) all table columns are returned. Word of caution here, it’s always better to explicitly specify the columns in the SELECT list, as this will improve your query performance significantly.
The table name following the SQL FROM keyword (in our case Table1) tells the SQL interpreter which table to use to retrieve the data.
The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.
The general SQL SELECT INTO syntax looks like this:
SELECT Column1, Column2, Column3, INTO Table2 FROM Table1
The list of column names after the SQL SELECT command determines which columns will be copied, and the table name after the SQL INTO keyword specifies to which table to copy those rows.
If we want to make an exact copy of the data in our Customers table, we need the following SQL SELECT INTO statement:
The SQL DISTINCT clause is used together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column.
We will use our Customers database table to illustrate the usage of SQL DISTINCT.
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
For example if we want to select all distinct surnames from our Customers table, we will use the following SQL DISTINCT statement:
SELECT DISTINCT LastName FROM Customers
The result of the SQL DISTINCT expression above will look like this:
The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. We are going to use the Customers table from the previous chapter, to illustrate the use of the SQL WHERE command.
Table: Customers
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
If we want to select all customers from our database table, having last name 'Smith' we need to use the following SQL syntax:
SELECT * FROM Customers WHERE LastName = 'Smith'
The result of the SQL expression above will be the following:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
In this simple SQL query we used the "=" (Equal) operator in our WHERE criteria:
LastName = 'Smith'
But we can use any of the following comparison operators in conjunction with the SQL WHERE clause:
<> (Not Equal)
SELECT * FROM Customers WHERE LastName <> 'Smith'
> (Greater than)
SELECT * FROM Customers WHERE DOB > '1/1/1970'
>= (Greater or Equal)
SELECT * FROM Customers WHERE DOB >= '1/1/1970'
< (Less than)
SELECT * FROM Customers WHERE DOB < '1/1/1970'
<= (Less or Equal)
SELECT * FROM Customers WHERE DOB =< '1/1/1970'
LIKE (similar to)
SELECT * FROM Customers WHERE Phone LIKE '626%'
Note the LIKE syntax is different with the different RDBMS (SQL Server syntax used above). Check the SQL LIKE article for more details.
Between (Defines a range)
SELECT * FROM Customers WHERE DOB BETWEEN '1/1/1970' AND '1/1/1975'
We will use the Customers table to illustrate the SQL LIKE clause usage:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
The SQL LIKE clause is very useful when you want to specify a search condition within your SQL WHERE clause, based on a part of a column contents. For example if you want to select all customers having FirstName starting with 'J' you need to use the following SQL statement:
SELECT * FROM Customers WHERE FirstName LIKE 'J%'
Here is the result of the SQL statement above:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
If you want to select all Customers with phone numbers starting with '416' you will use this SQL expression:
SELECT * FROM Customers WHERE Phone LIKE '416%'
The '%' is a so called wildcard character and represents any string in our pattern. You can put the wildcard anywhere in the string following the SQL LIKE clause and you can put as many wildcards as you like too.
Note that different databases use different characters as wildcard characters, for example '%' is a wildcard character for MS SQL Server representing any string, and '*' is the corresponding wildcard character used in MS Access.
Another wildcard character is '_' representing any single character.
The '[]' specifies a range of characters. Have a look at the following SQL statement:
SELECT * FROM Customers WHERE Phone LIKE '[4-6]_6%'
This SQL expression will return all customers satisfying the following conditions:
* The Phone column starts with a digit between 4 and 6 ([4-6]) * Second character in the Phone column can be anything (_) * The third character in the Phone column is 6 (6) * The remainder of the Phone column can be any character string (%)
Here is the result of this SQL expression:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
The SQL INSERT INTO syntax has 2 main forms and the result of either of them is adding a new row into the database table.
The first syntax form of the INSERT INTO SQL clause doesn't specify the column names where the data will be inserted, but just their values:
INSERT INTO Table1 VALUES (value1, value2, value3…)
The second form of the SQL INSERT INTO command, specifies both the columns and the values to be inserted in them:
INSERT INTO Table1 (Column1, Column2, Column3…) VALUES (Value1, Value2, Value3…)
As you might already have guessed, the number of the columns in the second INSERT INTO syntax form must match the number of values into the SQL statement, otherwise you will get an error.
If we want to insert a new row into our Customers table, we are going to use one of the following 2 SQL statements:
INSERT INTO Customers VALUES ('Peter', 'Hunt', 'peter.hunt@tgmail.net', '1/1/1974', '626 888-8888')
The result of the execution of either of the 2 INSERT INTO SQL statements will be a new row added to our Customers database table:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888 Peter Hunt peter.hunt@tgmail.net 1/1/1974 626 888-8888
If you want to enter data for just a few of the table columns, you’ll have to use the second syntax form of the SQL INSERT INTO clause, because the first form will produce an error if you haven’t supplied values for all columns.
To insert only the FirstName and LastName columns, execute the following SQL statement:
INSERT INTO Customers (FirstName, LastName) VALUES ('Peter', 'Hunt')
UPDATE Table1 SET Column1 = Value1, Column2 = Value2 WHERE Some_Column = Some_Value
The SQL UPDATE clause changes the data in already existing database row(s) and usually we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to specify which row(s) we intend to update.
If we want to update the Mr. Steven Goldfish's date of birth to '5/10/1974' in our Customers database table
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
we need the following SQL UPDATE statement:
UPDATE Customers SET DOB = '5/10/1974' WHERE LastName = 'Goldfish' AND FirstName = 'Steven'
If we don’t specify a WHERE clause in the SQL expression above, all customers' DOB will be updated to '5/10/1974', so be careful with the SQL UPDATE command usage.
We can update several database table rows at once, by using the SQL WHERE clause in our UPDATE statement. For example if we want to change the phone number for all customers with last name Smith (we have 2 in our example Customers table), we need to use the following SQL UPDATE statement:
UPDATE Customers SET Phone = '626 555-5555' WHERE LastName = 'Smith'
After the execution of the UPDATE SQL expression above, the Customers table will look as follows:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 555-5555 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 626 555-5555
So far we’ve learnt how to select data from a database table and how to insert and update data into a database table. Now it’s time to learn how to remove data from a database. Here comes the SQL DELETE statement!
The SQL DELETE command has the following generic SQL syntax:
DELETE FROM Table1 WHERE Some_Column = Some_Value
If you skip the SQL WHERE clause when executing SQL DELETE expression, then all the data in the specified table will be deleted. The following SQL statement will delete all the data from our Customers table and we’ll end up with completely empty table:
DELETE FROM Table1
If you specify a WHERE clause in your SQL DELETE statement, only the table rows satisfying the WHERE criteria will be deleted:
DELETE FROM Customers WHERE LastName = 'Smith'
The SQL query above will delete all database rows having LastName 'Smith' and will leave the Customers table in the following state:
FirstName LastName Email DOB Phone Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
The SQL ORDER BY clause comes in handy when you want to sort your SQL result sets by some column(s). For example if you want to select all the persons from the already familiar Customers table and order the result by date of birth, you will use the following statement:
SELECT * FROM Customers ORDER BY DOB
The result of the above SQL expression will be the following:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
As you can see the rows are sorted in ascending order by the DOB column, but what if you want to sort them in descending order? To do that you will have to add the DESC SQL keyword after your SQL ORDER BY clause:
SELECT * FROM Customers ORDER BY DOB DESC
The result of the SQL query above will look like this:
FirstName LastName Email DOB Phone James Smith jim@supergig.co.uk 20/10/1980 416 323-8888 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
If you don't specify how to order your rows, alphabetically or reverse, than the result set is ordered alphabetically, hence the following to SQL expressions produce the same result:
SELECT * FROM Customers ORDER BY DOB
SELECT * FROM Customers ORDER BY DOB ASC
You can sort your result set by more than one column by specifying those columns in the SQL ORDER BY list. The following SQL expression will order by DOB and LastName:
The SQL AND clause is used when you want to specify more than one condition in your SQL WHERE clause, and at the same time you want all conditions to be true. For example if you want to select all customers with FirstName "John" and LastName "Smith", you will use the following SQL expression:
SELECT * FROM Customers WHERE FirstName = 'John' AND LastName = 'Smith'
The result of the SQL query above is:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222
The following row in our Customer table, satisfies the second of the conditions (LastName = 'Smith'), but not the first one (FirstName = 'John'), and that's why it's not returned by our SQL query:
FirstName LastName Email DOB Phone James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
The SQL OR statement is used in similar fashion and the major difference compared to the SQL AND is that OR clause will return all rows satisfying any of the conditions listed in the WHERE clause.
If we want to select all customers having FirstName 'James' or FirstName 'Paula' we need to use the following SQL statement:
SELECT * FROM Customers WHERE FirstName = 'James' OR FirstName = 'Paula'
The result of this query will be the following:
FirstName LastName Email DOB Phone Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
You can combine AND and OR clauses anyway you want and you can use parentheses to define your logical expressions. Here is an example of such a SQL query, selecting all customers with LastName 'Brown' and FirstName either 'James' or 'Paula':
SELECT * FROM Customers WHERE (FirstName = 'James' OR FirstName = 'Paula') AND LastName = 'Brown'
The result of the SQL expression above will be:
FirstName LastName Email DOB Phone Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232
The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria.
THE SQL IN syntax looks like this:
SELECT Column1, Column2, Column3, … FROM Table1 WHERE Column1 IN (Valu1, Value2, …)
Lets use the EmployeeHours table to illustrate how SQL IN works:
Employee Date Hours John Smith 5/6/2004 8 Allan Babel 5/6/2004 8 Tina Crown 5/6/2004 8 John Smith 5/7/2004 9 Allan Babel 5/7/2004 8 Tina Crown 5/7/2004 10 John Smith 5/8/2004 8 Allan Babel 5/8/2004 8 Tina Crown 5/8/2004 9
Consider the following SQL query using the SQL IN clause:
SELECT * FROM EmployeeHours WHERE Date IN ('5/6/2004', '5/7/2004')
This SQL expression will select only the entries where the column Date has value of '5/6/2004' or '5/7/2004', and you can see the result below:
Employee Date Hours John Smith 5/6/2004 8 Allan Babel 5/6/2004 8 Tina Crown 5/6/2004 8 John Smith 5/7/2004 9 Allan Babel 5/7/2004 8 Tina Crown 5/7/2004 10
We can use the SQL IN statement with another column in our EmployeeHours table:
SELECT * FROM EmployeeHours WHERE Hours IN (9, 10)
The result of the SQL query above will be:
Employee Date Hours John Smith 5/7/2004 9 Tina Crown 5/7/2004 10 Tina Crown 5/8/2004 9
The SQL BETWEEN & AND keywords define a range of data between 2 values.
The SQL BETWEEN syntax looks like this:
SELECT Column1, Column2, Column3, … FROM Table1 WHERE Column1 BETWEEN Value1 AND Value2
The 2 values defining the range for SQL BETWEEN clause can be dates, numbers or just text.
In contrast with the SQL IN keyword, which allows you to specify discrete values in your SQL WHERE criteria, the SQL BETWEEN gives you the ability to specify a range in your search criteria.
We are going to use the familiar Customers table to show how SQL BETWEEN works:
FirstName LastName Email DOB Phone John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
Consider the following SQL BETWEEN statement:
SELECT * FROM Customers WHERE DOB BETWEEN '1/1/1975' AND '1/1/2004'
The SQL BETWEEN statement above will select all Customers having DOB column between '1/1/1975' and '1/1/2004' dates. Here is the result of this SQL expression:
FirstName LastName Email DOB Phone Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
This SQL statement will return the sum of all SaleAmount fields and the result of it will be:
SaleAmount $978.67
Of course you can specify search criteria using the SQL WHERE clause in your SQL SUM statement. If you want to select the total sales for customer with CustomerID = 3, you will use the following SQL SUM statement:
SELECT SUM(SaleAmount) FROM Sales WHERE CustomerID = 3
The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
The best way to explain how and when to use the SQL GROUP BY statement is by example, and that’s what we are going to do.
Consider the following database table called EmployeeHours storing the daily hours for each employee of a factious company:
Employee Date Hours John Smith 5/6/2004 8 Allan Babel 5/6/2004 8 Tina Crown 5/6/2004 8 John Smith 5/7/2004 9 Allan Babel 5/7/2004 8 Tina Crown 5/7/2004 10 John Smith 5/8/2004 8 Allan Babel 5/8/2004 8 Tina Crown 5/8/2004 9
If the manager of the company wants to get the simple sum of all hours worked by all employees, he needs to execute the following SQL statement:
SELECT SUM (Hours) FROM EmployeeHours
But what if the manager wants to get the sum of all hours for each of his employees? To do that he need to modify his SQL query and use the SQL GROUP BY statement:
SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee
The result of the SQL expression above will be the following:
Employee Hours John Smith 25 Allan Babel 24 Tina Crown 27
As you can see we have only one entry for each employee, because we are grouping by the Employee column.
The SQL GROUP BY clause can be used with other SQL aggregate functions, for example SQL AVG:
SELECT Employee, AVG(Hours) FROM EmployeeHours GROUP BY Employee
The result of the SQL statement above will be:
Employee Hours John Smith 8.33 Allan Babel 8 Tina Crown 9
In our Employee table we can group by the date column too, to find out what is the total number of hours worked on each of the dates into the table:
SELECT Date, SUM(Hours) FROM EmployeeHours GROUP BY Date
The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.
You can't specify criteria in a SQL WHERE clause against a column in the SELECT list for which SQL aggregate function is used. For example the following SQL statement will generate an error:
SELECT Employee, SUM (Hours) FROM EmployeeHours WHERE SUM (Hours) > 24 GROUP BY Employee
The SQL HAVING clause is used to do exactly this, to specify a condition for an aggregate function which is used in your query:
SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee HAVING SUM (Hours) > 24
The above SQL statement will select all employees and the sum of their respective hours, as long as this sum is greater than 24. The result of the SQL HAVING clause can be seen below:
The SQL JOIN clause is used whenever we have to select data from 2 or more tables.
To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables.
We are going to illustrate our SQL JOIN example with the following 2 tables:
Customers:
CustomerID FirstName LastName Email DOB Phone 1 John Smith John.Smith@yahoo.com 2/4/1968 626 222-2222 2 Steven Goldfish goldfish@fishhere.net 4/4/1974 323 455-4545 3 Paula Brown pb@herowndomain.org 5/24/1978 416 323-3232 4 James Smith jim@supergig.co.uk 20/10/1980 416 323-8888
As you can see those 2 tables have common field called CustomerID and thanks to that we can extract information from both tables by matching their CustomerID columns.
Consider the following SQL statement:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers, Sales WHERE Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName
The SQL expression above will select all distinct customers (their first and last names) and the total respective amount of dollars they have spent. The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustomerID columns.
Here is the result of this SQL statement:
FirstName LastName SalesPerCustomers John Smith $99.95 Steven Goldfish $100.22 Paula Brown $222.95 James Smith $555.55
The SQL statement above can be re-written using the SQL JOIN clause like this:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName
There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN" (note that different databases have different syntax for their JOIN clauses).
The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn't made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.
If the Sales table has the following rows:
CustomerID Date SaleAmount 2 5/6/2004 $100.22 1 5/6/2004 $99.95
And we use the same SQL JOIN statement from above:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName
We'll get the following result:
FirstName LastName SalesPerCustomers John Smith $99.95 Steven Goldfish $100.22
Even though Paula and James are listed as customers in the Customers table they won't be displayed because they haven't purchased anything yet.
But what if you want to display all the customers and their sales, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.
The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.
If we slightly modify our last SQL statement to:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName
and the Sales table still has the following rows:
CustomerID Date SaleAmount 2 5/6/2004 $100.22 1 5/6/2004 $99.95
The result will be the following:
FirstName LastName SalesPerCustomers John Smith $99.95 Steven Goldfish $100.22 Paula Brown NULL James Smith NULL
As you can see we have selected everything from the Customers (first table). For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).
The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).
IT professionals and students from all over the world have many options for SQL training nowadays. They can learn SQL by going to instructor-led SQL course, they can by a SQL book, they can take an online SQL training course, or they can use one of the many SQL training resources online. The first difference between the above SQL training options is the price tag. Instructor led courses usually last 2 to 5 days and can cost up to several thousand dollars. Online SQL training courses are usually less expensive, but they cost in hundreds of dollars most of the time. Another SQL training option is buying SQL training DVDs. Again the price may vary from $50 to $1,000. Buying a SQL book is the most inexpensive way for SQL preparation (usually cost between $30 and $100). The last option is to use free online resources like SQL-Tutorial.net or SQL Training.
Each of the SQL training alternatives has its pros and cons. For example the instructor led courses have the advantage of real time communication with the instructor and hands-on SQL training. On the other hand they are very expensive and not everybody will be willing to invest thousands of dollars for SQL education. If you can get your company to pay for such SQL course, don’t miss the opportunity.
You can buy SQL training DVDs, but the content won’t be interactive most of the time, which is a drawback. The advantage of the SQL DVD is that is less expensive.
If you buy a SQL training book, make sure that the book has good reviews; otherwise you will be wasting your money.
Finally – Practice, practice and practice again the SQL skills you have learned, no matter which SQL training avenue do you choose.
MS SQL Server or simply SQL Server is RDBMS developed by Microsoft Corp. SQL Server uses a dialect of SQL called Transact-SQL (T-SQL). The SQL Server versions prior 7 were based on Sybase SQL Server, but starting with SQL Server 7.0 Microsoft redeveloped the database engine and SQL Server 7 became the first database server using GUI (graphic user interface). SQL Server supports stored procedures, transactions, replication, database mirroring and clustering. SQL Server 2005
MS SQL Server runs on Windows OS only and the latest release available is SQL Server 2005. SQL Server 2005 is deeply integrated in the .NET framework and software and web developers can write stored procedures for SQL Server 2005 directly from their .NET code (C#, VB.NET, etc.). SQL Server 2005 has the following editions:
* SQL Server Enterprise Edition * SQL Server Workgroup Edition * SQL Server Standard Edition * SQL Server Developer Edition * SQL Server Compact Edition * SQL Server Mobile Edition * SQL Server Express Edition
SQL Server 2005 Express Edition
SQL Server Express Edition is a light version of SQL Server 2005 database engine, which is free to redistribute and use. The SQL Server Express Edition lacks some of the scalability features the fully featured editions have and has some limitations (# of CPUs, max RAM size, max size of a database). SQL Server 2008
The next version of SQL Server will be SQL Server 2008. This version will be release in the beginning of 2008. SQL Server 2008 has been designed to store data in many different formats including but not limited to files, XML, email, spatial, documents.
SQL Hosting is a web hosting plan featuring one or more SQL databases. Having SQL database backend is essential for most of the modern websites, that’s why almost all web hosting providers offer SQL hosting plans nowadays.
The most commonly used SQL hosting databases are MySQL, MS SQL Server 2000/ MS SQL Server 2005, MS Access and Oracle. Which SQL database to choose for your hosting?
You can have SQL hosting on both Windows and Linux/UNIX operating systems, but some of the database engines run only on one of them. For example if you want SQL hosting with MS SQL Server 2000/2005 backend, then you need to look for Windows hosting plan, because SQL Server runs only on Windows. If you are looking for MySQL hosting then you are in luck, because MySQL runs on both Windows and Linux. Oracle also runs on both operating systems, but MS Access hosting requires Windows OS. What are the advantages of SQL hosting?
Having SQL Hosting backend allows for developing robust and scalable web applications, which can handle thousands of visitors daily. When the website content is in a SQL database, it’s very easy to search the content for particular keyword or phrase. Having your data in a RDBMS allows for easy management of the content. Also when a website utilizes SQL hosting, the content of the website is separated by its presentation, which makes it really easy to apply design and layout changes.
SQL Replication term describes a group of technologies allowing information distribution and mirroring between different databases. SQL replication allows not only for copying data between databases, but also copying any database objects as well. Essentially replication performs synchronization between databases. By utilizing SQL replication, you can distribute data to as many remote network locations you need, and you can do that over different types of networks including LAN, WAN, and Internet to name a few. Why use SQL Replication?
There are many reasons why SQL replication exists. In many scenarios replication is done to ensure redundancy. For example if a business runs a mission-critical database, it may be wise to have a replication of this database on a separate physical database server, which can take over in case of failure in the primary database (software corruption, hardware failure, etc.). Another valid reason to use SQL replication is load balancing. By using replication you can share the access load between several database server having identical databases. There are many other reason to use SQL replication, however we won’t discuss them here. Replication Publisher and Subscriber
To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher. MS SQL Server Replication
MS SQL Server has 3 main replication models – Merge Replication, Transactional Replication and Snapshot Replication.
The Merge replication is used when both Publisher and Subscriber need to make changes to their respective databases. In this case both databases might have been changed between runs of the Merge SQL replication, and the replication merges the changes in both locations. Of course when using merge replication you should be aware that there might be conflicts, for example duplicated primary keys. If there is a conflict, then the merge replication follows predetermined conflict resolution plan to correct the issue.
The Transactional replication is usually used with databases where data changes frequently and there’s need for constant refreshing of the data. The replication process watches the publisher’s database for any changes, if there are changes, it distributes them over to the replication subscribers.
The Snapshot replication is relatively rarely used, and as its name implies it does a snapshot of the publisher database at certain point of time and distributes it to the subscribers. The Snapshot replication is time and resource consuming compared to the other two SQL replication types and is used when you need to fully overwrite the database at the subscriber or when you do an initial database replication update, which will be refreshed using one of the other 2 SQL replication types after the initial copy.
SQL Tutorial Learn what SQL (Structured Query Language) is, and where and how it is used.
SQL Table SQL Database Tables are the foundation of every RDBMS (Relational Database Management System). Learn more about SQL tables here.
SQL SELECT Learn how to use the SELECT SQL statement to retrieve data from a SQL database table.
SQL SELECT INTO Learn how to use the SQL SELECT INTO statement to copy data between database tables.
SQL DISTINCT Learn how to use the SQL DISTINCT clause together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column.
SQL WHERE The SQL WHERE command is used to specify selection criteria, thus restricting the result of a SQL query.
SQL LIKE The SQL LIKE clause is used along with the SQL WHERE clause and specifies criteria based on a string pattern.
SQL INSERT INTO Learn how to use the SQL INSERT INTO clause to insert data into a SQL database table.
SQL UPDATE Learn how to use the SQL UPDATE statement to update data in a SQL database table.
SQL DELETE Learn how to use the SQL DELETE statement to delete data from a SQL database table.
SQL ORDER BY Learn how to use the SQL ORDER BY statement to sort the data retrieved in your SQL query.
SQL OR & AND Learn how to use the SQL OR & AND keywords together with the SQL WHERE clause to add several conditions to your SQL statement.
SQL IN The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria.
SQL BETWEEN The SQL BETWEEN & AND keywords define a range of data between 2 values.
SQL Aliases SQL aliases can be used with database tables and/or with database table columns, depending on task you are performing.
SQL COUNT The SQL COUNT aggregate function is used to count the number of rows in a database table.
SQL MAX The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column.
SQL MIN The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
SQL AVG The SQL AVG aggregate function selects the average value for a certain table column.
SQL SUM The SQL SUM aggregate function allows selecting the total for a numeric column.
SQL GROUP BY The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
SQL HAVING The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns.
SQL JOIN The SQL JOIN clause is used whenever we have to select data from 2 or more tables.
* Things to check before you start the lab * Chapter 3 (Class #1, Jan. 16th homework) o Exercise 3.1: Write, compile, and run Hello Java program (30 minutes) o Exercise 3.2: Write, compile, and run Hello Java program using NetBeans (30 minutes) * Chapter 4 (Class #1, Jan. 16th homework) o Exercise 4.1: Declaring, initializing, printing variables (30 minutes) o Exercise 4.2: Declaring, initializing, printing variables using NetBeans (30 minutes) o Exercise 4.3: Conditional operator (30 minutes) o Exercise 4.4: Building and running AverageNumber sample program (30 minutes) o Exercise 4.5: Building and running GreatestValue sample program (30 minutes) * Chapter 5 (Class #2, Jan. 23rd homework) o Exercise 5.1: Getting input from keyboard via BufferedReader class & JavaDoc Online (30 minutes) o Exercise 5.2: Building and running LastThreeWorlds sample programming using NetBeans (30 minutes) o Exercise 5.3: Getting input from keyboard via JOptionPane class (30 minutes) * Chapter 6 (Class #2, Jan. 23rd homework) o Exercise 6.1: Building and running Grades sample application (30 minutes) o Exercise 6.2: Building and running NumWords sample application (30 minutes) o Exercise 6.3: Building and running HundredNames sample application (30 minutes) o Exercise 6.4: Building and running for loop application (30 minutes) * Chapter 7 (Class #3, Jan. 30th homework) o Exercise 7.1: Array (30 minutes) o Exercise 7.2: Building and running DaysOfTheWeek sample application (30 minutes) o Exercise 7.3: Building and running GreatestNumber sample application (30 minutes) * Chapter 8 (Class #3, Jan. 30th homework) o Exercise 8.1: Building and running CommandLine sample application (30 minutes) * Chapter 9 (Class #4, Feb. 6th homework) o Exercise 9.1: Class and object (30 minutes) o Exercise 9.2: Static method and instance methods (30 minutes) o Exercise 9.3: Static method and instance methods 2 (20 minutes) o Exercise 9.4: Pass by value (30 minutes) o Exercise 9.5: Pass by reference (30 minutes) o Exercise 9.6: Comparing objects (30 minutes) o Exercise 9.7: Scope of a variable (10 minutes) * Chapter 10 (Class #5, Feb 13th homework) o Exercise 10.1: Create your own class (30 minutes) o Exercise 10.2: Overloading (30 minutes) o Exercise 10.3: Classpath again (20 minutes) o Exercise 10.4: Packaging (30 minutes) * Chapter 11 (Class #6, Feb 20th homework) o Exercise 11.1: Inheritance - Constructor (30 minutes) o Exercise 11.2: Inheritance - Overriding (30 minutes) o Exercise 11.3: Polymorphism (30 minutes) o Exercise 11.4: Abstract class (30 minutes) o Exercise 11.5: Interfaces (30 minutes) * Chapter 12 (Class #6, Feb 20th homework) o Exercise 12.1: Exception handling (30 minutes)
* Install software as described in Software needed for the lab above. * Once you installed J2SE SDK, please check if it is installed correctly o Make sure the installation has set %JAVA_HOME% (Windows) or $JAVA_HOME (Solaris/Linux) environment variable to the installation directory of J2SE 1.5.0_06 (or later version). + Type "echo %JAVA_HOME%" (Windows) or "echo $JAVA_HOME" (Solaris/Linux) in a terminal window. You should see the following: # C:\Program Files\Java\jdk1.5.0_06 (Windows)
+ /usr/jdk/jdk1.5.0_06 (Solaris/Linux) o Trouble-shooting: Instead of seeing the value of %JAVA_HOME% or $JAVA_HOME environment variable as shown above, if you see the following, it means the environment variable has not been set. (Also under Windows, you have to open a new terminal window to see the change, The existing terminal windows do not see the change.) Please set the environment variable following the appropriate instruction of your operating system or open a new terminal window and try it again if you are running Windows and you have set the environment variable through control panel. + %JAVA_HOME% * Make sure the installation has placed %JAVA_HOME%\bin (Windows) or $JAVA_HOME/bin (Solaris/Linux) in the "path" environment variable. o After you set the environment variable, type "java -version" in a terminal window. (By terminal window, I mean DOS window for Windows platform.) You should see something like following + java version "1.5.0_06" Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05) Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode, sharing) o Trouble-shooting: If you don't see the above, the %JAVA_HOME%\bin is not in your path. You can set the PATH environment variable by following the instruction below (under Windows) + Right click on "My Computer", select properties, and then go to the "advanced" tab. + Once there, click on "Environmental Variables" at the bottom of the window. Look in the part that says "System Variables" and find the "Path" variable. + Select it and click edit, and append your java bin directory ("%JAVA_HOME%\bin") to the path. Then click "ok". + You have to open a new terminal window to have this path to take effect.
# Download and unzip NetBeans Lab Project Files as described in the NetBeans Lab Project Files above.
* Once you unzipped NetBeans Lab Project Files, it should create NetBeansFiles directory.
The goal of this exercise is to let you experience a complete development cycle - writing, compiling, and running a simplest possible Java program - using command line tools. If you have done any programming in the past using different programming languages such as C or C++, this is not that much different from it. (There is a slight difference, however. In Java, the compiler javac compiles the Java source code into what is called bytecode which can be run on any Java compliant platform, thus provides the portability of the Java programs. The bytecode is the same thing as Java class file, which is represented by *.class file notation.)
You will also get some exposure to the concept of the classpath. The classpath is the most basic but essential concept you will need to understand - it is basically a location where *.class files reside. If the classpath is not set up correctly, you will experience the infamous "java.lang.NoClassDefFoundError: " exception.
Steps to follow:
1. mkdir c:\myjavaprograms (to create a directory of your choice - this is the directory where you are going to write Java programs) 2. cd \myjavaprograms (this directory becomes the current directory) 3. Write Hello.java using your editor of choice (in this example, I am using jedit) as shown in Code-3.1-a below. You can cut and paste the code from the Code-3.1-a but I encourage you to write the code line by line yourself manually just to experience some compile errors.
* jedit Hello.java
public class Hello {
/** * My first Java program */ public static void main( String[] args ){
// Print the string "Hello world" on screen System.out.println("Hello world");
}
} Code-3.1-a: Hello.java
4. Compile Hello.java using javac compiler. The javac compiler comes with J2SE SDK you've download. It resides in %JAVA_HOME%\bin (Windows) or $JAVA_HOME/bin (Solaris/Linux) directory. The result of compilation will be the creation of Hello.class file.
* javac Hello.java
Trouble-shooting 3.1.a: If you experience the following error condition, it means the %JAVA_HOME%\bin for Windows platform or $JAVA_HOME/bin for Solaris/Linux platform is not set in your path. You can try C:\Program Files\Java\jdk1.5.0_06\bin\javac Hello.java (for Windows) if you want to proceed without setting the path.
* C:\myjavaprograms>javac Hello.java 'javac' is not recognized as an internal or external command, operable program or batch file.
Trouble-shooting 3.1.b: If your Hello.java program contains a problem such as a typo or missing semi-colon after each statement, you will experience compile errors like an example compile error below (I misspelled it - it should have been System.out.println("Hello world"); instead of Syste.out.println("Hello world");.
* C:\myjavaprograms>javac Hello.java Hello.java:9: package Syste does not exist Syste.out.println("Hello world"); ^ 1 error
Trouble-shooting 3.1.c: If you name your file as hello.java (instead of Hello.java), you will experience the following problem. It is because the file name, hello.java, and the class name inside the code does not match. Inside the code, you use Hello in "public class Hello" statement and you name your file as hello.java. They have to match. And Java is case sensitive. hello.java and Hello.java are different files as far as Java is concerned. So rename the file to Hello.java and do the compilation again.
* C:\myjavaprograms>javac hello.javahello.java:1: class Hello is public, should be declared in a file named Hello.java public class Hello { ^ 1 error
Experimentation 3.1.d: Try "javac -verbose Hello.java" to see what is happening when you run javac. You really don't need to understand what is happening underneath at this point, however, except that it reads Hello.java file and then created Hello.class at the end.
Experimentation 3.1.e: Try "javac -help" to see what options you can specify when you compile Java code using javac compiler. You don't need to understand every option right now but the important options are "-classpath ", "-cp ", "-sourcepath ", "-d ". Please feel free to play around with these options. Among these four, "-classpath ", "-cp " are more important to understand. By the way, "-classpath ", "-cp " are the same thing.
* C:\myjavaprograms>javac -help Usage: javac where possible options include: -g Generate all debugging info -g:none Generate no debugging info -g:{lines,vars,source} Generate only some debugging info -nowarn Generate no warnings -verbose Output messages about what the compiler is doing -deprecation Output source locations where deprecated APIs are used -classpath Specify where to find user class files -cp Specify where to find user class files -sourcepath Specify where to find input source files -bootclasspath Override location of bootstrap class files -extdirs Override location of installed extensions -endorseddirs Override location of endorsed standards path -d Specify where to place generated class files -encoding Specify character encoding used by source files -source Provide source compatibility with specified release
-target Generate class files for specific VM version -version Version information -help Print a synopsis of standard options -X Print a synopsis of nonstandard options -J Pass directly to the runtime system
5. Make sure Hello.class file has been created. The Hello.class file contains bytecode representation of the Hello class.
* dir Hello.class
6. Run the Hello program using java command. The java command starts the Java Virtual Machine and runs the Hello program in this example. A Java program can be made of multiple Java classes and and a set of libraries. In this example, the Hello program just contains a single class called Hello.class. You can regard the java command as Java interpreter.
* java Hello
You should see either of the following results.
* C:\myjavaprograms>java Hello Hello world
or
* C:\myjavaprograms>java Hello Exception in thread "main" java.lang.NoClassDefFoundError: Hello
Trouble-shooting 3.1.g: If you experience the "Exception in thread "main" java.lang.NoClassDefFoundError: Hello" exception as shown above, it is highly likely because you are using predefined or wrong (at least for running this program) CLASSPATH environment variable. You can find out whether CLASSPATH environment variable is set or not as following - in the example below, the CLASSPATH is set to C:\Program Files\Java\jre1.5.0_05\lib\ext\QTJava.zip. (You can type jar tvf "C:\Program Files\Java\jre1.5.0_05\lib\ext\QTJava.zip" at the command line to see what Java classes make up the zip file.)
The "Exception in thread "main" java.lang.NoClassDefFoundError: Hello" means that the Java interpreter, java, could not find the Hello.class file in the classpath. (Please be warned this will be the most frequently encountered error condition you will be experiencing the first time you learn Java programming using command line tools such as javac and java. The NetBeans IDE handles all this for you.)
One way to solve the problem is unsetting the CLASSPATH environment variable for now as described below and run the Hello program again as following. (In this case, the classpath is the current directory which contains the Hello.class file. I will explain how Java interpreter finds which classpath to use shortly.)
* C:\myjavaprograms>java Hello Exception in thread "main" java.lang.NoClassDefFoundError: Hello * C:\myjavaprograms>set CLASSPATH= * C:\myjavaprograms>set CLASSPATH Environment variable CLASSPATH not defined * C:\myjavaprograms>java Hello Hello world
Experimentation 3.1.h: You can also explicitly specify the classpath by using -classpath and -cp option. Try "java -classpath . Hello" and "java -cp . Hello" as shown below. (There is a dot . between -classpath and Hello and -cp and Hello.) It should work as following. What you did is basically specifying that the current directory, which is represented by the dot ., is the classpath you want to use.
* C:\myjavaprograms>java -classpath . Hello Hello world * C:\myjavaprograms>java -cp . Hello Hello world
Experimentation 3.1.i: Try to set the classpath with full directory path, C:\myjavaprograms in this example. It should work as well.
* C:\myjavaprograms>java -classpath C:\myjavaprograms Hello Hello world * C:\myjavaprograms>java -cp C:\myjavaprograms Hello Hello world
Important: The "-classpath " or "-cp " option specifies the location where the class files (in this example, we have only a single class file called Hello.class) reside. When you don't specify the classpath option, the Java interpreter tries to find the class files in the directories specified in the CLASSPATH environment variables or in a current directory. In fact, the Java interpreter tries to find the class files in the following order.
1. In the directories you specify with -classpath or -cp option. 2. In the directories that are specified in the CLASSPATH environment variable (If you have not specified the -classpath or -cp option) 3. In the currently directory (If you have not specified the -classpath or -cp option and the environment variable CLASSPATH is not set)
Online resource: For more detailed description on classpath, please see "Setting the class path" from java.sun.com.
Experimentation 3.1.j: Try "java -classpath .. Hello" to see if it still works. (Instead of single dot ., you specify double dots .. between -classpath and Hello. The double dot .. specifies a parent directory, C:\ in this example while single dot . specifies the current directory, c:\myjavaprograms in this example.) It should fail as following. It is because you set the classpath to a parent directory, C:\, which does not contain Hello.class file.
* C:\myjavaprograms>java -classpath .. Hello (this should fail because you are the parent directory C:\ as classpath here) Exception in thread "main" java.lang.NoClassDefFoundError: Hello
Now try to copy the Hello.class file to the parent directory and try the above command again. You should see the following result. Try to think why this works.
* C:\myjavaprograms>move Hello.class .. (move the Hello.class to parent directory) C:\myjavaprograms>java -classpath .. Hello (run the program using the classpath set to the parent directory) Hello world C:\myjavaprograms>java -classpath C:\ Hello (same as above, you the classpath explicitly) Hello world C:\myjavaprograms>java -classpath . Hello (this should fail because you no longer have Hello.class in the current directory) Exception in thread "main" java.lang.NoClassDefFoundError: Hello
Important: As is mentioned above, you can set system wide classpath via CLASSPATH environment variable. This classpath gets applied system-widely meaning it gets applied to all Java programs running on that platform. Of course, you can override it via -classpath or -cp option when you run a particular program.
Experimentation 3.1.k: Try to set the environment variable CLASSPATH first to the directory that contains Hello.class file and then run the program as following. It should work.
* C:\myjavaprograms>set CLASSPATH=c:\myjavaprograms C:\myjavaprograms>java Hello (You are going to experience the following Exception because you don't have Hello.class in the currently anymore since you moved it to a parent directory in the previous experimentation) Exception in thread "main" java.lang.NoClassDefFoundError: Hello C:\myjavaprograms>move ..\Hello.class . (So move the Hello.class back to the currently directory) C:\myjavaprograms>java Hello Hello world
Experimentation 3.1.l: Try to set the environment variable CLASSPATH to a bogus directory and then run the program as following:
* C:\myjavaprograms>set CLASSPATH=c:\tmp (set the CLASSPATH environment variable to a bogus directory, c:\tmp) C:\myjavaprograms>java Hello (should fail since the c:\tmp directory does not contain Hello.class file) Exception in thread "main" java.lang.NoClassDefFoundError: Hello C:\myjavaprograms>java -cp . Hello (now you are overriding the CLASSPATH with your own classpath) Hello world
Experimentation 3.1.m: Try to set the environment variable CLASSPATH to a set of directories and then run the program as following:
* C:\myjavaprograms>set CLASSPATH=c:\tmp;c:\myjavaprograms C:\myjavaprograms>java Hello Hello world
Important: As you've seen above, you can set the environment variable CLASSPATH to a set of directories. For Windows, you use semi-colon ; while on Solaris/Linux platform, you use colon : as a delimiter. You can also specify multiple directories when you specify -classpath or -cp option as following:
* C:\myjavaprograms>java -classpath c:\tmp Hello (this should fail since c:\tmp does not contain Hello.class file) Exception in thread "main" java.lang.NoClassDefFoundError: Hello C:\myjavaprograms>java -classpath c:\tmp;c:\myjavaprograms Hello (the classpath is set to two directories c:\tmp and c:\myjavaprograms - they will be searched in sequence) Hello world C:\myjavaprograms>java -classpath c:\tmp;. Hello Hello world
Homework:
1. Modify, compile, and run the Hello program so that it prints the following
* "This is my first Java program" (instead of "Hello world")
2. Please play around with the classpath settings with -classpath or -cp option and CLASSPATH environment variable.
In this exercise, you are going to build and run a sample Java program called CommandLineusing NetBeans. The sample program can be built and run as NetBeans project. So you are going to open an existing NetBeans project rather than creating a new project. (This is a similar setup as in Exercise 4.4 and Exercise 4.5 above.)
Steps to follow
0. If you have not downloaded and unzipped the NetBeansFiles.zip file as described above, please do that first. 1. Start NetBeans (if you have not done so.) 2. Open an existing CommandLine sample NetBeans project.
* Select File from menu bar. * Select Open Project (Crtl+SHift+O). * In the Open Project window, browse to C:\NetBeansFiles\Chapter 8\8.1 directory and select CommandLineProject. * Click Open Project Folder button. * Expand CommandLineProject project node. * Expand Source Packages. (The Source Packages node contains all the packages.) * Expand . (If you create Java code without a package statement, it belongs to a default package.) * Double click CommandLine.java to open it in the Editor window.
3. Add command line arguments
* Right click CommandLineProject project node and select Properties. Project Properties dialog box appears. * In the Project Properties dialog box, o Select Run under Categories pane on the left o In the Arguemtns: field, type in 5 4 3 2 1 o Click OK. 4. Run the program.
* Right-click CommandLineProject project node and then select Run Project. * You should see the numbers 5, 4, 3, 2, 1 are displayed in the Output window of NetBeans.
In this exercise, you will exercise the concept object creation using new keyword. We will learn how you are going to create your own Class in the next session.
* If you are new to Object-Oriented Programming concept, please read "Object Oriented Programming Concept" section of the Java Programming Tutorial.
Steps to follow:
1. Write ClassAndObject.java. (You are welcome to do this work using either command line tools or NetBeans. The instruction here is given using command line tools. In general, using NetBeans is highly recommended.) Please make sure you read the comments.
* cd \myjavaprograms * jedit ClassAndObject.java
public class ClassAndObject {
public static void main( String[] args ){
// Create an instance of a class by using a Java language keyword "new". // For example, in order to create an object instance of a String class, // you will do the following String strInstance1 = new String ("I am object instance of a String class"); System.out.println("value of strIntance1 = " + strInstance1);
// String class a special class for which you can create an instance // of it by assigning a string literal. No other class allows this form of object creation. String strInstance2 = "I am object instance of a String class"; System.out.println("value of strIntance2 = " + strInstance2);
// Create an object instance of Integer class Integer intInstance1 = new Integer (20); System.out.println("value of inIntance1 = " + intInstance1);
* C:\myjavaprograms>java -classpath . ClassAndObject value of strIntance1 = I am object instance of a String class value of strIntance2 = I am object instance of a String class value of inIntance1 = 20
Homework:
1. Modify ClassAndObject.java as following, compile and run the code
* Create another object instance of String class using new keyword. Initialize it with the following value. Display it using Sytem.out.println(..) method. o "I am another object instance of String class"
In this exercise, you will learn how to nvoke both static and non-static (instance) methods of a class. Please note that a static method of a class is called in the following form:
* . * Example: o Integer.parseInt("25"); // Convert "25" into int type
while an instance method of class is called only through an object instance as following.
* . * Example o String str1 = new String("Hello"); // Create an object instance of String class o char x = str1.charAt(0); // Call an instance method charAt() of String class through object instance
Steps to follow:
1. Write StaticAndInstanceMethods.java. (You are welcome to do this work using either command line tools or NetBeans. The instruction here is given using command line tools. In general, using NetBeans is highly recommended.)
* cd \myjavaprograms * jedit StaticAndInstanceMethods.java
public class StaticAndInstanceMethods{
public static void main( String[] args ){
// Create two instances of String class String strInstance1 = new String ("I am object instance of a String class"); String strInstance2 = "Live your passion!";
// Invoke an instance method charAt() through an object instance of String class char x = strInstance1.charAt(2); char y = strInstance2.charAt(1); char z = strInstance2.charAt(0);
System.out.println("The 3rd char of strInstance1 = " + x); System.out.println("The 2nd char of strInstance2 = " + y); System.out.println("The 1st char of strInstance2 = " + z);
// Invoke an instance method equalsIgnoreCase(..) method boolean b = strInstance1.equalsIgnoreCase(strInstance2); String strInstance3 = b? "Yes":"No"; System.out.println("Do strInstance1 and strInstance2 have same string ignoring case? " + strInstance3);
// Invoke a static-method, valueOf (int i), of the String class int i = 23; String strInstance4 = String.valueOf(i); System.out.println("value of strInstance4 = " + strInstance4);
// You already have used parseInt() static method of the Integer class in // previous exercises. String strInstance5 = new String("34"); // Create an object instance of String class int ii = Integer.parseInt(strInstance5); System.out.println("value of ii = " + ii);
* C:\myjavaprograms>java -classpath . StaticAndInstanceMethods The 3rd char of strInstance1 = a The 2nd char of strInstance2 = i The 1st char of strInstance2 = L Do strInstance1 and strInstance2 have same string ignoring case? No value of strInstance4 = 23 value of ii = 34
Homework:
1. Write StaticAndInstanceMethods2.java as following, and compile and see what compile errors you get. Fix any compile errors.
public class StaticAndInstanceMethods2{
public static void main( String[] args ){
// Create an instance of a String class by using a keyword "new". // For example, in order to create an object instance of a String class, // you will do the following String strInstance1 = new String ("I am object instance of a String class");
// The following code will generate a compile error since you are trying to // invoke a instance method through a class. Fix this compile error. char x = String.charAt(2);
In this exercise, you are going to build and run a sample Java program called Homework using NetBeans. The sample program can be built and run as NetBeans project. So you are going to open an existing NetBeans project rather than creating a new project. (This is a similar setup as in Exercise 4.4 and Exercise 4.5 above.)
Steps to follow
0. If you have not downloaded and unzipped the NetBeansFiles.zip file as described above, please do that first. 1. Start NetBeans (if you have not done so.) 2. Open an existing Homework sample NetBeans project.
* Select File from menu bar. * Select Open Project (Crtl+SHift+O). * In the Open Project window, browse to C:\NetBeansFiles\Chapter 9\9.2 directory and select HomeworkProject. * Click Open Project Folder button. * Expand HomeworkProject project node. * Expand Source Packages. (The Source Packages node contains all the packages.) * Expand . (If you create Java code without a package statement, it belongs to a default package.) * Double click Homework1.java to open it in the Editor window.
3. Run the program.
* Right-click HomeworkProject project node and then select Run Project.
In this exercise, you will exercise the concept of pass by reference. Please not that primitive type parameters are passed by value while reference type parameters are passed by reference. An array is considered a reference type even though the entries it contains are primitive type.
Steps to follow:
1. Write TestPassByReference.java as shown in Code 9.5 below. (You are welcome to do this work using either command line tools or NetBeans. The instruction here is given using command line tools. In general, using NetBeans is highly recommended.)
* cd \myjavaprograms
* jedit TestPassByReference.java
public class TestPassByReference {
public static void main(String[] args){
System.out.println("main: start");
int [] ages = {10, 11, 12};
for (int i=0; i System.out.println(ages[i]);
}
System.out.println("main: before calling the test method");
test(ages);
System.out.println("main: after calling the test method");
* C:\myjavaprograms>java -cp . TestPassByReference
main: start
10
11
12
main: before calling the test method
test: start
test: end
main: after calling the test method
50
51
52
main: end
Homework: (Optional)
I made this homework as an optional homework. It is because in order to do this homework right, you would need to create your own class, which we will learn in the Class #5. (in the future offerings of this course, I will move this homework to some place else.)
If you still want to do it, please do the homework as following:
1. Modify TestPassByReference.java as following. Compile and run the application.
* Pass the second reference type parameter to the test(..) method - you will have to create your own class and object instance of it. The class should have a private field and getter and setter method of the field.
* Set the value of the field of the object instance using setter method before calling the test(..) method. And change the value of the field of the object instance using setter method within the test(..) method as you did with the first parameter in Code-9.5 above.
* Modify the System.out.println(..) methods in the Code-9.5 above to display the values of both the first parameter and second parameter.
In this exercise, you will exercise the concept of comparing objects using String class as an example.
Steps to follow:
1. Write EqualsTestString.java as shown in Code 9-6.a below. Please make sure you read the comments. (You are welcome to do this work using either command line tools or NetBeans. The instruction here is given using command line tools. In general, using NetBeans is highly recommended.)
* cd \myjavaprograms * jedit EqualsTestString.java
class EqualsTestString { public static void main(String[] arguments) {
// Declare two String type variables, str1 and str2 String str1, str2;
// Initialize the variables. Note that the variables contain // pointers to the actual object instances. str1 = "Life is worth living... with Passion!"; str2 = str1;
// Display the values of the str1 and str2 variables System.out.println("String1: " + str1); System.out.println("String2: " + str2);
// The "==" notation, when it is used with variables of reference types not // primitive types, means whether the variables point to the same object // instance or not.
// Check if str1 and str2 are the same object meaning if the two variables // points to the same String type object instance. System.out.println("Same object? " + (str1 == str2));
// Reinitialize str2 variable. It is now pointing to a new String type object instance. str2 = new String(str1);
// Display the values of the str1 and str2 variables System.out.println("String1: " + str1); System.out.println("String2: " + str2);
// Check if str1 and str2 are the same object meaning if the two variables // points to the same String type object instance. System.out.println("Same object? " + (str1 == str2));
// Check if str1 and str2 have the same vaule System.out.println("Same value? " + str1.equals(str2)); } } Code-9.6.a: EqualsTestString.java
* C:\myjavaprograms>java -classpath . EqualsTestString String1: Life is worth living... with Passion! String2: Life is worth living... with Passion! Same object? true String1: Life is worth living... with Passion! String2: Life is worth living... with Passion! Same object? false Same value? true
Homework:
1. Write EqualsTestInteger.java as following. Compile and run the application.
* Compare object instances of Integer class using the same testing code as in EqualsTestString.java above.
In this exercise, you will exercise the concept scope variable.
Steps to follow:
1. Write ScopeOfVariable.java as shown in Code-9.7.a below. (You are welcome to do this work using either command line tools or NetBeans. The instruction here is given using command line tools. In general, using NetBeans is highly recommended.)
* cd \myjavaprograms * jedit ScopeOfVariable.java
public class ScopeOfVariable {
public static void main( String[] args ){
int var1 = 10;
if (var1 < 100){ int var2 = 20; } else { int var2 = 21; }
// Access to var1 is allowed so no compile error. System.out.println ("value of var1 = " + var1);
// Access to var2 is not allowed so compile error will be generated System.out.println ("value of var2 = " + var2);
}
} Code 9.1.a: ClassAndObject
2. Compile the code
* javac ScopeOfVariable.java
3. You should experience a compile problem as following. The problem is the var2's scope is only within the closest { } and cannot be used
* C:\myjavaprograms>javac ScopeOfVariable.java ScopeOfVariable.java:18: cannot find symbol symbol : variable var2 location: class ScopeOfVariable System.out.println ("value of var2 = " + var2); ^ 1 error
4. Modify the code as as shown in Code-9.7.b below. . The code fragment that is modified is highlighed with bold font.
public class ScopeOfVariable {
public static void main( String[] args ){
int var1 = 10; int var2 = 15;
if (var1 < 100){ var2 = 20; int var3 = 40; System.out.println ("value of var3 = " + var3); } else { var2 = 21; }
// Access to var1 is allowed so no compile error. System.out.println ("value of var1 = " + var1);
// Access to var2 is not allowed so compile error will be generated System.out.println ("value of var2 = " + var2);
So far, you have dealt with a single class that contains main(..) method. In this exercise, you are going to create mulitple classes among which one class invokes another class - more precisely, a method in a class invokes a method of another class.
* If you are new to Object-Oriented Programming concept, please read "Object Oriented Programming Concept" section of the Java Programming Tutorial. * If you are new on how to create a class, please see "Creating Class" section of Java Programmng Tutorial.
Steps to follow:
1. Write StudentRecord.java as shown in Code-10.1.a below. Please note that each *.java file contains a single Java class. (You are welcome to do this work using either command line tools or NetBeans. The instruction for using NetBeans is given below.)
// Declare static variables. private static int studentCount = 0;
/** * Returns the name of the student */ public String getName(){ return name; }
/** * Changes the name of the student */ public void setName(String temp ){ name =temp; }
/** * Computes the average of the english,math and science * grades */ public double getAverage(){ double result =0; result =(mathGrade+englishGrade+scienceGrade )/3; return result; }
/** * Returns the number of instances of StudentRecords */ public static int getStudentCount(){ return studentCount; }
/** * Returns the number of instances of StudentRecords */ public static void increaseStudentCount(){ studentCount++; }
} Code-10.1.a: StudentRecord.java
2. Write StudentRecordExample.java as shown in Code-10.1.b below.
* cd \myjavaprograms (if you are not in this directory already) * jedit StudentRecordExample.java
public class StudentRecordExample{ public static void main(String [] args ){
// Create an object instance of StudentRecord class. StudentRecord annaRecord =new StudentRecord();
// Increament the studentCount by invoking a static method. StudentRecord.increaseStudentCount();
// Create another object instance of StudentRecord class. StudentRecord beahRecord =new StudentRecord();
// Increament the studentCount by invoking a static method. StudentRecord.increaseStudentCount();
// Create the 3rd object instance of StudentRecord class. StudentRecord crisRecord =new StudentRecord();
// Increament the studentCount by invoking a static method. StudentRecord.increaseStudentCount();
// Set the names of the students. annaRecord.setName("Anna"); beahRecord.setName("Beah"); crisRecord.setName("Cris");
* C:\myjavaprograms1>java -classpath . StudentRecordExample Anna Student Count = 3
Steps to follow if you are using NetBeans
1. Start the NetBeans IDE (if you have not done so yet)
* Windows: Start > All Programs > NetBeans 5.0 > NetBeans IDE or click NetBeans IDE 5.0 desktop icon. * Solaris/Linux: /bin/netbeans or click NetBeans IDE desktop icon.
2. Create a new NetBeans project and StudentRecordExample main class
* Select File from the menu bar and select New Project. * Under Choose Project, select General and Java Application * Click Next. * Under Name and Location pane, (Figure-10.1.d below) o For Project Name field, fill it with StudentRecordExample o For Create Main Class field, change it to studentrecordexample.StudentRecordExample (from studentrecordexample.Main) - You are creating StudentRecordExample class under studentrecordexample package. o Click Finish 3. Modify the NetBeans generated code
* Replace the NetBeans generated StudentRecordExample.java code in the source editor with the one of Code-10.1.b above (not Code-10.1.a) while leaving the package studentrecordexample; statement on the top. o You will see NetBeans generates some compile errors indicating it cannot find StudentRecord class. This is what is expected since you have not written StudentRecord class yet,
4. Write StudentRecord.java
* Right click StudentRecordExample project node and select New->Java Class. The New Java Class window appears. * Under Name and Location pane, o for Class Name field, type StudentRecord o for Package field, choose studentrecordexample from the drop-down menu (or you can type studentrecordexample) - You are creating StudentRecord class under studentrecordexample package. o Click Finish
5. Modify the NetBeans generated code
* Replace the NetBeans generated StudentRecord.java code in the source editor with the one of Code-10.1.a above while leaving the package studentrecordexample; statement on the top.
6. Run StudentRecordExample application
* Right click StudentRecordExample project node and select Run Project. * Note that the Output window displays the result
INTRODUCTION TO 'C': C is a programming language developed at AT & T's Bell laboratories of USA in 1972.it was designed by d...
Learning about Events (Continue) Lets program the Form_Load event. "MsgBox" is Visual Basic command that launch a message box. for...
The Command Button's KeyPress, KeyDown and KeyUp Events The events that will be mentioned in the following pages are commonly used, and ...
