Expert answer:Structured Query Language
Solved by verified expert: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 on streets with names ending in “Hill“. 2. Print loan data, ordered by decreasing amounts, then increasing loan numbers. 3. Print the names and cities of customers who have a loan at Perryridge branch.NOTEI want to screen capture of the last tables on the word document.
steps_for_lab6.pdf_newwww.docx
make_banking_yki1._sql.txt
putty_winscp_tutorial__1____________.pdf
Unformatted Attachment Preview
Instructions for Lab 6
Step 1: Create tables and load data.
There are multiple ways to accomplish this task.
Option one:
a. Open make-banking.sql and change “use ;” to
use your own database. For example, “use yki1db;”. Save the file.
b. Transfer make-banking.sql to Pluto using a SFTP application if you haven’t
already.
c. Go to the directory in Pluto where you store make-banking.sql. At the
command prompt, type: mysql –h pluto–u yki –p ;” to
use your own database. For example, “use yki1db;”. Save the file.
b. Transfer make-banking.sql to Pluto using a SFTP application if you haven’t
already.
c. Go to the directory in Pluto where you store make-banking.sql. Log into
MariaDB by typing: mysql –h pluto.hood.edu –u jim –p, press enter. You
will be asked for your password.
d. Change to your database by issuing “use ;”
without quotes at the MariaDB prompt. Remember to change to your
database name.
e. At the MariaDB prompt, type: source make-banking.sql; Option three:
a. Go to the directory in Pluto where you store make-banking.sql. Log into
MariaDB by typing: mysql –h pluto.hood.edu –u jim –p, press enter. You
will be asked for your password.
b. Copy the SQL statements from make-banking.sql, create tables and load
data one by one.
Step 2: Check the data (this step is optional)
There are multiple ways to accomplish this task.
Option one:
a. Open banking-lab.sql and change “use ;” to use
your own database. Save the file.
b. Transfer banking-lab.sql to Pluto using a SFTP application if you haven’t
already.
c. Go to the directory in Pluto where you store banking-lab.sql. At the
command prompt, type: mysql –h pluto.hood.edu –u yki1 –p
out.txt, press enter.
d. Open out.txt using a text editor to see if you have the correct data.
Option two:
a. Open banking-lab.sql and change “use ;” to use
your own database. Save the file.
b. Transfer banking-lab.sql to Pluto using a SFTP application if you haven’t
already.
c. Go to the directory in Pluto where you store banking-lab.sql. Log into
MariaDB by typing: mysql –h pluto.hood.edu –u yki1 –p, press enter. You
will be asked for your password.
d. At the MariaDB prompt, type: source banking-lab.sql; press enter, you
should see all the results displayed on the screen.
Step 3: Construct and execute queries.
There are multiple ways to accomplish this task.
Option one:
a. Log into MariaDB by typing: mysql –h pluto.hood.edu –u yki1 –p, press
enter. You will be asked for your password.
b. At the command prompt, type the sql statement for each query and get
the result one by one. You will need to catch the result by using copy and
paste.
Option two:
a. Construct SQL statements for all queries and put them in one sql file, for
example, lab6.sql.
b. Add “use ;” at the beginning of lab6.sql.
Remember to use your own database.
c. Go to the directory in Pluto where you store lab6.sql. At the command
prompt, type: mysql –h pluto–u yki1 –p lab6out.txt, press
enter. You should have the result in lab6out.txt.
Note:
1. There are some formatting statements in banking-lab.sql. You may refer to
them to make your results more readable. Those statements are:
select ‘- – – – – – – – – – – – – – – – – – – – – – – – – – – ‘ from dual; select ‘Query 1: All customer
data.’ as ”;
2. If you work remotely, you can log into MariaDB without specifying –h pluto
after you connect to the Pluto server.
/* clean up old tables;
must drop tables with foreign keys first
due to referential integrity constraints
*/
use yki1db;
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’);
IT 530 Applied Database Systems
Dr. Jim
PuTTY Tutorial
1. Download the SSH client, PuTTY, from:
https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html
Get the latest release version (0.70) for Windows. Download the putty.exe file (under
“Alternative binary files”) that is appropriate for your version of the Windows operating
system (32-bit or 64-bit).
2. Open PuTTY and type in the host name: pluto.hood.edu. Click Open.
Page 1 of 11
IT 530 Applied Database Systems
Dr. Jim
If this is the first time logging into Pluto using PuTTY, you may see the following screen.
Click Yes to add the host key to PuTTY’s cache and trust the host.
3. Log in to Pluto using your account username and password (both case sensitive). Note
that nothing will show on the terminal screen when typing in your password.
Page 2 of 11
IT 530 Applied Database Systems
Dr. Jim
4. To log in and access MariaDB on the Pluto server, type in at the prompt:
mysql –u user –p
Note: Fill in user with your account username. Next, press Enter, and enter your
password. You will now see a prompt change showing that you have successfully
connected to MariaDB.
At this prompt, type in:
use databaseName;
Note: Fill in databaseName with your database name, and remember to end the
command with a semicolon. Then, press Enter. From here, you can begin typing in
commands at the prompt to show tables, create tables, insert data into tables, query tables
(select-from-where statements), etc.
5. To copy and paste your queries/results to a text document from the terminal window
(PuTTY) to your Windows machine, highlight the text that you want copied. This will
automatically copy that text.
Page 3 of 11
IT 530 Applied Database Systems
Dr. Jim
To paste the data you highlighted, open a text editor (e.g., Notepad, Wordpad, Microsoft
Word, etc.), right-click, and click Paste.
Your highlighted text will appear in the text editor.
Page 4 of 11
IT 530 Applied Database Systems
Dr. Jim
Save your file.
6. To exit from MariaDB, type in quit at the prompt. To exit from Pluto, type in exit at
the prompt.
Page 5 of 11
IT 530 Applied Database Systems
Dr. Jim
WinSCP Tutorial
1. Download the secure file transfer client, WinSCP, from:
https://winscp.net/eng/download.php
You can either choose to get the Installation Package if you want to install WinSCP, or
you can get the Portable Executables if you do not want to perform any installation.
2. Open WinSCP. Type in the host name: pluto.hood.edu. Type in your account username
and your password in the corresponding fields. Click Login.
Page 6 of 11
IT 530 Applied Database Systems
Dr. Jim
If this is the first time logging into Pluto using WinSCP, you may see the following
screen. Click Yes to add the host key to the cache and trust the host.
3. The left panel displays your files and directories on your local Windows machine while
the right panel displays your files and directories on the remote Pluto server.
Refresh
Refresh
Windows side
Pluto (or Linux) side
Page 7 of 11
IT 530 Applied Database Systems
Dr. Jim
Using WinSCP, you can safely transfer your files from Windows to Pluto and vice versa
from Pluto to Windows simply by clicking and dragging the file over to the side you wish
to transfer your file.
You can also create and delete directories and files on both the Windows and Pluto sides.
If you made changes to some file or directory, then make sure to refresh the side you
made changes on.
Page 8 of 11
IT 530 Applied Database Systems
Dr. Jim
Common Linux Commands
The following are some common Linux commands for use at the command prompt after logging
in to Pluto using PuTTY:
Command
ls
mkdir
cd
cd ..
Description
List files and directories
Make directory; Note: replace with a name of your choice
Change directory; Note: replace with some directory
Go up one level
FOR MAC/LINUX USERS
1. Open up Terminal by going to Applications Utilities Terminal
2. To log in to Pluto, type in at the prompt:
ssh username@pluto.hood.edu
Note: Fill in username with your account username. When prompted for your password,
type in your password and press Enter. If entered successfully, you should be logged in
to Pluto. Notice the prompt change.
3. To copy files from your local machine to the Pluto server, you need to use the scp
command unless you have a program for file transfer installed (e.g., Fugu, Fetch, etc.). It
may be more convenient to open up a separate Terminal (different than the one you are
using to log in to Pluto) just to do file transfer.
To transfer a file from your local machine to the remote Pluto server, type in at the
prompt:
scp /path/to/file username@pluto.hood.edu:/path/to/destination
To transfer a file from the remote Pluto server to your local machine, type in at the
prompt:
scp username@pluto.hood.edu:/path/to/file /path/to/destination
Note: Fill in username with your account username. Fill in the paths with the respective
paths to the file locations.
Page 9 of 11
IT 530 Applied Database Systems
Dr. Jim
4. There are text editors provided on Pluto if you choose to type or copy/paste text directly
onto Pluto rather than using scp to transfer files. A simple text editor available is called
pico and can be accessed when logged into Pluto by typing at the prompt:
pico
An example is shown below:
This is what the text editor (pico) looks like:
Page 10 of 11
IT 530 Applied Database Systems
Dr. Jim
To move the cursor, use the arrow keys on your keyboard. To save your file, press
Ctrl-O, then Enter to save the file to the filename listed.
To exit the editor, press Ctrl-X.
Page 11 of 11
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades