SQL Basics

Back

8. Select all records from table:

Note 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.

Back

9. Select set of records from table:

Comparison Operators that can be used in the condition are:

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

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:

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

Back

11. Add records to a table:

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.

Back

12. Updating records in a table;

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

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!!!

Back

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

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
modify fieldname newdatatype

Back

16. Removing field from the records

alter table tablename
drop fieldname

17. Combine two querries:

This union will remove all duplicates. To retain duplicates you have to use 'union all'

Back

18. Basic SQL-functions:

Back

19. String functions

Back

20. Arithmetic functions:

21. TEXT and IMAGE functions:

22. Date functions:

Back

23. Views:

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

23.1 Create views:

You 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:

23.2 Display definition of a view:

To prevent the possibility to view the definition of a view you can use encryption:

23.3 Display associations of a view:

23.4 Delete a view from the database:

23.5 Insert records through a view:

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:

23.6 Delete records through a view:

You 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:

You 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.

Back

24. Indexes:

24.1 Simple index:

24.2 Unique index:

This kind of index enforces integrity of the table by disallowing duplicete values in the indexed field.

24.3 Clustered index:

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:

24.5 Deleting an index:

24.6 Primary and foreign keys:

24.7 Display primary and foreign keys info:

24.6 Deleting primary and foreign keys:

Back

25. Transaction:

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

Back

26. Rules:

26.1 Create a rule:

26.2 Bind a rule:

26.3 Unbind a rule:

26.4 Drop a rule:

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'.

Back

27. Defaults:

27.1 Create a default:

27.2 Bind a default:

27.3 Unbind a default:

27.4 Drop a default:

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'.

Back

28. Stored procedures:

28.1 Create a stored procedure:

To 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:

28.3 Delete a stored procedure:

If 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:

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:

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

Back

29. Datatypes:

Back