Database SQL Commands

Insert Command

Syntax to insert value: Insert Into tablename (Col1, Col2……) values (Val1,Val2……);

Example:
In Mysql:
Insert into authors values (101, 'Kiran', '2001-10-10 ', 'M.Sc', 'kiran@fle.com', 123456);

In Oracle:
Insert into authors values (102, 'vas', '10-oct-2001', 'M.Sc', 'vas@fle.com', 123456);

Authors Table With Data

• Rectify It : IRectify It :nsert into authors values (103, 'sd', 'M.Sc' 12345);

• Rectified : Insert Into authors (aid, aname, quali, phonenumber) VALUES (103, 'sd', 'M.Sc', 12345);

Notes:

1. Char & date type values must be enclosed in single quotation marks.

2. Whenever you are inserting date type values, you should make sure that the correct date format is used.

3. Whenever you are not providing values for all the columns, you must specify the first column name and add that table name.

4. Whenever you are not supplying values for a column, a null will be placed in that column.

Question: What will happen when you give the following command:
INSERT INTO authors (phonenumber, aname, aid) VALUES (1234, 'sd',104);

Answer: It will execute successfully.

Update Command

Syntax to insert value: Update table-name set col1 = Val1, Col2 = val2 Where condition;

Example:

Single Update:
UPDATE authors SET email = 'kiran@fle.com', phonenumber = 999 WHERE aid = 101;

Multiple Update:
UPDATE authors SET phonenumber = 999;

Note:

1. Using one update state, you can update zero or more records.

2. ROLLBACK - insert, delete and update not for create and drop

Delete Command

Syntax to insert value: DELETE FROM tablename WHERE CONDITIONS;

Example:

Single Update:
DELETE FROM authors WHERE aid = 103;

Multiple Update:
DELETE FROM authors; (DELETES ALL DATA IN AUTHOR’S TABLE)

Note:

Using one delete statement, you can delete zero or more records.

Drop Command

Syntax: DROP TABLE tablename;

Example: DROP TABLE authors;

Truncate the Table

Syntax: TRUNCATE TABLE tablename;

Example: TRUNCATE TABLE authors; DELETE FROM authors;

1. Both the statements will give you an empty tab at the end.

2. The Delete statement deletes all records one by one, whereas the Truncate state does the table simply and creates a new table.

3. Delete takes more time than the Truncate state.

4. Delete operation can be cancelled i.e. it can be called back, whereas Truncate operations cannot be callback.

5. DELETE deletes the entire table with its structure whereas TRUNCATE deletes only the data that is inside that particular table.

Syntax :
Select (or) cal1, col2 _ _ _ _ _ _ _ _ _ tab1, tab2
Where condition Order by col-name Group by col-name Having condition;

Example