NEW
Font size
WorksheetsN5CS: Database Implementation
Total questions: 77
Worksheet time: 39mins
Before studying the implementation of relational databases, you should already be familiar with the concept that information can be stored in what type of database?
Hierarchical database
Object-oriented database
Flat-file database
NoSQL database
Which of the following SQL operations are you expected to describe and implement for pre-populated relational databases with a maximum of two linked tables?
SELECT, CREATE, JOIN, DELETE
SELECT, INSERT, UPDATE, DELETE
SELECT, ALTER, DROP, INSERT
CREATE, INSERT, UPDATE, TRUNCATE
When constructing queries, which of these SQL keywords are you expected to use to filter results based on specific conditions?
FROM, HAVING, GROUP BY
SELECT, JOIN, LIMIT
FROM, WHERE, ORDER BY
INSERT, UPDATE, DELETE
What type of join is used to select data from two tables based on a common field?
Outer join
Cross join
Equi-join
Natural join
What is the primary key of the hairdresser table in the given database schema?
firstname
contactnumber
hairdresserid
salon
What is the foreign key in the client table that references the hairdresser table?
clientid
clientfirstname
hairdresserid
phonenumber
If the row for Huda Quhshi is removed from the hairdresser table and referential integrity is enforces, what impact will it have on the client table?
There will be no impact on the client table.
A new hairdresser will be automatically assigned to the clients.
All records with the related foreign key will be deleted
The contactnumber for clients will be removed.
Which of the following best defines referential integrity in the context of a relational database?
It is the uniqueness of the primary key in any given table.
It is the relationship between two tables that checks that a foreign key in one table is a primary key in another table.
It is the relationship between two tables that allows for duplicate foreign keys.
It is the process of creating new tables from existing ones.
What does enforcing referential integrity ensure in a database?
It ensures that all primary keys have unique values.
It ensures that there cannot be foreign key values which do not have a corresponding primary key value in the linked table.
It ensures that all foreign keys have unique values.
It ensures that all tables must have the same number of rows.
What is the consequence of deleting a row in the 'hairdresser' table that has a primary key value referenced in the 'client' table if cascade delete is not enforced?
It will update the corresponding rows in the 'client' table.
It will create inconsistent rows in the 'client' table.
It will delete the 'client' table entirely.
It will prevent new rows from being added to the 'hairdresser' table.
What is meant by cascade delete?
A process that updates foreign keys when a primary key is changed.
A process that prevents deletion of a row with a primary key that is referenced by a foreign key.
A process that deletes all the rows in the linked table with the related foreign key when a row with the primary key is deleted.
A process that creates a backup of the deleted data.
What is the process called when there is a need to update the primary key value and all the linked foreign keys are also updated?
Primary update
Foreign key update
Cascade update
Key synchronization
If Sharon Watt's primary key in the 'hairdresser' table is changed from 2019 to 3982, what will be her new foreign key value in the 'client' table?
2019
1928
3982
The primary key value in the 'client' table will not change
What is the impact of deleting the first row of the pricelist table (i.e., code = 2374) which ONLY changes data in the pricelist table?
Cascade deletes will be activated and all related rows in the order table will be deleted.
Cascade updates will set the related foreign keys to an empty value.
Referential integrity has been broken and foreign keys in the order table no longer have linked primary key values.
Referential integrity ensures that the related foreign keys will be allocated the next available primary key value from the pricelist table.
What type of operation is this an example of when the primary key value is updated in the user table for the user with a userid = 72620, and the records in the post table with a userid = 72620 are also updated?
cascade update.
cascade delete.
entity referencing.
lookup use.
If a row in the car table is deleted, what will happen in the carcollector table?
The row with the related primary key value will be deleted.
The primary key in carcollector, with the same value, will be set to empty.
Nothing will happen.
A row will be added to include the deleted value.
What is the Microsoft Access database software normally used for?
Internet-based applications
Standalone databases not accessed from the internet
Large-scale commercial systems
Mobile applications
Which command language does Microsoft Access use for retrieving, updating, and deleting data from the database?
Python
SQL
VBA
HTML
What is the first step to create an SQL query in Microsoft Access 365 ?
Open the File menu
Display the Create ribbon and then choose Query Design
Close the table selection dialogue
Save the database file
Which view should you select to write SQL code in Microsoft Access as shown in the image?
Datasheet View
Design View
SQL View
Make Table
Which SQL command is used to select all records from the 'client' table?
SELECT * FROM client
INSERT INTO client
UPDATE client SET
DELETE FROM client
How can you execute a query in the Access software shown in the image?
Click the 'Run' button
Press the 'F5' key
Select 'Execute' from the 'File' menu
Right-click on the table and select 'Run Query
What will be displayed as a result of executing a query in the Access software?
The design view of the query
The SQL command text
An answer table with the query results
A confirmation message
What is the purpose of the SQL keyword SELECT?
Add a new row or rows into a table.
Displays all or specific columns from a table.
Amend existing rows in a table.
Delete rows from a table.
Which SQL keyword is used in a query to specify which table or tables to get the data from?
WHERE
ORDER BY
FROM
UPDATE
What symbol must a SQL statement always be terminated with?
A comma (,)
A semi-colon (;)
A period (.)
A colon (:)
What is the purpose of the ORDER BY clause in a SQL statement?
Sort the column data into ascending or descending order.
Specify which table or tables to get the data from.
Uses comparators and operations to identify and select only the data required.
Amend existing rows in a table.
Which SQL keyword is used to remove records from a table?
INSERT
UPDATE
SELECT
DELETE
The following SQL is an example of a ...
clause
keyword
program
statement
SQL statements are terminated with a:
colon
full-stop
semi-colon
space
Each SQL statement can be broken down into separate ...
lines
lists
clauses
statements
What will be the result of the SQL query shown?
It will display the userid and accountname from the user table in ascending order by userid.
It will display the userid and accountname from the user table in descending order by accountname and then ascending order by userid.
It will display all columns from the user table.
It will display the userid and accountname from the user table in random order.
What does the asterisk (*) symbol represent in an SQL SELECT statement?
It represents a multiplication operation.
It selects only the first column of a table.
It is used to select every column from a specified table.
It indicates an error in the query.
Which SQL statement would you use to display all records from the client table?
SELECT clientid, clientlastname, clientfirstname FROM client;
SELECT * FROM client;
SELECT clientname FROM client;
SELECT clientid FROM client WHERE clientid = 1;
Which SQL statement correctly displays just the clientid column from the client table?
SELECT clientid FROM client;
SELECT clientid FROM hairdresser;
SELECT * FROM clientid;
SELECT client FROM clientid;
Which SQL statement correctly displays just the contactnumber and salon columns from the hairdresser table?
SELECT contactnumber, salon FROM hairdresser;
SELECT contactnumber FROM salon;
SELECT salon, contactnumber FROM client;
SELECT * FROM hairdresser;
What is the result of executing the SQL statement "SELECT * FROM client, hairdresser;"?
It displays all the rows from the client table only.
It displays all the rows from the hairdresser table only.
It displays all the rows from both the client and hairdresser tables with a matching value in each.
It displays all the rows from the client table, immediately followed by all the rows from the hairdresser table.
What type of join is used in National 5 Computing Science to display all the columns from both tables where the value of the primary key in one table is the same as the foreign key in the other table?
Outer join
Inner join
Equi-join
Cross join
Which SQL keyword is used to display all the columns from the hairdresser and client tables for clients with the firstname "Peta"?
OR
AND
LIKE
IN
Which SQL statement would display all the columns from the hairdresser table where the hairdresserid is greater than 1928?
SELECT * FROM hairdresser WHERE hairdresserid = 1928;
SELECT * FROM hairdresser WHERE hairdresserid < 1928;
SELECT * FROM hairdresser WHERE hairdresserid >= 1928;
SELECT * FROM hairdresser WHERE hairdresserid > 1928;
What SQL keyword is used to filter the rows from a table that will be displayed in the answer table?
LESS THAN
WHERE
OR
AND
Which SQL operator is used to combine two conditions such that either one or both of the conditions must be true for the rows to be displayed?
AND
NOT
OR
LIKE
When combining AND and OR operators in a single SQL statement, what is used to group the conditions to control the order of evaluation?
Parentheses ()
Quotation marks ""
Square brackets []
Curly braces {}
What is the purpose of an equi-join in SQL?
To delete rows from a table
To update column values in a table
To combine rows from two or more tables based on a related column between them
To filter rows based on a condition
What is the correct SQL statement to display all the sorted rows from the client table in ascending order of hairdresserid and then clientfirstname descending where clientid is greater than 11000?
SELECT * FROM client WHERE clientid > 11000 ORDER BY hairdresserid ASC, clientfirstname DESC;
SELECT * FROM client ORDER BY hairdresserid ASC, clientfirstname DESC WHERE clientid > 11000;
SELECT * FROM client WHERE clientid > 11000 ORDER BY clientfirstname DESC, hairdresserid ASC;
SELECT * FROM client ORDER BY clientid ASC, hairdresserid DESC WHERE clientid > 11000;
Which SQL statement correctly displays the clientid and the hairdresserid from the client table where the hairdresserid is greater than 2000, sorted by hairdresserid ascending and clientid ascending?
SELECT clientid, hairdresserid FROM client WHERE hairdresserid > 2000 ORDER BY hairdresserid ASC, clientid ASC;
SELECT clientid, hairdresserid FROM client ORDER BY hairdresserid DESC, clientid DESC WHERE hairdresserid > 2000;
SELECT hairdresserid, clientid FROM client WHERE hairdresserid > 2000 ORDER BY clientid ASC, hairdresserid ASC;
SELECT hairdresserid, clientid FROM client ORDER BY hairdresserid ASC, clientid ASC WHERE hairdresserid > 2000;
What is the purpose of the FROM clause in a SQL query?
To specify the condition for selecting rows
To define the order in which the results are returned
To state which table or tables to get the data from
To list the columns that should be updated in a table
What does the WHERE clause do in a SQL query?
It specifies the tables from which to retrieve data
It defines the columns to be displayed in the result set
It determines the order in which rows are returned
It uses comparators and operations to select only the records required
Which SQL clause is used to sort the result table?
GROUP BY
ORDER BY
HAVING
LIMIT
What does the following SQL query do?
SELECT userid, accountname
FROM user
WHERE userid > 70000
ORDER BY accountname DESC, userid ASC;
It selects all user IDs and account names from the user table where the user ID is greater than 70000 and sorts them in ascending order by account name and then by user ID.
It selects all user IDs and account names from the user table and sorts them in descending order by account name and then by user ID.
It selects all user IDs and account names from the user table where the user ID is greater than 70000 and sorts them in descending order by account name and then in ascending order by user ID.
It updates user IDs and account names in the user table where the user ID is greater than 70000.
What is the result of the following SQL query?
SELECT userid, accountname
FROM user
WHERE userid = 70000
ORDER BY accountname DESC, userid ASC;
It selects rows where the userid is less than 70000.
It selects rows where the userid is not equal to 70000.
It selects rows where the userid is greater than 70000.
It selects rows where the userid is equal to 70000.
What does this SQL clause do?
SELECT userid, accountname
FROM user
WHERE userid < 3938
ORDER BY accountname DESC, userid ASC;
It selects all records where the userid is equal to 3938.
It selects all records where the userid is greater than 3938.
It selects all records where the userid is less than 3938.
It selects all records regardless of the userid value.
What is the result of an SQL query that includes the line:
WHERE customerid < 3938 AND userid = 70000
It selects records where either customerid is less than 3938 or userid equals 70000.
It selects records where customerid is less than 3938 and userid is any value.
It selects records where customerid is less than 3938 and userid equals 70000.
It selects records where customerid is greater than 3938 and userid equals 70000.
What does an SQL query that includes this line achieve?
WHERE customerid < 3938 OR userid = 70000
It selects records where both customerid is less than 3938 and userid equals 70000.
It selects records where either customerid is less than 3938 or userid equals 70000 or both.
It selects records where customerid is greater than 3938 and userid equals 70000.
It selects records where customerid equals 3938 or userid is less than 70000.
What is the correct SQL syntax to sort the answer table data?
USING SORT BY column1 ASC|DESC, column2 ASC|DESC, ...
ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...
ALIGN BY column1 ASC|DESC, column2 ASC|DESC, ...
ARRANGE BY column1 ASC|DESC, column2 ASC|DESC, ...
In SQL, how can the results in the answer tables be sorted?
Only in ascending order (ASC)
Only in descending order (DESC)
Either in ascending (ASC) or descending (DESC) order
Cannot be sorted
What is chosen as the default sort order if no specific sort order is specified in an SQL query?
Descending (DESC) order
Random order
Ascending (ASC) order
Alphabetical order
Which SQL keyword is used to sort the data retrieved from a database?
WHERE
SORT BY
ORDER BY
GROUP BY
The ORDER BY keywords in SQL can only be used with which type of query?
INSERT
UPDATE
SELECT
DELETE
Which SQL clause is used to sort the results of a query in descending order?
ORDER BY DES
ORDER BY DESC
SORT BY DESC
ORDER BY ASC
When sorting by two columns in SQL, how are the results ordered when the first column has identical values?
The results are ordered by the second column in descending order.
The results are ordered by the second column in either ascending or descending order.
The results are not ordered and remain random.
The results are ordered by the first column only.
To display all the rows from the 'client' table sorted in ascending order of 'clientid', which SQL statement should be used?
SELECT * FROM client ORDER BY clientid DESC;
SELECT * FROM client ORDER BY clientid ASC;
SELECT clientid FROM client ORDER BY ASC;
SELECT clientid FROM client;
What is the purpose of the INSERT query in SQL?
To delete a row or rows from a table
To add a row or rows to a table
To update existing information in a table
To create a new table
Which SQL statement correctly adds a new row to the 'hairdresser' table for Shirley Simpson?
INSERT INTO hairdresser VALUES ('Shirley', 'Simpson', '7261', '07700 900992', 'On The Button')
INSERT INTO hairdresser (hairdresserid, firstname, lastname, contactno, salon) VALUES ('7261', 'Shirley', 'Simpson', '07700 900992', 'On The Button')
ADD TO hairdresser ('7261', 'Shirley', 'Simpson', '07700 900992', 'On The Button')
UPDATE hairdresser SET VALUES ('7261', 'Shirley', 'Simpson', '07700 900992', 'On The Button')
How can you insert several rows at the same time into the 'hairdresser' table?
By using multiple INSERT INTO statements separated by commas
By using a single INSERT INTO statement with each row separated by a comma within the VALUES clause
By using the UPDATE statement with multiple rows
By creating a new table with the desired rows and merging it with the 'hairdresser' table
If the values to be inserted in a table are in the same order as the columns, you don't need to list the column names in the SQL statement.
True
False
What will the following SQL statement do?
INSERT INTO user (accountname, userid) VALUES ("shadow99", 72661), ("madhatter", 98815);
Insert one row into the table user.
Select the accountname and userid from the table user where the accountname and userid have the given values.
Insert two rows into the table user.
Update the user table where accountname and userid are equal to the given values.
What is the purpose of the INSERT statement in SQL?
To modify existing records in a table
To delete records from a table
To insert new records into a table
To create a new table
Which of the following correctly represents the syntax for inserting a row into a table using column order in SQL?
INSERT INTO table_name (column1, column2) VALUES (value1, value2)
UPDATE table_name SET column1 = value1, column2 = value2
DELETE FROM table_name WHERE condition
SELECT column1, column2 FROM table_name
Based on the example provided, what will be the SQL statement to insert a new client into the client table using the given values?
INSERT INTO client VALUES (3029, 'Joe', 'Walsh', '0141 496 0000')
INSERT INTO client (hairdresserid, clientid, clientfirstname, clientlastname, phonenumber) VALUES (3029, 40292, 'Joe', 'Walsh', '0141 496 0000')
UPDATE client SET hairdresserid = 3029, clientid = 40292, clientfirstname = 'Joe', clientlastname = 'Walsh', phonenumber = '0141 496 0000'
SELECT * FROM client WHERE hairdresserid = 3029 AND clientid = 40292
What is the correct SQL syntax for inserting a single row into a table specifying the column order?
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
INSERT INTO table_name VALUES (value1, value2);
UPDATE table_name SET column1 = value1, column2 = value2;
SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;
What is the purpose of the WHERE clause in an UPDATE statement?
To select the table to update.
To specify the columns to be updated.
To select the records to be updated.
To insert new data into the table.
What happens if you run an UPDATE statement without a WHERE clause?
It will update only the first row in the table.
It will generate an error and not run.
Every row in the table will be updated.
The UPDATE statement will be ignored.
Which SQL statement correctly inserts a new client into the client table?
INSERT INTO client (phonenumber, hairdresserid, clientlastname, clientfirstname, clientid) VALUES ('0141 496 0001', 3030, 'Thunders', 'Johnny', 40391);
INSERT INTO client VALUES ('0141 496 0001', 3030, 'Thunders', 'Johnny', 40391);
UPDATE client SET phonenumber = '0141 496 0001', hairdresserid = 3030, clientlastname = 'Thunders', clientfirstname = 'Johnny', clientid = 40391;
SELECT * FROM client WHERE clientid = 40391;
What happens to the related entries in the orders table when a product is deleted from the products table?
All related orders will be deleted.
Related orders will have their product reference set to null.
No changes will occur in the orders table.
The orders table will automatically update to reference a different product.
How does the database ensure that every entry in the client table has a corresponding hairdresser from the hairdresser table?
By using a unique constraint on the hairdresserid in the client table.
By using a primary key in the hairdresser table.
By enforcing referential integrity between the client and hairdresser tables.
By automatically deleting clients who do not have a valid hairdresserid.
What is the result of executing a DELETE SQL command on a row in a table without a WHERE clause?
Only the specified row is deleted.
No action is taken and an error is returned.
All rows in the table are deleted.
The first row of the table is deleted.
