Northwind2003 database

1. Northwind2003 database 1

2. Sakila database 2

3. MySQL server Relational database management program Administer relational database Update database information Extract information through queries Server-client architecture User authentication Competitors: Access, Oracle, PostgreSQL 3

4. MySQL client Personal program to access MySQL Command line interface HeidiSQL Preconfigured portable version fromcourse website 4

5. Starting HeidiSQL Uncompress it on your Desktop Connect to alcor.inf.unibz.it Type your unibz username and password To use it from outside unibz LAN, you need before to log in to VPN here https://vpn.scientificnet.org . Manually configuring HeidiSQL (not needed) Installing MySQL server on your computer (useful but not necessary) 5

6. Using HeidiSQL Database structure in the left window Commands executed and errors in the log window below Queries can be written in the Query tab and executed pressing F9 or Highlighting some commands and pressing F1 activates the SQL help 6

7. Using HeidiSQL USE {database}; or click in the left window You must have appropriate privileges: GRANT SELECT GRANT INSERT GRANT UPDATE GRANT DELETE GRANT ALTER No way to undo your changes!!! ROLLBACK; command does not work on most MySQL tables 7

8. A question which produces a temporary table SELECT fieldsFROM table; SELECT fieldsFROM tableWHERE conditionORDER BY fieldASC|DESC; Mathematical operations AND, OR, NOT and parentheses Virtual field: expression AS name Views CREATE VIEW name AS selectionquery; DROP VIEW name; Selection query

9. mathematical operators + - * / and comparisons = < > <= >= <> ROUND( ), ABS( ), EXP( ), SQRT( ), LOG( ) condition1AND condition2, condition1OR condition2, NOT condition fieldBETWEEN value1AND value2 IS NULL, IS NOT NULL fieldIN (list) fieldLIKE expression containing % or _ CURDATE(), DATE_ADD(date, INTERVAL numberDAY|MONTH|YEAR) YEAR(date), MONTH(date), DAY(date) DATEDIFF(date2,date1) To get difference in years: Approximate: ROUND(DATEDIFF(date2,date1)/365.25) Exact: YEAR(date2) - YEAR(date1) - ( DATE_FORMAT( date2, '%m%d' ) < DATE_FORMAT( date1, '%m%d' ) ) Selection query

10. Inner join SELECT fieldsFROM table1 INNER JOIN table2 ON field1 = field2; Namescontainingspacesmust be enclosedby grave accent` Values enclosed instead by apostrophe ’ Ambiguous field names use table.field tableAS shortname from now on you must use nickname Difference between WHERE and ON Cross join Multiple inner joins Joins

11. aggregates the records based on a GROUP BY instruction and returns one record per distinct value of GROUP BY fields If no GROUP BY is inserted, it aggregates everything SELECT function(field), grouping fields FROM tables with inner joins GROUP BY field; function is an aggregating function: Sum(field), Avg(field), Max(field), Min(field) Count(*), Count(DISTINCT field) Summary query

12. Conditions WHERE conditionfilters (and is written) before aggregation and thus must be used on fields which disappears after aggregation HAVING condition filters (and is written) after aggregation and thus must be used on aggregating functions fields which exist before and after aggregation, such as grouping fields, can be used in both conditions equivalently Do not select extra fields in summary queries! Summary query

13. Using HeidiSQL graphical interface TRUNCATE table; DELETE FROM tableWHERE condition; UPDATE tableSET field= valueWHERE condition; INSERT INTO table (fields) VALUES (values), …, (values); Exporting text data Fields’ delimiter Values’ encloser Importing text data Preparestructurebefore Modifying data 13

14. INT -2 billions to 2 billions TINYINT 0 or 1 DECIMAL(total number of digits, number of decimal digits) FLOAT non-exact real number CHAR (number of characters) fixed length text VARCHAR(maximum number of characters) variable length text TEXT very long text up to 60,000 characters ENUM( value, … , value ) Pay attention to numbers DATE time is supposed to be midnight, DATETIME Options NOT NULL, AUTO_INCREMENT, UNIQUE, DEFAULT defaultvalue,INDEX Primary key automatically has UNIQUEand NOT NULL and is an index Numerical codes are not numbers! Field types

15. CREATE TABLE table (fieldfield-typeoptions, …,PRIMARY KEY (field), INDEX(field),CHECK (condition)); DROP TABLE table; ALTER TABLE tableADD fieldfield-typeoptions; ALTER TABLE tableDROP field; ALTER TABLE tableADD PRIMARY KEY field; ALTER TABLE tableDROP PRIMARY KEY; Using MySQL graphical interface Steal code from another table Table

16. Create a blank database Build the tables Start from the tables on the “1” side Put appropriate types Put appropriate primary key Check the structure Fill in the tables Home exercise: Your library 16

17. Queries Build a query to show book title, author surname, publishing year and author birth date Build a query to show book title, author surname and publishing date only for German and French authors. Do not rewrite a new one. Modify easily the previous one adding another field with a condition and hiding it Build a query to show book title, author surname and publishing date only for those book published before 1930. Do not rewrite a new one. Modify the first one. Home exercise : Your library 17

18. Exercises Is there the book TITLE? Is there a book TITLE published after 1/1/2000? Which books AUTHOR has published? Improvements Put NOT NULL for appropriate fields Insert constraints for appropriate fields Build a structure for predefined values lists where appropriate Home exercise : Your library 18

19. Advanced queries. Build a query which: shows book title, author surname and publishing year only for those authors born before a date invented by you. shows book title, author surname and the age of the author when the book was published. counts the books for every author. calculates the average publishing year of each author calculates the average publishing year of each author considering only German and English authors. Home exercise : Your library 19

20. Build a blank database Build the tables Start from the tables on the “1” side Put appropriate types Put appropriate primary keys Check the structure Fill in the tables Home exercise : students and exams 20

21. Queries Build a query which displays, for every student, the list of his passed exams. Build a query which displays the list of passed exams and students’ last names, considering only who got a laude. Build a query which displays the student number (ordered from lowest to highest) of students who passed computer science. Home exercise : students and exams 21

22. Expand the database inserting information about professors and their exams Many to many relation  another extra table Put NOT NULL for appropriate fields Insert constraints for appropriate fields Build a structure for predefined values lists where appropriate Home exercise : students and exams 22

23. Advanced queries. Build a query which: shows the list of courses of professor Coletti. shows the list of exams of professor Coletti with the average grade that students obtain in the exam (considering, obviously, only the passed attempts) shows the list of professor Coletti’s exams with the number of students who got more that 24 Home exercise : students and exams 23