1. CREATE
This command is used to create a table. The syntax of this command is:
create table tablename
(column1name datatype [constraint],
column2name datatype [constraint],
column3name datatype [constraint]);
create table citylist
(name varchar(20),
state varchar(20),
population number(8),
zipcode number(5) unique);
2. SELECT
The SELECT statement is used to select data from a database. The result is stored in a result table, called the
result-set.
Syntax:
SELECT [ALL | DISTINCT] columnname1 [,columnname2]
FROM tablename1 [,tablename2]
[WHERE condition] [ and|or condition...]
[GROUP BY column-list]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
The sections between the brackets [] are optional. A simpler syntax statement is:
select columnname1 [,columnname2] from tablename [where condition];
A "*" may be used to select all columns. The where clause is optional and only one column name must
be specified.
TheWhere Clause
This clause is used to specify which columns and values are returned. Where conditions specify an
OPERATOR to use for comparison. OPERATORs include:
select city, state from towntable where state LIKE 'north%';
This allows selection of all towns in states that begin with the word "north" allowing states like
North Dakota and North Carolina to be selected.
The GROUP BY Clause
This "GROUP BY" clause allows multiple columns to be grouped so aggregate functions (listed
below) may be performed on multiple columns with one command.
Aggregate function keywords:
SELECT MAX(population)
FROM citylist;
WHERE state = 'Indiana';
Example using the GROUP BY clause which gets the smallest population of each city in every state:
SELECT MIN(population)
FROM citylist;
GROUP BY state;
The HAVING Clause
Allows selection of set test criteria on rows. You can display average size of towns whose population is less
than 100.
The ORDER BY Clause
This clause lets results be displayed in ascending or descending order. Keywords:
The insert statement is used to insert a new row in a table. This statement is used to insert a row of data in a
table. All inserted values are enclosed using single quote strings. The syntax of this command is:
insert into tablename
(column1name,column2name...columnxname)
values (value1,value2...valuex);
Example
insert into citylist
(name, state, population, zipcode)
values ('Argos', 'Indiana', '89', '46501');
or
insert into citylist
values ('Argos', 'Indiana', '89', '46501');
4. UPDATE
The UPDATE statement is used to update existing records in a table. This command is used to make
changes to records in tables. Syntax:
update tablename
set columnname = newvalue [,columnxname = newvaluex...]
where columnname OPERATOR value [and|or columnnamex OPERATOR valuex];
The OPERATOR is one of the conditions listed on the Select Command page.
OPERATORs include:
This allows selection of all towns in states that begin with the word "north" allowing states like
North Dakota and North Carolina to be selected.
Example
update citylist
set population = population+1
where name = 'Argos' and
state = 'Indiana';
5. DELETE
The DELETE statement is used to delete rows in a table. This command is used to delete rows or records in
a table. The syntax of this command is:
delete from "tablename"
where columnname OPERATOR value [and|or
columnnamex OPERATOR valuex];
Without the where clause, all records in the table will be deleted.
Example
delete from citylist
where name = 'Argos' and state = 'Indiana';
6. DROP
The DROP TABLE statement is used to delete a table. The DROP DATABASE statement is used to delete
a database. Used to remove an entire table from the database. Syntax:
drop table tablename
Example
drop table citylist;
7. ALTER
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. To add a
column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
To delete a column in a table, use the following syntax
ALTER TABLE table_name
DROP COLUMN column_name;
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
8. RENAME
We can rename any table by using RENAME sql command. The data will not be lost. Only the table name
will be changed to new name. Here is the command to change the name of a table.
Syntax :- RENAME Source table name to destination table name;
Example:- rename emp to employee;
This command is used to create a table. The syntax of this command is:
create table tablename
(column1name datatype [constraint],
column2name datatype [constraint],
column3name datatype [constraint]);
create table citylist
(name varchar(20),
state varchar(20),
population number(8),
zipcode number(5) unique);
2. SELECT
The SELECT statement is used to select data from a database. The result is stored in a result table, called the
result-set.
Syntax:
SELECT [ALL | DISTINCT] columnname1 [,columnname2]
FROM tablename1 [,tablename2]
[WHERE condition] [ and|or condition...]
[GROUP BY column-list]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]
The sections between the brackets [] are optional. A simpler syntax statement is:
select columnname1 [,columnname2] from tablename [where condition];
A "*" may be used to select all columns. The where clause is optional and only one column name must
be specified.
TheWhere Clause
This clause is used to specify which columns and values are returned. Where conditions specify an
OPERATOR to use for comparison. OPERATORs include:
- = - Equal
- < - Less than
- > - Greater than
- <= - Less than or equal
- >= - Greater than or equal
- <> - Not equal
- LIKE - Allows the wildcard operator, %, to be used to select items that are a partial match. An
select city, state from towntable where state LIKE 'north%';
This allows selection of all towns in states that begin with the word "north" allowing states like
North Dakota and North Carolina to be selected.
The GROUP BY Clause
This "GROUP BY" clause allows multiple columns to be grouped so aggregate functions (listed
below) may be performed on multiple columns with one command.
Aggregate function keywords:
- AVG - Get the average of a specified column.
- COUNT - Get the quantity of values in the column.
- MAX - Return the maximum value in a specified column.
- MIN - Return the minimum value in a specified column.
- SUM - Return the sum of all numeric values in the specified column.
SELECT MAX(population)
FROM citylist;
WHERE state = 'Indiana';
Example using the GROUP BY clause which gets the smallest population of each city in every state:
SELECT MIN(population)
FROM citylist;
GROUP BY state;
The HAVING Clause
Allows selection of set test criteria on rows. You can display average size of towns whose population is less
than 100.
The ORDER BY Clause
This clause lets results be displayed in ascending or descending order. Keywords:
- ASC - Ascending order.
- DESC - Descending order.
- Other Keywords
- ALL - Used to select all records.
- DISTINCT - Used to select unique records. Only unique values are returned.
- Example:- SELECT city, state FROM towntable WHERE population > '100000';
The insert statement is used to insert a new row in a table. This statement is used to insert a row of data in a
table. All inserted values are enclosed using single quote strings. The syntax of this command is:
insert into tablename
(column1name,column2name...columnxname)
values (value1,value2...valuex);
Example
insert into citylist
(name, state, population, zipcode)
values ('Argos', 'Indiana', '89', '46501');
or
insert into citylist
values ('Argos', 'Indiana', '89', '46501');
4. UPDATE
The UPDATE statement is used to update existing records in a table. This command is used to make
changes to records in tables. Syntax:
update tablename
set columnname = newvalue [,columnxname = newvaluex...]
where columnname OPERATOR value [and|or columnnamex OPERATOR valuex];
The OPERATOR is one of the conditions listed on the Select Command page.
OPERATORs include:
- = - Equal
- < - Less than
- > - Greater than
- <= - Less than or equal
- >= - Greater than or equal
- <> - Not equal
- LIKE - Allows the wildcard operator, %, to be used to select items that are a partial match. An
- example is:
This allows selection of all towns in states that begin with the word "north" allowing states like
North Dakota and North Carolina to be selected.
Example
update citylist
set population = population+1
where name = 'Argos' and
state = 'Indiana';
5. DELETE
The DELETE statement is used to delete rows in a table. This command is used to delete rows or records in
a table. The syntax of this command is:
delete from "tablename"
where columnname OPERATOR value [and|or
columnnamex OPERATOR valuex];
Without the where clause, all records in the table will be deleted.
Example
delete from citylist
where name = 'Argos' and state = 'Indiana';
6. DROP
The DROP TABLE statement is used to delete a table. The DROP DATABASE statement is used to delete
a database. Used to remove an entire table from the database. Syntax:
drop table tablename
Example
drop table citylist;
7. ALTER
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. To add a
column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
To delete a column in a table, use the following syntax
ALTER TABLE table_name
DROP COLUMN column_name;
To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
8. RENAME
We can rename any table by using RENAME sql command. The data will not be lost. Only the table name
will be changed to new name. Here is the command to change the name of a table.
Syntax :- RENAME Source table name to destination table name;
Example:- rename emp to employee;