For the following tasks, your SQL must correctly manage transactions and use sequences to generate new primary keys for numeric primary key values (under no circumstances may a new primary key value be hardcoded as a number or value)
(i) Create sequences which will allow entry of data into the AW_STATUS, AW_DISPLAY and SALE tables - the sequences must begin at 300 and go up in steps of 1 (i.e., the first value is 300, the next 301, etc.)
(ii) Suppose it is now 10 AM on 22nd October 2020 and an artwork called “Saint Catherine of Siena'' has just been received by the MAU central warehouse from the artist with artist code 17. The minimum payment this artist is prepared to accept for this artwork is $500,000. Take the necessary steps in the database to record the required entries for this new arrival.
(iii) Using the timing as indicated, take the necessary steps in the database to record the following activities. You may assume that no other artworks have been added by this artist (artist code 17).
a) On the same day at 11 AM, this new arrival is sent from the MAU warehouse to Karma Art gallery (Ph:0413432569).
b) It is received by the gallery 3 hours and 15 minutes after leaving the MAU warehouse. MAU is immediately informed of the safe arrival of this extremely precious artwork through a telephone call.
c) On the next day the gallery places the artwork on display for a total of 10 days.
(iv) It is now 2:30 PM on the 5th day since this artwork has been displayed. No other artwork has been put on display after this artwork was displayed in any gallery (ie. it is the most recent display). It is now sold to a customer (customer id 1) for $850,000. Take the necessary steps in the database to record the required activities.
Please help me with this tasks.
Thank you in advance:)
Solution:
(i) CREATE SEQUENCE SQ1 START WITH 300 INCREMENT BY 1;
INSERT INTO AW-STATUS(aws_id) VALUES(SQ1.nextval);
INSERT INTO AW-DISPLAY(aw_display_id) VALUES(SQ1.nextval);
INSERT INTO SALE(sale_id) VALUES(SQ1.nextval);
(ii) CREATE SEQUENCE SQ2 START WITH 500000;
ALTER TABLE AW_STATUS ADD PRIMARY KEY(artist_code);
INSERT INTO ARTWORK VALUES(17,001,“Saint Catherine of Siena'',SQ2.nextval,"2020-10-22 10:00:00");
INSERT INTO AW_STATUS VALUES(300,17,001,"2020-10-22 10:00:00","Y",111);
(iii) (a) INSERT INTO AW_STATUS VALUES(SQ1.nextval,17,001,"2020-10-22 11:00:00","N",111);
(b) UPDATE AW_STATUS SET set aws_date_time="2020-10-22 14:15:00",aws_action="Y" where gallery_id=111;
(c) INSERT INTO AW_DISPLAY VALUES(300,17,001,"2020-10-23", "2020-11-01",111);
(iv) INSERT INTO SALE VALUES(SQ1.nextval,"2020-10-27",850000 ,1,300);
For the following tasks, your SQL must correctly manage transactions and use sequences to generate new...
Write Ten SQL SELECT statements to query the STUDENT schema you created for practice lab. 4. List all cities that have 10 or more students and instructors combined. Show city, state, number of student residents, number of instructor residents, and total student/instructor residents in that city. Sort by total in descending order. 5. List the instructor id and name of the instructors that teach fewer than 10 sections. 7. Find how many students are enrolled in sections taught by Todd...
An online company sells hundreds of office supply products on
its e-Commerce store. It has asked you to design and implement a
sales order processing system which will be used by users in our
Operations department to process sales orders. Here is an overview
of the sales order process. Customers make purchases by placing
orders. Each customer has a customer number and profile (such as
name, shipping address). To simplify the matter, each time an order
is placed, only one...
1,List the first and last name of the donators that donated in
December 2009.
2. List the ID's of the volunteers that have not worked.
3. List the ID, first name, and last name of any employees who
works in the Finance department.
4. List all the different prices suppliers have for 'Tasty Meat'
product.
5. Select the store ID and store phone number for all stores in
'St.Paul'.
6. List the member first and last name, address, city, zip...
Use only a single SQL statement for each of the following questions 1 Give a listing of all the ssns,first names and the class descriptions of all the classes the students are taking. If there are no class _descriptions display 'No description is available yet'. (USE NVL) 2 Give a listing of only the lname and the class_code for students who are taking 'Introduction to C programming'. (Inner join) 3 Give a lising of all the class_descriptions and the number...