Computing - SQL
AQA Computer Science 2022
What SQL is
Filtering rows with WHERE
What does “WHERE population BETWEEN 200 and 300” match?
WHERE population BETWEEN 200 and 300” match?A population between 200 and 300.
What is a where clause to match an age between 5 and 16?
WHERE age BETWEEN 5 and 15
Selecting and computing columns
If you have a table with a “population” and an “area” column in a “world” table, how could you select the population density?
population” and an “area” column in a “world” table, how could you select the population density?SELECT population/area FROM world
Pattern matching with LIKE and wildcards
What would the “WHERE” clause be for matching a name that starts with United?
WHERE” clause be for matching a name that starts with United?WHERE name LIKE 'United%'
What would the “WHERE” clause be for matching an “assault” in crimes?
WHERE” clause be for matching an “assault” in crimes?WHERE crimes LIKE '%assault%'
String and number functions
Equality and more WHERE clauses
What would the “WHERE” clause be for matching rows with the same length names and capitals?
WHERE” clause be for matching rows with the same length names and capitals?WHERE LENGTH(name) = LENGTH(capital)
What would the “WHERE” clause be for excluding “bribing the state” from crimes?
WHERE” clause be for excluding “bribing the state” from crimes?WHERE crimes NOT LIKE 'bribing the state'
Strings and wildcards
What would the match-string be for anything that has ‘n’ as the second letter in SQL?
'_n%'
Sorting, limiting and counting results
How would you make the query “SELECT director FROM movies” only return uniques?
SELECT director FROM movies” only return uniques?SELECT DISTINCT director FROM movies
What is the SQL syntax for sorting a table descending on a “column”?
column”?ORDER BY column DESC
Joining tables
What does “INNER JOIN” do?
INNER JOIN” do?Allows you to combine multiple tables sharing a common attribute.
Where does “INNER JOIN” come after in an SQL statement?
INNER JOIN” come after in an SQL statement?After the SELECT.
What is the syntax to join two tables (“table_a”, “table_b”) that share an id attribute?
table_a”, “table_b”) that share an id attribute?INNER JOIN table_b ON table_a.id = table_b.id
Inserting and creating tables
I want to insert the customer with “id” 5 and “forename” Bob into the table “customers”. What is the syntax to do this?
INSERT INTO customers (id, forename) VALUES (1, 'Bob');
What is the syntax for creating an attribute “customer _ id” that’s an integer primary key?
customer_id INTEGER PRIMARY KEY
What is the syntax for creating a table “customers” with attributes “…”?
CREATE TABLE customers ( ... );
When creating an attribute in a “CREATE TABLE” statement, what comes first: “datatype” or “column”?
column datatype
How can you specify “RegistrationNumber” (a VARCHAR) being a foreign key from the table “Vehicles” when creating the table?
RegistrationNumber VARCHAR FOREIGN KEY REFERENCES Vehicles(RegistrationNumber)
What could you prefix to “PRIMARY KEY” to specify it can’t be null?
PRIMARY KEY(NOT NULL)
The four basic commands
What is the syntax of the DELETE command in SQL?
DELETE FROM table _ name WHERE …
What is the syntax of the INSERT command in SQL?
INSERT INTO table _ name (column _ 1, column _ 2, …) VALUES (value _ 1, value _ 2, …)
What is the syntax of the UPDATE command in SQL?
UPDATE table _ name SET column _ 1=value, column _ 2=value WHERE …
What is the syntax for creating a table in SQL?
CREATE TABLE table _ name (column _ name data _ type, column _ name data _ type, …);
How would you delete all records from the “invoices” table that were paid on 2021-07-27 using the datePaid column?
DELETE FROM invoices WHERE datePaid=“2021-07-27”
Entities and relationships
What are entity descriptions?
A statement that describe an entity and their attributes.
What is
\[\text{Customer}(\text{custID}, \text{firstname}, \text{surname})\]
an example of?
An entity description.
What sort of relationships should you try avoid in database design?
- Many-to-many
- One-to-one
How can you resolve a many-to-many relationship in a database?
Add a new table in the middle with a one-to-many on each side.
How can you resolve a one-to-one relationship in a database?
Make the information in the extra table attributes in the existing table.