Database

select Customers.cname as c_id, sum(Products.price*Purchases.quantity) as z
from Purchases
right outer join Customers on (Customers.CID = Purchases.cust)
left outer join Stores on (Stores.SID = Purchases.Stores)
left outer join Products on (Products.SKU = Purchases.prod)
###where Stores.city = "Porto"
group by Customers.cname
order by z desc

Table: Words
Id Name
-- ---- 
1  One
2  Two
3  Three

Table: Letters
Id Letter
-- ---- 
1  A
1  B
1  C
2  A
2  D
3  A
3  C

SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='A'
INTERSECT
SELECT DISTINCT Words.Name FROM Words JOIN Letters ON Words.Id=Letters.Id WHERE Letters.Letter='C'
ORDER BY name COLLATE NOCASE

SELECT DISTINCT Words.Name FROM Words 
  INNER JOIN Letters AS L1 ON Words.Id=L1.Id
  INNER JOIN Letters AS L2 ON Words.Id=L2.Id
  WHERE L1.Letter='A' AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE

SELECT DISTINCT Words.Name FROM Words 
  INNER JOIN Letters AS L1 ON Words.Id=L1.Id AND L1.Letter='A'
  INNER JOIN Letters AS L2 ON Words.Id=L2.Id AND L2.Letter='C'
ORDER BY Words.Name COLLATE NOCASE

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_add_new_job_order`(IN param_customer_id int, IN param_cleaner_id int, IN param_job_order_date date, IN param_start_time time, IN param_end_time time, IN param_job_order_note text, IN param_is_contract bit, IN param_contract_id int, IN param_total_hours decimal(11,6), IN param_created_user int, OUT param_record_identity int)
BEGIN
	DECLARE param_job_order_id int;

	INSERT INTO job_orders (customer_id, cleaner_id, job_order_date, start_time, end_time, total_hours, job_order_note, is_contract, contract_id, created_date, created_user) VALUES (param_customer_id, param_cleaner_id, param_job_order_date, param_start_time, param_end_time, param_total_hours, param_job_order_note, param_is_contract, param_contract_id, NOW(), param_created_user);
	SET param_job_order_id = LAST_INSERT_ID();
	SET param_record_identity = param_job_order_id;

	UPDATE customers SET allow_delete = FALSE WHERE customer_id = param_customer_id;

	IF (param_is_contract = TRUE) THEN
	BEGIN
		UPDATE job_orders SET job_order_status = 6 WHERE job_order_id = param_job_order_id;
		UPDATE contracts SET remaining_hours = remaining_hours - param_total_hours WHERE contract_id = param_contract_id;
	END;
	END IF;
	-- add event log;
	CALL sp_add_event_log("JOBORDER", param_job_order_id, param_created_user, "Job order was created.");
	CALL sp_add_event_log("CUSTOMER", param_customer_id, param_created_user, "Job order was created.");
END

"SELECT * From CompletedOrders " & _
"UNION " & _
"SELECT * FROM CompletedOrdersHistory " & _
"WHERE Day(OrderDate)=<a href="/Members/theday">@TheDay</a> " & _
"AND Month(OrderDate)=@TheMonth " & _
"AND Year(OrderDate)=<a href="/Members/theyear">@TheYear</a>"

DECLARE @TheDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
 
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 2;
set <a href="/Members/Year">@year</a> = 2013;
set @TheDate = DateFromParts(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>);

SELECT * From CompletedOrders
WHERE OrderDate > @TheDate
UNION 
SELECT * FROM CompletedOrdersHistory 
WHERE OrderDate > @TheDate;

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
DECLARE <a href="/Members/month">@month</a> AS INTEGER;
DECLARE <a href="/Members/Day">@day</a> AS INTEGER;
DECLARE <a href="/Members/Year">@year</a> AS INTEGER;
 
set <a href="/Members/month">@month</a> = 12;
set <a href="/Members/Day">@day</a> = 3;
set <a href="/Members/Year">@year</a> = 2013;
set @startDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 0, 0, 0, 0);
set @stopDate = DATETIMEFROMPARTS(<a href="/Members/Year">@year</a>, <a href="/Members/month">@Month</a>, <a href="/Members/Day">@Day</a>, 23, 59, 59, 999);

SELECT * From CompletedOrders
WHERE OrderDate > @startDate AND OrderDate < @stopDate
UNION 
SELECT * FROM CompletedOrdersHistory 
WHERE OrderDate > @startDate AND OrderDate < @stopDate;