HI I need help with the following task, I have uploaded the scripts for you that creates the database, table, and loads the data. I need to be completed by Saturday night.
Query Two:Create one query that displays all the information in the tables based on one of the Short Descriptions. This query should add a column in the one query that parses out and displays the first 2 digits of the account number using SUBSTRING and CONVERT (if required). Execute the code and take a screenshot of the code and the results.
Note: The query will display all fields (account number, store name, short description, long description, balance, and any others you may have added. If you have more than one table, you will need to JOIN the tables to include columns from each table) using one store name of your choice to filter the results of the query.
Â
Â
--Creates Database CREATE DATABASE [TeamCChartOfAccounts]; --Creates Accounts Table USE TeamCChartOfAccounts; CREATE TABLE [dbo].[Accounts] ( [Account_No] [INT] NOT NULL, [Account_Type] [NCHAR](20) NOT NULL, [Short_Description] [NCHAR](50) NOT NULL, [Long_Description] [NCHAR](200) NOT NULL, [Balance] [MONEY] NOT NULL, CONSTRAINT pk_acct_no PRIMARY KEY (Account_No) ); --Loads data USE TeamCChartOfAccounts; INSERT INTO dbo.Accounts (Account_No, Account_Type, Short_Description, Long_Description, Balance) VALUES (100003, 'Assets', 'Petty Cash', 'Operating Cash - Scottsdale', 2320.00), (100010, 'Assets', 'Tempe Undeposited Receipts', 'Undeposited Reports', 603.00), (112000, 'Assets', 'Phoenix', 'Accounts Receivable', 1319.00), (112001, 'Assets', 'Phoenix', 'Reserve for Bad Debts', 1836.00), (121002, 'Inventory', 'Calamata Olive Bread', 'Tempe Bakery Department Merchandise Inventory', 1415.00), (121003, 'Inventory', 'Ciabatta', 'Tempe Bakery Department Merchandise Inventory', 1253.00), (131003, 'Inventory', 'Duck', 'Tempe Meat and Seafood Merchandise Inventory', 2458.00), (131004, 'Inventory', 'Pheasant', 'Tempe Meat and Seafood Merchandise Inventory', 943.00), (141003, 'Inventory', 'Cortland Apples', 'Tempe Produce Merchandise Inventory', 54.00), (141004, 'Inventory', 'Empire Apples', 'Tempe Produce Merchandise Inventory', 999.00), (151002, 'Inventory', 'Chedder', 'Tempe Cheese & Dairy Merchandise Inventory', 311.00), (151003, 'Inventory', 'Brie', 'Tempe Cheese & Dairy Merchandise Inventory', 326.00), (161003, 'Inventory', 'Blush Wines', 'Tempe Wine Merchandise Inventory', 1169.00), (161004, 'Inventory', 'Sparkling Wines', 'Tempe Wine Merchandise Inventory', 1377.00), (123003, 'Inventory', 'Calamata Olive Bread', 'Phoenix Bakery Department Merchandise Inventory', 553.00), (123004, 'Inventory', 'Ciabatta', 'Phoenix Bakery Department Merchandise Inventory', 1068.00), (133003, 'Inventory', 'Duck', 'Phoenix Meat and Seafood Merchandise Inventory', 840.00), (133004, 'Inventory', 'Pheasant', 'Phoenix Meat and Seafood Merchandise Inventory', 1833.00), (143003, 'Inventory', 'Cortland Apples', 'Phoenix Produce Merchandise Inventory', 1284.00), (143004, 'Inventory', 'Empire Apples', 'Phoenix Produce Merchandise Inventory', 272.00), (153003, 'Inventory', 'Brie', 'Phoenix Cheese & Dairy Merchandise Inventory', 1919.00), (153004, 'Inventory', 'Gouda', 'Phoenix Cheese & Dairy Merchandise Inventory', 464.00), (163003, 'Inventory', 'Blush Wines', 'Phoenix Wine Merchandise Inventory', 196.00), (163004, 'Inventory', 'Sparkling Wines', 'Phoenix Wine Merchandise Inventory', 2054.00), (125003, 'Inventory', 'Calamata Olive Bread', 'Scottsdale Bakery Department Merchandise Inventory', 2451.00), (125004, 'Inventory', 'Ciabatta', 'Scottsdale Bakery Department Merchandise Inventory', 901.00), (135003, 'Inventory', 'Duck', 'Scottsdale Meat and Seafood Merchandise Inventory', 655.00), (135004, 'Inventory', 'Pheasant', 'Scottsdale Meat and Seafood Merchandise Inventory', 1773.00), (145003, 'Inventory', 'Cortland Apples', 'Scottsdale Produce Merchandise Inventory', 883.00), (145004, 'Inventory', 'Empire Apples', 'Scottsdale Produce Merchandise Inventory', 238.00), (147002, 'Inventory', 'Brie', 'Scottsdale Cheese & Dairy Merchandise Inventory', 1804.00), (147003, 'Inventory', 'Gouda', 'Scottsdale Cheese & Dairy Merchandise Inventory', 1455.00), (149002, 'Inventory', 'Blush Wines', 'Scottsdale Wine Merchandise Inventory', 376.00), (149003, 'Inventory', 'Sparkling Wines', 'Scottsdale Wine Merchandise Inventory', 722.00), (160002, 'Assets', 'Tempe', 'Prepaid Software Maintenance', 1517.00), (160003, 'Assets', 'Phoenix', 'Prepaid Advertising', 2267.00), (171003, 'Assets', 'Scottsdale', 'Accounts Receivable', 1119.00), (171004, 'Assets', 'Tempe', 'Notes Receivable', 2239.00), (174002, 'Assets', 'Phoenix', 'Furnature and Fixtures', 428.00), (174003, 'Assets', 'Phoenix', 'Accumulated Depreciation - Furnature & Fixtures', 250.00), (181200,'Assets','Scottsdale','Goodwill',1681.00), (181300,'Assets','Tempe ','Charity and Donations',418.00), (200003,'Assets','Boardman Fine Foods','Scottsdale Bakery Department Vendor',1993.00), (200011,'Assets','Brooks Surf and Turf','Tempe Meat and Seafood Vendor',1827.00), (210003,'Payouts','Scottsdale Sales Tax','Scottsdale Sales Tax Payable',442.00), (210004,'Payouts','Tempe County Tax','Tempe County Tax',1490.00), (220003,'Payouts','FICA W/H','FICA Withholding - Payroll',1131.00), (220004,'Payouts','Unemployment Pemium','Unemployment',1727.00), ('400003','Assets','Calamata Olive Bread','Tempe Bakery Department Sales','2,418.00'), ('400004','Assets','Ciabatta','Tempe Bakery Department Sales','1,881.00'), ('411003','Assets','Duck','Tempe Meat and Seafood Sales','294.00'), ('411004','Assets','Pheasant','Tempe Meat and Seafood Sales','2,416.00'), ('420003','Assets','Cortland Apples','Tempe Produce Sales','$1,314.00'), ('420004','Assets','Empire Apples','Tempe Produce Sales','2,240.00'), ('430003','Assets','Brie','Tempe Cheese & Dairy Sales','1,002.00'), ('430004','Assets','Gouda','Tempe Cheese & Dairy Sales','2,434.00'), ('440003','Assets','Blush Wines','Tempe Wine Sales','1,896.00'), ('440004','Assets','Sparkling Wines','Tempe Wine Sales','157.00'), (450003,'Assets', 'Calamata Olive Bread', 'Phoenix Bakery Department Sales', 1904.00), (450004,'Assets', 'Ciabatta', 'Phoenix Bakery Department Sales', 261.00), (460003,'Assets', 'Duck', 'Phoenix Meat and Seafood Sales', 2379.00), (460004,'Assets', 'Pheasant', 'Phoenix Meat and Seafood Sales', 1811.00), (470003,'Assets', 'Cortland Apples', 'Phoenix Produce Sales', 1148.00), (470004,'Assets', 'Empire Apples', 'Phoenix Produce Sales', 696.00), (480003,'Assets', 'Brie', 'Phoenix Cheese & Dairy Sales', 772.00), (480004,'Assets', 'Gouda', 'Phoenix Cheese & Dairy Sales', 424.00), (490003,'Assets', 'Blush Wines', 'Phoenix Wine Sales', 421.00), (490004,'Assets', 'parkling Wines', 'Phoenix Wine Sales', 527.00),
