--group by 1 (Number of Phone Numbers that each Custemaer has)
select Customer, COUNT(phoneNo) Phone_Numbers
from Customer_Phone_NoC2207
group by Customer;

--group by 2 (Number of Employees in each Division)
select Division, COUNT(eid) Employees
from EmployeeC2207
group by Division;

--group by 3 (Total payments for each Supplier)
select supplier, SUM(sup_payment) Total_Payment
from Movie_CopyC2207
group by supplier;

--group by 4 (Total number of Movies supplied by each day)----------------------------
SELECT Sup_date,COUNT(*)AS NumOfMovies
FROM Movie_CopyC2207
GROUP BY Sup_date;

--group by 5 (Number Of Movies from Each item)---------------------------------
SELECT itemNo,COUNT(*)As NumOfMovies
FROM Movie_CopyC2207
GROUP BY itemNo;

--group by 6 (number Of Movies Borrowed by each day)---------------------------------------
SELECT borrowedDate,COUNT(*)AS numOfBorrowedMovies
from C_Borrow_and_ReturnC2207
GROUP BY borrowedDate; 







--Nested 1 (Find Registered customers who have not returned Videos on time)
select cid, NICNo, fname, lname
from CustomerC2207
where cid in ( select customer
			   from C_Borrow_and_ReturnC2207
			   where DATEDIFF(DAY, borrowedDate, CONVERT(date, GETDATE())) > 7 and 
			         returnDate is null);

--Nested 2 (Find Employees who have borrowed videos)
select eid, NICNo, fname, lname, Division
from EmployeeC2207
where eid in ( select Employee
			   from E_Borrow_and_ReturnC2207)

--Nested 3 (Find registerd Suppliers who have supplied more than 10 Videos)
select sid, name, address
from SupplierC2207
where sid in (select supplier
                  from Movie_CopyC2207
                  group by supplier
                  having COUNT(supplier) > 9);
                  
--Nested 4 (Find Registered customers who have burrowed more than 1 Videos)
select cid, NICNo, fname, lname
from CustomerC2207
where cid in ( select customer 
				   from C_Borrow_and_ReturnC2207
				   group by customer
				   having COUNT(customer) > 1);





--Joint 1 (Customer Details with Phone Numbers)----------------------------------
SELECT c.fname,c.lname,c.address,cp.phoneNo
FROM CustomerC2207 c,Customer_Phone_NoC2207 cp
WHERE c.cid=cp.customer;

--Joint 2 (Suppliers' Phone numbers)---------------------------------------------
SELECT sid, name, phoneNo
FROM SupplierC2207 s,Supplier_Phone_NoC2207 sp
WHERE s.sid=sp.Supplier;

--Joint 3 (Employees with their Division Details)------------------------------------------------
SELECT e.eid,e.fname,e.lname,d.name,d.location,d.phoneNo
FROM EmployeeC2207 e,DivisionC2207 d
WHERE e.Division =d.did;








--select 1
select * from TypeC2207;


--select 2
select * from DivisionC2207;


--select 3
select * 
from C_Borrow_and_ReturnC2207
where DATEDIFF(DAY, borrowedDate, CONVERT(date, GETDATE())) > 7 and
      returnDate is null;


--select 4
select relesed_date, title, duration, language, rackNo 
from Movie_ItemC2207
order by relesed_date desc;


--select 5
select itemNo, copyNo
from Movie_CopyC2207
where DATEDIFF(DAY, sup_date, CONVERT(date, GETDATE())) > 120;


--select 6
select itemNo, copyNo, customer
from C_Borrow_and_ReturnC2207
where returnDate = GETDATE();


--select 7
select itemNo, copyNo, Employee
from E_Borrow_and_ReturnC2207
where returnDate = GETDATE();


--select 8
select Customer, phoneNo
from Customer_Phone_NoC2207
where Customer like 'C05';


--select 9
select itemNo, copyNo, customer, borrowedDate
from C_Borrow_and_ReturnC2207
where returnDate is null;

--select 10
select eid, fname, lname, address, NICNo
from EmployeeC2207
where Division like '03';