Step-2- in SQl-server2000

Page 1 of 1

[ 1 post ]

Author

Question

msi_fci

Question subject: Step-2- in SQl-server2000

Posted: Sat Apr 07, 2007 6:55 pm

Joined: Thu Apr 05, 2007 9:57 pm
Posts: 17
Has thanked: 0 time
Have thanks: 0 time

DML Statements(On One Table)

1. Inserting New Rows

Code:

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

Example 1:

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:

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

Code:

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

Example 1:

Increase the SALARY by 5 percent

Code:

UPDATE STAFF
SET STAFF_SALARY=STAFF_SALARY * 1.05;

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

Code:

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

3. Deleting rows from a table

Code:

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

Example 1:

Delete all rows of table STAFF (DonØ·Â·Ø¢Â£Ø·Â¢Ø¢Â¢Ø·Â£Ø¢Â¢Ø£Â¢Ã¢â€šÂ¬Ø¹â€˜Ø·Â¢Ø¢Â¬Ø·Â£Ø¢Â¢Ø£Â¢Ã¢â€šÂ¬Ã¢â‚¬Ú†Ø·Â¢Ø¢Â¢t try it)

Code:

DELETE FROM STAFF;

Example 2:

Delete the STAFF with id 2

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

Code:

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

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

Code:

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

4. Simple Select Statements

Code:

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

Example 1:

Select all information of all STAFF MEMBERS

Code:

Select * from STAFF;

Example 2:

Select STAFF id and STAFF name of all STAFF MEMBERS

Code:

SELECT STAFF_ID , STAFF_NAME
FROM STAFF;

Example 3:

Get all STAFF MEMBERS where their names contain the letter H

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

Code:

SELECT DISTINCT STAFF_NAME
From STAFF;

Page 1 of 1

[ 1 post ]

Similar topics
Topic title	Forum	Author	Comments
unit step and impulse function L.T	General Discussion	loomy	0
(MS Press) Microsoft Visual CSharp 2008 - Step By Step	offers	Casper	0
Cisco - Press - Configuring CallManager and Unity A Step-by-	offers	Casper	0
TCP IP First Step	offers	Casper	0
Cisco Press - Routing First-Step 2004	offers	Casper	0

Codemiles.com » Database Forums » SQL Server

All times are UTC [ DST ]

Users browsing similar posts

Users browsing this forum: No registered users and 1 guest

Jump to:

Previous Question | Next Question