Above select query pulls all the data from the table. But user requirement is not always to retrieve whole table data. We should be able to certain records from the table depending on the user requirement. Hence we use WHERE clause query to limit the number of records in the query.
Suppose we need to find the book published by the publisher whose id is ‘PRSN_45’. Then we will query the table using SELECT query as above and will add the condition to filter the PUBLISHER_ID using WHERE clause.
SELECT * FROM BOOKS WHERE PUBLISHER_ID = ‘PRSN_45’;
Above query will query the whole table and then search for records with publisher id ‘PRSN_45’. It finds only one record in the table and displays it. Similarly, consider the query which finds the books borrowed by BRWR_ID = ‘ALX_1002’.
SELECT * FROM BOOK_LOAN WHERE BRWR_ID = ‘ALX_1002’;
Get the books which are arrived to library in 2015. Where can we find these details about books? These details are specific to each copy of the book and are available in BOOK_COPY table. Hence we have to query BOOK_COPY table to see which books are arrived in 2015. Hence the query to get these details is as below :
SELECT * FROM BOOK_COPY WHERE RECEIVED_DATE >= ‘1-Jan-2015’;
Find the details of the borrower who have borrowed the books and their due date is in June 2015. These details will be available in BOOK_LOAN table, and our data will be retrieved by adding the filter on DUE_DATE column.
SELECT * FROM BOOK_LOAN WHERE DUE_DATE >= ‘1-Jun-2015’;
Similarly we can query any table to see any specific records from the table. Here WHERE clause limit the number of records retrieved from the table. But it retrieves all the columns from the table that we are querying. Ideally all column values are not required when users requests some data. They will be specific to see only certain attributes from the table and rest of the information would be irrelevant to them.
In above query to find the borrower of the book whose due date is in June, attributes like copy number and loan date are not requested by the user. We need to show them only borrower id and ISBN for which due date is in June. Hence we have to restrict the columns in the SELECT query by specifying the column names which we want to see in the result. Now above query will change like below :
SELECT BRWR_ID, ISBN
FROM BOOK_LOAN
WHERE DUE_DATE >= ‘1-Jun-2015’;
Now the result shows only the requested attributes and gets rides of unnecessary information in the result.
These queries result seems to be correct for the designer or the one who is very much familiar with all the ISBN, PUB_ID, CATEGORY_ID etc. But for any other user these IDs are black box. They will not understand what it is or which book it is. Hence it is necessary to provide meaningful result.
Consider the simple query on BOOK_COPY above. It shows ISBN, which user will not understand. If book name is also displayed in the result, it will give some meaning to their request. In order to get such result, we need to display book name too in above query, which we will get from the BOOKS table. But how to we query BOOKS table to get the book names for the ISBNs got from above query? Since BOOKS and BOOK_COPY are mapped by means of ISBN, we can query both the tables together by linking them using ISBN.
SELECT * FROM BOOKS B, BOOK_COPY BC WHERE B.ISBN = BC.ISBN;
This will now map matching ISBN of BOOKS table with the ISBN of BOOK_COPY table. Hence we will get records which are matching ISBN of both the tables.
But above query has selected all the columns from both the tables. But user has requested to see the copies of the books. Hence displaying number of pages, publisher id etc is irrelevant here. Hence we need to restrict the number of columns in above query to what has requested. Hence the query becomes as below. Now the query has limited columns with meaningful result.
SELECT BC.ISBN, B.BOOK_NAME, BC.COPY_NUM, BC.RECIEVED_DATE
FROM BOOKS B, BOOK_COPY BC
WHERE B.ISBN = BC.ISBN;