Basic DB Using SQL Server (Part 1) - Basic commands!

rinsama77 profile image rinzzzz Updated on ・3 min read

Hello! This post will be about "Basic commands" just like what the topic said. We will cover 2 types of SQL commands; DDL commands and DML commands. Let's see what these 2 mean first...

DDL, or Data Definition Language, is used to create and/or modify the structure of a database and database objects1. DDL commands include CREATE, ALTER, RENAME, DROP, and TRUNCATE.

DML, or Data Manipulation Language, is used to manipulate data in the server 2. DML commands include INSERT, DELETE, UPDATE, LOCK, and MERGE.

For more information, click here.

1) DDL Commands

Now let's look at some DDL commands that we commonly use.

-- Create a database user account --
CREATE LOGIN login_name
WITH PASSWORD = 'type_password_here';
GO  -- this is optional. If the above command doesn't work, the program won't stop and keep GOing --

-- Create user and add server role --
CREATE USER user_name FOR LOGIN login_name
EXEC sp_addsrvrolemember 'login_name', 'role'

When we successfully enter into the server, we don't want to use sa, or system administrator, user account. Thus, we will create a new account into the server using the above commands. The role can be sysadmin.

Create a new database


According to this site, collation is a set of rules that tell database engine how to compare and sort the character data in SQL Server.

What we did above is creating a database and naming it whatever you put in the database_name part while also specifying some set of rules; in this case, THAI_CI_AS.

Delete an existing database

DROP DATABASE database_name

-- optional step --
-- checking to see if database_name exists --
IF EXISTS(SELECT * FROM sys.databases WHERE name='database_name')
DROP DATABASE database_name -- if it exists, drop it --

Sometimes, there's an error saying cannot drop database "database_name" because it is currently in use.. In order to solve this, we will switch to another database using the following command.

USE master  -- every SQL server will have one --

2) DML Commands

After creating a database (or two), we can now create tables inside!

Create a new table

CREATE TABLE database_name.schema_name.table_name  -- usually we use dbo schema --
      column_name1 data_type(size),
      column_name2 data_type(size),

CREATE TABLE test_db.dbo.Staff
      StaffId int,
      LastName varchar(255),
      FirstName varchar(255)

Delete an existing table

DROP TABLE database_name.schema_name.table_name 
-- or --
DROP TABLE table_name

Insert a new record into a table

INSERT INTO table_name
VALUES (value1, value2, value3);

--enter multiple records --
INSERT INTO table_name
VALUES (value1, value2, value3),
       (value1, value2, value3),
       (value1, value2, value3),
       (value1, value2, value3);

However, if your table has 4 values to insert, but you would like to insert only 3 values, there are 2 approaches to not cause an error.

-- 1) use null --
INSERT INTO table_name
VALUES(value1, value2, value3, null);

-- 2) specify which column you want to insert into --
INSERT INTO table_name(column1, column2, column3)
VALUES (value1, value2, value3);

Update an existing record

UPDATE table_name
SET column1=value1, column2=value2,...
WHERE condition;

-- for example --
-- updating all records in Staff --
SET Address = 'Bangkok'
-- updating only StaffId = 124 --
SET Address = 'Chiang Mai'
WHERE StaffId = 124;

Delete an existing record

DELETE FROM table_name
WHERE condition;    -- this is optional --

And that's all for this part, see you in the next part!

Posted on by:

rinsama77 profile



Hey ya! Just another girl who codes ;) Still new and learning (hopefully I'll be getting better and learning soon)


Editor guide