Lesson 7 - Introduction To Sql For Visual Basic Programming

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Introduction to SQL If you want to work with databases, you must learn to speak their language. Databases speak STRUCTURED QUERY LANGUAGE, better known as SQL, which was invented by E. F. Codd in the 1970’s. SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieved and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

What is SQL?

• • • • • •

SQL stands for Structured Query Language. SQL is a standard computer language for accessing and manipulating databases. SQL can retrieve or extract data from a database. SQL can insert new records in a database. SQL can update and delete records from a database. SQL is easy to learn.

SQL encompasses two distinct categories of statements: Data Definition language (DDL) and Data Manipulation Language (DML). The DDL subset includes a group of statements that allow you to create database structures, such as tables, fields, indices and so on. The DML subset includes all the commands that allow you to query and modify the data in the database, add new records, or delete existing ones. Most of the time, you’ll use only DML statements to retrieve and update data stored in a database.

Two Categories of SQL statements: 1. SQL Data Definition Language (DDL) – permits database tables to be created or deleted. Using SQL DDL, you can define indexes (keys), specify links between tables, and impose constraints between database tables. The most important DDL commands in SQL are: a. CREATE TABLE – creates a new database table. b. ALTER TABLE – alters (changes) a database table. c. DROP TABLE – deletes a database table. d. CREATE INDEX – creates an index (search key) e. DROP INDEX – deletes an index.

2. SQL Data Manipulation Language (DML) – commands that modify or manipulate records in a database table. Commands that form the Data Manipulation Language: a. SELECT – extracts data from a database table. b. UPDATE – updates data in a database table. Information and Communication Technology Department Palompon Institute of Technology

53

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

c. DELETE – deletes data from a database table. d. INSERT – inserts new data into a database table. SQL Database Tables A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (row) with data. Below is an example of a table called “Persons” LastName

FirstName

Address

City

Hansen Stevendson Pettersen

Ola Tove Karl

Timotein 20 Borgvn 23 Storgt 20

Sandnes Sandnes Stavanger

The table above contains three records (one for each person) and four columns (LastName, FirstName, Address, City).

The SELECT Statement The SELECT statement selects columns of data from a database table. The tabular result is stored in a result table called the resultset. Syntax: SELECT column-name(s) FROM table-name

Example 1: Select Columns from a Table. To select the columns named “LastName” and “FirstName”, use a SELECT statement like this: SELECT LastName, FirstName FROM Persons

The “Persons” table: LastName

FirstName

Address

City

Hansen Stevendson Pettersen

Ola Tove Karl

Timotein 20 Borgvn 23 Storgt 20

Sandnes Sandnes Stavanger

The result: LastName Hansen Stevendson Pettersen

FirstName Ola Tove Karl

Example 2: Select all Columns Information and Communication Technology Department Palompon Institute of Technology

54

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

To select all columns from the “Persons” table, use an * (asterisk) symbol instead of column name, like this: SELECT * FROM Persons

The result: LastName Hansen Stevendson Pettersen

FirstName Ola Tove Karl

Address Timotein 20 Borgvn 23 Storgt 20

City Sandnes Sandnes Stavanger

The WHERE Clause To conditionally select data from a table, a WHERE clause can be added to the SELECT statement. Syntax: SELECT column-name FROM table-name WHERE column Operator value

With WHERE clause, the following operator can be used: Operator = <> > < >= <= BETWEEN LIKE

Description Equal Not equal Greater than Less than Greater than or equal Less than or equal Between an inclusive range Search for a pattern

Note: In some versions of SQL, the Not Equal operator <> can be written as != Example: To select the persons living in the city “Sandnes”, we add a WHERE clause to the SELECT statement: SELECT * FROM Persons WHERE City = 'Sandnes'

The “Persons” table: LastName Hansen Stevendson Stevendson Pettersen

FirstName Ola Tove Stale Karl

Address Timotein 20 Borgvn 23 Kaivn 18 Storgt 20

City Sandnes Sandnes Sandnes Stavanger

Year 1951 1978 1980 1960

The result: Information and Communication Technology Department Palompon Institute of Technology

55

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

LastName Hansen Stevendson Stevendson

FirstName Ola Tove Stale

Address Timotein 20 Borgvn 23 Kaivn 18

City Sandnes Sandnes Sandnes

7 Year 1951 1978 1980

The LIKE Condition The LIKE condition is used to specify a search for a pattern in a column. Syntax: SELECT column-name FROM table-name WHERE column LIKE pattern

A “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern. Example 1: To extract record(s) with First Names that start with an ‘O’: SELECT * FROM Persons WHERE FirstName LIKE 'O%'

Example 2: To retrieved record(s) from Persons table with First Names that end with an ‘a’: SELECT * FROM Persons WHERE FirstName LIKE '%a'

Example 3: To return record(s) of persons with First Names that contains the pattern ‘la’: SELECT * FROM Persons WHERE FirstName LIKE '%la%'

All the examples above will return the following result: LastName Hansen

FirstName Ola

Address Timotein 20

City Sandnes

Year 1951

SQL AND & OR Operator AND and OR operator join two or more conditions in a WHERE clause. The AND operator displays a record if ALL conditions listed evaluates to TRUE. The OR operator displays a record if ANY of the condition listed evaluates to TRUE. Original Table (used in the examples) LastName Hansen Stevendson Stevendson

FirstName Ola Tove Stephen

Address Timotein 20 Borgvn 23 Kaivn 18

Information and Communication Technology Department Palompon Institute of Technology

City Sandnes Sandnes Sandnes

56

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Example 1: Use AND to display each person with the First Name equal to “Tove”, and the Last Name equal to “Stevendson”: SELECT * FROM Persons WHERE FirstName = 'Tove' AND LastName = 'Stevendson'

Result: LastName Stevendson

FirstName Tove

Address Borgvn 23

City Sandnes

Example 2: Use OR to display each person with the First Name equal to “Tove”, or the Last Name equal to “Stevendson”: SELECT * FROM Persons WHERE FirstName = 'Tove' OR LastName = 'Stevendson'

Result: LastName Stevendson Stevendson

FirstName Tove Stephen

Address Borgvn 23 Kaivn 18

City Sandnes Sandnes

Example 3: You can also combine AND and OR (use parenthesis to form complex expressions): SELECT * FROM Persons WHERE (FirstName = 'Tove' OR FirstName = 'Stephen') AND LastName = 'Stevendson'

The result: LastName Stevendson Stevendson

FirstName Tove Stephen

Address Borgvn 23 Kaivn 18

City Sandnes Sandnes

Using BETWEEN . . . AND Operator The BETWEEN . . . AND operator selects an inclusive range of data between two values. These values can be numbers, text, or dates. Syntax: SELECT column-name FROM table-name WHERE column-name BETWEEN value1 AND value2

The “Persons” table: LastName Hansen Stevendson Nordmann Pettersen

FirstName Ola Tove Anna Karl

Address Timotein 20 Borgvn 23 Neset 18 Storgt 20

Information and Communication Technology Department Palompon Institute of Technology

City Sandnes Sandnes Sandnes Stavanger

57

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Example 1: To display the persons alphabetically between (and including) “Hansen” and “Pettersen”, use the following SQL: SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

Result: LastName Hansen Nordmann Pettersen

FirstName Ola Anna Karl

Address Timotein 20 Neset 18 Storgt 20

City Sandnes Sandnes Stavanger

Example 2: To display the persons outside the range used in the previous example, use the NOT operator: SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Hansen' AND 'Pettersen'

The result: LastName Stevendson

FirstName Tove

Address Borgvn 23

City Sandnes

The DISTINCT Keyword The DISTINCT keyword is used to return only distinct (unique) values. Syntax: SELECT DISTINCT column-name(s) FROM table-name

The “Orders” table: Company Sega Williams ABC Shop Williams

OrderNumber 3412 2312 4678 6798

Example 1: Select Companies from Orders table. This SQL statement: SELECT Company FROM Orders

Will return this result: Company Sega Williams ABC Shop Williams

Note: The Company Williams is listed twice in the result. Information and Communication Technology Department Palompon Institute of Technology

58

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Example 2: Select Distinct Companies from Orders To select distinct or unique values from the column named “Company”, we use the DISTINCT keyword with the SELECT statement: SELECT DISTINCT Company FROM Orders

The result: Company Sega Williams ABC Shop

Note: “Williams” is listed only once in the resultset.

SQL ORDER BY The ORDER BY keyword is used to sort records. Using the “Orders” table: Company Sega ABC Shop Williams Williams

OrderNumber 3412 5678 2312 6798

Example 1: To display the Companies and Order Number in alphabetical order: SELECT * FROM Orders ORDER BY Company

Result: Company ABC Shop Sega Williams Williams

OrderNumber 5678 3412 6798 2312

Example 2: To display the Companies in alphabetical order and the Order Number in numerical order: SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

Result: Company ABC Shop Sega Williams Williams

OrderNumber 5678 3412 2312 6798

Information and Communication Technology Department Palompon Institute of Technology

59

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Example 3: To display the Companies in reverse alphabetical order: SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

Result: Company Williams Williams Sega ABC Shop

OrderNumber 6798 2312 3412 5678

SQL INSERT INTO Statement The INSERT INTO statement inserts new rows into a table. Syntax: INSERT INTO table-name VALUES (value1, value2, . . ., valueN)

You can also specify the columns for which you want to insert data: INSERT INTO table-name(column1, column2, . . ., columnN) VALUES(value1, value2, . . ., valueN)

The “Persons” table: LastName Pettersen

FirstName Karl

Address Storgt 20

City Stavanger

Example 1: Inserting record to Persons table: INSERT INTO Persons VALUES('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')

Result: LastName Pettersen Hetland

FirstName Karl Camilla

Address Storgt 20 Hagabakka 24

City Stavanger Sandnes

Example 2: Insert record in specified column. This “Persons” table: LastName Pettersen Hetland

FirstName Karl Camilla

Address Storgt 20 Hagabakka 24

City Stavanger Sandnes

And this SQL statement: INSERT INTO Persons(LastName, Address) VALUES('Rasmussen', 'Storgt 67') Information and Communication Technology Department Palompon Institute of Technology

60

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Will give this result: LastName Pettersen Hetland Rasmussen

FirstName Karl Camilla

Address Storgt 20 Hagabakka 24 Storgt 67

City Stavanger Sandnes

SQL UPDATE Statement The UPDATE statement updates or changes records. Syntax: UPDATE table-name SET column-name = new-value WHERE column-name = somevalue

The “Persons” table: LastName Nilsen Rasmussen

FirstName Fred

Address Kirkegt 56 Storgt 67

City Stavanger

Example 1: Update one column in a row. UPDATE Persons SET FirstName = 'Nina' WHERE LastName = 'Rasmussen'

Result: LastName Nilsen Rasmussen

FirstName Fred Nina

Address Kirkegt 56 Storgt 67

City Stavanger

Example 2: Update several columns in a row. UPDATE Persons SET Address = 'Stien 12', City = 'Stavanger' WHERE LastName = 'Rasmussen'

Result: LastName Nilsen Rasmussen

FirstName Fred Nina

Address Kirkegt 56 Stien 12

City Stavanger Stavanger

SQL DELETE Statement The DELETE statement is used to delete rows in a table. Syntax: DELETE FROM table-name WHERE column-name = some-value Information and Communication Technology Department Palompon Institute of Technology

61

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

The “Persons” table: LastName Nilsen Rasmussen

FirstName Fred Nina

Address Kirkegt 56 Stien 12

City Stavanger Stavanger

Example 1: Delete a row. “Nina Rasmussen” is going to be deleted: DELETE FROM Persons WHERE LastName = 'Rasmussen'

Result: LastName Nilsen

FirstName Fred

Address Kirkegt 56

City Stavanger

It is also possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will still be intact: Syntax: DELETE FROM table-name Or DELETE * FROM table-name

Example 2: Delete all rows in a table. DELETE * FROM Persons

Result: LastName

FirstName

Address

City

SQL COUNT Function The COUNT function returns the number of records in a database table. Syntax: SELECT COUNT(column-name) FROM table-name

With this “Persons” table: Name Hansen, Ola Stevendson, Tove Pettersen, Karl Rasmussen, Nina

Information and Communication Technology Department Palompon Institute of Technology

Age 34 45 19 20

62

INTRODUCTION TO SQL FOR VISUAL BASIC PROGRAMMING

7

Example 1: To count the number of records in the Persons table: SELECT COUNT(*) FROM Persons

Result: 4

Example 2: To return the number of persons older than 20 years: SELECT COUNT(*) FROM Persons WHERE Age > 20

Result: 2

Information and Communication Technology Department Palompon Institute of Technology

63