Problem 3 (Parts a-b are 3 points each, c-fare 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. 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), FOREIGN KEY (customer_name) REFERENCES customer(customer_name), FOREIGN KEY (loan_number) references loan(loan_number) ) /* populate relations */ INSERT INTO customer VALUES ('ones 'Main' (Smith Main' "Hayes', Main', (Curry' North, ("Lindsay, Park (Turner Putnam ('Williams "Nassau ('Adams *Spring's (Johnson "Alma' ("Glenn Sand Hill, (Brooks, *Senator ('Green Walnut (Jackson University', (Majeris First McBride! Safety ('Brown South (Davis', Ember (Millen willow Wilson' Second (Moore Green (Taylor "Harrison'), Rye'), "Harrison'), *Rye), Pittsfield'). Stamford'), Princeton), Pittsfield'). Palo Alto), "Woodside'), 'Brooklyn'), Stamford), 'Salt Lake) Rye'>, Rye'). Rye'), *Stamford') Brooklyn forange ford') Princeton) Palo Alto Shady cover, 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 HIUMERIC (12,2) PRIMARY KEY (loan_number) NOT NULL) NOT NULL) NOT NULL, CREATE TABLE deposita Problem 3 (Parts a-b are 3 points each, c-fare 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. 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), FOREIGN KEY (customer_name) REFERENCES customer(customer_name), FOREIGN KEY (loan_number) references loan(loan_number) ) /* populate relations */ INSERT INTO customer VALUES ('ones 'Main' (Smith Main' "Hayes', Main', (Curry' North, ("Lindsay, Park (Turner Putnam ('Williams "Nassau ('Adams *Spring's (Johnson "Alma' ("Glenn Sand Hill, (Brooks, *Senator ('Green Walnut (Jackson University', (Majeris First McBride! Safety ('Brown South (Davis', Ember (Millen willow Wilson' Second (Moore Green (Taylor "Harrison'), Rye'), "Harrison'), *Rye), Pittsfield'). Stamford'), Princeton), Pittsfield'). Palo Alto), "Woodside'), 'Brooklyn'), Stamford), 'Salt Lake) Rye'>, Rye'). Rye'), *Stamford') Brooklyn forange ford') Princeton) Palo Alto Shady cover, 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 HIUMERIC (12,2) PRIMARY KEY (loan_number) NOT NULL) NOT NULL) NOT NULL, CREATE TABLE deposita