- CREATE Table :
The
SQL CREATE TABLE statement is used to create a new table.
Syntax:
Basic syntax of
CREATE TABLE statement is as follows:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
CREATE TABLE is
the keyword telling the database system what you want to do. In this case, you
want to create a new table. The unique name or identifier for the table follows
the CREATE TABLE statement.
Then in brackets
comes the list defining each column in the table and what sort of data type it
is. The syntax becomes clearer with an example below.
A copy of an
existing table can be created using a combination of the CREATE TABLE statement
and the SELECT statement. You can check complete details at Create
Table Using another Table.
Example:
Following is an
example, which creates a CUSTOMERS table with ID as primary key and NOT NULL
are the constraints showing that these fields can not be NULL while creating
records in this table:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
You can verify
if your table has been created successfully by looking at the message displayed
by the SQL server, otherwise you can use DESC command
as follows:
SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | int(11) | NO | PRI | | |
| NAME | varchar(20) | NO | | | |
| AGE | int(11) | NO | | | |
| ADDRESS | char(25) | YES | | NULL | |
| SALARY | decimal(18,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2. INSERT Query
The SQL INSERT
INTO Statement
is used to add new rows of data to a table in the database.
Syntax:
There are two
basic syntaxes of INSERT INTO statement as follows:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
Here, column1,
column2,...columnN are the names of the columns in the table into which you
want to insert data.
You may not need
to specify the column(s) name in the SQL query if you are adding values for all
the columns of the table. But make sure the order of the values is in the same
order as the columns in the table. The SQL INSERT INTO syntax would be as
follows:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
Example:
Following
statements would create six records in CUSTOMERS table:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
Ø You can create a
record in CUSTOMERS table using second syntax as follows:
INSERT INTO CUSTOMERS
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
All the above
statements would produce the following records in CUSTOMERS table:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Populate one table using another table:
You can populate
data into a table through select statement over another table provided another
table has a set of fields, which are required to populate first table. Here is
the syntax:
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE
condition];
3. SELECT Statement
SQL SELECT statement is used to fetch the data
from a database table which returns data in the form of result table. These
result tables are called result-sets.
Syntax:
The basic syntax
of SELECT statement is as follows:
SELECT column1, column2, columnN FROM table_name;
Here, column1,
column2...are the fields of a table whose values you want to fetch. If you want
to fetch all the fields available in the field, then you can use the following
syntax:
SELECT * FROM table_name;
Example:
Consider the
CUSTOMERS table having the following records:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Following is an
example, which would fetch ID, Name and Salary fields of the customers
available in CUSTOMERS table:
SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;
This would
produce the following result:
+----+----------+----------+
| ID | NAME | SALARY |
+----+----------+----------+
| 1 | Ramesh | 2000.00 |
| 2 | Khilan | 1500.00 |
| 3 | kaushik | 2000.00 |
| 4 | Chaitali | 6500.00 |
| 5 | Hardik | 8500.00 |
| 6 | Komal | 4500.00 |
| 7 | Muffy | 10000.00 |
+----+----------+----------+
If you want to
fetch all the fields of CUSTOMERS table, then use the following query:
SQL> SELECT * FROM CUSTOMERS;
This would
produce the following result:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
For Oracle online training classes please conatct : training@virtualnuggets.com
