SQL
Create a database called MyMusician
Create three tables, one named “User”, one named “Song”, and one named “Playlist”.
The “User” table should have a column for userID (integer, autoincrement, not null, primary key), username, and password. It should also have one column for a foreign key we will assign later called “playlistID” (integer).
The “Playlist” table should have a “playlistID” (integer, autoincrement, not null, primary key), and a column for a foreign key we will assign later called “songID” (integer).
The “Song table should have a “songID” (integer, autoincrement, not null, primary key), and a column for name, artist, album, year, duration, and genre.
Add an additional column to Playlist named “name”, which will contain the playlist name. Then add another table called “UserPL” that contains a userID, and a “name”. This is how we connect multip[le users with multiuple playlists.
Create a database called MyMusician
create database MyMusician;
Create three tables, one named "User", one named "Song", and one named "Playlist" within MyMusician
Create table User:
create table User(usedID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, username varchar(100), password varchar(100), playlistID INT);
Create table Playlist:
create table Playlist( playlistID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, songID INT);
Create table Song:
create table Song(songID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name varchar(100), artist varchar(100), album varchar(100), year INT, duration DECIMAL(8,2), genre varchar(20));
Add an additional column to Playlist named "name", which will contain the playlist name.
Alter table Playlist add name varchar(100) NOT NULL;
Create table UserPL:
create table UserPL(userID INT NOT NULL , name varchar(100) NOT NULL, constraint fk_user_userPL FOREIGN KEY(userID) references User(userID), PRIMARY KEY(userID, name));
Add foreign key constraint to User table:
Alter table User fk_user_playlist FOREIGN KEY(playlistID) references Playlist(playlistID);
Add foreign key constraint to Playlist:
Alter table Playlist fk_playlist_song FOREIGN KEY(songID) references Song(songID);
SQL Create a database called MyMusician Create three tables, one named “User”, one named “Song”, and...
a. Write five select statements to select data from each table individually. b. Write one select statement with joins to list the playlist name, playlist track number, song name, artist name, album name, sorted by playlist name and playlist track number. DROP TABLE Artists CASCADE CONSTRAINTS; DROP TABLE Albums CASCADE CONSTRAINTS; DROP TABLE Songs CASCADE CONSTRAINTS; DROP TABLE Playlists CASCADE CONSTRAINTS; DROP TABLE PlaylistSongs CASCADE CONSTRAINTS; CREATE TABLE Artists ( ArtistID int NOT NULL, ArtistName varchar(255), PRIMARY KEY (ArtistID) );...
Define an SQL view JokesNum that gives the number of jokes each user posts on each day. Database: create table User( userid varchar(30) not null, password varchar(30), firstname varchar(30), lastname varchar(50), email varchar(50), gender char(1), age integer, banned boolean, primary key(userid), unique(email)); create table MyFriends( userid varchar(30), friendid varchar(30), primary key(userid,friendid), foreign key(userid) references User, foreign key(friendid) references User(userid)); Create table Jokes( Jokeid integer, title varchar(100), description varchar(255), authorid varchar(30) not null, primary key(jokeid), posttime Datetime, foreign key(authorid) references User(userid));...
4. Consider the following schema for an online streaming service, where users are allowed to play (stream) songs performed by different artists. Primary and foreign key constraints are also listed for the schema of each table. User (ID, Password, Name, Location) Primary key = ID Artist (ID, Name, Birthyear) Primary key = ID Song (ID, Title, Album, ArtistID) Primary key = ID Song(ArtistID) references Artist(ID) Play (ID, UserID, SongID, Timestamp) Primary key = ID Play(UserID) references User(ID) Play(SongID) references Song(ID)...
Based on the CREATE TABLE
statements, make an ER model of the database. Give suitable names
to the relationships. (Remember cardinality and participation
constraints.) The diagram must use either the notation used in the
textbook (and the lectures) or the crow’s foot notation. To save
you some time: There are a few tables that include the following
address fields: Address, City, State, Country and PostalCode (and
the same fields with the prefix Billing-). You are allowed to
replace these attributes...
Given the mySQL tables created below... Create a mySQL solution to return the itemIDs of items posted by user X, such that all the reviews are “Excellent” or “Good” for these items CREATE TABLE Users ( userId varchar (30) NOT NULL, pass varchar (30), fname varchar (50), lname varchar (50), email varchar (50), gender char(1), age integer, banned boolean, PRIMARY KEY (userId), UNIQUE(email)) CREATE TABLE FavSellers ( userId...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
database and sql problme
THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...
Use an INSERT INTO statement with a subquery containing a SQL string function to create user names for the volunteers based on their legal names and insert them into the table. +----------------------+ | Tables_in_volunteers | +----------------------+ | address | | email | | funds | | hours | | person | | phone | | users | +----------------------+ users legal names are in the address table user table is : CREATE TABLE USERS(volunteer_id INT NOT NULL, username VARCHAR(50), PRIMARY KEY...
Utilize the JigSaw SQL file below to create a Star Schema diagram. Remember, to create a Star Schema from a normalized data model, you will need to denormalize the data model into fact and dimension tables. The diagram should contain all of the facts and dimension tables necessary to integrate the JigSaw operational database into a data warehouse. Write a brief paper describing the challenges you experienced in completing this assignment. -- CREATE DATABASE js; CREATE TABLE buy_methods ( buy_code...
Create a new MySQL database called "players". Create a user for your database named "Jane". Jane is a new employee who, in your challenge, will create a new table called "phillies". This table will hold 1 record only. This record is a description of Jane's favorite sports team, the Philadelphia Phillies. She needs to input the following data for her favorite player, Cole Hamels: Be sure to consider which data types you should use for each data point that needs...