南京大学：《计算机程序的构造和解释 Structure and Interpretation of Computer Programs》课程教学资源（PPT课件讲稿）23-SQL-I

Lecture 23 SQL I

Lecture 23 - SQL I

Declarative Programming

Declarative Programming

Programming Paradigms Up until now,we've been focused(primarily)on imperative programming. Imperative program contain explicit instructions to tell the computer how to accomplish something.The interpreter then executes those instructions Now,we'll learn about declarative programming,where we can just tell the computer what we want,instead of how we want it done.The interpreter then figures out how to accomplish that. Declarative programs are often specialized to perform a specific task, because they allow for repetitive computation to be abstracted away and for the interpreter to optimize its execution

Programming Paradigms Up until now, we’ve been focused (primarily) on imperative programming. Imperative program contain explicit instructions to tell the computer how to accomplish something. The interpreter then executes those instructions Now, we’ll learn about declarative programming, where we can just tell the computer what we want, instead of how we want it done. The interpreter then figures out how to accomplish that. Declarative programs are often specialized to perform a specific task, because they allow for repetitive computation to be abstracted away and for the interpreter to optimize its execution

Imperative vs.Declarative Suppose you're going to a restaurant for dinner and need a table Option 1: "First I need to find a table,so I'll look through every available table and pick the one in the best location that has enough seats for my group,then I'll need to find someone to wait on me and make sure I have enough menus,then..." Option 2: “Table for two!

Imperative vs. Declarative Suppose you’re going to a restaurant for dinner and need a table Option 1: “First I need to find a table, so I’ll look through every available table and pick the one in the best location that has enough seats for my group, then I’ll need to find someone to wait on me and make sure I have enough menus, then…” Option 2: “Table for two!

SQL

SQL

SQL Database Languages SQL is an example of a(declarative)language with interacts with a database management system(DBMS)in order to make data processing easier and faster It collects records into tables,or a collection of rows with a value for each column A row has a value for titude Longitude Name each column A column 38 122 Berkeley... has a name and a type 42 71 Cambridge 45 93 Minneapolis A table has columns and rows

SQL & Database Languages SQL is an example of a (declarative) language with interacts with a database management system (DBMS) in order to make data processing easier and faster It collects records into tables, or a collection of rows with a value for each column Latitude Longitude Name 38 122 Berkeley 42 71 Cambridge 45 93 Minneapolis A table has columns and rows A row has a value for each column A column has a name and a type

Tables in SQL CREATE TABLE cities AS SELECT 38 AS latitude,122 AS longitude,"Berkeley"AS name UNION SELECT 42, 71, "Cambridge" UNION SELECT 45, 93, "Minneapolis"; SELECT "west coast"AS region,name FROM cities WHERE longitude >115 UNION SELECT "other", name FROM cities WHERE longitude 115; Cities: Latitude Longitude Name Region Name 38 122 Berkeley west coast Berkeley 42 71 Cambridge other Minneapolis 45 93 Minneapolis other Cambridge

Tables in SQL CREATE TABLE cities AS SELECT 38 AS latitude, 122 AS longitude, "Berkeley" AS name UNION SELECT 42, 71, "Cambridge" UNION SELECT 45, 93, "Minneapolis"; Latitude Longitude Name 38 122 Berkeley 42 71 Cambridge 45 93 Minneapolis SELECT "west coast" AS region, name FROM cities WHERE longitude >= 115 UNION SELECT "other", name FROM cities WHERE longitude < 115; Cities: Region Name west coast Berkeley other Minneapolis other Cambridge

SQL Basics The SQL language varies across implementations but we will look at some shared concepts. A SELECT statement creates a new table,either from scratch or by taking information from an existing table .A CREATE TABLE statement gives a global name to a table. Lots of other statements exist:DELETE,INSERT,UPDATE etc... Most of the important action is in the SELECT statement. Full credit to John Today's theme: DeNero for the examples in today's lecture

SQL Basics The SQL language varies across implementations but we will look at some shared concepts. ● A SELECT statement creates a new table, either from scratch or by taking information from an existing table ● A CREATE TABLE statement gives a global name to a table. ● Lots of other statements exist: DELETE, INSERT, UPDATE etc… ● Most of the important action is in the SELECT statement. Today’s theme: Full credit to John DeNero for the examples in today’s lecture

Using SQL Can download SQL at https://sqlite.orq/download.html Just want to follow along or try out some examples?Go to sqlcs61a.org.It also has all of today's examples loaded. We'll go over how to run SQL code on your own computer in lab on Wednesday

Using SQL Can download SQL at https://sqlite.org/download.html Just want to follow along or try out some examples? Go to sql.cs61a.org. It also has all of today’s examples loaded. We’ll go over how to run SQL code on your own computer in lab on Wednesday

Selecting Value Literals A SELECT statement always includes a comma-separated list of column descriptions. A column description is an expression,optionally followed by AS and a column name. SELECT [expression]AS [name],[expression]AS [name],... SELECTing literals CREATEs a one-row table. The UNION of two SELECT statements is a table containing the rows of Eisenhower both of their results. SELECT "delano"AS parent,"herbert"AS child UNION Fillmore SELECT“abraham", "barack" UNION SELECT "abraham", "clinton" UNION SELECT "fillmore", "abraham" UNION Abraham Delano Grover SELECT "fillmore", "delano" UNION SELECT "fillmore", "grover" UNION SELECT "eisenhower", "fillmore"; Barack Clinton Herbert

Selecting Value Literals A SELECT statement always includes a comma-separated list of column descriptions. A column description is an expression, optionally followed by AS and a column name. SELECT [expression] AS [name], [expression] AS [name], ... ; SELECTing literals CREATEs a one-row table. The UNION of two SELECT statements is a table containing the rows of both of their results. SELECT "delano" AS parent, "herbert" AS child UNION SELECT “abraham”, "barack" UNION SELECT "abraham", "clinton“ UNION SELECT "fillmore", "abraham" UNION SELECT "fillmore", "delano" UNION SELECT "fillmore", “grover" UNION SELECT "eisenhower", "fillmore"; Eisenhower Fillmore Abraham Delano Grover Barack Clinton Herbert