SQL Table

SQL Table

SQL Table : In this blog post we are discussing important SQL Table commands. Using these SQL Table commands we can handle all SQL Table operations. Some of the SQL Table commands are CREATE , INSERT INTO, DELETE and DELETE.

 Create Table Command
Syntax:
CREATE TABLE tablename
(columnname datatype(size), columnnamedatatype(size));
As seen in the CREATE TABLE syntax, the SQL statement starts with ‘CREATE’ i.e. a verb, followed by ‘TABLE’ i.e. a noun and ‘<tablename>’ i.e. adjective.
The CREATE TABLE command includes a single clause for the column definition. Each column is a parameter for the clause and thus it is separated by comma.
Finally, the SQL statement with a semi colon.

Example:
Create a client_master table who structure is:
Column Name
Data Type
Size
Client_no
name
address1
address2
city
state
pincode
remarks
bal_due
varchar2
varchar2
varchar2
varchar2
varchar2
varchar2
number
varchar2
number
6
20
30
30
15
15
6
60
10,2
CREATE TABLE client_master
(client_no varchar2(6), name varchar2(20), address1 varchar2(30), address2 varchar2(30), city varchar2(15), state varchar2(15), pincode number(6), remarks varchar2(60),bal_due number(10,2));

INSERTION OF DATA INTO TABLES

Once a table is created the most natural thing to do is load this table with data to be manipulated later. The appropriate SQL sentence syntax is:
When inserting a single row of data into the table, the insert operation:
  1. Create a new row in the database table
  2. Loads the values passed into all the columns specified.
Syntax:
INSERT INTO tablename
(columnname, columnname)
VALUES (expression, expression);
Example:
Insert the following values into client_master table
Column Name             Values
Client_no                                           C02000,
name                                                   Sudhakar Pandey
address1                                            A-5, Jay Apartments,
address2                                            Service Road, Vindhy Valley,
city                                                        Mumbai
state                                                    Maharashtra
pincode                                              400057;
INSERT INTO client_master
(client_no, name, address1, address2, city, state, pincode)
VALUES (‘C02000’, ’Sudhakar Pandey’, ‘A-5,Jay Apartments’, ‘Service Road Vindhy Valley ’,
‘Mumbai’, ‘Maharashtra’, 400057);
Filtering Table Data: 
While viewing data from a table it is rare that all the data from the SQL table will be required each time. Hence, SQL must give us a method of filtering out data that is not required.
The ways of filtering table data will be:
  • Selected columns and all rows
  • Selected rows and all columns
  • Selected columns and selected rows

Select Columns and Select Rows from SQL table:

To view a specific data set from the table and also a select number of columns the syntax will be:
Syntax:
SELECT columnname,columnname
FROM tablename
WHERE search condition;
Example:
Retrieve the client_no, name from the table client_master where the value in the bal_due field is greater than 5000;
SELECT client_no, name
FROM client_master
WHERE bal_due>5000;

INSERTING DATA INTO A TABLE FROM ANOTHER TABLE :

In addition to inserting data one row at a time into a table, it is quite possible to populate a table with data that already exists in another table. The syntax for doing so is described under:
Syntax:
INSERT INTO tablename
SELECT columnname, columnname,
FROM tablename;
Example:
Insert records into table supplier_master from the table client_master;
INSERT INTO supplier_master
SELECT client_no, name, address1, address2,city, state, pincode, remarks
FROM client_master;

Insertion of a data set into a table from another SQL table:

Syntax:
INSERT INTO tablename
SELECT columnname, columnname
FROM tablename
WHERE column = expression;
Example:
Insert records into the table supplier_master from the table client_master where the field
client_no contains the value ‘C01001’;
INSERT INTO supplie_master
SELECT client_no, name, address1, address2,
city, pincode, state, remarks
FROM client_master
WHERE CLIENT_NO = ‘C01001’;

DELETE OPERATIONS :

The verb DELETE in SQL is used to remove rows from table. To remove
  • All the rows from a table
       Or
  • A select set of rows from a table

Removal Of All SQL Table Rows:

Syntax:
DELETE FROM tablename;
Example:
Delete all rows from the table client_master;
DELETE FROM client_master;
Syntax:
DELETE FROM tablename WHERE search condition;
Example:
Delete rows from the table client _master where the value in the bal_due field is less than
500;
DELETE FROM client_master WHERE bal_due < 500;

UPDATING THE CONDITION OF A SQL TABLE :

The UPDATE command is used to change or modify data values in a table. Update:
  • All the rows from a table
       Or
  • A select set of rows from a table

Updating of All SQL Table Rows:

Syntax:
UPDATE tablename
SET columnname = expression, columnname = expression;

Leave a Reply

Your email address will not be published.