SUB QUERIES - Revision history

Bacchas at 23:08, 14 November 2017

2017-11-14T23:08:19Z

← Older revision		Revision as of 23:08, 14 November 2017
Line 51:		Line 51:
	WHERE b.customer_id = a.customer_id)		WHERE b.customer_id = a.customer_id)
	ORDER BY CUSTOMER_ID;		ORDER BY CUSTOMER_ID;

			SUBQUERY is executed for each row on the sales table

	[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 23:04, 14 November 2017

2017-11-14T23:04:17Z

← Older revision		Revision as of 23:04, 14 November 2017
Line 44:		Line 44:
	WHERE s.sales_date = st.sales_date;e		WHERE s.sales_date = st.sales_date;e

			Problem: Select data from sales table where the selected TOTAL_AMOUNT are greater than the average TOTAL_AMOUNT of their respective customer
			SELECT *
			FROM SALES a
			WHERE TOTAL_AMOUNT >(SELECT AVG(TOTAL_AMOUNT)
			FROM SALES b
			WHERE b.customer_id = a.customer_id)
			ORDER BY CUSTOMER_ID;

	[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 21:55, 14 November 2017

2017-11-14T21:55:44Z

← Older revision		Revision as of 21:55, 14 November 2017
Line 31:		Line 31:
	Used mostly when we join detailed data with aggregated data		Used mostly when we join detailed data with aggregated data

	SELECT s.sales_date, s.order_id, s.customer_id, s.salesperson_id, s.total_amount, st.sales_total		SELECT
	FROM sales s, (SELECT sum(total_amount) AS sales_total FROM sales) st		s.sales_date,
			s.order_id,
			s.customer_id,
			s.salesperson_id,
			s.total_amount,
			st.sales_total
			FROM sales s,
			(SELECT sales_date, sum(total_amount) AS sales_total
			FROM sales
			GROUP BY sales_date) st
			WHERE s.sales_date = st.sales_date;e


	[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 21:48, 14 November 2017

2017-11-14T21:48:44Z

← Older revision		Revision as of 21:48, 14 November 2017
Line 27:		Line 27:
	WHERE sales_date = '01-jan-2015'		WHERE sales_date = '01-jan-2015'
	)		)

			USING subqueries in the FROM clause
			Used mostly when we join detailed data with aggregated data

			SELECT s.sales_date, s.order_id, s.customer_id, s.salesperson_id, s.total_amount, st.sales_total
			FROM sales s, (SELECT sum(total_amount) AS sales_total FROM sales) st


	[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 20:40, 14 November 2017

2017-11-14T20:40:15Z

@@ Line 16: / Line 16: @@
   WHERE CUSTOMER_ID IN
   (SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH' OR last_name = 'mann'
 [[#Rules for Character Strings Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]

Bacchas at 20:20, 14 November 2017

2017-11-14T20:20:23Z

← Older revision		Revision as of 20:20, 14 November 2017
Line 1:		Line 1:
	A SUB QUERY is a query within a query which can return one or more rows		A SUB QUERY is a query within a query which can return one or more rows

	SELECT * FROM SALES		SELECT * FROM SALES
	WHERE CUSTOMER_ID =		WHERE CUSTOMER_ID =
	(SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH'		(SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH'

	A SUBQUERY executes first before the main query		A SUBQUERY executes first before the main query, SO the customer id was returned from the the subquery then passed to the original query

			When the subquery returns more than one value use the IN instead of the =

	SELECT * FROM SALES		SELECT * FROM SALES
	WHERE CUSTOMER_ID IN		WHERE CUSTOMER_ID IN
	(SELECT CUSTOMER_ID FROM CUSTOMER WHERE REGION ='SOUTH')		(SELECT CUSTOMER_ID FROM CUSTOMER WHERE REGION ='SOUTH')

			SELECT * FROM SALES
			WHERE CUSTOMER_ID IN
			(SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH' OR last_name = 'mann'

	[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Rules for Character Strings Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas: Created page with "A SUB QUERY is a query within a query which can return one or more rows SELECT * FROM SALES WHERE CUSTOMER_ID = (SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH'..."

2017-11-14T20:13:04Z

Created page with "A SUB QUERY is a query within a query which can return one or more rows SELECT * FROM SALES WHERE CUSTOMER_ID = (SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH'..."

New page

A SUB QUERY is a query within a query which can return one or more rows
SELECT * FROM SALES
WHERE CUSTOMER_ID =
(SELECT CUSTOMER_ID FROM CUSTOMER WHERE LAST_NAME = 'JOSEPH'

A SUBQUERY executes first before the main query

SELECT * FROM SALES
WHERE CUSTOMER_ID IN
(SELECT CUSTOMER_ID FROM CUSTOMER WHERE REGION ='SOUTH')

[[#Rules for Character Strings Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]