SQL Inner join Movie Databases

--drop table Movies
--drop table Actors
--drop table Quotes
--drop table xrefMovieActor
--drop table xrefActorQuote
--drop table xrefMovieQuotes

use DragonsQuotes
go

create table Quotes
(
      QuoteID int identity (1,1) primary key
      , Quote varchar(100)
)

Insert into Quotes (Quote) values ('Hakuna Matata')
Insert into Quotes (Quote) values ('Dat-da-dah!  We''re dead.')
Insert into Quotes (Quote) values ('Well you shouldn''t have been trying to steal our Ozone.')
Insert into Quotes (Quote) values ('I am Iron Man.  The suit and I are one.')
Insert into Quotes (Quote) values ('This is some rescue. When you came in here, didn''t you have a plan for getting out?')

select *
from Quotes

create table xrefActorQuote
(
      xrefAQID int identity (1,1) primary key
      , ActorID int
      , QuoteID int
);

insert into xrefActorQuote (ActorID, QuoteID) values (1,1)
insert into xrefActorQuote (ActorID, QuoteID) values (2,2)
insert into xrefActorQuote (ActorID, QuoteID) values (3,3)
insert into xrefActorQuote (ActorID, QuoteID) values (4,4)
insert into xrefActorQuote (ActorID, QuoteID) values (5,5)

select *
from xrefActorQuote

create table Movies
(
      MovieID int identity(1,1) not null primary Key
      ,MovieName varchar(100)
)

Insert into Movies
(
      MovieName
)

values
(
      'The Lion King'
)

Insert into Movies
(
      MovieName
)

values
(
      'How to Train Your Dragon'
)

Insert into Movies
(
      MovieName
)

values
(
      'Men in Black 2'
)

Insert into Movies
(
      MovieName
)

values
(
      'Iron Man 2'
)

Insert into Movies
(
      MovieName
)

values
(
      'Star Wars'
)

Select * 

from Movies

create table xrefMovieQuotes
(
xrefMQID int identity(1,1) not null primary Key
,MovieID int
,QuotesID int
)

insert into xrefMovieQuotes(MovieID,QuotesID) values (1,1)
insert into xrefMovieQuotes(MovieID,QuotesID) values (2,2)
insert into xrefMovieQuotes(MovieID,QuotesID) values (3,3)
insert into xrefMovieQuotes(MovieID,QuotesID) values (4,4)
insert into xrefMovieQuotes(MovieID,QuotesID) values (5,5) 

select * from xrefMovieQuotes

create table Actor
(
      ActorID int identity (1,1) PRIMARY KEY,
      ActorName varchar (40)
);

insert into Actor
(ActorName)
Values
('Rafiki')

insert into Actor
(ActorName)
Values
('Hiccup')

insert into Actor
(ActorName)
Values
('Will Smith')

insert into Actor
(ActorName)
Values
('Robert Downey Jr.')

insert into Actor
(ActorName)
Values
('Carrie Fisher')

select *
from Actor

create table xrefMovieActor
(
      xrefMAID int identity (1,1) Primary Key,
      MovieID int,
      ActorID int
);

insert into xrefMovieActor
(MovieID, ActorID)
Values
('1', '1')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('2', '2')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('3', '3')

insert into xrefMovieActor
(MovieID, ActorID)
Values
('4', '4')
insert into xrefMovieActor
(MovieID, ActorID)
Values
('5', '5')

select *
from xrefMovieActor


select *
from xrefMovieActor


Select A.ActorName, M.MovieName
from xrefMovieActor xMA
inner join Actor A
      on A.ActorID = xMA.ActorID
inner join xrefActorQuote xAQ
      on A.ActorID = xAQ.ActorID
inner join Movies M
      on M.MovieID = xMA.MovieID
where MovieName = 'Men in Black 2'

select *
from Movies M
inner join xrefMovieActor xrefMA
      on M.MovieID = xrefMA.MovieID
inner join xrefMovieQuotes xrefMQ
      on xrefMA.MovieID = xrefMQ.MovieID
inner join Quotes Q
      on Q.QuoteID = xrefMQ.QuotesID
inner join Actor A
      on A.ActorID = xrefMA.ActorID
where QUOTE like '%suit%'

select a.ActorName,m.MovieName
from Actor a
inner join xrefMovieActor x on x.ActorID = a.ActorID
inner join Movies m on m.MovieID = x.MovieID
where a.ActorName = 'Will Smith'

SQL If/Case statement practice

use AdventureWorks
go

--1. The boss needs a quick query.  All he needs is a query with Sales Order Id and --whether the order was online or offline.  Select those two columns and display --either Offline or Online in the second column

select SalesOrderID,
case OnlineOrderFlag
 when 0 then 'offline'
 when 1 then 'online'
 end online
from Sales.SalesOrderHeader SOD


--2. It's time for bonuses again.  Go through all sales, listing the  firstname and --lastname for the salesperson, as well as the sales level.  
--less than a $50,000 sale is the Tin level
--less than a $100,000 sale is the Silver level
--Anything over $100,000 sale is the Gold level

select LastName, FirstName,
 case
  when TotalDue < 50000 then 'Tin Level'
  when TotalDue < 100000 then 'Silver Level'
  when TotalDue > 100000 then 'Gold Level'
 end
from Sales.SalesOrderHeader soh
 inner join Person.Contact p
  on soh.ContactID = p.ContactID
  


--Using an if statement, you are going to write a query that has two modes.  
--If a variable is set to Sales mode, it will give you a unique list of all 
--the names of sales people in 2004.  If the variable is set to Customer mode, 
--it will give you a unique list of all the names of the customers in 2004.

declare @mode varchar (150)
set @mode = 'c' 
If @mode = 's'
 begin
 select distinct firstname, lastname
 from person.Contact pc
 inner join HumanResources.Employee hr
 on pc.ContactID = hr.ContactID
 inner join Sales.SalesOrderHeader so
 on hr.EmployeeID = so.contactID
 where so.OrderDate between '20040101' and '20041231'
 end

else if @mode = 'c'
 begin
 select distinct firstname, lastname 
 from Person.Contact pc
 inner join Sales.SalesOrderHeader so
 on pc.ContactID = so.ContactID
 where so.OrderDate between '20040101' and '20041231'
 end 

SQL If and Case statements

If Statements example:

declare @num int
set @num = 4

select @num

if @num = 3 
begin
 select 'yay...its 3'
end
else
begin
 select 'boo...no 3'
end

Case Statements example

use AdventureWorks
go

select FirstName, LastName,
 case EmailPromotion
  when 1 then 'EmailPromo'
  when 2 then 'TextPromo'
  else 'Leave Me Alone'
 end as promo
from Person.Contact

SQL Union operator

data types need to be the same but columns don't have to have the same name
The union operator brings back all rows that are the same in the tables
The union all operator brings back only the rows that aren't the same.

select bid, name, tid
from boys
where name like 'B%'
union
select bid, name, tid
from boys
where name like 'J%'


select bid, name, tid
from boys
where name like 'B%'
union all
select bid, name, tid
from boys
where name like 'J%'

self join

select name, el.name
from employees e
    inner join employees e1
    on e.mid = e1.empid

Visual Basic Methods

The following is an example of how to use a sub and a function in order to reuse code.

   Public Class Form1
   
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
   Dim str1 As String = TextBox1.Text
   Dim strFormatted As String = ""
    
   strFormatted = digitsOnly(str1)
   checkNumberLength(strFormatted)
   TextBox1.Text = strFormatted
    
  End Sub
    
  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
  Dim strFormatted As String = "("
  TextBox1.Text = digitsOnly(TextBox1.Text)
  checkNumberLength(TextBox1.Text)
    
  Dim x As Integer = 1
  For Each c As String In TextBox1.Text
  If x = 4 Then
  strFormatted += ") "
  End If
  If x = 7 Then
  strFormatted += "-"
  End If
    
  strFormatted += c
  x += 1
  Next
  TextBox1.Text = strFormatted
  End Sub
    
  Private Function digitsOnly(ByVal str As String) As String
  Dim strFormatted As String = ""
    
  For Each c As String In str
  If Char.IsDigit(c) Then
  strFormatted += c
  End If
  Next
  Return strFormatted
  End Function
    
  Private Sub checkNumberLength(ByVal num As String)
  If num.Length <> 10 Then
  MsgBox("Sorry, you need 10 numbers")
  End If
  End Sub
  End Class

SQL Subqueries Practice 1

1. Select firstname, lastname, and email of employees who are sales people.

use adventureworks
go

select FirstName, LastName, EmailAddress
from person.Contact p
 inner join HumanResources.Employee HRE
 on p.ContactID = HRE.ContactID
where hre.EmployeeID in
 (select distinct SalesPersonID from Sales.SalesPerson)

2. Get emails of all sales people who have made a sale greater than $150K.

select emailaddress
from person.Contact p
 inner join HumanResources.Employee HRE
  on p.ContactID = HRE.ContactID
 inner join Sales.SalesPerson SP
  on HRE.EmployeeID = SP.SalesPersonID
where SP.SalesPersonID in
 (select SalesPersonID from Sales.SalesOrderHeader sh
where sh.TotalDue > 150000)