Lecture 3 – SQL introduction

1. 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. Lecture 3 – SQL introduction

2. 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 Review of Seminar material

3. 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.... Cont .... Not a definitive list!

4. One possible solution .... Not definitive customer Payment method order product seller invoice invoice orderline How does this differ from yours?

5. 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 of tuples • Relationally complete • Has the power of the relational algebra • Functionally incomplete • Does not have the power of a programming language like Java SQL – as a programming language

6. 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, project & join shown in this lecture (all others are available) • Functionally incomplete • Does not matter here if just want information displayed Some properties

7. 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. SQL – Program Constructions

8. Student Enrolled Marks Department StaffMember Class Subject • In your seminar you will run a script that will create the following tables: • Marks • Enrolled • Class • Subject • Student • Staffmember • Department Examining the seminar database

9. 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 * select name From staffmember; from staffmember; STAFFID NAME DEPTID ---------- -------------------- ---------- 811 Glen Maxwell 4 831 EsmeLettitia 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 Maxwell EsmeLettitia 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 Only select what you need!

10. 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? • If multiple relations required identify the related elements for each relation.Range of joining tables select appropriate method. Process for designing your query

11. Identify the names of students who are enrolled on the course COMP2031 • What tables/relations do we need? Enrolled & student??? • 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; SQL Program – retrieving from 2 tables/relations student enrolled 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

12. 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 staffmemberdepartment Name Null? Type -------------------------------------------------------- STAFFID NOT NULL NUMBER(6) NAME VARCHAR2(20) DEPTID NUMBER(5) Pulling data from multiple tables/relations Name Null? Type ------------------------------------------------- DEPTID NOT NULL NUMBER(5) DNAME VARCHAR2(25)

13. Student Enrolled Marks Department StaffMember Class Subject task Write SQL to list the names of the staff and the name of the department they work with. select name, dname from staffmember, department where staffmember.deptid = department.deptid; Department table DEPTID DNAME Staffmember table STAFFID NAME DEPTID The attribute that relates staffmember and department is deptid