Total members 11889 |It is currently Fri Mar 29, 2024 6:11 am Login / Join Codemiles

Java

C/C++

PHP

C#

HTML

CSS

ASP

Javascript

JQuery

AJAX

XSD

Python

Matlab

R Scripts

Weka





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;




_________________
Recommend my post if you found it helpful.


Author:
Newbie
User avatar Posts: 15
Have thanks: 0 time
Post new topic Reply to topic  [ 1 post ] 

  Related Posts  to : DML Statements, insert row, delete row
 Types of Databases and statements, Create, Alter, delete     -  
 Asp.net insert update delete Examples     -  
 java statements     -  
 For statement without all three statements     -  
 translate XQuery to SQL statements     -  
 DDL Statements-Adding Constraints-CREATE-ALTER-TABLE     -  
 insert query example     -  
 insert a namespace in a tag with jaxb     -  
 Insert using native query     -  
 Image Insert in Excel File Using POI     -  









Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
All copyrights reserved to codemiles.com 2007-2011
mileX v1.0 designed by codemiles team
Codemiles.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com