180 likes | 304 Views
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.
E N D
CTEC2902Advanced Programming Revising RDB and SQL SQL Exercises
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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