CSCI1406

1
Lecture 18
CSCI1406 Introduction to Programming with Visual
Basic Dr Peter Yardley
2

• Using Forms.
• Creating queries.
• Introduction to SQL.

3
In a RDBMS all data is in tables. A form is a
view which allows you to carry out all operations
on data. Typically it shows one record at a time.
4
Since it is just a view you can have as many
forms as you wish
You can show only the fields you want.
5
Operations which extract data are called
queries. The result of a query is an answer
table. This is a key feature of RDBMS called
closure. The data tables of the database are
called base tables. As the data in the base
tables changes the answer table remains
unchanged. If you run the query again, a new
answer table will be produced. If you change the
data in an answer table the underlying data in
the base table is changed - but this is fraught
with problems "incompatible with preserving the
integrity of the data"
6
View queries. Update queries change the data
in a table, for example to increase prices by 5
(but only if the product costs more than
35.00) Append queries Find data in one table
and add that to another table, for example to
find all the sale people who sell more than
100,000 and to add their names to another
table. Delete queries Find the names of sales
people who have sold less than 50,000 and delete
them from another table.
7
Structured Query Language pronounced as Sequel or
S-Q-L a data access language or data
sub-language. Used for creating and deleting
tables, updating and editing as well as simple
queries. Queries are the most commonly used
aspect - this comprises the Data Manipulation
Language (DML) statements.
8
SALES
9
The SELECT statement extracts a collection of
fields from a table SELECT Item, AmountFROM
SALES produces
10
To remove duplicates you can use
DISTINCT SELECT DISTINCT Item, AmountFROM
SALES produces
11
WHERE allows you to apply a condition SELECT
Item, AmountFROM SALESWHERE Item"Sofa"
12
SELECT Item, AmountFROM SALESWHERE Item"Sofa"
AND Customer"Smith"
The basic format of a SELECT statement
is SELECT field namesFROM table nameWHERE
conditions
13
There are other operators in WHERE clauses in
addition to the operator
14
(No Transcript)
15
SELECT Item, AmountFROM SALESWHERE Item"Sofa"
AND SalesNo gt 5
16
SELECT Item, AmountFROM SALESWHERE Item IN
("Sofa", "Suite", "Bed")
17
SELECT Item, AmountFROM SALESWHERE item IN
("Sofa", "Suite", "Bed")AND SalesNo gt 6
18
SELECT Item, AmountFROM SALESWHERE Item In
("Sofa", "Suite", "Bed")OR SalesNo gt 6
19
This clause lists in numeric or alphabetical
order. SELECT Item, Amount, SalesNoFROM
SALESWHERE Item "Sofa"ORDER BY SalesNo
The columns are in the specified order in
increasing SalesNo order.
20
SELECT Item, AmountFROM SalesWHERE SalesNo gt
2ORDER BY Item ASC
The items are listed in ascending alphabetical
order. To reverse the order use DESC.