# Computing - SQL

> Source: https://ollybritton.com/notes/a-level/computing/topics/sql/ · Updated: 2020-11-23 · Tags: computing

##### What does SQL stand for??
Structured Query Language

##### What does "`WHERE`" do in SQL??
Only returns the results that match the clause.

##### What does "`BETWEEN`" do in SQL??
Allows matching a range of decimal values.

##### What does "`IN`" do in SQL??
Allows matching if the item is in a list.

##### What does "`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??
```
SELECT population/area FROM world
```

##### What would the "`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 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??
One.

##### What would the "`WHERE`" clause be for matching rows with the same length `name`s and `capital`s??
```
WHERE LENGTH(name) = LENGTH(capital)
```

##### What would the "`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 DISTINCT director FROM movies
```

##### What is the SQL syntax for sorting a table descending on a "`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??
```
SELECT COUNT(city)
```

##### What does "`INNER JOIN`" do??
Allows you to combine multiple tables sharing a common attribute.

##### What is "`INNER JOIN`" often shortened to??
```
JOIN
```

##### Where does "`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??
```
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??
1. Insert
2. Select
3. Update
4. 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.

### 2021-09-19
##### How could you use the ROUND function in SQL to round $x$ to the nearest $1000$??
$$
\text{ROUND}(x, -3)
$$

##### How could you pick the first 3 letters from $x$ in SQL??
$$
\text{LEFT}(x, 3)
$$

---
Olly Britton — https://ollybritton.com. Machine-readable index: https://ollybritton.com/llms.txt