Transcript COMPSCI 280 : tutorial 1

COMPSCI 280 : TUTORIAL 1
Presented by: Teererai Marange
AGENDA
 $whoami
 Admin
 Some
 Intro
details
Tips
to lab 1
 Revision
exercises
 Huddle
 More
revision exercises
$whoami

My name is Terry.

My background:

Originally from a business background.

Used to trade options and shares for a living.

Made some observations that led to my switch to Compsci.

Currently a Phd student in Computer Science.

My research focus:


Data Structures and Algorithms.

Artificial intelligence.
Most importantly I will be your tutor.
Admin details
 My
email: [email protected]
 My
office hours:

Every Tuesday between 12 and 1pm in rm 303-375

I will also be available at lab 303s-G91 btw 8am and 12pm on every
Wednesday.

I will also be answering queries on Canvas.

Ideally please post your questions here so everyone can see them.
Some tips
 Practise
 Every
makes perfect.
technology has a use case and limitations.

Try to pick out the use cases

Try to look for limitations as well.
 If
in doubt, ask.
 If
I’m moving too fast or too slow let me know.
I
welcome feedback on topics covered or any topics
of interest that you would like to discuss briefly.
 Ideally
start assignments and labs ASAP.
Introduction
What is a database?

A collection of related data stored in a manner so it can be
retrieved as needed.

Why a database?

Reduced duplication and better control over data consistency.

Concurrency control.

Recovery.

Security.

Centralized control.

Platform independence.

Encourage good data management practices.
Use case 1: local database

App and database stored on the
same device
Use case 2: Remote database

Remote database

Scales better.
SQL
 SQL
(Structured query language)

A language for interacting with a relational database.

High level language

Declarative(As opposed to procedural).

User specifies what they want done ignoring how.

This is put in a command called a query.
Let’s talk about Lab 1:

3 databases:

Sdf file(local)

MySQL(remote)

MS SQL Server(remote)

Let’s connect to each.

You are required to

Create a table on each.

Insert records into each.

Use select Query to check that your records and table have been
persisted to the databases.
Create table:

Structure:

CREATE TABLE some_table (
Column_name1 data_type_1 [optional constraints]
Column_name2 data_type_1 [optional constraints]
…
);

Constraints:

NOT NULL: value should not be null.

UNIQUE: All values in column must be unique.

DEFAULT. Assigns value to attribute when a new row is added to table
MYSQL datatypes(Using Putty SQL
client):

Characters: CHAR(20), VARCHAR(50)




Char: Holds a fixed length string
Varchar: Holds a variable length string
Numbers: INT, BIGINT, SMALLINT, FLOAT
Others: DATE, DATETIME
SQL CE Datatypes(Using Linqpad):

Characters: NCHAR(20), NVARCHAR(50)




nChar: Holds a fixed length string
nVarchar: Holds a variable length string
Numbers: INT, BIGINT, SMALLINT, FLOAT
Others: DATE, DATETIME
Exercises:
We will be using the Northind.sdf database file so download it from
canvas and connect to it using linqpad.
Exercise: 1
A. Write a SQL statement to create a table named countries including
columns country_id,country_name and region_id. Make sure that no all
values of country_id will be unique. The default value for
country_name is Zimbabwe and a value must be entered for region_id.
Exercise: 1
Exercise: 1

After clicking the play button, right click the database in question
and then click refresh.
Exercise: 1

Notice that the countries table appears on the left hand side.
Exercise: 1

After pressing the play button, right click the database in
question and then click refresh.
Insert
Method 1:

Specifies a value for each column of the table

Values MUST be in the same order as the columns of the table
Method 2:

Put values in a few columns of the table.
Exercise 2:
A. Enter the following and then press play.
Exercise 2:
A. Now check whether the record is in the table. Enter the code below
and then click play. You should see the results below.
Exercise 2:
C. Now reenter the insert query from before and press play. You should
receive the following error. This is because you are attempting to
inserta duplicate record.
Exercise 2:
A. Enter the following and then press play.
Select statement

Used to retrieve data from a table.

6 parts

SELECT [DISTINCT] Column_1, [Column_2, Column_3, …]

FROM Table_Name

[WHERE Clause]

[GROUP BY Clause]

[HAVING CLAUSE]

[ORDER BY Clause]
SELECT

SELECT [DISTINCT] Column_1, [Column_2, Column_3, …]

Which columns we want to see in results table.

Column list can be *(meaning all columns in the order they are in the
original table)

Result table has columns in the order given.

Aliases allowed


Eg. Column 1 AS Name
Optional DISTINCT clause retrieves unique records on the results tables.
WHERE Clause

A statement that returns true for all rows in the result table.

Examples

WHERE manager_id=203

WHERE manager_id IN (203, 204, 210)

WHERE manager_id BETWEEN 203 and 210



What are the boundaries.
WHERE last_name LIKE ‘%M_ar%’
Note: We can use connectors like AND OR and NOT
WHERE Clause

A statement that returns true for all rows in the result table.

Examples

WHERE manager_id=203

WHERE manager_id IN (203, 204, 210)

WHERE manager_id BETWEEN 203 and 210



What are the boundaries.
WHERE last_name LIKE ‘%M_ar%’
Note: We can use connectors like AND OR and NOT
ORDER BY

Specifies a sort order for columns:

Structure:


ORDER BY Column 1, [Column 2, …., Column n] [ASC or DESC]
Works in the order of the columns listed ie. First try to arrange column in
order of Column 1 but if Column 1 matches for 2 or more records then
order by Column 2.
Exercises 3
1.
Write a Query to show all records in the Customers table in
Ascending order of the ContactTitle.
2.
List all distinct values of ContactTitle in Ascending order of color.
3.
List the the OrderID, Order Date and Required Date for all Orders in
the Orders table whose OrderID is between 10248 and 10257
4.
List all Countries of origin of all Customers whose name has and k in
it. There should be no duplicate values in the results.
5.
List all Cities whose name starts with an r or an o in the customers
table.
Exercises 3
1.
Write a Query to show all records in the Customers table in
Ascending order of the ContactTitle.
Exercises 3
1.
Enter the following and then click play. You should see the following
in the results tab.
Exercises 3
2. List all unique values of ContactTitle in the Customers table in
Descending alphabetic order.
Exercises 3
2. Enter the following and then click play. You should see the following
in the results tab.
Exercises 3
List the the OrderID, Order Date and Required Date for all Orders in the
Orders table whose OrderID is between 10248 and 10257
Exercises 3
2. Enter the following and then click play. You should see the following
in the results tab.
Exercises 3
List all Countries of origin of all Customers whose name has k in it. There
should be no duplicate values in the results.
Exercises 3
2. Enter the following and then click play. You should see the following
in the results tab.
Exercises 3
1.
List all Cities mentioned in the Customers table whose name starts
with an r or an o. No duplicates are allowed in the results table.
Exercises 3
2. Enter the following and then click play. You should see the following
in the results tab.