PRACTICAL PAPER: SQL Vehicles table

1. PRACTICAL PAPER: SQL Vehicles table Presentation by Marina Myburgh

2. Rentals table Presentation by Marina Myburgh

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

11. 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

12. 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

13. 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

14. #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

15. 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

16. 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

17. 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

18. 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

19. 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

20. 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

21. 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

22. 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

23. 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

24. 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

25. 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

26. 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

27. 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

28. 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

29. 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

30. Exercise Presentation by Marina Myburgh

31. 1NF: No repeating groups, PKs Presentation by Marina Myburgh

32. Relationships: FKs Presentation by Marina Myburgh

33. 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

34. 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

35. 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

36. 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 -aDateTime -aProcedure ___________ +constructor(id,p,t,pr) +getID: integer +getPet(): string +getDatetime(): date +getProc():string +getbill(): real Presentation by Marina Myburgh