database Sql

Anonymous
timer Asked: Oct 22nd, 2018
account_balance_wallet $10

Question Description

Query 1: Find the names of customers who live in cities with names ending in "on".

customer_name customer_city

Hayes Harrison

Jones Harrison

Williams Princeton

Query 2: Find the names and cities of customers who have account(s) at "Downtown" branch.

customer_name customer_city

Hayes Harrison

Johnson Palo Alto

Query 3: Print in descending order the names of customer who have a loan amount between 1000 and 2000.

customer_name amount

Williams 1000.00

Smith 2000.00

Jones 1000.00

Jackson 1500.00

Hayes 1500.00

Adams 1300.00

Query 4: Find all customers who have a loan, an account, or both.

customer_name

Hayes

Johnson

Jones

Lindsay

Majeris

Smith

Turner

Adams

Curry

Jackson

McBride

Williams

Unformatted Attachment Preview

/* 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 loan; DROP TABLE IF EXISTS branch; DROP TABLE IF EXISTS customer; create table branch (branch_name varchar(15) not null, branch_city varchar(15) not null, assets FLOAT(10,2) not null, primary key(branch_name))ENGINE=InnoDB; create table customer (customer_name varchar(15) not null, customer_street varchar(12) not null, customer_city varchar(15) not null, primary key(customer_name))ENGINE=InnoDB; create table loan (loan_number varchar(15) not null, branch_name varchar(15) not null, amount FLOAT(10,2) not null, primary key(loan_number), foreign key(branch_name) references branch(branch_name))ENGINE=InnoDB; create table account (account_number varchar(15) not null, branch_name varchar(15) not null, balance FLOAT(10,2) not null, primary key(account_number), foreign key(branch_name) references branch(branch_name))ENGINE=InnoDB; 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))ENGINE=InnoDB; 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))ENGINE=InnoDB; /* populate relations */ insert into customer values ('Jones', 'Main', 'Harrison'); insert into customer values ('Smith', 'Main', 'Rye'); insert into customer values ('Hayes', 'Main', 'Harrison'); insert into customer values ('Curry', 'North', 'Rye'); insert into customer values ('Lindsay', 'Park', 'Pittsfield'); insert into customer values ('Turner', 'Putnam', 'Stamford'); insert into customer values ('Williams', 'Nassau', 'Princeton'); insert into customer values ('Adams', 'Spring', 'Pittsfield'); insert into customer values ('Johnson', 'Alma', 'Palo Alto'); insert into customer values ('Glenn', 'Sand Hill', 'Woodside'); insert into customer values ('Brooks', 'Senator', 'Brooklyn'); insert into customer values ('Green', 'Walnut', 'Stamford'); insert into customer values ('Jackson', 'University', 'Salt Lake'); insert into customer values ('Majeris', 'First', 'Rye'); insert into customer values ('McBride', 'Safety', 'Rye'); insert into branch values ('Downtown', 'Brooklyn', 900000); insert into branch values ('Redwood', 'Palo Alto', 2100000); insert into branch values ('Perryridge', 'Horseneck', 1700000); insert into branch values ('Mianus', 'Horseneck', 400200); insert into branch values ('Round Hill', 'Horseneck', 8000000); insert into branch values ('Pownal', 'Bennington', 400000); insert into branch values ('North Town', 'Rye', 3700000); insert into branch values ('Brighton', 'Brooklyn', 7000000); insert into branch values ('Central', 'Rye', 400280); insert into account values ('A-101', 'Downtown', 500); insert into account values ('A-215', 'Mianus', 700); insert into account values ('A-102', 'Perryridge', 400); insert into account values ('A-305', 'Round Hill', 350); insert into account values ('A-201', 'Perryridge', 900); insert into account values ('A-222', 'Redwood', 700); insert into account values ('A-217', 'Brighton', 750); insert into account values ('A-333', 'Central', 850); insert into account values ('A-444', 'North Town', 625); insert into depositor values ('Johnson','A-101'); insert into depositor values ('Smith', 'A-215'); insert into depositor values ('Hayes', 'A-102'); insert into depositor values ('Hayes', 'A-101'); insert into depositor values ('Turner', 'A-305'); insert into depositor values ('Johnson','A-201'); insert into depositor values ('Jones', 'A-217'); insert into depositor values ('Lindsay','A-222'); insert into depositor values ('Majeris','A-333'); insert into depositor values ('Smith', 'A-444'); insert into loan values ('L-17', 'Downtown', 1000); insert into loan values ('L-23', 'Redwood', 2000); insert into loan values ('L-15', 'Perryridge', 1500); insert into loan values ('L-14', 'Downtown', 1500); insert into loan values ('L-93', 'Mianus', 500); insert into loan values ('L-11', 'Round Hill', 900); insert into loan values ('L-16', 'Perryridge', 1300); insert into loan values ('L-20', 'North Town', 7500); insert into loan values ('L-21', 'Central', 570); insert into borrower values ('Jones', 'L-17'); insert into borrower values ('Smith', 'L-23'); insert into borrower values ('Hayes', 'L-15'); insert into borrower values ('Jackson', 'L-14'); insert into borrower values ('Curry', 'L-93'); insert into borrower values ('Smith', 'L-11'); insert into borrower values ('Williams','L-17'); insert into borrower values ('Adams', 'L-16'); insert into borrower values ('McBride', 'L-20'); insert into borrower values ('Smith', 'L-21'); SQL Lab Practice 1 Consider the banking example we used in lecture: branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower (customer_name, loan_number) Write and execute the following queries in SQL: 1) Find the names of customers who live in cities with names ending in “on" 2) Find the names and cities of customers who have account(s) at “Downtown” branch. 3) Print in descending order the names of customer who have a loan amount between 1000 and 2000, i.e. >= 1000 and <= 2000. 4) Find all customers who have a loan, an account, or both Steps: 1. Create the tables using the file make_banking_db.sql. Tables can be created individually or in batch-mode. 2. Create SQL code for the queries above. Submission: Due: before next class Please submit both your sql statements and output, either in one file, or separately in two. ...
Purchase answer to see full attachment

Tutor Answer

onesmasd
School: UC Berkeley

Hey, I am through. Please check out the attached files

Tables

/*Query 1: Find the names of customers who live in cities with names ending in "on". */

select customer_name, customer_city from customer where customer_city like '%on';

/*Query 2: Find the names and cities of cust...

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors