The following database will be used in this question:SALESREPSalesRepNo654734345434RepNameJonesSmithChenJohnsonHireDate01/02/200502/03/200701/25/200411/23/2004CUSTOMERCustNo9870859078404870CustNameWinstonGonzalesHarrisMilesBalance500350SalesRepNo345434654345800100 a) Create the two tables above and insert the data in the rows accordingly.b) Write the standard MYSQL query to retrieve all information about the customer whosecustomer number is 8590

Hi Student. Warm welcome from my side. Hope you are doing great. Here is your answer.

Answered: a) Create the two tables above and…

a) Create the two tables above and insert the data in the rows accordingly. b) Write the standard MYSQL query to retrieve all information about the customer whose customer number is 8590

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

Related questions

Q: List unique first (first_name) and last names (last_name) of customers (CUSTOMER table) who rented a…

A: In this question, we are asked to write a SQL query to fetch the unique first and last names who…

Q: Write a query to list the first name and last name of all authors. Concatenate the first and last…

A: The SQL query is: SELECT CONCAT(CONCAT(firstname,' '),lastname) AS FullName FROM author;…

Q: What is the result of the query below: SELECT V_CODE, COUNT (P_CODE) FROM PRODUCT HAVING V_CODE…

A: Answer: Data is filtered using the having clause in accordance with the specified requirements.…

Q: 7- Write a query to produce the number of invoices and the total purchase amounts by customer, using…

A: Hey, since multiple questions posted, we will answer first question according to our policy. If you…

Q: Fill in the blanks('x') of this nested query to show the countries in EUROPE with a per capita GDP…

A: Hi. Let's move on to the complete SQL command in the next step.

Q: Write a query to display the author ID, first and last name for all authors who have never written a…

A: The below query will return the author ID, first name, and last name for all authors who have never…

Q: Write the T-SQL scripts for the following tasks: Write a query to select the employees whose…

A: Please refer to the following step for the complete solution of the problem above.

Q: Write a query to display the author ID, first name, last name, and year of birth for all authors.…

A: A database may be defined in one of these ways it's an organized series of established facts or…

Q: Write a query to display the author ID, first name, last name, and year of birth for all authors…

A: The below query will return a list of authors with their IDs, first names, last names, and birth…

Q: Using the software.subscription table, pull all columns for subscriptions that have been cancelled.…

A: For the given database, if the user wants to select all the columns by giving the condition that the…

Q: Add a Calculated Field to a Totals Query In the first blank column, add a new column named Time on…

A: MS Access:- MS Access is an information and data management tool that is used to manage database and…

Q: Write a query to display the current salary for each employee in department 300. Assume that only…

A: In a corporate setting, managing employee information, salary details, and departmental affiliations…

Q: write a MySQL procedure to retrieve and output the item number, description, storehouse number, and…

A: Always prefer to provide database of table.

Q: Write a query to create a report that displays the count of the number of tables in the SQ library.…

A: The question is to write SQL statement for the given query.

Q: Q1) Please write. a query statement from emp table to display deptno 10â€™s empno, ename, sal, and a…

A: --> As new_sale is increased 15% that means it increases by 115 out of 100% which is 115/100 =…

Q: Create a query to sort by city in ascending order and within each city the yrgoal in descending…

A: SQL Query: CREATE TABLE donors( donorid INT NOT NULL, name VARCHAR(20) NOT NULL, city…

Q: Write query "List the title and ISBN of all books using the BOOKS table"

A: Query to display all the records of the books table: SELECT ISBN, BOOK_TITLE FROM BOOKS;

Q: Write a query to display the current salary for each employee in department 300. Assume that only…

A: We must take into account the compensation history kept in the figure 48 table in order to get the…

Q: 3. Write a query that retrieves the following results (note the column names and order of results):

A: The required result needs to fetch below columns : Pub_id -> from publisher table Publisher's…

Q: Refer to the CREATE TABLE statement: CREATE TABLE Country ( ); Name VARCHAR (50), Population INT,…

A: The question is asking to complete an SQL INSERT INTO statement to add a new row into the 'Country'…

Q: The code: "SELECT SupplierlID FROM Products Order By SupplierName DESC;" extracts data from Products…

A: Sol1: Current procedure terminology (CPT) is a code such as reflex testing and is an integrated part…

Q: Write a query to display the checkout number, book number, patron ID, checkout date, and due date…

A: The query follows the syntax given below. SELECT column1, column2 FROM table1 WHERE condition ORDER…

Q: Create a query that uses any THREE of the tables (SCHOOL, DEPARTMENT, PROFESSOR, TECHNOLOGY &…

A: Creating all the three tables required for the question-For Department-create table…

Q: 10. Produce a list of each customer number and the third and fourth digits of his or her zip code.…

A: The query for a list of each customer number and the third and fourth digits of his or her zipcode…

Q: Topic: Databse Prompt: Transform as necessary and then write the XML elements to the appropriate…

A: The question asks to write for the SQL queries for the following: What are the titles and prices of…

Q: Run lab_emp.sql and lab_students.sql, this will create 2 tables for you (emp and students) and will…

A: Note : As per guidelines we can author 1 question per post. Repost the task 2.

Q: Which group function can be used to perform a count that includes NULL values?

A: Group by functions are : SUM ( [ALL | DISTINCT ] expression ) AVG ( ALL | DISTINCT ] expression )…

Concept explainers

Single Table

In a database management system, a single table is designed to obtain data of heterogeneous items. A single-table design with DynamoDB resolves the issue of raising multiple requests for the relevant data of an entity.

Question

100%

a) Create the two tables above and insert the data in the rows accordingly.
b) Write the standard MYSQL query to retrieve all information about the customer whose
customer number is 8590

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Create table SALESREP

VIEW

Create table CUSTOMER

VIEW

Insert into SALESREP

VIEW

Insert into CUSTOMER

VIEW

All information about customer number 8590

VIEW

Proof that I have done it myself

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 7 steps with 6 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Write a procedure named prc_cust_add to add a new customer to the CUST_MYSQL table. Use the following values in the new record: CUIST_NUM CUST_LNAME CUST_FNAME CUST_BALANCE 2003 Rauthor Peter 0.00 (You should execute the procedure and verify that the new customer was added to ensure your code is correct.)
Update table purchaseOrder, set column orderStateld equal to subquery a. (select column id from table orderstate where state equals "Payment sent") 3 Alter table supplierTransaction add column paymentDate as data type date, not null, default value of (CURRENT_DATE) (Note: the parenthesis MUST be included) Insert into table supplierTransaction, columns purchaseOrderId and total result set returned from query a. Select columns id and total from table purchaseOrder where column orderStateld is equal to subquery i. (select column id from table orderState where column state is equal to "Payment sent") Write Test Case 2 as a SQL join query to do the following a. Select supplierName from table supplier as Supplier b. Select purchaseOrderld from table supplierTransaction as “Purchase Order" c. Select description from table product as Product d. Select total from table purchaseOrder as “Product Total" e. Join tables şupplierTransaction, purchaseOrder, supplier, product f. Order by column…
Do this in MySQL please: Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX.
Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure.
ERD:- PK CHECKOUT Check Num FK1 Book_Num FK2 Pat_ID Check Out Date Check_Due Date Check_In_Date PK BOOK Book Num Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID -H- PATRON PK Pat ID Pat_FName Pat_LName Pat_Type WRITES PK,FK1 Book Num PK,FK2 Au ID AUTHOR PK AU ID Au_FName Au_LName Au_BirthYear
From invoices table, write a query to retrieve everything (all columns), plus, add an additional column that retrieves difference in the number of days between the invoice due date and paymentdate, and name the column “Difference”. Note that the difference in days should be calculated bysubtracting “payment_date” from “invoice_due_date”.Hint1, refer to the following sample:... Invoice Due Date Payment Date Difference... 2018-05-08 2018-05-07 1... 2018-05-10 2018-05-14 -4... 2018-05-13 2018-05-09 4Hint 2: simple subtraction or DATEDIFF function. Both works.
Write the following as MySQL query: Find the employee ID, first and last name, and salary for each employee whose salary ismore than $20,000 or who works in the shipping department. Use the UNION set operator. List the country ID, country name, city, and state/province. Be sure all country names areincluded in the result, regardless of whether there is no matching city and state/province. You have to use an outer join.
3. Run a query that gives the average grade on the final exam for male students and for femalestudents. Round that number to 1 decimal place only.
The following database will be used in this question: SALESREP SalesRepNo 654 734 345 434 RepName Jones Smith Chen Johnson HireDate 01/02/2005 02/03/2007 01/25/2004 11/23/2004 CUSTOMER CustNo 9870 8590 7840 4870 CustName Winston Gonzales Harris Miles Balance 500 350 SalesRepNo 345 434 654 345 800 100
Write a query that returns the Name of ALL Customers (based on CUS_FNAME and CUS_LNAME in CUSTOMER table, call this CUSTOMER_NAME), Phone number of ALL Customers (based on CUS_AREACODE and CUS_PHONE in CUSTOMER table, call this CUSTOMER_PHONE), for those haven’t flied before (based on the record in CHARTER table).
The given SQL creates a Song table and inserts some songs. The first SELECT statement selects songs released after 1992. The second SELECT statement selects the release year for song with ID 800. Create a third query that combines the two existing queries. The first SELECT should be the outer query, and the second SELECT should be the subquery. The ORDER BY clause should appear after the subquery. Run your solution and verify the new query returns a result table with five rows, all with release years after 1992. CREATE TABLE Song (ID INT,Title VARCHAR(60),Artist VARCHAR(60),ReleaseYear INT,Genre VARCHAR(20),PRIMARY KEY (ID)); INSERT INTO Song VALUES(100, 'Hey Jude', 'Beatles', 1968, 'pop rock'),(200, 'You Belong With Me', 'Taylor Swift', 2008, 'country pop'),(300, 'You\'re Still the One', 'Shania Twain', 1998, 'country pop'),(400, 'Need You Now', 'Lady Antebellum', 2011, 'country pop'),(500, 'You\'ve Lost That Lovin\' Feeling', 'The Righteous Brothers', 1964, 'R&B'),(600, 'That\'s…
Given a small company database tables below. Create the database and tables for the company and answer all the questions below. LINE CUSTOMER VENDOR INV_NUMBER LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Cus CODE CUS_LNAME CUS_FNAME CUS_INITIAL cus AREACODE CUS_PHONE CUS_BALANCE V_CODE V_NAME V_CONTACT V_AREACODE V_PHONE V_STATE V_ORDER 1000 3 23100-HB 0.05 10010 Ramas Afred 015 844-2573 21225 Bryson, Ine. Smithson 015 223-3234 TN 1006 4 89-WRE-Q 256.00 10011 Dunne Leona 713 804-1238 21220 SuperLoo, Inc. Flushing 04 215-8006 FL N 1 13-Q2/P2 2 14.00 1007 10012 Smith Kathy 015 804-2205 345.88 21231 DSE Supply Singh 815 228-3246 TN 1007 254778-2T 4.00 10013 Olowski Paul 815 804-2100 530.75 21344 Gomez Bros. Ortega 815 889-2546 KY N 1008 1 PVC23DRT 5.87 10014 Orlando Myron le15 222-1872 22507 Dome Supply Smith 01 e78-1419 GA IN 1008 2 WR3/TT3 3 110.05 10015 OBrian Amy 713 442-3381 23110 Randsets Ltd. Anderson 001 078-3008 GA 1008 3 23100-HB 0.05 10010 Brown James 015 297-1228 221.19 24004…