---
###### tags: Computer , Database
---
# Computer.Database.SQLServer

## Install
### SQL Server 2019 Developer [^link^](https://www.microsoft.com/en-us/sql-server/sql-server-downloads#)
- 
- Microsoft SQL Server 2019 Developer Edition Installation [^link^](https://www.youtube.com/watch?v=1H1nBxmRSok)
- SQL Server Management Studio (SSMS)[^Link^](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?redirectedfrom=MSDN&view=sql-server-ver15)

## Songs Db
### Reference
- Schema for music lists + playlists web app [^link^](https://stackoverflow.com/questions/20921266/schema-for-music-lists-playlists-web-app)
- MusicBrainz Database [^link^](https://musicbrainz.org/doc/MusicBrainz_Database)
- MusicBrainz Schema [^link^](https://musicbrainz.org/doc/MusicBrainz_Database/Schema)

### Core data

- [^link^]()

## SQL Statement
### Join
- two table join

Select s.name, a.name  from Song s
left join Song_Artist sa on s.id=sa.song_id
join Artist a on sa.artist_id=a.id

### Select
- string_agg

Select s.*, artist_names from Song s
left join (
  Select song_id,string_agg(name,';') artist_names from Song_Artist sa
  left join Artist a on sa.artist_id=a.id
  group by song_id
) aa on s.id=aa.song_id

### Where
- SubQuery

-- SubQuery all male artist's song
Select s.*, artist_names from Song s
left join (
  Select song_id,string_agg(name,';') artist_names from Song_Artist sa
  left join Artist a on sa.artist_id=a.id
  group by song_id
) aa on s.id=aa.song_id
where song_id in (
	select song_id from Song_Artist sa
    left join Artist a on sa.artist_id=a.id 
	where a.sex='M')

###### tags:
{%hackmd BJrTq20hE %}

- [^link^]()
- [^link^]()
- [^link^]()
- [^link^]()
- [^link^]()
- [^link^]()
- [^link^]()