Introduction to SQL Server - Most Important SQL Queries

By Introduction to SQL Server - Most Important SQL Queries

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:

  1. Admin
  2. Users
  3. Tutors
  4. Students
  5. Class
  6. Completed classes
  7. Notes
  8. Questions
  9. Answers
  10. Messages

The views will be:

  1. Students_view
  2. 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.


Was this article helpful?
Donate with PayPal: https://www.paypal.com/donate

Bessy
Eric Murithi Muchenah

Life is beautiful, time is precious. Make the most out of it.