19Nov.sql

use Northwind

select distinct E.FirstName, E.LastName, Self.ReportsTo, Other.FirstName + ' ' + Other.LastName as boss
from Employees as E 
inner join Employees as Self on E.EmployeeID = Self.ReportsTo
inner join Employees as Other on Self.EmployeeID = Other.EmployeeID

select E.FirstName, E.LastName
from Employees as E 
left outer join Employees as Self on E.EmployeeID = Self.ReportsTo
where Self.EmployeeID IS NULL

select * from Employees

-----------------------------------------------

use library

select firstname + ' ' + lastname as name, street + ', ' + city + ', ' + state + ', ' + zip as address
from member as m
inner join adult as a on a.member_no = m.member_no

select i.isbn, copy_no, on_loan, title, translation, cover
from title as t
inner join item as i on i.title_no = t.title_no
inner join copy as c on c.title_no = t.title_no
where i.isbn = 1 or i.isbn = 500 or i.isbn = 1000
order by i.isbn

--doesn't work
select m.member_no, firstname, lastname
from member as m
left outer join reservation as r on r.member_no = m.member_no
where m.member_no = 250 or m.member_no = 342 or m.member_no = 1675

--1 missing: AZ
--------------


use Northwind

select CompanyName, O.OrderID,  sum(unitprice * quantity) value
from Customers as C
inner join Orders as O on O.CustomerID = C.CustomerID
inner join [Order Details] as OD on OD.OrderID = O.OrderID
group by CompanyName, O.OrderID
--order by CompanyName, O.OrderID

union

select CompanyName, O.OrderID,  sum(unitprice * quantity) value
from Customers as C
inner join Orders as O on O.CustomerID = C.CustomerID
inner join [Order Details] as OD on OD.OrderID = O.OrderID
group by CompanyName, O.OrderID