[MSSQL] Baseball

05 Feb 2021

Reading time ~15 minutes

Reference by [C#과 유니티로 만드는 MMORPG 게임 개발 시리즈]Part5: 데이터베이스

Restore Database

bak
- Database Backup file
SQlskills
- Download Baseball
Restore database
- Right click database folder and click [Restore Database]
- Add your file and click [OK] button

MSSQL Baseball

Table

Create new table in GUI
- Right click table folder and click [new]-[table]
- Input your data
- Input table name, for example, dbo.~

MSSQL Baseball

Change Design in GUI
- Right click your table and click [design]
- You can edit your table design in GUI

MSSQL Baseball

Change Row in GUI
- Right click your table and click [edit rows in top 200]
- You can edit your table row in GUI

MSSQL Baseball

Select rows
- Right click your table and click [select rows in top 1000]
- You can read your rows in top 1000

MSSQL Baseball

SELECT FROM WHERE

SELECT
- define column what you want to see in table
FROM
- define table you want to read
WHERE
- define condition
- = is equal
- AND is && and OR is ||
- % means every length word, and _ is only one character
- IS NULL means this data can be null, and IS NOT NULL is vice versa.
  Don’t use = when you want to null check, it’s always null!
Compile order
- FROM → WHERE → SELECT

SELECT *
  FROM players
 WHERE deathYear IS NULL;

MSSQL Baseball

SELECT nameFirst, nameLast, birthYear, birthCountry
  FROM players
 WHERE birthYear = 1974 AND birthCountry != 'USA' AND weight > 185;

MSSQL Baseball

 SELECT *
   FROM players
  WHERE birthCity LIKE 'New%';

MSSQL Baseball

 SELECT *
   FROM players
  WHERE birthCity LIKE 'New Yor_';

MSSQL Baseball

ORDER BY

ORDER BY
- Sort the table with specified condition
TOP
- defines how many rows to read
DESC and ASC
- DESC: sort data by descending
- ASC: sort data by ascending
PERCENT
- is used with TOP
- defines how many rows with calculating percent to read
OFFSET # ROWS FETCH NEXT # ROWS ONLY
- OFFSET defines how many rows to read
- FETCH NEXT means read rows after #th rows

SELECT TOP 10 *
FROM players
WHERE birthYear IS NOT NULL
ORDER BY birthYear DESC, birthMonth DESC, birthDay DESC;

MSSQL Baseball

SELECT TOP 10 PERCENT *
FROM players
WHERE birthYear IS NOT NULL
ORDER BY birthYear DESC, birthMonth DESC, birthDay DESC;

MSSQL Baseball

SELECT *
FROM players
WHERE birthYear IS NOT NULL
ORDER BY birthYear DESC, birthMonth DESC, birthDay DESC
OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

MSSQL Baseball

Calculate and String

Calculate
You can use arithmetic operations, for example, +, -, / and *
ROUND is to round value
You can also use Triangle function, for example, COS, SIN and TAN

SELECT 2021 - birthYear As KoreanAge
FROM players
WHERE deathYear IS NULL AND birthYear IS NOT NULL AND 2021 - birthYear <= 80
ORDER BY KoreanAge;

MSSQL Baseball

SELECT ROUND(3.141592, 3);

MSSQL Baseball

SELECT COS(0);

MSSQL Baseball

String
- To read string that each character is 2-Byte, you should input N in front of the string
- SUBSTRING slices the string, but start point is 1
- TRIM erases blank in the string
- You can link several strings by +
SELECT # AS
- define column name by context after the AS

SELECT N'안녕하세요';

MSSQL Baseball

SELECT SUBSTRING('20200425', 1, 4);

MSSQL Baseball

SELECT TRIM('          HelloWorld');

MSSQL Baseball

SELECT nameFirst + ' ' + nameLast AS fullName
From players
WHERE nameFirst IS NOT NULL AND nameLast IS NOT NULL;

MSSQL Baseball

DATETIME

Create new table in GUI
- Save table name DateTimeTest
INSERT in GUI
- Right click the table and click [Table Scripting]-[INSERT]-[New Query editor window]

MSSQL Baseball

Date Syntax
- YYYYMMDD
- YYYYMMDD hh:mm
- YYYYMMDD hh:mm:ss
- YYYYMMDD hh:mm:ss.nnn
GETUTCDATE
- Get time by UCT Criteria
CAST(# AS DATETIME)
- You can change type of string to DATETIME
DATEADD
- Adds date between DATETIME values
DATEDIFF
- Sub date between DATETIME values
DATEPART
- Extract value by type in DATETIME value
- You can just write type what you want to extract

SELECT GETUTCDATE();

MSSQL Baseball

SELECT *
FROM DateTimeTest
WHERE time >= CAST('20100101' AS DATETIME);

MSSQL Baseball

SELECT DATEADD(YEAR, 1, '20200426');

MSSQL Baseball

SELECT DATEADD(DAY, 5, '20200426');

MSSQL Baseball

SELECT DATEADD(SECOND, -121323, '20200426');

MSSQL Baseball

SELECT DATEDIFF(DAY, '20200826', '20200503');

MSSQL Baseball

SELECT DATEPART(DAY, '20200826');

MSSQL Baseball

SELECT YEAR('20200826');

MSSQL Baseball

CASE

CASE WHEN ELSE END
- smillar with switch
- WHEN defines condition and THEN defines result
- ELSE specifies result when there is not match condition with this value

SELECT
	CASE birthMonth
		WHEN 1 THEN 'Winter'
		WHEN 2 THEN 'Winter'
		WHEN 3 THEN 'Spring'
		WHEN 4 THEN 'Spring'
		WHEN 5 THEN 'Spring'
		WHEN 6 THEN 'Summer'
		WHEN 7 THEN 'Summer'
		WHEN 8 THEN 'Summer'
		WHEN 9 THEN 'Autumn'
		WHEN 10 THEN 'Autumn'
		WHEN 11 THEN 'Autumn'
		WHEN 12 THEN 'Winter'
		ELSE 'NONE'
	END AS birthSeason
FROM players;

MSSQL Baseball

SELECT
	CASE
		WHEN birthMonth <= 2 THEN 'Witer'
		WHEN birthMonth <= 5 THEN 'Spring'
		WHEN birthMonth <= 8 THEN 'Summer'
		WHEN birthMonth <= 11 THEN 'Autumn'
		ELSE 'NONE'
	END AS birthSeason
FROM players;

MSSQL Baseball

Aggregation function

SELECT COUNT(*)
FROM players;

MSSQL Baseball

SELECT COUNT(birthYear)
FROM players;

MSSQL Baseball

SELECT DISTINCT birthCity
FROM players;

MSSQL Baseball

SELECT DISTINCT birthYear, birthMonth, birthDay
FROM players
ORDER BY birthYear;

MSSQL Baseball

SELECT COUNT(DISTINCT(birthCity))
FROM players;

MSSQL Baseball

SELECT AVG(weight)
FROM players;

MSSQL Baseball

SELECT SUM(weight) / COUNT(weight)
FROM players;

MSSQL Baseball

SELECT AVG(CASE WHEN weight IS NULL THEN 0 ELSE weight END)
FROM players;

MSSQL Baseball

SELECT MIN(weight), MAX(weight)
FROM players;

MSSQL Baseball

GROUP BY

GROUP BY
- makes group by specified column
- You can make group by several columns
HAVING
- define condition in GROUP BY

SELECT teamID, COUNT(teamID) AS playerCount, SUM(HR) AS homeRuns
FROM batting
WHERE yearID=2004
GROUP BY teamID;

MSSQL Baseball

SELECT TOP 1 teamID, SUM(HR) AS homeRuns
FROM batting
WHERE yearID=2004
GROUP BY teamID;

MSSQL Baseball

SELECT teamID, SUM(HR) AS homeRuns
FROM batting
WHERE yearID=2004
GROUP BY teamID
HAVING SUM(HR) >= 200
ORDER BY homeRuns DESC;

MSSQL Baseball

SELECT DISTINCT(teamID), yearID, SUM(HR) AS homeRuns
FROM batting
GROUP By teamID, yearID
ORDER BY homeRuns DESC;

MSSQL Baseball

INSERT

INSERT
- Adds row in table
- in VALUE, You should put data that is not null
- if data can be null, you don’t need to fill the data

INSERT INTO salaries
VALUES (2020, 'KOR', 'NL', 'Hanna', 99999);

MSSQL Baseball

INSERT INTO salaries(yearID, teamID, playerID, lgID, salary)
VALUES (2020, 'KOR', 'Hanna2', 'NL', 88888);

MSSQL Baseball

INSERT INTO salaries(yearID, teamID, playerID, lgID)
VALUES (2020, 'KOR', 'Hanna2', 'NL');

MSSQL Baseball

DELETE

DELETE
- deletes specified row

DELETE FROM salaries
WHERE playerID='Hanna2'

MSSQL Baseball

DELETE FROM salaries
WHERE yearID >= 2020;

MSSQL Baseball

UPDATE

UPDATE
- changes specified data in SET
- SET defines how to change the data

UPDATE salaries
SET salary = salary * 2, yearID = yearID + 1
WHERE teamID='KOR';

MSSQL Baseball

SUBQUERY

Double SELECT statement
- Select query from the select

SELECT playerID
FROM players
WHERE playerID = (SELECT TOP 1 playerID FROM salaries ORDER BY salary DESC);

MSSQL Baseball

SELECT playerID
FROM players
WHERE playerID IN (SELECT TOP 20 playerID FROM salaries ORDER BY salary DESC);

MSSQL Baseball

SELECT(SELECT COUNT(*) FROM players) AS playerCount, (SELECT COUNT(*) FROM batting) AS battingCount;

MSSQL Baseball

You can make input value from SELECT statement

INSERT INTO salaries
VALUES(2020, 'KOR', 'NL', 'Hanna', (SELECT MAX(salary) FROM salaries));

MSSQL Baseball

You can use SELECT instead of VALUES

INSERT INTO salaries
SELECT 2020, 'KOR', 'NL', 'Hanna2', (SELECT MAX(salary) FROM salaries);

MSSQL Baseball

IN
- substitutes the result value of the subquery to the main query and output the result after the condition comparison.(Sub query → MAIN query)

SELECT playerID
FROM players
WHERE playerID IN(SELECT playerID FROM battingpost);

MSSQL Baseball

EXISTS
- The results of the main query are substituted to the subquery to output the results after the condition comparison(MAIN query → Sub query)

SELECT playerID
FROM players
WHERE EXISTS (SELECT playerID FROM battingpost WHERE players.playerID = battingpost.playerID);

MSSQL Baseball

Make new table with INSERT INTO
- seems like copy context and pate to the table

INSERT INTO salaries_temp
SELECT yearID, playerID, salary FROM salaries;

MSSQL Baseball

UNION

UNION
- merges two select
- Duplicate values are shown only once
- result columns are always same in SELECT statements

SELECT playerId
FROM salaries
GROUP BY playerId
HAVING AVG(salary) >= 3000000
UNION
SELECT playerID
FROM players
WHERE birthMonth = 12;

MSSQL Baseball

UNION ALL
- selects dulicate values

SELECT playerId
FROM salaries
GROUP BY playerId
HAVING AVG(salary) >= 3000000
UNION ALL
SELECT playerId
FROM players
WHERE birthMonth = 12
ORDER BY playerId;

MSSQL Baseball

INTERSECT
- shows intersect results of two SELECT statements

SELECT playerId
FROM salaries
GROUP BY playerId
HAVING AVG(salary) >= 3000000
INTERSECT
SELECT playerId
FROM players
WHERE birthMonth = 12
ORDER BY playerId;

MSSQL Baseball

EXCEPT
- shows the result of subtracting the second SELECT statement from the first SELECT statement

SELECT playerId
FROM salaries
GROUP BY playerId
HAVING AVG(salary)>=3000000
EXCEPT
SELECT playerId
FROM players
WHERE birthMonth=12
ORDER BY playerId;

MSSQL Baseball

JOIN

ON
- specifies the condition of JOIN
INNER JOIN
- Combine two tables horizontally

SELECT p.playerID, s.salary
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID;

MSSQL Baseball

OUTER JOIN
- Combine data even if it exists on either side
- LEFT JOIN
- RIGHT JOIN
LEFT JOIN
- If there is information on the left, show it no matter what
- If there is no information on the right, the right information is NULL

SELECT p.playerID, s.salary
FROM players AS p
	LEFT JOIN salaries AS s
	ON p.playerID = s.playerID;

MSSQL Baseball

RIGHT JOIN
- If there is information on the right, show it no matter what
- If there is no information on the left, the left information is NULL

SELECT p.playerID, s.salary
FROM players AS p
	RIGHT JOIN salaries AS s
	ON p.playerID = s.playerID;

MSSQL Baseball

DECLARE

DECLARE
- declares variable

DECLARE @i AS INT = 10;

MSSQL Baseball

DECLARE TABLE
- declares table

DECLARE @test TABLE
(
	name VARCHAR(50) NOT NULL,
	salary INT NOT NULL
);

INSERT INTO @test
SELECT p.nameFirst + ' ' + p.nameLast, s.salary
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID;

MSSQL Baseball

SET
- input value in variable

DECLARE @j AS INT;
SET @j = 10;

MSSQL Baseball

You can set value from SELECT

DECLARE @firstName AS NVARCHAR(15);

SET @firstName=(SELECT TOP 1 nameFirst
				        FROM players AS p
					        INNER JOIN salaries AS s
					        ON p.playerID = s.playerID
				        ORDER BY s.salary DESC);

MSSQL Baseball

DECLARE @firstName AS NVARCHAR(15);
DECLARE @lastName AS NVARCHAR(15);

SELECT TOP 1 @firstName = p.nameFirst, @lastName = p.nameLast
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID
ORDER BY s.salary DESC;

MSSQL Baseball

GO

GO
- Set Valid Range for Variables
- A set of commands that are analyzed and executed in a single batch

MSSQL Baseball

IF-ELSE

IF-ELSE
- same with if-else statement in C#
PRINT
- prints content on console

IF @i = 10
	PRINT('BINGO');
ELSE
	PRINT('NO');

MSSQL Baseball

BEGIN-END
- You can several process in statement

IF @i=10
BEGIN
	PRINT('BINGO');
	PRINT('BINGO');
END
ELSE
BEGIN
	PRINT('NO');
END

MSSQL Baseball

WHILE

WHILE
- same with while statement in C#
BREAK
- same with break statement in C#
CONTINUE
- same with continue statement in C#

DECLARE @i AS INT = 0;
WHILE @i <=10
BEGIN
	SET @i = @i + 1;
	IF @i = 9 BREAK;
	IF @i = 6 CONTINUE;
	PRINT @i
END

MSSQL Baseball

WINDOW FUNCTION

WINDOW FUNCTION
- always use with OVER
ROW_NUMBER
- shows number of row
RANK
- shows the ranking
DENSE_RANK
- shows rankings including ties
NTITLE
- show the percentage

SELECT *,
	ROW_NUMBER() OVER (ORDER BY salary DESC),
	RANK() OVER (ORDER BY salary DESC),
	DENSE_RANK() OVER (ORDER BY salary DESC),
	NTILE(100) OVER (ORDER BY salary DESC)
FROM salaries;

MSSQL Baseball

PARTITION
- divide by reference value and process

SELECT *,
	RANK() OVER(PARTITION BY playerID ORDER BY salary DESC)
FROM salaries
ORDER BY playerID;

MSSQL Baseball

LAG
- show previous value
LEAD
- show next value

SELECT *,
	RANK() OVER(PARTITION BY playerID ORDER BY salary DESC),
	LAG(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS prevSalary,
	LEAD(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS nextSAlary
FROM salaries
ORDER BY playerID;

MSSQL Baseball

FIRST_VALUE
- show the biggest value between previous values and current value
LAST_VALUE
- show the smalliest value between previous values and current value

SELECT *,
	RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
	FIRST_VALUE(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS best,
	LAST_VALUE(salary) OVER (PARTITION BY playerID ORDER BY salary DESC) AS worst
FROM salaries
ORDER BY playerID;

MSSQL Baseball

ROWS BETWEEN AND
- specifies the range of rows to compare
- UNBOUNDED PRECEDING: from all previous row in partition
- CURRENT ROW: from/to current row
- UNBOUNDED FOLLOWING: to all previous row in partition

SELECT *,
	RANK() OVER (PARTITION BY playerID ORDER BY salary DESC),
	FIRST_VALUE(salary) OVER (PARTITION BY playerID 
								ORDER BY salary DESC
								ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
								) AS best,
	LAST_VALUE(salary) OVER (PARTITION BY playerID 
								ORDER BY salary DESC
								ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
								) AS worst
FROM salaries
ORDER BY playerID;

MSSQL Baseball

JOIN

Merge

Sort Merge
- Merge Join has condition
- outer has to be unique
- One-to-Many(PK, unique)
- Random Access X → Clustered Scan and sorting

SELECT *
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID;

MSSQL Baseball

Merge Join in C#

  using System;
  using System.Collections.Generic;
  using System.Diagnostics.CodeAnalysis;

  namespace MergeJoin
  {
    class Player : IComparable<Player>
    {
      public int playerId;

      public int CompareTo(Player other)
      {
        if (playerId == other.playerId)
          return 0;
        return (playerId > other.playerId) ? 1 : -1;
      }
    }

    class Salary : IComparable<Salary>
    {
      public int playerId;

      public int CompareTo(Salary other)
      {
        if (playerId == other.playerId)
          return 0;
        return (playerId > other.playerId) ? 1 : -1;
      }
    }

    class Program
    {
      static void Main(string[] args)
      {
        List<Player> players = new List<Player>();
        players.Add(new Player() { playerId = 0 });
        players.Add(new Player() { playerId = 9 });
        players.Add(new Player() { playerId = 1 });
        players.Add(new Player() { playerId = 3 });
        players.Add(new Player() { playerId = 4 });

        List<Salary> salaries = new List<Salary>();
        salaries.Add(new Salary() { playerId = 0 });
        salaries.Add(new Salary() { playerId = 5 });
        salaries.Add(new Salary() { playerId = 0 });
        salaries.Add(new Salary() { playerId = 2 });
        salaries.Add(new Salary() { playerId = 9 });

        // Step 1) Sort (if already sorted, SKIP)
        // O(N * Log(N))
        players.Sort();
        salaries.Sort();

        // One-To-Many(players is unique)
        // Step 2) Merge
        // outer [0,1,3,4,9]
        // inner [0,0,2,5,9]

        int p = 0;
        int s = 0;

        List<int> result = new List<int>();

        // O(N+M)
        while (p<players.Count && s<salaries.Count)
        {
          if(players[p].playerId == salaries[s].playerId)
          {
            result.Add(players[p].playerId);
            s++;
          }
          else if(players[p].playerId < salaries[s].playerId)
          {
            p++;
          }
          else
          {
            s++;
          }
        }

        // Many-To-Many(Player is not unique)
        // outer [0,0,0,0,0] -> N
        // inner [0,0,0,0,0] -> N
        // O(N+M)
      }
    }
  }

Case: Outer is clustered
- is the best way to Merge Join

SELECT *
FROM schools AS s
	INNER JOIN schoolsplayers AS p
	ON s.schoolID = p.schoolID;

MSSQL Baseball

Nested Loop

Nested Loop
- access outer table which is accessed first, and then access randomly in inner table
- if there is not index in inner table, time complexity O(N^2) is really heavy
- Nested Loop is great for ranging process

SELECT TOP 5 *
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID;

MSSQL Baseball

Nested Loop in C#

  using System;
  using System.Collections.Generic;

  namespace NestedLoop
  {
    class Player
    {
      public int playerId;
    }

    class Salary
    {
      public int playerId;
    }

    class Program
    {
      static void Main(string[] args)
      {
        Random rand = new Random();

        // N
        List<Player> players = new List<Player>();
        for(int i=0; i<1000; i++)
        {
          if (rand.Next(0, 2) == 0)
            continue;

          players.Add(new Player() { playerId = i });
        }

        /* N
        List<Salary> salaries = new List<Salary>();
        for (int i = 0; i < 1000; i++)
        {
          if (rand.Next(0, 2) == 0)
            continue;

          salaries.Add(new Salary() { playerId = i });
        }
        ////////////////////////////////////////////////*/
        
        Dictionary<int, Salary> salaries = new Dictionary<int, Salary>();
        for (int i = 0; i < 1000; i++)
        {
          if (rand.Next(0, 2) == 0)
            continue;

          salaries.Add(i, new Salary() { playerId = i });
        }

        // Nested Loop
        List<int> result = new List<int>();
        foreach (Player p in players)
        {
          /* foreach(Salary s in salaries)
          {
            if(s.playerId == p.playerId)
            {
              result.Add(p.playerId);
              break;
            }
          } 
          ////////////////////////////////////////////////*/

          Salary s = null;
          if (salaries.TryGetValue(p.playerId, out s))
          {
            result.Add(p.playerId);
            if (result.Count == 5)
              break;
          }              
        }
      }
    }
  }

Forcing to use Nested Loop
- use OPTION(LOOP JOIN) to force using Nested Loop

SELECT *
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID
	OPTION(LOOP JOIN);

MSSQL Baseball

use OPTION(FORCE ORDER) to force ordering

SELECT *
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID
	OPTION(FORCE ORDER, LOOP JOIN);

MSSQL Baseball

Hash

SELECT *
FROM salaries AS s
	INNER JOIN teams AS t
	ON s.teamID = t.teamID;

MSSQL Baseball

Sorting

Decrease Sorting

Reason
- O(NLogN) → DB has too many data
- if size is to big to cover useable memory, then database search disk also
- So, you need to know when the sorting is excuteing
SORT MERGE JOIN
- in algorithm, before to Merge, Sort is caused
ORDER BY
- the database have to Sort by ORDER BY
- if you can use INDEX exactly, you don’t habe to sort probably

SELECT *
FROM players
ORDER BY college;

MSSQL Baseball

SELECT *
FROM batting
ORDER BY playerID;

MSSQL Baseball

GROUP BY
- to sum up
- if you can use INDEX exactly, you don’t habe to sort probably

SELECT college, COUNT(college)
FROM players
WHERE college LIKE 'C%'
GROUP BY college;

MSSQL Baseball

SELECT playerID, COUNT(playerID)
FROM players
WHERE playerID LIKE 'C%'
GROUP BY playerID;

MSSQL Baseball

DISTINCT
- to remove duplication

SELECT DISTINCT college
FROM players
WHERE college LIKE 'C%';

MSSQL Baseball

UNION
- to remove duplication
- if you can use UNION ALL exactly, you don’t habe to sort probably

SELECT college
FROM players
WHERE college LIKE 'B%'
UNION
SELECT college
FROM players
WHERE college LIKE 'C%';

MSSQL Baseball

SELECT college
FROM players
WHERE college LIKE 'B%'
UNION ALL
SELECT college
FROM players
WHERE college LIKE 'C%';

MSSQL Baseball

RANKING WINDOWS FUNCTION
- to sum up

SELECT ROW_NUMBER() OVER (ORDER BY college)
FROM players;

MSSQL Baseball

SELECT ROW_NUMBER() OVER (ORDER BY playerID)
FROM players;

MSSQL Baseball

MIN MAX
- to sum up

Download