Transcript eLL

Databases and modelling
3. day
Agenda
•
•
•
•
•
Introduction to SQL
Status on database implementation
Forms
Exercises
Reports
2
Introduction to SQL
• SQL = Structured Query Language
• (DML – Data Manipulation Language included)
• A declarative programming language (telling the
computer what to do – not how to do it by means
of algoritms like imperative programming
languages)
 SQL tells the database management system what to do and
to do the actual changes and retrievals in the database
• Developed as part of the development of relational
databases
• SQL is in slightly different versions implemented in
databases as MySQL, PostgreSQL, Oracle databases
and MS Access
3
What SQL does
• SQL (+DML) can
 execute queries
 retrieve data from a database
 insert new records in a database
 delete records from a database
 update records in a database
• Example (see the difference?)
 SELECT author.ID, author.Author FROM author;
 SELECT book.[Book number], book.[Book title]
FROM book;
4
SQL Statements
• SELECT - extracts data from a database
table
• UPDATE - updates data in a database table
• DELETE - deletes data from a database table
• INSERT INTO - inserts new data into a
database table
• SELECT LastName,FirstName FROM Persons
5
SQL Example
 SELECT book.[Book number], book.[Book title]
FROM book;
 SELECT table_name.[field name],
table_name.[field name 2] FROM table name;
 SELECT book.[Book title], book.[Book number]
FROM book WHERE (((book.[Book number])=1));
 SELECT author.Author, book.[Book title]
FROM book INNER JOIN (author INNER JOIN
[Written by] ON author.ID = [Written by].Author)
ON book.[Book number] = [Written by].Book;
6
SQL exercise
• Write SQL
expression that
extracts book
title, publication
year and
publisher from
the book table
• Find publications
from 2006 only
7
Step 6
8
Example
9
Different kinds of forms
• Forms for displaying data
• Form for navigating data
• Forms for user dialog and search
(combined with queries)
10
Exercise - forms
• Analyse the Northwind database
How does the main switchboard call other
forms?
Orders form: How does the content in the
Bill to dropdown come from?
Important and often easier to learn (and
copy…) from something already working
11
Reports
• Pulling data out of the database
formatted for other media
12