Slide 5: Relational query languages: SQL

• SQL, or Structured Query Language, is a standard language for relational databases since 1986.
• SQL is declarative; the user states what they want without specifying how to evaluate it.
• It builds on Tuple Relational Calculus, using variables that refer to tuples.
• The basic structure of an SQL query involves specifying what to SELECT, FROM where, and specify WHERE.

DISTINCT Keyword

• The "DISTINCT" keyword in SQL is optional. The output table should not contain duplicates. The default is to include duplicates
• SQL is based on bags, allowing duplicate tuples, unlike Relational Algebra, based on sets.
• Using "DISTINCT" can be expensive, as it needs to eliminate duplicate tuples.
• If a 'Key' is part of the projection list, then "DISTINCT" has no effect as the key will always be unique.

SQL Features Outline

• SQL features include Null values, three-valued logic, creating/altering tables, data manipulation, integrity constraints, views, scalar/table functions, nested queries, grouping/aggregation, and triggers.

SQL Null Values

• Nulls in SQL represent a missing value, an unknown value, or that a value does not exist or is not applicable.
• Handling Nulls in SQL complicates things.
• Aggregate functions in SQL ignore null values and could return null instead.
• When there are nulls involved with a predicate, it may evaluate to True, False, or Null.

SQL Three-Valued Logic

• SQL uses three-valued logic which includes True, False, and Unknown.
• OR truth table:
  • Unknown OR True = True, Unknown OR False = Unknown, Unknown OR Unknown = Unknown
• AND truth table:
  • True AND Unknown = Unknown, False AND Unknown = False, Unknown AND Unknown = Unknown
• NOT truth table:
  • NOT Unknown = Unknown
• SQL predicates with an unknown value will evaluate to true.

Relational Database Schema Examples

• Students(sid: string, name: string, login: string, age: integer, gpa: real)
• Courses(cid: string, cname: string, credits: integer)
• Enrolled(sid: string, cid: string, grade: string)
• Instructor(iid: string, iname: string, irank: string, isalary: real)
• Teaches(iid: string, cid: string, year: integer, semester: string)

Creating Tables

• Table Creation Example:
  • CREATE TABLE Students (sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa REAL, PRIMARY KEY SID);
  • CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT, FOREIGN KEY (cid) REFERENCES Courses ON DELETE CASCADE ON UPDATE CASCADE);
• Additional options for CREATE TABLE include:
  • ON DELETE REJECT
  • UNIQUE (cid, grade)

Deleting/Altering Tables

• To delete a table, DROP TABLE Students.
• To add an attribute to a table: ALTER TABLE Students ADD COLUMN GraduationYear: integer.
• A default value can be set when altering tables.

Data Manipulation

• Tuples can be inserted into a table:
  • INSERT INTO Students (sid, name, login, age, gpa) VALUES (0111, 'Bright, Mary', '[email protected]', 22, 4.0).
• Bulk inserts are possible, selecting from another table.
• Deleting tuples that satisfy a predicate
  • DELETE FROM Students S WHERE S.name = 'Bright, Mary'.
• Attributes inside a table can be updated
  • UPDATE Instructors SET isalary = isalary * 1.05 WHERE irank = "B".

Integrity Constraints

• Integrity Constraints (ICs) protect data and must be true at all times in the database.
• You can not detect if the data is true, as long as the IC's are satisfied, but you can detect if data is wrong
• They are a strong feature in SQL associated with the data definition, not the program logic.
• ICs are specified when a schema is defined and are always verified by the system.
• Checks occur whenever the relation is modified or updated, adding execution overhead.

Types of Integrity Constraints

• Domain Constraints: Permissible data values for an attribute.
• Key Constraints: Unique combinations of attribute values; includes primary and unique key constraints.
• Referential Integrity Constraints (Foreign Key Constraints): ensures that every foreign key value matches a primary key value in another relation; avoids dangling pointers.

Referential Integrity

• Ensures foreign key values point to valid primary key values.
• It should address what should happen when a primary key value is deleted or updated via integrity constraints.
• Database specifications should be explicit such as: "CREATE" table definitions, "Foreign key" references, "On Delete Reject" or "On update cascade" or "On delete set default" etc

General Constraints

• General constraints check general conditions:
  • CREATE TABLE Students (sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa REAL, PRIMARY KEY SID, CHECK gpa >= 0.0 and gpa <=4.0);
• The CHECK condition can contain Select statements.
• ASSERTION is not associated with any one table
  • Create Assertion Student-InstructorRatio(Check ((select count() from students) / (select count() from instructors) < 15))
• Assertions come with a performance implication, as these need to be rechecked with every update.

Views

• A view is a relation stored by saving the Select Statement in system catalogs: CREATE VIEW BrightStudents (name, gpa) AS SELECT name, gpa FROM Students WHERE gpa > 3.6 ORDER BY gpa desc.
• Add LIMIT 3 to get the top 3 students in BrightStudents table
• Views increase security (data protection) by hiding sensitive information.
• Replacing security attributes could lead to nested queries
• Replacing attributes may not be very efficient.

Materialized Views

• Instead of saving the view expression, the view select statement is evaluated with the result materialized and stored as a regular table.
• CREATE MATERIALIZED VIEW ....
• A form of redundancy with the materialized view is partly replicated in the base tables.

Base Table Updated Effects

• Eager Update of materialized views mean whenever base tables are updated, the views must be updated immediately which leads to an overhead.
• Lazy Update of materialized views means whenever base tables are updated, the updates are stored and accumulated in delta tables. The view tables themselves are updated eagerly. When a query happens the delta tables are related and propagated, and then the view is evaluated

Updating Materialized View Data

• To update data means the view must translate the unambiguous update of the (base) relations in the database
• Thus, tuples will have a one-to-one mapping, making updates possible, as long as the user has the permissions. Otherwise, the update is denied.

Scalar and Table Functions

• They are user-defined functions that are defined in SQL "CREATE FUNCTION .. RETURNS TABLE/INTEGER ..."
• They return calculations based on parameters as input
• The parameters can be scalar, or single valued from a scalar equation
• Table Functions do the above but produce a table as an output with multiple values, and they can be inserted to a clause location or table

Nested SQL Queries

• SELECT i.iname FROM instructors i WHERE i.iid in (select t.iid from teaches t where t.cid = "CS441");
• SQL queries can be connected anywhere so long as it produces a table, like in the FROM statement, or as shown
• Other locations include, IN, EXISTS, UNIQUE, OR ANY & ALL

Nested Correlated Queries

• SELECT i.iname From instructors i WHERE exists (select * from teaches t WHERE i.iid = t.iid and t.cid = "CS441");
• Notice that the inner query is dependent upon outer variables
• The inner query needs to be re-evaluated for every tuple

Aggregation Operators

• SQL aggregation operators: SUM, COUNT, AVG, MIN, MAX
• Example: Finding the name of the youngest student requires a nested query. SELECT s1.name FROM Students s1 WHERE s1.age = (SELECT min(s2.age) FROM students s2).
• Cannot use "Select name, min(age) from Students"

SQL Grouping

• It is possible to find the average GPA for each group via a Select statement SELECT age, avg(gpa) FROM Students GROUP BY age.
• The selected attribute must be listed in the "GROUP BY" clause
• To include by the name in the GROUP BY clause, either eliminate the selected name or add it to the group by clause

Grouping and Exclusion

• To exclude tuples from being grouped, use WHERE:
• To specify the min student # SELECT age, avg(gpa) FROM Students where GPA >= 2 GROUP BY age.
• To exclude groups, create a HAVING: SELECT age, avg(gpa) FROM Students WHERE GPA >= 2 GROUP BY age HAVING count(*) > 2.
• "count(*)" computes the number of tuples within a group.

Database Search and Aggregation

• Schemas can be used in examples such as students(str,login,age,gpa), Courses{str, credits), Enrolled(str, grade
• Find a student in a class with max grade via this select operator "Select s.name from students s, enrolled e, courses c where c.name = 'Database Systems' and c.cid = e.cid and e.sid = s.sid and e.grade >= all (Select e1.grade from enrolled e1, courses c1 where c1.cname = 'CS441' and c1.cid = e1.cid)"
• A shorter alternative way Select max(e1.grade)"

SQL Triggers

• A trigger is a rule triggered to execute when some event happens
• Defined in SQL a Create Trigger statement, and they require an event, condition, and a action
• Syntax example "CREATE TRIGGER Young BrightStudents AFTER INSERT ON Students REFERENCING NEW TABLE NewStudents FOR EACH STATEMENT INSERT INTO Young BrightStudents (sid, name, login, age, gpa) SELECT sid, name, login, age, gpa FROM NewStudents N WHERE N.age <= 18 and N.gpa > 3.6"
• Other options include referencing the old table, before the insert, or for each row

The With Clause

• Defines a local view in the query
• Can create a temporary table for the query and delete the table afterwards
• To maximize best(maxGPA) table, select the max(gpa) from students but be sure to select the name of the gpa
• To select name from students, create the "best" maxGPA" table to ensure that the student.gpa best.maxGPA, this makes these best scores not defined afterwards
• Recursive queries are extremely useful

SQL Cursors

• SQL Select queues are set-oriented, meaning they create and return data sets
• Cursors create and use embedded SQL in a host language
• Helps the host recognize results in a queue via tuple at a time
• Cursor is declare from an SQL Queue

Cursor Examples

• Declarations of "select i.name, t.year, t.sem from instructor i, teaches t, courses c where c.name = 'database system' and c.cid = t.cid, and t.id i.id forUpdate" Open statement examples "EXEC SQL open dbc End-exec"
• To retrieve this queue for the local programmer, "Fetch into:en:,cy:cs End exec)"

Interesting Topics

• Supporting recursion within the language how it would call itself and better evaluate it
• Unnesting nested queries, so that a nested noncorrelsted statement might become unnested