Section 1: Short Answers 1. If you were to run the following DML command, what e

Question

Section 1: Short Answers

1. If you were to run the following DML command, what else would you have to do to maintain consistency in the DB?

insert into titles values (8, 2, "Down Below the Cold, Cold Ground", 3, '0987654321', 'blues');

2. What happens if you run a delete statement that contains a from clause but no where clause?

Section 3 English To SQL, Write the SQL/DML/DDL commands to accomplish the task described in each question.

Use transactions, so that you can easily recover the original data. If a rollback does not work for some
reason, you can run lyric.sql again.

5. The city council of Alverez, TX changes the spelling of the name of the town to "Alvarez". Update the members table accordingly.

6. a) Delete all the artists which have not recorded any jazz titles (remember the distinction between this and deleting those that have recorded non-jazz titles!)
b) You decide not to delete the members of these artists since they might join other artists in the future. What else should you do in the DB when you delete the artists?

7. Insert a new record into the members table using any valid data and update xrefartistsmembers to make the person a member of Sonata. (2 statements)

8. Change the emails for all members of Sonata lastname@sonata.org, where lastname is the member's actual last name.

Tracks TitleID TrackNum TrackTitle LengthSeconds MP3 RealAud Titles Title ID ArtistID Title Studi oID UPC Gee nre Genre B Genre Artists Y ArtistID ArtistName City Region Country Web Address Entry Date Lead Source Xref Artists Members MemberID V Y Art tID RespParty Studios StudioID StudioN ame Address City Region PostalCode Country WebAdd Contact: EMa Phone Sales ID Members B MemberID ame LastName Address City Region PostalCode Country HomePhone Work Phone EMa Gender Birthday SalesID Sales People Sales ID FirstNam La Name nitials Base Supervisor

Explanation / Answer

1. If you were to run the following DML command, what else would you have to do to maintain consistency in the DB?

insert into titles values (8, 2, "Down Below the Cold, Cold Ground", 3, '0987654321', 'blues');

          a. Since Title have foreign keys ArtistID,StudioID,Genre referencing Artists, Studios and Genre tables respectively so before inserting values into titles, it must be made sure that primary keys of these tables corresponding to foreign keys of titles contain the values being inserted.

            b. Also,TitleID works as the descriptor key for table Tracks, so records should be added to tracks as well. It doesn't make any sense if a title doesn't have any tracks.

  

2. What happens if you run a delete statement that contains a from clause but no where clause?

          delete from table_name will delete all records from table.

5. The city council of Alverez, TX changes the spelling of the name of the town to "Alvarez". Update the members table accordingly.

          Update Members

            Set City = "Alvarez"

            Where City = "Alvarez" and Region = "TX"

6. a) Delete all the artists which have not recorded any jazz titles (remember the distinction between this and deleting those that have recorded non-jazz titles!)

          Since ArtistID in xrefartistsmembers is a foreign key referencing Artists so any Artist record to be deleted from Artists must be deleted from xrefartistsmembers 1st.

Also we need to delete members of artists to be removed from table Members.

We do it like this.

           

            a. Delete Members of those artists who have not recorded any jazz titles from Members table.

            b. Delete those records from xrefartistsmembers where artist has not recorded any jazz titles.

            c. Delete those records from artists where artist has not recorded any jazz titles.

            SQL queries for these operations are as following.

           

            a. Delete Members of those artists who have not recorded any jazz titles.

           

            Delete from members

            Where MemberID NOT IN (Select MemberID

                                                From xrefartistsmembers,Titles

                                                Where xrefartistsmembers.ArtistID = Titles. ArtistID and                                                                       Titles.title like "%jazz%")

            b. Delete those records from xrefartistsmembers where artists has not recorded any jazz titles.

            Delete from xrefartistsmembers

            Where ArtistID NOT IN (Select ArtistID

                                                From Titles

                                                Where Titles.title like "%jazz%")

            c. Delete records from artists where artist has not recorded any jazz titles.

               Delete from Artists

               Where ArtistID NOT IN (Select ArtistID

                                                From Titles

                                                Where Titles.title like "%jazz%")

           

b) You decide not to delete the members of these artists since they might join other artists in the future. What else should you do in the DB when you delete the artists?

            Then We will run the last two steps of above query.

            a. Delete those records from xrefartistsmembers where artists has not recorded any jazz titles.

            b. Delete records from artists where artist has not recorded any jazz titles.

Please let me know in case of any doubts.

Thanks.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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