Please compile and also 3) Develop an SQL script file to perform the following q
ID: 3795454 • Letter: P
Question
Please compile and also 3) Develop an SQL script file to perform the following queries and updates. You should test your SQL script file until it runs without any errors. • Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number. • Retrieve all of the videos rented in the last 30 days and sort in chronological rental date order. • Produce a list of your distributors and all their information sorted in order by company name. • Update a customer name to change their maiden name to a married name. You can choose which row to update. Make sure that you use the primary key column in your WHERE clause to affect only a specific row. You may want to include a ROLLBACK statement to undo your data update. • Delete a customer from the database. You can choose which row to delete. Make sure that you use the primary key column in your WHERE clause to affect only a specific row. You may want to include a ROLLBACK statement to undo your data deletion. SPOOL figure10.log DROP TABLE Distributors CASCADE CONSTRAINT; DROP TABLE Catalogs CASCADE CONSTRAINT; DROP TABLE Orders CASCADE CONSTRAINT; DROP TABLE Customers CASCADE CONSTRAINT; CREATE TABLE Customers ( Customer_id NUMBER(10) NOT NULL, CustomerFirst_name VARCHAR(20) NOT NULL, CustomerLast_name VARCHAR(20) NOT NULL, CustomerStreet_address VARCHAR(30) NOT NULL, CostumerCity VARCHAR(20) NOT NULL, CustomerState CHAR(2) NOT NULL, CostumerZip VARCHAR(9) NOT NULL, CustomerPhone_number VARCHAR(10) NOT NULL, CONSTRAINT Customer_PK PRIMARY KEY (Customer_id)); CREATE TABLE Orders ( Order_id NUMBER(10) NOT NULL, OrderDate DATE DEFAULT SYSDATE, CONSTRAINT Order_PK PRIMARY KEY (Order_id)); CREATE TABLE Products ( Product_id NUMBER(10) NOT NULL, ProductDescription VARCHAR2(50), ProductMovieType NUMBER(11), Order_id NUMBER(10) NOT NULL, CONSTRAINT Product_PK PRIMARY KEY(Product_id), CONSTRAINT Product_FK FOREIGN KEY(Order_id) REFERENCES Orders (Order_id)); CREATE TABLE Distributors ( DistributorDisc_id NUMBER(11,0) NOT NULL, DistributorPrice NUMBER(11,0) NOT NULL, DistributorOrderQuantity NUMBER(11), Order_id NUMBER(10) NOT NULL, CONSTRAINT Distributor_PK PRIMARY KEY (DistributorDisc_id) ,CONSTRAINT Distributor_FK FOREIGN KEY (Order_id) REFERENCES Orders (Order_id)); CREATE TABLE Catalogs ( Catalog_id NUMBER(5,0) NOT NULL, CatalogRating VARCHAR(10) NOT NULL, CatalogAcademyAwards VARCHAR(50) NOT NULL, CatalogDateReleased DATE DEFAULT SYSDATE, Product_id NUMBER(10) NOT NULL, Order_id NUMBER(10) NOT NULL, DistributorDisc_id NUMBER(11,0) NOT NULL, CONSTRAINT Catalog_PK1 PRIMARY KEY (Catalog_id), CONSTRAINT Catalog_FK1 FOREIGN KEY (Order_id) REFERENCES Orders (Order_id), CONSTRAINT Catalog_FK2 FOREIGN KEY (Product_id) REFERENCES Product (Product_id), CONSTRAINT Catalog_FK3 FOREIGN KEY (DistributorDisc_id) REFERENCES Distributor (DistributorDisc_id)); CREATE TABLE Rentals(Rental_id NUMBER(9) NOT NULL,RentalReturnDate DATE DEFAULT SYSDATE,RentalRentedQuantity VARCHAR(1) NOT NULL, RentalTotal_charge NUMBER(19,4) NOT NULL,RentalTax NUMBER(19,4) NOT NULL,Product_id NUMBER(10) NOT NULL,CONSTRAINT Rental_PK PRIMARY KEY(Rental_id), CONSTRAINT Rental_FK FOREIGN KEY(Product_id) REFERENCES Product(Product_id)); /* Create Foreign Keys */ ALTER TABLE Rentals ADD FOREIGN KEY (DistributorDisc_id) REFERENCES Distributors (DistributorDisc_id); ALTER TABLE Distributors ADD FOREIGN KEY (Product_id) REFERENCES Product (Product_id); ALTER TABLE Products ADD FOREIGN KEY (DistributorDisc_id) REFERENCES Distributor (DistributorDisc_id); ALTER TABLE Rentals DROP COLUMN Catalog_id; ALTER TABLE Customers ADD FOREIGN KEY (RentalTotal_charge); -- Customers INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip, CustomerPhone_number) VALUES ('100', 'Ben', 'Bill', '201 Ash Street', 'San Silva', 'MD', '20850', '443-123-4567'); INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip, CustomerPhone_number) VALUES ('101', 'James', 'Seth', '142 Athens Avenue', 'Silver Boro', 'MD', '20841', '443-230-4444'); INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip, CustomerPhone_number) VALUES ('200', 'Victor', 'Jones', '124 Flower Street', 'Rockville','MD', '20857', '301-547-2036'); INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip, CustomerPhone_number) VALUES ('300', 'Ashley', 'Maverick', '105 University Boulevard', 'Baltimore', 'MD', '21593', '228-567-8174'); INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip, CustomerPhone_number) VALUES ('450', 'Bill','Booker', '208 New Hampshire Avenue', 'Wheaton', 'MD', '20103', '250-413-7020'); -- Order INSERT INTO Order (Order_id, OrderDate) VALUES ('1', TO_DATE('02/05/2007','MM/DD/YYYY')); INSERT INTO Order (Order_id, OrderDate) VALUES ('2', TO_DATE('2/20/2011','MM/DD/YYYY')); INSERT INTO Order (Order_id, OrderDate) VALUES ('3', TO_DATE('02/05/2003','MM/DD/YYYY')); INSERT INTO Order (Order_id, OrderDate) VALUES ('4', TO_DATE('02/05/2005','MM/DD/YYYY')); INSERT INTO Order (Order_id, OrderDate) VALUES ('5', TO_DATE('02/05/2006','MM/DD/YYYY')); -- Product INSERT INTO Product (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('1', 'Beauty and the Beast', 'Romance','6'); INSERT INTO Product (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('2', 'Mist', 'Thriller','7'); INSERT INTO Product (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('3', 'Police Academy', 'Comedy','8'); INSERT INTO Product (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('5', 'When Sally met Harry', 'Romance','9'); INSERT INTO Product (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('6', 'Rambo', 'Action','10'); -- Distributor INSERT INTO Distributor (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('1', '5','10', '11'); INSERT INTO Distributor (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('2', '5','10', '12'); INSERT INTO Distributor (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('3', '15','10','13'); INSERT INTO Distributor (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('4', '5','10', '14'); INSERT INTO Distributor (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('5', '5','10', '15'); -- Catalog INSERT INTO Catalog (Catalog_id, CatalogRating, CatalogAcademyAwards, CatalogDateRelease, Product_id, Order_id, DistributorDisc_id) VALUES ('1', 'Rated R','2', TO_DATE('02/05/2000','MM/DD/YYYY'),'7','16','6'); INSERT INTO Catalog (Catalog_id, CatalogRating, CatalogAcademyAwards, CatalogDateRelease, Product_id, Order_id, DistributorDisc_id) VALUES ('2', 'Parental Guide', '12', TO_DATE('02/05/2001','MM/DD/YYYY'),'8',,'17','7'); INSERT INTO Catalog (Catalog_id, CatalogRating, CatalogAcademyAwards, CatalogDateRelease, Product_id, Order_id, DistributorDisc_id) VALUES ('3', 'Youth','3',TO_DATE('02/05/2002','MM/DD/YYYY'), '9','18','8'); INSERT INTO Catalog (Catalog_id, CatalogRating, CatalogAcademyAwards, CatalogDateRelease, Product_id, Order_id, DistributorDisc_id) VALUES ('4', 'Everyone','8', TO_DATE('02/05/2003','MM/DD/YYYY'),'10','19','9'); INSERT INTO Catalog (Catalog_id, CatalogRating, CatalogAcademyAwards, CatalogDateRelease, Product_id, Order_id, DistributorDisc_id) VALUES ('5', 'Preschool','9',TO_DATE('02/05/2004','MM/DD/YYYY'),'11','20','10'); -- Rental INSERT INTO Rental (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id) VALUES ('1', TO_DATE('04/05/2009','MM/DD/YYYY'), '35','17.99', '0.3','11'); INSERT INTO Rental (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id) VALUES ('2', TO_DATE('03/04/2001','MM/DD/YYYY'), '34','17.99', '0.3','12'); INSERT INTO Rental (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id) VALUES ('3', TO_DATE('04/03/2002','MM/DD/YYYY'), '33','17.99', '0.3','13'); INSERT INTO Rental (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id) VALUES ('4', TO_DATE('04/01/2003','MM/DD/YYYY'), '32','17.99', '0.3','14'); INSERT INTO Rental (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id) VALUES ('5', TO_DATE('04/03/2004','MM/DD/YYYY'), '31','17.99', '0.3','15'); SPOOL OFF; SPOOL OFF
Explanation / Answer
CREATE TABLE Customers (
Customer_id NUMBER(10) NOT NULL,
CustomerFirst_name VARCHAR(20) NOT NULL,
CustomerLast_name VARCHAR(20) NOT NULL,
CustomerStreet_address VARCHAR(30) NOT NULL,
CostumerCity VARCHAR(20) NOT NULL,
CustomerState CHAR(2) NOT NULL,
CostumerZip VARCHAR(9) NOT NULL,
CustomerPhone_number VARCHAR(12) NOT NULL,
CONSTRAINT Customer_PK PRIMARY KEY (Customer_id));
--Customers
INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState,
CostumerZip, CustomerPhone_number) VALUES ('100', 'Ben', 'Bill', '201 Ash Street', 'San Silva', 'MD', '20850', '443-123-4567');
INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip,
CustomerPhone_number) VALUES ('101', 'James', 'Seth', '142 Athens Avenue', 'Silver Boro', 'MD', '20841', '443-230-4444');
INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState,
CostumerZip, CustomerPhone_number) VALUES ('200', 'Victor', 'Jones', '124 Flower Street', 'Rockville','MD', '20857', '301-547-2036');
INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip,
CustomerPhone_number) VALUES ('300', 'Ashley', 'Maverick', '105 University Boulevard', 'Baltimore', 'MD', '21593', '228-567-8174');
INSERT INTO Customers (Customer_id, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity, CustomerState, CostumerZip,
CustomerPhone_number) VALUES ('450', 'Bill','Booker', '208 New Hampshire Avenue', 'Wheaton', 'MD', '20103', '250-413-7020');
CREATE TABLE Orders(
Order_id NUMBER(10) NOT NULL,
OrderDate DATE DEFAULT SYSDATE,
CONSTRAINT Order_PK PRIMARY KEY (Order_id));
-- Order
INSERT INTO Orders (Order_id, OrderDate) VALUES ('1', TO_DATE('02/05/2007','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate) VALUES ('2', TO_DATE('2/20/2011','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate) VALUES ('3', TO_DATE('02/05/2003','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate) VALUES ('4', TO_DATE('02/05/2005','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('5', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('6', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('7', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('8', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('9', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('10', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('11', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('12', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('13', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('14', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('15', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('15', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('16', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('17', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('18', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('19', TO_DATE('02/05/2006','MM/DD/YYYY'));
INSERT INTO Orders (Order_id, OrderDate)VALUES ('20', TO_DATE('02/05/2006','MM/DD/YYYY'));
CREATE TABLE Products (
Product_id NUMBER(10) NOT NULL CONSTRAINT Product_PK PRIMARY KEY,
ProductDescription VARCHAR2(50),
ProductMovieType varchar(11),
Order_id NUMBER(10) NOT NULL
CONSTRAINT Product_FK REFERENCES Orders (Order_id));
-- Product
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('1', 'Beauty and the Beast', 'Romance','6');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('2', 'Mist', 'Thriller','7');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('3', 'Police Academy', 'Comedy','8');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('5', 'When Sally met Harry', 'Romance','9');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('6', 'Rambo', 'Action','10');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('7', 'Rambo', 'Action','10');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('8', 'Rambo', 'Action','10');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('9', 'Rambo', 'Action','10');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('10', 'Rambo', 'Action','10');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('11', 'Rambo', 'Action','10');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('12', 'Beauty and the Beast', 'Romance','6');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('13', 'Mist', 'Thriller','7');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('14', 'Police Academy', 'Comedy','8');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('15', 'When Sally met Harry', 'Romance','9');
INSERT INTO Products (Product_id, ProductDescription, ProductMovieType, Order_id) VALUES ('16', 'Rambo', 'Action','10');
CREATE TABLE Distributors (
DistributorDisc_id NUMBER(11,0) NOT NULL,
DistributorPrice NUMBER(11,0) NOT NULL,
DistributorOrderQuantity NUMBER(11),
Order_id NUMBER(10) NOT NULL,
CONSTRAINT Distributor_PK PRIMARY KEY (DistributorDisc_id) ,
CONSTRAINT Distributor_FK FOREIGN KEY (Order_id) REFERENCES Orders (Order_id));
-- Distributor
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('1', '5','10', '11');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('2', '5','10', '12');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('3', '15','10','13');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('4', '5','10', '14');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('5', '5','10', '15');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('6', '5','10', '15');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('7', '5','10', '15');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('8', '5','10', '15');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('9', '5','10', '15');
INSERT INTO Distributors (DistributorDisc_id, DistributorPrice, DistributorOrderQuantity, Order_id) VALUES ('10', '5','10', '15');
CREATE TABLE Catalogs (
Catalog_id NUMBER(5,0) NOT NULL,
CatalogRating VARCHAR(10) NOT NULL,
CatalogAcademyAwards VARCHAR(50) NOT NULL,
CatalogDateReleased DATE DEFAULT SYSDATE,
Product_id NUMBER(10) NOT NULL,
Order_id NUMBER(10) NOT NULL,
DistributorDisc_id NUMBER(11,0) NOT NULL,
CONSTRAINT Catalog_PK1 PRIMARY KEY (Catalog_id),
CONSTRAINT Catalog_FK1 FOREIGN KEY (Order_id) REFERENCES Orders (Order_id),
CONSTRAINT Catalog_FK2 FOREIGN KEY (Product_id) REFERENCES Products (Product_id),
CONSTRAINT Catalog_FK3 FOREIGN KEY (DistributorDisc_id) REFERENCES Distributors (DistributorDisc_id));
-- Catalog
INSERT INTO Catalogs (Catalog_id,CatalogAcademyAwards,CatalogRating, CatalogDateReleased, Product_id, Order_id, DistributorDisc_id)
VALUES ('1', 'Rated R','2',TO_DATE('02/05/2000','MM/DD/YYYY'),'7','16','6');
INSERT INTO Catalogs (Catalog_id,CatalogAcademyAwards,CatalogRating, CatalogDateReleased, Product_id, Order_id, DistributorDisc_id)
VALUES ('2', 'Parental Guide', '12', TO_DATE('02/05/2001','MM/DD/YYYY'),'8','17','7');
INSERT INTO Catalogs (Catalog_id,CatalogAcademyAwards,CatalogRating, CatalogDateReleased, Product_id, Order_id, DistributorDisc_id)
VALUES ('3', 'Youth','3',TO_DATE('02/05/2002','MM/DD/YYYY'), '9','18','8');
INSERT INTO Catalogs (Catalog_id,CatalogAcademyAwards,CatalogRating, CatalogDateReleased, Product_id, Order_id, DistributorDisc_id)
VALUES ('4', 'Everyone','8', TO_DATE('02/05/2003','MM/DD/YYYY'),'10','19','9');
INSERT INTO Catalogs (Catalog_id,CatalogAcademyAwards,CatalogRating, CatalogDateReleased, Product_id, Order_id, DistributorDisc_id)
VALUES ('5', 'Preschool','9',TO_DATE('02/05/2004','MM/DD/YYYY'),'11','20','10');
CREATE TABLE Rentals(
Rental_id NUMBER(9) NOT NULL,
RentalReturnDate DATE DEFAULT SYSDATE,
RentalRentedQuantity VARCHAR(2) NOT NULL,
RentalTotal_charge NUMBER(19,4) NOT NULL,
RentalTax NUMBER(19,4) NOT NULL,
Product_id NUMBER(10) NOT NULL,
CONSTRAINT Rental_PK PRIMARY KEY(Rental_id),
CONSTRAINT Rental_FK FOREIGN KEY(Product_id) REFERENCES Products(Product_id));
-- Rental
INSERT INTO Rentals (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id)
VALUES ('1', TO_DATE('04/05/2009','MM/DD/YYYY'), '35','17.99', '0.3','11');
INSERT INTO Rentals (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id)
VALUES ('2', TO_DATE('03/04/2001','MM/DD/YYYY'), '34','17.99', '0.3','12');
INSERT INTO Rentals (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id)
VALUES ('3', TO_DATE('04/03/2002','MM/DD/YYYY'), '33','17.99', '0.3','13');
INSERT INTO Rentals (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id)
VALUES ('4', TO_DATE('04/01/2003','MM/DD/YYYY'), '32','17.99', '0.3','14');
INSERT INTO Rentals (Rental_id, RentalReturnDate, RentalRentedQuantity, RentalTotal_charge, RentalTax, Product_id)
VALUES ('5', TO_DATE('04/03/2004','MM/DD/YYYY'), '31','17.99', '0.3','15');
-- • Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number.
Answer: for the first query.
SELECT Customer_id AS ACCOUNT_NUMBER, CustomerFirst_name, CustomerLast_name, CustomerStreet_address, CostumerCity,
CostumerZip FROM CUSTOMERS ORDER BY CUSTOMER_ID;
• Update a customer name to change their maiden name to a married name. You can choose which row to update.
Make sure that you use the primary key column in your WHERE clause to affect only a specific row.
You may want to include a ROLLBACK statement to undo your data update.
Answer :- For updating the customers name for the perticular customer by using the where clause of perticular customer_id then update thatcustomr name.
• Delete a customer from the database. You can choose which row to delete.
Make sure that you use the primary key column in your WHERE clause to affect only a specific row.
You may want to include a ROLLBACK statement to undo your data deletion.
Answer :- For delete a perticular customer name from database by using query "delete from <tablename> where <condition> ". and rollback if want again.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.