Related questions
USE this part to write SQL statements
- Branch(branch_id:integer, branch_name:varchar(50), branch_location:varchar(40), money_on_hand:numeric(15,2)
create table Branch
(branch_id integer,
branch_name varchar(50),
branch_location varchar(40),
money_on_hand numeric(15,2),
primary key (branch_id));
- Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2))
foreign key branch_id references Branch(branch_id)
create table Loan
(loan_number integer,
branch_id integer,
amount numeric(8,2),
primary key (loan_number),
foreign key (branch_id) references Branch (branch_id));
- Customer(customer_id:integer, customer_last_name:varchar(35),customer_first_name:varchar(25), customer_street:varchar(30), customer_zip:integer)
create table Customer
(customer_id integer,
customer_last_name varchar(35),
customer_first_name varchar(25),
customer_street varchar(30),
customer_zip integer,
primary key (customer_id));
- Borrower(customer_id:integer, loan_number:integer)
foreign key (customer_id) references Customer(customer_id)
foreign key (loan_number) references Loan(loan_number)
create table Borrower
(customer_id integer,
loan_number integer,
primary key (customer_id, loan_number),
foreign key (customer_id) references Customer (customer_id),
foreign key (loan_number) references Loan (loan_number));
- Depositor(customer_id:integer, account_number:integer)
foreign key (customer_id) references Customer(customer_id)
foreign key (account_number) references Account(account_number)
Note: as the Account table is a referenced table, you have to create that table first.
create table Depositor
(customer_id integer,
account_number integer,
primary key (customer_id, account_number),
foreign key (customer_id) references Customer (customer_id),
foreign key (account_number) references Account (account_number));
- Account(account_number:integer, branch_id:integer, balance:numeric(8,2))
foreign key branch_id references Branch(branch_id)
create table Account
(account_number integer,
branch_id integer,
balance numeric(8,2),
primary key (account_number),
foreign key (branch_id) references Branch (branch_id));
Write SQL statements to answer the following questions using Assignment 3’s schema (tables from part 1). You can add more data to the tables if you want, just follow the PK and FK rules. Accounts are not Loans and Loans are not Accounts.
1- Find how many branches have loans between 4,100ドル.00 and 7,000ドル.00. HINT: Do not manually count the rows, have the DBMS engine do the work.
2- For each branch, find the Min and Max loan amounts. Your output should include Branch Id, min loan amount and max loan amount for that Branch.
3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer.
4- Find the average account balance for each Branch. The output should be a list of Branch Id and for each Branch Id, the average account balance in that Branch.
5- Find Customer ID, Customer name and Customer City for all accounts, sorted by Customer City, then Customer Last name.
6- Find Customer ID, Customer name and the number of loans for each Customer.
7- Find Loan number and Customer Id of the loan with the lowest amount.
8- Create a view called Gary_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Gary.
9- For each Customer in Hopkins, find the balance in their account(s).
10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID.
11- Find the branch with the highest or largest Average loan amount. List the Branch ID, Branch Name, and the Highest Average loan amount.
Trending nowThis is a popular solution!
Step by stepSolved in 2 steps with 3 images
- Note: I am asking you to create a table in a database. If you already have a database, that's fine, so create the table within it. Otherwise create a database for your table. (sql server has a script to create a database) Create a SQL table using your name with the following features: the columns of your table must include, at least the data types (in this order) and one more of your choice NOTE: You need to specify a 2 column (i.e 2 attribute 1. varchar (n), // where n covers the string length you want to enter 2. Int, 3. decimal, (precision = 8, scale = 3 4. date. 5. ??? your choice here ??? Table constraints: 1. It has a two column primary key 2. a check constraint on 2 columns, on the decimal and the date field 3. Use '2024-02-18' date as the default on the date field 5. write down your relational schema 5. Create the table, insert at least 4 rows, and do a Select * to show them example don't just copy these, change the constraint namesarrow_forwardThe only required sections of a PL/SQL block are DECLARE and END. Answers: Yes Noarrow_forwardPL/SQL Question I need to build pl/sql block that prompts a user for the number of visitors each day for the past 5 days and then displays the average number of visitors per day. For example; day 1: 19 day 2: 21 day 3: 23 ... The avg number of visitors is: ___ like this.arrow_forward
- What is mean by cursor in sqlarrow_forward423532.1801938.qx3zqy7 Jump to level 1 Complete the SQL statement to generate the Result table. Order Part Number Quantity 492 492 662 Check OrderCode Z23 R58 A36 1 Next 3 4 1 Order.PartNumber 662 SELECT */Type your code here */ FROM Order, Part WHERE */Type your code here */ Result 2 ●くろまるPartNumber 827 662 PartName Part Wingding PartName Price Left wizard 1000 Wingding 11 ; 3arrow_forwardSQL Query questionarrow_forward
- I need help with creating the SQL statement for the instructions below. Create the following 2 tables with columns and constraints Table Lab4program_xxxx has 3 columns: code is varchar(5) and the primar key, name is varchar(20), tel varchar(20). Table Lab4course_xxxx has 3 columns: cid is int, and the primary key, name is varchar(20), credits is int, mycode is a foreign key reference to your Lab4program table. Table Lab4program_xxxx has 3 columns: code is varchar(5) and the primar key, name is varchar(20), tel varchar(20). Table Lab4course_xxxx has 3 columns: cid is int, and the primary key, name is varchar(20), credits is int, mycode is a foreign key reference to your Lab4program table.arrow_forwardWrite the appropriate SQL DDL statements to declare the relational libraryDatabase. Diagram of Figure 4.6. Select the keys and reference actions that are triggered.arrow_forwardYou have the following tables: MANAGERS (MANAGER_ID, LAST_NAME, FIRST_NAME, DEPARTMENT) ACCOUNTS (ACCOUNT_NUMBER, MANAGER_ID, CUST_ID, BALANCE) CUSTOMERS (CUST_ID, LAST_NAME, FIRST_NAME) Write a SQL statement that lists account number, balance, MANAGER’s last name, CUSTOMER ID, and CUSTOMER’s last name for every account in the ACCOUNTS table.arrow_forward
- Branch Table BranchNo Name B001 Location 567, Kileleshwa 890, Karen 894, Rongai Kileleshwa BO02 Karen ВООЗ Rongai i). Write appropriate SQL statement to display all staff who work at BranchNo "BO02"arrow_forwardIntersection table is needed when you have a relationship 1:1 M:N M:1 1:Marrow_forwardCan we use DDL and DML statements in Function SQL?arrow_forward
- Text book imageDatabase System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationText book imageStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONText book imageDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- Text book imageC How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONText book imageDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningText book imageProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education