PRACTICAL PAPER: SQL

hesitantdoubtfulAI and Robotics

Oct 29, 2013 (3 years and 5 months ago)

50 views

PRACTICAL PAPER: SQL

Vehicles table

Vehicles

Vehicle_ID

Rates_ID

Reg_Num

Make

Model

1

1

LKD342GP

VW

Jetta

1.8

2

1

JJK109GP

Ford

Bantam 1.3

3

3

SDF575GP

Nissan

Hardbody

2.4

4

3

LCV328GP

Toyota

Raider 2.7i

5

2

PPR844GP

VW

Microbus

6

2

HWE361GP

Toyota

HiLux

2.7

7

1

EMN349GP

Opel

Astra 2.0

8

1

PCF523GP

BMW

525i

9

3

FOS492GP

Isuzu

3.2 V6 LX

10

3

ERB258GP

Colt

Rodeo D/Cab

11

2

HLK246GP

VW

Microbus 2.4

12

1

ADQ767GP

Toyota

Corolla 1.6

13

1

LRK211GP

Nissan

Champ 4.4

14

2

LRO462GP

VW

Combi

2.5

Presentation by Marina
Myburgh

1

Rentals table

Rentals

Rental_ID

Vehicle_ID

Driver_ID

Start_Km

Stop_Km

Start_Date

Stop_Date

1

2

3

32050

32275

2004/03/19

2004/03/22

2

2

5

32280

33020

2004/04/01

2004/04/20

3

4

4

89023

89850

2004/01/20

2004/02/10

4

10

1

12090

13901

2003/09/03

2004/10/27

5

14

3

123045

127456

2003/02/22

2003/12/06

6

7

6

45690

45745

2004/01/03

2004/01/05

7

4

9

89940

90230

2004/03/01

2004/03/13

8

10

1

13950

14089

2004/10/28

2004/11/07

9

1

7

56034

57102

2003/05/23

2004/12/03

10

13

8

234567

234891

2003/12/23

2004/01/14

11

11

6

200120

201800

2004/02/12

2004/03/01

12

5

2

67099

67689

2003/06/21

2003/06/29

13

3

10

180345

182930

2003/11/04

2004/02/12

Presentation by Marina
Myburgh

2

Complete the SQL statements:


Presentation by Marina
Myburgh

3

a) List all makes in table Vehicles once only






b) List the
vehicleID

of all cars rented in 2004







c) List the
vehicleID

of all cars rented in February






SELECT DISTINCT make FROM vehicles

SELECT
vehicle_ID

FROM rentals

WHERE year(
start_date
) = 2004

SELECT
vehicle_ID

FROM rentals

WHERE month(
start_date
) = 2

Distinct (1.2)

Distinct is used
to list a value of
a field only once

Some managed
to list the
animals by
using “group by”

Presentation by Marina
Myburgh

for Study Opportunities

4

Select distinct animal from
tblSightings

Functions in SQL (1.3)


NOW() returns the current date and time


DATE() returns the current date


YEAR(), MONTH(), DAY() and
dayofweek

() return specific parts of a
date field e.g.


YEAR(#16/05/2002#) = 2002


MONTH(
birth_date
) = 5


DAY(Date()) = 16


dayofweek
(#9/3/2009#) = 2
//(Monday


Presentation by Marina Myburgh
for Study Opportunities

5

1.Complete the SQL statements:


Presentation by Marina Myburgh
for the Road show 2010

6

d)
List the car registration numbers of all cars rented
in February






e)
List number of cars of each make





SELECT
reg_num

FROM vehicles, rentals

WHERE
Rentals.vehicleID

=
vehicles.vehicleID


AND month(
start_date
) = 2




SELECT make, count(make) FROM vehicles

GROUP BY make

SELECT…
Multiple tables (1.6)


You have to list all tables you need in the FROM
clause of the query.


The order in which the tables appear in the FROM line
is
irrelevent
.


You have to include the
fields that are related in the
WHERE clause

if the field name for the foreign key is the same as the
field name for the primary key, use the
tablename

before the field name with dot notation

e.g
.



SELECT * FROM
tbl

,
tbl




WHERE
tbl

.ID =
tbl

.ID

Presentation by Marina Myburgh

7

Group By (1.4)


When you need two columns, e.g. If you want
the average number of sightings for each ranger,
you have to use
GROUP BY,
else you will get a
"group by" error.


e.g.



SELECT ranger,
avg
(
numberOfSightings
) FROM
tblSightings


GROUP BY ranger;



a simple rule which is 99% accurate:


If you have a SELECT line with aggregate
functions, then you need a GROUP BY listing all
the column names from the SELECT line which
are not used by the functions.

Presentation by Marina Myburgh

8

Complete the SQL statements(cont.)


Receive user input and display the
matching records:


i
)

Display all records in Vehicles table
where the
reg_num

matches the user
input





Presentation by Marina
Myburgh

9

Regnum :=
inputbox
(‘
Input’,’Enter

car
reg







num’,’BB53HCGP’);

Qry.sql.text

:=

SELECT * FROM vehicles


where
reg_num

=


+ regnum +



;


HKT 359 GP

Complete the SQL statements(cont.)


f)

Receive user input and display the
matching records:


ii)

Display all records in Vehicles table
where the
vehicle_ID

matches the user
input

Presentation by Marina
Myburgh

10

VehicleID

:=
inputbox
(‘
Input’,’Enter

vehicleID’,’1’);

Qry.sql.text

:=

SELECT * FROM vehicles


where
vehicleID

=

+

vehicleID

;


1

1.Complete the SQL statements(cont.)


f)

Receive user input and display the
matching records:


iii)

Display all records in the rentals table
where the start date matches the user
input





Presentation by Marina
Myburgh

11

sdate

:=
inputbox
(‘
Input’,’Enter

date’,’
2004/09/13
’);

Qry.sql.text

:=

SELECT * FROM rentals


where
start_date

=
#

+
sdate

+


#

;


2004/09/13

User input for SQL statement(1.5)


The SQL string must be closed and another string
with the user input must be added to the SQL string.
Many learners did not close the SQL string and
forgot to use + before the user input .



In this question the user input for the “ID” is of the
type number in the database and the string with user
input should therefore not have quotation marks (“ ”)
around the user input. The SQL string must only
include “ “ around input for fields where the data
type of the field in the database is text.


E.g. ……..WHERE ID = ‘ +
userID
;


e.g. ……

WHERE
animal = “’+
animalType
+’”’;

Presentation by Marina
Myburgh

12

Question 2


We should aim to teach OOP in a way so that learners develop
more understanding.


S
ome learners were caught off guard since they memorized the
“steps” to create a class and then had to apply their knowledge by
adding to an existing class.


Make sure your learners create the object properly:



object := T……….create(…..);






not
object.create


Learners should call functions declared in the class to display
values and should not re
-
calculate values for price and profit.


Some also used an array of objects because that is what was
expected in the examination, although the question did not require
an array of objects


Presentation by Marina
Myburgh

13

#13 to start a new line in output statements


Many learners thought the output
statement was complete and simply
removed the curly brackets.


They are not familiar with
long output
statements over multiple lines
. Teachers
should include #13 in their teaching.

Presentation by Marina
Myburgh

14

Question 3


Interesting question using different separators


Various methods to find the largest value of 3


Problems with random function


2.

Write down the Delphi statements to assign a
random value between:


1 and 6 (inclusive)


100 and 200 (inclusive)


-
10 and 10 (inclusive)


Presentation by Marina
Myburgh

15

X = random(6)+1

X = random(101)+100

X = random(21)
-
10

Theory Paper: General


Learners (especially those who do not write the paper
in their first language)
do not express themselves well
and do not know the
correct terminology.


Learners should
keep answers simple and to the point


Learners do not always
read the question

carefully


English terms together with less known Afrikaans terms
were used so that learners were not disadvantaged

Presentation by Marina
Myburgh

16

Define the following:


podcasting


mobi


superscalar


partitioning


defragmentation


spoofing


instruction set


hyper
-
threading


multithreading


firewall


anti
-
virus


server


switch.


Presentation by Marina Myburgh
for the Road show 2010

17

Podcasting

is a method of distributing recorded audio
in a format (usually MP3) suitable for
devices such as iPods. These audio files
are published as audio files on the
Internet and can be downloaded to a
computer or media player at any time
after it was published. (3.1.1 )

Presentation by Marina Myburgh
for the Road show 2010

18

Mobi

The domain name
mobi

is a top
-
level
domain (TLD) in the Domain Name
System of the Internet. Its name is
derived from mobile, indicating its use by
mobile devices for accessing Internet
resources via the Mobile Web

Presentation by Marina Myburgh

19

Superscalar

Is a processor architecture in which the
processor can execute multiple
instructions (typically two or four) per
instruction cycle by using more than one
pipeline.


Presentation by Marina Myburgh

20

Partitioning

is a way of using the operating system to
divided the available hard disk space into
more than one virtual drive. Each
partition appears as a separate drive e.g.
D: E: although there is only one physical
hard drive. Each drive can house a
separate operating system or data can
be stored on a separate drive.

Presentation by Marina Myburgh

21

Defragmentation

is a utility program in the operating system
that reorganises the files so the file
segments from the same file that were
stored in different clusters anywhere on
the hard disk, are placed together in
contiguous clusters on the hard drive to
improve access speed to these files

Presentation by Marina Myburgh
for the Road show 2010

22

Spoofing

is a term referring to
fraudulent email
actions whereby
sender's address
or other sections
thereof are altered
to simulate the
message is coming
from another
source or sender

Presentation by Marina Myburgh

23

Instruction set

is a basic set of commands that the CPU
can recognize and execute. These are
very basic instructions that are wired into
the logic of the CPU. (6.5)

Presentation by Marina Myburgh
for the Road show 2010

24

Hyper
-
threading
vs

multi
-

threading


Hyper
-
threading: A feature of certain Pentium
processors that makes one physical CPU
appear as two logical CPUs, overlapping two
instruction streams in order to achieve a gain in
performance.



Multithreading:The

ability of an operating
system to run different parts of ONE program
called threads, simultaneously.

Presentation by Marina Myburgh

25

Firewall
vs

anti
-
virus


A firewall helps protect your computer by preventing
unauthorised access to the network from the Internet or
other networks. It prevents ports from being illegally
used and prevents programs from illegally
communicating with the computer(6.3.2)


Anti
-
virus software is used to prevent, detect, and
remove malware, including computer viruses, worms,
and
trojan

horses. The anti
-
virus program runs in the
background and scans the computer's memory and all
files that are accessed for known and unknown viruses
in your computer and removes them. It even helps
repair the damaged files left behind.




Presentation by Marina Myburgh
for the Road show 2010

26

Server
vs

switch


Server: a computer that provides client stations
with access to files and printers as shared
resources to a computer network by using
network software


Switch: Hardware to enable you to link multiple
computers together in a network. This allows
you to have dedicated bandwidth on point
-
to
-
point connections with every computer and to
therefore run in Full duplex with no collisions.


Presentation by Marina Myburgh

27

Functions of switch


Connects several devices in a network;


transmits/amplifies signals


can detect errors and isolate error so that
the rest of the network can still function;


can send data to the required destination
on the network/ chooses the correct/best
path/intelligent path selection /


reduces traffic/ manages bandwidth;
(2.2.2)

Presentation by Marina Myburgh
for the Road show 2010

28

Normalisation

Advantage of normalising tables is :


Less duplication/repeating of data/data
integrity


Prevents update anomalies (details are
only changed in one place)


Prevents delete anomalies (all related
data will be deleted when record is
deleted and nothing more)


Easier to query the database

Presentation by Marina Myburgh
for the Road show 2010

29

Exercise

PET

ID

PET

NAME

PET

TYPE


PET

AGE


OWNER


VISIT

DATE


PROCEDURE

246


ROVER


DOG


12


SAM

COOK


JAN

13
/
2002


01

-

RABIES

VACCINATION











MAR

27
/
2002


10

-

EXAMINE

and

TREAT

WOUND











APR

02
/
2002


05

-

HEART

WORM

TEST

248


SPOT


DOG



2


TERRY

KIM


JAN

21
/
2002


08

-

TETANUS

VACCINATION












MAR

10
/
2002


05

-

HEART

WORM

TEST

341


MORRIS


CAT


4


SAM

COOK


JAN

23
/
2001


01

-

RABIES

VACCINATION











JAN

13
/
2002


01

-

RABIES

VACCINATION

519


TWEEDY


BIRD



2


TERRY

KIM


APR

30
/
2002


20

-

ANNUAL

CHECK

UP











APR

30
/
2002


12

-

EYE


Presentation by Marina Myburgh

30

1NF: No repeating groups, PKs


Presentation by Marina Myburgh

31

PID

PROCEDURE


01

-

RABIES

VACCINATION


10

-

EXAMINE

and

TREAT

WOUND


05

-

HEART

WORM

TEST


08

-

TETANUS

VACCINATION



20

-

ANNUAL

CHECK

UP


12

-

EYE



PET

ID


PET

NAME


PET

TYPE



PET

AGE



OWNER



246

ROVER



DOG



12

1

248

SPOT



DOG



2

2

341

MORRIS



CAT



4

1

519

TWEEDY



BIRD



2

2

PET

ID


VISIT

DATE



PROCED
URE


246

JAN

13
/
2002



1

246

MAR

27
/
2002



10

248

APR

02
/
2002



5

248

JAN

21
/
2002



8

248

MAR

10
/
2002



5

341

JAN

23
/
2001



1

341

JAN

13
/
2002



1

519

APR

30
/
2002



20

519

APR

30
/
2002



12

OID

OName



OSurname

1

SAM

COOK

2

TERRY

KIM

Relationships: FKs

Presentation by Marina Myburgh

32

Algorithms

Presentation by Marina Myburgh
for Study Opportunities

33

The following algorithm is supposed to print
the biggest number of the 3 integer values:

INPUT x, y, z

IF x>y then


IF x>z THEN PRINT x


ELSE PRINT z

ELSE PRINT Y




Find the logical errors (if any)


x

y

z

12

4

6

2

4

6

5

15

4

8

8

8

ELSE IF y > z then PRINT Y


ELSE PRINT z

5.5


Normal data: well within the acceptable range of
data


Erroneous data: numbers or text not in range
and of incorrect format


Extreme data: correct format but much
smaller/bigger than the acceptable range


Invalid data: value not in required range/ not the
correct format or data type


validation can be
used to prevent it


Incorrect data: meets the requirements for being
valid but the user inputs the wrong value by
mistake

Presentation by Marina Myburgh

34

Class design


Design a class(
es
) to use in a program
that will handle appointments at the Vet.
Use the same data that was used for the
normalisation. Use the class design
format that was used in the examinations

Presentation by Marina Myburgh

35

Class design

Presentation by Marina Myburgh

36

OWNER______

-
fID

-
Fname

-
Fsurname

___________

+constructor(
id,n,s
)

+
getID

: Integer

+
getName
():string

+
getSurnm
():string

PET______

-
pID

-
pname

-
ptype

-
pAge

___________

+constructor(
id,n,t,a
)

+
getID
: Integer

+
getName
():string

+
gettype
():string

+
getage
():string


APPOINTMENT

-
aID

-
pID

-
aDateTime

-
aProcedure

___________

+constructor(
id,p,t,pr
)

+
getID
: integer

+
getPet
(): string

+
getDatetime
(): date

+
getProc
():string

+
getbill
(): real