Declarative Languages columns and rows name and a type 45 93 - PowerPoint PPT Presentation

Database Management Systems Declarative Programming Cities: Database management systems (DBMS) are important, heavily used, and interesting! In declarative languages such as SQL & Prolog: • A "program" is a description of the desired result latitude longitude name A table is a collection of records, which are rows that have a value for each column • The interpreter figures out how to generate the result 38 122 Berkeley In imperative languages such as Python & Scheme: 42 71 Cambridge A table has A column has a Latitude Longitude Name • A "program" is a description of computational processes Declarative Languages columns and rows name and a type 45 93 Minneapolis 38 122 Berkeley • The interpreter carries out execution/evaluation rules region name A row has a value 42 71 Cambridge create table cities as for each column 45 93 Minneapolis select 38 as latitude, 122 as longitude, "Berkeley" as name union west coast Berkeley select 42, 71, "Cambridge" union other Minneapolis select 45, 93, "Minneapolis"; other Cambridge The Structured Query Language (SQL) is perhaps the most widely used programming language select "west coast" as region, name from cities where longitude >= 115 union SQL is a declarative programming language select "other", name from cities where longitude < 115; 4 5 SQL Overview Getting Started with SQL The SQL language is an ANSI and ISO standard, but DBMS's implement custom variants Install sqlite (version 3.8.3 or later): http://sqlite.org/download.html • A select statement creates a new table, either from scratch or by projecting a table Use sqlite online: code.cs61a.org/sql • A create table statement gives a global name to a table • Lots of other statements exist: analyze , delete , explain , insert , replace , update , etc. • Most of the important action is in the select statement Structured Query Language (SQL) Today's theme: 7 8 http://awhimsicalbohemian.typepad.com/.a/6a00e5538b84f3883301538dfa8f19970b-800wi Selecting Value Literals Naming Tables A select statement always includes a comma-separated list of column descriptions SQL is often used as an interactive language A column description is an expression, optionally followed by as and a column name The result of a select statement is displayed to the user, but not stored select [expression] as [name] , [expression] as [name] ; , ... A create table statement gives the result a name Selecting literals creates a one-row table Parents: create table [name] as [select statement]; E isenhower E isenhower The union of two select statements is a table Projecting Tables Parent Child containing the rows of both of their results create table parents as abraham barack select "delano" as parent, "herbert" as child ; union select "delano" as parent, "herbert" as child union F illmore F illmore abraham clinton select "abraham" , "barack" union select "abraham" , "barack" union delano herbert select "abraham" , "clinton" union select "abraham" , "clinton" union fillmore abraham select "fillmore" , "abraham" union select "fillmore" , "abraham" union A braham D elano G rover A braham D elano G rover fillmore delano select "fillmore" , "delano" union select "fillmore" , "delano" union select "fillmore" , "grover" union select "fillmore" , "grover" union fillmore grover B arack C linton H erbert B arack C linton H erbert select "eisenhower" , "fillmore"; select "eisenhower" , "fillmore"; eisenhower fillmore 9 10

Select Statements Project Existing Tables Arithmetic in Select Expressions A select statement can specify an input table using a from clause In a select expression, column names evaluate to row values A subset of the rows of the input table can be selected using a where clause Arithmetic expressions can combine row values and constants An ordering over the remaining rows can be declared using an order by clause Column descriptions determine how each input row is projected to a result row create table lift as select 101 as chair, 2 as single, 2 as couple union Arithmetic select 102 , 0 , 3 union select [expression] as [name], [expression] as [name], ... ; E isenhower select 103 , 4 , 1; order by [order] select [columns] ; from [table] where [condition] F illmore select chair, single + 2 * couple as total from lift; select child from parents where parent = "abraham"; 101 select parent from parents where parent > child; chair total A braham D elano G rover 102 101 6 Child Parent 102 6 barack fillmore 103 B arack C linton H erbert 103 6 clinton fillmore (Demo) 12 14 Discussion Question Reminder: John the Patriotic Dog Breeder Given the table ints that describes how to sum powers of 2 to form various integers create table ints as select "zero" as word, 0 as one, 0 as two, 0 as four, 0 as eight union select "one" , 1 , 0 , 0 , 0 union select "two" , 0 , 2 , 0 , 0 union select "three" , 1 , 2 , 0 , 0 union select "four" , 0 , 0 , 4 , 0 union Parents: select "five" , 1 , 0 , 4 , 0 union E isenhower Joining Tables Parent Child select "six" , 0 , 2 , 4 , 0 union select "seven" , 1 , 2 , 4 , 0 union CREATE TABLE parents AS abraham barack select "eight" , 0 , 0 , 0 , 8 union select "nine" , 1 , 0 , 0 , 8; SELECT "abraham" AS parent, "barack" AS child UNION F illmore abraham clinton (A) Write a select statement for a two-column (B) Write a select statement for the SELECT "abraham" , "clinton" UNION delano herbert table of the word and value for each integer word names of the powers of two SELECT "delano" , "herbert" UNION fillmore abraham word value word SELECT "fillmore" , "abraham" UNION A braham D elano G rover zero 0 one fillmore delano SELECT "fillmore" , "delano" UNION two one 1 fillmore grover SELECT "fillmore" , "grover" UNION two 2 four B arack C linton H erbert three 3 eight SELECT "eisenhower" , "fillmore"; eisenhower fillmore ... ... (Demo) 15 ! 4 Joining Two Tables Joining a Table with Itself Two tables A & B are joined by a comma to yield all combos of a row from A & a row from B Two tables may share a column name; dot expressions and aliases disambiguate column values CREATE TABLE dogs AS SELECT [columns] FROM [table] WHERE [condition] ORDER BY [order]; SELECT "abraham" AS name, "long" AS fur UNION E E SELECT "barack" , "short" UNION [table] is a comma-separated list of table names with optional aliases SELECT "clinton" , "long" UNION SELECT "delano" , "long" UNION Select all pairs of siblings SELECT "eisenhower" , "short" UNION Aliases and Dot Expressions SELECT "fillmore" , "curly" UNION F F SELECT a.child AS first, b.child AS second SELECT "grover" , "short" UNION FROM parents AS a, parents AS b SELECT "herbert" , "curly"; WHERE a.parent = b.parent AND a.child < b.child; CREATE TABLE parents AS SELECT "abraham" AS parent, "barack" AS child UNION A D G A D G SELECT "abraham" , "clinton" UNION First Second ...; barack clinton Select the parents of curly-furred dogs abraham delano B C H B C H abraham grover SELECT parent FROM parents, dogs delano grover WHERE child = name AND fur = "curly"; ! 5 ! 7 (Demo)