PRACTICAL PAPER: SQL Vehicles table

1 / 36

# PRACTICAL PAPER: SQL Vehicles table - PowerPoint PPT Presentation

PRACTICAL PAPER: SQL Vehicles table. Rentals table. Complete the SQL statements:. 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 .

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'PRACTICAL PAPER: SQL Vehicles table' - nita

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
PRACTICAL PAPER: SQL Vehicles table

Presentation by Marina Myburgh

Rentals table

Presentation by Marina Myburgh

Complete the SQL statements:

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

Presentation by Marina Myburgh

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”

Select distinct animal from tblSightings

Presentation by Marina Myburgh for Study Opportunities

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

1.Complete the SQL statements:
• List the car registration numbers of all cars rented in February
• 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

Presentation by Marina Myburgh for the Road show 2010

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

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

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

• Regnum := inputbox(‘Input’,’Enter car reg num’,’BB53HCGP’);
• Qry.sql.text := ‘SELECT * FROM vehicles
• where reg_num = “’+ regnum + ‘”’;

HKT 359 GP

Presentation by Marina Myburgh

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

• VehicleID := inputbox(‘Input’,’Enter vehicleID’,’1’);
• Qry.sql.text := ‘SELECT * FROM vehicles
• where vehicleID = ’+vehicleID ;

1

Presentation by Marina Myburgh

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

• sdate := inputbox(‘Input’,’Enter date’,’2004/09/13’);
• Qry.sql.text := ‘SELECT * FROM rentals
• where start_date = #’+ sdate +‘#’;

2004/09/13

Presentation by Marina Myburgh

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

Question 2
• We should aim to teach OOP in a way so that learners develop more understanding.
• Some 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 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

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)

X = random(6)+1

X = random(101)+100

X = random(21)-10

Presentation by Marina Myburgh

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

Define the following:
• podcasting
• mobi
• superscalar
• partitioning
• defragmentation
• spoofing
• instruction set
• firewall
• anti-virus
• server
• switch.

Presentation by Marina Myburgh for the Road show 2010

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

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

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

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

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

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

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

• 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

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

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

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

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

Exercise

Presentation by Marina Myburgh

1NF: No repeating groups, PKs

Presentation by Marina Myburgh

Relationships: FKs

Presentation by Marina Myburgh

Algorithms

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)

ELSE IF y > z then PRINT Y

ELSE PRINT z

Presentation by Marina Myburgh for Study Opportunities

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

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

Class design

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

-aProcedure

___________

+constructor(id,p,t,pr)

+getID: integer

+getPet(): string

+getDatetime(): date

+getProc():string

+getbill(): real

Presentation by Marina Myburgh