ONGD lecture slides

[blitzacesamy]

Table of Contents

• Fundamentals of Database Systems
• Constraint of Database
• SQL Basic
• Table Structures and Constraints
  Making Changes to Table Contents
• Advanced SELECT

Fundamentals of Database Systems

• Lesson Objectives
  • Introduce the concept of a database
  • Introduce the concept of a Database Management System (DBMS)
  • Describe some functions of a DBMS
  • Explain the components of Relational Database
• Uses of information in an organization
  • How many students own an iPhone in TP?
  • What are the subjects that Michael Teo is taking?
  • What are Michael Teo's favorite food?
  • What are Ricky's grades last semester?
• Purpose of storing information
  • Help people keep track of things like orders, customers, jobs, employees, phone calls, insurance claims, medical X-rays, etc.
  • Databases nowadays are also increasingly used in applications for the Internet, organizational intranets as well as extranets
  • Databases today store not just text, but hotos, images, audio-visual objects and other multimedia content
• What is a Database?
  • A collection of related facts
  • Hard disk full of data
  • Filing Cabinet
  • Library of books
  • Diary of records
• What is a Database Management System (DBMS)?
  • A (software) system for
    • maintaining data
    • answering queries about the data
  • Multiple billion dollar market for DBMS products and services
• DBMS + Database = Database System
  • 
    
• Functions of a DBMS
  • Defining the Database
  • Constructing the Database
  • Manipulating the Database
  • Controlling redundancy
  • Restricting unauthorized access
  • Inferencing using deductive rules
  • Providing multiple user interfaces
  • Representing complex relationships between data
  • Enforcing integrity constraints
  • Providing backup and recovery
• Defining the Database
  • Data types
  • Data structures
  • Basic constraints
    • Gender must be either "M" or "F"
• Manipulating the Database
  • Functions for querying the Database to retrieve specific data
    • Who are the students who study "ONGD" and live in postal code 4067?
  • Updating the Database
  • Generating reports
  • Ensuring correct results even when there are concurrent users of the database
• Data Model
  • An abstract view of data that excludes many details
  • Comprised of logical concepts, e.g., objects or entities, their properties and their interrelationships
  • May be easier for most users to understand
  • Hides computer storage details that are either too complex or not of interest to most users
• Types of Data Models
  • We will study the Relational Data Model now.
  • Very common for traditional applications
  • However, there are several others in active use in many deployed information systems
  • Examples: Network Model, Hierarchical Model, Object Model, Logic Model, Object-Relational Model
• Components of Relational Database
  • Schema
  • Instance
  • Relations
  • Domains
  • Tuples
  • Attributes
  • Constraints
• Database Schema
  • The description of the database
  • Specified when the database is first designed
  • Not expected to change frequently
  • Occasional changes required due to changes in requirements
  • Description of data, not the data itself
  • Metadata - data about data
• Instance
  • The data in the database at a particular moment in time
  • It is also known as database state or snapshot
  • Whenever we add, delete or modify data, the instance changes
  • When a database is newly defined, we only specify the schema to the Database Management System. At this point, the database is at an empty state until it is populated (loaded) with data
  • The database must always be in a valid state: i.e. all data in the database must satisfy the structure and constraints (rules) specified in the schema
• Relation
  • Basically, in a relational database, the relation is a table in the database; one part of the entire database
• Attributes and Domain
  • Attributes are the columns of each table
  • Each attribute has a domain
  • A domain is the set of possible values that an attribute can take
  • Each domain has a data type and size
  • Example:
    • The domain of attribute 'Name' is: a character string of up to 50 characters
    • The domain of attribute 'StudentNo' is: a character string of 8 characters
    • The domain of attribute 'GPA' is: a positive number of 3 digits with one decimal place
• Some Domain Types
  • Integers
  • Real numbers
  • Character strings
  • Constant
  • Date
  • Currency
  • Enumerated data type, eg {'Male', 'Female'}
• Tuples
  • The rows of a relation, other than the header row containing the attributes, are called tuples
  • A tuple has a value for each attribute of the relation
  • Example of a tuple in the Student relation
    • ('Alex Teo', '0302013T', 'M', '3.12')
• Summary
  • The purpose of information and database
  • What a DBMS is
  • Functions of a DMBS
  • Components of Relational Database

Constraint of Database

• Lesson objectives
  • Discuss the Primary Key Constraint
  • Discuss the Referential Integrity Constraint
• Database Integrity Constraints
  • Constraints are rules that the data in a database must obey at all times
  • Specified on a database schema
  • Apply on every instance of that schema
  • Must also hold on transitions of the schema
  • Some are enforced by the DBMS itself
  • Some are enforced using procedural logic (triggers)
• Some Relational Database Constraints
  • Domain Constraint - defined on attribute
  • Primary Key Constraint - defined on a single relation
  • Referential Integrity Constraint - defined between relations using the foreign key
• Domain Constraints
  • Each attribute value must be
    • atomic (atomic datatypes are those having values which are regarded by this specification as being indivisible, as compared to list datatypes, which have values each of which consists of a finite-length (possibly empty) sequence of values of an atomic datatype.)
    • from the domain of the attribute
• Primary Key
  • Primary Key (PK): Consists of one or more than one attribute that uniquely identifies each tuple in a relation
  • Each relation can have only one Primary Key
  • Primary Key must be unique
  • Underline the Primary Key to distinguish it from other non-key attributes
• Primary Key Constraint
  • The Primary Key value must be unique (i.e. no duplicate) and cannot be null
• Candidate Key
  • A candidate key is one or more than one attribute in a relation which uniquely identifies each tuple
  • A relation may have more than one candidate key
  • Only one candidate key is selected as the primary key
• Foreign Key
  • A foreign key is a column (or collection of columns) in a table whose value is required to match the value of the primary key for a second table
  • Foreign key is said to "reference" primary key
  • Foreign key can be null but if it holds a value, the value must already exist in the primary key attribute of the referenced table
  • Foreign key can be non-unique and null
• Referential Integrity Constraint
  • Referential Integrity is the rule that if table A contains a foreign key that matches the primary key of table B, the values of this foreign key must either match the value of the primary key for some row in table B or be null
  • Referential integrity constraints are specified between two relations
• Constraint Enforcement
  • Let's see there are two relations, Department and Employee
  • Attribute Department_Name in Employee relation is a foreign key that references attribute Department_Name in Department relation
  • The DBMS enforces constraints:
    • The user is not permitted to remove department from Department table if there are employees in it
    • The user is also not permitted to insert employees who do not belong to a department with data in the Department relation
• Constraints and Insertion
  • Integrity constraints can be violated by inserting a new tuple
    • Data with the same primary key already exists
    • Input is of incorrect data type as the attribute
  • The insert can be rejected, or the reason for rejection corrected
• Constraints and Deletion
  • Referential integrity can be violated if the tuple being deleted is referenced by foreign keys from other tuples
    • Deleting a department while there are still employees working in that department
  • The deletion can be rejected, cascaded (having the values in the other relation deleted along as well) or the referencing attribute values can be modified
• Constraints and Modification
  • Modifying non-key values: domain check
  • Modifying primary key: similar to performing a delete and an insert
  • Modifying foreign key: DBMS must ensure new value refers to existing tuple in referenced relation
• Constraints and Database Operation
  • Enforcement of integrity constraints ensure that the database remains consistent
  • Changes to the database must not violate integrity constraints (leave the database in an inconsistent state)
  • If a database update is submitted to the DBMS that would violate integrity, it must be refused
  • But, more constraints also imply more processing overheads (i.e. slower in processing)

SQL Basic

• Lesson Objectives
  • Understand what SQL is
  • Write and execute a basic SELECT statement
  • Limit the rows retrieved by a query
  • Sort the rows retrieved by a query
  • Use character, number and date functions in SELECT statements
  • Use Aggregate functions
• What is SQL?
  • Structured Query Language - a standard language for operating upon a relational database and its data
  • Supported on almost every relational database product, e.g. Oracle, DB2, Sybase, SQL/Svr
• SQL Basic SELECT
  • We will learn:
    • Basic SELECT Statement
      • Arithmetic Expressions
      • Null Values
      • Concatenation Operator
      • DISTINCT keyword
    • Limiting Rows
      • WHERE clause
      • Logical operators
    • Sorting Rows
      • ORDER BY clause
• Basic SELECT Statement
  • SELECT column-list
    FROM table-name;
  • Where
    1. column-list consists of one or more column names separated by commas. If all columns are to be included, then an asterisk (*) may be used for the list
    2. table-name is the name of the relation
  • The SELECT statement:
    • is not case-sensitive
    • can be on one or more lines
    • is terminated with a semi-colon (;) in Oracle
  • Example:
    • SELECT * FROM Subject;
• Column Aliases
  • Used to display improved column headings
  • Alias is listed immediately following the column name in the list (before the comma)
  • Default display is all capital letters
  • Use double quotes for different letter case usage and/or special characters
  • Having an AS is optional
  • Example:
    • SELECT Stud_no AS "Student Number", Name, Class_Code FROM Student;
• Arithmetic Expressions
  • Arithmetic operators can be used in any clause of a SQL statement except the FROM clause
  • Symbol Operation
    *
    Multiplication
    /
    Division
    +
    Addition
    -
    Subtraction
  • Order of Precedence
    • 1st, parentheses will be evaluated
    • 2nd, * and / are evaluated before + and -
    • 3rd, operators of same priority - evaluated from left to right
  • Example:
    • SELECT Stud_no, Sub_code, Marks * 2 FROM Score;
• NULL values
  • A NULL is a value that is unavailable, unassigned, unknown or inapplicable
  • It is not zero, blank or space
• DISTINCT Values
  • Duplicate rows are removed by using DISTINCT before the column name
  • Example:
    • SELECT DISTINCT Class_representative FROM Student;
  • Shows the class_representative's identification numbers with duplicate rows removed
• Limiting Rows
  • SELECT [DISTINCT] {* | column [alias] ,.....}
    FROM table
    [WHERE condition(s)];
  • WHERE restricts the query to rows that meet a condition
  • condition is composed of column names, expressions, constants and a comparison operator
  • Example:
    • SELECT Description
      FROM Subject
      WHERE subject_code = 'FNDB';
• Comparison Operators
  • Operator Meaning
    =
    Equal to
    != OR <>
    Not equal to
    >
    Greater than
    >=
    Greater than or equal to
    <
    Less than
    <=
    Less than or equal to
    BETWEEN ... AND ...
    Between two values (inclusive)
    IN (list)
    Match any of a list of values
    LIKE
    Match a character pattern (used for strings)
    IS NULL
    Is a null value
• Comparisons
  • The two expressions must be of the same type
  • Character string literals should be enclosed in single quotes
  • Date literals should be of the form 'DD-MON-YY'
  • Numeric literals should consist of digits, and optionally, a decimal and/or sign (no commas or dollar signs)
• The BETWEEN ... AND ... Operator
  • Example:
    • SELECT Student_number, Name, Date_of_birth
      FROM Student
      WHERE Date_of_birth BETWEEN '01-Jan-1983' AND '31-Dec-1983';
  • Is the same as
    • SELECT Student_number, Name, Date_of_birth
      FROM Student
      WHERE Date_of_birth >= '01-Jan-1983'
      AND Date_of_birth <= '31-Dec-1983';
• The IN Operator
  • The set of possible values is specified within parenthesis with commas between values
  • Example:
    • SELECT Student_number, Name
      FROM Student
      WHERE Student_number IN (123, 234, 345);
• The LIKE Operator
  • Used together with wildcard characters to make character string patterns
  • % is used to match zero or more characters
  • _ is used to match a single character
  • Wildcards cannot be used without the LIKE operator
  • The LIKE operator cannot be used without wildcards
  • Example: Retrieve student names whose second character is 'a'
    • SELECT Name
      FROM Student
      WHERE Name LIKE '_a%';
  • Example: Retrieve student names that contain the letter 'a'
    • SELECT Name
      FROM Student
      WHERE Name LIKE '%a%';
  • Note: Chong Alex is excluded since character strings are case-sensitive
• The IS NULL Operator
  • Any comparison to a null value that uses the standard comparison operators will not yield a match
  • If a check for null values is needed, the IS NULL operator must be used
  • Example:
    • SELECT Name
      FROM Student
      WHERE Mobile_number IS NULL;
• Logical Operators
  • Operator Meaning AND Returns a value if both component conditions are TRUE OR Returns a value if either or both component conditions are TRUE NOT Returns the opposite value of the expression
  • Order of Precedence
    • 1st - Parentheses, then * and /, then + and -
    • 2nd - NOT
    • 3rd - AND
    • 4rd - OR
  • Example: Retrieve student number, subject code and marks for students who score 65 marks or more in Computing Mathematics or Fundamentals of Database Systems
    • SELECT Student_number, Subject_code, Marks
      FROM Score
      WHERE Marks >= 65
      AND (Subject_code = 'FNDB'
      OR Subject_code = 'CM1');
    • Same as:
      • SELECT Student_number, Subject_code, Marks
        FROM Score
        WHERE Marks >= 65
        AND Subject_code IN ('FNDB, 'CM1');
  • Example
    • SELECT Name
      FROM Student
      WHERE Student_number NOT IN (123, 234, 345, 456);
• ORDER BY clause
  • SELECT [DISTINCT] {* | column [alias] , .....}
    FROM table
    [WHERE condition(s)]
    [ORDER BY {column, expression, } [ASC | DESC]];
  • ORDER BY specifies the order in which the retrieved rows are displayed
  • ASC orders the row in ascending order (default)
  • DESC orders the rows in descending order
  • If ORDER BY clause is specified, it must be placed last in the SELECT statement
  • Example: Retrieve the student name, date of birth, gender and class code of all male students in class C11B. List the youngest student first
    • SELECT Name, Date_of_birth, Gender, Class_code
      FROM Student
      WHERE Gender = 'M'
      AND Class_code = 'C11B'
      ORDER BY Date_of_birth DESC;
  • Ascending date: earliest to latest
  • Descending date: latest to earliest
  • Example:
    • SELECT Subject_code, Student_number, Marks * 0.5 AS Adjusted_Marks
      FROM Score
      ORDER BY Subject_code, Student_number, Adjusted_Marks;
    • Orders by subject code first, then student number, and finally adjusted marks (same subject code first, then student number, etc.)
• Date-to-Character Conversion
  • Example: Display the name and date of birth (re-label as birthdate) for student 345. Show the month in full and year as four digits
    • SELECT Name, TO_CHAR(Date_of_birth, 'DD-MONTH-YYYY') AS Birthdate
      FROM Student
      WHERE Student_number = 345;
• TO_CHAR Format Elements for Dates
  • Date Element Description (e.g. 25/7/2004, Sunday) YYYY Full year in numbers (2004) YY Last two digits of the year (04) MM Two-digit value for month (07) MON Name of month, three letter abbreviation (Jul) MONTH Name of month (July) DAY Full name of the day (Sunday) DD Day of the month (25) DY Name of day, three letter abbreviation (Sun) / - , ; . Punctuation as needed
• Date-to-Character Conversion
  • Example
    • SELECT Name, TO_CHAR(Date_of_birth, 'MON') AS "Birth Month"
      FROM Student
      WHERE TO_CHAR(Date_of_birth, 'MON') = 'JAN';
• INSERT Statement
  • Add new rows to a table by using the INSERT statement
  • Only one row is inserted at a time with this syntax
  • INSERT INTO table [(column [, column ...])]
    VALUES (value [, value ....]);
  • table is the name of the table to be inserted
  • column is the name of the column in the table to populate
  • value is the corresponding value for the column
• Inserting New Rows
  • INSERT INTO Score
    VALUES (123, 'FNDB', 76);
    • Sequence of columns follows the default order of the table
  • INSER INTO Score (Subject_code, Marks, Student_number)
    VALUES ('FNDB', 76, 123);
    • Sequence of columns follows the listed sequence of INSERT statement
  • INSERT INTO Score (Student_number, Subject_code)
    VALUES (456, 'FNDB');
    • Marks implicitly takes NULL value
  • INSERT INTO Score
    VALUES (456, 'FNDB', NULL)
    • Marks explicitly take NULL value
• The UPDATE Statement
  • UPDATE table
    SET column = value [, column = value, ...]
    [WHERE condition];
  • table is the name of the table
  • column is the name of the column in the table to modify
  • value is the corresponding value of sub-query for the column
  • condition identifies the rows to be updated - composed of column names, expressions, constants, sub-queries and comparison operators
• Updating Rows in a Table
  • UPDATE Score
    SET Marks = 70
    WHERE Student_number = 123
    AND Subject_code = 'CM1';
    • Specific row - only one, is modified
      (WHERE clause is specified)
  • UPDATE Student
    SET Class_representative = 234
    WHERE Class_code = 'C11B';
    • Specific row - several are modified
      (WHERE clause is specified)
  • UPDATE Score
    SET Marks = Marks * 2;
    • All rows are modified
      (WHERE clause is omitted)
• The DELETE Statement
  • DELETE [FROM] table
    [WHERE condition];
  • table is the name of the table
  • condition specifies the rows to be deleted - composed of column names, expressions, constants, sub-queries and comparison operators
• Deleting Rows from a Table
  
  • DELETE FROM Staff
    WHERE Staff_number = 'S4';
    • Delete specific row(s)
      (WHERE clause is specified)
  • DELETE FROM Score;
    • Delete all rows from the Score table
    • Empty Score table remains
    • WHERE clause is omitted
  • DELETE FROM Score
    WHERE Student_number =
    (SELECT Student_number
    FROM Student
    WHERE Name = 'Fang Yin'
    AND Class_code = 'T13C');
    • Delete rows based on values from another table

Table Structures and Constraints
Making Changes to Table Contents

• Objectives
  • After completing this lesson, you should be able to:
    • Create tables
    • Describe the data types to be used when specifying column definitions
    • Insert rows into a table
    • Update rows in a table
    • Delete rows from a table
    • Drop tables
• CREATE TABLE Statement
  • CREATE TABLE table_name
    (column datatype [DEFAULT expr] [column_constraint],
    .........,
    [table_constraint] [, .....]);
  • table_name is the name of the table
  • column is the name of the column
  • datatype is the column's data type and length
  • DEFAULT expr specifies a default value if a value is omitted in the INSERT statement
  • column_constraint is a rule defined for the column
  • table_constraint is a rule defined for the table
• Naming Conventions for table and column names
  • Must begin with a letter
  • Can be one to thirty characters long
  • Must contain only A to Z, a to z, 0 to 9, _, $ and #
  • Must not duplicate the name of another table
  • Must not be an Oracle Server reserved keyword
• Data Types
  • Data type Description VARCHAR2(size) Variable-length character data - size is the max size. Default is 1 (minimum) and 4000 (max) CHAR(size) Fixed-length character data of length size bytes. Default is 1 (minimum) and 2000 (maximum) NUMBER(p,s) Number with p total number of decimal digits, and s number of digits to the right of the decimal point DATE Date and time values LONG Variable-length character up to two gigabytes
• VARCHAR vs CHAR
  • VARCHAR2(8)
    • Variable-Length Column
    • BANANA (Storage required: 6 bytes)
    • APPLE (Storage required: 5 bytes)
  • CHAR(8)
    • Fixed-Length Column
    • BANANA (Storage required: 8 bytes)
    • APPLE (Storage required: 8 bytes)
• Numberic Type
  • NUMBER(8)
    • 12345678
  • NUMBER(8,2)
    • 123456.98
• Constraint Specification
  • Constraints can be used to ensure that entity integrity and referential integrity are maintained
  • A primary key constraint will prevent the insertion of primary key values that already exist, thus ensuring uniqueness and entity integrity
  • A foreign key constraint will prevent the appearance of foreign key values that do not have a corresponding matching primary key value in the related table
  • Constraints can be defined to ensure that certain business rules are followed
  • These are called check constraints, and some people refer to these as a means of maintaining database integrity in general
  • A not null constraint ensures that null values are not permitted for the column
• Type of column constraints
  • Underlined column means that the column is a primary key in its table
  • An arrow means that the column mist exist in one table as a primary key before it can exist in another table as a foreign key
• CREATE Table
  • CREATE TABLE Student
    (Student_number NUMBER(4) PRIMARY KEY,
    Name VARCHAR2(30),
    Class_representative NUMBER(4),
    Address VARCHAR2(30),
    Date_of_birth DATE NOT NULL,
    Telephone_number NUMBER(8),
    Mobile_number NUMBER(8),
    Gender VARCHAR2(1) NOT NULL,
    Class_code VARCHAR2(4));
  • CREATE TABLE Score
    (Student_number NUMBER(4),
    Subject_code VARCHAR2(4),
    Marks NUMBER(3),
    CONSTRAINT score_key_pk PRIMARY KEY (Student_number, Subject_code),
    CONSTRAINT student_score_fk FOREIGN KEY (Student_number) REFERENCES Student (Student_number),
    CONSTRAINT subject_score_fk FOREIGN KEY (Subject_code) REFERENCES Subject (Subject_code),
    CONSTRAINT marks_ck CHECK (Marks BETWEEN 0 AND 100));
    • score_key_pk is the name of the PRIMARY KEY constraint
    • student_score_fk is the name of the 1st FOREIGN KEY constraint
    • subject_score_fk is the name of the 2nd FOREIGN KEY constraint
    • marks_ck is the name of the CHECK constraint
  • Recommendations:
    • Suffixes Constraint ......_pk Primary key ......_fk Foreign key ......_ck Check
• Adding a Constraint
  • ALTER TABLE table
    ADD [CONSTRAINT constraint] type (column);
  • To add a constraint to an existing table
  • table is the name of the table
  • constraint is the name of the constraint
  • type is the constraint type
  • column is the name of the column affected by the constraint
  • Example: Add a FOREIGN KEY constraint to the Score table indicating that a student must already exist as a valid student in the Student table
    • ALTER TABLE Score
      ADD CONSTRAINT student_score_fk FOREIGN KEY (Student_number) REFERENCES Student (Student_number);
  • Example: Add a CHECK constraint to the Student table to ensure that gender column should either be 'M' for male or 'F' for female
    • ALTER TABLE Student
      ADD CONSTRAINT gender_ck CHECK (Gender IN ('M', 'F'));
• Dropping a Constraint
  • To find out the constraints created:
    • SELECT * FROM USER_CONSTRAINTS;
    • SELECT * FROM USER_CONS_COLUMNS;
  • To remove a constraint:
    • ALTER TABLE Score
      DROP CONSTRAINT student_score_fk;
• CREATE and INSERT together
  • Example: Create table Section_Staff by copying from the table Staff
    • CREATE TABLE Section_staff
      AS SELECT * FROM Staff;
      • Staff is an existing table
      • Section_Staff table will be newly created and all data from Staff will be copied over to it
      • Use this command to duplicate tables

Advanced SELECT

• Lesson Objectives
  • After completing this lesson, you should be able to:
    • Use Aggregate functions
    • Write SELECT statement to access data from more than one table using joins
    • Group data using the GROUP BY clause
    • Use the HAVING clause
• Aggregate Functions
  • Functions can be used to calculate and return summary information
  • Examples: COUNT, SUM, AVG, MAX and MIN
  • In each case, the function name is followed by a parameter enclosed in parenthesis
  • Aggregate functions operate on sets of rows to give an aggregated result
  • These sets may be the whole table or the table split into sets
• Types of Aggregate Functions
  • COUNT({*|DISTINCT|ALL|expr})
  • MAX([DISTINCT|ALL|expr])
  • MIN([DISTINCT|ALL|expr])
  • SUM([DISTINCT|ALL|n])
  • AVG([DISTINCT]|ALL|n)
  • DISTINCT makes the functions consider only non-duplicates
  • ALL makes the function consider every value including duplicates - default
  • All functions except COUNT(*) ignore null values
• AVG, MAX, MIN and SUM
  • Example: Find the average, maximum, minimum and sum of all marks for the FNDB subject
    • SELECT AVG(Marks), MAX(Marks), MIN(Marks), SUM(Marks)
      FROM Score
      WHERE Subject_code = 'FNDB';
• COUNT(*)
  • Example: Find out how many students are there in class C11B
    • SELECT COUNT(*)
      FROM Student
      WHERE Class_code = 'C11B';
  • Count ALL rows even if some columns have null values
• COUNT(column-name)
  • COUNT(column-name) does not count rows in which they have null values for that particular column
• COUNT(DISTINCT column-name)
  • If a data for the column appears more than once, count it only once
• What is a Join?
  • The ability to join related tables is one of the most important features of relational database management systems
  • A relationship between two tables is implemented by adding a "relationship link" called a foreign key to one of the tables
  • This foreign key is a column that contains values that match primary key values in the related table
• Syntax of equijoin
  • General syntax for joining two tables (equijoin):
    • SELECT column-list
      FROM table1, table2
      WHERE table1.primary_key_column = table2.foreign_key_column;
    • equijoin - a type of join based on equal values in related tables. There are other types of joins
• Joining two tables
  • Example: Find the Names of students and the Staff_number of the staff looking after the students
  • We need to access both the Student and Class tables
  • The common column in both tables is Class_code (Class_code is a foreign key in Student table referencing to Class table Class_code column)
  • For equijoin, values of Class_code in Student and Class must be equal
  • To join n tables, we will need at least (n - 1) join conditions, i.e. join 2 tables with 1 condition, join 3 tables with 2 conditions, etc.
• Joining of two Tables
  • SELECT Staff_no, Name, S.Class_code, C.Class_code
    FROM Student S, Class C
    Where S.Class_code = C.Class_code;
  • S is the alias for Student table
  • C is the alias for Class table
  • S.Class_code = C.Class_code is the Join Condition between Class_code of Student table and Class_code of Class table
  • Retrieve from two tables, so require one join condition
  • Since Class_code is common to both tables, it must be prefixed by the table name to avoid ambiguity
• Common Error: Omission of Condition
  • SELECT Student_number, Name, S.Class_Code, C.Class_code
    FROM Student S, Class C;
  • Without a join condition, nine rows from Student table multiply by three rows from Class table results in twenty-seven rows
• Joining More Than Two Tables
  • Techniques used to join two tables can be extended to join more than two tables
  • List all tables on the FROM clause
  • Match the primary keys and foreign keys on the WHERE clause
• Joining Three Tables
  • Example: Find the Staff_no of Staff whose Students scored 23 marks in FNDB
    • Staff_no of Staff is found in Class table
    • FNDB = 23 marks is found in Score table
    • However, these two tables do not have common columns
    • Hence we need to access Student table
  • SELECT Staff_number
    FROM Class C, Student S, Score M
    WHERE M.Stud_no = S.Stud_no
    AND S.Class_code = C.Class_code
    AND Subject_code = 'FNDB'
    AND Marks = 23;
• Grouping Rows
  • Rows can be arranged temporarily and arranged in group of like values
  • Purpose: Apply the aggregate functions to the groups
  • The GROUP BY clause is used to specify the column to use for the grouping
  • General syntax:
    • SELECT [column,] group_function(column)
      FROM table
      [WHERE condition]
      [GROUP BY column]
      [ORDER BY column];
• GROUP BY clause
  • Example:
    • SELECT Class_code, COUNT(*)
      FROM Student
      GROUP BY Class_code;
  • Example:
    • SELECT COUNT(*)
      FROM Student
      GROUP BY Class_code
  • The GROUP BY column does not need to be in the SELECT clause.
  • However the result is not meaningful without it
• Groups Within Groups
  • Two or more columns may be specified on the GROUP BY clause
  • Rows will initially be sorted into groups of like values in the first column
  • Each of those groups will then be sorted into subgroups of like values in the second column
• GROUPing BY more than 1 column
  • Example: Find the number of students in each gender in each class
    • SELECT Class_code, Gender, Count(*)
      FROM Student,
      GROUP BY Class_code, Gender;
  • First, the rows are grouped by Class_code
  • Second, the rows are grouped by Gender
  • Then COUNT function is applied for the same gender within each class
• The HAVING Clause
  • The HAVING clause can be added after the GROUP BY clause to specify the inclusion only of groups that meet a certain condition
  • The HAVING clause applies to groups in the same manner that the WHERE clause applies to rows
  • General syntax: HAVING condition, where the condition is a logical expression of the same format as that used on the WHERE clause
• Having Clause
  • Example:
    • SELECT Class_code, COUNT(*)
      FROM Student
      GROUP BY Class_code
      HAVING COUNT(*) >= 5;
• Order of Specification
  • SELECT
  • FROM
  • [WHERE]
  • [GROUP BY]
  • [HAVING]
  • [ORDER BY]
  • [] denotes optional clause
• Order of Evaluation
  1. Tables listed on FROM clause are copied to temporary storage
  2. Rows that do not satisfy condition listed on WHERE clause are eliminated
  3. Rows are rearranged and grouped by like values as specified on GROUP BY clause
  4. Groups that do not satisfy condition listed on HAVING clause are eliminated
  5. Group functions are applied as specified on SELECT clause
  6. Results are displayed in order specified on ORDER BY clause