Skip to content

Instantly share code, notes, and snippets.

@Thoughtscript
Last active Nov 27, 2020
Embed
What would you like to do?
MSSQL Notes

MS SQL Notes

Just a few notes.

Caveats

Some of the syntax here may not be valid for older versions of MSSQL.

Resources

/**
* Create table after check.
*/
DROP TABLE IF EXISTS example;
CREATE TABLE example (
id INT(11) NOT NULL AUTO_INCREMENT,
text VARCHAR(45) NOT NULL,
UNIQUE KEY uni_example_id (id),
PRIMARY KEY (id)
);
/**
* Modify the table.
*/
ALTER TABLE example ADD more_text VARCHAR(45);
/**
* Insert values into table.
*/
INSERT INTO example VALUES (0, "text", "more text");
INSERT INTO example (id, text, more_text)
VALUES (1, "text", "more text");
INSERT INTO example (id, text, more_text)
VALUES (2, "text", "more text");
INSERT INTO example (id, text, more_text)
VALUES (3, "text", "more text");
/**
* Update a row.
*/
UPDATE example SET id = "new text" WHERE id = 2;
/**
* Select examples.
*/
SELECT TOP 1 * FROM example WHERE id = 0;
SELECT * FROM example AS e HAVING COUNT(e.id) > 0 GROUP BY e.id;
SELECT *, yearly / 12 AS monthly FROM example;
/**
* Temporary tables.
*/
DECLARE temp TABLE (id INT(11), text VARCHAR(45));
SELECT * INTO temp FROM example;
/**
* Create another table for examples after check.
*/
DROP TABLE IF EXISTS employee;
CREATE TABLE employee (
id INT(11) NOT NULL AUTO_INCREMENT,
earnings DECIMAL NOT NULL,
months INT NOT NULL,
salary DECIMAL NOT NULL,
UNIQUE KEY uni_example_id (id),
PRIMARY KEY (id)
);
/**
* WITH example.
*/
WITH x AS (
SELECT months*salary AS earnings, id
FROM employee
)
SELECT TOP 1 earnings, Count(id)
FROM x
GROUP BY earnings
ORDER BY earnings DESC
/** Perform two stages of computations then combine */
WITH e AS (
SELECT REPLACE(`salary`, "0", "") AS miscalculated FROM employee
)
SELECT CEILING(AVG(emp.salary) - AVG(e.miscalculated))
FROM e, employee as emp
/**
* Nested queries.
*/
SELECT *
FROM example
WHERE id =
(SELECT id
FROM example
WHERE text = "text");
/** Return product with highest total sales from table with name, prices, quantities */
SELECT name FROM Products WHERE name =
(SELECT name
FROM Products
ORDER BY price * quantity DESC, name ASC LIMIT 1);
/** Return 4th through 8th place on unsorted table */
SELECT name
FROM (SELECT *,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM leaderboard) t
WHERE row_num > 3 AND row_num < 9;
/**
* Join example.
*/
SELECT * FROM example AS e
INNER JOIN employee AS em ON e.id = em.id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment