#Author: Jen Chen
#Copyrighted by Jen Chen
#Fall 2004
#Lesson 4: Subqueries, UNION, string manipulation, and aggregate functions.


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 select all records containing publishers with ID's 1, 2, and 3.
//Solution: Use the IN in the WHERE clause, then enclose all publishers' ID's in the IN().
//Note that if we display more than one field using any of the aggregate function, then
//we need to supply the "group by" in the WHERE clause.

 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;


//Task: To select all records containing publishers with ID's 1, 2, and 3.
//Solution: Use the IN in the WHERE clause, then enclose all publishers' ID's in the IN().

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: To find the sum of all books from all publishers.
//Solution: Use the aggregate function SUM(), and privide the field name that we want to sum
//up in the parentheses.

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

//To display record number 5 to 8, we use "limit" as follow:
//Note that the first number after the word "limit" is the starting number of the record,
//and the next number indicates the total number of records retrieved, starting from record
//number 5.

	select * from BooksJC
    	limit 5,4;

//Task: Removing all occurences of comma by using the string function replace().

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

//Task: To select a portion of an existing string, we use the function substr().

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

//Task: To concatenate/combine some strings together, we use the function substr().

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

//Task: To combine the result of one query to the result of another query.
//Solution: Use UNION to combine the result of one SQL query with the result of other(s).
//NOTE: Make sure that the data type of the 2 result sets match field by field. Otherwise
//an error will return.
//The result of the UNION DOES NOT contain any duplicate record.

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


//We use UNION ALL to combine the result of one SQL query with the result of other(s) 
//SQL queries. The result DO contain duplicate records.

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


//Task: To find the publisher with ID 1 and its most expensive book.
//Solution: Use subquery to find the most expensive book, then based on this result we query
//the publisher with ID =  1 to obtain the result.

 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');
    
    
- Note:
	We can UNION the result of a set with our input, as long as the field ttype matched,
	as follow:
	
	select ISBN,Title from BooksJC where PubId='3' 
	UNION
    	select "N/A", "My first ever book";
    	
	Notice that in the 2nd "select ..." statement, we provided the string "N/A" as the 
	1st field to match the first field from the 1st "select ..." statement, and the
	string "My first ever book" as a string to match the 2nd field of the 1st SELECT
	statement.
	Remember that as long as the field type matched, we are allowed to UNION the results
	of one SELECT-statement with the result of another SELECT-statement.