DML Statements, insert row, delete row

DML Statements(On One Table)

1. Inserting New Rows

sql code

INSERT INTO <tablename> [<column list>]
VALUES (<expression>{[,<expression>]})

Example 1:

sql code

Insert new STAFF memeber into the STAFF table with the following properties:
    STAFF id=1 and STAFF SALARY= 2000

You can specify values to be inserted without specifying the column names because SQL assumes that the order in which the values are entered is the same as the default sequence of the columns.

Example 2:

sql code

INSERT INTO STAFF VALUES (1,'ALI',2000,NULL); 
INSERT INTO STAFF VALUES (2,'EHAB',1000,NULL); 
INSERT INTO STAFF VALUES (3,'MARWA',3000,NULL); 
INSERT INTO STAFF VALUES (4,'ALI',4000,NULL);

2. Updating Values in Rows

sql code

UPDATE <tablename>
SET <column name>=<expression>[,<column name>=<expression>]
[WHERE <Condition>]

Example 1:

Increase the SALARY by 5 percent

sql code

UPDATE STAFF
SET STAFF_SALARY=STAFF_SALARY * 1.05;

Example 2: (Using Where Clause)

Change the name of the STAFF with id 1 to be AHMED

sql code

UPDATE STAFF 
SET STAFF_NAME='AHMED'
WHERE STAFF_ID=1;

Example 3: (Update more than one cloumn with a condition)

Change the name of the STAFF with id 1 back to "ALI" and let his salary be 3000

sql code

UPDATE STAFF
SET STAFF_NAME='ALI',
STAFF_SALARY = 3000
WHERE STAFF_ID = 1;

3. Deleting rows from a table

sql code

DELETE
FROM <table name>
[WHERE <Condition>]

Example 1:

Delete all rows of table STAFF (Don't try it)

sql code

DELETE FROM STAFF;

Example 2:

Delete the STAFF with id 2
sql code

DELETE FROM STAFF
WHERE STAFF_ID=2;

Example 3:
(Using more than one condition)

Delete the STAFF that has an id 1 or 2

sql code

DELETE from STAFF
WHERE STAFF_ID=1
OR STAFF_ID=2;

BETTER STATEMENT THAT DOES THE SAME JOB OF THE PREVIOUS ONE :

sql code

DELETE from STAFF
WHERE STAFF_ID IN (1,2);

4. Simple Select Statements

sql code

SELECT <Column list>
FROM <table names>
[WHERE <Condition>]

Example 1:

Select all information of all STAFF MEMBERS

sql code

Select * from STAFF;

Example 2:

Select STAFF id and STAFF name of all STAFF MEMBERS

sql code

SELECT STAFF_ID , STAFF_NAME
FROM STAFF;

Example 3:

Get all STAFF MEMBERS where their names contain the letter H

sql code

SELECT *
FROM STAFF
WHERE STAFF_NAME LIKE '%H%';

Search for other operators such as LIKE to be used in the where clause and see example(s) on them?

Example 4: (Using DISTINCT Keyword)

Get all unique names from the table STAFF

To feel it you must have duplicate names of staff members
sql code

SELECT DISTINCT STAFF_NAME
From STAFF;