Database:LemonadeStand Table: EmployeeSales ID FristName LastName Year Q1Sales Q
ID: 3667304 • Letter: D
Question
Database:LemonadeStand Table: EmployeeSales
ID
FristName
LastName
Year
Q1Sales
Q2Sales
Q3Sales
Q4Sales
1
Christina
Yonkers
2011
268.39
310.39
410.11
289.92
1
Christina
Yonkers
2012
292.03
305.25
499.84
416.22
2
Bill
Chisholm
2012
199.44
215.55
312.50
278.88
3
Belinda
Johnson
2011
215.25
188.14
266.34
244.20
4
Greg
Hathaway
2010
188.15
205.11
383.75
301.01
4
Greg
Hathaway
2011
245.96
360.33
588.02
489.00
4
Greg
Hathaway
2012
307.68
362.66
601.23
501.17
1.Create the database and the table structure above, including the composite primary key (see the underlined column name). (4 pts)
2.Write all of the insert statements to put data into this table. (2 pts)
3.Write a query to display the full name, the year, and each of the quarters. The full name should be a combination of first and last names and formatted like this “Yonkers, Christina”. The column names for the quarterly sales columns should be formatted like this: “Q1 Sales”. (2 pts)
4.Modify the query in step 3 so that there is an additional column created called “Yearly Sales”. The values in this column should be the sum of the individual quarters. (2 pts)
Put all of your work in ONE SQL file called homework4.sql. Remember to put in the keyword GO between commands. make sure you TEST it thoroughly. No excuses
ID
FristName
LastName
Year
Q1Sales
Q2Sales
Q3Sales
Q4Sales
1
Christina
Yonkers
2011
268.39
310.39
410.11
289.92
1
Christina
Yonkers
2012
292.03
305.25
499.84
416.22
2
Bill
Chisholm
2012
199.44
215.55
312.50
278.88
3
Belinda
Johnson
2011
215.25
188.14
266.34
244.20
4
Greg
Hathaway
2010
188.15
205.11
383.75
301.01
4
Greg
Hathaway
2011
245.96
360.33
588.02
489.00
4
Greg
Hathaway
2012
307.68
362.66
601.23
501.17
Explanation / Answer
CREATE DATABASE LemonadeStand
GO
USE LemonadeStand
GO
CREATE TABLE EmployeeSales
(
ID INT,
FristName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
Year VARCHAR(6) NOT NULL,
Q1Sales NUMERIC(5,2),
Q2Sales NUMERIC(5,2),
Q3Sales NUMERIC(5,2),
Q4Sales NUMERIC(5,2)
);
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (4, 'Greg', 'Hathaway','2012', 307.68, 362.66, 601.23, 501.17);
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (4, 'Greg', 'Hathaway', '2011', 245.96, 360.33, 588.02,489.00 );
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (4,'Greg', 'Hathaway','2010',188.15, 205.11, 383.75,301.01 );
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (3,'Belinda','johnson','2011',215.25, 188.14, 266.34, 244.20);
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (2,'Bill','Chisholm','2012', 199.44, 215.55,312.50 ,278.88 );
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (1,'Christina', 'Yonkers','2012',292.03, 305.25, 499.84, 416.22);
INSERT INTO EmployeeSales (ID, FristName, LastName,Year,Q1Sales,Q2Sales,Q3Sales,Q4Sales)
VALUES (1,'Christina', 'Yonkers','2011',268.39, 310.39,410.11 ,289.92);
select FristName+', '+LastName, Year, Q1Sales as 'Q1 Sales', Q2Sales as 'Q2 Sales',
Q3Sales as 'Q3 Sales',Q4Sales as 'Q4 sales';
select FristName+', '+LastName, Year, Q1Sales as 'Q1 Sales', Q2Sales as 'Q2 Sales',
Q3Sales as 'Q3 Sales',Q4Sales as 'Q4 sales', (Q1Sales+Q2Sales+Q3Sales+Q4Sales) as 'Yearly Sales';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.