Introduction to SQL

1. Introduction to SQL

2. SQL • What is SQL • SQL Components • Syntax & Conventions • SQL Data Types • INNER JOIN • SELECT Statements

3. What Is SQL? • SQL (Structured Query Language) • Is a standard language to create, modify, manipulate, and query relational database • SQL • is traditionally a nonprocedural Language. • A procedural language, like FORTRAN or C, describes instructions to the computer by HOW to proceed. • A nonprocedural language describes WHAT to produce. • SQL3 (1999) • contains procedural features—BEGIN-END block, IF statement, functions. • SQL is an open standard—not owned by a company

4. Brief History of SQL • Developed by IBM in the 70’s, along with Relational DB • Officially released in 1981 as SQL • SQL-86 • ANSI (American National Standards Institute) standard • ISO (International Standards Organization) standard • SQL-89 (SQL1) • SQL-92 (SQL2) • SQL-99 (SQL3) • New Standards • Support for Internet • Support for XML • Support for Java • Support for OOP

5. Why Use SQL? • SQL can do things Access Design Window cannot • E.g., subquery • SQL can be used from other applications • E.g., from MS Excel, VB • Web applicaitions • SQL is independent of MS Access

6. SQL Components • DDL (Data Definition Language) • e.g., CREATE TABLE, DROP TABLE • DML (Data Manipulation Language) • e.g., SELECT field FROM table • DCL (Data Control Language) • e.g., for internal security

7. DDL • CREATE TABLE • ALTER TABLE • DROP TABLE • CREATE INDEX

8. DML • SELECT • UNION • UPDATE • DELETE -- delete a row • INSERT INTO -- insert a row • SELECT INTO -- select fields into newTable • PARAMETER -- allows user to enter information interactively

9. SELECT Statementwith one table SELECT title, price FROM Books Given: Books (bkID, title, price)

10. SELECT Statementwith condition SELECT title, price FROM Books WHERE Books.price >= 25.0

11. Arranging in Ascending or Descending Order SELECT title, price FROM Books ORDER BY title SELECT title, price FROM Books ORDER BY price DESC

12. Ordering by More Than One Field SELECT * FROM Books ORDER BY price, title

13. Your Turn • Given:Persons(lastName, firstName, address, city, ZIP, state) • Write an SQL statement for a view containing • lastName, firstName, state of persons from the state of “AZ” • People (all fields) ordered by their last name • lastName, firstName, state of persons arranged by the state and then by last name.

14. Books bkID title price pubID Publishers pubID pubName SELECT Statement from two tables SELECT Books.title, Books.price, Publishers.pubName FROM Publishers INNER JOIN Book ON Publishers.pubID = Books.pubID;

15. Inner Join Books Publishers Books Inner Join PublishersOn Books.pubID = Publishers.pubID

16. Books bkID title price pubID Publishers pubID pubName SELECT Statement from Two Tables SELECT Books.title, Books.price, Publishers.pubNameFROM Publishers INNER JOIN BookON Publishers.pubID = Books.pubIDWHERE Books.price > 25;

17. Your Turn • Given:Books (ISBN, title, price, pubID)Publishers (putID, pubName, pubPhone) • Write an SQL statement to return • ISBN, title, price, and pubName of all books • title, price, pubName of all books published by “Alpha House” • Title, price pubName of all books which cost $25 or over and published by “Alpha House”

18. Syntax and Conventions • Case insensitive, but • Use UPPERCASE for keywords (convention) • Statements can be broken up over multiple lines • Each SQL statement ends with a semicolon • Capitalize table names (our convention) • For Access: BooksAuthors or [Books/Authors}

19. BOOLEAN, LOGICAL BYTE, INTEGER COUNTER, AUTOINCREMENT CURRENCY, MONEY DATE, DATETIME SHORT, SMALLINT LONG, INTEGER SINGLE, REAL Yes/No Number size = byte Autonumber, size = long Currency Date/Time Number, size = Integer Number, size = Long Integer Number, size = single SQL Data Types SQL Data Type Access Field Type

20. DOUBLE, FLOAT, NUMBER TEXT, CHAR, STRING LONGTEXT, MEMO Number, size = Double TEXT Memo SQL Data Types (cont.) SQL Data Type Access Field Type

21. BooksbkID bkTitle pubID AuthorsauID auName auPone BooksAuthorsbkIDauID PublisherspubID pubName pubPhone Joining Tables

22. Inner Join • Books INNER JOIN PublishersON Books.pubID = Publishers.pubID • SELECT title, pubNameFROM Books INNER JOIN PublishersON Books.pubID = Publishers.pubID

23. Books ISBN title price pubID BooksAuthors bkIDauID Authors auID auName Nested Joins SELECT Books.title, Authors.auName FROM Books INNER JOIN (Authors INNER JOIN BooksAuthors ON Authors.auID = BooksAuthors.auID) ON Books.ISBN = BooksAuthors.ISBN; Display Book titles and their Authors.

24. Publishers pubID pubName Books bkID title price pubID BooksAuthors ISBNauID Authors auID auName Relating Many Tables • Display book titles, their authors, and their publishers.

25. Relating Many Tables Publishers pubID pubName Books bkID title price pubID BooksAuthors bkIDauID Authors auID auName SELECT Books.title, Authors.auName, Publishers.pubNameFROM Authors INNER JOIN (BooksAuthors INNER JOIN (Books INNER JOIN Publishers ON Books.pubID = Publishers.pubID ) ON BooksAuthors.bkID = Books.bkID )ON Authors.auID = BooksAuthors.auID

26. Practice with Access (Download library2.mdb) • Write SQL statements to display the following. Then create Access queries to answer the same questions and check their SQL statements. • Display the authors’ names and their phone numbers • Display the phone number of author named “Snoopy” • Display titles and price of all books which cost $300 or more

27. Practice with SQL • Display titles and prices of books and their publishers’ names • Display the book titles and their authors • Display the book tittles, their authors, and their publishers • Display titles and prices of books by publisher named “Big House” • Display all books by author named “Sleepy” • Display books between $20 and $30 and their authors • Display books that are less than $20 or more than $30 and their authors

28. ON Clause Or WHERE Clause • ON clause is part of INNER JOIN, LEFT JOIN, & RIGHT JOIN • WHERE places conditions on data values to be displayed. (Think of WHERE as a filter to restrict rows--can provide additional restriction.)

29. ON or WHERE • SELECT Books.Title, Publishers.pubNameFROM Books INNER JOIN PublishersON Books.pubID = Publishers.pubIDis equivalent to • SELECT Books.Title, Publishers.pubNameFROM Books, PublishersWHERE Books.pubID = Publishers.pubID;

30. Can This Be Simplified? • SELECT Books.title, Authors.auName, Publishers.pubNameFROM Authors INNER JOIN (BooksAuthors INNER JOIN (Books INNER JOIN Publishers ON Books.pubID = Publishers.pubID ) ON BooksAuthors.bkID = Books.bkID )ON Authors.auID = BooksAuthors.auID

31. Recall if (cond1){ statement1}else if (cond2){ statement2}else if (cond3){ statement3} Is equivalent to • if (cond1) { statement1}else { if (cont2) { statement2 } else { if (cond3) { statement3 } }}

32. Analogously… • SELECT Books.title, Authors.auName, Publishers.pubNameFROM Authors INNER JOIN (BooksAuthors INNER JOIN (Books INNER JOIN Publishers ON Books.pubID = Publishers.pubID ) ON BooksAuthors.bkID = Books.bkID )ON Authors.auID = BooksAuthors.auID is equivalent to • SELECT Books.title, Authors.auName, Publishers.pubNameFROM Authors, BooksAuthors,Books, PublishersWHERE Authors.auID = BooksAuthor.auIDAND BooksAuthor.bkID = Books.bkIDAND Books.pubID = Publishers.pubID

33. UPDATE Statement • First, make a copy of the Books table--so that you can preserve the original table. • Change the price of “Iliad” to $50.UPDATE Books2 SET Books2.Price = 50WHERE BOOKS2.Title="iliad"; • What will result from the following?UPDATE Books2 SET Books2.Price = 50;

34. UPDATE Statement (cont.) • Raise the price of all books from publisher “Big House” by 10 % • Note: subquerySELECT pubIDFROM PublishersWHERE pubName = “Big House” • UPDATE BooksSET price = price * 1.1WHERE Books2.pubID = Subquery

35. Raise the price of all books from publisher “Big House” by 10 % • UPDATE Books2SET Books2.price = Books2.price * 1.1WHERE Books2.pubID = (SELECT Publishers.pubID FROM Publishers WEHRE pubName = “Big House”)

36. Alternately…UPDATE Books2 INNER JOIN Publishers ON Books2.pubID = Publishers.pubIDSET Books2.price = price*1.1WHERE Publishers.pubName=”Big House";

37. UPDATE with Values from Another Table • Update Price column in Books2 table, with new prices from table NewPrices (ISBN, price).UPDATE Books2 INNER JOIN NewPrices ON Books2.ISBN = NewPrices.ISBNSET Books2.price = NewPrices.priceWHERE Books2.prices <> NewPrices.price

38. Previewing Update • To check which records will be modified by the UPDATE statement… SELECT Books.* FROM Books INNER JOIN NewPrices ON Books.ISBN = NewPrices.ISBNWHERE BOOKS.Price<>NewPrices.Price;

39. Your Turn • Change Author Shakespeare’s telephone number to “999-8888” • Reduce the price of all books from publisher “Small House” by 5% • Raise the price of all books by Author Shakespeare by 5% (solution)

40. Raise by 5% the price of all books by Shakespeare UPDATE Books2 SET bkPrice =1.05 * bkPriceWHERE Books2.bkID IN (SELECT BooksAuthors2.bkID FROM BooksAuthors2, Authors2 WHERE BooksAuthors2.auID = Authors2.auID AND Authors2.auName = "Shakespeare" ) Back

41. Difference between“=” and “IN” OK if subquery returns only one value UPDATE Books2 SET bkPrice =1.05 * bkPriceWHERE Books2.bkID = (SELECT BooksAuthors2.bkID . . . ) UPDATE Books2 SET bkPrice =1.05 * bkPriceWHERE Books2.bkID IN (SELECT BooksAuthors2.bkID FROM BooksAuthors2, Authors2 WHERE BooksAuthors2.auID IN Authors2.auID AND Authors2.auName = "Shakespeare" ) Necessary when subquery can return more than one value

42. INSERT INTO Statement • INSERT INTO Books2VALUES (“1-1111-1111-1”, “SQL Is Fun”, 1, 25.00) • INSERT INTO Books2 (ISBN, Title)VALUES (“2-2222-2222-2”, “Born to Code”)

43. INSERT INTO Statement (cont.) • Given:Publishers2 (pubID, pubName, pubPhone)where pubID is autonumber type • Insert a new publisher INSERT INTO Publisher2(pubName, pubPhone)VALUES (“Aloha Press”, “808-738-2222”)

44. Your Turn • Add the following record to Books2 • ISBN: 1-2345-6789-0 • Title: SQL for Dummies • Publisher ID: 3 • Price: $35.50 • Add the following record to Authors2 • auName: Brando • auPhone: 222-2222 • Add the following record to Publihsers2 • pubName: Aina Haina Associates • pubPhone: 555-5555