---
# System prepended metadata

title: Computer.Database.SQLServer

---

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

## Install
### SQL Server 2019 Developer [^link^](https://www.microsoft.com/en-us/sql-server/sql-server-downloads#)
-  ![](https://i.imgur.com/Lv14Zjc.png)
- 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^]()