Learn Online Program
Today Use Tomarrow Use

Database » SQL

What is SQL?

SQL  stands for Structured Query Language. SQL is used to communicate with a database. According to (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".

city, state, high, and low are the columns. The rows contain the data for this table:

Weather
citystatehighlow
PhoenixArizona10590
TucsonArizona10192
FlagstaffArizona8869
San DiegoCalifornia7760
AlbuquerqueNew
Mexico
8072

Selecting Data

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:

select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns.

The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

Conditional selections used in the where clause:

=Equal
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
<>Not equal to
LIKE*See note below

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

select first, last, city from empinfo where first LIKE 'Er%';

This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes.

Or you can specify,

select first, last from empinfo where last LIKE '%s';

This statement will match any last names that end in a 's'.

select * from employees where first = 'Eric';

This will only select rows where the first name equals 'Eric' exactly.

Sample Table: employees
firstlastidagecitystate
JohnJones9998045PaysonArizona
MaryJones9998225PaysonArizona
EricEdwards8823232San DiegoCalifornia
Mary AnnEdwards8823332PhoenixArizona
GingerHowell9800242CottonwoodArizona
SebastianSmith9200123Gila BendArizona
GusGray2232235BagdadArizona
Mary AnnMay3232652TucsonArizona
EricaWilliams3232760Show LowArizona
LeroyBrown3238022PinetopArizona
ElroyCleaver3238222GlobeArizona

Enter the following sample select statements in the SQL Interpreter Form at the bottom of this page. Before you press "submit", write down your expected results. Press "submit", and compare the results.

select first, last, city from employees; select last, city, age from employees; where age > 30; select first, last, city, state from employees;
 where first LIKE 'J%'; select * from 
employees;
select first, last, from 
employees;
 where last LIKE '%s'; select first, last, age from 
employees;
 where last LIKE '%illia%'; 

    • Creating Tables

The create table statement is used to create a new table. Here is the format of a simple create table statement:

create table "tablename"
("column1" "data type", "column2" "data type", "column3" "data type");

 Format of create table if you were to use optional constraints:

create table "tablename"
 ("column1" "data type" [constraint],
 "column2" "data type" [constraint],
 "column3" "data type" [constraint]);
 [ ] = optional

 Note: You may have as many columns as you'd like, and the constraints are optional.

 Example:

create table employee (first varchar(15),   last varchar(20),   age number(3),
 address varchar(30) , city varchar(20),    state varchar(20));

  • Inserting into a Table

The insert statement is used to insert or add a row of data into the table.

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.

insert into "tablename"
(first_column,...last_column)
values (first_value,...last_value);

In the example below, the column name first will match up with the value 'Luke', and the column name state will match up with the value 'Georgia'.

Example:

insert into employee
(first, last, age, address, city, state)
values ('Rishbah', 'kumar', 45, '2130 Boars Nest',
'jaipur', 'Raj');

Updating Records

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.

update "tablename"
set "columnname" =
"newvalue"
[,"nextcolumn" =
"newvalue2"...]
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];

[] = optional

[The above example was line wrapped for better viewing on this Web page.]

Drop a Table

The drop table command is used to delete a table and all rows in the table.

To delete an entire table including all of its rows, issue the drop table command followed by the tablename. drop tableis different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. Dropping the table removes the table definition as well as all of its rows.

drop table "tablename"

Example:

drop table myemployees_ts0211;

Drop Table exercises

  1. Drop your employee table.

Deleting Records

The delete statement is used to delete records or rows from the table.

delete from "tablename"

where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];

[ ] = optional

[The above example was line wrapped for better viewing on this Web page.]

Examples:

delete from employee;

Note: if you leave off the where clause, all records will be deleted!

delete from employee
where lastname = 'May';

delete from employee
where firstname = 'Mike' or firstname = 'Eric';

To delete an entire record/row from a table, enter "delete from" followed by the table name, followed by the whereclause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.



rss feed widget