DBMS basics table query
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:
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:
|>=||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 empinfo where first = 'Eric';
This will only select rows where the first name equals 'Eric' exactly.
|Sample Table: empinfo|
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.
create table employee (first varchar(15), last varchar(20), age number(3), address varchar(30), city varchar(20), state varchar(20));
To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you seperate each column definition with a comma. All SQL statements should end with a ";".