Computing - SQL
What does SQL stand for?
Structured Query Language
What does “WHERE” do in SQL?
WHERE” do in SQL?Only returns the results that match the clause.
What does “BETWEEN” do in SQL?
BETWEEN” do in SQL?Allows matching a range of decimal values.
What does “IN” do in SQL?
IN” do in SQL?Allows matching if the item is in a list.
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
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
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%'
In SQL, what character used to signify an unknown bit of a string?
%
What is the function for finding the length of a string in SQL?
LENGTH
What is the function for rounding a number in SQL?
ROUND(x, decimal places)
How many = for testing equality in SQL?
= for testing equality in SQL?One.
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'
What characters do you use for strings in SQL?
'single quotes'
What is the wildcard character in SQL for matching?
_
What would the match-string be for anything that has ‘n’ as the second letter in SQL?
'_n%'
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
What are the two types of sorting in SQL?
ASC/DESC
What is the SQL syntax for the first 4 rows in a query?
LIMIT 4
What is the SQL syntax for the 5th to 8th rows in a query?
LIMIT 4 OFFSET 4
How can you count the number of “city” rows in a table?
city” rows in a table?SELECT COUNT(city)
What does “INNER JOIN” do?
INNER JOIN” do?Allows you to combine multiple tables sharing a common attribute.
What is “INNER JOIN” often shortened to?
INNER JOIN” often shortened to?JOIN
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
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)
2021-07-27
What are the names of the 4 basic SQL commands?
- Insert
- Select
- Update
- Delete
What is the command for adding new data to a table in SQL?
Insert
What is the command for getting data from a table in SQL?
Select
What is the command for changing data in a table in SQL?
Update
What is the command for removing data in a table in SQL?
Delete
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 do you delete an entire table in SQL?
DROP TABLE table_name
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”
2021-09-08
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 is an example of a one-to-one relationship?
Husband and wife.
What is an example of a one-to-many relationship?
Mother and child.
What is an example of a many-to-many relationship?
Actor and film.
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.