(Solved) : Consider Following Relational Schemas Singer Singerid Number 4 Singername Varchar2 20 City Q31677100 . . .
Consider the following relational schemas
Singer(singerid: number(4); singer_name: varchar2(20); city:varchar2(15); street: varchar2(10); city: varchar2(15))
Sings(singerid: number(4); lyricid: number(4))
Lyric( lyricid: number(4); duration: number(3); dor: date;lyric_writer: varchar2(20); movie: varchar2(25))
Note: ‘dor’ stands for ‘date of recording’.
Do the following
1. Create the above tables with primary on appropriate fields.Enforce the foreign keys on the fields of ‘Sings’ table.
2. Ensure that duration of each lyric should be not less than 5minsand not more than 7 mins. Also ensure that field ‘singer_name’should not be null. Eliminate ‘street’ field.
3. Add at least 6 records to each table as per the requirements ofqueries given below
4. Display lyricid sung by each singer and then get total number oflyrics sung by each singer.
5. Retrieve maximum, minimum and average duration of songs for eachmovie. (c-2)
6. Display date of recording of the song by adding one month toeach one of them.
7. Retrieve the lyric whose duration is more than the duration ofany other lyric in the movie ‘Khiladi’. (Sub Query)
8. Create a virtual table that displays singer names in capitals,movie title in smaller letters and the length of movie titles.
Mandatory:Display the structure and content of each table for each change andstore it in your file to be uploaded.
Answer thefollowing queries in SQL and obtain required output (Output for each querybe there in your file and it carries halfof the marks allotted to the query)
Expert Answer
Answer to Consider Following Relational Schemas Singer Singerid Number 4 Singername Varchar2 20 City Q31677100 . . .
OR