#Amazon
Find the total cost of each customer's orders. Output customer's id, first name, and the total order cost. Order records by customer's first name alphabetically.
table name: customers
table name: orders
Solution:
select c.id, c.first_name, sum(total_order_cost) as totalCost
from customers as c
join orders as o
on c.id = o.cust_id
group by c.id,c.first_name
Output:
SQL Script:
USE [StrataScratch]
GO
CREATE TABLE [dbo].[customers](
[id] [int] NOT NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[city] [varchar](50) NULL,
[address] [varchar](50) NULL,
[phone_number] [varchar](50) NULL,
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orders](
[id] [int] NOT NULL,
[cust_id] [int] NULL,
[order_date] [datetime] NULL,
[order_details] [varchar](50) NULL,
[total_order_cost] [int] NULL,
CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (1, N'Mark', N'Thomas', N'Arizona', N'4476 Parkway Drive', N'602-993-5916')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (2, N'Mona', N'Adrian', N'Los Angeles', N'1958 Peck Court', N'714-409-9432')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (3, N'Farida', N'Joseph', N'San Francisco', N'3153 Rhapsody Street', N'813-368-1200')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (4, N'William', N'Daniel', N'Denver', N'', N'813-368-1200')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (5, N'Henry', N'Jackson', N'Miami', N'', N'808-601-7513')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (6, N'Jack', N'Aiden', N'Arizona', N'4833 Coplin Avenue', N'480-303-1527')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (7, N'Jill', N'Michael', N'Austin', N'', N'813-297-0692')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (8, N'John', N'Joseph', N'San Francisco', N'', N'928-386-8164')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (9, N'Justin', N'Alexander', N'Denver', N'4470 McKinley Avenue', N'970-433-7589')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (10, N'Lili', N'Oliver', N'Los Angeles', N'3832 Euclid Avenue', N'530-695-1180')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (11, N'Frank', N'Jacob', N'Miami', N'1299 Randall Drive', N'808-590-5201')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (12, N'Eva', N'Lucas', N'Arizona', N'4379 Skips Lane', N'301-509-8805')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (13, N'Emma', N'Isaac', N'Miami', N'', N'808-690-5201')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (14, N'Liam', N'Samuel', N'Miami', N'', N'808-555-5201')
GO
INSERT [dbo].[customers] ([id], [first_name], [last_name], [city], [address], [phone_number]) VALUES (15, N'Mia', N'Owen', N'Miami', N'', N'808-640-5201')
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (1, 3, CAST(N'2019-03-04T00:00:00.000' AS DateTime), N'Coat', 100)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (2, 3, CAST(N'2019-03-01T00:00:00.000' AS DateTime), N'Shoes', 80)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (3, 3, CAST(N'2019-03-07T00:00:00.000' AS DateTime), N'Skirt', 30)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (4, 7, CAST(N'2019-02-01T00:00:00.000' AS DateTime), N'Coat', 25)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (5, 7, CAST(N'2019-03-10T00:00:00.000' AS DateTime), N'Shoes', 80)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (6, 15, CAST(N'2019-02-01T00:00:00.000' AS DateTime), N'Boats', 100)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (7, 15, CAST(N'2019-01-11T00:00:00.000' AS DateTime), N'Shirts', 60)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (8, 15, CAST(N'2019-03-11T00:00:00.000' AS DateTime), N'Slipper', 20)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (9, 15, CAST(N'2019-03-01T00:00:00.000' AS DateTime), N'Jeans', 80)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (10, 15, CAST(N'2019-03-09T00:00:00.000' AS DateTime), N'Shirts', 50)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (11, 5, CAST(N'2019-02-01T00:00:00.000' AS DateTime), N'Shoes', 80)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (12, 12, CAST(N'2019-01-11T00:00:00.000' AS DateTime), N'Shirts', 60)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (13, 12, CAST(N'2019-03-11T00:00:00.000' AS DateTime), N'Slipper', 20)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (14, 4, CAST(N'2019-02-01T00:00:00.000' AS DateTime), N'Shoes', 80)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (15, 4, CAST(N'2019-01-11T00:00:00.000' AS DateTime), N'Shirts', 60)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (16, 3, CAST(N'2019-04-19T00:00:00.000' AS DateTime), N'Shirts', 50)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (17, 7, CAST(N'2019-04-19T00:00:00.000' AS DateTime), N'Suit', 150)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (18, 15, CAST(N'2019-04-19T00:00:00.000' AS DateTime), N'Skirt', 30)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (19, 15, CAST(N'2019-04-20T00:00:00.000' AS DateTime), N'Dresses', 200)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (20, 12, CAST(N'2019-01-11T00:00:00.000' AS DateTime), N'Coat', 125)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (21, 7, CAST(N'2019-04-01T00:00:00.000' AS DateTime), N'Suit', 50)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (22, 7, CAST(N'2019-04-02T00:00:00.000' AS DateTime), N'Skirt', 30)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (23, 7, CAST(N'2019-04-03T00:00:00.000' AS DateTime), N'Dresses', 50)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (24, 7, CAST(N'2019-04-04T00:00:00.000' AS DateTime), N'Coat', 25)
GO
INSERT [dbo].[orders] ([id], [cust_id], [order_date], [order_details], [total_order_cost]) VALUES (25, 7, CAST(N'2019-04-19T00:00:00.000' AS DateTime), N'Coat', 125)
GO