WorksheetsSQL 3
Total questions: 14
Worksheet time: 10mins
SQL stands for (a) .
Which of the following best describes the function of a primary key in a database?
A method to secure database access
A unique identifier for each record
A way to enhance the performance of database queries
A tool for defining relationships between tables
Select the appropriate SQL query to show...
The title and description of all reviews for products that cost less than £100.
SELECT title, description
FROM Product, Review
WHERE Product.ProductID = Review.ProductID
AND Price < 100
SELECT title, description
FROM *
WHERE Price < 100
SELECT *
FROM Product, Review
WHERE Product.ProductID = Review.ProductID
SELECT title, description
FROM Product
WHERE Product.ProductID = Review.ProductID
AND Price <= 100
Which word is missing from the following SQL statement?
SELECT * table_name
WITH
WHERE
FROM
AND
How would we script a SQL query to select "Description" from the Item table?
SELECT Item.Description
EXTRACT Description FROM Item
SELECT Item FROM Description
SELECT Description FROM Item
Which query would return only the fields DesignerID and Designer?
SELECT * FROM Designers
SELECT DesignerID, Name FROM Designers
SELECT DesignerID AND Designer FROM Designers
SELECT DesignerID, Designer FROM Designers
You are required to update the phone number for only the DesignerID "SMI01" in the "Designer" table. Which of these would successfully do that?
UPDATE Designer SET PhoneNo = '01224123456'
UPDATE Designer (PhoneNo) VALUES ('01224123456')
UPDATE Designer SET PhoneNo = '01224123456' WHERE DesignerID = 'SMI01'
UPDATE PhoneNo = '01224123456' FROM Designer WHERE DesignerID = 'SMI01'
With SQL how can I return all items in the Item table sorted from the lowest priced to the highest priced?
SELECT * FROM Items ORDER BY Price ASCENDING
SELECT * FROM Items ORDER BY Price ASC
SELECT * FROM Items BY Price LOWEST TO HIGHEST
SELECT * FROM Items ORDER BY Price DESC
How would you display Chairs in the Items table that have a Price greater than £50.
SELECT * FROM Items WHERE Type = 'Chair' AND Price > 50
SELECT * FROM Items WHERE Type = 'Chair' OR Price < 100
SELECT * FROM Iterms WHERE Type = 'Chair' AND Price >= 50
SELECT * FROM Items WHERE Price > 50
Which SQL statement would add a record to the database?
INSERT INTO Designer VALUES ('SMI01', 'M Smith', 'mail@msmith.com', '01224123456')
INSERT VALUES ('SMI01', 'M Smith', 'mail@msmith.com', 01224123456') INTO Designer
INSERT ('SMI01', 'M Smith', 'mail@msmith.com', '01224123456') INTO Designer
INSERT Designer INTO VALUES (''SMI01', 'M Smith', 'mail@msmith.com', '01224123456')
(a) <list of fields> (b) <name of table> (c) <logical condition> (d) <field name> (e)
When using the UPDATE command, you need to specify the fields to update.
Why don't you need to do this for the DELETE command?
Because you can only delete the entire record
Because DELETE has additional logic
Because UPDATE is more flexible
Because this functionality is not yet in SQL
In databases, what is a foreign key?
A field in a table that's a primary key in another table
A field that uniquely identifies each record
A key that is used to encrypt information in the database
An optional field that can be copied between tables
To get information from more than one table we need to join them together.
In this case, what TWO jobs does the 'WHERE' clause do? (PICK 2)
Implicitly join the tables
Filter the results according to a logical condition(s)
Copy foreign keys between tables to make links
Sort the results ascending or descending
