#Written by Jen Chen
#Copyrighted by Jen Chen
#CS 122
#Lecture on 10/18/04
#Topics: UNION, UNION ALL, string manipulation, sub-queries.
#Tables in used: BooksJC, PublishersJC.

Table: BooksJC
	===================================================   
	Field Name		Data Type	
	===================================================   
	ISBN			varchar(15) primary key not null
	Title			varchar(50)
	PubID			varchar(25)
	Price			decimal(10,2)

Table: PublishersJC
	===================================================   
	Field Name		Data Type	
	===================================================   
	PubID			varchar(25) primary key not null
	PubName			varchar(50)
	PubPhone		varchar(15)
	
Contents of BooksJC:

  ISBN          Title   	PubID   Price
  0-103-45678-9	Iliad		1	25
  0-11-345678-9	Moby, Dick	3	49
  0-12-333433-3	On, Liberty	1	25
  0-123-45678-0	Ulysses		2	34
  0-12-345678-9	Jane, Eyre	3	49
  0-321-32132-1	Balloon		3	34
  0-55-123456-9	Main, Street	3	22.95
  0-555-55555-9	Macbeth		2	12
  0-91-045678-5	Hamlet		2	20
  0-91-335678-7	Faire, Queen	1	15
  0-99-777777-7	King, Lear	2	49
  0-99-999999-9	Emma		1	20
  1-1111-1111-1	C++		1	29.95
  1-22-233700-0	Visual, Basic	1	25

	
Contents of PublishersJC:

	+-------+------------------+----------------+
	| PubID | PubName          | PubPhone       |
	+-------+------------------+----------------+
	| 1     | "Big, House"     | 123-456-7890   |
	| 
	  2     | "Alpha, Press"   | 999-999-9999   |
	| 
	  3     | "Small, House"   | 714-000-0000   |
	| 
	  4     | "Another, Press" | 111-222-3333   |
	+-------+------------------+----------------+
	4 rows in set (0.00 sec)
	

//Task: to find out the total price of purchasing 2 books titled "Ulysses".

	select 2*Price
	   from BooksJC
	   where
	   Title='Ulysses';

//Task: to find out the total price of purchasing 2 books titled "Hamlet".
	
	select Price 
	   from BooksJC
	   where Title='Hamlet';
	
//Task: to find out the total price of purchasing 2 books titled "Shakespeare"	
	
	select 3*Price 
	   from BooksJC
	   where Title='Shakespeare';

//Putting the above 3 queries together by using UNION, we obtain:

	select Title, 2*Price from BooksJC where Title='Ulysses'
	UNION
	select Title, Price from BooksJC where Title='Hamlet'
	UNION
	select Title, 3*Price from BooksJC where Title='Shakespeare';

//Then to find the total amount of these books, we use sub-query as follow:
	select 2*(select Price from BooksJC where Title='Ulysses') +
		(select Price from BooksJC where Title='Hamlet') +
	       3*(select Price from BooksJC where Title='Shakespeare')
	  from BooksJC

//Task: to find the most expensive books from all publishers.
//Solution: Use sub-query in the WHERE clause.

	select * 
	   from 
	     BooksJC
	   where
	     Price in (select MAX(Price),MIN(Price) from BooksJC);

//Task: to find the least expensive and the most expensive book from all books.
//Solution: First query the least expensive book, the query the most expensive book.
//Put the two results together by using UNION in the WHERE clause.

	select *  
	   from  BooksJC 
	   where Price in (select MAX(Price) from BooksJC UNION select MIN(Price)from BooksJC);

//THis will display the most and the least expensive price tags.

	select MAX(Price), MIN(Price) from BooksJC;


//Task: to list the most expensive and the least expensive books from the table
//BooksJC.
//Solution: We need to find a way to query the highest and the lowest prices among the 
//books. Then we need to query the table based on these data.
//- Subqueries are good for this purpose.

	select title,ISBN,Price  
	   from  BooksJC 
	where Price in (select MAX(Price) 
			   from BooksJC 
			UNION 
			select MIN(Price) 
			   from BooksJC);


//Task: To list all books published by the publisher "Small, House".

	select B.Title, B.ISBN, P.PubName
	   from
		BooksJC B,
		PublishersJC P
	where
		P.PubName='Small, House'
		and P.PubId = B.PubId;

//Task: To find the total cost of purchasing 2 "Moby, Dick", one "Ulysses", and
//three "Hamlet" books.
//Solution: First we find the costs for 2 "Moby, Dick" books by using a subquery,
//then we find the cost of the "Ulysses" book, and finally we find the cost for
//the three "Hamlet" books. All using subqueries.
//- Note that these subqueries are placed in the SELECT clause, not in the 
//WHERE clause. (although you may do it differently)

select distinct (2*(select Price from BooksJC where Title="Moby, Dick")) + 
			(select Price from BooksJC where Title="Ulysses") + 
			3*(select Price from BooksJC where Title="Hamlet") AS TOTAL;

//Task: To find the total price of all boooks.

	select sum(Price) from BooksJC

//Task: To find the total sum of all books that cost between $25 and $40.
//Solution: Use the BETWEEN clause.

	select sum(Price) from BooksJC
	where
	   Price BETWEEN 25 and 40

//Task: To find the  most expensive books from the table BooksJC.
//Solution: USe subquery to find the most expensive price, then based on this
//information, we query the data from the table BooksJC.

	select * from BooksJC
	where
	   Price = (select max(Price) from BooksJC);

//Task: As above, but this time we find the least expensive book.

	select * from BooksJC
	where
	   Price = (select min(Price) from BooksJC)


select ISBN, sum(Price)
from BooksJC
group by ISBN;

//Task: To find the total price of all books from the publisher with ID 2.
//Solution: Indicate the publisher id in the WHERE clause and DO NOT FORGET to set
//the 2 publisher ID to equal to each other.
//Since we query more than one field using aggregate function, then we need to use
//the GROUP BY clause at the end.

	select P.PubName, sum(B.Price)
	   from BooksJC B, PublishersJC P
	where
	   B.PubID='2'
	   and B.PubID = P.PubID
	   group by P.PubName;


//Task: Same as above, but this time we query more than one publisher.
//Solution: Use the key word IN inside the WHERE clause.

	select P.PubName, sum(B.Price)
	   from BooksJC B, PublishersJC P
	where	
	   B.PubID in ('2','4')
	   and B.PubID = P.PubID
	   group by P.PubName;

//Task: As above but with more constraint.

	select P.PubName, sum(B.Price)
	   from BooksJC B, PublishersJC P
	where
	   B.PubID in ('2','1','3')
	   and B.PubID = P.PubID
	   and P.PubName like '%Press'
	   group by P.PubName;


select substring(trim(ISBN),3,2), Title from BooksJC limit 3;

select concat_ws(',',Title) from BooksJC;


select replace(Title,',','') from BooksJC;

select instr(Title,',') from BooksJC;


select substr(Title,instr(Title,',')) from BooksJC;

select substr(Title,instr(Title,',') + 2) from BooksJC;


select substr(Title,(instr(Title,',')+1)) from BooksJC;


select substr(trim(Title),1,5) from BooksJC;

select substr(Title,(instr(Title,',')+1)), substr(Title,1,instr(Title,',')-1) from BooksJC;

select trim(substr(Title,(instr(Title,',')+1))), substr(Title,1,instr(Title,',')-1) from BooksJC;


//Task: To swap the name of the books, where it is seperated by a comma originally.

	select concat(trim(substr(Title,(instr(Title,',')+1))),' ',
			substr(Title,1,instr(Title,',')-1)) 
			   from BooksJC;

select concat(trim(substr(Title,(instr(Title,',')+1))),               ' ',substr(Title,1,instr(Title,',')-1))  from BooksJC;


//Task: To list all books published by publishers with ID's 1 and 3.
//Solution: Use UNION or the IN key word.

	select * from BooksJC where PubID='3'
	UNION
	select * from BooksJC where PubID='1';

select * from BooksJC
where
Title='moby, Dick';


select B.ISBN, P.PubName, B.Price
from BooksJC B,
PublishersJC P
where
B.PubID='1'
and B.PubID=P.PubID
and B.Price=(select max(Price) from BooksJC);


select B.ISBN, P.PubName, B.Price
from BooksJC B,
PublishersJC P
where
B.PubID='1'
and B.PubID=P.PubID
and B.Price=(select max(Price) from BooksJC where PubID='1');