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

  anything beginning with a specific letter, in this case C

  the id numbers

  everything

  all the stars (*)

 2. What is a database primarily used for?

  only to store data

  to data mine

  to give data a base

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

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

  flatfile / 3

  flatfile / 18

  relational / 2

 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

  relational / record

  relational / primary

  flatfile / secondary

  relational / linking

 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

  78,87,78

  Bob, Cecil, Nora

  Name

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

  78

  All names in the database with the results

  Bob

  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 return just the name 'Cecil'

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

  It will return the name Cecil and Cecil's result

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

 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 /

  start / end / any / o /

  end /starting / containing / t /

 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"


Report an error