--Practice 1 Answers

-- #1
-- a 
select customernum, customername from customer 
where repnum = 30 and creditlimit = 10000

--b 
select sum(balance)as TotalSum from customer
where repnum = 15

--c 
select category,sum(onhand) as onhandsum
from item
group by category

--#2
--a 
select ownernum, lastname
from owner
where state in ('PA','GA')


--b 
select max(condofee) from condounit
where locationnum = 2 and bdrms >= 2

--c 
delete from condounit where condofee < 150

--#3
--a 
select reservationId, customer.customernum,lastname, firstname 
from reservation
left join customer 
on reservation.customernum = customer.customernum
where tripdate > '07/01/2016'

select reservationId, customer.customernum,lastname, firstname 
from reservation
cross join customer 
--on reservation.customernum = customer.customernum
where tripdate > '07/01/2016'


--b 
update reservation
set OtherFees = 5
where otherfees = 0
and tripId
in (select tripID from trip where type = 'Hiking')

update reservation
set OtherFees = 5
from reservation inner join trip
on reservation.tripId = trip.tripid
where otherfees = 0 and type = 'hiking'

--c
select tripname,state from trip
where season = 'summer'
order by state asc,  tripname asc

--#4

--a
CREATE TABLE Employee 
(
EmployeeID int NOT NULL, 
FirstName nvarchar(20) NOT NULL, 
LastName nvarchar(50) NOT NULL, 
Gender char(1) NOT NULL, 
DateOfBirth datetime NOT NULL, 
SSN char(9) NOT NULL
)

CREATE TABLE Project
(
ProjectID int NOT NULL, 
Description nvarchar(50) NOT NULL,
Status nvarchar(10) NOT NULL,
StartDate datetime NOT NULL,
ProjectedEndDate datetime NOT NULL,
Manager int NOT NULL
)


--b
INSERT INTO Employee
(EmployeeID, FirstName, LastName, Gender, DateOfBirth, SSN)
VALUES
(01,  'Juan', 'Perez', 'M', '12/01/1978', '123456789')

INSERT INTO Project
(ProjectID, Description, Status, StartDate, ProjectedEndDate, Manager)
VALUES
(100,  'NY Road', 'CLOSED', '01/01/2013', '01/30/2013', 01),


--c)	

SELECT Employee.FirstName, Employee.LastName 
FROM 
Employee JOIN 
Project ON Employee.EmployeeID = Project.Manager
GROUP BY Employee.FirstName, Employee.LastName
HAVING COUNT(Project.ProjectID) >= 2



--d)	
DELETE FROM Employee
WHERE 
DateOfBirth < '08/01/1970'
	AND
SSN LIKE '522%'

--#5)
create view myViews as
	select lastname,firstname 
	from rep inner join customer
	on rep.repnum = customer.repnum
	 where commission > 22000
	group by lastname,firstname



create view myViews as
	select distinct lastname,firstname 
	from rep inner join customer
	on rep.repnum = customer.repnum
	 where commission > 22000

