Perform the following tasks in your < userid > schema and create snapshots of ex
ID: 3736455 • Letter: P
Question
Perform the following tasks in your <userid> schema and create snapshots of executed SQL statements from the History and their outputs from Answer Set.
Provide proof of ownership. Tasks w/o proof of ownership will not be counted.
Create Product_T table in your DB <userid> schema
Show table Product_T definition that was created in the <userid> schema
Enter the following data into <userid>.Product_T
Execute query: List Current_User, ProductID, ProductDescription, ProductStandatdPrice from <userid>.Product_T and sort output table by ProductStandatdPrice in ascending order
Enter another product into Product_T:
Execute query: List Current_User, ProductID, ProductDescription, ProductStandatdPrice from <userid>.Product_T for ProductID = 6
Modify the <userid>.Product_T table by adding an attribute QtyOnHand that can be used to track the finished goods inventory. The field should be an integer
Show updated <userid>.Product_T table definition
Show all data stored in <userid>.Product_T and sort output table by ProductID in ascending order
Modify the <userid>.Product_T table by adding CHECK constraint for attribute QtyOnHand that will accepts only positive numbers that are less than or equal to 5,000. [Hint: use CHECK constraint for range of values like CHECK (A > 1 and A <= 100)]
Show updated <userid>.Product_T table definition
Enter QtyOnHand values into the <userid>.Product_T table as following:
List CURRENT_USER, ProductID, QtyOnHand from <userid>.Product_T and sort output by QtyOnHand in descending order
Update QtyOnHand for ProductID = 1 to 150
Show CURRENT_USER, ProductID, QtyOnHand from <userid>.Product_T for ProductID = 1
Update QtyOnHand for ProductID = 2 to 5,001 units.
Update QtyOnHand for ProductID = 3 to –10 (minus 10).
Remove “End Table” product from the table <userid>.Product_T
Show CURRENT_USER, ProductID, QtyOnHand from <userid>.Product_T for ProductID = 1
Delete column QtyOnHand from <userid>.Product_T and execute
Show updated <userid>.Product_T table definition
Remove all rows from the <userid>.Product_T table in one SQL statement.
Prove that the <userid>.Product_T table is empty.
Delete table <userid>.Product_T from <userid> schema.
Prove that the <userid>.Product_T table doesn’t exist.
Create table <userid>.Course_T with attributes
CourseID (6 fixed characters, Primary key)
CourseName (60 variable characters)
For CourseID add In-List constraint limiting its values to 'BA 353', 'CS 137', 'BA 360'.
Show table definition for Course_T stored in schema userid
Enter two courses to the table <userid>.Course_T
CourseID
CourseName
BA 353
Information Systems Database Management
BA 460
Quantitative Methods for Business and Finance Management
Show all data stored in <userid>.Course_T
Explanation / Answer
Solution:
The first four subparts have been answered as per Chegg guidelines, please repost others.
Create Product_T table in your DB <userid> schema
CREATE TABLE Product_T (
productID int NOT NULL,
productName varchar(40);
productDescription text(500)
PRIMARY KEY (productID)
);
Show table Product_T definition that was created in the <userid> schema
SELECT * FROM Product_T
Enter the following data into <userid>.Product_T
INSERT INTO <userid>Product_T(productNumber, productName, productDescription )
VALUES ('123', 'abc', 'This is sparta')
Execute query: List Current_User, ProductID, ProductDescription, ProductStandatdPrice from <userid>.Product_T and sort output table by ProductStandatdPrice in ascending order
SELECT Current_User, ProductID, ProductDescription, ProductStandatdPrice from <userid>.Product_T ORDER BY ProductStandatdPrice ASC.
Complete details of the table was not given so I have taken standard columns, you can edit them accordingly.
The query will work like a charm. All the best
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)