SQL Test, Sql Server

SQL Test

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

/**** RUN THE FOLLOWING CODE BLOCK BEFORE ANSWERING THE BELOW QUESTIONS ****/
 CREATE TABLE ACCOUNTS
 (ACCOUNT_NUMBER INT
, ACCOUNT_NAME VARCHAR(100)
 , ADDRESS VARCHAR(100)
 , CITY VARCHAR(100)
 , STATE VARCHAR(5)
 , ZIP VARCHAR(10)
 , COUNTRY VARCHAR(5)
 )
 CREATE TABLE INVOICES
 (ACCOUNT_NUMBER INT
, INVOICE_NUMBER VARCHAR(100)
 , INVOICE_DATE DATE
, INVOICE_AMOUNT DECIMAL(15,2)
 , CURRENCY VARCHAR(5)
 )
 INSERT INTO ACCOUNTS VALUES (123, 'ABC Supply', '6425 Pat Booker Rd', 'Live Oak', 'TX', '78160', 'US')
 INSERT INTO ACCOUNTS VALUES (456, 'Custom Paint', '8950 Broadway', 'San Antonio', 'TX', '78159', 'US')
 INSERT INTO ACCOUNTS VALUES (789, 'Master Corporation', '4830 W. 42nd St', 'New York', 'NY', '10036',
'US')
 INSERT INTO ACCOUNTS VALUES (135, 'Direct Line', '1245 HWY 35', 'Austin ', 'TX', '78680', 'US')
 INSERT INTO ACCOUNTS VALUES (246, 'Essential Systems', '10 Keith Rd', 'London', '', 'UB3 5AX', 'UK')
 INSERT INTO ACCOUNTS VALUES (579, 'Board Manufacturing', '564 W. Holly St', 'Boston', 'MA', '2120',
'US')
 INSERT INTO ACCOUNTS VALUES (680, 'Yellow Squared', '9640 N. Bear Hollow Way', 'Denver', 'CO',
'80240', 'US')
 INSERT INTO INVOICES VALUES (123, 'I-123456', '4/1/2018', 1560.25, 'USD')
 INSERT INTO INVOICES VALUES (456, 'I-123457', '4/1/2018', 5240, 'USD')
 INSERT INTO INVOICES VALUES (789, 'I-123458', '4/4/2018', 802.5, 'USD')
 INSERT INTO INVOICES VALUES (135, 'I-123459', '4/8/2018', 640.96, 'USD')
 INSERT INTO INVOICES VALUES (246, 'I-123460', '4/21/2018', 54.95, 'GBP')
 INSERT INTO INVOICES VALUES (680, 'I-123462', '4/29/2018', 7278.95, 'USD')
 INSERT INTO INVOICES VALUES (123, 'I-123463', '5/1/2018', 1032.56, 'USD')
 INSERT INTO INVOICES VALUES (789, 'I-123464', '5/4/2018', 624.23, 'USD')
 INSERT INTO INVOICES VALUES (135, 'I-123465', '5/8/2018', 710.69, 'USD')
 INSERT INTO INVOICES VALUES (246, 'I-123466', '5/21/2018', 62.04, 'GBP')
 INSERT INTO INVOICES VALUES (680, 'I-123468', '5/29/2018', 6130.47, 'USD')
 INSERT INTO INVOICES VALUES (789, 'I-123469', '6/4/2018', 742.18, 'USD')
 INSERT INTO INVOICES VALUES (135, 'I-123470', '6/8/2018', 596.75, 'USD')
/** QUESTION 1 **/
--SUMMARIZE INVOICE AMOUNT BY ACCOUNT NAME FOR ACCOUNTS EXISTING IN BOTH THE ACCOUNTS AND INVOICES TABLE.
--PLEASE DISPLAY THE FOLLOWING COLUMNS AND ORDER BY ACCOUNT NAME ASCENDING: ACCOUNT_NAME, CURRENCY, TOTAL
--INVOICE AMOUNT
select a.account_name, i.currency, sum(invoice_amount) as 'Total invoice Amount'
from accounts A
left join invoices I on a. account_number=i.account_number
group by a.account_name, i.currency
order by a.account_name asc

/** QUESTION 2 **/
--IDENTIFY ALL ACCOUNTS WITH NO INVOICES PRESENT IN THE INVOICES TABLE. PLEASE DISPLAY ALL COLUMNS IN THE
--ACCOUNTS TABLE IN YOUR OUTPUT.
select a.account_number,a.account_name,a.address,a.city,a.state,a.zip,a.country
from accounts a
left join invoices i on a.account_number=i.account_number
where i.invoice_number is null

/** QUESTION 3 **/
--WRITE A QUERY TO SOLVE QUESTION 2 IN A DIFFERENT WAY
select a.account_number,a.account_name,a.address,a.city,a.state,a.zip,a.country
from accounts a
left join invoices i on a.account_number=i.account_number
where case when i.invoice_number is null then 'n' else 'y' end = 'n'

/** QUESTION 4 **/
--SUMMARIZE THE TOTAL INVOICE AMOUNT BY MONTH IN USD (USING GBP TO USD RATE OF 1.33. PLEASE DISPLAY
--INVOICE MONTH (ANY FORMAT IS FINE) AND TOTAL INVOICE AMOUNT ORDERED BY INVOICE MONTH
select datename(month,invoice_date)as Invoice_Month,sum(case when currency='GBP' then invoice_amount*1.33 else invoice_amount end) as Invoice_Amount
from invoices
Group by datename(month,invoice_date),month(invoice_date)
order by month(invoice_date)


run \| edit \| history \| help	0