Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
Task2_Final
SQl_QUEST_2_PRESENTATION_IMG_COUNT
Count by days
updated
https://rextester.com/IYRZ86271
Finding Row Wise Max Value From Table
Common Table Expression
Status History
trigger
Gangadhar