For the below 'bank' schema: customer(customerid, username,fname, Ina me,street1,street2,city, state,zip) account(accountid,customerid,description,) transaction(transactionid,trantimestamp,acc ountid,amount) A customer may have several accounts, and each account may participate in many transactions. Each transaction will have at least two records, one deducting amount from an account, and one adding amount to an account (for a single transactionid, the sum of amounts will equal zero). Using SQL, answer this question (write a SQL query that answers this question): 8. List the top 1% of customers (ordered by total balance).

For the below 'bank' schema: customer(customerid, username,fname, Ina me,street1,street2,city, state,zip) account(accountid,customerid,description,) transaction(transactionid,trantimestamp,acc ountid,amount) A customer may have several accounts, and each account may participate in many transactions. Each transaction will have at least two records, one deducting amount from an account, and one adding amount to an account (for a single transactionid, the sum of amounts will equal zero). Using SQL, answer this question (write a SQL query that answers this question): 8. List the top 1% of customers (ordered by total balance).

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Design your application's/organization's database. You need to create the 3 tables for each of the services offered. You must provide at least 5 examples in each of the table. There must be a table in which you have prices linked to the services you are providing. THIS IS THE NAME & DESCRIPTION OF THE ORGANIZATION FOR WHICH YOU NEED TO CREATE 3 TABLES FOR EACH SERVICE PROVIDED *Name of the Organization* :: ECOHUB *Description of the Organization* :: EcoHub is a sustainability-focused organisation that seeks to encourage environmentally friendly behaviours, increase public knowledge of environmental problems, and offer solutions for people, organisations, and communities to reduce their carbon footprint and help make the world a greener place. We think that many little individual actions can have a big influence on the environment when taken together. *Services Offered by the Organization are*1. EcoConsultancy : We provide individualised eco-consultancy services to people and…
Given the Bank database that contains the following tables (primary keys are underlined and foreign keys are preceded with #):Agency(agencyNumber, agencyName, city, assets)Client (clientNumber, firstName, lastName, clientCity)Account(accountNumber, #agencyNumber, #clientNumber, balance)Loan(loanNumber, #agencyNumber, #clientNumber, amount ) c) Find the average balance of customer accounts, for each agency whose average balance is greater than 10,000. The table result should have the columns agencyNumber and Balance_Average
The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined) CUSTOMER (CustID, FirstName, LastName, City, Phone, Email) INVOICE (InvoiceNumber, CustID, Date) INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity) ITEM (ItemNumber, ItemName, UnitPrice) When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead. Provide relational algebra (NOT SQL) queries to find the following information. NOTE: You can use the symbols s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer. You do not need to try to make efficient queries – just correct ones. Where you use a join, always show the join condition. List the dates on which Homer Griffin made purchases. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover” List the first…
Using the database schema attached, write SQL query for these statements: (1) List stores with more than 200 customers. (2) Display the average amount paid by each customer, along with the customer's first and last name.
name PERSON address salary fee EMPLOYEE STUDENT thesis RESEARCH TEACHING POST GRAD UNDER GRAD Year = 4 FINAL YEAR LECTURER TUTOR SUPERVISOR project courseno PART II: University Database in MYSQL • Create a database and its tables and constraints based on the relational model of PART I. • You can use my slides and any other resources to complete this exercise. • Submit the SQL statements you used to create the database and its tables (you can copy/paste directly from the mysql console).
Consider the following schema of hotel reservations database: Hotel (Hotel No, Name, City) Room (Room No, Hotel No, Type, Price) Booking (Hotel No, Guest No, Date From, Date_To, Room No) Guest (Guest_No, Name, City) Write RA queries for the following: a) Get the details (names and price) of all hotels in California. b) Get the average price of a room grouped by city. c) Find the hotel name and city of the hotel with the highest priced room. d) Get the hotel names and room numbers of any hotel rooms that have not been booked.
The database should contain the following six tables: Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime) Write a single SQL query for each request below. Find the ids of those users who have listened to at least one track but have not followed any artists. Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'. Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.
SportTech Events. SportTech Events SportTech Events puts on athletic events for local high school athletes. The company needs a database designed to keep track of the sponsor for the event and where the event is located. Each event needs a description, date, and cost. Separate costs are negotiated for each event. The company would also like to have a list of potential sponsors that includes each sponsor’s contact information, such as the name, phone number, and address. Each event will have a single sponsor, but a particular sponsor may sponsor more than one event. Each location will need an ID, a contact person, and a phone number. A particular event will use only one location, but a location may be used for multiple events. SportTech asks you to create a Conceptual Entity Relationship Diagram from the information described above.
The following database is maintained by a bookstore: the database structure is shown below. AUTHOR(AuthorName) BOOK(ISBN, AuthorName, Title, PublisherName, PublicationYear, RetailPrice)AuthorName foreign key to AUTHORPublisherName foreign key to PUBLISHER PUBLISHER(PublisherName) CUSTOMER(CustomerNumber, Name, Address, Phone, Email, CreditCardNumber, ExpiryDate) ORDER(OrderNumber, CustomerNumber, OrderDate)CustomerNumber foreign key to CUSTOMER ORDERDETAIL(OrderNumber, ISBN, QuantitySold, RetailPrice, TotalCost)OrderNumber foreign key to ORDER Write SQL code to implement the following separate queries numbered 1 to 4: 1. Find all orders placed in 2018-2019. 2. Report the following information: ISBN, AuthorName, Title, RetailPrice for all books written byAlexandre Dumas or by Mark Twain, published by ClassicsPublishingCo, with a retail price at least$24.99. 3. Create a table which shows the value of orders by each customer who actually placed an orderin 2019. Group in order by customer…
Page - 1 DBMS - TASK 1: Draw an ERD and Map it to relational schema. Please complete reading page 1 before going to page 2. In this task, you will be given a text that describes a company (in page 2). Each student should complete the steps below to fully solve the task Represent the company description as an ER-diagram, including all relevant constraints as the following steps: - Each student should download Lucidchart app Every student should use Lucidchart to draw the ERD Edit Lucidchart Blank diagram Select View Insert Arrange Help 80 What's New Saved Start a free trial to access unlimited shapes and documents! X BIUAT- 2 px None None 408 Liberation Sans ▾ 10 pt + Shapes O Standard × T Flowchart 000 ☐ ODOT ☐ ☐ ☐ ☐ ☐☐ )>0 × TU Xx 14 << ΔΟ ▼ Shapes ΠΑ ☐ ☐ Name B Present MA when you create After each entity ID print screen, your name include your user ID should be shown university ID number Department 0190334 Dep_ID Employee 0190334 Name
Following tables support the database of a shopping store. The schema contains four tables i.e.Customer, Product, Order and Order Product. Table details including attributes, primary keys(underline) and foreign keys (bold) are also shown below:CUSTOMER(CustID, CustName, CustEmail, CustPhone, CustCNIC)PRODUCT(ProdID, ProdName, ProdPrice)ORDER(OrderID, OrderDate, OrderPrice, CustID)ORDER_PRODUCT(OPID, ProdID, OrderID)