- All programming languages utilise program constructs. In imperative languages they are used to control the order (flow) in which statements are executed (or not executed).
Describe and give examples of the following programming constructs:
- Sequence (top down processing)
Statements are executed in the order in which they appear in the code.
e.g. the code:
printf(”Hello,”);
printf(“ World\n”);
will output Hello, World.- Selection (also known as branching)
Which commands are executed depends on some condition, such as the value of a variable.
e.g. the code:
if(guess==answer) {
printf(“Correct”\n”)
}
else {
printf(“Wrong, try again\n”);
}
compares the values of two variables (guess and answer, classically this will be a simple game where a player guesses a random number), if they are the same (the guess is correct) then the code outputs Correct, if they are different then the code outputs Wrong, try again
- Repetition (also known as iteration or looping)
The same commands are executed multiple times, either a fixed number of times or until some condition is met.
e.g. the code:
for(i=0; i=9;i++)
{
printf(“Step %i\n”);
}
will print the numbers 0 to 9 then stop.
- Explain the following programming terminology
- Modularisation or modular programming
Functionality of a program is divided into separate modules containing related functions/procedures with documented interfaces between them. This encourages reuse of code, rather than including the code to perform an action every time it is needed one standard function or procedure can be called, and allows multiple programmers to work on the program at the same time as each module will be a separate source code file. In many modern operating systems modularization supports dynamically loaded libraries where the code for a function is not loaded unto memory until it is needed, so minimizing the amount of memory used for running program code.
- Coupling
Coupling refers to how tightly two different modules are dependent. The more tightly they are coupled the more difficult it can be to reuse independently and the more likely that changes to one will require changes to the others. Generally coupling should be minimized.
- Cohesion
Cohesion relates to how closely the functions in a module are related. At the lowest level there is little or no relationship between functions, any relationship is purely coincidental, at the other extreme all functions in a module may be used to deliver the same process.
- Each column in a database table is required to have a name and datatype. The datatype is a guideline for SQL to understand what type of data is expected inside of each column. It also identifies how SQL will interact with stored data.
There are 3 main data types:
- Give 2 examples of a STRING datatype
CHAR(N) – A fixed length string of characters in the default character set for the database, a variation of this is NCHAR(N) which uses the National Language Character Set for the database. The length is set by the number in parenthesizes. As these are fixed length character types they are best suited to strings that are similarly fixed in length (e.g. an invoice reference with a fixed format) to minimize wasted space which may impact performance, although modern enterprise database systems will often silently convert CHAR(N) fields to VARCHAR(N) in the background. In some older technologies which may still be in use for legacy systems, the CHAR(N) datatype may only be store characters that map to the 8 bit ASCII/ANSI character set values (0-254) so may not be able to store larger charactersets such as UNICODE, this can be resolved by using the NCHAR(N) datatype, if available.
VARCHAR(N) – A variable length string of characters in the default character set (or for NVARCHAR(N) the National Language Character Set) for the database. The maximum length is set by the number in parenthesizes, some database systems implement the use of the word MAX as an alternative to setting a maximum size which defaults to the maximum size permissible for the platform. Due to it’s variable length this is suited to most character string data such as names, descriptions, addresses (or parts of addresses) or any string where you do not know how long it will be or the length may vary greatly. - Give 2 examples of a NUMERIC datatype
INT – An integer (whole number) value. Often this will be split into short and long integers and signed (can be above or below zero) and unsigned (zero or greater only). The maximum value that can be stored in each is determined by the platform. INT datatype can be used for any numeric value that has no fractional part. In particular countable objects. - FLOAT/DOUBLE – A decimal number (i.e. one that can have a fractional part) stored as a multibyte construct. The number of bytes determines the size of number that can be stored, based on the precision (number of significant digits), DOUBLE is typically implemented as FLOAT with a high precision. Larger numbers will usually be depicted in scientific notation (N*10^E). FLOAT/DOUBLE datatypes can be used for any numerical value in particular where a fractional part is needed such as currency, weights or other measurements or where the value is outside the range supported by INT. If the fractional part is not needed then an INT type should be used if the value falls in the supported range, as they typically require less memory to store.
Give 2 examples of a DATE and TIME datatype
- DATETIME/DATE/TIMESTAMP – Represents a calendar date, with or without time element. Due to differing formats for dates in different regions dates are typically stored as numeric value, counting seconds or, more usually, fractional seconds since a fixed point in the past, which date varies from system to system common examples are 1st January 1900, 1st January 1000, 1st January 1970 (the UNIX Epoch) or 14th September 1752 (when the UK switched from the Julian to Gregorian calendar). TIMESTAMP is a special case of DATETIME and often has system level rules associated with it. Typically, a field of TIMESTAMP would not be updated by the application but by a trigger or similar on the table to provide a record of when a field was last updated. Dependent on the system DATE may be a separate datatype or may just be a synonym for DATETIME. DATE/DATETIME/TIMESTAMP are suited to storing calendar dates such as dates of appointments, anniversaries, events &c. They are not generally well suited to storing elapsed time such as time between events or how long some thing has lasted, in particular where the start and end may be on different calendar days, or there is a chance of a negative value. These are usually better stored as a FLOAT or INT and custom processing/formatting used to output the results.
- TIME – Implementation of this datatype varies from system to system so care must be used. In many cases (Microsoft Excel being a common one) it is a DATETIME but the date portion is ignored so 12.5 hours plus 12.5 hours will give a result of 1 hour, not 25, as it will have carried 24 of the hours to the days element. In most cases this can be worked around by applying formatting to the output of the calculation (e.g. in Excel selecting the Time format (hh:mm) for a field will display the result of the above calculation as 01:00 but using a custom format of [hh]:mm will display 25:00). Alternatively TIME may be implemented as a numeric type, usually number of seconds, with custom formatting for output. It may be suited to holding elapsed time.
- There are numerous SQL commands for manipulating data.
- What would be the reason for using the SELECT statement?
A select statement is mostly used to extract data from one or more tables. Some database management systems support SELECT FOR UPDATE as part of transactional integrity. This is particular useful for forms based applications where a user may need to update a specific record but it might take some time. Using SELECT FOR UPDATE locks the record(s) to prevent other transactions being able to update them until the transaction is either committed or rolled back. This can cause problems if the query is not specific enough as if you lock too many records (generally due to poor application design and/or developers who don’t understand multiuser databases) then this can cause other transactions that need to update a locked record to wait until the locking transaction is committed or rolled back.
Please provide an example of a SELECT statement and an example of a SQL command using the FROM clause
select a.Asset_tag
, a.Allocated_User
, a.User_ID
, a.Last_logged_on_user_name
, a.Last_Logged_On_User_ID
, a.Most_Frequent_User_Name
, a.Most_Frequent_User_ID
, a.Directorate
, a.Division
, a.Section
from dbo.HW a
- What would be the reason for using the SELECT * statement?
SELECT * returns all columns in a table in the order they appear (typically the order they were in the initial create table command). You would use this where you want to get all of the data in a table such as to export to a text file or to back up a table before carrying out a change
Please provide an example of a SELECT * statement
Select * from emp
Create table emp_backup as select * from emp
Give an example of a SQL command using the WHERE clause
select [Asset_tag], State, Substate, [Allocated_User], Division, Section, [Operating_System], b.mail, Model
from HW4 a
left outer join allocated_Email b on (a.[User_ID] = b.[User Name])
where Directorate = ‘Capita ICTDS’
and Division not in (‘Link2ICT’)
order by [Allocated_User]
- When we combine logical operators such as AND / OR / NOT with the WHERE clause, these are known as compound conditions
Give an example of a SQL command using the AND clause
select [Asset_tag], State, Substate, [Allocated_User], Division, Section, [Operating_System], b.mail, Model
from HW4 a
left outer join allocated_Email b on (a.[User_ID] = b.[User Name])
where Directorate = ‘Capita ICTDS’
and Division not in (‘Link2ICT’)
order by [Allocated_User]
Give an example of a SQL command using the OR clause
select asset_tag, model, model_category, Manufacturer, directorate, division, Section, User_ID, Most_Frequent_User_ID, Last_Logged_On_User_ID
from HW
where User_ID like ‘kios%’
or Most_Frequent_User_ID like ‘kios%’
or Last_Logged_On_User_ID like ‘kios%’
or User_ID like ‘Kios%’
or Most_Frequent_User_ID like ‘Kios%’
or Last_Logged_On_User_ID like ‘Kios%’
Give an example of a SQL command using the NOT clause
select GETDATE() as Date, max(DaysOpen) as OldestOpenCall
from EngineerCallTracking
where State not in (‘Resolved’, ‘Closed’)
select GETDATE() as Date, max(DaysOpen) as OldestOpenCall
from EngineerCallTracking
where State != ‘Resolved’
- Why would we use the LIKE operator in a SQL command?
The like operator is used with wildcard characters to filter records that contain a particular string of characters.
Give an example of a SQL command using the LIKE operator
select asset_tag, model, model_category, Manufacturer, directorate, division, Section, User_ID, Most_Frequent_User_ID, Last_Logged_On_User_ID
from HW
where User_ID like ‘kios%’
or Most_Frequent_User_ID like ‘kios%’
or Last_Logged_On_User_ID like ‘kios%’
or User_ID like ‘Kios%’
or Most_Frequent_User_ID like ‘Kios%’
or Last_Logged_On_User_ID like ‘Kios%’
- List at least 3 wildcard characters and explain their usage.
The exact wild card characters used vary from DBMS (database management system) to DBMS. The ISO standard specifies only 2 wildcards (‘%’ and ‘_’) but others are implemented by different DBMS.
%/* – Matches any zero or more characters, often used when you are looking for a string within another string e.g. ‘Fred%’ will match any string that starts with ‘Fred’ so will match ‘Fred’, ‘Fred ‘, ‘Fred Bloggs’ and ‘Frederick Bloggs’ but not ‘Bloggs, Fred’, but ‘%Fred’ will match ‘Fred’ and ‘Bloggs, Fred’ but not ‘Fred ‘, ‘Fred Bloggs’ and ‘Frederick Bloggs’
_ – Matches any one and only one character so ‘Fred_’ will match any string starting with Fred and followed by any single character so will match ‘Fred ‘ but not ‘Fred’, ‘Bloggs, Fred’, ‘Fred Bloggs’ and ‘Frederick Bloggs’.
[] – Matches any occurrence of one character between the square brackets. E.g. ‘Fr[eo][dg]’ will match ‘Fred’ or ‘Frog’ (it will also match ‘Frod’ and ‘Freg’ but not being words in the English language they should not appear, presuming the string is in English)
- Why would we use the keyword ‘ORDER BY’ in a SQL command?
Please provide an example
Order by sorts the columns specified so would be used any time the order of the data is important for example when you want similar items to appear together, lowest price to highest price, most expensive to cheapest &c. By default sorting will be ascending (alphabetical/smallest to largest) but this can be switched to descending (reverse alphabetical/largest to smallest) by adding ‘desc’ following each field you wish to reverse the sort order for.
- A function is a predefined formula which takes one or more arguments as input then processes the arguments and returns an output.
There are two types of SQL functions
- aggregate functions
- scalar(non-aggregate) functions
An SQL aggregate function calculates on a set of values and returns a single value. For example, the average function ( AVG) takes a list of values and returns the average.
Describe the following aggregate functions giving examples
- COUNT – returns the number of rows returned by a query. Count(*) or count(1) will return the total number of rows whilst count([field name]) will return the number of rows where the field identified by [field name] is not null. select count(*) from sales;
- AVG – return the average (arithmetic mean) of the values of a numeric field. Select avg(price) from sales;
- SUM – returns the total of the values of a numeric field. Select sum(price) from sales;
- MIN – returns the minimum of the values of a numeric field. Select min (price) from sales;
- MAX – returns the maximum of the values of a numeric field. Select max(price) from sales;
- Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the result set into groups of values and the aggregate function returns a single value for each group.
Describe the GROUP BY statement giving examples
The group by clause must include all non-aggregated fields used in the query, e.g.
Select product_category, date_of_sale, sum(price) as sum_of_sales
From salesInventory
Group by product_category, date_of_sale;
Group by carries out an implicit sort on the data, however this should not be relied upon.
- Non-aggregate or scalar functions operate on each record independently
These functions are based on user input and return a single value
Describe the following non-aggregate functions giving examples
- ROUND() – Rounds a number up or down to the specified number of significant figures, e.g. round(3.1415, 2) will give 3.14 and round(3.1415) will give 3.142
- CAST() – Converts from one data type to another, e.g. cast(‘1234’ as int). This is useful where different systems might store the same data in different types, or the Export/Import process may have carried out an implicit conversion incorrectly, and you now need to compare them, for example where one system stores invoice numbers as an integer and another as a string.
- CONVERT() – Converts from one datatype to another, e.g. convert(nvarchar(9), today(), 103) will return a text string in the dd/mm/yyyy format. Convert() differs from Cast() in that you can use the optional third parameter to specify the format of the data.
- ISNULL() – returns true if the field is null and false if it is not null, e.g. isnull(Complete).
- 13. What is the meaning of ‘implicit data conversion’?
If using fields of two different data types in a calculation/comparison then one will, if possibe, be converted by the software to the datatype of the other. E.g. if adding an integer to a float the integer will be promoted to a float and then the addition will occur.
- There are many built-in functions in SQL to do various calculations on data.
Describe the following built-in function giving an example
CASE
Case replaces lengthy if…the…else if…then…else… statements where you need to branch in a variety of possible ways based on the value of a variable.
e.g.
set @RetValue =
case
when @outcome = 0 then ‘None OK’
when @outcome = 1 then ‘Processor OK, Disk and RAM not OK’
when @outcome = 2 then ‘Disk OK, Processor and RAM not OK’
when @outcome = 3 then ‘Disk and Processor OK, RAM not OK’
when @outcome = 4 then ‘RAM OK, Disk and Processor not OK’
when @outcome = 5 then ‘RAM and Processor OK, Disk not OK’
when @outcome = 6 then ‘RAM and Disk OK, Processor not OK’
when @outcome = 7 then ‘All OK’
else ‘Something broke’
end;
Each comparison is executed in order, so if more than one is a match the first will be the one taken, until a match is found or the else statement is reached, the else statement is executedif no match is found.
- A SQL expression is a combination of one or more values, operators and SQL functions that evaluate to a value. These SQL EXPRESSIONs are like formulae and they are written in query language. You can also use them to query the database for a specific set of data.
There are 3 different types of SQL expressions
Please give an example of a SQL Boolean expression
Select employeeName from emp where Salary =10000;
Select userRoleTitle from users where username = ‘fred’;
Select employeename, startdate from emp where endDate is not null;
Please give an example of a SQL Numeric expression
Select 2 + 3 from dual;
Select employeeName, salary + bonus as totalIncome from emp;
Please give an example of a SQL Date expression
Select today() from dual;
- A JOIN clause is used to combine rows from two or more tables, based on a related column between them
Describe and give examples for the use of the following JOIN keywords
- INNER JOIN – Joins two tables and returns those rows where the key value used appears in both tables, e.g. select a.product_name, a.RRP, b.DateOfSale, b.SaleQuantity from product_catalogue a inner join sales_ledger b on (a.sku = b.sku); will return all those products in the catalogue table for which there is at least one sale and the date and quantity of each sale. It will not return any products with no sales or any sales that do not match a product in the catalogue.
- LEFT JOIN – Joins two tables and returns all rows in the table to the left of the join and any matching rows in the table to the right of the join with null values in the sales related fields for those products without sales, , e.g. select a.product_name, a.RRP, b.DateOfSale, b.SaleQuantity from product_catalogue a left join sales_ledger b on (a.sku = b.sku); will return all products in the catalogue, even if they do not have any sales, and just those rows in the sales table that match a product in the catalogue. It will not return any sales that do not match a product in the catalogue.
- RIGHT JOIN – Joins two tables and returns all rows in the table to the right of the join and any matching rows in the table to the left of the join with null values in the product related fields for those sales that do not have related products, , e.g. select a.product_name, a.RRP, b.DateOfSale, b.SaleQuantity from product_catalogue a right join sales_ledger b on (a.sku = b.sku); will return all rows in the sales table even if they do not match a product in the catalogue and only those products that have at least one sale. It will not return any products that do not have sales.
- FULL JOIN – Joins two tables and returns all rows in both tables with null values in the appropriate fields for those products that do not have sales and for those sales that do not have associated products, , e.g. select a.product_name, a.RRP, b.DateOfSale, b.SaleQuantity from product_catalogue a full join sales_ledger b on (a.sku = b.sku);. Will return all rows in the catalogue table that do not have sales, all rows in the catalogue table for those products with sales and the associated sales information and all rows in the sales table that do not have associated products in the catalogue.
- When would we use the UNION operator?
UNION is used to merge the output from two queries (usually with the same structure) into a single output, removing any duplicate rows. It might be used where you have a working transactions table where periodically, for performance reasons, old transactions are moved into an archive table and you wish to get results from both as a single output.
Please give an example
select a.product_name, a.RRP, b.DateOfSale, b.SaleQuantity from product_catalogue a inner join sales_ledger b on (a.sku = b.sku) where a.product_category=’BOOK’
UNION
select a.product_name, a.RRP, b.DateOfSale, b.SaleQuantity from product_catalogue a inner join sales_ledger_archive b on (a.sku = b.sku) where a.product_category=’BOOK’
- When would we use the SELECT INTO clause?
Select into is used to copy data from one table to another. This is particularly useful if you want to take a sample of table to test a script or back up a table before an update incase you need to recover from a problem.
Please give an example
Select * into newUsersTable from usersTable
- When would you use the following SQL keywords?
Please give examples
- TOP – Limits the output to the top N rows of the output, often used with an order by clause. This is not supported by all database management sytems, e.g. select top 10 product_name, numSales from sales_volumes order by numSales;
- LIMIT – Similar function to TOP implemented in MySQL, which does nto support TOP, e.g. select product_name, numSales from sales_volumes order by numSales limit 10;
- ROWNUM – Used in Oracle, ROWNUM is a pseudo-variable the identifies where a row appears in the output from a query, it can be used in a where clause to simulate TOP, e.g. select product_name, numSales from sales_volumes order by numSales where rownum< 11; It can also be used where you are interested in a specific row in the output or range of rows (e.g. on a message board where there are 25 messages listed per page so for page 3 you want rows 51 to 75.
- Explain the term SQL Subquery and the rules that a subquery must follow
A SQL subquery is a query within a main query where the output of which is fed into the main query. They can be used anywhere an expression can be used subject to certain rules such as if used in a where clause the field datatype the output must be of a compatible type with the field to which it is compared, the output cannot contain certain large datatypes (e.g. image, blob &c) and the order by clause cannot be used except to enable a top clause to be used.
Please provide an example of a subquery
Select devicename, assigneduser
From devices
Where assigneduser not in (select username from emp where location=;’WFH’);
Suggested websites
https://www.w3schools.com/sql/default.asp
https://www.tutorialspoint.com/sql/index/htm
https://www.techonthenet.com/sql/index.php