Library System
if any one want answers of following questions send messege on
Use the following Schema to perform the given set of assignment. Tables-
Member – It contains information about the members
Column Name | Data Type | Description |
Member_Id | Number(5) | Unique Member ID |
Member_Name | Varchar2(30) | Name of the Library member |
Member_address | Varchar2(50) | Address of the member |
Acc_Open_Date | Date | Date of membership |
Membership_type | Varchar2(20) | Type of the membership such as ‘Lifetime’,’ Annual’, ‘Half Yearly’,’ Quarterly’ |
Fees_paid | Number(4) | Membership fees paid |
Max_Books_Allowed | Number(2) | Total Number of books that can be issued to the member. |
Penalty_Amount | Number(7,2) | Penalty amount due |
Books- It contains information about the books belongs to the library
Column Name | Data Type | Description |
Book_No | Number(6) | Book identification number |
Book_Name | VarChar2(30) | Name of the book |
Author_name | Varchar2(30) | Author of the book |
Cost | Number(7,2) | Cost of the book |
Category | Char(10) | Category like Science , Fiction etc. |
Issue – It contains the information about issue of the books
Column Name | Data Type | Description |
Lib_Issue_Id | Number(10) | Library Book Issue No |
Book_No | Number(6) | Number of the book issued |
Member_Id | Number(5) | Member that issued the book |
Issue_Date | Date | Date of Issue |
Return_date | Date | Return date |
Day # 2 Assignments (Estimated Time: 2 Hrs)
Concept: Select Command
SQL operators and functions
Single Row functions: Date, Numeric, Character functions, Regular expression functions
Aggregate functions.
Objective: At the end of the assignments, participants will be confident to understand basic select query, Logical, Arithmetic operators, Date, character, Numeric function and regular expression functions.
Aggregate functions and their use.
Task / Problems:
1) List all the books that are written by Author Loni and has price less than 600.
2) List the Issue details for the books that are not returned yet.
3) Update all the blank return_date with 31-Dec-06 excluding 7005 and 7006.
4) List all the Issue details that have books issued for more then 30 days.
5) List all the books that have price in range of 500 to 750 and has category as Database.
6) List all the books that belong to any one of the following categories Science, Database, Fiction, and Management.
7) List all the members in the descending order of Penalty due on them.
8) List all the books in ascending order of category and descending order of price.
9) List all the books that contain word SQL in the name of the book.
10) List all the members whose name starts with R or G and contains letter I in it.
11) List the entire book name in Init cap and author in upper case in the descending order of the book name.
12) List the Issue Details for all the books issue by member 101 with Issue_date and Return Date in following format. ‘Monday, July, 10, 2006’.
13) List the data in the book table with category data displayed as D for Database, S for Science, R for RDBMS and O for all the others.
14) List all the members that became the member in the year 2006.
15) List the Lib_Issue_Id, Issue_Date, Return_Date and No of days Book was issued.
16) Find the details of the member of the Library in the order of their joining the library.
17) Display the count of total no of books issued to Member 101.
18) Display the total penalty due for all the members.
19) Display the total no of members
20) Display the total no of books issued
21) Display the average membership fees paid by all the members
22) Display no of months between issue date and return date for all issue
23) Display the length of member’s name
24) Display the first 5 characters of membership_type for all members
25) Display the last day of the issue date
26) Using the regular expression function select name of book beginning with L
27) Using regular expression replace using the string
28) Display the member_name starting with R using regular expression
29) Display the Book_name containing word SQL using regular expression
30) Display the Author_name starting with "L" from first position using regular expression
31) Display the member_name containing "Ga" from first to second position using regular expression
32) Replace the book_name "Mastering SQL" with "Advanced SQL" using regular expression
33) Replace the author_name "Scott Urman" with "Scott K Urman" using regular expression
34) Display the value of book_name from position 1 to 5 containing "G" using the using regular expression
No comments:
Post a Comment