Create the database schema called “music_albums” which allows users to find all

ID: 3707900 • Letter: C

Question

Create the database schema called “music_albums” which allows users to find all the music `albums on a set of user preferences. These user preferences include the album name, artist name, main track name, release date and its producer. Design a schema for the database that would store this information. Use just one table.

Write the MySQL queries to perform the following:

List the albums based on the release date

Add an extra column and name it as “genre”

List the albums released after the “particular” date.

Show the data after changing the genre.

List only the main track names.

List the total no. of albums in the database.

Display the list showing the first and the last column.

List the album in the descending order.

Insert 5 tuples.

Show all the records.

Explanation / Answer

Solution:

Note: First for requirements have been completed as per Chegg guidelines, please repost others.

The table will be created from the query:

CREATE TABLE music_albums(
albumName varchar(20),

artistName varchar(40)

mainTrackName varchar(40),

releaseDate date,

producer varchar(20)
PRIMARY KEY (albumName)

);

List the albums based on the release date:

SELECT albumName FROM music_albums ORDER BY releaseDate

Add an extra column and name it as “genre”

ALTER TABLE music_albums

ADD COLUMN genre varchar(20)

List the albums released after the “particular” date.

SELECT albumName FROM music_albums WHERE releaseDate>15/06/2014

Show the data after changing the genre.

SELECT * FROM music_albums

List only the main track names.

SELECT mainTrackName from music_albums

List the total no. of albums in the database.

SELECT COUNT(albumName) FROM music_albums

Display the list showing the first and the last column.

SELECT albumName, releaseDate FROM music_albums

Explanation:

HEre the first and last columns are albumName and releaseDate

List the album in the descending order.

SELECT albumName FROM music_albums ORDER BY DESC

Insert 5 tuples.

INSERT INTO music_albums