SQL Cheatsheet

Created: Code snippets for common tasks with SQL Server and SQL Express

Updated: 04 October 2023

Mostly applies to SQL Server and SQL Express

Log Into Instance

1
#sqlcmd -S <YOUR DATABASE NAME> -E
2
sqlcmd -S localhost\SQLEXPRESS -E

Instance Level Operations

List Instance Databases

1
SELECT [name] FROM [master].[dbo].[sysdatabases]
2
GO

Create Database

1
CREATE DATABASE [TestDatabase]
2
GO

Drop Database

1
DROP DATABASE TestDatabase
2
GO

Database Level Operations

List Database Tables

1
SELECT [TABLE_NAME]
2
FROM [TestDatabase].[INFORMATION_SCHEMA].[TABLES]
3
WHERE [TABLE_TYPE] = 'BASE TABLE'
4
GO

List Columns in Table

1
SELECT * FROM [TestDatabase].[INFORMATION_SCHEMA].[COLUMNS]
2
 WHERE [TABLE_NAME] = 'Persons'
3
GO

Create Table

1
CREATE TABLE [TestDatabase].[dbo].[Persons] (
2
    PersonId int,
3
    LastName varchar(255),
4
    FirstName varchar(255),
5
    Address varchar(255),
6
    City varchar(255)
7
)
8
GO

Update Column Data Type

1
ALTER Table [TestDatabase].[dbo].[Persons]
2
  ALTER COLUMN [PersonId] int NOT NULL
3
GO

Add Column Constraint

1
ALTER Table [TestDatabase].[dbo].[Persons]
2
  ADD CONSTRAINT PK_Person PRIMARY KEY ([PersonId])
3
GO

Create Table with Links

1
CREATE TABLE [TestDatabase].[dbo].[Items]
2
   (
3
      ItemId int NOT NULL,
4
    PersonId int NOT NULL,
5
    Name nvarchar(50),
6
      CONSTRAINT PK_Items PRIMARY KEY (ItemId),
7
      CONSTRAINT FK_Items_Person FOREIGN KEY ([PersonId])
8
      REFERENCES [Persons] ([PersonId])
9
   )
10
GO

Drop Table

1
DROP TABLE [Persons]
2
GO

Insert Item into Table

1
INSERT INTO [TestDatabase].[dbo].[Persons]
2
    ([PersonId], [LastName], [FirstName], [Address], [City])
3
VALUES (1, 'Name', 'Surname', 'Home', 'Place')
4
GO

Retrieve Table Values

We can retrieve all values from a table with:

1
SELECT TOP (10) [PersonId]
2
      ,[LastName]
3
      ,[FirstName]
4
      ,[Address]
5
      ,[City]
6
  FROM [TestDatabase].[dbo].[Persons]
7
GO

We can get a specific set of values with a condition

1
SELECT *
2
FROM [TestDatabase].[dbo].[Persons]
3
WHERE [FirstName] = 'John'

Or search for a pattern in a field with LIKE:

1
SELECT *
2
FROM [TestDatabase].[dbo].[Persons]
3
WHERE [FirstName] LIKE '%John%'

Update Table Item

1
UPDATE [TestDatabase].[dbo].[Persons]
2
SET [FirstName] = 'John', [LastName] = 'Smith'
3
WHERE [PersonId] = 1
4
GO

Values in List

We can use the IN operator to select some data based on values being in a given list

1
SELECT * FROM users
2
WHERE id in (1,2,3)

Testing Statements

When running SQL queries it may sometimes be necessary to check if your query will work as expected before you actually run it you can wrap your query in:

1
BEGIN TRANSACTION
2
    ... DO STUFF
3
ROLLBACK

ROLLBACK will roll back to the DB status before the query was carried out

And once you have verified that the query did what you expected, you can change the ROLLBACK to COMMIT

1
BEGIN TRANSACTION
2
    ... DO STUFF
3
COMMIT

We can test a deletion of a Person and view the result with:

1
BEGIN TRANSACTION
2

3
SELECT * FROM [TestDatabase].[dbo].[Persons]
4

5
DELETE FROM [TestDatabase].[dbo].[Persons]
6
  WHERE [LastName] = 'Person2'
7

8
SELECT * FROM [TestDatabase].[dbo].[Persons]
9

10
ROLLBACK

And we can then COMMIT this when we are sure it works

1
BEGIN TRANSACTION
2

3
SELECT * FROM [TestDatabase].[dbo].[Persons]
4

5
DELETE FROM [TestDatabase].[dbo].[Persons]
6
  WHERE [LastName] = 'Person2'
7

8
SELECT * FROM [TestDatabase].[dbo].[Persons]
9

10
COMMIT

Table Joining

Inner Join

To use an Inner Join based on two tables we can use the INNER JOIN keywords and then get the fields from the tables we want to use for our output table:

1
SELECT
2
a.FirstName as FirstName,
3
a.Email as Email,
4
a.ID as ID,
5

6
b.Vehicle as Vehicle,
7
b.Registered as IsRegistered
8

9
FROM Table_1 as a
10
INNER JOIN Table_2 as b
11
ON a.ID = b.UserId

Inner Queries

You can use subqueries inside of SQL queries for the purpose of comparing data against without actually returning/selecting the data from the inner query

1
SELECT *
2
FROM users
3
WHERE id IN
4
    (
5
        SELECT user_id
6
        FROM orders
7
        WHERE order_id IN (1,3)
8
    )
9
AND LOWER(username) LIKE LOWER('%bob%')