SQL Programming Tutorial

Data Search

The Structured Query Language (SQL) is used to interact with a variety of databases like Oracle and Microsoft's SQL Server. With SQL you can retrieve and manipulate the data contained within a database as well as change its structure by adding tables or procedures.

Instructions

Data Commands

1. Add data to a table with the insert command.

   INSERT INTO table_name(column_name_1, column_name_2)
   VALUES (column_value_1, column_value_2)

2. Get data from a table with the select command. Using the star symbol returns every column in the table. If you wanted to retrieve a subset of the columns you would simply have to type out each column name.

   SELECT *
   FROM table_name

3. Sort data by adding an ORDER BY clause to your query. You can sort by multiple fields in either ascending (ASC) or descending order (DESC).

   SELECT *
   FROM table_name
   ORDER BY column_name

4. Return unique values with the DISTINCT clause.

   SELECT DISTINCT column_name
   FROM table_name

5. Filter data by adding a where clause to your select command. The where clause can contain any expression. The example below finds records where column_name_2 are null or empty, but you could also compare numbers, strings or dates. You can put multiple expressions in a where clause by inserting an AND or OR clause.

   SELECT column_name_1
   FROM table_name
   WHERE column_name_2 IS NULL

6. Return a small portion of data from a table instead of all of it with the TOP command. This applies only to SQL Server. To accomplish the same thing in Oracle you would use a where clause with an expression using the ROWNUM variable.

   SELECT TOP 100 *
   FROM table_name

7. Remove data from a table with the delete command. The example below removes records where the contents of the string field column_name_2 contain the word "junk". The percent signs indicate wild-card values so any number of characters could come before or after the word junk in this example. Without using wild-cards the value would have to match exactly and the like condition would work much like an equal.

   DELETE FROM table_name
   WHERE column_name_2 LIKE '%junk%'

8. Edit data in a table by using the update command. The example below replaces null values with empty strings, which are not the same thing in SQL. The NOT clause returns the opposite of the expression.

   UPDATE table_name
   SET column_name_2 = ''
   WHERE column_name_2 IS NULL AND NOT column_name_1 IS NULL

9. Summarize data using the GROUP BY clause and a at least one data aggregate clause. The possible aggregate functions are MAX, MIN, SUM, AVG for an average and COUNT for the number of records. You can use aggregate clauses without the GROUP BY clause but not the other way around. The example below returns the number of records for each value in column_name_2.

   SELECT COUNT(*), column_name_2
   FROM table_name
   GROUP BY column_name_2

10. Join multiple tables together using the INNER, LEFT, RIGHT and OUTER join clauses. Inner joins return data where the value is present in both tables. Left and right joins return all the data in the left or right side table respectively regards less of whether there is a match present in the other table. Outer or full joins return data where the value is present in either table.

    SELECT T1.C1, T2.C5
    FROM table_name_1 T1 INNER JOIN
    table_name_2 T2 ON T1.id = T2.id

Table Commands

1. Add a table with the CREATE command. There are many data types available for columns, but the basics are numbers, dates and strings.

   CREATE TABLE table_name
   (
   column1 int,
   column2 datetime,
   column3 varchar(50)
   )

2. Edit a table structure with the ALTER command. Some of the other data types available in SQL are bit (zero or one, like a boolean value), money, text (a string of unlimited length) and image.

   ALTER table_name
   ADD column4 bit

3. Remove all the data in a table with the TRUNCATE command.

   TRUNCATE TABLE table_name

4. Remove a table from the database with the DROP command.

   DROP TABLE table_name

5. Use the same commands to manipulate views and stored procedures that you would with tables. A view is like a virtual table whose contents are determined by a query. A stored procedure is a set of commands to be executed at the same time.

Other Useful Commands

1. Return the current date with the GETDATE() function.

2. Mask a column name using the AS command.

   SELECT column_name_1 AS Col1
   FROM table_name

3. Create a table alias by adding a name after it.

   SELECT T1.column_name_1
   FROM table_name T1

4. Compare a field to multiple values using the IN clause.

   SELECT *
   FROM table_name
   WHERE column_name_1 IN ('good', 'better', 'best')

5. Use the BETWEEN command to find data between two values. The between command may or may not be inclusive in the database you are working with so be sure to check before using it.

   WHERE column1 BETWEEN '1/1/2009' AND '1/1/2010'

   instead of

   WHERE column1 > '1/1/2009' AND column2 < '1/1/2010'

References

• w3schools: SQL Tutorial