Learn SQL [Practical aspects]

SQL stands for "Structured Query Language". It is used by relational database technologies such as Oracle, Microsoft Access, and Sybase, among others. We've categorized SQL into the following topics:

Data Types	UNION Query
SELECT Statement	UNION ALL Query
DISTINCT	INTERSECT Query
COUNT / SUM / MIN / MAX	MINUS Query
WHERE Clause	UPDATE Statement
"AND" Condition	INSERT Statement
"OR" Condition	DELETE Statement
Combining "AND" with "OR"
	Tables (create, alter, drop, temp)
"LIKE" Condition	Views
"IN" Function
BETWEEN Condition
EXISTS Condition
GROUP BY
HAVING
ORDER BY (sort by)
JOINS (inner, outer)

SQL: Data Types

The following is a list of general SQL datatypes that may not be supported by all relational databases.

Data Type	Syntax	Explanation (if applicable)
integer	integer
smallint	smallint
numeric	numeric(p,s)	Where p is a precision value; s is a scale value. For example, numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after the decimal.
decimal	decimal(p,s)	Where p is a precision value; s is a scale value.
real	real	Single-precision floating point number
double precision	double precision	Double-precision floating point number
float	float(p)	Where p is a precision value.
character	char(x)	Where x is the number of characters to store. This data type is space padded to fill the number of characters specified.
character varying	varchar2(x)	Where x is the number of characters to store. This data type does NOT space pad.
bit	bit(x)	Where x is the number of bits to store.
bit varying	bit varying(x)	Where x is the number of bits to store. The length can vary up to x.
date	date	Stores year, month, and day values.
time	time	Stores the hour, minute, and second values.
timestamp	timestamp	Stores year, month, day, hour, minute, and second values.
time with time zone	time with time zone	Exactly the same as time, but also stores an offset from UTC of the time specified.
timestamp with time zone	timestamp with time zone	Exactly the same as timestamp, but also stores an offset from UTC of the time specified.
year-month interval		Contains a year value, a month value, or both.
day-time interval		Contains a day value, an hour value, a minute value, and/or a second value.

SQL: SELECT Statement

--The SELECT statement allows you to retrieve records from one or more tables in your database.
--The syntax for the SELECT statement is:

SELECT columns
FROM tables
WHERE predicates;

Example #1

Let's take a look at how to select all fields from a table.

SELECT *
FROM suppliers
WHERE city = 'Newark';

--In our example, we've used * to signify that we wish to view all fields from the suppliers table where the      supplier resides in Newark.

Example #2

You can also choose to select individual fields as opposed to all fields in the table.

For example:

SELECT name, city, state
FROM suppliers
WHERE supplier_id > 1000;

This select statement would return all name, city, and state values from the suppliers table where the supplier_id value is greater than 1000.

DISTINCT Clause

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax for the DISTINCT clause is:

SELECT DISTINCT columns
FROM tables
WHERE predicates;

Example #1

Let's take a look at a very simple example.

SELECT DISTINCT city
FROM suppliers;

This SQL statement would return all unique cities from the suppliers table.

Example #2

The DISTINCT clause can be used with more than one field.
For example:

SELECT DISTINCT city, state
FROM suppliers;

This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

Example #3

You can also use the select statement to retrieve fields from multiple tables.

SELECT orders.order_id, suppliers.name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

The result set would display the order_id and suppier name fields where the supplier_id value existed in both the suppliers and orders table.

COUNT Function

The COUNT function returns the number of rows in a query.
The syntax for the COUNT function is:

SELECT COUNT(expression)
FROM tables
WHERE predicates;

Note:

The COUNT function will only count those records in which the field in the brackets is NOT NULL.

For example, if you have the following table called suppliers:

Supplier_ID	Supplier_Name	State
1	IBM	CA
2	Microsoft
3	NVidia

The result for this query will return 3.

Select COUNT(Supplier_ID) from suppliers;

While the result for the next query will only return 1, since there is only one row in the suppliers table where the State field is NOT NULL.

Select COUNT(State) from suppliers;

Simple Example

For example, you might wish to know how many employees have a salary that is above $25,000 / year.

SELECT COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000;

In this example, we've aliased the count(*) field as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

Example using DISTINCT

You can use the DISTINCT clause within the COUNT function.
For example, the SQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year.

SELECT COUNT(DISTINCT department) as "Unique departments"
FROM employees
WHERE salary > 25000;

Again, the count(DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.

Example using GROUP BY

In some cases, you will be required to use a GROUP BY clause with the COUNT function.
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

SUM Function

The SUM function returns the summed value of an expression.
The syntax for the SUM function is:

SELECT SUM(expression )
FROM tables
WHERE predicates;

--expression can be a numeric field or formula.

Simple Example

For example, you might wish to know how the combined total salary of all employees whose salary is above $25,000 / year.

SELECT SUM(salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

--In this example, we've aliased the sum(salary) field as "Total Salary". As a result, "Total Salary" ---    will display as the field name when the result set is returned.

Example using DISTINCT

You can use the DISTINCT clause within the SUM function. For example, the SQL statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year.

SELECT SUM(DISTINCT salary) as "Total Salary"
FROM employees
WHERE salary > 25000;

If there were two salaries of $30,000/year, only one of these values would be used in the SUM function.

Example using a Formula

The expression contained within the SUM function does not need to be a single field. You could also use a formula. For example, you might want the net income for a business. Net Income is calculated as total income less total expenses.

SELECT SUM(income - expenses) as "Net Income"
FROM gl_transactions;

--You might also want to perform a mathematical operation within a SUM function. For example, you might      determine total commission as 10% of total sales.

SELECT SUM(sales * 0.10) as "Commission"
FROM order_details;

Example using GROUP BY

--In some cases, you will be required to use a GROUP BY clause with the SUM function.
--For example, you could also use the SUM function to return the name of the department and the total sales (in   the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

--Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function,  you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

MIN Function

The MIN function returns the minimum value of an expression.

The syntax for the MIN function is:

SELECT MIN(expression )
FROM tables
WHERE predicates;

Simple Example

For example, you might wish to know the minimum salary of all employees.

SELECT MIN(salary) as "Lowest salary"
FROM employees;

In this example, we've aliased the min(salary) field as "Lowest salary". As a result, "Lowest salary" will display as the field name when the result set is returned.

Example using GROUP BY

--In some cases, you will be required to use a GROUP BY clause with the MIN function.
--For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the MIN function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

MAX Function

The MAX function returns the maximum value of an expression.

The syntax for the MAX function is:

SELECT MAX(expression)
FROM tables
WHERE predicates;

Simple Example

For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) as "Highest salary" FROM employees;

In this example, we've aliased the max(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned.

Example using GROUP BY

--In some cases, you will be required to use a GROUP BY clause with the MAX function.

--For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;

--Because you have listed one column in your SELECT statement that is not encapsulated in the MAX function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

CREATE TABLE

The basic syntax for a CREATE TABLE is:



SYNTAX:


CREATE TABLE table_name(
column1 datatype null/not null,
column2 datatype null/not null,
...
);


Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

For example:

CREATE TABLE supplier
(	supplier_id	numeric(100)	not null,
	supplier_name	varchar2(100)	not null,
	contact_name	varchar2(100),
	contact_no	varchar2(100)	Not null,
)

 

CREATE Table from another table

You can also create a table from an existing table by copying the existing table's columns.
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax #1 - Copying all columns from another table

The basic syntax is:

CREATE TABLE target_table AS (SELECT * FROM source_table);

For example:

CREATE TABLE suppliers AS (SELECT * FROM companies WHERE id > 1000);

--This would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #2 - Copying selected columns from another table

The basic syntax is:

CREATE TABLE target_table
AS (SELECT column_1, column2, ... column_n FROM source_table);

For example:

CREATE TABLE suppliers AS (SELECT id, address, city, state, zip  FROM companies WHERE id > 1000);

--This would create a new table called suppliers, but the new table would only include the specified columns from the companies table.
--Again, if there were records in the companies table, then the new suppliers table would also contain the records selected by the SELECT statement.

Syntax #3 - Copying selected columns from multiple tables

The basic syntax is:

CREATE TABLE target_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... source_table_n);

For example:

CREATE TABLE suppliers
AS (SELECT cy.id, cy.address, cat.cat_type
FROM companies cy, categories cat
WHERE cy.id = cat.id
AND cy.id > 1000);


--This would create a new table called suppliers based on columns from both the companies and categories tables.

ALTER Table

The ALTER TABLE command allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
RENAME TO vendors;

--This will rename the suppliers table to vendors.

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD column_name column-definition;

For example:

ALTER TABLE supplier
ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	...
	column_n	column_definition );

For example:

ALTER TABLE supplier
ADD (	supplier_name	varchar2(50),
	city	varchar2(45) );

--This will add two columns (supplier_name and city) to the supplier table.

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY column_name column_type;

For example:

ALTER TABLE supplier
MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (	column_1	column_type,
	column_2	column_type,
	...
	column_n	column_type );

For example:

ALTER TABLE supplier
MODIFY (	supplier_name	varchar2(100)	not null,
	city	varchar2(75)		);

This will modify both the supplier_name and city columns.

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
DROP COLUMN column_name;

For example:

ALTER TABLE supplier
DROP COLUMN supplier_name;

--This will drop the column called supplier_name from the table called supplier.

Rename column(s) in a table

Syntax #1

Started from Oracle 9i Release 2, you can rename a column.

--To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
RENAME COLUMN supplier_name to sname;

--This will rename the column called supplier_name to sname.

SQL: DROP Table

The basic syntax for a DROP TABLE is:

DROP TABLE table_name;

For example:

DROP TABLE supplier;


--This would drop table called supplier

WHERE Clause

The WHERE clause allows you to filter the results from an SQL statement - select, insert, update, or delete statement.
It is difficult to explain the basic syntax for the WHERE clause, so instead, we'll take a look at some examples.

Example #1

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM';

--In this first example, we've used the WHERE clause to filter our results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the supplier_name is IBM. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

Example #2

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'IBM'
or supplier_city = 'Newark';

We can define a WHERE clause with multiple conditions. This SQL statement would return all supplier_id values where the supplier_name is IBM or the supplier_city is Newark.

Example #3

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_city = 'Atlantic City';

We can also use the WHERE clause to join multiple tables together in a single SQL statement. This SQL statement would return all supplier names and order_ids where there is a matching record in the suppliers and orders tables based on supplier_id, and where the supplier_city is Atlantic City.
Learn more about joins.

"AND" Condition

The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax for the AND condition is:

SELECT columns
FROM tables
WHERE column1 = 'value1'
and column2 = 'value2';

The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.

Example #1

The first example that we'll take a look at involves a very simple example using the AND condition.

SELECT *
FROM suppliers
WHERE city = 'New York'
and type = 'PC Manufacturer';

This would return all suppliers that reside in New York and are PC Manufacturers. Because the * is used in the select, all fields from the supplier table would appear in the result set.

Example #2

Our next example demonstrates how the AND condition can be used to "join" multiple tables in an SQL statement.

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';

--This would return all rows where the supplier_name is IBM. And the suppliers and orders tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the suppliers and orders tables.
In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).

"OR" Condition

The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax for the OR condition is:

SELECT columns
FROM tables
WHERE column1 = 'value1'
or column2 = 'value2';

The OR condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal 'value1' OR column2 has to equal 'value2'.

Example #1

The first example that we'll take a look at involves a very simple example using the OR condition.

SELECT *
FROM suppliers
WHERE city = 'New York'
or city = 'Newark';

--This would return all suppliers that reside in either New York or Newark. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

Example #2

The next example takes a look at three conditions. If any of these conditions is met, the record will be included in the result set.

SELECT supplier_id
FROM suppliers
WHERE name = 'IBM'
or name = 'Hewlett Packard'
or name = 'Gateway';

--This SQL statement would return all supplier_id values where the supplier's name is either IBM, Hewlett Packard or Gateway.

Combining the "AND" and "OR" Conditions

The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.

When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.

Example #1

The first example that we'll take a look at an example that combines the AND and OR conditions.

SELECT *
FROM suppliers
WHERE (city = 'New York' and name = 'IBM')
or (city = 'Newark');

This would return all suppliers that reside in New York whose name is IBM and all suppliers that reside in Newark. The brackets determine what order the AND and OR conditions are evaluated in.

Example #2

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
or (name = 'Hewlett Packard' and city = 'Atlantic City')
or (name = 'Gateway' and status = 'Active' and city = 'Burma');

This SQL statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.

BETWEEN Condition

The BETWEEN condition allows you to retrieve values within a range.
The syntax for the BETWEEN condition is:

SELECT columns
FROM tables
WHERE column1 between value1 and value2;

This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1 - Numbers

The following is an SQL statement that uses the BETWEEN function:

SELECT *
FROM suppliers
WHERE supplier_id between 5000 AND 5010;

This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;

Example #2 - Dates

You can also use the BETWEEN function with dates.

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).

It would be equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

Example #3 - NOT BETWEEN

The BETWEEN function can also be combined with the NOT operator.

For example,

SELECT *
FROM suppliers
WHERE supplier_id not between 5000 and 5500;

This would be equivalent to the following SQL:

SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;

In this example, the result set would exclude all supplier_id values between the range of 5000 and 5500 (inclusive).

GROUP BY Clause

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

The syntax for the GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;

HAVING Clause

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

The syntax for the HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

--aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;

ORDER BY Clause

The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.

The syntax for the ORDER BY clause is:

SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;

The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, it is sorted by ASC.

ASC indicates ascending order. (default)
DESC indicates descending order.

Example #1

SELECT supplier_cityFROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;

This would return all records sorted by the supplier_city field in ascending order.

Example #2

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;

This would return all records sorted by the supplier_city field in descending order.

Example #3

You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;

This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.

Example #4

SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;

--This would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.

UPDATE Statement

The UPDATE statement allows you to update a single record or multiple records in a table.

The syntax the UPDATE statement is:

UPDATE table
SET column = expression
WHERE predicates;

Example #1 - Simple example

Let's take a look at a very simple example.

UPDATE suppliers
SET name = 'HP'
WHERE name = 'IBM';

This statement would update all supplier names in the suppliers table from IBM to HP.

Example #2 - More complex example

--You can also perform more complicated updates.
--You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.

For example:

UPDATE suppliers
SET supplier_name =	( SELECT customers.name FROM customers WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS   ( SELECT customers.name     FROM customers     WHERE customers.customer_id = suppliers.supplier_id);

Whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer name from the customers table.

"IN" Function

The IN function helps reduce the need to use multiple OR conditions.

The syntax for the IN function is:

SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);

This SQL statement will return the records where column1 is value1, value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1

The following is an SQL statement that uses the IN function:

SELECT *
FROM suppliers
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

It is equivalent to the following statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the IN function makes the statement easier to read and more efficient.

Example #2

You can also use the IN function with numeric values.

SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);

This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

It is equivalent to the following statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example #3 using "NOT IN"

The IN function can also be combined with the NOT operator.
For example,

SELECT *
FROM suppliers
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');


--This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. --Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.