— Query to show all fields in the country table
SELECT CountryID, CountryName
FROM Country
ORDER BY CountryID;
— Query to show all fields in the vendor table
SELECT VendorID, VendorName, CountryID, Email
FROM Vendor
ORDER BY VendorID;
— Query to link tne vendor and country tables
SELECT VendorID, VendorName, CountryName
FROM Vendor, Country
WHERE Vendor.CountryID = Country.CountryID
ORDER BY VendorName;
— Query to show all fields in the contact table
SELECT VendorID, LastName, FirstName, Email
FROM Contact
ORDER BY VendorID;
— Query to link tne vendor and contact tables
SELECT Vendor.VendorID, VendorName, FirstName, LastName, Contact.email
FROM Vendor
LEFT OUTER JOIN Contact
ON Vendor.VendorID = Contact.VendorID
ORDER BY VendorName;
— Query to show all fields in the category table
SELECT CategoryID, Description
FROM Category
ORDER BY CategoryID;
— Query to show all fields in the client table
SELECT ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID, CreditStatus, PhoneNumber
FROM Client
ORDER BY ClientID;
— Query to show all fields in the client cetagory table
SELECT ClientID, CategoryID, CC_Level
FROM ClientCategory
ORDER BY ClientID, CategoryID;
— Query to link the category, client category and client tables
SELECT Category.CategoryID As Category_ID,
Category.Description As Description,
Client.LastName AS Client_LastName,
Client.FirstName AS Client_FirstName,
CC_Level AS Client_Category_Level
FROM Category, ClientCategory, Client
WHERE Category.CategoryID = ClientCategory.CategoryID
AND ClientCategory.ClientID = Client.ClientID
ORDER BY Category.CategoryID;
— Query to show all fields in the book table
SELECT BookID, Cost, Price, Available, DatePublished,
BookInfoID, VendorID, ClientOrderID
FROM Book
ORDER BY BookID;
— Query to show all fields in the book info table
SELECT BookInfoID, Title, AuthorID, Notes
FROM BookInfo
ORDER BY BookInfoID;
— Query to show all fields in the author table
SELECT AuthorID, LastName, FirstName
FROM Author
ORDER BY AuthorID;
— Query to show all fields in the book cetagory table
SELECT BookID, CategoryID
FROM BookCategory
ORDER BY BookID, CategoryID;
— Query to link the category, book category, book and book info tables
SELECT Category.CategoryID As Category_ID,
Category.Description As Description,
Book.BookID AS BookID,
BooKInfo.Title AS Title
FROM Category, BooKCategory, Book, BookInfo
WHERE Category.CategoryID = BookCategory.CategoryID
AND BookCategory.BookID = Book.BookID
AND Book.BookInfoID = BookInfo.BookInfoID
ORDER BY Category.CategoryID;
— Query to link the book info and author tables
SELECT Title, LastName AS Author_Last_Name, FirstName AS Author_First_Name
FROM BookInfo, Author
WHERE BookInfo.AuthorID = Author.AuthorID
ORDER BY Title;
— Query to show all fields in the client order table
SELECT ClientOrderID, OrderDate, ClientID, Status
FROM ClientOrder
ORDER BY ClientOrder.ClientOrderID;
— Query to link the country and client tables
SELECT LastName AS Client_Last_Name, FirstName AS Client_First_Name,
CountryName As Country
FROM Client, Country
WHERE Client.CountryID = Country.CountryID
ORDER BY LastName, FirstName;
— Query to link the client and client order tables
SELECT LastName AS Client_Last_Name, FirstName AS Client_First_Name,
ClientOrderID, Status
FROM Client, ClientOrder
WHERE Client.ClientID = ClientOrder.ClientID
ORDER BY LastName, FirstName;
— Query to link the client order, book and book info tables
SELECT ClientOrder.ClientOrderID, Status, BookID, Title
FROM ClientOrder, Book, BookInfo
WHERE ClientOrder.ClientOrderID = Book.ClientOrderID
AND Book.BookInfoID = BookInfo.BookInfoID
ORDER BY ClientOrder.ClientOrderID;
— Query to link the vendor, book and book info tables
SELECT VendorName, BookID, Title
FROM Vendor, Book, BookInfo
WHERE Vendor.VendorID = Book.VendorID
AND Book.BookInfoID = BookInfo.BookInfoID
ORDER BY VendorName;