Today, data is the basis of any business out there. Big enterprises do data-driven businesses. This data needs to be stored in well-structured storage facilities called database. Therefore in this article I shall focus on teaching you the most important SQL Server commands. Structured Query Language. SQL is used to communicate with a database. You can use the commands in other relational DBMS like MySQL, Oracle, Postgress and Sqlite.
Creating a Database
A database is created using create database command.
E.g.
create database db_name;
Db_name is the name of the database.
If the database is already there or you are not sure, you will use if not exists command.
E.g.
IF NOT EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = 'db_name'
)
CREATE DATABASE db_name;
Deleting a Database
To delete the database drop database command is used.
E.g.
drop database db_name;
If the database does not exist of you are not sure you will use if exists command.
E.g.
IF EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = 'db_name'
)
DROP DATABASE db_name;
Renaming a database
To rename a database you will use the following command.
alter database old_name modify name = new_name;
Creating a Table
A table is defined by create table command.
E.g.
create table table_name(C1 D1, c2 D2, …. Cn Dn,
(integrity-constraint),
……,
(integrity-constraint))
Table_name is the name of the table or relation.
Each Ci is the column name in the table. It is also called attribute of the table.
Each Di is the data type of the column.
Integrity-constraints include primary keys, foreign keys etc.
If the table is already there or you are not sure you will use if not exists command.
E.g.
create table if not exists table_name …;
Example of creating tables
Now we shall create tables in the database and write queries to insert, delete, update and select from these tables. We start with creating the table student to hold students' information.
Create table students(
Student_id char(5),
Name varchar(50) not null,
Dept_name varchar(50) not null,
Previous_school varchar(50) null,
Fee numeric(4,2),
Primary key(student_id)
);
Constraints explained
Not null ensures the column/attribute will not contain any null value.
Primary key declaration on a column automatically ensures not null.
Foreign key constraint specifies a relation between tables. For example dept_name should be present in table department for it to be present in students.
Delete a table
To delete a table from the database we use drop table command.
E.g.
drop table table_name;
If the table does not exist or you are not sure you will use if exists command.
E.g.
drop table if exists table_name;
Modify the table
We modify the structure of a table by adding or deleting columns.
We use alter table command.
E.g.
alter table tbl_name add C D;
C is the name of the column to be added to the table tbl_name and D is the data type.
All tuples or rows in the table are assigned null as the value for the new column.
Dropping a column
atable tbl_name drop C;
Where C is the name of the column of table tbl_name.
Modelling Online Classroom
Now we shall create a real database with tables to demonstrate the queries introduced up there. The name of the database will be online_classroom. You can download the online classroom database here. The tables are:
- Admin
- Users
- Tutors
- Students
- Class
- Completed classes
- Notes
- Questions
- Answers
- Messages
The views will be:
- Students_view
- Tutors_view
Creating database
IF NOT EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'online_classroom'
)
CREATE DATABASE online_classroom;
Drop Database
IF EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'online_classroom'
)
DROP DATABASE online_classroom;
Creating tables
Now create the database again and proceed with creating tables
Admin table
CREATE TABLE admin (
id INT NOT NULL IDENTITY(1,1),
name VARCHAR(50) NOT NULL DEFAULT '0',
email VARCHAR(50) NOT NULL DEFAULT '0',
password VARCHAR(50) NOT NULL DEFAULT '0',
datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
CONSTRAINT email UNIQUE (email),
PRIMARY KEY (id)
);
Column id is the primary key and auto increments. Column email is unique.
Users table
CREATE TABLE users (
id INT NOT NULL IDENTITY(1,1),
firstname VARCHAR(50) NOT NULL DEFAULT '0',
lastname VARCHAR(50) NOT NULL DEFAULT '0',
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT username UNIQUE (username)
);
Students table
CREATE TABLE students (
id INT NOT NULL,
contactemail VARCHAR(50) NULL DEFAULT NULL,
contacttelephone VARCHAR(50) NULL DEFAULT NULL,
location VARCHAR(50) NULL DEFAULT NULL,
dob DATE NULL DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT FK_studentid FOREIGN KEY (id)
REFERENCES users(id)
);
Tutors table
CREATE TABLE tutors (
id INT NOT NULL,
email VARCHAR(50) NULL DEFAULT NULL,
telephone VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT t_email UNIQUE (email),
CONSTRAINT telephone UNIQUE (telephone),
CONSTRAINT FK_tutorid FOREIGN KEY (id)
REFERENCES users(id)
);
Class table
CREATE TABLE class (
id INT NOT NULL IDENTITY(1,1),
name VARCHAR(50) NULL DEFAULT NULL,
description TEXT NULL,
PRIMARY KEY (id)
);
Completed classes table
CREATE TABLE completedclasses (
id INT NOT NULL IDENTITY(1,1),
studentid INT NULL DEFAULT NULL,
classid INT NULL DEFAULT NULL,
points INT NULL DEFAULT NULL,
datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT studentid_classid UNIQUE (studentid, classid),
CONSTRAINT FK__studentsc FOREIGN KEY (studentid) REFERENCES students(id),
CONSTRAINT FK__classc FOREIGN KEY (classid) REFERENCES class(id)
);
Notes table
CREATE TABLE notes (
id INT NOT NULL IDENTITY(1,1),
classid INT NULL DEFAULT NULL,
topicname VARCHAR(100) NULL DEFAULT NULL,
notes TEXT NULL,
datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT FK__class_notes FOREIGN KEY (classid) REFERENCES class(id)
);
Questions table
CREATE TABLE questions (
id INT NOT NULL IDENTITY(1,1),
classid INT NULL DEFAULT NULL,
question TEXT NULL,
choices TEXT NULL,
answer CHAR(50) NULL DEFAULT NULL,
datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
Answers table
CREATE TABLE answers (
id INT NOT NULL IDENTITY(1,1),
studentid INT NULL DEFAULT NULL,
classid INT NULL DEFAULT NULL,
attemptnumber INT NULL DEFAULT '0',
answers TEXT NULL,
score INT NULL DEFAULT '0',
datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT FK__studentsd FOREIGN KEY (studentid) REFERENCES students(id),
CONSTRAINT FK__classd FOREIGN KEY (classid) REFERENCES class(id)
);
Messages table
CREATE TABLE messages (
id INT NOT NULL IDENTITY(1,1),
senderid INT NULL DEFAULT NULL,
receiverid INT NULL DEFAULT NULL,
conversationid INT NULL DEFAULT NULL,
message VARCHAR(260) NULL DEFAULT NULL,
seen CHAR(1) CHECK (seen IN('Y', 'N')) NULL DEFAULT 'N',
datetime DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT FK_messages_user1 FOREIGN KEY (senderid) REFERENCES users(id),
CONSTRAINT FK_messages_user2 FOREIGN KEY (receiverid) REFERENCES users(id)
);
Students view
CREATE VIEW students_view
AS
SELECT users.*, contactemail, contacttelephone, location, dob
FROM students
INNER JOIN users ON users.id = students.id;
Tutors view
CREATE VIEW tutors_view
AS
SELECT users.*, email, telephone
FROM tutors
INNER JOIN users ON tutors.id = users.id;
The database now has the tables
Backup database
Conclusion
So you have known how to use common sql queries in SQL server. You can download my demo database here.You may also make changes to the code and use it in your projects.
Thank you for reading this article. Please vote below and share it with your friends and fellow SQL developers.