Problem 2 (Part a is 2 points, parts b-c are 3 points each; total is 8 points) Write SQL statements that define the following views. Make sure that all columns in each view's results have meaningful names and are in the order specified. a) A view called pownal_customers containing the account numbers and customer names (but not the balances) for all accounts at the Pownal branch. b) A view called onlyacct_customers containing the name, street, and city of all customers who have an account with the bank, but do not have a loan. This view can be defined such that it is updatable; for full points you must make sure that it is. c) A view called branch_deposits that lists all branches in the bank, along with the total account balance of each branch, and the average account balance of each branch. Make sure all computed values are given reasonable names in the view. Make sure that branches with no accounts are included in the result! (The Markham branch has no accounts.) Such branches should have a total balance of 0, and an average of NULL. (You may find the MySQL IFNULL() function useful. See the MySQL 8.0 documentation, $12.5 "Control Flow Functions," for details.) Problem 3 (Parts a-b are 3 points each, c-f are 4 points each; 22 points total) Here are some more challenging problems to try against the banking database. a) Generate a list of all cities that customers live in, where there is no bank branch in that city, Make sure that the results are distinct; no city should appear twice. Also, sort the cities in increasing alphabetical order. b) Are there any customers who have neither an account nor a loan? Write a SQL query that reports the name of any customers that have neither an account nor a loan. Note that MySQL does not support the EXCEPT operator! But there is more than one way... c) The bank decides to promote its branches located in the city of Horseneck, so it wants to make a $50 gift-deposit into all accounts held at branches in the city of Horseneck. Write the SQL UPDATE command for performing this operation. University Databa takes ID course_id sec_id semester year grade student ID name dept_name tot cred section course course.id sec id semester coursed title dept name credits advisor Sid Lid time slot year building room.no time_slotid time_slot.id day start time end time department dept.name building budget classroom building room.no capacity prere course_id prerequid instructor teaches ID course. Id sec.id semester year ID name dept name salary Figure 2.8 Schema diagram for the university database. classroom (building room number capacity) department (dept name building, budget) course (course id title, dept_name, credits) instructor (ID name, dept_name, salary) section (course id sec id semester year building, room_number, time_slot_id) teaches (ID course id sec id semester year) student (ID name, dept_name, tot_cred) takes (ID course id. sec id semester year grade) advisor (s ID LID) time_slot (time slot id day start time end_time) prereq (course id prereq id) Figure 2.9 Schema of the university database. create table department (dep_name varchar (20) building varchar (15) budget numeric (12,2). primary key (dept_name)); create table course (course_id varchar(7), title varchar(50). dept name varchar (20) credits numeric (2,0). primary key (course_id). foreign key (dept_name) references department); create table instructor (ID varchar (5) name varchar (20) not null dept_name varchar (20) salary numeric (8,2). primary key (ID). foreign key (dept_name) references department): create table section (course_id varchar (8) sec_id varchar (8) semester varchar (6) year numeric (4,0). building varchar (15) room_number varchar(7). time_slot_id varchar(4). primary key (course_id, sec_id, semester, year). foreign key (course_id) references course): create table teaches (ID varchar (5) course_id varchar(8). sec_id varchar (8) semester varchar (6) year numeric (4,0). primary key (ID, course_id, sec_id, semester, year). foreign key (course_id, sec_id, semester, year) references section, foreign key (ID) references instructor); Figure 3.1 SQL data definition for part of the university database. Danking - Notepad File Edit Format View Help V* clean up old tables; must drop tables with foreign keys first due to referential integrity constraints */ DROP TABLE IF EXISTS depositor; DROP TABLE IF EXISTS borrower; DROP TABLE IF EXISTS account; DROP TABLE IF EXISTS branch; DROP TABLE IF EXISTS loan; DROP TABLE IF EXISTS customer; CREATE TABLE account ( account_number VARCHAR(15) branch_name VARCHAR(15) balance NUMERIC(12,2) PRIMARY KEY (account_number) ); NOT NULL, NOT NULL, NOT NULL, CREATE TABLE branch branch_name VARCHAR(15) branch_city VARCHAR(15) assets NUMERIC(14,2) PRIMARY KEY (branch_name) NOT NULL) NOT NULL, NOT NULL, CREATE TABLE customer customer name VARCHAR(15) customer_street VARCHAR(12) customer_city VARCHAR(15) PRIMARY KEY (customer_name) NOT NULL, NOT NULL NOT NULL CREATE TABLE loan loan_number VARCHAR(15) branch name VARCHAR (15) amount NUMERIC (12,2) PRIMARY KEY (loan_number) NOT NULL, NOT NULL) NOT NULL CREATE TABLE depositor make-banking - Notepad File Edit Format View Help ); CREATE TABLE depositor ( customer_name VARCHAR(15) NOT NULL, account number VARCHAR(15) NOT NULL) PRIMARY KEY (customer_name, account_number), FOREIGN KEY (account_number) REFERENCES account (account_number), FOREIGN KEY (customer_name) REFERENCES customer(customer_name) CREATE TABLE borrower ( customer name VARCHAR(15) NOT NULL, loan_number VARCHAR(15) NOT NULL PRIMARY KEY (customer_name, loan_number), FORETGN KEY (customer_name) REFERENCES customer(customer_name), FOREIGN KEY (loan_number) references loan(loan_number) D /* populate relations */ * Park INSERT INTO customer VALUES (Iones 'Main Smith Main', (Hayes", Main', ('curry', North ('Lindsay', ('Turner 'Putnam' ('Williams, "Nassau, ('Adams Spring', (Johnson, "Alma ('Glenn Sand Hill's (Brooks, "Senator ('Green Walnut (Jackson University', Majeris! 'First C'McBride' 'Safety ('Brown's South (Davis Ember (Miller willow wilson Second (Moore Green' (Taylor Shady cover, Harrison'), Rye'). "Harrison'), *Rye'), Pittsfield'), Stamford'), Princeton) *Pittsfield), Palo Alto'), 'Woodside), Brooklyn, Stamford), 'Salt Lake'), Rye'>, Rye), Rye'). stanford') Brooklyn orange ford') Princeton) Palo Alto