INTRODUCTION SQLRelational Databases

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 the elements from the paper 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 ordernumorderdate deliveryid fulfilled custidorderval

ue ....Orderline ordernum orderlinenum

prodidquantity linevalue discount ....ProductProdidDescPriceQuantitySellerid

Condition postagememo ...SellerSelleridNameAddr1addr2,Addr3addpc,

...Customercustid firstname, surname, Addr1Addr2addr3creditlimit ...

Etc....

Not a definitive list!

ONE POSSIBLE SOLUTION .... NOT DEFINITIVE

order product

orderline

customer

invoiceseller

Payment method

Delivery note

How does this differ from yours?

SQL – AS A PROGRAMMING LANGUAGE

SQL is the basis of all database programming As a language SQL is:

Non-procedural Specify the target, not the mechanism (what not how)

Safe Negations limited by context

Set-oriented All operations are on entire sets

Relationally complete Has the power of the relational algebra

Functionally incomplete Does not have the power of a programming language like

Java

SOME PROPERTIES

Non-procedural No loops or tests for end of file

Set-oriented The operation is automatically applied to all the

rows in STUDENT Relationally complete

Restrict shown here (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 stuname 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)

EXAMINING THE SEMINAR DATABASE

In your seminar you will run a script that will create the following tables:

o Markso Enrolledo Classo Subjecto Studento Staffmembero Department

RETRIEVING DATA

If we want a list of staff in the database we determine the table that holds that data and retrieve the data from that table.

Select * select nameFrom staffmember; 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

NAME--------------------Glen MaxwellEsme LettitiaBertie WoosterAndrew TurnbullMark Hurrell;Akhtar AliBen WightmanTim RoseGareth PriceNeil ThompsonPaul SamsonGrant SmithJohn TaitGareth PhillipsEmma-Jane Phillips

Only select

what you need!

PROCESS FOR DESIGNING YOUR QUERY

1) Identify the tables/relation which hold the information you need understand your system and the ERD

2) Determine the attributes of the table/relation that are required do not default to select *

3) 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. Is there a relationship between these relations yes studentid

3. Which attributes do you need only name

select stuname from student, enrolled where subjectid = 'COMP2031' and student.studentid = enrolled.studentid;

student

enrolled

COMP2031

STUNAME--------------------Jim SmithJack SmithTom JonesIsacc ThomasGlenda WilliamsonJohn SmithGrant SmithCharlie BrownCarl SmithKarl BrownDavid JonesWarrick Brown

PULLING 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 the same datatype and have the same meaning but do not need the same attribute name

staffmember department

Name Null? Type -------------------------------------------------------- STAFFID NOT NULL NUMBER(6) NAME VARCHAR2(20) DEPTID NUMBER(5)

Name Null? Type-------------------------------------------------DEPTID NOT NULL NUMBER(5)DNAME VARCHAR2(25)

TASK

Department tableDEPTIDDNAME

Staffmember tableSTAFFIDNAMEDEPTID

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 name, dnamefrom staffmember, departmentwhere staffmember.deptid =

department.deptid;