SQL Basics: An Introduction to Structured Query Language, Slides of Introduction to Database Management Systems

Download SQL Basics: An Introduction to Structured Query Language and more Slides Introduction to Database Management Systems in PDF only on Docsity!

SQL: the Basics

David Toman

School of Computer Science University of Waterloo

Introduction to Databases CS

SQL

• (^) Structured Query Language ) Developed in IBM Almaden (system R)
• (^) Based on ) (Conjunctive) queries in Relational Calculus ) Set/Bag semantics and operations ) Aggregation
• (^) BAG SEMANTICS (next time)
• A committee design ) choices often more “pragmatic” than “logical” ) several standard versions: SQL/89, SQL/92 = SQL2, SQL3,...

SQL (cont.)

Three major parts of the language: 1 DML (Data Manipulation Language) ) Query language ) Update language 2 DDL (Data Definition Language) ) defines schema for relations ) creates (modifies/destroys) database objects. 3 DCL (Data Control Language) ) access control

Also: Embedded SQL (SQL/J) and ODBC (JDBC) ) necessary for application development

Roadmap to SQL Queries

• the “select block”
  • simple select-from-where
  • subqueries in the “from” clause
  • grouping, aggregation, and having clauses
  • duplicates and “distinct”
  • (^) subqueries in the “where” clause
  • (^) ordering the output
• (^) set operations
  • (^) with duplicates
• naming queries and views

Sample Database Revisited

author(aid integer, name char(20)) wrote(author integer, publication char(8)) publication(pubid char(8), title char(70)) book(pubid char(8), publisher char(50), year integer) journal(pubid char(8), volume integer, no integer, year integer) proceedings(pubid char(8), year integer) article(pubid char(8), crossref char(8), startpage integer, endpage integer)

... SQL is NOT case sensitive.

The “SELECT Block”

Basic syntax:

SELECT DISTINCT FROM WHERE

• Allows formulation of conjunctive ( 9 ; ^) queries ) a conjunction of and ) attributes not in existentially quantified ) specifies values in the resulting tuples
• many other clauses to follow later...

Naming Attributes

• problem: what if two relations use the same attribute name? ) publication(pubid, ...) ) book(pubid, ...)
• (^)... and we want to get, e.g., titles of all books

9 p; x ; y:publication(p; n) ^ book(p; x ; y)

) we prefix the ambiguous attributes names by the name of the appropriate relation:

• (^) publication.pubid (first “p”)
• (^) book.pubid (second “p”)

Example

List titles of all books:

SQL> select distinct title 2 from publication, book 3 where publication.pubid=book.pubid;

TITLE

Logics for Databases and Information Systems

Example

List all publications with at least two authors:

SQL> select distinct r1.publication 2 from wrote r1, wrote r 3 where r1.publication=r2.publication 4 and r1.author!=r2.author;

PUBLICATION

ChSa ChTo ChTo98a

The "FROM" Clause (summary)

Syntax: FROM R 1 [ n 1 ]; : : : ; Rk [ nk ]

• (^) Ri are relation (table) names
• ni are distinct identifiers
• (^) the clause represents a conjunction R 1 ^ : : : ^ Rk ) all variables of Ri ’s are distinct ) we use (co)relation names to resolve ambiguities
• can NOT appear alone ) only as a part of the select block

Standard Expressions

we can create values in the answer tuples using built-in functions:

• (^) on numeric types: +; ; ; =; : : : (usual arithmetic)
• on strings: jj (concatenation), substr,...
• constants (of appropriate types) "SELECT 1" is a valid query in SQL/
• UDF (user defined functions)

Note: all attribute names MUST be “present” in the FROM clause.

Example

For every article list the number of pages: SQL> select pubid, endpage-startpage+ 2 from article;

PUBID ENDPAGE-STARTPAGE+

---

ChTo98 40 ChTo98a 28 Tom97 19

Example

and name the resulting attributes id,numberofpages: SQL> select pubid as id, 2 endpage-startpage+1 as numberofpages 3 from article;

ID NUMBEROFPAGES

---

ChTo98 40 ChTo98a 28 Tom97 19

The "WHERE" Clause

Additional conditions on tuples that qualify for the answer.

WHERE C

• (^) standard atomic conditions: 1 equality: =, != (on all types) 2 order: <, <=, >, >=, <> (on numeric and string types)
• (^) conditions may involve expressions ) similar conditions as in the SELECT clause