CTEC2902
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Revising RDB and SQL PowerPoint PPT Presentation


  • 81 Views
  • Uploaded on
  • Presentation posted in: General

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.

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Revising rdb and sql

CTEC2902Advanced Programming

Revising RDB and SQL

SQL Exercises


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

  • 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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


Revising rdb and sql

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


  • Login