SQL Date Calculations and Null Values in Database Management Systems

1. SQL Database Management SystemsChapter 5

2. Date Calculation • TimeStampDiff(unit, date_expr1, date_expre2) mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); - > -1 mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); -> 89

3. Data Calculations CREATE TABLESailorsNew (sid INTEGER, sname CHAR(30) NOT NULL, rating INTEGER, DOB DATE, CONSTRAINT StudentsKey PRIMARY KEY (sid)); SELECT sname, DOB, CURDATE( ), TIMESTAMPDIFF(YEAR, DOB,CURDATE( )) AS age FROM SailorsNew; SELECT sname, DOB, CURDATE( ), TIMESTAMPDIFF(YEAR, DOB,CURDATE( )) AS age FROM SailorsNew Order By sname;

4. Query Results

5. Extract Information from Date • Month(), Year(), DayOfMonth()

6. Null Values • Field values in a tuple are sometimes • Unknown : a rating has not been assigned or • Inapplicable: no spouse’s name for un-married person. • SQL provides a special value null for such situations. • The presence of null complicates many issues.

7. Comparisons Using Null Values • We need a 3-valued logic: true, false and unknown. • Is rating>8 true or false when rating is equal to null? • The answer is unknown. • Special operators to check if value is/is not null. • IS NULL returns true is the value is null. • IS NOT NULL returns false is the valule is null.

8. Logical Operator AND with One Argument as Null Value

9. Logical Operator OR with One Argument as Null Value

10. Logical Operator NOT with a Null Value

11. Null Value’s Impact on SQL Constructs • WHERE clause eliminates rows that don’t evaluate to true (false and null is eliminated). • When corresponding columns are either equal, or both contain null, two rows are regarded as duplicates. • Arithmetic operations +, -, *, and / all return null if one of their arguments is null. • COUNT (*) handles null values as other values. • Other aggregate operations ( SUM, AVG, MIN, MAX, and variations using DISTINCT) simply discard null values. • If it applies to only null values, the result is null.

13. Disallowing Null Values • We can disallow null values by specifying NOT NULL as part of field definition. • The fields in a primary key are not allowed as null. CREATE TABLEStudents (sid CHAR(20), name CHAR(30) NOT NULL, login CHAR(20), DOB Date, gpa REAL)

14. More About Create Table • Insert values with local data file CREATE TABLESailors (sid INTEGER, sname CHAR(30) NOT NULL, rating INTEGER, age REAL, CONSTRAINT StudentsKey PRIMARY KEY (sid)); Load Data Local Infile '~/Sailors.txt' Into Table Sailors; NULL 1 Tom Hanks 11 25 2 Tom Cruise \N 26 3 Hello K 10 30 Sailors.txt Tab Key \t

15. Clone or Copy a Table • Clone a table • Copy a table • Copy part of a table • Rename a table CREATE TABLESailorsAnother LIKE Sailors; CREATE TABLESailorsThird Select * FROM Sailors; CREATE TABLESailorsFourth Select sid, sname FROM Sailors; ALTER TABLESailors RENAME SailorsFifth;

16. Integrity Constraints (Review) • An IC describes conditions that every legal instance of a relation must satisfy. • Inserts/deletes/updates that violate IC’s are disallowed. • Can be used to ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 100) • Types of IC’s: Domain constraints, primary key constraints, foreign key constraints, general constraints.

17. Constraints over a Single Table Mysql does not support ! • Table Constraints • CHECKconditional-expression CREATE TABLESailors (sid INTEGER, sname CHAR(30) NOT NULL, rating INTEGER, age REAL, CONSTRAINT StudentsKey PRIMARY KEY (sid), CHECK(rating >=1 AND rating<=10)) Rating must be an integer in the range of 1-10.

18. Constraints over a Single Table CREATE TABLEReserves (sid INTEGER, bid INTEGER, day DATE, FOREIGN KEY (sid) REFERENCES Sailors, FOREIGN KEY (bid) REFERENCES Boats, CONSTRAINT noInterlakeRes CHECK( ‘Interlake’ <> (SELECT B.bname FROM Boats B WHERE B.bid = Reserves.bid) ) ) Interlake boats cannot be reserved.

19. Triggers • Trigger: procedure that starts automatically if specified changes occur to the DBMS • Three parts: • Event (activates the trigger) • Condition (tests whether the triggers should run) • Action (what happens if the trigger runs)

20. Advantages of using SQL triggers • Check the integrity of data. • Catch errors in business logic in the database layer. • Run the scheduled tasks because the triggers are invoked  automatically before or after a change  is made to the data in the tables. • Audit the changes of data in tables.

21. Disadvantages of using SQL triggers • It only can provide an extended validation, not all validations. • Some simple validations have to be done in the application layer - JavaScript (client side) or JSP, PHP, ASP.NET, Perl, etc. (server side) • SQL triggers are invoked and executed invisibly from client-applications therefore it is difficult to figure out what happen in the database layer. • SQL triggers may increase the overhead of the database server.

22. Trigger Syntax CREATE TRIGGER trigger_name trigger_timetrigger_event ON tbl_name FOR EACH ROW trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE }

23. Association • Even data in tables are deleted or updated, if your statement does not use INSERT, DELETE or UPDATE statement to change data in a table, the triggers associated with the table are not invoked. • TRUNCATE TABLE statement removes all data of a table but does not invoke the trigger associated with that table. • Statements using the INSERT statement behind the scenes such as REPLACE statement or LOAD DATA statement will invoke the corresponding triggers associated with the table.

24. Trigger Example • Mysql> Delimiter / • Mysql> CREATE TABLE SailorThird LIKE Sailors/ • Mysql> CREATE TRIGGER SailorUpdate • BEFORE INSERT ON SailorThird • FOR EACH ROW • BEGIN • IF NEW.rating IS NULL THEN SET NEW.rating = 0; • ELSEIF NEW.rating <1 THEN SET NEW.rating = 1; • ELSEIF NEW.rating > 10 THEN SET NEW.rating = 10; • END IF; • END;/ • Mysql> Load Data Local Infile ‘~/SailorsNew.txt’ Into Table SailorsThird/ • Mysql> Delimiter ;

26. To Stop an Operation with Trigger Create Trigger ReservesUpdate Before Insert On Reserves For Each Row Begin If ‘Interlack’ = (Select B.bname From Boats B Where B.bid = New.bid) Then signal sqlstate ‘45000’; End If; End; • CREATE TABLEReserves • (sid INTEGER, • bid INTEGER, • day DATE, • FOREIGN KEY (sid) REFERENCES Sailors, • FOREIGN KEY (bid) REFERENCES Boats, • CONSTRAINT noInterlakeRes • CHECK( ‘Interlake’ <> • (SELECT B.bname • FROM Boats B • WHERE B.bid = Reserves.bid) • ) ) ;

27. Organize Triggers • In database Information_Schema, Table Triggers hold all information about triggers. SELECT * FROM information_schema.triggers WHERE trigger_schema = ‘DatabaseName’ AND trigger_name = ‘TriggerName’; SELECT * FROM information_schema.triggers WHERE  trigger_schema = 'database_name' AND event_object_table = 'table_name'; SHOWTRIGGERSFROM classicmodels WHERE `table` = 'employees';