Basic SQL Tutorial


I am writing this SQL tutorial with hopes that it provides a detailed introduction to the process of SQL query. You can use as a guide as you write programming for any database management system working with a relational database. It is a step by step instruction that will walk you through the process. Here goes…


Queries: Executing the SQL Statement

The SQL query language is used to extract the data from the database to form meaningful information. This is a simple SQL query language.

  • 1. SELECT [DISTINCT] {COLUMN(S)}
  • 2. FROM {TABLE}
  • 3. [WHERE {CONDITIONS}]
  • 4. [ORDER BY {COLUMN(S) [ASC|DESC]}];

Note:The brackets [ ] are optional but can be included when writing the queries.


Columns (Attributes)


Selecting Columns:

When you are writing the query on selecting the column from a table, it is specifically after keyword select.

  • 1. SELECT CUSTID
  • 2. FROM CUSTOMER;

This query will list only customer identification from the table (relation) customer at your request for the analysis.


Retrieving Multiple Columns

If you intend to retrieve and to receive not just the customer identification, but you also need to extract the phone number for the customer; you may be able to write a query like the following

  • 1. SELECT CUSTID, PHONENUM
  • 2. FROM CUSTOMER;

This query will list only the customer identification and their phone number from the table customer.


Selecting all Columns

You can choose to select all columns if necessary from the relation customer, if the case, the query

  • 1. SELECT *
  • 2. FROM CUSTOMER;

Note:asterisk (*) can be used to denote all columns (attributes).


Use of DISTINCT to Eliminate Duplicates

The DISTINCT is used to eliminate the unnecessary duplicate data in the result data set. The following query

  • 1. SELECT DISTINCT zip
  • 2. FROM CUSTOMER;

It will only return the distinct value of zip code, eliminating the repeating values.


Use of Aggregate Functions

For an arithmetic expression, say you need to know the salary level with a 15% raise, the query would be

  • 1. SELECT EMPID, SAL*1.15
  • 2. FROM EMPLOYEE;

Note: A database tool such as Oracle can support

  • Numbers: abs, cos, sin, exp, log, power, sqrt, +, -, *, /, etc.
  • Strings: chr, concat(string1, string2), lower, upper, translate
  • Date data type: add_month, month_between

Formatting the numbers

For a column that is described in a form of number datatype, you are able to change a format in column command. As you format the numbers, you can add the following

  • Dollar signs
  • Leading zeros
  • Angle brackets for negative numbers
  • Round values

Column Alias

Do you know that you have the ability to change the column name to make your result set much easier to read. For instance, the example of column name currently read as customerid. With column alias, you are able to change from customerid to Client for a formal presentation of data.

  • 1. SELECT customerid Client
  • 2. FROM CUSTOMER;
  • 1. SELECT customerid "Client"
  • 2. FROM CUSTOMER;
  • 1. SELECT customerid AS Client
  • 2. FROM CUSTOMER;

You can use one of these options for your convenience.


Rows (Tuples)


Selecting Rows with use of Comparison Operators

Since the columns have been selected, suppose you have conditions that you need/want to satisfy within the rows; the where clause is used with the comparison operators.

Example: You have been asked to list the students that are taking statistics class.

  • 1. SELECT studentid
  • 2. FROM student
  • 3. WHERE course = 'statistics';

For further comparison operator:

Comparison Operator Definition

  • = Equal
  • !=, <> Not Equal
  • >, >= Greater than, greater than or equal to
  • <, <= Less than, less than or equal to
  • BETWEEN…, AND… Inclusive of two values
  • LIKE Pattern matching with wildcard characters% and _
  • IN (…) List of values
  • IS NULL Test for null values

Exercise:

Write a query listing the last names of students living either in zip code 20332, 29876, or 12312.

  • 1. SELECT last_name
  • 2. FROM student
  • 3. WHERE zip IN('20332', '29876', '12312');
  • OR
  • 1. SELECT last_name
  • 2. FROM student
  • 3. WHERE zip = 20332
  • 4. OR zip = 29876
  • 5. OR zip = 12312;

Selecting Rows with use of Logical Operator

An interesting fact is that the logical operator is also referred to as a Boolean operator. Examples of Boolean operators include AND and OR. Boolean operators’ job is to harness the power of where clause by combining 2 or more comparison operators.

Example:

  • 1. SELECT customerid, zip
  • 2. FROM CUSTOMER
  • 3. WHERE zip = 26569
  • 4. AND customerid LIKe 'J%';

You can see that the data return the result set from the database is not in a particular order. However, you have the ability to set your own preference in order of rows in a particular column. You have options that you can set a descending or ascending order.

Examples:

  • 1. SELECT customerid, address, zip
  • 2. FROM CUSTOMER
  • 3. ORDER BY zip {ASC|DESC};