Objectives for Week 2 and 3 SQL

1
Objectives for Week 2 and 3 - SQL

• Relational algebra and SQL
• Identify the operations of the 8 functions of
  relational algebra.
• Understand the role of Structured Query Language
  in regard to Relational Databases.
• Identify the 3 main procedure sets of SQL, and an
  understanding of associated basic commands.

2
Extracting information from a database

• What do we need?
• A means to manipulate the relations in a
  database.
• Criteria for retrieval is restrictions on the
  records to be retrieved and displayed.
• A language needs to be adapt to the requirements
  of the user.
• Such things as Boolean operators need to be
  available for any queries including the
  application of multiple criteria.

3
Relational Model

• The relational model represents the database as a
  collection of relations (resembles a table)
• Relations contain a collection of related data
  values compared to a flat file.
• A relation typically corresponds to a real-world
  entity or relationship.
• In this model a row is called a tuple a column
  header is called an attribute, and the table is
  called a relation.

4
Relational Calculus

• Definition of the Relational Model is based on
  complex mathematical theory referred to as
  predicate calculus.
• Ted Codd (1970) proposed relational calculus or
  relational algebra as a means to define the
  Relational Model.

5
Relational algebra

• Relational algebra is a theoretical way of
  manipulating a relational database.
• Relational algebra does not specify the actual
  manipulation language.
• Processing data from a relational database
  involves issuing relational algebra commands.
• To be considered minimally relational, the DBMS
  must support the key relational functions SELECT,
  PROJECT and JOIN.
• Note very few support all eight relational
  operators.

6
Types of operations possible on a relational
database

• Codd originally defined eight relational
  operators.
• SELECT originally called RESTRICT
• PROJECT
• JOIN
• INTERSECT
• UNION
• DIFFERENCE
• PRODUCT
• DIVIDE

7
Basic Relational Algebra Operators

• 1. SELECT basically selects a subset of the
  tuples from a relation according to specific
  conditions
• Consider
• SELECT Customer WHERE Credit Limit
  1000GIVING Temp
• the result of this query will become available in
  a table called Answer
• All column will be included in the new table
  however .
• The rows will be restricted to the condition given

8

• 2. PROJECT - basically selects specific columns
  required to be displayed in a new table e.g.
• PROJECT table name OVER specified fields
  GIVING (New table name
• SELECT Customer WHERE Credit Limit
  1000GIVING Temp
• PROJECT Temp OVER (Customer Number, First
  NameGIVING Answer

9
Relational Algebra

• 3. JOIN
• Allows for the combination of related tuples from
  2 or more relations based on matching attribute
  into a single tuple.
• The link between the two tables is a foreign key
  and a primary key called the join attribute.
• Results in the formation of a new relations
  containing the attributes from both tables.
• Naturally the PROJECT command could then be used
  to restrict the attributes in a resulting table.

10

• Example of JOIN refer to Fig 2.29 page 47
• this shows two relations Customer and Sales Rep
• The resulting JOIN is shown Fig 2.30, p. 47
• Can you see the criteria used in the JOIN?
• Answer a common attribute needed to be
  specified.
• Basically it is the expanding or linking of the
  foreign key with the associated primary key
  information.
• There are many different types of JOINS possible

11
Example of the JOIN command

• JOIN Customer Sales Rep WHERE
  Customer.Sales Rep No Sales Rep.Sales
  Rep NoGIVING Temp
• PROJECT Temp OVER (Customer Number, Last
  Name, Sale Rep No)GIVING Answer
• Result
• The attributes from Customer and Sales Rep tables
  are JOINed together where the Sales Rep Nos
  correspond to form a new table (Temp).
• The PROJECT command results in a new table
  (Answer) being created with the attribute names
  given above

12
Relational Dbase Algebra Operators cont

• INTERSECT - displays only tuples that appear in
  both tables ideal to identify duplication of
  rows in two table.
• 5. UNION - combines all rows from two tables
  requires same number of attributes in both tables
  containing the same type of data.
• 6. DIFFERENCE - displays all tuples in one table
  that do not occur in another.

13
More relational algebra ..

• PRODUCT
• Combines a tuple from one relation to a tuple of
  another relation
• For example T110 rows T2 20 rows 200 rows.
• DIVIDE
• typically used with 1 x column table and a 2 x
  column table with a common column in both tables
• the output in a list of the second column
  attributes that is not common attributes to both
  tables.
• Refer to page 60 of your texts for an example

14
When is a database relational?

• A database is only truly relational if it
  conforms to rules defined by Mr. E. F. CODD.
• Codd postulated certain characteristics of a
  relational database which included
• - support all 8 relational operators.
• - enforces both entity and referential
  integrity rules.

15
What is SQL?

• Structured Query Language - advanced Relational
  Database Language which operates on data entirely
  as Logical sets.
• Designed by IBM mid 70s
• Structured language for accessing RDB (DB2,
  ORACLE, Access, mainframes to micro) ANSI
  standard
• Non procedural (dont need to specify HOW data is
  retrieved)
• Only about 30 commands.

16
SQL Objectives

• Is a complete database language. Can be used
  to
• Initially create a database
• Create underlying physical objects
• Create application specific logical objects
• Define authorisation other controls
• Retrieve manipulate data in a DB

17
Tablecolumn naming conventions

• Naming conventions can vary depending on the
  version of SQL general rules
• No longer than 18 characters
• Must start with a letter
• Can contain letters, numbers and underscore ( _ )
• Cannot contain any spaces although ...

18
Field attribute type must be specified

• Number integer, smallint, decimal
• Character char
• date (Julian date - allows add subtraction of
  dates)
• Logical - true or false
• memo columns.

19

• CREATING A DATABASE
• 1. CREATE DATABASE student()
• 2. LOAD student
• CREATE TABLE student
• (stud_id CHAR(4) NOT NULL UNIQUE,
• surname CHAR(15) NOT NULL,
• income_level DECIMAL(10,2),
• course_id CHAR NULL,
• date_start DATE,
• PRIMARY KEY (stud_id),
• FOREIGN KEY(course_id)REFERENCES course)

20
General rules

• No rules as far as format of the way the commands
  are written
• NOT NULL means that that cell must contain a
  value.
• PRIMARY KEY means to uniquely identify the
  data.
• FOREIGN KEY means to link one table to another.

21
Importance of primary and foreign keys

• Two important integrity rules that must be
  enforced by a DBMS are
• Referential integrity states
• that if table A contains a foreign key that
  matches a primary key of table then .
• Values for this foreign key either must
• Match the value of the primary key OR
• Be null
• Entity integrity states
• That a column which is part of a primary key
  CANNOT accept a null value.

22
Data Management - SQL commands to

• INSERT INTO adds new data to a tables.
• UPDATE(s) existing data in a table.
• DELETE(s) records from a table meeting specific
  conditions be careful using this command why?
• COMMIT applies all changes to the database and
  releases any locks on tables.
• ROLLBACK restores database to state prior to
  changes - since last commit.
• ALTER command to add new columns to an existing
  table easiest approach is to make this a NULL
  value.

23
INSERT INTO

• The INSERT operation provides a list of
  attribute values for a new tuple that is to be
  inserted into a new relation
• This operation can violate key constraints
  including
• Primary key entered that already exists
• Primary key is null
• Foreign key entered that does not correspond to a
  primary key in another table
• 4. Inserting Data
• INSERT INTO student VALUES (123, Smith,
  BIT678, 23/02/98)

24
Examples of the INSERT INTO command

• Make comments on the following
• INSERT INTO Employee (Cecilia, F, Kolonsky,
  677678989, 05-APR-50, 6357 Windy Lane,
  Katy, TX F, 28000, null, 5)
• INSERT INTO Employee (Alicia, J, Zelaya,
  999887777, 05-APR-50, 6357 Windy Lane, Katy,
  TX, F, 28000, 987654321, 1)
• INSERT INTO Employee (Cecilia, F, Kolonsky,
  null, 05-APR-50, 6357 Windy Lane, Katy, TX F,
  28000, null, 4)
• INSERT INTO Employee (Cecilia, F, Kolonsky,
  677678989, 05-APR-50, 6357 Windswept, Katy,
  TX 28000, 987654321, 7)

25
Update ..

• 5. Editing current table values
• UPDATE student SET surname Kostner
  WHERE stud_id 123Consider the following
  situations
• Update the SALARY of the EMPLOYEE tuple with SNN
  999887777 to 28000
• Update the DNO of the EMPLOYEE tuple with SNN
  999887777 to 1
• Update the DNO of the EMPLOYEE with SNN
  9998877777 to 7
• Update the SNN of the EMPLOYEE tuple with SNN
  999887777 to 987654321

26
Delete .

• 6. Deleting data meeting a specific criteria
• DELETE FROM student WHERE stud_id
  123
• Consider the following situations
• Delete the WORKS_ON tuple with ESSN
  9998877777 and PNO 10
• Delete the EMPLOYEE tuple with SNN 9998877777
• Delete the EMPLOYEE tuple with SSN 333445555

27

• 7. Not Saving changes / Saving changes
• ROLLBACK command or COMMIT command
• 8. Altering a tables structure
• All for the changing of the customer structure.
  Typically this would include
• Adding new columns note original entries will
  contain a NULL value for the added field unless
  specified.
• Deleting existing column careful doing this
• Changing the size of a field

28
Alter

• Examples of altering a tables structure
• eg.1 ALTER TABLE subjects ADD Grade CHAR (2)
• eg. 2 ALTER TABLE subject DELETE telex_no
• eg. 3 ALTER TABLE subject CHANGE COLUMN
  Lastname to CHAR (20)
• 9. Deleting a table DROP TABLE subject

29
Data Queries

• All based on the SELECT command together with
  required restrictions (WHERE) using
• mathematical operators (
• logical operators (AND, OR)
• other special operators (IS NULL, NOT NULL,
  BETWEEN, LIKE, IN( ), EXISTS)
• numeric functions (SUM, AVG, MAX, MIN)
• ordering
• grouping of data

30
Write down what you expect the following Data
Queries to do?

• 10. SELECT FROM student
• 11. SELECT surname, address, telephone FROM
  student
• 12. SELECT FROM student WHERE sex M

31
Write the SQL queries for the following

• 13. Select all records from the student table
  that contain NULL values in the course_id column
• 14. Display all student_id(s) that have a value
  in the course_id from the student table
• 13. SELECT FROM student WHERE course_id IS
  NULL
• 14. SELECT student_id FROM student WHERE
  course_id IS NOT NULL

32
The LIKE command

• The LIKE command can be used as part of the
  WHERE command for characters only e.g.
• WHERE name LIKE Atkin
• The following special characters can be used
• or ?- matches pattern of text any other text
• ? or the underscore_- matches the occurrence of
  a single character only
• 15. Write down what you expect the following SQL
  statement to return
• SELECT FROM student WHERE
  course_id IS NOT NULL AND subject
  LIKE C3??

33
The BETWEEN statement .

• The BETWEEN operator can be used with either
  TEXT or CHAR and finds all rows equal to, or
  between the lower and upper limits e.g.
• SELECT FROM customer WHERE lastname
  BETWEEN A and TZZZ
• or
• SELECT FROM customer WHERE overdue
  BETWEEN 1000 and 1000000
• How would we handle the following situation?
• Display all the following account codes 100, 101,
  104, 106, 108, 109, 113, 117 and 119

34
A similar situation arises with

• 16. Write the SQL command to display all details
  of students whose stud_Ids are either 1234, 5678
  6000, 7000 or 7890.
• SELECT FROM student WHERE (stud_id
  1234 OR stud_id 5678 OR stud_id
  6000 OR stud_id 7000 OR stud_id
  7890)

35
The IN command

• A similar situation arises for the following
• Write the SQL command to display all details of
  students whose stud_Ids are either 1234, 5678,
  6000, 7000 or 7890.
• The IN command it allows us to specify that a
  variable should have a variable which matches any
  one of a number from a list
• 17. SELECT FROM student WHERE stud_id IN
  (1234, 5678, 6000,7000,7890)

36
Consider the following .

• 18. Display all records from the student table
  doing the course BIT and who have stud_id of
  either 931 or 932
• 19. Display all records for students doing a
  level 3 ITC subject
• 18. SELECT FROM student WHERE (stud_id
  931 OR stud_id 932)
  AND course BIT
• 19. SELECT FROM student WHERE subject
  LIKE ITC3

37
More on ORDER BY and DISTINCT

• The ORDER BY command simply displays the output
  fields in a particular order.
• The DESC command after a particular field
  indicates that the output is displayed in
  descending order for that particular field.
• The DISTINCT command ensures that duplicates of
  particular records do not occur.

38
Order by .. and DESC

• From the student table display all records so
  that they are ordered by the surname and the
  firstname.
• 20. SELECT FROM student ORDER BY
  surname, firstname
• From the appliance table display model_no and
  condition in descending order of the model_no.
• 21. SELECT model_no, condition FROM
  appliance ORDER BY model_no DESC

39
DISTINCT

• As we know the primary key is unique however
  other fields may not be. The DISTINCT command
  can be used to stop duplicate rows being
  displayed in an output request. For example
• SELECT DISTINCT subjects
• FROM enrolments

40
Example of DISTINCT

• 22. SELECT model_num
• FROM appliance
• 23. SELECT DISTINCT model_numFROM appliance

41

• What does the following SQL command do?
• 24. SELECT stud_name AS students FROM
  student WHERE stud_id IN (SELECT
  distinct stud_id IN subject)
• This is an example of one way that we can link or
  join two tables together.

42
JOINS

• A JOIN is where we link one or more tables in a
  database together.
• For a JOIN to work we need to link the tables
  together with a common field. Typically this is
  the link between the primary and foreign keys.

43
A number of JOINs exist.

• Consider
• 25. SELECT FROM model, appliance WHERE
  model_code model_num
• This is a JOIN whereby the link between the two
  tables is based on the primary key in one and the
  foreign key in another table

44
Rule for combining TWO or more tables

• If you need to use TWO tables in a SELECT, then
  you need ONE condition to JOIN these tables.
• If you need to use THREE tables in a SELECT, then
  you need TWO conditions to JOIN the tables

45
Example

• 26. SELECT customer.name, date_hired, condition,
  mth_charge
• FROM customer, hire, appliance, model
• WHERE customer.csut_num hire.cust_num
• AND hire.stock_num appliance.stock_numAND
  appliance.model_num model.model_code

46
Aggregation

• Aggregation is the grouping of results together
  it summarises sections of data.
• The simplest aggregation is by using the COUNT
  command. The COUNT command will count the number
  of rows typically it is expressed as COUNT
  () although the could be expressed as an
  attribute however if the attribute contains a
  NULL value it will NOT be counted.

47
Examples of COUNT

• 27. SELECT COUNT () AS studentsFROM students
• 28. SELECT COUNT () as num_hires, COUNT
  (date_hired) AS Num_hires COUNT (date_ret)
  AS RETURNS FROM hire

48
GROUPING

• The GROUPing of information is typically
  associated with some mathematical operation
  (COUNT, SUM, MAX, AVG).
• The GROUP BY clause can only work if the
  attributes in it are contained in the SELECT
  clause and/or the mathematical function.
• The GROUP BY clause can use the ORDER BY clause
  and the HAVING clause.
• The HAVING clause is used to impose a condition
  on the group

49
GROUP BY

• GROUP BY
• 29. SELECT stud_id, count() FROM subject
  GROUP BY stud_id
• 30. SELECT stud_id, count() FROM subject
  GROUP BY stud_id HAVING count() 0

50
HAVING .

• The HAVING is a condition on an aggregate or a
  group.
• Typically the HAVING clause will include a number
  of aggregation functions e.g. COUNT, MIN, MAX etc

51
What is the catalog?

• Information about tables in the database is kept
  in the system catalog. The catalog
• is a self-maintaining relational database
• Basically a meta storage area e.g.
• Systables typically contains the column Name,
  Creator and Colcount
• Syscolumns typically contains Colname, Tbname
  and Coltype
• They can be queried like any relational table.

52
Examples of Systables and Syscolumns

• For each of the following queries write down the
  outcome
• 1. SELECT Name, Colcount FROM Systables
• 2. SELECT Colname, Tbname FROM Syscolumns
  WHERE Tbname Student
• 3. SELECT Name, Tbname FROM Systables

53
Other catalog command possible with some versions
of SQL

• SELECT sysdate Sysdate 15 Mar 2001
• SELECT sysddate Sysddate 20010315
• SELECT sysday Sysday 15
• SELECT sysmon Sysmon 3
• SELECT systime Systime 1236PM
• SELECT sysmin Sysmin 36

54

• DATES and TIMES - vary in each RDBMS
• eg. in MS Access uses the NOW( ) to return the
  current date
• SELECT now()
• FROM student
• 2nd eg.
• SELECT stud_id, date_start30 AS 30_days_later
• FROM student

55

• More on dates ......
• 27. SELECT date_start, year(date_start),
• month(date_start), day(date_start) FROM
  student
• 28. SELECT stud_id, date_start,
  day(sysdate) -day(date_start) AS
  HECS_days FROM student WHERE date_start
  is NOT NULL

56
Data Queries

• CHANGING COLUMN HEADING NAMES
• 29. SELECT stud_id AS student_id, lastname as
  surname FROM student
• 30. SELECT COUNT() AS total_students,
• COUNT (ass1) AS assignment_one,
• COUNT (DISTINCT dob) AS date_of_birth
• FROM student
• 31. SELECT SUM(ass1), AVG(ass1), MAX(ass1),
  MIN(ass1) FROM student

57
Views of the data

• A view is a virtual look at data with a table/s
  at a given point of time. A view can be
• A report
• A graph
• Views are used to restrict user access to the
  whole database they are a control mechanism
  basically a restricted window into the whole
  database.
• General Format
• CREATE VIEW test_example AS
• SELECT (firstname, lastname, age)
• FROM Student
• WHERE Class ITC114

58
How is a view used .

• Remember a VIEW is a virtual table once
  created it can be used as if it was a real
  table.
• Example
• SELECT FROM test_example WHERE age

59
VIEWS

• 32. CREATE VIEW student_results AS SELECT
  stud_id, lastname, subject, result FROM
  student WHERE ass1 50
• 33. CREATE VIEW student_results (Student_id,
  Surname, Subject, Result) AS SELECT stud_id,
  lastname, subject, result FROM student

60
What else can a view do?

• A VIEW
• can be created from two or more tables. This
  makes it a lot more transparent to the user
  effectively they are seeing additional table
• allows for many different views of the same data
• is a means of security
• can be deleted by using the DROP VIEW command

61
What are INDEXES?

• Is a means of creating an order to a table.
• It is a means to increase the efficiency of
  retrieving information from a database.
• It is a physical access and not a logical access
  later versions of SQL do not include the INDEX
  statement
• It is a huge overhead in that the DBMS must
  update the index when updates are made to the
  database.