SQL Statements

SQL Statements

1.SQL INSERT Statement

The INSERT Statement is used to add new rows of data to a table.

We can insert data to a table in two ways,

1) Inserting the data directly to a table.

Syntax for SQL INSERT is:

INSERT INTO TABLE_NAME

[ (col1, col2, col3,...colN)]
VALUES (value1, value2, value3,...valueN);

    col1, col2,...colN -- the names of the columns in the table into which you want to insert data.

While inserting a row, if you are adding value for all the columns of the table you need not specify the column(s) name in the sql query. But you need to make sure the order of the values is in the same order as the columns in the table. The sql insert query will be as follows

INSERT INTO TABLE_NAME
VALUES (value1, value2, value3,...valueN);

For Example: If you want to insert a row to the employee table, the query would be like,

INSERT INTO employee (id, name, dept, age, salary location) VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);

NOTE:When adding a row, only the characters or date values should be enclosed with single quotes.

If you are inserting data to all the columns, the column names can be omitted. The above insert statement can also be written as,

INSERT INTO employee
VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);

Inserting data to a table through a select statement.
Syntax for SQL INSERT is:

INSERT INTO table_name
[(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM table_name [WHERE condition];

For Example: To insert a row into the employee table from a temporary table, the sql insert query would be like,

INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_name, dept, age, salary, location FROM temp_employee;

If you are inserting data to all the columns, the above insert statement can also be written as,

INSERT INTO employee
SELECT * FROM temp_employee;

NOTE:We have assumed the temp_employee table has columns emp_id, emp_name, dept, age, salary, location in the above given order and the same datatype.

IMPORTANT NOTE:

1) When adding a new row, you should ensure the datatype of the value and the column matches

2) You follow the integrity constraints, if any, defined for the table.


2.SQL SELECT Statement:

The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.
Syntax of SQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

i.table-name is the name of the table from which the information is retrieved.
ii.column_list includes one or more columns from which data is retrieved.
iii.The code within the brackets is optional.

3.SQL UPDATE Statement:


The SQL UPDATE Query is used to modify the existing records in a table.

You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be affected.
Syntax:

The basic syntax of UPDATE query with WHERE clause is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using AND or OR operators.
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 update ADDRESS for a customer whose ID is 6:

SQL> UPDATE CUSTOMERS
SET ADDRESS = 'Pune'
WHERE ID = 6;

Now, CUSTOMERS table would have 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 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

If you want to modify all ADDRESS and SALARY column values in CUSTOMERS table, you do not need to use WHERE clause and UPDATE query would be as follows:

SQL> UPDATE CUSTOMERS
SET ADDRESS = 'Pune', SALARY = 1000.00;

Now, CUSTOMERS table would have the following records:

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  1 | Ramesh   |  32 | Pune    | 1000.00 |
|  2 | Khilan   |  25 | Pune    | 1000.00 |
|  3 | kaushik  |  23 | Pune    | 1000.00 |
|  4 | Chaitali |  25 | Pune    | 1000.00 |
|  5 | Hardik   |  27 | Pune    | 1000.00 |
|  6 | Komal    |  22 | Pune    | 1000.00 |
|  7 | Muffy    |  24 | Pune    | 1000.00 |
+----+----------+-----+---------+---------+

4.SQL DELETE Statement

The SQL DELETE Query is used to delete the existing records from a table.

You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.
Syntax:

The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.
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 DELETE a customer, whose ID is 6:

SQL> DELETE FROM CUSTOMERS
WHERE ID = 6;

Now, CUSTOMERS table would have 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 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

If you want to DELETE all the records from CUSTOMERS table, you do not need to use WHERE clause and DELETE query would be as follows:

SQL> DELETE FROM CUSTOMERS;

0 comments:

Post a Comment