Basic MSSQL Queries

Create a Database

CREATE DATABASE mytestdatabase;

View All of the Databases

SELECT * FROM sys.databases;

Delete a Database

DROP DATABASE mytestdatabase;

Delete Multiple Databases

DROP DATABASE mytestdatabase1, mytestdatabase2;

Create a Database and Delete One of the Same Name, If It Already Exists

IF DB_ID ('mytestdatabase') IS NOT NULL
DROP DATABASE mytestdatabase;

CREATE DATABASE mytestdatabase;

Create a Table

CREATE TABLE mytable(
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

Insert Rows into a Table

INSERT INTO mytable(id, name) VALUES(1, 'Peter');
INSERT INTO mytable(id, name) VALUES(2, 'Paul');
INSERT INTO mytable(id, name) VALUES(3, 'James');
INSERT INTO mytable(id, name) VALUES(4, 'John');

Select Data from a Table

SELECT * FROM mytable;

Create a Table with Its Values Constrained by Two Other Tables

CREATE TABLE usersRoles (
  userId INTEGER NOT NULL,
  roleId INTEGER NOT NULL,
  FOREIGN KEY(userId) REFERENCES users(id),
  FOREIGN KEY(roleId) REFERENCES roles(id),
  CONSTRAINT upair UNIQUE (userId,roleId)
);