Transcript Lecture 3 - Introducing SQL (select)x

LECTURE 3 – SQL INTRODUCTION
Over the Past 2 weeks we have looked (in brief) at
the basic elements of a relational database.
What we understand by the phrase database?
 How the paper system needs to be adapted for
computer database system?
 How tables/relations are identified and broken down?

This understanding will be added to over the course,
adding complexity and depth of knowledge.
This week we are going to start to look at the use of
SQL to extract the data from the database.
REVIEW OF SEMINAR MATERIAL
Identify all data elements from the paper-based
system.
 The raw elements fall initially into 2 clusters
(Order & Invoice) but closer examination
indicate that additional elements are needed
(customer, product, seller etc.)
 Order needs to be split into 2 (orderheader &
orderline)
 Invoice links to order and customer

CONT ....
Orderheader (ordernum, orderdate, deliveryid, fulfilled, custid, ordervalue, ....)
Orderline (ordernum, orderlinenum, prodid, quantity, linevalue, discount ....)
product(prodid, desc, price, quantity, sellerid, condition, postage, memo ....)
Seller(sellerid, name, addr1, addr2, addr3, addpc, ....)
Customer(custid, firstname, surname, addr1, addr2, addr3, creditlimit ...)
Etc....
ONE POSSIBLE SOLUTION .... NOT DEFINITIVE
SQL – AS A PROGRAMMING LANGUAGE


SQL is the basis of all database programming
As a language SQL is:

Non-procedural


Safe


All operations are on entire sets of tuples
Relationally complete


Negations limited by context
Set-oriented


Specify the target, not the mechanism (what not how)
Has the power of the relational algebra
Functionally incomplete

Does not have the power of a programming language like Java
SOME PROPERTIES

Non-procedural


Set-oriented


The operation is automatically applied to all the rows in
STUDENT
Relationally complete


No loops or tests for end of file
Restrict, project & join shown in this lecture (all others are
available)
Functionally incomplete

Does not matter here if just want information displayed
SQL – PROGRAM CONSTRUCTIONS
The basic SQL statement comprises 3 main elements, what
you want, where it is found and how it can be filtered.
select *
from student
where major = 'Games';



FROM statement specifies tables to be queried
(source/range)
WHERE statement specifies restriction on values to be
processed (predicate)
SELECT statement specifies what is to be retrieved
(target), * means all columns in this case.
EXAMINING THE SEMINAR DATABASE

In your seminar you will run a script that will
create the following tables:
o
o
o
o
o
o
o
Marks
Enrolled
Class
Subject
Student
Staffmember
Department
Student
Department
StaffMember
Enrolled
Marks
Class
Subject
RETRIEVING DATA (PROJECTING SPECIFIC
COLUMNS)
If we want a list of staff names in the database we determine the
table that holds that data and retrieve the data from that table.
Select *
From staffmember;
STAFFID NAME
DEPTID
---------- -------------------- ---------811 Glen Maxwell
4
831 Esme Lettitia
1
851 Bertie Wooster
1
891 Andrew Turnbull 2
911 Mark Hurrell;
2
912 Akhtar Ali
1
921 Ben Wightman
2
922 Tim Rose
3
931 Gareth Price
2
932 Neil Thompson
2
951 Paul Samson
1
961 Grant Smith
4
962 John Tait
4
971 Gareth Phillips
2
989 Emma-Jane Phillips 4
select name
from staffmember;
NAME
-------------------Glen Maxwell
Esme Lettitia
Bertie Wooster
Andrew Turnbull
Mark Hurrell;
Akhtar Ali
Ben Wightman
Tim Rose
Gareth Price
Neil Thompson
Paul Samson
Grant Smith
John Tait
Gareth Phillips
Emma-Jane Phillips
PROCESS FOR DESIGNING YOUR QUERY
1)
2)
3)
Identify the tables/relation which hold the
information you need understand your system and the ERD
Determine the attributes of the table/relation
that are required do not default to select *
Is all the data held in one table/relation?
1)
If multiple relations required identify the related
elements for each relation. Range of joining tables select
appropriate method.
SQL PROGRAM – RETRIEVING FROM 2 TABLES/RELATIONS
Identify the names of students who are enrolled
on the course COMP2031
1. What tables/relations do we need? Enrolled &
student???
2.
3.
Is there a relationship between these
relations? yes through studentid
Which attributes do you need? only name
select stuname
from student, enrolled
where subjectid = 'COMP2031'
and student.studentid = enrolled.studentid;
COMP2031
STUNAME
-------------------Jim Smith
Jack Smith
Tom Jones
Isacc Thomas
Glenda Williamson
John Smith
Grant Smith
Charlie Brown
Carl Smith
Karl Brown
David Jones
Warrick Brown
PULLING DATA FROM MULTIPLE TABLES/RELATIONS
There are more effective ways of pulling data from
multiple tables/relations but initially we are going
to force the join in the where clause
 Attributes that are shared between
relations/tables must be of the same datatype
and have the same meaning but do not need the
same attribute name

staffmember
Name
Null?
Type
-------------------------------------------------------STAFFID NOT NULL NUMBER(6)
NAME
VARCHAR2(20)
DEPTID
NUMBER(5)
department
Name
Null?
Type
------------------------------------------------DEPTID NOT NULL NUMBER(5)
DNAME
VARCHAR2(25)
TASK
Student
Department
StaffMember
Department table
DEPTID
DNAME
Staffmember table
STAFFID
NAME
DEPTID
Enrolled
Marks
Class
Subject
The attribute that
relates
staffmember and
department is
deptid
Write SQL to list the
names of the staff and the
name of the department
they work with.
select
from
where
name, dname
staffmember, department
staffmember.deptid =
department.deptid;