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

  everything

  anything beginning with a specific letter, in this case C

  all the stars (*)

  the id numbers

 2. What is a database primarily used for?

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

  to data mine

  to give data a base

  only to store data

 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

  relational / 2

  flatfile / 3

  flatfile / 7

  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

  relational / primary

  flatfile / secondary

  relational / record

  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, Cecil, Nora

  78,87,78

  Bob

  Name

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

  Bob

  Bob 78

  78

  All names in the database with the results

 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 not work as WHERE can only be used on the same field.

  It will return the name Cecil and Cecil's result

 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 ______

  end /starting / containing / t /

  start / end / any / o /

  start and end /starting / containing / t /

  start /ending / without / 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 all titles from movies WHERE "Thriller(Genre)

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

  SELECT titles WHERE Genre=Thriller

  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 FROM movies WHERE < 99 AND "PG"

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

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

  A SQL query as complex as this cannot be generated.


Report an error