Relational Databases and SQL: Powerful, Fast, and Scalable Data Management

1. CSC 337 Lecture 20: Relational Databases and SQL

2. Relational databases • relational database: A method of structuring data as tables associated to each other by shared attributes. • a table row corresponds to a unit of data called a record; a column corresponds to an attribute of that record • relational databases typically use Structured Query Language (SQL) to define, manage, and search data

3. Why use a database? • powerful: can search it, filter data, combine data from multiple sources • fast: can search/filter a database very quickly compared to a file • big: scale well up to very large data sizes • safe: built-in mechanisms for failure recovery (e.g. transactions) • multi-user: concurrency features let many users view/edit data at same time • abstract: provides layer of abstraction between stored data and app(s) • many database programs understand the same SQL commands

4. Why use SQL? • Better for relational data • Still more popular Strong opinion about this? Let me know!

5. Database software • Oracle • Microsoft SQL Server (powerful) and Microsoft Access (simple) • PostgreSQL (powerful/complex free open-source database system) • SQLite (transportable, lightweight free open-source database system) • MySQL (simple free open-source database system) • many servers run "LAMP" (Linux, Apache, MySQL, and PHP) • Wikipedia is run on PHP and MySQL • we will use MySQL in this course

6. Example csc337simpsons database teachers students courses grades • to test queries on this database, use username csc337homer, password d0ughnut

7. Example csc337world database countries(Other columns: region, surface_area, life_expectancy, gnp_old, local_name, government_form, capital, code2) cities languages • to test queries on this database, use username csc337traveler, password packmybags

8. Example imdb database actors movies roles movies_genres movies_directors directors • also available, imdb_small with fewer records (for testing queries)

9. SQLbasics • SELECT name FROM cities WHERE id = 17; SQL INSERT INTO countries VALUES ('SLD', 'ENG', 'T', 100.0); SQL • Structured Query Language (SQL): a language for searching and updating a database • a standard syntax that is used by all database software (with minor incompatibilities) • generally case-insensitive • a declarative language: describes what data you are seeking, not exactly how to find it

10. The SQL SELECT statement • SELECT column(s) FROM table; SQL SELECT name, code FROM countries; SQL • the SELECT statement searches a database and returns a set of results • the column name(s) written after SELECT filter which parts of the rows are returned • table and column names are case-sensitive

11. The DISTINCT modifier • SELECT DISTINCT column(s) FROM table; PHP • eliminates duplicates from the result set SELECT language FROM languages; SQL SELECT DISTINCT language FROM languages; SQL

12. The WHERE clause • SELECT column(s) FROM table WHERE condition(s); SQL SELECT name, population FROM cities WHERE country_code = "FSM"; • WHERE clause filters out rows based on their columns' data values • in large databases, it's critical to use a WHERE clause to reduce the result set size • suggestion: when trying to write a query, think of the FROM part first, then the WHERE part, and lastly the SELECT part

13. More about the WHERE clause • WHERE column operator value(s) SQL SELECT name, gnp FROM countries WHERE gnp > 2000000; SQL • the WHERE portion of a SELECT statement can use the following operators: • =, >, >=, <, <= • <> : not equal • BETWEENminANDmax • LIKEpattern • IN (value, value, ..., value)

14. Multiple WHERE clauses: AND, OR • SELECT * FROM cities WHERE code = 'USA' AND population >= 2000000; • multiple WHERE conditions can be combined using ANDandOR

15. Approximate matches: LIKE • WHERE column LIKE pattern SQL SELECT code, name, population FROM countries WHERE name LIKE 'United%'; SQL • LIKE 'text%' searches for text that starts with a given prefix • LIKE '%text' searches for text that ends with a given suffix • LIKE '%text%' searches for text that contains a given substring

16. Sorting by a column: ORDER BY • ORDER BY column(s) SQL SELECT code, name, population FROM countries WHERE name LIKE 'United%' ORDER BY population; SQL • can write ASC or DESC to sort in ascending (default) or descending order: SELECT * FROM countries ORDER BY population DESC; SQL • can specify multiple orderings in decreasing order of significance: SELECT * FROM countries ORDER BY population DESC, gnp; SQL

17. Limiting rows: LIMIT • LIMIT number SQL SELECT name FROM cities WHERE name LIKE 'K%' LIMIT 5; SQL • can be used to get the top-N of a given category (ORDER BY and LIMIT) • also useful as a sanity check to make sure your query doesn't return 107 rows

18. Querying databases in Node.js • You will need to install the node package called mysql. • npm install mysql

19. Connecting to a database • var mysql = require('mysql'); • var con = mysql.createConnection({ • host: hostname, • database: databasename, • user: username, • password: password, • debug: "true" • }); • con.connect(function(err) { • if (err) throw err; • console.log("Connected!"); • });

20. Connecting to a Database Example • var mysql = require('mysql'); • var con = mysql.createConnection({ • host: "mysql.allisonobourn.com", • database: "csc337world", • user: "csc337traveler", • password: "packmybags", • debug: "true" • }); • con.connect(function(err) { • if (err) throw err; • console.log("Connected!"); • });

21. Querying a Database • var mysql = require('mysql'); • var con = mysql.createConnection({ • host: "mysql.allisonobourn.com", • database: "csc337world", • user: "csc337traveler", • password: "packmybags", • debug: "true" • }); • con.connect(function(err) { • if (err) throw err; • console.log("Connected!"); • con.query("SELECT * FROM cities WHERE name='london'", • function (err, result, fields) { • if (err) throw err; • console.log("Result: " + result[0]["name"]); • }); • });

22. Querying a Database Result • The result object returned by the query is a list of the rows that match the query. • Data for each column can be gotten by accessing the row at the column name. • result[0]["name"] from the last slide returns the name of the city in the first returned row.

23. HTML tables: <table>, <tr>, <td> • A 2D table of rows and columns of data (block element) <table> <tr><td>1,1</td><td>1,2 okay</td></tr> <tr><td>2,1 real wide</td><td>2,2</td></tr> </table> HTML output • table defines the overall table, tr each row, and td each cell's data • tables are useful for displaying large row/column data sets • NOTE: tables are sometimes used by novices for web page layout, but this is not proper semantic HTML and should be avoided

24. Table headers, captions: <th>, <caption> • <table> • <caption>My important data</caption> • <tr><th>Column 1</th><th>Column 2</th></tr> • <tr><td>1,1</td><td>1,2 okay</td></tr> • <tr><td>2,1 real wide</td><td>2,2</td></tr> • </table> HTML output My important data • th cells in a row are considered headers; by default, they appear bold • a caption at the start of the table labels its meaning

25. Styling tables • table { border: 2px solid black; caption-side: bottom; } • tr { font-style: italic; } • td { background-color: yellow; text-align: center; width: 30%; } output My important data • all standard CSS styles can be applied to a table, row, or cell • table specific CSS properties: • border-collapse, border-spacing, caption-side, empty-cells, table-layout

26. The border-collapse property • table, td, th { border: 2px solid black; } • table { border-collapse: collapse; } CSS • by default, the overall table has a separate border from each cell inside • the border-collapse property merges these borders into one

27. The rowspan and colspan attributes • <table> • <tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr> • <tr><td colspan="2">1,1-1,2</td> • <td rowspan="3">1,3-3,3</td></tr> • <tr><td>2,1</td><td>2,2</td></tr> • <tr><td>3,1</td><td>3,2</td></tr> • </table> HTML HTML • colspan makes a cell occupy multiple columns; rowspan multiple rows • text-align and vertical-align control where the text appears within a cell

28. Column styles: <col>, <colgroup> • <table> • <col class="urgent" /> • <colgroup class="highlight" span="2"></colgroup> • <tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr> • <tr><td>1,1</td><td>1,2</td><td>1,3</td></tr> • <tr><td>2,1</td><td>2,2</td><td>2,3</td></tr> • </table> HTML output • col tag can be used to define styles that apply to an entire column (self-closing) • colgroup tag applies a style to a group of columns (NOT self-closing

29. Don't use tables for layout! • (borderless) tables appear to be an easy way to achieve grid-like page layouts • many "newbie" web pages do this (including many UW CSE web pages...) • but, a table has semantics; it should be used only to represent an actual table of data • instead of tables, use divs, widths/margins, floats, etc. to perform layout • tables should not be used for layout! • tables should not be used for layout!! • TABLES SHOULD NOT BE USED FOR LAYOUT!!! • TABLES SHOULD NOT BE USED FOR LAYOUT!!!!

30. Designing a query • Figure out the proper SQL queries in the following way: • Which table(s) contain the critical data? (FROM) • Which columns do I need in the result set? (SELECT) • How are tables connected (JOIN) and values filtered (WHERE)? • Test on a small data set (imdb_small). • Confirm on the real data set (imdb). • Try out the queries first in the MySQL console. • Write the Node.js code to run those same queries. • Make sure to check for SQL errors at every step!!