Preview

14 - Data storage and SQL

 1. Have a look at the example provided below and the sql command at the start. This selects ___________ (*) from the Customers database.
>>SELECT * FROM Customers	

This example assumes there is a database created called ‘Customers’ 
with columns(fields) called:

•	CustomerID
•	CustomerName
•	ContactName
•	Address
•	City
•	Country

  all the stars (*)

  anything beginning with a specific letter, in this case C

  everything

  the id numbers

 2. What is a database primarily used for?

  To store and organise data, as well as perform useful operations on it

  only to store data

  to data mine

  to give data a base

 3. A database in which all the data is stored in a single table is known as a _______________database. The following database has ______ records.
Name      Age      Profession
==============================
Ben       33       Doctor
Monica    42       Teacher
Rita      41       Software Developer

  flatfile / 7

  relational / 2

  flatfile / 3

  flatfile / 18

 4. A ____________ database has more than one table and the tables are linked using key fields. A unique key field like Customer ID is called a ____________ key.
uploads/Rel_db.PNG

  flatfile / secondary

  relational / linking

  relational / primary

  relational / record

 5. The example below shows a table storing the names and results of three different people. The SQL command: SELECT Name from Results will return ...
Note: Assume 'Results' is the name of the table we are referring to
uploads/sql.png

  Bob

  Name

  78,87,78

  Bob, Cecil, Nora

 6. What will the following SQL statement (refer to the table in the previous question) return?
SELECT * FROM Results WHERE Name="Bob"

  Bob

  All names in the database with the results

  78

  Bob 78

 7. Again, refer to the table in the question (with the three individuals and their results). What will the following SQL statement return?
SELECT * FROM Results WHERE Name="Cecil" or Result="78"

  It will not work as WHERE can only be used on the same field.

  It will return just the name 'Cecil'

  It will return the name Cecil and Cecil's result

  It will return all the results in the database (names and results)

 8. We can also use SQL commands to find partial matches using LIKE. Fill in the blanks for the below.
WHERE CustomerName LIKE 'a%' finds any values that _________ with a
WHERE CustomerName LIKE '%a' finds any values that _________ with a
WHERE CustomerName LIKE '%or%' finds any values that have 'or' in ______ position
WHERE ContactName LIKE 'a%o' finds any values that start with 'a'  and end with ______

  start and end /starting / containing / t /

  start /ending / without / t /

  end /starting / containing / t /

  start / end / any / o /

 9. A movie rental store keeps information about each of its movies in a database. The table shows the first two entries from the 'movies' table. Write a SQL query to return the titles of all 'Thriller' movies in the database.
ID-Number   Title        Release-Date   Length   Genre    Rating
------------------------------------------------------------------
0001        Shrek          2012		      82    Animated    PG
0002        Hope Floats    2016           65    Thriller    15   

  SELECT titles WHERE Genre=Thriller

  SELECT ID-Number from movies WHERE Genre="Thriller"

  SELECT all titles from movies WHERE "Thriller(Genre)

  SELECT Title FROM movies WHERE Genre="Thriller"

 10. Create an SQL query that will return all titles and lengths of all the movies that have fewer than 99 minutes of run time (length) and a rating of PG
ID-Number   Title        Release-Date   Length   Genre    Rating
------------------------------------------------------------------
0001        Shrek          2012		       99    Animated    PG
0002        Hope Floats    2016           165    Thriller    15   

  SELECT Title, Length FROM movies WHERE Length < 99 AND Rating="PG"

  SELECT * FROM movies WHERE Length AND Rating=< 99 AND "PG"

  A SQL query as complex as this cannot be generated.

  SELECT FROM movies WHERE < 99 AND "PG"