SQL Basics

Last update: 18/01/2003This page contains the essential commands to manage databases using SQL (Structured Query Language). I use it as a refference for myself. I don't pretend to be an SQL-expert. Others may use this reference freely. If you have any suggestions or comments please e-mail me!
Some commands depend on the type of server used. I experiment with four servers, Microsoft SQL Server, Borland Interbase Server, MySQL Server en Sybase SQL Anywhere. If a command is specific to one server I indicate this with an icon.

1.  indicates that the command works with MS SQL Server.
2.  indicates the command works with Borland InterBase Server.
3. indicates that the command works with MySQL Server.
4. indicates that the command works with Sybase SQL Anywhere.

The commands are written in bold, italicised text (databasenames, fieldnames, values, ...) is up to you to fill in. Note also that I speak of fields and records instaid off rows and coloms. In MS SQL Server manuals I see they use rows and coloms, but I prefer to use fields and records, probably because that was the way in dBase and MS Access, which I used for years.

---

1. List all the databases on the server:

sp_databases 
show databases 

---

2. Select a database to use:

use databasename  

---

3. List tables in the database:

show tables 
sp_help There is another, more complicated, command to retrieve the names of the tables in the database, but where you get a list of ALL tables, including system tables using sp_help, you can specify to view only user defined tables
select * from SYSOBJECTS 
where TYPE = 'U'
order by NAME If you want to see the tables of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Click "OK" and you see the tables of the active database. Using ISQL you can use show tables
 To see the tables in Sybase press functionkey F7.

---

4. List fields in a table:

describe tabelname 
sp_help tablename  To see the fields of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Type the name of the database in the "Object name"-textfield. Click "OK" and you see the fields of the specified table. Using ISQL you can use show table tablename
 To see the fields in a Sybase table press functionkey F7, select the table and click "Columns".

---

5. Create a database:

create database databasename 
on
(name='databasename_dat',
filename='c:\mssql7\data\databasename_dat.mdf',
size=2mb,
filegrowth=1mb)
log on
(name='databasename_log',
filename='c\mssql7\data\databasename_log.ldf',
size=2mb,
filegrowth=1mb)

---

6. Create a table in the database en store values in it:

create table tablename    
(fieldname1 datatype1, fieldname2 datatype2, ...)
insert into tablename
values(fieldvalue1, fieldvalue2, ...)

---

7. Constraints:

Primary key: constraint constraintname primary key (non)clustered (fieldname1, fieldname2, ...)

Foreign key: constraint constraintname foreign key (fieldname) references tablename(fieldname)

No dulicates: constraint constraintname unique nonclustered (fieldname)

Check: Add values to the fieldnamelist in the 'create table'-command:
(fieldname1 datatype1 check (fieldname1 in ('value1', 'value2', ...)), fieldname2 datatype2, ...)

Default value: Add default value to the fieldlist in the 'create table'-command:
(fieldname1 datatype1, fieldname2 datatype2 default 'value', fieldname3 datatype3, ...)

---

8. Select all records from table:

select * from tablename    
order by fieldnameNote that this command could take some time and put a lot of stress on the processor and memory of the server if you try to view a large database this way. On my server (Pentium MMX 233 Mhz - 64 Mb memory) it took 1:25 minutes to retrieve 25000 records from a database and it gave warnigs of shortage in virtual memory, so please don't try this on a production database (;-). Usualy it is better to select fields and conditions to limit the amount of records and the stress on the server.

---

9. Select set of records from table:

select fieldname1, fieldname2, ... from tablename    
where fieldname = "condition"
order by fieldname
Comparison Operators that can be used in the condition are:

=, !=, <>, <, >, <=, >= and LIKE.

With LIKE you can specify e.g. all the names beginning with S as 'name LIKE "s%"'.
You can also use boolean operators to specify more then one condition (OR, AND, NOT, BETWEEN, IN). With 'BETWEEN' you give a minimum and maximum value to the condition, with 'IN' you can give a list of values.

Example:

select Firstname, Lastname, Department from Employees
where Lastname LIKE "Van%" AND
Badge BETWEEN 121990 and 141990 AND
Department IN ("Sales", "Logistics")
order by Lastname
This statement retrieves all the employees where Lastname begins with "Van", whose badgenumbers are between 121990 and 141990, who work at the Sales- or Logisticsdepartment, and displays their Lastname, Firstname and Dapartment ordered by Lastname.

Note theat the 'ORDER BY' statemant can have more then one fieldname and can also take 'asc' or 'desc' as last argument (for ascending or descnding order).

---

10. Retrieve unique values

select distinct fieldname from table    

---

11. Add records to a table:

insert into tablename (fieldname1, fieldname2, ...)    
values ('value1', 'value2', ...)
If you leave out the fieldnames then the values must match the number of fields in the table. If you only want to add a value to some fields you have to specify them and the values must mach the number of specified fields. The unspecified fields are filled with NULL or the default constraint defined in the table. You could concider to specify defaults like "unknown" or "N/A" to avoid a NULL-value in the field.

---

12. Updating records in a table;

update tablename    
set fieldname1='value1', fieldname2='value2', ...
where fieldname3 = condition
If you ommit the 'where'-statement the update will be performed on ALL the records in the table!

---

13. Deleting a record from a table

delete from tablename    
where fieldname=condition
Be arefull in using this command!! Ommitting the 'where'-statement will erae ALL the records in the table and leave you with an empty table!!!

---

14. Adding a field to the records in a table:

alter table tablename    
add fieldname datatype
The new field is filled with NULL and can be filled using the update-command after adding the field.

---

15. Changing the width of a field

alter table tablename 
alter column fieldname newdatatypealter table tablename   
modify fieldname newdatatype

---

16. Removing field from the records

alter table tablename 
drop column fieldnamealter table tablename   
drop fieldname

---

17. Combine two querries:

select fieldname from tablename
union
select fieldname2 from tablename2
order by fieldname
This union will remove all duplicates. To retain duplicates you have to use 'union all'

---

18. Basic SQL-functions:

select avg(fieldname) from tablename
Returns the arithmeticaverage of the fields.

select count(*) from tablename
where fieldname=condition
Returns the number of records that match the condition.

select max(fieldname) from tablename
Returns the largest value of fieldname.

select min(fieldname) from tablename
Returns the smallest value of fieldname.

select sum(fieldname) from tablename
Returns the summation value of fieldname.

select convert(newdatatype, fieldname) from tablename
converts one datatype into another.

---

19. String functions

ASCII returns ASCII code of leftmost character of a characterexpression.

CHAR converts an ASCII code to a character.

SOUNDEX returns four-digit code used to compare two strings with DIFFERENCE.

DIFFERENCE returns difference between values of two character expressions returned by SOUNDEX.

LEFT returns part of character string, starting at number of character from the left specified in the argument.

LOWER converts uppercase strings to lowercase.

UPPER converts lowercase strings to uppercase.

LTRIM removes leading spaces from a string.

RTRIM removes trailing spaces from a string.

CHARINDEX returns starting position of specified character expression in a string.

PATINDEX rerurns starting position of first occurence of a substring in a string.

REPLICATE returns multiple sets of characters specified in the first argument. The second argument specifies number of sets. (eg. select replicate ('a', 5) returns 'aaaa')

REVERSE returns reverse order of a string of characters.

RIGHT returns part of character string, starting at number of character from the right specified in the argument.

SPACE returns a string of spaces, length specified in argument.

STR converts numeric data to character data.

STUFF inserts a string into another string.

SUBSTRING returns a part of a string (arguments are startpoint and length).

+ (concatenetion) concatenates two or more strings.

---

20. Arithmetic functions:

ACOS, ASIN, ATAN, ATAN2, COS, COT, SIN, TAN, DEGREES, RADIANS, CEILING, FLOOR, EXP, LOG, LOG10, PI(), POWER, ABS, RAND, ROUND, SIGN, SQRT.

---

21. TEXT and IMAGE functions:

SET TEXTSIZE specifies number of bytes displayed for data stored as TEXT or as IMAGE.

TEXTPTR returns pointer to first database page of stoed text.

READTEXT extracts substring from data stored as TEXT or as IMAGE.

TEXTVALID check validity of textpointer.

---

22. Date functions:

DATENAME returns specified part of date as characterstring.

DATEPART returns specified part of date as integer value.

GETDATE returns current date and time.

DATEADD returns value of the date with an additional date interval added.

DATEDIFF returns difference between parts of two specified dates.

---

23. Views:

Note that a view under SQL is the same as a query you create with Access.

23.1 Create views:

create view viewname as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = conditionYou can't edit a view using SQL. You can use the Enterprise Manager of MS SQL to edit a view or you can delete a view and recreate it with the same name.
You can use alternative names for the columns in a view:
create view viewname (col1, col2, col3, ...)as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition

23.2 Display definition of a view:

sp_helptext viewnameTo prevent the possibility to view the definition of a view you can use encryption:
create view viewname with encryption as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition

23.3 Display associations of a view:

sp_depends viewname

23.4 Delete a view from the database:

drop view viewname1, viewname2, ...

23.5 Insert records through a view:

insert into viewname
values ('value1', 'value2', ...)You can insert rows through the view that DON'T match the WHERE-statement inthe view definition, but then you can't retrieve the new row with the view. If you want to prevent this you can use the check option:
create view viewname as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition with check option

23.6 Delete records through a view:

delete from viewname where fieldname = conditionYou can't delete records through a view that don't match the WHERE-statement in the view definition.

23.7 Updating records through a view:

update viewname set fieldname='value' where fieldname=conditionYou can update a record through a view so that it doesn't match the WHERE-statement anymore. You can't update a view if the updated columns belong to different tables.

---

24. Indexes:

24.1 Simple index:

create index index_name
on table_name (fieldname)

24.2 Unique index:

create unique index index_name
on table_name (fieldname)This kind of index enforces integrity of the table by disallowing duplicete values in the indexed field.

24.3 Clustered index:

create clustered index index_name
on table_name (fieldname)A clustered index forces SQL Server to phisicaly store the table data in the exact order of of the index. This improves the performance of the table. You can only have obne clustered index on a table, the selected fieldname should be choosen carefully and every table should have an clustered index.

24.4 Display index info:

sp_helpindex tablename

24.5 Deleting an index:

drop index table_name.index_name

24.6 Primary and foreign keys:

You can specify a primary key on a table while creating it (see constraint), or you can add a primary key to an existing table by altering it. alter table table_name add constraint constraint_name primary key

24.7 Display primary and foreign keys info:

sp_helpconstraint table_name

24.6 Deleting primary and foreign keys:

alter table table_name drop constraint constraint_name

---

25. Transaction:

A transaction is a series of SQL-statements performed at once, with the ability to undo the changes if something goes wrong during the processing of the statements. A transaction is enclosed in "begin tran" and "commit tran".Example:

begin tran
 update tablename
 set fieldname = newvalue
 where fieldname = condition

 update tablename2
 set fieldname =  newvalue
 where fieldname = condition

 if @@error != 0
 begin
  rollback tran
  print 'Error occured, no rows updated'
  return
 end
commit tran

You can nest one transaction ino another by using named transactions. Be shure however to include a 'commit tran' for every 'begin tran' to avoid leaving a transaction open.
Example:

begin tran tran1_name
 update tablename
 set fieldname = newvalue
 where fieldname = condition

  begin tran tran2_name
   update tablename
   set fieldnname = newvalue
   where fieldname = condition
   if @@error != 0
   begin
    rollback tran tran2_name
    print 'Failed to update tablename'
    return
   end
  commit tran tran2_name
commit tran tran1_name

---

26. Rules:

A rule controls the values that can be entered into a field of a table. To apply a rule to a field you have to take two steps: create the rule and bind the rule to the field.

26.1 Create a rule:

create rule rule_name
as @fieldname in ('value1','value2','value3')

26.2 Bind a rule:

sp_bindrule 'rule_name', 'table_name.field_name'

26.3 Unbind a rule:

sp_unbindrule table_name.field_name

26.4 Drop a rule:

drop rule rule_name
!!You first have to unbind the rule before you can drop it!!To display rule bindings you can use 'sp_help tablename', to display the rules you can use 'sp_helptext rule_name' and to rename a rule you can use 'sp_rename rule_name, new_name'.

---

27. Defaults:

A default is create to provide a value for a field if the user doesn't fill in one. Just like creating rules you have to make to steps to apply ad efault: create it and bind it to a field in a table.

27.1 Create a default:

create default default_name as value

27.2 Bind a default:

sp_bindefault default_name, 'tablename.fieldname'

27.3 Unbind a default:

sp_unbindefault 'tablename.fieldname'

27.4 Drop a default:

drop default default_name
!!You first have to unbind the default before you can drop it!!To display default bindings you can use 'sp_help tablename', to display the default you can use 'sp_helptext default_name' and to rename a default you can use 'sp_rename default_name, new_name'.

---

28. Stored procedures:

Stored procedures are routines or series of SQL-commands that run on the server side. The benefits are performance, because the server typically is the most powerfull computer on the network, and security, because you can control add/change/delete/list operations.

28.1 Create a stored procedure:

create procedure procedure_name
as procedure_commandsTo execute the procedure you use 'exec procedure_name'. You can use parameters in procedures to pass values to the procedure:
create procedure procedure_name (@p1 type, @p2 type, ...)
as insert into table_name
values (@p1, @p2, ...)
Now you can execute this procedure by passing values to it:
exec procedure_name (value1, value2, ...)
You can also use variables in a procedure. You first have to declare them (declare @var_name var_type) and then you can assign a value to it (select @var_name = expression).

28.2 Display a stored procedure:

sp_helptext procedure_name

28.3 Delete a stored procedure:

drop procedure procedure_nameIf you want to alter a procedure, you first have to drop it and then recreate it under the same name.

28.4 Procedure Auto Execution:

You can automaticaly run a procedure every time the server is started by using:sp_makestartup procedure_name
sp_unmakestartup procedure_name removes the procedure from the startupprocedures
sp_helpstartup displays the procedures currently running at startup

28.5 Flow-control statements:

The following statements can be used in stored procedures to control the flow of the procedure:

• if ... else ...
• begin ... end
• while ...
• break
• continue
• print
• goto
• return
• raiserror
• waitfor
• case ... when

---

29. Datatypes:

This is an overview of the different datatypes used by the different servers.

MySQL	Borland InterBase Server	MS SQL Server	Sybase SQL
tinyint	-	tinyint	tinyint
smallint	smallint	smallint	smallint
mediumint	-	-	-
int	int	int	int
bigint	-	-	-
-	numeric	numeric	numeric
decimal	decimal	decimal	decimal
-	-	real	real
float	float	float	float
double	double precision	-	double
date	date	-	date
-	-	smalldatetime	-
datetime	-	datetime	-
timestamp	-	timestamp	timestamp
time	-	-	time
year	-	-	-
char(n)	char(n)	char(n)	char(n)
varchar(n)	varchar(n)	varchar(n)	varchar(n)
-	-	-	long varchar
tinytext	-	-	-
text	-	text	-
-	-	ntext	-
mediumtext	-	-	-
longtext	-	-	-
-	nchar(n)	nchar(n)	-
-	nchar varying(n)	nvarchar(n)	-
-	-	binary(n)	binary(n)
-	-	varbinary(n)	-
-	-	-	long binary
tinyblob	-	-	-
-	-	image	-
blob	blob	-	-
mediumblob	-	-	-
longblob	-	-	-
enum	-	-	-
set	-	-	-
-	-	bit	-
-	-	uniqueidentifier	-
-	-	money	-
-	-	smallmoney	-

---

Microsoft SQL Server Data Types

SQL Server offers six categories of data types for your use:

• Exact numeric data types store numeric values where you wish to specify the precision of the variable. They may include integer or decimal numbers and are the most common category of data type used for numeric information. You can read about the exact numeric data types in further detail.


• Approximate numeric data types are less precise than exact numeric data types. They allow for the specification of the number of digits to store precisely while the remainder of a variable’s value is subject to rounding error. You can read about the approximate numeric data types in further detail.


• Date and time data types allow the storage of timestamps. You can read about the date and time data types in further detail.


• Character string data types contain text-based values. You can read about the character string data types in further detail.


• Binary data types allow you to store any type of binary data, including entire files of up to 2GB. You can read about the binary data types in further detail.


• Other data types allow for the storage of unique identifiers, cursors, tables and XML. You can read about the other data types in further detail.

Triggers

Triggers offer database developers and administrators a tremendous degree of flexibility. They are, quite simply, stored procedures that may be configured to automatically execute (or "fire" in tech lingo) when certain events take place. 

All enterprise-level relational databases support triggers in one form or another. We'll take a brief look at the syntax used by Microsoft SQL Server, but the basic concepts in this article apply equally to Oracle databases. There are only some slight differences in keywords and formatting. 

The basic command used to create a new trigger is the CREATE TRIGGER statement. This statement is followed by the details of when the trigger should fire. Triggers may be associated with INSERT, UPDATE and DELETE events and may be used either INSTEAD OF or AFTER the specified operation. Once you've specified the trigger criteria, you supply the SQL statement that contains the trigger's payload. For example, if we wanted to configure a simple trigger to send an e-mail to the president each time a record is removed from the Orders table, we would use the following syntax: 

CREATE TRIGGER OrderRemoved ON Orders
AFTER DELETE
AS
EXEC xp_sendmail 'president@whitehouse.gov', 'A record has been deleted from the Orders table' 

If you later get an angry phone call from the President asking you to stop sending him e-mail and decide to redirect your messages to the Vice President, you could modify the trigger using the ALTER TRIGGER command, which has the same basic syntax: 

ALTER TRIGGER OrderRemoved ON Orders
AFTER DELETE
AS
EXEC xp_sendmail 'vicepresident@whitehouse.gov', 'A record has been deleted from the Orders table' 

If you forget the name of your trigger, you may obtain a list of all of the triggers that fire based upon the Orders table by using the command: 

sp_helptrigger Orders 

If you'd like to view the SQL behind a specific trigger, you may view it by issuing the command:

sp_helptext OrderRemoved 

Finally, if you later decide that you want to completely remove a trigger, the syntax is: 

DROP TRIGGER OrderRemoved 

Triggers add a tremendously powerful capability to database servers. The examples mentioned above are relatively trivial, but you should keep in mind that triggers are indeed stored procedures and you may use them to perform any action that a stored procedure can perform. This includes calling other stored procedures, accessing information from other data sources and modifying the contents of other database tables.

What is SQL?

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. 

Table Basics

A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. Here is a sample table called "weather".

city, state, high, and low are the columns. The rows contain the data for this table:

Weather
city	state	high	low
Phoenix	Arizona	105	90
Tucson	Arizona	101	92
Flagstaff	Arizona	88	69
San Diego	California	77	60
Albuquerque	New Mexico	80	72

Primary key VS Unique key

Primary key - Primary key means main key
def:- A primary key is one which uniquely identifies a row 
of a table. this key does not allow null values and also 
does not allow duplicate values. for ex,
     empno   empname   salary
       1      firoz     35000
       2      basha     34000
       3      chintoo   40000

it will not the values as follows:
1      firoz      35000
1      basha      34000
       chintoo    35000

Unique key -  single and main key
            A unique is one which uniquely identifies a row 
of a table, but there is a difference like it will not 
allow duplicate values and it will any number of  allow 
null values(In oracle).
it allows only a single null value(In sql server 2000)

Both will function in a similar way but a slight difference 
will be there. So, decalaring it as a primary key is the 
best one.

foreign key - a foreign key is one which will refer to a 
primary key of another table

for ex,
       emp_table                         dept_table
empno empname salary  deptno         deptno deptname

In the above relation, deptno is there in emp_table which 
is a primary key of dept_table. that means, deptno is 
refering the dept_table.

Foreign Key: A foreign key is one or more columns whose
value must exist in the primary key of another table.

how can session information/data be stored using a SQL server?

At work we use an Oracle 9i db to store our session info by removing the standard session provided by .NET and forcing our own session implementation in the web.config file.

So to answer your question, in order to store ASP.NET session information inside SQL Server you must:

1) Have SQL Server version 7 or 2000 installed.

2) Execute the following script using SQL Server’s Query Analyzer, which creates the database that ASP.NET uses to store Session:

\%winroot%\Microsoft.NET\Framework\versionNum\InstallSqlState.sql
3. Modify the web.config file appropriately (where the connectionString attribute denotes the connection string that should be used to open the database):
<sessionState mode="SQLServer" sqlConnectionString=
"data source=127.0.0.1;user sa;password=" />

Storing Session inside SQL Server is the most durable option, as information persists even if the machine crashes. This is the slowest option of the three, however, because session data must be accessed from disk rather than directly from memory, a process thousands of times slower. Note that as with the Windows Service option, you can point ASP.NET to a Session store on a different machine, which is also appropriate in Web farm settings.