Dealing with Securities

Write a query to display the company name from Issuers and the security symbol from Securities for all securities.

SELECT CompanyName, Symbol FROM Issuers 

INNER JOIN Securities ON FK_IssuerId = Issuers.Id

Display the announcement headline, company name, and document name for all announcements that have documents attached.

SELECT 
	Announcements.Headline AS 'Announcement Headline', 
	Issuers.CompanyName AS 'Company Name', Documents.Name  
FROM Announcements
	INNER JOIN Issuers ON Issuers.Id = Announcements.FK_IssuerId
	INNER JOIN Documents ON Announcements.FK_DocumentId = Documents.Id

List all issuers and their securities. Include issuers even if they have no securities listed.

SELECT 
	Issuers.CompanyName, 
	Securities.Symbol 
FROM Issuers
	LEFT OUTER JOIN  Securities ON FK_IssuerId = Issuers.Id

Find all issuers that have NO securities listed

SELECT 
	Issuers.CompanyName AS 'Companies with out Securities'
FROM Issuers
	LEFT OUTER JOIN  Securities ON FK_IssuerId = NULL

Count how many securities each issuer has, showing company name and count.

SELECT CompanyName, Count(Symbol) AS 'Total Number of Securities' FROM Issuers 

INNER JOIN Securities ON FK_IssuerId = Issuers.Id

GROUP BY CompanyName

Find issuers that have more than 5 securities listed.

SELECT CompanyName, Count(Symbol) AS 'Total Number of Securities' FROM Issuers 

INNER JOIN Securities ON FK_IssuerId = Issuers.Id

GROUP BY CompanyName
 HAVING  Count(Symbol) > 5

Find issuers that have at least one security with a maturity date in the next 12 months.

SELECT FORMAT(Securities.MaturityDate, 'MM-dd-yyyy'), Securities.Symbol, Issuers.CompanyName FROM Securities
Inner Join Issuers  ON FK_IssuerId = Securities.FK_IssuerId	
WHERE MaturityDate >= GETDATE()							
  AND MaturityDate < DATEADD(month, 12, GETDATE());		

Create a query that categorizes securities by IPO price:
  "Low" if IPO price < 10
  "Medium" if IPO price between 10 and 50
  "High" if IPO price > 50

SELECT IPOPrice, Symbol,
	CASE WHEN IPOPrice < 10 THEN 'Low'
	WHEN IPOPrice between  10 and 50 THEN 'Medium'
	ELSE 'High'
END AS SecurityCategory
FROM Securities
Order by  IPOPrice;