XIONG, Fanyu58952947 is5413219 is54132192007
IS5413 Database Management Systems
Lab Tutorial 1 –Introducing SQL Server
Objectives:
Create tables in SQL Server
Insert records into tables in SQL Server
Select records from tables in SQL Server
Update records in tables in SQL Server
Delete records from tables in SQL Server
IS5413 Database Management Systems
Lab Tutorial 1 – Introducing SQL Server
Throughout this course, we will use SQL Server to do the database application implementation.
For Windows users, you may download and install the SQL Server Management Studio SSMS 19.01
with the following URL:
https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-
ver16
For Mac users, you have the following options, since SSMS is not available for MacOS.
1. Use the Virtual Desktop Service (VDS) to access a Windows environment
a) First, install the Virtual Desktop Service by following the instructions here (click to see).
b) Make sure that you are connected to the wifi network “CityU WLAN (WPA)” instead of others.
c) Once you have access to the Windows environment, you can follow the same steps as Windows
users to install and use SSMS.
2. Install Azure Data Studio as an alternative
a) You can download Azure Data Studio here (Click to download), which is a lightweight, cross-
platform alternative to SSMS.
b) While its interface differs slightly from SSMS, it provides all the necessary features required for
this course.
Find SQL Server Management Studio from the application list and start it.
!!!You should have received an email containing your SQL
Server login name and password. Please use them to login to the server for your
upcoming tutorials and group project.
In the prompt, enter the following details to login to the SQL Server:
Server type: Database Engine
Server name: 144.214.55.157
Authentication: SQL Server Authentication
Login: Provided through email
Password: Provided through email
After successful login, you should see the following screen.
Click the
Database
You can locate and expand the databases folder of your own database (e.g. is5413xxx). Click to view the
details of the database objects in your database, e.g. tables, views, constraints, etc.
Let’s start writing our 1st query. Click on the New Query button in the middle of the toolbar.
After you clicked on the New Query button, you should see the query editor is opened. On the top left
corner, a pull-down menu will show you the current database you are working on now. All the queries you
write will take effect on this database.
Exercise 1: Creating Table
Create the following tables that satisfy the requirements stated.
SQL syntax for creating a table:
CREATE TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
...
);
Some commonly used constraints:
NOT NULL: Ensures that a column cannot have a NULL value
UNIQUE: Ensures that all values in a column are different
PRIMARY KEY: Uniquely identifies each row in a table
FOREIGN KEY: Uniquely identifies a row in another table
CHECK: Ensures that all values in a column satisfy a specific condition
DEFAULT: Sets a default value for a column when no value is specified
Table name: Course
Column Name Data Type Null is allowed or not? Remarks
CID Varchar, width = 6 Not allowed Unique identifier of a
course.
Primary key of the
Course table
Name Varchar, width = 100 Not allowed Name of the course
Credit Integer Not allowed Credit of the course
SQL:
create table Course (
CID varchar(6) not null primary key,
Name varchar(100) not null,
Credit int not null
);
Table name: Student
Column Name Data Type Null is allowed or not? Remarks
SID Varchar, width = 8 Not allowed Unique identifier of a
student.
Primary key of the
Student table
Name Varchar, width = 50 Not allowed Name of the student
Phone Integer Allowed Contact phone of the
student
SQL:
create table Student (
SID varchar(8) not null primary key,
Name varchar(50) not null,
Phone int
);
Table name: Enrollment
Column Name Data Type Null is allowed or not? Remarks
SID Varchar, width = 8 Not allowed Foreign key referring to
the SID of the Student
table.
Composite Primary Key
CID Varchar, width = 6 Not allowed Foreign key referring to
the CID of the Course
table.
Composite Primary Key
Date Datetime Not allowed Enrollment date.
Status Varchar, width = 1 Not allowed Enrollment status.
S = successful
N = unsuccessful
P = pending approval
SQL:
create table Enrollment (
SID varchar(8) not null foreign key references Student(SID),
CID varchar(6) not null foreign key references Course(CID),
Date datetime not null,
Status varchar(1) not null,
primary key (SID, CID)
);
To create the above Course table, enter the SQL statements in the query editor. Click Execute to
executing the SQL statements.
After executing the SQL statements, you should see the commands complete successfully message.
You may need to right-click the Tables folder and select Refresh to see the newly created tables.
!!!!You should make sure the table creation order, i.e., Course and
Student tables before Enrollment table (why?). And check the message to make sure you have
successfully created all 3 tables.
In case you would like to delete a table, you may use the SQL command:
drop table Enrollment;
SQL:
create table Enrollment (
SID varchar(8) not null foreign key references Student(SID),
CID varchar(6) not null foreign key references Course(CID),
Date datetime not null,
Status varchar(1) not null,
primary key (SID, CID)
);
Exercise 2: INSERT statement
SQL syntax for inserting a record:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Insert the following data into the corresponding table.
Insert into Course table:
IS5413, Data Management, 3
IS3232, Information Systems Management, 3
SQL:
insert into Course values ('IS5413', 'Data Management', 3);
insert into Course values ('IS3232', 'Information Systems Management',
3);
Insert into Student table:
50123456, Weiwei, 27888514
50123457, Julie, 21942392
50123458, Tina, 27889972
SQL:
insert into Student values ('50123456', 'Weiwei', 27888514);
insert into Student values ('50123457', 'Julie', 21942392);
insert into Student values ('50123458', 'Tina', 27889972);
Insert to Enrollment table:
1, 50123456, IS5413, 2007-10-20, P
2, 50123457, IS5413, 2007-10-20, P
3, 50123458, IS5413, 2007-10-20, P
4, 50123456, IS3232, 2006-10-20, P
5, 50123458, IS3232, 2006-10-20, P
SQL:
insert into Enrollment values ('50123456', 'IS5413', '2007-10-20',
'P');
insert into Enrollment values ('50123457', 'IS5413', '2007-10-20',
'P');
insert into Enrollment values ('50123458', 'IS5413', '2007-10-20',
'P');
insert into Enrollment values ('50123456', 'IS3232', '2006-10-20',
'P');
insert into Enrollment values ('50123458', 'IS3232', '2006-10-20',
'P');
Execute the SQL insert statements below and check the message for completion.
Message
shows the
You could also check your inserted data by right-click on the table and select viewing/editing the rows:
Exercise 3: SELECT statement
SQL syntax of the select statement:
SELECT some_column
FROM table_name
WHERE column_name operator value;
Use queries to answer the following questions.
A. Which courses offer 3 credit points? List the CI, course name, and credit.
select CID, Name, Credit
from Course
where Credit = 3;
B. How many students enrolled in course IS5413?
select count(SID) as 'Num of Students'
from Enrollment
where CID = 'IS5413';
C. List the enrollments after the date of 10/20/2006.
select SID, CID
from Enrollment
where Date > '2006-10-20';
Exercise 4: DELETE and UPDATE statements
SQL syntax for updating records:
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE column_name operator value;
SQL syntax for deleting records:
DELETE FROM table_name
WHERE column_name operator value;
* One advantage of using SQL statements is to update/delete a number of records at a time.
For example:
update Course
set Credit = 3
where Credit = 1;
Let’s try this combined case:
1. A new course is now available: IS5740, Decision Support Systems, 3 credit units
insert into Course values ('IS5740', 'Decision Support Systems', 3);
2. Enroll all students to the new course with Pending status
insert into Enrollment values ('50123456', 'IS5740', '2021-01-01',
'P');
insert into Enrollment values ('50123457', 'IS5740', '2021-01-01',
'P');
insert into Enrollment values ('50123458', 'IS5740', '2021-01-01',
'P');
3. All enrollments for the new course is now successful
update Enrollment
set Status = 'S'
where CID = 'IS5740';
4. The course IS5740 is no longer offered
delete from Enrollment
where CID = 'IS5740';
delete from Course
where CID = 'IS5740';
* Pay special attention to the correct delete order (one-to-many relationship)
Exercise 5: Another way to create tables.
1. In the Object Explorer Details window, locate the Tables folder, right-click on the blank area, as
shown below. To find the window if it doesn’t appear by default, click on View Object
Explorer Details, or simply press the hotkey F7.
2. A new tab of the table design interface as below will show up. You may specify a column name,
data type and other characteristics here. Try to create a table and delete it using a SQL query.
Reference:
Modern Database Management, 10th Edition, Chapter 6
http://msdn2.microsoft.com/en-us/library/ms189826.aspx
- End of Lab Tutorial 2 -
