Home | Contact Us | Search
 
 
  Microsoft Office
  MS Word
  MS Excel
  MS Access
  MS Powerpoint
  Common Features
  Microsoft Windows
  Desktop Basics
  Windows 95
  Windows 98
  Windows XP
  Windows NT
  Network Issues
  Internet Basics
  Netscape Communicator
  Internet Explorer
  E-mail
  Web Publishing
  Developer Corners
  Visual Basic
  SQL Server
  Visual Interdev (ASP)
  HTML
 
SQL Server/Transact SQL
Introduction SQL Query SQL Stored Procedures SQL Triggers SQL Joins
SQL Views   Indexes BCP (Bulk Copy Program) Table Commands SQL Operators

Sorry the information on this page is outdated as it relates to older versions of SQL Server.

We have new content and free videos at this location, http://www.learningcomputer.com/videotraining.html Please note that rest of the SQL Server videos can be bought from this page Buy Tutorials Please send any comments or suggestion to  kashi@learningcomputer.com

  1. INSERT INTO

  2. DELETE..FROM

  3. SELECT..FROM

  4. UPDATE

  5. SELECT MAX ..FROM

  6. SELECT COUNT .. FROM

  7. SELECT DISTINCT..FROM

  8. SELECT..FROM..WHERE

  9. SELECT..FROM..ORDER by



  INSERT INTO:

This SQL command is used to add a new row to a table or a view. The syntax is

INSERT [INTO] {table_name | view_name} [(column_list)]{DEFAULT VALUES | values_list |
select_statement}

where

INTO:Is an optional keyword.

table_name | view_name = [[database_name.]owner.]{table_name | view_name}

Specifies the name of the table or view used in the INSERT statement. If the table or view is not in the current
database, use a fully qualified table_name or view_name (database_name.owner.object_name).

column_list:Lists one or more columns to which data is to be added. The columns can be listed in any order, but the incoming data (whether in a values_clause or a select_statement) must be in the same order as the
columns.

DEFAULT VALUES

Inserts the default values for all columns. If the column has the IDENTITY property or the timestamp datatype, the next appropriate value will be inserted. If a default for the column does not exist and the column allows NULLs, NULL will be inserted. If any column of the table does not have a default or does not allow NULL, an error will be returned and the INSERT statement rejected.

values_list = VALUES (DEFAULT | constant_expression [, DEFAULT | constant_expression]...)

VALUES:Is a required keyword used to introduce the list of values for each column in the column_list or table.

select_statement:Is a standard SELECT statement used to retrieve the values to be inserted from an existing
table.

Remarks

INSERT adds new rows only. To modify column values in existing rows, use UPDATE.

When inserting rows, these rules apply:

Inserting an empty string (' ') into a varchar or text column inserts a single space. All char columns are
right-padded to the defined length.

All trailing spaces are removed from data inserted into varchar columns, except in strings that contain only
spaces. These strings are truncated to a single space.

If an INSERT statement violates a constraint, default, or rule, or if it is the wrong datatype, the statement fails
and SQL Server displays an error message.

Constraints are defined with either the CREATE TABLE or ALTER TABLE statement. Defaults are created
with the CREATE DEFAULT statement and rules are created with the CREATE RULE statement.

Inserting a null value into a text or image column does not create a valid text pointer, nor does it preallocate a
2K text page. For details on inserting text and image data, see the text and image Manipulation topic.

An INSERT statement must follow the rules for a batch. For details, see the Batches topic.

When you specify values for only some of the columns in the column_list, one of three things can happen to the
columns that have no values:

A default value is entered if the column has a DEFAULT constraint, if a default is bound to the column, or a
default is bound to the underlying user-defined datatype.

NULL is entered if the column allows NULLs and no default value exists for the column.

An error message is displayed and the row is rejected if the column is defined as NOT NULL and no default
exists.

Examples

1.    This example shows an insert into the titles table in the pubs database. Only the values for the columns
listed in the column_list are shown in the values_list.

INSERT titles(title_id, title, type, pub_id, notes, pubdate)

VALUES ('BU1237', 'Get Going!', 'business', '1389', 'great', '06/18/86')

2.    This example inserts all rows from the authors table (for authors only in San Francisco) into the newauthors
table.

INSERT INTO newauthors

SELECT *

FROM authors

WHERE city = 'San Francisco'

Bitmap Image


  DELETE FROM:

This Transact SQL statement removes rows from a table. The syntax for the command is:

DELETE [FROM] {table_name | view_name} [WHERE clause]

where

table_name | view_name = [[database_name.]owner.]{table_name | view_name}

Specifies the table or view used in the DELETE statement.

WHERE clause = WHERE {search_conditions | CURRENT OF cursor_name}

Is used to perform a searched delete (using search_conditions) or a positioned delete (using CURRENT OF
cursor_name). When no WHERE clause is given in the DELETE statement, all rows in the table are removed.
The table itself, along with its indexes, constraints, and so on, remains in the database.

EXAMPLE:

delete from mrm_report_post
where remport_num>26000
and report_num<27000

Remarks

The TRUNCATE TABLE statement and the DELETE statement without a WHERE clause are functionally
equivalent, but TRUNCATE TABLE is faster. The DELETE statement removes rows one at a time and logs
each row deletion; the TRUNCATE TABLE statement deletes all rows by logging only the page deallocations.
Both DELETE and TRUNCATE TABLE reclaim the space occupied by the data and its associated indexes.


Bitmap Image


  SELECT .. FROM:

SELECT is one of most commonly used DML(Data Manipulation Language) commands. It is used to retrieve
data from the database. The general form for a SELECT statement, retrieving all of the rows in the table is:

SELECT ColumnName, ColumnName, ...
FROM TableName;

Lets us look at the following table (tbl_employee) including five fields: Social_Security , Last_Name,
First_Name, Address and Zip_Code:

Social_Security Last_ Name First_Name Address Zip_Code
476-02-3475 Mitchel John 1223 West Palm Beach Rd 85023
376-76-9083 Spencer Teri 2349 S. 76th Street #102 53219
733-05-3598 James Taylor 23 N. Atlantic Blvd 76215
387-41-1189 Pewinsky Lewis 675 E Indian School Rd 85023
498-32-9089 James Sue 3567 E Tatum Blvd 85032


If you want to retrive all the information in the table,  use the following statement:

SELECT * FROM tbl_employee

The result from the command is

Social_Security Last_ Name First_Name Address Zip_Code
476-02-3475 Mitchel John 1223 West Palm Beach Rd 85023
376-76-9083 Spencer Teri 2349 S. 76th Street #102 53219
733-05-3598 James Taylor 23 N. Atlantic Blvd 76215
387-41-1189 Pewinsky Lewis 675 E Indian School Rd 85023
498-32-9089 James Sue 3567 E Tatum Blvd 85032


If you want to limit the number of fields from the table use the following command

SELECT First_Name, Last_Name, Zip_Code  
FROM tbl_employee

The following is the results of your query of the database:

Last_Name First_Name Zip_Code
Mitchel John 85023
Spencer Teri 53219
James Taylor 76215
Pewinsky Lewis 85023
James Sue 85032


To explain what you just did, you asked for the all of data in the table tbl_employee, and specifically, you asked
for the fields called First Name, Last Name, Zip Coe. Note that column names and table names do not have
spaces.

Bitmap Image


  UPDATE:

This SQL command changes data in existing rows, either by adding new data or by modifying existing data.
The syntax for this command is

UPDATE {table_name | view_name}

SET [{table_name | view_name}] {column_list | variable_list}

... [, {column_listN | variable_listN | variable_and_column_listN}]]

[WHERE clause]

where

table_name | view_name = [[database_name.]owner.]{table_name | view_name}

Specifies the name of the table or view used in the UPDATE statement. If the table or view is not in the current
database, use a fully qualified table_name or view_name (database_name.owner.object_name).

SET:Is a required keyword used to introduce the list of column or variable clauses to be updated. When more
than one column name and value pair are listed, separate the names with commas.

column_list = column_name = {expression | DEFAULT | NULL}

variable_list =variable_name = {expression | NULL}

column_name:Specifies a column from the table (table_name) or view (view_name).

WHERE clause = WHERE {search_conditions | CURRENT OF cursor_name}

Is used to perform a searched update (using search_conditions) or a positioned update (using CURRENT OF
cursor_name). When no WHERE clause is given in the UPDATE statement, all rows in the table are modified.

Remarks

Use the UPDATE statement to change single rows, groups of rows, or all rows in a table. UPDATE specifies
which row(s) to change and provides the new data. When updating rows, these rules apply:

Updating a column with an empty string (' ') into a varchar or text column inserts a single space. All char
columns are right-padded to the defined length.

Modifying a text column with UPDATE initializes it, assigns a valid text pointer to it, and allocates at least one
2K data page (even if updating the column with NULL). For details about updating text or image fields, see the
Text and Image Manipulation topic.

Note The UPDATE statement is logged; if you are replacing or modifying large blocks of text or image data,
use the WRITETEXT or UPDATETEXT statement instead of the UPDATE statement. The WRITETEXT and
UPDATETEXT statements (by default) are not logged. For details, see the Text and Image Manipulation topic.

Examples

A. UPDATE Statement with Only the SET Clause

These examples show how all rows can be affected if a WHERE clause is eliminated from an UPDATE
statement.

In this example, if all the publishing houses in the publishers table move their head offices to Atlanta, Georgia,
this is how the publishers table could be updated:

UPDATE publishers

SET city = 'Atlanta', state = 'GA'

This example changes the date and time for a specific id number 13 on a Friday(spooky):

UPDATE tbl_time

SET char_day='Friday',char_date='08/13/99',char_time='12:00:00'

WHERE int_id=13

Bitmap Image


  SELECT MAX FROM

This command is used to find the maximum value in a data field (data column). It is useful when you are trying to update a database table and want to make sure the data has been transferred. Let us look at the following table(tbl_employee)

Employee_id Last_ Name First_Name Address Zip_Code
10010 Mitchel John 1223 West Palm Beach Rd 85023
10011 Spencer Teri 2349 S. 76th Street #102 53219
10012 James Taylor 23 N. Atlantic Blvd 76215
10013 Pewinsky Lewis 675 E Indian School Rd 85023
10014 James Sue 3567 E Tatum Blvd 85032


SELECT MAX(Employee_id) FROM tbl_employee
will return 10014

Bitmap Image


  SELECT COUNT FROM
This command is used to find the number of records in a data field (data column). It is useful when you are trying to update a database table and want to make sure the data has been transferred correctly. Let us look at the following table(tbl_employee)

Employee_id Last_ Name First_Name Address Zip_Code
10010 Mitchel John 1223 West Palm Beach Rd 85023
10011 Spencer Teri 2349 S. 76th Street #102 53219
10012 James Taylor 23 N. Atlantic Blvd 76215
10013 Pewinsky Lewis 675 E Indian School Rd 85023
10014 James Sue 3567 E Tatum Blvd 85032


SELECT COUNT (Employee_id) FROM tbl_employee
will return 5

Bitmap Image


  SELECT DISTINCT.. FROM:

SELECT DISTINCT..FROM is used to eliminate duplicate values in the table. The syntax is:

SELECT DISTINCT ColumnName, ColumnName, ...
FROM TableName;

Lets us look at the following table (tbl_employee) including five fields: Social_Security , Last_Name, First_Name, Address and Zip_Code:

Social_Security Last_ Name First_Name Address Zip_Code
476-02-3475 Mitchel John 1223 West Palm Beach Rd 85023
376-76-9083 Spencer Teri 2349 S. 76th Street #102 53219
733-05-3598 James Taylor 23 N. Atlantic Blvd 76215
387-41-1189 Pewinsky Lewis 675 E Indian School Rd 85023
498-32-9089 James Sue 3567 E Tatum Blvd 85032


If you want to retrive distinct employee names, use the following statement:

SELECT DISTINCT Last_Name, First_Name FROM tbl_employee

The result from the command is:

Last_Name First_Name Zip_Code
Mitchel John 85023
Spencer Teri 53219
James Taylor 76215
Pewinsky Lewis 85023


Notice how in the result set, only the first employee with the last name "James " is included. This command is useful if you are looking at duplicate information and trying to limit the final result

Bitmap Image


  SELECT..FROM..WHERE
If you want to limit the number of rows returned in a query, WHERE clause is used. The syntax for the command is

SELECT <col_name1>, ....
FROM <table_name1>, ....
WHERE <col_name><operator><value>, ........

For a definition of the operators, follow the link
Lets look at the following table tbl_employee again.

Social_Security Last_Name First_Name Address Zip_Code
476-02-3475 Mitchel John 1223 West Palm Beach Rd 85023
376-76-9083 Spencer Teri 2349 S. 76th Street #102 53219
733-05-3598 James Taylor 23 N. Atlantic Blvd 76215
387-41-1189 Pewinsky Lewis 675 E Indian School Rd 85023
498-32-9089 James Sue 3567 E Tatum Blvd 85032


If you want to limit the number of fields from the table use the following command

SELECT First_Name, Last_Name, Zip_Code
FROM tbl_employee
WHERE Zip_Code=85023

The following is the results of your query of the database:

Last_Name First_Name Zip_Code
Mitchel John 85023
Pewinsky Lewis 85023


As you can see, using the WHERE clause limited the number of rows from five to two.

Bitmap Image


  SELECT..FROM..WHERE..ORDER by
This DML command is used when the data needs to be sorted in some manner. Lets us look at the following table (tbl_employee) including five fields: Social_Security , Last_Name, First_Name, Address and Zip_Code:

Social_Security Last_ Name First_Name Address Zip_Code
476-02-3475 Mitchel John 1223 West Palm Beach Rd 85023
376-76-9083 Spencer Teri 2349 S. 76th Street #102 53219
733-05-3598 James Taylor 23 N. Atlantic Blvd 76215
387-41-1189 Pewinsky Lewis 675 E Indian School Rd 85023
498-32-9089 James Sue 3567 E Tatum Blvd 85032


If you want to sort the information in the table by Zip Codes, use the following statement:

SELECT * FROM tbl_employee
ORDER BY Zip_Code ASC

The result from the command is

Social_Security Last_ Name First_Name Address Zip_Code
376-76-9083 Spencer Teri 2349 S. 76th Street #102 53219
733-05-3598 James Taylor 23 N. Atlantic Blvd 76215
476-02-3475 Mitchel John 1223 West Palm Beach Rd 85023
387-41-1189 Pewinsky Lewis 675 E Indian School Rd 85023
498-32-9089 James Sue 3567 E Tatum Blvd 85032



Notice how the the field Zip_Code is sorted in ascending order.
ASC: Is used to sort the data in ascending order
DESC: Is used to sort the data in descending order

 
 
Copyright © 2008. learningcomputer. All rights reserved. || Powered by: Digital Fusion