My first post. iTunes database with Microsoft SQL

Greeting everyone.

Thanks for reading my blog. Now you are honorably the reader of my first post.

For this post, i want to share about my tutorial for making the iTunes database in the SQL query language. Which is i have learned in this semester of my university. By the way, I am from Binus International University in South Jakarta, Indonesia. I am majoring at Information System and still on semester 2 (freshmen).  And now back to the topic. 

By the way for the video, sorry for the complicated process to watch it, You must watch the Introduction Part and then the Tutorial Part. I am really sorry because i do not have any time left to edit the video. I have already tried the video editor application but, there are several lack of system. When i wanted to edit the video in the laptop, the part of the screen capture video is blur, And when i wanted to edit it in my handphone, The video can not be loaded at the video editor application. I have found many solution for this problem. But still, i do not get it at all. It is also because of the time that i cannot find another video editor program. So, sorry for the inconvenient at watching the videos.

This is my full query of my iTunes database so you can try it. (for the further explanation is on the videos)

--------------------------------------------------------------------------------------------------------------------------

create database iTunes -- Creating a Database

create table masterSong -- designing a Main song list table in the store

(

songID char(5) primary key not null,

songTitle varchar(100) not null,

songArtist varchar(100) not null,

srecordingCompany varchar(100) not null,

songPrice float not null,

)

create table masterPlaylist -- designing a playlist list table in the store

(

playlistID char(6) primary key not null,

playlistName varchar(100) not null,

playlistArtist varchar(100) not null,

precordingCompany varchar(100) not null,

playlistPrice float not null,

)

create table masterCustomer -- designing a customer list who has registered in iTunes

(

customerID char(7) primary key not null,

customerName varchar(100) not null,

customerAddress varchar(100) not null,

customerZipcode char(5) not null,

creditcardNumber char(16) not null,

)

create table songTransaction -- designing the transactional table for song

(

STID varchar(10) primary key not null,

customerID char(7) references masterCustomer(customerID) not null,

songID char(5) references masterSong(songID) not null,

STDescription varchar(100) not null,

)

create table playlistTransaction -- designing the transactional table for playlist

(

PTID varchar(10) primary key not null,

customerID char(7) references masterCustomer(customerID) not null,

playlistID char(6) references masterPlaylist(playlistID) not null,

PTDesctiption varchar(100) not null,

)

--this is for editing the column

alter table playlistTransaction -- effecting the playlisttransaction table

drop column total -- delete the column named total

add total money

--display for the data that has been input

select * from masterSong

select * from masterPlaylist

select * from masterCustomer

select * from songTransaction

select * from playlistTransaction

--input the value 

insert into masterSong values('00001', 'Dont stop the madness','Hardwell','Revealed Recording',7000.00)

insert into masterSong values('00002','Secrets','Tiesto','Musical Freedom',7000.00)

insert into masterSong values('00003','Blank Space','Taylor Swift','Sony Recording','8000.00')

insert into masterPlaylist values('000001','United We Are','Hardwell','Revealed Recording','34000.00')

insert into masterPlaylist values('000002','Ultra Music Festival 2015 OST','Various Artist','Ultra Recording',70000.00)

insert into masterPlaylist values('000003','1989','Taylor Swift','Sony Recording',80000.00)

insert into masterCustomer values('0000001','Nickolas Tunggara', 'Bukit Gading Villa nomor 20','14250','0123456789765248')

insert into masterCustomer values('0000002','Alexander','Jalan kramat jati 3','12340','0978654738495087')

insert into masterCustomer values('0000003','Andrew','Kelapa Nias23','12453','4354676543347890')

insert into songTransaction values('1','0000001','00001','Success')

insert into playlistTransaction values('1','0000001','000001','success')

--sort with condition

select * from masterCustomer

where customerName like 'a%'

--delete the value with condition

delete from masterCustomer

where customerName = 'Alexander'

--checking the customer

select * from  masterCustomer

--update the table customer

update masterCustomer

set customerName = 'Andrew Natanael'

where customerZipcode = '12453'

--order by

select * from masterCustomer

order by customerName desc

select * from masterCustomer

order by customerAddress 

--union method

select playlistID, playlistName, playlistArtist

from masterPlaylist

union

select songID, songTitle, songArtist

from masterSong

--intersect method

select  playlistArtist

from masterPlaylist

intersect

select  songArtist

from masterSong

-- Joining the table

select * from masterCustomer

select * from masterSong a

join songTransaction b

on a.songid = b.songID

join masterCustomer c

on b.customerID = c.customerID

--------------------------------------------------------------------------------------------------------------------------

Well, This database query and the video is honorably dedicated to my lecturer in the Introduction to Database System subject, Mr. Samuel Mahatmaputera and to my teaching assistant in the Introduction to Database System subject, Ms. Sharon Jesica. Thanks for teaching me about the query language of database. This knowledge is very precious for me. 

That is all about my post. Thank you for reading and also watching to my video. I hope you guys enjoy it and sorry for my mistake in making of these.

Christoffel Yehezkiel Angga (1801441972).

(You can contact me from the comment if you have a question based on the tutorial)

Manual link:
Part 1:  https://youtu.be/XCorcbttNDY .
Part 2: https://youtu.be/jscLONzxurY .

Updated 14th April, At last i have done my full video version of my tutorial. Enjoy all! :)

Manual link:
Full version: https://www.youtube.com/watch?v=uxmOcGSU7Cw