Create SQL VIEW statements: please could you help me with the other three I got
ID: 3716630 • Letter: C
Question
Create SQL VIEW statements:
please could you help me with the other three I got problems with them
1. Use your EY table. This VIEW statement should display all transactions from the 2013 fiscal year for anyone with the cardholder last name of Schanz. Besides the last name, display the fiscal year, amount, and what the purchase was/description.
CREATE VIEW ey
AS SELECT last_name, amount, fiscal_year, PURCHASE_DESCRIPTION
FROM EY18
WHERE Fiscal_year = 2013 and last_name = 'Schanz';
2. Now modify this VIEW statement to change the names of the columns to: Customer Last Name, EY Fiscal Year, amount, and Purchase Description
3. Create an SQL VIEW statement that displays each department id with the sum of the salary of each of the departments. With the output, the sum of the salaries’ column header/name should be displayed as: Total Sales by Dept Use the EMPLOYEES table.
4. Modify the View Statement in # 3 to change the column headers to: Department Identification Number and Sum of the Salaries,
Explanation / Answer
1.
CREATE VIEW ey
AS SELECT last_name, amount, fiscal_year, PURCHASE_DESCRIPTION
FROM EY18
WHERE Fiscal_year = 2013 and last_name = 'Schanz';
=========================================================================================
2.
CREATE VIEW ey
AS SELECT last_name as "Customer Last Name", amount, fiscal_year as "EY Fiscal Year", PURCHASE_DESCRIPTION as "Purchase Description"
FROM EY18
WHERE Fiscal_year = 2013 and last_name = 'Schanz';
=========================================================================================
3.
CREATE VIEW emp_view
AS SELECT departmend_id, sum(salary) as "Total Sales By Dept"
FROM EMPLOYEES
GROUP BY department_id;
=========================================================================================
4.
CREATE VIEW emp_view
AS SELECT departmend_id as "Department Identification Number", sum(salary) as "Sum of the salaries"
FROM EMPLOYEES
GROUP BY department_id;