Purchase Solution

SQL Statements for Library Database

Not what you're looking for?

Ask Custom Question

E. Given the database schema on the relations as follows:

LIBRARY(LNO, LNAME, LOCATION)
AUTHOR(ANO, ANAME)
BOOK(BNO, TITLE, ANO)
COPY(CNO, LNO, BNO)
PATRON(PNO, PNAME, ADDRESS)
LOAN(LOANNO, PNO, CNO)

LNO - Library Number
LNAME - Library Name
ANO - Author Number
BNO - Book Number
CNO - Copy Number
PNO - Patron Number
LOAN - Loan Number

Write the following queries in SQL:
(You are not supposed to use any aggregation functions such as: count(), sum(), etc.)

1. Find all pairs of different book titles that have copies in the same library.
(Your SQL statement should exclude pairs of the form (x, x) from the answer set and to provide one of the pairs (x, y) or (y, x) when both qualify.)
2. Find all triplets of libraries that are collocated.
(Your SQL statement should include the library name in the form of (a, b, c), exclude the triplets of the form (x, x, x), (x, x, y), (x, y, x), and (y, x, x) from the answer set, and just list one of the six permutations of (x, y, z) when triplets qualify.)
3. Find the titles of books with copies in all Fort Lauderdale libraries.
4. Find the titles of books with copies in all Fort Lauderdale libraries that features copies of all books written by Maier.

Purchase this Solution
Solution Summary

Word attachment shows how to find pairs or book copies, triplets of collocated libraries and titles within a certain library using SQL.

Purchase this Solution
Free BrainMass Quizzes
C# variables and classes

This quiz contains questions about C# classes and variables.

Basic UNIX commands

Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.

C++ Operators

This quiz tests a student's knowledge about C++ operators.

Inserting and deleting in a linked list

This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.

Basic Networking Questions

This quiz consists of some basic networking questions.

View More Free Quizzes
Related BrainMass Solutions

  • Database and Database Manipulation Language

    Write a paper (2-5 pages) that includes your SQL statements and screenshots of the queries in OpenOffice. Please find attached document and access database for this exercise. 1. Customer, Library_Card, and Customer_CheckOut tables populated 2.

  • Please answer and elaborate on the following

    By default, SQL Server uses named pipe \\,\pipe\sql\query for client connections and \\machinename\pipe\sql\query for network connections. The TCP/IP Sockets library is probably the most widely used Net-Library.

  • Writing SQL Queries Using a Database and Tables

    Using the database and tables (see attachment) write SQL INSERT statements to insert records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location.

  • SQL Statements To Run In SQl Server

    I have included database and SQl Statements Please see the attached file.

  • Do all DBMSs have their own language

    For conditional control, PL/SQL offer if-then-else pattern if then statements> [elsif ] then < Series of statements> . . . .

  • Write SQL SELECT, UPDATE, and DELETE statements

    Save the SQL statements and the data from both tables in order to show how the data was affected. Using the database and tables from Week Three, write SQL statements to: Calculate the average salary for all employees.

  • Information Systems- SQL Servers

    References: [1]. http://msdn.microsoft.com/en-us/library/bb500435%28v=SQL.100%29.aspx [2].

  • SQL Server Database for Kudler Fine Foods/Service

    Using the database and tables from Week Two, write SQL statements and enter the records into the employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location.

  • features and uses of SQL

    SQL (Structured Query Language) Pronounced "S-Q-L" a language used to interrogate and process data in a relational database. Originally developed by IBM for its mainframes, all database systems designed for client/server environments support SQL.

View More Related Solutions