PRACTICAL-9
AIM: Consider the following schema for a Library Database: BOOK (Book_id, Title,
Publisher_Name, Pub_Year) BOOK_AUTHORS (Book_id, Author_Name) PUBLISHER
(Name, Address, Phone) BOOK_COPIES (Book_id, Branch_id, No-of_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)
LIBRARY_BRANCH (Branch_id, Branch_Name, Address) Write SQL queries to
1. Retrieve details of all books in the library_id, title, name of publisher, authors, number of
copies in each branch, etc.
2. Get the particulars of borrowers who have borrowed more than 3 books between Jan 2018
to Jun 2018
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation
. 4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simple query.
5. Create a view of all books and its number of copies that are currently available in the
Library.
Create tables:
Insert the values into tables:
Output:
Table 1: PUBLISHER
Table 2: BOOK
Table 3: BOOK_AUTHORS
Table 4: LIBRARY_BRANCH
Table 5: BOOK_COPIES
Table 6: CARD
Table 7: BOOK_LENDING
Queries:
1. Retrieve details of all books in the library_id, title, name of publisher, authors, number of
copies in each branch, etc.
Input :
Output :
[Link] the particulars of borrowers who have borrowed more than 3 books between Jan 2018
to Jun 2018
Input:
Output:
[Link] a book in BOOK table. Update the contents of other tables to reflect this data
manipulation operation.
Input :
Output :
[Link] the BOOK table based on year of publication. Demonstrate its working with a
simple query
Input :
Output:
[Link] a view of all books and its number of copies that are currently available in the
Library
Input:
Output:
Practical – 7
Aim: Use of substring comparison
SUBSTRING function is one of the built-in function and that helps to obtain a particular
character data .of the text data in the queries. This function is widely used by database
developers in the queries, for this reason, we will focus on performance details of this
function.
It takes three arguments.
• The first one is the field that we want to query on.
• The second argument is the starting character,
• and the third argument is the ending character
The SQL Server SUBSTRING function syntax is as follows:
SUBSTRING (expression, position, length)
Parameters:
• expression: Input source string
• position: Is an integer value that specifies the initial position from which the
characters can be extracted from the given expression. The first position of an
expression is always starting with 1. The initial position can be a negative integer.
• length: Is a positive integer value. It specifies the ending limit and determines how
many characters are going to be extracted from the given expression
Note: The SQL Substring function traversal is always from left to right.
Input:
Output:
Create a Table
1. Simple data handling with the SQL Server Substring function
Syntax:
SELECT FIRST_NAME, SUBSTRING(FIRST_NAME,1,4), LAST_NAME FROM
PERSON
Output:
Practical –8
Aim: Use of order by statement.
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records
in descending order, use the DESC keyword.
This clause is always used with the SELECT query in Structured Query Language
Input:
Output:
Create table:
1) Use the ORDER BY clause
The following query uses ODDER BY clause to show all records of PRODUCT by
PRODUCT_ID from minimum to maximum:
Syntax:
Input:
SELECT * FROM PRODUCT ORDER BY PRODUCT_ID;
Output:
2) The following query uses ODDER BY clause with more than one column of the
Patient table
Syntax:
SELECT PRODUCT_ID, NAME
, QUANTITY_IN_STOCK FROM PRODUCT ORDER BY NAME,
QUANTITY_IN_STOCK
Input:
Output:
3) ORDER BY ASC
The ASC is a keyword used with the ORDER BY clause to sort the selected row in ascending
order.
Syntax of ORDER BY ASC
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Nam
e, ORDER BY column_name ASC
Input:
Output:
4) ORDER BY DESC
The DESC is a keyword used with the ORDER BY clause to sort the selected rows in a
descending order, i.e., from high to low.
Syntax of ORDER BY DESC:
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Nam
e ORDER BY col
Input:
Output:
5) ORDER BY with WHERE Clause
The ORDER BY keyword can also be used with the WHERE clause in the SELECT query of
Structured Query Language.
Syntax of ORDER BY with WHERE Clause:
SELECT Column_Name_1, Column_Name_2, ........, Column_Name_N FROM Table_Nam
e ORDER BY WHERE [ CONDITION ]
Input:
Output: