Question

This assignment consists of a series of SQL questions. For each question, you will need to include:

• SQL query.

• An explanation of the query. Please include explanations as a comment AFTER your query, e.g., enclosed within a /* comments block */ ). See the first example in the SQL tutorial for a comment. You don’t need to explain straightforward code in comments. Only the parts that are interesting or different, so that we understand what you did.

Question-1 For each member and author display the following information. • Type - Which denotes if it's a member or an author • ID-Display the author ID or member ID • First Name - Display the first name of the member or author Last Name - Display the last name of the member or author Book Count - Display the total number of books written by authors. For members, display the number of books issued to the member (if the same book is issued repeatedly it should count only once). Also display 0 as the count for those authors who have not yet written a book or members who have not been issued a book. Sort the results by the last name of the member or author. Your output should like the following including column headings): Type First Name Last Name Book Count Author A001 John Doe Member M002 Michael Jordan Author A004 Tom Sawyer ID

Answer 1

Drop table If it Exist

drop table Authors;
drop table Books_Authors;
drop table Members;
drop table issue_detail;

Create Necessary Table

Create table Authors(Au_ID number(4),Au_Lname varchar(20),Au_Fname varchar(20),phone varchar(10),Address varchar(20),city varchar(20),state varchar(10),country varchar(10),postalCode number(7));

desc Authors;
insert into Authors values(101,'viral','Rajgor','123','108 A','bhuj','gujrat','India',370001);
insert into Authors values(102,'Devji','Chhanga','444','10 A','Rannal','gujrat','India',370301);
Select * from Authors;

O X KuK SUL Command Line - SQL> Create table Authors (Au_ID number(4), Au_Lname varchar(20), Au_Fname varchar(20), phone varchar(10),Address varchar(20),city varchar(20), state varchar(10), country varchar(10), postalCode number(7)); Table created SQL> desc Authors; Name Null? Type AU ID AU_LNAME AU FNAME PHONE ADDRESS CITY STATE COUNTRY POSTALCODE NUMBER(4) VARCHAR2(20) VARCHAR2 (20) VARCHAR2 (10) VARCHAR2 (20) VARCHAR2(20) VARCHAR2(10) VARCHAR2(10) NUMBER(7) SQL> insert into Authors values(101, 'viral', 'Rajgor', '123', '108 A', 'bhuj', 'gujrat', 'India',370001); 1 row created. SQL> insert into Authors values (102, 'Devji', 'Chhanga', '444', '10 A', 'Rannal', 'gujrat', 'India',370301); 1 row created. SQL> Select * from Authors; AU_ID AU_LNAME AU_FNAME PHONE ADDRESS CITY STATE COUNTRY POSTAL CODE - - -- - - - - - - - - -- - 101 viral Rajgor 108 A bhuj 123 India gujrat 370001 102 Devji Chhanga 10 A Rannal gujrat 444 India 370301

Create table Books_Authors(Book_ID number(5),Au_ID number(5));
desc Books_Authors;
insert into Books_Authors values(201,101);
insert into Books_Authors values(201,102);
insert into Books_Authors values(204,101);
insert into Books_Authors values(205,101);
Select * from Books_Authors;

FLY Run SQL Command Line SQL> Create table Books_Authors (Book_ID number(5), Au_ID number(5)); Table created. SQL> desc Books_Authors; Name Null? Type BOOK ID AU_ID NUMBER (5) NUMBER (5) SQL> insert into Books_Authors values (201, 101); 1 row created. SQL> insert into Books_Authors values (201, 102); 1 row created. SQL> insert into Books_Authors values (204,101); 1 row created. SQL> insert into Books_Authors values (205, 101); 1 row created. SQL> Select * from Books_Authors; BOOK_ID AU_ID - - - - - - - - - - - - - - - - 201 101 201 102 101 204 205 101 SOL

Create table Members(Member_ID number(5),First_name varchar(20),Last_name varchar(20),member_Since number(4),Exipry_date date,Address1 varchar(20),Address2 varchar(20),city varchar(10),state varchar(20),zipcode number(7),phone_num number(5),Balance number(10,2));
desc Members;
insert into Members values(301,'Bhatt','Poonam',2002,'01-dec-2022','10 A','12 -B','Bhuj','gujrat',370001,123,23.45);
Select * from Members;

Create table issue_detail(issue_Detail_ID number(5),item_copy_ID number(5),Member_ID number(5),issue_date date,due_date date,return_date date,comments varchar(20));
desc issue_detail;
insert into issue_detail values(401,501,301,'01-mar-2020','10-mar-2020','09-mar-2020','emj');
Select * from issue_detail;

- O X Run SOL Command Line 181 205 SQL> create table Members (Member_ID number(5), First_name varchar(20), Last_name varchar(29), member_Since number(4), Exipry_date datr, Address1 varchar(29),Address2 varchar(20),city varchar(10), state varchar(20),zin code number (7) phone_rium number (5), Balance number (10,2)); Tahir created. SQL> desc Members; Nane Null? Type MEMBER ID FTRST NAME LAST_NAME MEMBER SINCE EXTPRY DATE ADDRESSI ADDRESS2 NUMBER(5) VARCHAR2(28) VARCHAR2(28) NUMBER (4) DATE VARCIAR2(20) VARCHAR2(20) VARCHAR2(10) VARCHAR2(28) NUMBER(7) NUMBER(5) NUMBER (10,2) CITY STATE ZIPLCDE PHONE NUM BALANCE SQL> insert into Members values (381, 'Bhatt', 'Poonam',2002, 91-dec-2022', '19 A', '12 -B', 'Bhuj', 'gujrat',370081,123,23.45); 1 row created SQL> select * front Members; LAST_NAME MEMBER_SINCE EXIPRY_DA MEMBER_ID FIRST_NAME ADDRESS1 ADDRESS CITY STATE ZIPCODE PHONE_NUM BALANCE Poonam 2002 01-DEC-22 301 nhatt 10 A 370001 12 -3 123 Bhuj gujrat 23.45 -SQL)

Query:

RB Run SQL Command Line Table dropped. SQL> Create table issue_detail(issue_Detail_ID number(5),item_copy_ID number(5), Member_ID number(5), issue_date date, due_date date, return_date date, comments varchar(20)); Table created. SQL> desc issue_detail; Name Null? Type ISSUE_DETAIL ID ITEM_COPY_ID MEMBER_ID ISSUE_DATE DUE_DATE RETURN_DATE COMMENTS NUMBER (5) NUMBER (5) NUMBER (5) DATE DATE DATE VARCHAR2(20) SQL> insert into issue_detail values(401,501, 301, '01-mar-2020', '10-mar-2020', '09-mar-2020', 'emj'); 1 row created. SQL> Select * from issue_detail; ISSUE_DETAIL_ID ITEM_COPY_ID MEMBER_ID ISSUE_DAT DUE_DATE RETURN_DA -- - - - - - - - - - - - - - COMMENTS 401 501 301 01-MAR-20 10-MAR-20 09-MAR-20 emj

Select 'Authors' "Type",Au_Lname"Last Name",Au_Fname "First Name",count(*)"Books Count" from Authors inner join Books_Authors on Authors.Au_ID=Books_Authors.Au_ID group by Au_Lname,Au_Fname
union
Select 'Member' "Type",Last_name"Last Name" ,First_name "First Name",count(*)"Books Count" from Members inner join issue_detail on Members.Member_ID=issue_detail.Member_ID group by Last_name,First_name;

Run SOL Command Line -ox Member Poonan Bhatt SQL> select 'Authors' "Type",Au_name"Last Name,Au_Fname "First Nane", count(*) "Books Count" from Author's inner join Books_Authors on Authors. Au_ID-Books_Authors.Au_ID group by Au_Lriane, Au_Frame 2 union 3. Select Member' "Type",last_name"Last Name" , First_name "First Name, count(*) Books Count" from Members inner join issur_detail on Members.Member_TD-issue_detail.Member_ID group by Last_namr, First_name; Type Last Name First Name Books Count Muthors Devji Authors viral Member Poonam Change Rajgor Bhatt SQL> Final output

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........