SQL Server By Example

By Johnny Thunder on August 12, 2018 0 1545 Views

Today we’ll be learning how to create SQL Server databases, tables and queries using SQL studio. The easiest way to learn anything is by example. Let’s examine an example where we create a video hosting site like YouTube.

The first step is to download SQL Server and SQL Studio to cour computer

Download SQL Server Developer

Download SQL Studio

The first step is to create our database. Open up your server and right click on Databases and select new Database

Since we’re creating a video storage site, we’ll probably need video and user tables. Lets start with really simple fields (we can always add more later):

CREATE TABLE Users (
    firstname varchar(512),
    lastname varchar(512),
    email varchar(512)
);

CREATE TABLE Videos (
    title varchar(512) default '',
    descr varchar(5000) default ''
);

Next, let’s add some users and videos:

INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Appleseed', 'johnappleseed@gmail.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Paul', 'McJohnson', 'paul@gmail.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Ringo', 'Rando', 'ringo@gmail.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('Rebecca', 'Albertson', 'rebeccaalbertson@gmail.com');
INSERT INTO Users (firstname, lastname, email) VALUES ('John', 'Harrison', 'johnharrison@gmail.com');

INSERT INTO Videos (title, descr) VALUES ('Bohemian Rhapsody', 'Queen official music video');
INSERT INTO Videos (title, descr) VALUES ('A Kind of Magic', 'Queen official music video');
INSERT INTO Videos (title, descr) VALUES ('Keep Yourself Alive', 'Queen official music video');

As a sanity check, let’s see the records using two SELECT statements:

SELECT * FROM Users;
SELECT * FROM Videos;

ALTER TABLE Users ADD ID INT IDENTITY(1,1);
ALTER TABLE Videos ADD ID INT IDENTITY(1,1);

SELECT * FROM Users;
SELECT * FROM Videos;

SELECT * FROM Users;
SELECT * FROM Videos;


INSERT INTO Users (firstname, lastname, email) VALUES ('Suzy', 'Person', 'suxy@gmail.com');

SELECT * FROM Users;


INSERT INTO Videos (title, descr) VALUES ('This Could Be Heaven', 'Queen official music video');

SELECT * FROM Videos;

ALTER TABLE Videos ADD uploader int;


SELECT * FROM Videos;


UPDATE Videos SET uploader = 1;

SELECT * FROM Videos;

CREATE TABLE VideoViews (
    videoId int NOT NULL,
    userId int NOT NULL,
    dte datetime
);


CREATE TABLE VideoRatings(
    videoId int NOT NULL,
    userId int NOT NULL,
    rating int,
    dte datetime,
    primary key (videoId, userId)
);

INSERT INTO VideoViews (videoId, userId, dte) VALUES (1, 1, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (1, 2, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (1, 3, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (1, 4, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (2, 1, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (2, 3, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (3, 1, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (3, 2, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (4, 1, GETDATE());
INSERT INTO VideoViews (videoId, userId, dte) VALUES (4, 2, GETDATE());



INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (1, 1, 3, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (1, 2, 4, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (1, 3, 5, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (1, 4, 5, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (2, 1, 4, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (2, 3, 5, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (3, 1, 4, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (3, 2, 4, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (4, 1, 3, GETDATE());
INSERT INTO VideoRatings (videoId, userId, rating, dte) VALUES (4, 2, 3, GETDATE());

--View counts for each video
SELECT videoId, COUNT(*) as totalViews FROM VideoViews GROUP BY videoId;

SELECT videoId, totalViews, title  FROM (
    SELECT videoId, COUNT(*) as totalViews FROM VideoViews GROUP BY videoId
) sub
INNER JOIN Videos on sub.videoId = Videos.ID
ORDER BY totalViews desc;

--Average ratings for each video
SELECT videoId, COUNT(*) as totalRatings, AVG(rating) as avgRating FROM VideoRatings GROUP BY videoId;

SELECT videoId, totalRatings, avgRating, title  FROM (
    SELECT videoId, COUNT(*) as totalRatings, AVG(rating) as avgRating FROM VideoRatings GROUP BY videoId
) sub
INNER JOIN Videos on sub.videoId = Videos.ID
ORDER BY avgRating desc;

--Average ratings for each user
SELECT userId, COUNT(*) as totalRatings, AVG(rating) as avgRating FROM VideoRatings GROUP BY userId;

SELECT userId, totalRatings, avgRating, firstname, lastname  FROM (
    SELECT userId, COUNT(*) as totalRatings, AVG(rating) as avgRating FROM VideoRatings GROUP BY userId
) sub
INNER JOIN Users on sub.userId = Users.ID
ORDER BY avgRating desc;

Video Walkthrough

Microsoft SQL Server

SQL Server By Example

Video Walkthrough

PostgreSQL Inline Tables

Related Posts

PostgreSQL Inline Tables

Leave a comment