From the book Database Systems by Carlos Coronel. Please fully explain your answ
ID: 3558203 • Letter: F
Question
From the book Database Systems by Carlos Coronel. Please fully explain your answer. If I could award more than 1500 points I would. Thank you in advance.
Refer to the database tables in Figure P8.1 in this problem. Refer to Section 8.4 for this problem.
a. Create the CUSTOMER_2 table.
Hint: to create the CUSTOMER table, you may use:
CREATE TABLE CUSTOMER (
CUST_NUM NUMBER PRIMARY KEY,
CUST_LNAME VARCHAR(20),
CUST_FNAME VARCHAR(20),
CUST_BALANCE NUMBER);
b. Insert the data into the CUSTOMER_2 table created in part a.
Hint: to insert data into the CUSTOMER_2 table, you may use:
INSERT INTO CUSTOMER VALUES(1000 ,'Smith' ,'Jeanne' ,1050.11);
INSERT INTO CUSTOMER VALUES(1001 ,'Ortega' ,'Juan' ,840.92);
c. Write the query that will generate only the records that are unique to the CUSTOMER table.
d. Write a query to show the invoice number, the customer number, the customer name , for all invoices in the INVOICE table with an invoice amount of $500 or more.
Hint: You may assume that the customers are from the CUSTOMER table and not the CUSTOMER_2 table.
Explanation / Answer
Ans a.) CREATE TABLE CUSTOMER_2 (
CUST_NUM NUMBER PRIMARY KEY,
CUST_LNAME VARCHAR(20),
CUST_FNAME VARCHAR(20),
CUST_BALANCE NUMBER);
Explanation:- This is a simple query of creating a table in SQL. You need to enter the command:-
CREATE TABLE TABLE_NAME(
COLUMN_NAME1 DATA_TYPE,
COLUMN_NAME2 DATA_TYPE);
I had mentioned CUST_NUM as the primary key as it is unique for all customer records.
Ans b.) INSERT INTO CUSTOMER_2 VALUES(10 ,'Agarwal' ,'Manish' ,5550.1);
Explanation:- The insert command in SQL takes the form:-
INSERT INTO TABLE_NAME VALUES(RESPECTIVE VALUES FOR EACH COLUMN SEPARATED BY COMMAS);
NOTE:- IF THE DATA_TYPE IS A CHARACTER OR A VARCHAR, YOU NEED TO ENCLOSE THE VALUES IN ' '.
Ans c.) SELECT DISTINCT CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE FROM CUSTOMER;
Explanation:- This query is very important. It is asking to generate unique records from the customer table. The DISTINCT keyword in SQL is used to select unique rows and eliminates duplicates from appearing in the resultant output. Thus I combined the SELECT statement with DISTINCT keyword to select unique records from the CUSTOMER table.
Ans d.) SELECT INVOICE_NUM,CUST_NUM,CUST_LNAME,CUST_FNAME FROM INVOICE,CUSTOMER WHERE INVOICE_AMT>=500;
Explanation:- The SELECT statement can have a WHERE clause to attach some conditions to it. Here, i have attached the condition that the INVOICE_AMT should be greater than or equal to 500 for an invoice number to appear in the resultant output.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.