Create and Use a Database with SQL Commands
Hey everyone. In this article we are going to learn how to create and use a database with SQL commands. I am going to do this processes with MSSQL. Let’s start.
First of all, SQL Server and Management Studio must be installed on your computer. After open the SSMS, you will be greeted with a screen like this.
In this part, we connect to SQL Server. We will connect to the SQL Server you downloaded on your own computer. After connecting to SQL Server, we first open a new query from the option in the image below.
After this point, we can write and run our queries on the white page that appears.
Create Database
CREATE DATABASE School;
First, let’s create a database. We will examine our example through the school database.
Create Tables
USE [School]
GO
CREATE TABLE Student(
Id INT PRIMARY KEY IDENTITY (1, 1),
First_Name VARCHAR (50) NOT NULL,
Last_Name VARCHAR (50) NOT NULL,
School_Number INT,
Phone VARCHAR(20)
);
CREATE TABLE Lesson(
Id INT PRIMARY KEY IDENTITY (1, 1),
Lesson_Name VARCHAR(20)
);
CREATE TABLE ResultsOfExam(
Id INT PRIMARY KEY IDENTITY (1, 1),
Student_Id INT NOT NULL,
Lesson_Id INT NOT NULL,
Exam1 INT NOT NULL,
Exam2 INT NOT NULL,
Exam3 INT NOT NULL,
Average INT NOT NULL,
Statu VARCHAR(20)
);
After created the database, we create the tables. At this stage, we use the Use [db.Name] and Go commands to create the tables in the correct database, and we can also choose the correct database from the “Avaible Databases” Combobox in the upper left.
Now that we have created the tables, we can start data entry.
Insert Into Values
USE [School]
GO
INSERT INTO student (First_Name,Last_Name,Phone,School_Number)
VALUES
(‘Ali’,’Yunus’,’+905435411421',212120015),
(‘Mesut’,’Tufan’,’+905318548914',212120019),
(‘Hami’,’Takoz’,’+905179653711',212120011),
(‘Hakan’,’Demirel’,’+905466635443',212120021);
We create the above query by using the INSERT INTO and VALUES commands together to add data to our tables.
Alter table
USE [School]
GO
Alter Table Lesson
add limit smallint
Let’s assume that there is a missing column in our table. In this case, this is the query we will use to add the column.
By the way, we noticed that we wrote the column name wrong. Let’s fix it now.
USE [School]
GO
EXEC SP_RENAME ‘Lesson.limit’ , ‘Limit’, ‘COLUMN’
If we want to delete the Limit column, we use the Drop command.
USE [School]
GO
Alter Table Lesson
Drop Column Limit
Commands
Now that we have created the database and tables, we can come to our commands.
DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.
The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.
We talked about all of the DDL commands except the truncate command. Let’s talk about Truncate and move on to DML commands.
Truncate
Truncate command deletes all data in the table.
USE [School]
GO
Truncate Table Lesson
DML Commands
Select
The SELECT command is used to get the desired row values from one or more tables in a database.
USE [School]
GO
SELECT * FROM Student
If you put * after the select, you call the whole table. From is a keyword we use to specify the table we will use.
If we specify the name of the columns we want instead of *, it will create a result grid according to the columns we want and present it to us.
USE [School]
GO
SELECT First_Name,Last_Name FROM Student
Another use of select is its conditional use.
USE [School]
GO
SELECT * FROM Student where First_Name=’ali’
Insert Into
We already used the Insert Into command above to insert data into the tables when we first created the database. Let’s use Insert Into again to test other commands.
USE [School]
GO
INSERT INTO lesson (Lesson_Name)
VALUES
(‘Math’),
(‘Literature’),
(‘Physics’),
(‘Geometry’);
Delete
We use the DELETE command to delete the data in the table.
USE [School]
GO
Delete From lesson
If you do not use the Delete command with the Where condition, you will delete the entire table. Now let’s create the table again and delete the records one by one.
USE [School]
GO
INSERT INTO lesson (Lesson_Name)
VALUES
(‘Math’),
(‘Literature’),
(‘Physics’),
(‘Geometry’);
As you can see the id information has been reassigned.
USE [School]
GO
Delete From Lesson where id=12
USE [School]
GO
Delete From Lesson where Lesson_Name=’Physics’
Update
We use the UPDATE command to update the data in the table. The most important thing about the update command is that you should never use it without a where condition.
Now let’s check what happens if we don’t use where command.
Let’s update Mesut Tufan’s surname as Turan.
USE [School]
GO
Update Student Set Last_Name = ‘Turan’
It updated the whole table because we didn’t add the where condition to our command. If you don’t have a backup, it can be a big disaster for you. Never do this.
We’re updating them one by one to fix it.
USE [School]
GO
Update Student Set Last_Name = ‘Yunus’ where First_Name=’Ali’
Update Student Set Last_Name = ‘Takoz’ where id=3
Update Student Set Last_Name = ‘Demirel’ where First_Name=’Hakan’
As you can see, if the where condition is not used during the update process, the whole table is affected and it is very difficult to return.
I will write a few more articles about SQL and go step by step.
bolŞans…
