Resume
unsplash-image-SLbN0BLiadY.jpg

Database Management

SQL Management


Entity Relationship Diagram

Logical Model Updated.jpeg

Star Schema

View the Business Intelligence Page for more examples on how I approach data intensive projects.


 

SQL Simple Examples

--Simple script to query above ERD

With CC as
(
Select *, Count(CourseID) over (partition by PlayerID) as CountCourses,
  count(NoHoursEarned) over (partition by PlayerID) as CountCoursesEarned,
  Sum(NoHoursAttempted) over (partition by PlayerID) as SumHoursAttempted
From CoursePerformance
)
Select CourseID, PlayerID, CountCourses,CountCoursesEarned, SumHoursAttempted
From CC
-- Other sample SQL used: ncase, concat, lower, upper, stuff, joins, datediff, isnull, cast ,len, rtrim ,ltrim ,min, max

--Simple sample query
select p.PASSENGERNUM, p.PASSENGERNAME, COUNT(cruisenum)
from Passenger as p, Voyage as v
where p.PASSENGERNUM = v.PASSENGERNUM
group by p.PASSENGERNUM, PASSENGERNAME
having count(cruisenum) > 1
--Query Designer example
select * from Ship
SELECT   Passenger.PASSENGERNUM, Passenger.PASSENGERNAME, Ship.SHIPNAME
FROM     Passenger INNER JOIN
                  Voyage ON Passenger.PASSENGERNUM = Voyage.PASSENGERNUM INNER JOIN
                  Cruise ON Voyage.CRUISENUM = Cruise.CRUISENUM INNER JOIN
                  Ship ON Cruise.SHIPNUM = Ship.SHIPNUM
WHERE    (Ship.SHIPNAME = 'Prince Al')
--Give pairs of ships where the builder is the same  and the weight is different by more than 10000
select s1.SHIPNAME, s1.WEIGHT, s1.BUILDER, s2.SHIPNAME, s2.WEIGHT
from Ship as s1, ship as s2
where s1.BUILDER=s2.BUILDER
and (s1.WEIGHT-s2.WEIGHT) > 10000