Hard A Menace's Guide to SQL Quiz / Test | Computers

Quiz Answer Key and Fun Facts

1. Jonnowales is three years into his four year degree course (graduated with a BSc, about to take an MSc) at the University of Quizdom (UoQ) and over the summer he has bagged himself a job with the UoQ's management information team; what a poor hiring decision! Jonnowales is a tidy enough chap but when things don't go his way, he can get a little bit petulant. Today is a good day for Jonno (new job and all), so on his first day he decides to do something benign with the table, named 'Papers', that lists every academic paper published by the establishment. Which of the following pieces of SQL would allow Jonno to extract all the columns from the 'Papers' table? Note: An asterisk can be used in SQL to represent all available columns.

Answer: SELECT * FROM Papers;

The asterisk is very useful in SQL as it does away with the necessity of having to type the name of every single column in the table. It is not always good practice to use the asterisk however as it can result in queries being more computationally expensive for no reason; sometimes only a few columns are needed and the names of these should be typed out.

SELECT and FROM are the two most fundamental keywords in SQL with respect to the manipulation of data. The correct answer, SELECT * FROM Papers, will extract all columns from the 'Papers' table as well as all the rows from the table. Statements are almost always terminated by a semicolon and even in cases where it is not required, it is still good practice to use this piece of punctuation (it becomes a habit).

2. Upon extracting all columns (and all rows) from the 'Papers' table in the university database, Jonno decides that he is only really interested in geography papers. Luckily for Jonno, the subject of a paper is captured and the data is stored in a column in the 'Papers' table called 'Category'. The 'Category' field is then populated, for every entry, with the name of the paper's subject. Which of the following statements is the only one that would successfully extract just the geography papers from the 'Papers' table?

Answer: SELECT * FROM Papers WHERE Category = 'Geography';

The WHERE clause builds upon the SELECT...FROM foundation. In this case we want to SELECT all columns, denoted by an asterisk, from the 'Papers' table but we only wish to display a subset of the rows. When a filter is required, WHERE is the go-to clause, and being familiar with how it works will take your SQL skills to the next level. In this case, the condition that needs to be met is that the paper be a geography paper. The correct syntax for that, given the existence of the 'Category' column, would be WHERE Category = 'Geography'. You'll note that in SQL, character strings need to be enclosed by apostrophes; this is not required when specifying a condition involving a numeric value.

The correct SQL in full is SELECT * FROM Papers WHERE Category = 'Geography';

3. Jonno has copied all of the university's geography papers into a new table called 'GeoPapers' but he discovers that there is no real order to the papers; it is all a bit random! He would very much like to see what papers have been cited most frequently and spots a useful column called 'Citations' which provides the number of citations for each paper. Which of the following pieces of SQL would allow Jonno to sort the papers by number of citations (lowest to highest)?

Answer: SELECT * FROM GeoPapers ORDER BY Citations;

The ORDER BY clause is useful when it comes to tidying up your dataset and is usually necessary in some form or another if you present SQL output in reports (unless a user is more comfortable using a facility such as Excel's sort). The first element is getting the information that you want, which is achieved by the SELECT...FROM combination, and the second stage is sorting it. Getting all the columns from the 'GeoPapers' table is achieved by using SELECT * FROM GeoPapers and the sorting is done by using ORDER BY Citations. ORDER BY can be used to sort data in an ascending fashion or in descending order; the default is typically ascending (ASC). In this case we wanted to sort the data by an ascending Citations, so there was no need to add anything to the ORDER BY clause; however, if you wished to sort the data in descending order then it is a requirement that you add the DESC keyword at the end of the ORDER BY clause.

Ascending Citations: SELECT * FROM GeoPapers ORDER BY Citations;
Descending Citations: SELECT * FROM GeoPapers ORDER BY Citations DESC;

4. Different database systems require the use of different syntax for some SQL clauses (which can be a nuisance!). Jonno is a fan of Oracle and he uses this particular database management system (DBMS) to find geography papers in the top 1000 of all papers published by his university by citation count. Inspecting a table called 'GeoPapers', he sees a column named 'PaperTitle' (which contains the name of each paper) and decides this may be useful. It looks as though the 'CiteRank' column will be of use as it provides a University of Quizdom ranking to every paper published by the institution based on number of times it has been cited. The most cited paper will have the top 'CiteRank' value of one. Using all of this information he puts the following code together: SELECT PaperTitle, CiteRank FROM GeoPapers WHERE CiteRank != 1000; Will this statement provide Jonno with all the geography papers that rank in the university's top 1000 papers by number of citations?

Answer: No

Unfortunately Jonnowales has got this wrong! The SELECT...FROM...WHERE syntax is expressed correctly but the operator is not right. The SQL as written would provide Jonnowales with a list of the title and rank of every paper from the 'GeoPapers' table so long as it isn't exactly ranked 1000.

This is a bit of a nonsense filter really, as it will only ever reduce the list of papers by one at the most. The operator that has specified this filter is != which represents 'not equal to', so WHERE CiteRank != 1000 means "where the rank of the paper is not equal to 1000".

In order to get those papers ranked in the top 1000, you'd need to use the less than or equal to operator (expressed with the "less than" symbol, followed by the equals).

5. Jonnowales is intrigued to find out how many University of Quizdom (UoQ) papers are associated with a "high quality" marker, a marker given if the paper is made up of content that has been well received nationally. In order to do that he checks out the 'Papers' table (which lists all UoQ papers) and focuses on the column called 'Quality'. The 'Quality' column is populated with a 'Yes' if the paper has been well received enough to earn "high quality" status, otherwise it is a 'No'. Which of the following pieces of SQL would inform Jonnowales of the number of papers that have the much-desired "high quality" marker?

Answer: SELECT COUNT(Quality) FROM Papers WHERE Quality = 'Yes';

COUNT is one of the many functions available in SQL and it pretty much does what it says on the tin. It will count the number of rows that satisfy a certain condition (or will count all rows in a table if no condition is applied by a WHERE clause). The correct code is:

SELECT COUNT(Quality) FROM Papers WHERE Quality = 'Yes';

What this piece of SQL does is filter the rows by the condition WHERE Quality = 'Yes'. Looking at it the opposite way, any paper which hasn't been received well enough to pick up the "high quality" designation will not be included in the output data set. Once the filter has been applied, the rows are then counted and the SQL output will inform you of the number of papers which have the marker.

6. Jonnowales would really like to be able to author a paper that is good enough to reach number one in the university's citation rankings table. In order to get some inspiration he searches through the 'Paper' table (which lists all the university's papers) to find the current number one paper. Given the large number of rows in this table he decides to write some SQL so the searching job is done for him! He is particularly interested in a column called 'CiteRank' which gives the rank of every paper published by the University of Quizdom (UoQ) by number of citations. Which of the following SQL statements would return ALL the details stored in the 'Papers' table (including the name of the paper stored in the column called 'PaperTitle') about the highest ranking UoQ paper? Note: Keep in mind that the most cited UoQ paper would be stored in 'CiteRank' as 1.

Answer: SELECT * FROM Papers WHERE CiteRank = 1;

The best paper across all UoQ departments is ranked number one so we wish to include WHERE CiteRank = 1 in our SQL statement somewhere. This query falls back on the trusted SELECT...FROM...WHERE syntax and doesn't require any functions whatsoever. What we want is all the details (in other words, all columns from the 'Papers' table) of the number one paper (an output of just one row). This is achieved by utilising the asterisk to represent all columns and the equals operator to correctly specify the appropriate filter. The correct SQL is:

SELECT * FROM Papers WHERE CiteRank = 1;

7. Jonnowales wishes to feature more prominently in the 'Papers' table that contains the details of every paper published by the University of Quizdom. He gets to work and, in his spare time, authors a very tedious paper based on tiny villages in Irkutsk Oblast, a paper he thinks is, without a doubt, the best, most accessible academic geography paper ever written (he also thought his BSc Geography paper was world class)! He thought it was easily worth the "Internationally Significant" designation but the mean-spirited peer review panel thought otherwise. The paper was published in the famous "GeoJournal" but didn't get the recognition it clearly deserved. As Jonnowales has often been told, if you want something done then do it yourself! Jonnowales quickly spots the column named 'IS' which is populated with a 'Yes' if a paper is believed to be "Internationally Significant" and with a 'No' if, like Jonno's fantastic Irkutsk paper, it hasn't been so lucky. Jonno decides to use his privileges to change the 'No' associated with his paper to a 'Yes', and in so doing gets his paper a prominent link on the home research page of the university's website! Which of these clauses would be the most useful in allowing him to get up to this sort of mischief?

Answer: UPDATE

SQL is one of the easiest programming languages to learn mainly because of its straightforward syntax and the obviousness of its keywords. The UPDATE keyword allows for the amendment of one or more rows in a specified table. If Jonnowales wanted to sneakily award his paper "Internationally Significant" status, he could make use of the following statement:

UPDATE Papers SET IS = 'Yes'
WHERE Author = 'Jonnowales' AND PaperTitle = 'Irkutsk Oblast: The Villages Not Even Putin Knows About';

This would work if the column that stored the name of the paper's author is called 'Author' and if Jonnowales had actually named his paper in that very creative way!

There are two further points to be made here, the first is that the above code has been split such that it is expressed over two lines. This is perfectly valid as database management systems (DBMSs) such as Oracle will ignore the white space and execute the code as if it weren't there. This is a useful feature as it allows for more elaborate SQL to be written in an easy-to-read format. The second point, which is vitally important when using the UPDATE clause, is make sure the WHERE clause is present and accurate. If the WHERE clause was absent in this particular example, Jonno's code would have designated every single University of Quizdom paper as "Internationally Significant"!

8. Needless to say that the University of Quizdom's President wasn't impressed when he found out Jonnowales had awarded his mediocre paper "Internationally Significant" status through using SQL! It should also go without saying that the President has the IT team remove the "thumb". Jonnowales decided to have a tantrum about this and said to himself, "if I can't be internationally significant, then NOBODY can be"! Jonnowales put together a piece of SQL which uses the 'Papers' table (which contains the details of all the university's papers) and the column labeled 'IS'. The 'IS' column is populated with 'Yes' if a paper has been designated "Internationally Significant" and 'No' if it hasn't. The code is as follows: DELETE FROM Papers WHERE IS = 'Yes'; What has Jonnowales done?

Answer: Deleted every one of the university's "Internationally Significant" research papers!

DELETE FROM Papers WHERE IS = 'Yes';

This piece of SQL just goes to show how simple the language is and also how powerful it can be. In ten seconds, a piece of code can be written that will wipe out a significant number of rows in a table. In this case, Jonnowales, in his petulance, deleted every internationally significant piece of research ever published by his university! If Jonnowales hadn't specified a WHERE clause, every single paper would have been deleted. That would ensure Jonno's popularity with the university's PhD students, readers and professors would plummet!

The deletion of a table can be catastrophic for a business that is reliant on websites that utilise databases; however, safeguards can of course be put into place. One of the most important is to protect your database(s) from being manipulated by way of SQL injection. SQL injection usually takes the form of a user entering a string into a badly protected web input form so that they can exploit the databases used by a website. If you are intrigued, you may find yourself lost in the world of SQL for hours!

9. The university's President found out all about Jonnowales' SQL exploits and decided that enough was enough! His first step was to get IT to restore the 'Papers' table back to the state it was in before Jonnowales messed it up. They did this by simply retrieving a view of the main table that is created daily! Jonnowales was flabbergasted that he had been so easily outsmarted and asked Bob from IT what SQL he used to create a backup copy of the 'Papers' table. Bob was gracious enough to tell him the answer. Which of the following clauses would Bob have used to create a backup copy of a table?

Answer: SELECT INTO

It is always useful to keep backups of any table or database. Creating backups can be achieved in different ways but one method makes use of the SELECT...INTO combination. As an example, the 'Papers' table could be copied quite easily by use of the following statement:

SELECT * INTO PapersCopy1 FROM Papers;

This statement creates a new table called 'PapersCopy1' which contains the same rows, columns and data as that stored in 'Papers' at the time the copy is made. Having backups helps reduce the consequences a mistake in your SQL could have on the integrity of your data...and helps protect you from Jonnowales when he is having a tantrum!

10. Once Bob had put right what Jonnowales had messed up, the university's President decided that Jonno was too much of a menace to keep around. So, he got Bob to check the 'Degree' table (which contains details of all degrees awarded by the University of Quizdom) so as to find the column that can be used to revoke a graduate's degree! The column happens to be called 'Revoke'. Switching the value of this column, for a specified graduate, from 'No' to 'Yes' would result in the specified graduate no longer being recorded as having a degree awarded by the institution (Jonno personally thinks this is a bit of overreaction). The name of the university graduate is stored in a column named 'Graduate'. Which of the following pieces of SQL would revoke Jonnowales' BSc?

Answer: UPDATE Degree SET Revoke = 'Yes' WHERE Graduate = 'Jonnowales';

Just like that, Jonnowales becomes a mere footnote in the University of Quizdom's history! A statement that modifies an existing entry in a table makes use of the UPDATE clause. The SQL required to get rid of Jonnowales' BSc is:

UPDATE Degree SET Revoke = 'Yes' WHERE Graduate = 'Jonnowales';

What this is doing is updating the 'Degree' table by setting the value of the 'Revoke' column to 'Yes' from 'No' where the graduate's name is 'Jonnowales' as specified in the 'Graduate' column. One point to emphasise is the importance of the WHERE statement; if Bob had failed to include it then he would end up revoking everybody's degree! What a disaster that would be!

Source: Author jonnowales

This quiz was reviewed by FunTrivia editor WesleyCrusher before going online.
Any errors found in FunTrivia content are routinely corrected through our feedback system.

A Menace's Guide to SQL Trivia Quiz

SQL (Structured Query Language) is a language used to interact with databases. This quiz invents some university-related tables and sees what SQL could do with the data contained within them. Enjoy!

NEXT>

NEXT>

NEXT>

NEXT>

NEXT>

NEXT>

NEXT>

NEXT>

NEXT>