1 / 18

Revising RDB and SQL

CTEC2902 Advanced Programming. Revising RDB and SQL. CTEC2902 Advanced Programming. The story so far... You know How to use existing classes (via their API) You are now ready to tackle ADO.NET, but first ... Let’s remember RDB and SQL. e.g. A Simple Library database. Publishers.

wyoming-mendoza
Download Presentation

Revising RDB and SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CTEC2902Advanced Programming Revising RDB and SQL SQL Exercises

  2. CTEC2902Advanced Programming • The story so far... • You know • How to use existing classes (via their API) • You are now ready to tackle ADO.NET, but first ... • Let’s remember RDB and SQL SQL Exercises

  3. e.g. A Simple Library database Publishers Loans Books Table definitions Books (CopyID, Title, Author, ISBN, PublisherID, Cost) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) Borrowers RDB consists of 1 or more tables A table consist of 0 or more records A record consist of 1 or more fields Table name is in bold Primary keys are underlined Tables are linked using primary and foreign keys Use this database to answer the following questions SQL Exercises

  4. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. Extract the name, address, and phone number of all the publishers in the database SELECT Name, Address, Phone FROM Publishers • Remember: SELECT always returns a (temporary) table • How many columns does the above table have? • How many rows? SQL Exercises

  5. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. Extract names and addresses of all borrowers on BSc Computing course SELECT Name, Address FROM Borrowers WHERE Course = ‘BSc Computing’ Only those records that satisfy the condition are selected How many columns (or fields) does the above table have? How many rows (or records)? SQL Exercises

  6. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. Names of all BIS students with books out SELECT Name FROM Borrowers, Loans WHERE Course = ‘BIS’ AND Loans.BorrowerID = Borrowers.BorrowerID Is there a problem with the table that this SQL returns? Yes, this SQL will produce duplicate records if a student has borrowed several books SQL Exercises

  7. The keyword DISTINCT will prevent duplicate records occurring in the resulting data table Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. Names of all BIS students with books out SELECT DISTINCT Name FROM Borrowers, Loans WHERE Course = ‘BIS’ AND Loans.BorrowerID = Borrowers.BorrowerID SQL Exercises

  8. The * means “all the fields” Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. All details of all borrowers registered in the library SELECT * FROM Borrowers SQL Exercises

  9. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. All names of all authors, whose names begin with letter K SELECT DISTINCT Author FROM books WHERE Author LIKE ‘K%’ % is called “wildcard”; it means “any string” SQL Exercises

  10. Simple Library • Books (CopyID, Title, Author, ISBN, PublisherID) • Publishers (PublisherID, Name, Address, Phone) • Borrowers (BorrowerID, Name, Address, Course) • Loans (CopyID, BorrowerID, DueDate) • E.g. All books with .NET anywhere in their title • SELECT * FROM books WHERE Title LIKE ‘%.NET%’ (The % is a .NET requirement; in other SQL, the & may be required) SQL Exercises

  11. Note the # delimiters; they enclose literal date values Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. Names and addresses of all borrowers with books due in on 16/12/2011 SELECT Name, Address FROM Borrowers, Loans WHERE Loans.BorrowerID = Borrowers.BorrowerID AND DueDate = #16/12/2011# SQL Exercises

  12. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g. All details of all borrowers registered in the library, in ascending order of name SELECT * FROM borrowers ORDER BY Name Default is Ascending or ASC • e.g., All books in descending order of title • SELECT * FROM books ORDER BY Title DESC SQL Exercises

  13. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) • E.g., Fetch the titles of those books that are currently on loan • SELECT Title • FROM books, loans • WHERE books.CopyID = loans.CopyID SQL Exercises

  14. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) • E.g., Fetch the names of all Computing students with books out • SELECT Name • FROM borrowers, loans • WHERE Course = ‘Computing’ AND • loans.BorrowerID = borrowers.BorrowerID SQL Exercises

  15. Simple Library Books (CopyID, Title, Author, ISBN, PublisherID) Publishers (PublisherID, Name, Address, Phone) Borrowers (BorrowerID, Name, Address, Course) Loans (CopyID, BorrowerID, DueDate) E.g., Get the ISBN of the book, entitled VB.NET Secrets, by Jo Bloggs SELECT ISBN FROM Books WHERE Author = ‘Jo Bloggs’ AND Title = ‘VB.NET Secrets’ SQL Exercises

  16. Questions 1. I want to select two books, one with ID 11 and the other with ID 25. Will this command do the job? SELECT * FROM books WHERE CopyID = 11 AND CopyID = 25 2. Which records will be selected by the following command? SELECT * FROM books WHERE CopyID <> 11 AND CopyID <> 25 SQL Exercises

  17. Extended WHERE Specifying a range of numeric values SELECT * FROM employee WHERE Salary BETWEEN 12000 AND 18000 Q: can similar ranges be specified for strings? Dates? Using sets of values; e.g., SELECT * FROM books WHERE PublisherID IN (3, 4, 5, 6) SELECT * FROM books WHERE PublisherID NOT IN (1, 2) SQL Exercises

  18. Other SQL commands you will use INSERT INTO UPDATE DELETE Each command has its own, specific syntax Find out from www (or books & notes) Look out for: stored procedures SQL Exercises

More Related