Cerrar
What is database query languages?
Features of Database query language
What is Relational Algebra?
Relation Specific Operators
RESTRICT AND PROJECT
The RESTRICT Operator
Eg. EMPLOYEE (E#, Name, Age, Salary)
'Restrict to Employees whose age is less than 30'
The PROJECT Operator
Eg. EMPLOYEE (E#, Name, Age, Salary)
'List the names and salaries of all employees'
CARTESIAN PRODUCT Operator
NATURAL JOIN Operator
OUTER JOIN Operator
The DIVISION Operator
"Traditional" Set Operators
Union Compatibility
Union
Difference (or Minus)
Intersection
Aggregation and Grouping Operators
Eg. How many students are enrolled in the Semester 2, 2013 offering of ICT218?
What is SQL?
BASIC SQL SELECT
RESTRICT in SQL
Eg. “List the names of students with a StdGPA >= 3.0”
Standard Comparison Operators:
The WHERE clause can also contain:
Eg. “List the names of students with a GPA >= 3.0 and who are in the Games Tech major”
Other logical operators
Eg. “List the name of any student that does not have a GPA”
Eg. “List the names of students who are from Koondoola, Girrawheen, or Balga”
Pattern Matching
Eg. “List the unit code and title of units with Finance in their description”
Eg. Range Searching
“List the names and GPA of students with a GPA between 3 and 4”
Subqueries
Eg. “List the students who are currently studying ICT218”
Eg. “List the students that are NOT enrolled in any units in S2 2014”
SQL Joins
Example Join using JOIN operator
Eg. List the Names of students enrolled in ICT218 in Semester 2, 2014
JOINS based on PK/FK Equality
Eg. List the Names of students enrolled in ICT218 in Semester 2, 2014
Subqueries and Joins
Eg. “List the titles of units being offered in Semester 2 2014”
EXISTS and NOT EXISTS example
“Retrieve the StaffNumber, the name, school , and salary of staff members who are NOT students.”
Aggregate Functions
COUNT
AS
Aggregate Functions - WHERE
Instead, you need to use a subquery
GROUP BY
Eg. “List the minimum, maximum and average GPA for each of the majors”
GROUP BY - using HAVING
Eg. E.g. “List the majors and the average GPA of majors where the GPA is greater than 3.3”
Set Operators
UNION
INTERSECT
MINUS (Difference)
Eg. “List the students who have been enrolled in ICT231 but not ICT218”
DIVISION
Data Definition Language (DDL)
DDL
Eg. CREATE TABLE
Eg. CREATE TABLE ... AS
ALTER TABLE
Eg. 'Remove not null constraint for CustFirstName'
DROP TABLE
CONSTRAINTS
Required data constraint
Uniqueness
Entity integrity
Referential integrity
Referential Actions
Eg. Oracle allows you to specify only ‘on delete cascade’ in the constraint definition.
Data Manipulating Language (DML)
DML
INSERT
Eg. INSERT INTO TableName [(columnList)] VALUES (dataValueList);
INSERT rows from other tables
UPDATE
Eg. If no WHERE clause specified all rows are updated.
DELETE
Eg. If no WHERE clause if specified all rows are deleted.
