Write a script that adds rows to the database that you created in exercise 2. Ad

Question

Write a script that adds rows to the database that you created in exercise 2. Add two rows to the Users and Products tables. Add three rows to the Downloads table: one row for user 1 and product 2; one for user 2 and product 1; and one for user 2 and product 2. Use the GETDATE function to insert the current date and time into the DownloadDate column. Write a SELECT statement that joins the three tables and retrieves the data from these tables like this: Sort the results by the email address in descending order and the product name in ascending order

This what I have so far, but I'm not so sure where to clean up

CREATE TABLE USERS2

(

USERID int Primary key NOT NULL,

EmailAddress varchar(20),
FirstName varchar(20),
LastName varchar(20)
);

CREATE TABLE PRODUCTS2
(
ProductID int Primary key NOT NULL,
ProductName varchar(20)
);

CREATE TABLE DOWNLOADS2
(
DownloadID int Primary key NOT NULL,
USERID int,
DownloadDate date,
filename varchar(20),
ProductID int,
CONSTRAINT fkColumn
FOREIGN KEY (USERID)
REFERENCES users2(USERID),
FOREIGN KEY (ProductID)
REFERENCES products2(ProductID)
);

CREATE OR REPLACE PROCEDURE Insert INTO PRODUCTS2 VALUES (ProductName)
BEGIN
INSERT INTO PRODUCTS2 VALUES((select * from PRODUCTS2)+1 , ProductName)
END;

begin
Insert INTO PRODUCTS2 VALUES('waffers');
end

begin
Insert INTO PRODUCTS2 VALUES('chocolates');
end

select * from products2

CREATE OR REPLACE PROCEDURE Insert into USERS2 values (EmailAddress,FirstName,LastName)
BEGIN
INSERT INTO USERS2 VALUES((select count(*) from users2)+1 , EmailAddress,FirstName,LastName);
END;

begin
Insert INTO USERS2 VALUES('abc@bsd.sdcj','hdbh','sndjskj');
end

begin
Insert INTO USERS2 VALUES('cdc@sdd.sdcj','absxj','sadja');
end

select * from USERS2

CREATE OR REPLACE PROCEDURE INSERT INTO DOWNLOADS2 VALUES(USERID in number,PRODUCTID in number ,fl IN string)
AS BEGIN
INSERT INTO DOWNLOADS2 VALUES((select count(*) from DOWNLOADS2)+1 ,uid,GETDATE(),fl,pid);
END;

begin
INSERT INTO DOWNLOADS2 VALUES(1,2,'sbsndc');
end

begin
INSERT INTO DOWNLOADS2 VALUES(2,1,'sdnjs');
end

begin
INSERT INTO DOWNLOADS2 VALUES(2,2,'ssnc');
end

select * from DOWNLOADS2

select EmailAddress, FirstName,LastName,DownloadDate,
filename,ProductName from DOWNLOADS2 inner join PRODUCTS2
on DOWNLOADS2.ProductID=PRODUCTS2.ProductID inner join users2 on DOWNLOADS2.USERID=USERS2.USERID

Explanation / Answer

select * from USERS2 inner join DOWNLOADS2 on DOWNLOADS2.USERID=USERS2.USERID inner join PRODUCTS2 on DOWNLOADS2.ProductID=PRODUCTS2.ProductID order by EmailAddress desc, ProductName asc;

--> The above is the query which retrieves the information of the users and products and the user downloads by the descending order of email address and the ascending order of product name using inner join

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.