Nico's VB Page 6 (SQL Introduction)

You are visitor number

SQL Structured Query Language

1.0 Introduction

In the 1970’s Dr EF Codd from IBM designed the Structured "English" Query Language pronounced as "SEQUEL" and eventualy abreviated to SQL

This system was developed and still runs on IBM Mainframes However it is now run on all platforms all the way down to a Desktop PC. By using IBM’s DB2 SQL Server you have a Scalable Database that can be run on all platforms from Mainframe to PC running Windows 95/NT and even Linux (which you can communicate via TCPIP and ODBC)

It is important to have a good knowledge of SQL as you may need to use pass-through queries and that means you communicate with you SQL server(database) using SQL statements

2.0 The SQL Language

This is non-procedural Query language.

In the early days Queries were typed on a terminal to instruct the (Database Server) Mainframe to

do a task and return the restults on the terminal’s display

You write SELECT queries to retrieve data from the database

UPDATE queries to send data to the database

DROP(delete) and CREATE queries to delete or create data in the database (tables)

2.1 Types of Commands(Queries)

Data Security Commands

Data Definition Commands
Data Manipulation Commands

Command Category	Command	Description
Data Definition	CREATE	This is used for creating tables
Data Definition	ALTER	To alter the structure of tables
Data Definition	DROP	To delete tables (Most dangerous use it carefully)
Data Manipulation	INSERT	This is used to insert rows (records) to tables
Data Manipulation	UPDATE	This is used to force changes to rows in tables
Data Manipulation	DELETE	This is used to delete rows from the tables
Data Manipulation	SELECT	This is used to make conditional searches(queries) to retrieve data from the database
Data Security	REVOKE	To specify who may not have access to the database/tables
Data Security	GRANT	Allow a user or group to access the database and tables

2.2 Basic Data Types

INTEGER

DEC(len,dec)

FLOAT

CHAR(len)
VARCHAR(len)
TEXT(len)

LONG VARCHAR Stores BLOBs Binary Large Objects

DATE
TIME
TIMESTAMP

2.3 Opperators

Comparison Opperators

=	equal
!= <>	not equal
<	smaller
>	greater
<=	smaller equal
>=	greater equal

Logical Opperators

AND	Boolean AND
OR	Boolean OR
NOT	Boolean NOT

Query Selection Opperators

NOT IN

BETWEEN

NOT BETWEEN

NOT LIKE

IS NULL

IS NOT NULL

2.4 Using The Commands

Command	Example
CREATE CRTEATE INDEX	CREATE TABLE DIARY ( DIDATE DATE NOT NULL, DIENTRY CHAR(25) ) CREATE INDEX DDATE ON DIARY (DIDATE) WITH PRIMARY
ALTER	ALTER TABLE DAIRY ADD COLUMN DITIME TIME ALTER TABLE SALES DROP COLUMN PROFIT
DROP	DROP TABLE BRIBES DROP INDEX DDATE ON DIARY
INSERT	INSERT INTO DIARY (DIDATE , DITIME , DIENTRY) VALUES ("09/23/1999" , "10:00" , "Dental Appointment")
UPDATE	UPDATE DIARY SET DIENTRY ="Dental Appointment" WHERE DIDATE = "09/23/1999" AND DITIME = "10:00"
DELETE	DELETE FROM DIARY WHERE DIDATE = "09/23/1999" AND DITIME = "10:00"
SELECT	This will be handled sepperately
GRANT	GRANT SELECT ON DIARY TO STAFF GRANT SELECT , INSERT , UPDATE , DELETE ON DIARY TO MANAGERS
REVOKE	REVOKE ALL ON DIARY TO COMPETITORS REVOKE INSERT , UPDATE , DELETE ON DIARY TO TEMPS

2.5 The Select Query

2.5.1 Select Query Options

*Selects all collumns(fields) in a table

Colm2,Colm3Will select specific columns Colm2 and Colm3 from the table

See Column functions below

2.5.1.1 Column Functions

Column Function	Description
MIN	Returns the smallest number in a column
MAX	Returns the largest number in a column
AVG	Returns the average number in a column
SUM	Returns the sum of numbers in a column
COUNT(*)	Returns the number of rows in a column meeting the search criteria
COUNT (DISTINCT)	Returns the number of unique rows in a column meeting the search criteria
FIRST	Returns the first number in a column
LAST	Returns the last number in a column

2.5.2 The Select Query Structure

Command	Conditions for Command	Description
SELECT DISTINCT (columns to select)	from of these : * COLNAME2,COLNAME3 M.ROW1,Y.ROW2	The Select Statement This removes duplicates from selection This selects all collumns This selects specific columns (COLNAME2,COLNAME3) M & Y are correlation variables for Joining tables
( or column expressions)	and optionaly from these: SALARY-TAX AS AMOUNT SUM(SALARY) AS TOTAL COUNT(*) AS NR_CHEQUES
FROM	TABLE1 MTABLE M , YTABLE Y	Table selected M & Y are correlation variables for Joining tables
WHERE (row expressions)	optional filter conditions: IN ("K1" , "K6" …….etc ) AND NOT IN ("K2,"K7") AND BETWEEN ("K1" AND "K9") AND LIKE "%AM_AT%" AND NOT LIKE "%SK_A_%" AND IS NULL AND IS NOT NULL M.ROW1=Y.ROW2	Conditions for selection rows M & Y are correlation variables for Joining tables
GROUP BY	DEPTNO , DEPTNAME
HAVING (conditions for selecting groups )	COUNT(*) <5 AND SUM(MSALARY) >10000
ORDER BY	optional sort order conditions: Column names MYCOL DESC	Sort data descending by column=MYCOL

2.5.3 Examples of Select Queries

1) Simple Select Statement

SELECT * FROM DIARY

________________________________________

2) Simple Select Statement

SELECT DIDATE , DIENTRY

FROM DIARY

________________________________________

3) Simple Select Statement

SELECT * FROM DIARY

WHERE DIDATE = "09/23/1999" AND

DITIME = "10:00"

________________________________________

4) Order By Select Statement

SELECT * FROM DIARY

WHERE DIDATE >= "09/23/1999"

ORDER BY DIDATE , DITIME

_______________________________________________

5) Order By Select Statement

SELECT NAME , SURNAME , PAY , TAX , PAY – TAX

FROM SALARIES

WHERE PAY > 15000 AND

TAX < 2300

ORDER BY NAME

_______________________________________________

6) GroupBy Having Select Statement

SELECT SUPPLIER ,

NR_OF_CHEQUES AS COUNT(*),

TOTAL_PAYMENT AS SUM(PAYMENT)

FROM CREDITORS

GROUP BY SUPPLIER

HAVING TOTAL_PAYMENT>0

HAVING COUNT(*) >1

________________________________________

7) Distinct Select Statement

SELECT DISTINCT DIDATE , DITIME , DIENTRIES

FROM DIARY

WHERE DIDATE IS NOT NULL AND

DITIME IS NOT NULL AND

DIENTRY IS NOT NULL

________________________________________

8) Distinct Select Statement

SELECT DISTINCT DIDATE , DITIME , DIENTRIES

FROM DIARY

WHERE DIDATE >= "09/23/1999"

ORDER BY DIDATE , DITIME

________________________________________

9) Table Join Select Statement using Correlation Variables M & Y

SELECT M.ROW1 , M.ROW2 , Y.ROW1 , Y.ROW2 M & Y are correlation variables for Joining tables

FROM MYTABLE M , YOURTABLE Y MYTABLE and YOURTABLE

WHERE M.ROW1 = Y.ROW3

ORDER BY M.ROW2

_______________________________________________

Top of Page

Return to Nico's Visual Basic Page

(http://www.geocities.com/SiliconValley/Peaks/2852/VB_Charts.html)

Please send mail to: [email protected] with questions or comments about the content this VB Web site.

This page hosted by Get your own Free Home Page

Hosted by www.Geocities.ws