Basic MS SQL Commands for Beginners

What is MS SQL?

A database is a place where programs store information in tables. Microsoft SQL Server is a database system made by Microsoft. SQL is the language you use to talk to it. The commands are called statements. The language is also called T-SQL on SQL Server.

You type commands in a tool such as SQL Server Management Studio, or in sqlcmd. Ask Hostvento support whether MS SQL is part of your plan.

Warning: Some commands delete data. Back up first. Test on a copy.

Databases

CREATE DATABASE shop;
USE shop;
DROP DATABASE shop;

The first line makes a database. The second picks it. The third deletes it for good.

Tables

CREATE TABLE customers (
  id INT IDENTITY(1,1) PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(150)
);

This makes a table with three columns. IDENTITY counts up by itself. PRIMARY KEY makes each row unique.

ALTER TABLE customers ADD phone VARCHAR(20);
DROP TABLE customers;

The first line adds a column. The second deletes the table.

Working with data

INSERT INTO customers (name, email) VALUES ('Asha', 'asha@example.com');

Adds one row.

SELECT * FROM customers;
SELECT name FROM customers WHERE id = 1;
SELECT TOP 10 * FROM customers ORDER BY name;

These read data. The star means all columns. WHERE filters rows. TOP 10 returns the first ten. ORDER BY sorts them.

UPDATE customers SET email = 'new@example.com' WHERE id = 1;
DELETE FROM customers WHERE id = 1;

Change or remove rows. Always include WHERE. Without it, every row changes or goes.

Joining tables

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

This combines two tables by matching ids.

Counting and grouping

SELECT COUNT(*) FROM customers;
SELECT country, COUNT(*) FROM customers GROUP BY country;

The first counts all rows. The second counts rows for each country.

Backup and users

BACKUP DATABASE shop TO DISK = 'C:\backup\shop.bak';

Saves a copy of the database to a file.

CREATE LOGIN appuser WITH PASSWORD = 'Use-A-Strong-One-1';
GRANT SELECT ON customers TO appuser;

Makes a login and lets it read one table. Use your own path and password. In practice, you also add a user to the database before granting rights.

Quick recap

• SQL is the language used to talk to a database.
• SELECT reads, INSERT adds, UPDATE changes, DELETE removes.
• Always use WHERE with UPDATE and DELETE.
• JOIN combines tables.
• Back up before risky commands.