DEV Community

loading...
Cover image for SQL Server Management Studio (Professors Example)

SQL Server Management Studio (Professors Example)

Kiron Roy
Digital Artist
Updated on ・9 min read

Article navigation
⤵️ Tables
⤵️ Insert Data
⤵️ Queries
⤵️ Update & Delete
⤵️ Stored Procedures

Introduction

I created a simple database using SQL Server Management Studio. The database tracks professors giving lectures at universities.

A Database is a container of:

  • tables
  • queries
  • functions

Some universities have more than one campus. Professors can give more than one lecture. All the columns must be filled out. Null values are not allowed, all columns have to have data.

Data types of columns include: int for both primary and surrogate keys (ids). BillRate and Bonus have the type of money. Dates will have the type of datetime(7). All other columns will have the data type of varchar.

The database's name is ProfessorTracker.

The database has 5 tables:

  1. Professors
  2. TopicOfLecture
  3. LecturesDone
  4. Universities
  5. Locations

/*
The database and table names do not have 
spaces in between the words
Database and tables use CapitalCase
*/
-- The database is labled 

dbo.ProfesorTracker

-- dbo. stands for database owner.

-- The the tables are labeled as such

dbo.Professors
dbo.TopicOfLecture
dbo.LecturesDone
dbo.Universities
dbo.Locations

Enter fullscreen mode Exit fullscreen mode

The 5 tables are interrelated to each other. That is why Structured Query Language (SQL) are known as relational databases.

  • Every table will have a primary key 🔑 that will be unique to that table.
  • Every table will have CreateDate and LastUpdated columns.
  • The tables will have surrogate keys (ids) that will align to primary keys (ids) to create table relationships.
  • A University might have multiple locations. Example: Pepperdine has 2 campuses. One university, 2 locations. This is a One to many relationship. Note: this will be discussed in the Queries section

Tables

Table 1

Professors is a 8 column table that contains information on Professors:

  • id 🔑
  • FirstName
  • LastName
  • EmailAddress
  • PayRate
  • Bonus
  • CreateDate
  • LastUpdated
id FirstName LastName EmailAddress PayRate Bonus CreateDate LastUpdated
1 Andhrea Gezz andhreagezz@ucla.edu 100.00 200.00 2021-03-15 18:08:44.4133333 2021-03-15 18:08:44.4133333
2 Sal Permutter salpermutter@cal.edu 150.00 200.00 2021-03-15 18:09:43.7033333 2021-03-15 18:09:43.7033333
3 Edward Murphy edmurphy@jefferson.com 110.00 120.00 2021-03-16 06:53:03.0500000 2021-03-16 06:53:03.0500000
4 Paul Brown paulbrown@yale.edu 200.00 300.00 2021-03-16 07:20:39.7500000 2021-03-16 07:20:39.7500000
1005 Steve Silent stevesilent@humbolt.edu 300.00 400.00 2021-03-17 18:13:04.4200000 2021-03-17 18:13:04.4200000
1006 Charles Evans charlesevans@csuf.edu 310.00 320.00 2021-03-17 18:17:32.4400000 2021-03-17 18:17:32.4400000

Note: Sometimes ids can get out of sync. For example, a wrong data type: varchar instead of an int type, or failed to include data (missing columns).

SSMS has created those ids, but they do not show up on the table. ids do not have to be in sequential order from 1,2,3.... There can be gaps. The main idea is that the ids have to be unique.
_

Table 2

Topic of Lectures is a 5 column table that contains information on the topic of a lecture:

  • id 🔑
  • LectureTopic
  • UniversityId
  • CreateDate
  • LastUpdated
id LectureTopic UniversityId CreateDate LastUpdated
1 The Expanding Universe. 1 2021-03-15 18:24:38.9500000 2021-03-15 18:24:38.9500000
2 Black Hole in the Milkyway Galaxy 2 2021-03-15 18:24:38.9500000 2021-03-15 18:24:38.9500000
3 The Orgins of the Elements 3 2021-03-16 06:58:08.5033333 2021-03-16 06:58:08.5033333
4 Nuclear Reactions 4 2021-03-16 07:24:15.2600000 2021-03-16 07:24:15.2600000
5 Microsoft Word 4 2021-03-16 07:24:31.3666667 2021-03-16 07:24:31.3666667

Table 3

Lectures Done is a 9 column table that

  • id
  • ProfessorId
  • TopicId
  • LocationId
  • HoursLectured
  • DescrptionOfLecture
  • DateOfLecture
  • CreateDate
  • LastUpdated
id ProfessorId TopicId LocationId HoursLectured DescrptionOfLecture DateOfLecture CreateDate LastUpdated
1 1 2 2 6 How I discovered a black hole in our Milkway Galaxy 2017-02-02 2021-03-15 18:38:50.6333333 2021-03-15 18:38:50.6333333
2 2 1 1 4 The Universee is exapnding and accelerating 2016-01-03 2021-03-15 18:38:50.6333333 2021-03-15 18:38:50.6333333
3 3 3 3 2 Origin of the all the known elements in the perodic table 2019-07-05 2021-03-16 07:05:26.6100000 2021-03-16 07:05:26.6100000
4 4 4 4 1 Atomic 101 2000-08-03 2021-03-16 07:31:32.1600000 2021-03-16 07:31:32.1600000
5 4 5 5 3 The coolness of Microsoft Word 1999-02-04 2021-03-16 07:31:32.1600000 2021-03-16 07:31:32.1600000

Table 4

Universities is a 6 column table that contains information on the topic of a lecture:

  • id 🔑
  • UniversityName
  • Website
  • ContactEmail
  • CreateDate
  • LastUpdated
id UniversityName Website ContactEmail CreateDate LastUpdated
1 UCLA www.ucla.edu randyjackson@ucla.edu 2021-03-15 18:17:59.1366667 2021-03-15 18:17:59.1366667
2 Harvard www.harvard.edu theoratcliff@harvard.edu 2021-03-15 18:18:32.2400000 2021-03-15 18:18:32.2400000
3 Stanford www.stanford.edu maxcool@pine.edu 2021-03-16 06:55:40.2333333 2021-03-16 06:55:40.2333333
4 Pepperdine University www.pepperdine.edu kennystar@pep.edu 2021-03-16 07:13:18.4966667 2021-03-16 07:13:18.4966667

Table 5

Locations is a 8 column table that contains information of Universities:

  • id 🔑
  • StreetAddress
  • City
  • State
  • ZipCode
  • UniversityId
  • CreateDate
  • LastUpdated
id StreetAddress City State ZipCode UniversityId CreateDate LastUpdated
1 123 Westwood Blvd L.A. CA 90095 1 2021-03-15 18:30:04.7400000 2021-03-15 18:30:04.7400000
2 357 Harvard Road Boston MA 01434 2 2021-03-15 18:30:04.7400000 2021-03-15 18:30:04.7400000
3 123 Pine Street Palo Alto CA 94301 3 2021-03-16 07:00:20.0000000 2021-03-16 07:00:20.0000000
4 444 Eden way Malibu CA 90263 4 2021-03-16 07:16:05.7000000 2021-03-16 07:16:05.7000000
5 6750 Argo Road Calabasas CA 91302 4 2021-03-16 07:17:47.7100000 2021-03-16 07:17:47.7100000

Table Relationship Diagram

Alt Text

Article Navigation
⤴️ Back to the Top

Insert data into Professors.dbo


-- Primary keys are created automatically
-- CreateDate and LastUpdate will be created automatically
-- always end statements with a semicolon ;

-- FirstName, LastName, and email are 'varchar' type need single quotes ' '
-- PayRate, and Bonus are ints, just put numbers

insert into dbo.Professors (FirstName, LastName, EmailAddress, PayRate, Bonus)
values ('Andhrea', 'Gezz', 'andhreagezz@ucla.edu', 100, 200);

Enter fullscreen mode Exit fullscreen mode
Article Navigation
⤴️ Back to the Top

Queries

Order by will order the data, here it is ordered reverse-alphabetical by first name:

/*
 Multi 
 line 
 comment 
*/

-- single line comment 
-- desc = ordering it by reverse alpha order, z-a

select FirstName, LastName, EmailAddress, PayRate, Bonus
from dbo.Professors
order by FirstName desc;
Enter fullscreen mode Exit fullscreen mode

Output ⏩

FirstName LastName EmailAddress PayRate Bonus
Steve Silent stevesilent@humbolt.edu 300.00 400.00
Sal Permutter salpermutter@cal.edu 150.00 200.00
Paul Brown paulbrown@yale.edu 200.00 300.00
Edward Murphy edmurphy@jefferson.com 110.00 120.00
Charles Evans charlesevans@csuf.edu 310.00 320.00
Andhrea Gezz andhreagezz@ucla.edu 100.00 200.00

Where statement acts on a query before it is grouped:

select FirstName, LastName, EmailAddress, PayRate, Bonus
from dbo.Professors
-- where and then a condition 
where Bonus > 300;
Enter fullscreen mode Exit fullscreen mode

Output ⏩

FirstName LastName EmailAddress PayRate Bonus
Stephen Silent stevesilent@humbolt.edu 300.00 400.00
Charles Evans charlesevans@csuf.edu 310.00 320.00

Left Join Connects a one table's information (left side) with another table's information on the (right side)

-- University table will have a varible of 'u'
-- Location will have a variable of 'l'
-- the 'as' keyword can create custom headers

select u.UniversityName as 'University'
     , u.id as 'University Id'
     , l.StreetAddress as 'Street Address'
     , l.State
     , l.id as 'Location Id'
from dbo.Universities u

left join dbo.Locations l on u.id = l.UniversityId;
Enter fullscreen mode Exit fullscreen mode

Output ⏩

University University Id Street Address State Location Id
UCLA 1 123 Westwood Blvd CA 1
Harvard 2 357 Harvard Road MA 2
Stanford 3 123 Pine Street CA 3
Pepperdine University 4 444 Eden way CA 4
Pepperdine University 4 6750 Argo Road CA 5

Note: Pepperdine has a unique id of 4, but since it has two locations it has two Location ids: 4 and 5 respectively.

Inner Join

-- variables for each table:
    -- ld LecturesDone table
    -- p Professors table
    -- t TopicOfLecture table
    -- u Universitites table
    -- l locations table

-- ** This query uses all 5 tables **

select p.FirstName + ' ' + p.LastName as 'Professor'
      , t.LectureTopic as 'Topic of Lecture'
      , ld.DescrptionOfLecture as' Title of Lecture'
      , u.UniversityName as 'University'
      , l.City
      , ld.HoursLectured as 'Total Hours Lectured'
from dbo.LecturesDone ld

-- How do these tables connect?

-- Join the Professors and the Lectures they did
inner join dbo.Professors p on ld.ProfessorId = p.id

-- Topic of lecture on the Lectures done table
inner join dbo.TopicOfLecture t on ld.TopicId = t.id

-- Which University was the lecture done in
inner join dbo.Locations l on ld.LocationId = l.id
inner join dbo.Universities u on t.UniversityId = u.id
Enter fullscreen mode Exit fullscreen mode

Output ⏩

Professor Topic of Lecture Title of Lecture University City Total Hours Lectured
Andhrea Gezz Black Hole in the Milkyway Galaxy How I discovered a black hole in our Milkway Galaxy Harvard Boston 6
Sal Permutter The Expanding Universe. The Universee is exapnding and accelerating UCLA L.A. 4
Edward Murphy The Orgins of the Elements Origin of the all the known elements in the perodic table Stanford Palo Alto 2
Paul Brown Nuclear Reactions Atomic 101 Pepperdine University Malibu 1
Paul Brown Microsoft Word The coolness of Microsoft Word Pepperdine University Calabasas 3
Article Navigation
⤴️ Back to the Top

Update and Delete

Update a Record

-- The Profesor table needs to be updated to say:
-- 'Steve' to 'Stephen' 

select * from dbo.Professors

update dbo.Professors
set FirstName = 'Stephen'
where id = 1005;
Enter fullscreen mode Exit fullscreen mode
id FirstName LastName EmailAddress PayRate Bonus CreateDate LastUpdated
1 Andhrea Gezz andhreagezz@ucla.edu 100.00 200.00 2021-03-15 18:08:44.4133333 2021-03-15 18:08:44.4133333
2 Sal Permutter salpermutter@cal.edu 150.00 200.00 2021-03-15 18:09:43.7033333 2021-03-15 18:09:43.7033333
3 Edward Murphy edmurphy@jefferson.com 110.00 120.00 2021-03-16 06:53:03.0500000 2021-03-16 06:53:03.0500000
4 Paul Brown paulbrown@yale.edu 200.00 300.00 2021-03-16 07:20:39.7500000 2021-03-16 07:20:39.7500000
1005 Stephen Silent stevesilent@humbolt.edu 300.00 400.00 2021-03-17 18:13:04.4200000 2021-03-17 18:13:04.4200000
1006 Charles Evans charlesevans@csuf.edu 310.00 320.00 2021-03-17 18:17:32.4400000 2021-03-17 18:17:32.4400000

Delete a record

-- BE CAREFUL ABOUT THIS 
-- ONCE IT IS DELETED, IT'S GONE
-- NO REDO OPTION!

delete
from dbo.Professors
where id = 1
Enter fullscreen mode Exit fullscreen mode
Article Navigation
⤴️ Back to the Top

Stored Procedures

  • Stored procedures can limit access to the database.

  • It's a layer of control and protection.

    • Users cannot access tables
    • Users cannot access views
    • Users cannot type select * dbo.LecturesDone;
    • Stored procedure make it harder for [sql injection (https://www.w3schools.com/sql/sql_injection.asp)
    • SQL injection = Smith (as a last name); delete from from dbo.Customers;
  • Stored procedures are fast.

Creation of a Stored Procedure


CREATE PROCEDURE dbo.spUniversityLookup

-- sp = stands for stored procedures

AS
BEGIN

    SET NOCOUNT ON;
    select *
    from dbo.Universities;

END
GO

Enter fullscreen mode Exit fullscreen mode

Use the spUniversityLookup

exec dbo.spUniversityLookup;
Enter fullscreen mode Exit fullscreen mode

Output ⏩

UniversityName Website ContactEmail CreateDate LastUpdated
UCLA www.ucla.edu randyjackson@ucla.edu 2021-03-15 18:17:59.1366667 2021-03-15 18:17:59.1366667
Harvard www.harvard.edu theoratcliff@harvard.edu 2021-03-15 18:18:32.2400000 2021-03-15 18:18:32.2400000
Stanford www.stanford.edu maxcool@pine.edu 2021-03-16 06:55:40.2333333 2021-03-16 06:55:40.2333333
Pepperdine University www.pepperdine.edu kennystar@pep.edu 2021-03-16 07:13:18.4966667 2021-03-16 07:13:18.4966667
/*
This will display the same information 
from the query section
*/

exec dbo.spProfesorLectureLoookup;
Enter fullscreen mode Exit fullscreen mode

Output ⏩

Professor Topic of Lecture Title of Lecture University City Total Hours Lectured
Andhrea Gezz Black Hole in the Milkyway Galaxy How I discovered a black hole in our Milkway Galaxy Harvard Boston 6
Sal Permutter The Expanding Universe. The Universee is exapnding and accelerating UCLA L.A. 4
Edward Murphy The Orgins of the Elements Origin of the all the known elements in the perodic table Stanford Palo Alto 2
Paul Brown Nuclear Reactions Atomic 101 Pepperdine University Malibu 1
Paul Brown Microsoft Word The coolness of Microsoft Word Pepperdine University Calabasas 3
Article Navigation
⤴️ Back to the Top

Discussion (0)

Forem Open with the Forem app