120 likes | 267 Views
CS 341 Programming Language Design and Implementation. Administrative Next homework (HW7) coming soon… Today: Declarative paradigm SQL. HW7. Q4. Declarative programming:.
E N D
CS 341 Programming Language Design and Implementation • Administrative • Next homework (HW7) coming soon… • Today: • Declarative paradigm • SQL HW7 Q4 CS 341 -- 10 Mar 2014
Declarative programming: Def: declarative programming is a paradigm where you express what you want, but not how — you leave the details of how to the system. CS 341 -- 10 Mar 2014
*THE* best example of declarative programming? • SQL • Structured Query Language • the language of relational databases… SELECT Title, PubDateFROMBooks WHERE Author = "Stephen King" ORDER BYPubDateDESC, Title ASC LIMIT10; CS 341 -- 10 Mar 2014
Netflix • Example: relational database • Netflix movie reviews… Movies table Reviews table CS 341 -- 10 Mar 2014
Databases are an incredibly important subject area • Databases store most of the world's data • Most businesses would cease to exist if their databases were lost… CS 341 -- 10 Mar 2014
Data DB Engine • Database management systems • MySQL, Microsoft SQL Server, Oracle, … • Two main components of a DBMS: • Data • Database engine for executing requests against the data request Select *from … ; CS 341 -- 10 Mar 2014
SQL is the language of modern databases The SQL language has 2 parts: DDL: Data Definition Language for creating database design: Create, Drop, etc. DML: Data Manipulation Language to access/modify data: Select, Insert, Delete, etc. Most business apps are referred to as "CRUD-style" apps — what does CRUD mean? CS 341 -- 10 Mar 2014
Netflix • DDL: • Creating tables in SQL CREATE TABLE [Movies] ( [MovieID] INTEGER PRIMARY KEY, [MovieName] TEXT ); CREATE TABLE [Reviews] ( [ReviewID] INTEGER PRIMARY KEY AUTOINCREMENT, [MovieID] INTEGER REFERENCES Movies(MovieID), [UserID] INTEGER, [Rating] INTEGER ); CS 341 -- 10 Mar 2014
DML: SQL action queries… • Inserting, updating, and deleting records INSERTINTO Movies(MovieID, MovieName) Values(1, 'When Harry Met Salley'); UPDATEMovies SET MovieName = 'When Harry Met Sally' WHERE MovieID = 1; DELETE FROM Reviews WHERE UserID = 666; CS 341 -- 10 Mar 2014
DML: SQL select queries, part 1 • Retrieving data… • Scalar queries return a single value • Example: • retrieve movie name based on movie id SELECT MovieName FROM Movies WHERE MovieID = 124; Despicable Me CS 341 -- 10 Mar 2014
DML: SQL select queries, part 2 • Non-scalar queries return a temporary, memory-based table • Example: • retrieve *all* reviews for a given movie id "TABLE" SELECT UserID, Rating FROM Reviews WHERE MovieID = 128; CS 341 -- 10 Mar 2014
DML: SQL select queries, part 3: • joining tables… • Example: • given a movie name, compute average rating for that movie + SELECT AVG(Rating) FROM Reviews INNER JOIN Movies ON Reviews.MovieID = Movies.MovieID WHERE Movies.MovieName = 'Finding Nemo'; 3.3487 CS 341 -- 10 Mar 2014