SQL Flashcards
What is a relational database?
- Data is stored in relations (or tables) depending on grouped attributes
- each table has a schema
What is the typical format of a schema?
nameOfTable(Attribute1, Attribute2..)
- Example from the diagram above: Measurements(Tag#, Height, Weight)
In relational databases, columns are ___ and rows are ___
In relational databases, columns are attributes and rows are different entities
What does SQL stand for?
Standard query language
What is DDL and which SQL commands fall into it?
Data definition language
- Create/alter/delete databases, tables and their attributes.
What is DML and which SQL commands fall into it?
Data Manipulation Language
- Add/remove/update and query rows in tables Transact-SQL
What is the query for creating a table in a database?
CREATE tableName(attributeName attribute Type, …);
What is the query for creating a database in SQL?
CREATE DATABASE databaseName;
What are the data types in SQL?
INT, FLOAT, CHAR, VARCHAR, DATE, DATETIME, XML, BLOB
What does the UNIQUE keyword mean in SQL?
You can specify that you want some attribute (or set of attributes) to be unique, so all the values in that column must be different. Though this is not commonly used due to primary keys.
What is a primary key?
- an attribute that is unique in a table and is used to reference rows in another table
What is the SQL for a primary key?
CREATE TABLE people (name VARCHAR(20), birthday DATE, CONSTRAINT PK_people PRIMARY KEY (name));
What is a foreign key?
The primary key from another table used to reference things in this table.
- used to link two table together
What is the SQL for a foreign key?
CREATE TABLE staff (employee VARCHAR(20) , staffnum INT, CONSTRAINT FK_staff FOREIGN KEY (employee) REFERENCES people(name));
What is the SQL to delete/drop a database?
DROP DATABASE databaseName;
What is the SQL to delete/drop a table?
DROP TABLE tableName;
What is the SQL for adding an attribute to a table
ALTER TABLE people ADD email VARCHAR(30);
What is the SQL for changing an attribute to a table?
ALTER TABLE people MODIFY email VARCHAR(100)
What is the SQL for removing an attribute to a table?
ALTER TABLE people DROP COLUMN email;
What is the SQL for inserting into a table?
INSERT INTO people VALUES (‘phoebe’, 19, ‘student’)
if the schema is people(name, number, course) how do you insert into the database leaving a field empty in SQL?
INSERT INTO people(name, course) VALUES (‘Danny’, ‘G402’);
What is an SQL injection?
- A common attack where malicious users input into a form SQL commands to DROP TABLE … or INSERT INTO
What are 4 common methods for avoiding SQL injection attacks?
- Input validation ◦ Check the input is valid for the field entered
- Use prepared statements ◦ You write the query, except have some ? or similar which you can later replace with say a first name
- Stored procedures ◦ Like prepared statements, but stored in the database instead of in your programming language
- Escaping ◦ Make sure that you escape all characters users input – should be functions for this
What is the SQL command to delete from a table where the person is called John?
DELETE FROM people WHERE name = ‘John’;`
What is the SQL command to delete from a table where the person is called John?
DELETE FROM people WHERE name = ‘John’;
What are the conditions for WHERE clauses?
- comparisons: <, >, <=, >=, ==, !=
- operators: OR, AND, NOT, XOR, BETWEEN, LIKE
Give an example of an SQL statement that uses the LIKE with WHERE clause?
SELECT * FROM people WHERE name LIKE ‘O%r’;
What does the command DELETE FROM people WHERE name IN (list of names) do?
It deletes all the rows that have a name that is in that list
What is the SQL command to update a row?
UPDATE people SET course = ‘G407’ WHERE name = ‘oliver’
What is the format for querying a database?
SELECT
FROM
WHERE
GROUP BY
HAVING (same as WHERE but inside GROUP BY)
ORDER BY
what does * mean in SELECT * FROM people?
- It means select ALL of the things that apply
What is protection in SQL?
Projection allows you to select attributes you want to keep (the rest are discarded)
How do you write protection in SQL if the schema is people(name, course, id, birthday) ?
SELECT (name, id) FROM people;
What does the DISTINCT keyword do in SQL?
- It removes duplicate rows
Where does DISTINCT come in an SQL command?
SELECT DISTINCT (name) FROM people;
What is renaming in SQL?
- Renaming allows you to rename attributes when you query them?
How do you write protection in SQL if you want to return name as firstName instead?
SELECT name AS firstname FROM people;
How do you create new columns on returning a table of data in SQL?
SELECT name, price, number, price * number AS total_cost FROM items;
How do you add all the items in a column?
use the SUM keyword
- SELECT SUM(prices) FROM items
What are the aggregation keywords we can use in SQL?
- COUNT, SUM, MIN, MAX, AVG
What does the FROM keyword do?
- FROM defines the input, FROM can contain many input tables
What are the ways we can join table together?
- Cross product
- Natural Join (semi-join, equi-join)
How do we carry out a cross product on two tables if they have schemas: employee(birthday, firstName, familyName) and items(name, price, number)?
SELECT firstName, name FROM employee, items;
What does the cross product command SELECT firstName, name FROM employee, items; return?
Each iteration is the next calculation between first_name and name so:
11 = Anne * 2L Cola
12 = Anne * Banana
21 = David * 2L Cola
22 = David * Banana
31 = William * 2L Cola
32 = William * Banana
What is a natural join?
Merging two tables together that have an overlap of one or more attributes
What is the command for doing a natural join between two tables employees and transactions?
SELECT * FROM employees NATURAL JOIN transactions;
How is a natural join carried out logically?
- You take a cross product of the two tables, remove all rows where the common attributes do not match and then only keep one column for each common attribute
What is the SQL to do a natural join but choosing a specific attribute to do the natural join on (since there may be multiple common attribute names in two tables)
SELECT birthday, familyName, firstName Employees.e_id AS e_id, t_id, c_id FROM Employees, Transactions WHERE Employees.e_id = Transactions .e_id
Why do we usually not use natural joins (in real life)?
OSs are too stupid to realise that first_name and family_name in the employees table is not the same as the similarly named attributes in the customers table.
- In the example above we simply end up losing all the data from the customers table because the OS thinks this is already in the Employees table
What is a sub query in SQL?
- A query inside another query
- so the inside query fetches data using the whole table and then the outside query fetches data only using what was returned from the inside query
What is the exists keyword?
- EXISTS is a generalisation of IN
- With EXISTS you write a subquery and if it returns something, the current row will be kept otherwise thrown out.
Why would we use SELECT 1 in a query?
- common to use the constant 1 when we just want to know if there’s an output or not
What is a left semi-join?
A (left) semi-join between two tables A and B, finds the subset of rows in A that would be used to form some row of the natural join of A and B
What is a right semi-join?
A right semi-join between two tables A and B, finds the subset of rows in B that would be used to form some row of the natural join of A and B
Why do left semi joins make querying faster over natural joins?
- because in semi-joins, one of the table is actually a subset of it’s rows joined with another table, this means that there’s less data to query in total
what is the sql for a left semi join on tables employees to transactions?
SELECT * FROM Employees E WHERE EXISTS (SELECT 1 FROM Transactions E.e_id = T.e_id);
What do we use GROUP BY for in SQL?
- Use group by to generalise the query for one entity (in this example people) to many.
- so we can group and then count how many things are in a group
What is the issue with group by?
If we include GROUP BY, then we can only include in SELECT attributes we GROUP BY
E.g. say we wanted the first name of the employees as well as their employee id and the number of their transactions
- Say we try to do this with a new table that includes two people with the same first name. The OS won’t know the difference and count all the transactions under one ID
- This does not work here, since we now have two employees with the same first_name and e_id is not clear ◦ That said, MySQL allows it by default, even if the manual claims it does not. It returns the top e_id.
What is the solution to the group by complication?
put multiple attributes after the GROUP BY clause:
- instead of GROUP BY first_name
- do GROUP BY first_name, e_id
What is the HAVING keyword in SQL?
HAVING is easy! It is just WHERE, but done following the GROUP BY command.
Meaning that it takes what the GROUP BY returns and then queries that
What is the ORDER BY keyword in SQL?
- what is the keyword for different orders
ORDER BY defines how the output is sorted.
- write ORDER BY attributeName (default is ascending order so don’t need to put that)
- write ORDER BY attributeName DESC for descending order
What is the order of execution of SQL queries
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
What are views?
-Intuitively, saved queries or virtual tables
- It’s a saved query because we’ve created (or saved rather) a new table by querying one or more tables that already exist in the database
Can Views be modified?
- By default data in views can’t be modified
- However we can modify the actual saved statement to change the saved query
What is the SQL command to create a View?
CREATE VIEW employeeTransactionCount AS
SELECT first_name, e_id, COUNT(t_id)
FROM Employees NATURAL JOIN Transactions
GROUP BY first_name, e_id
What is the SQL to ‘modify’ a view?
CREATE OR REPLACE VIEW employeeTransactionCount AS
SELECT first_name, e_id, COUNT(t_id)
FROM Employees NATURAL JOIN Transactions
GROUP BY first_name, e_id
What is UNION in SQL?
- Same as in maths, merges two groups of data
- When we do UNION it changes first_name to name first then simply adds the family_name entities onto the end of the list titled name.
What is the command for UNION in SQL?
SELECT firstName AS name, e_id
FROM Employees
UNION
SELECT familyName, e_id
FROM Employees
What is relational algebra?
- Relational Algebra is algebra that deals with tables ≈ SQL SELECT queries
Why do we use relational algebra?
Relational algebra is crucial for optimisation
What is protection in relational algebra
Projection (𝜋) Projection is just the same as using a DISTINCT command
What is renaming in relational algebra
Renaming (𝜌) Renaming is just the same as using an AS command
What is selection in relational algebra
Selection (𝜎) Selection is just the same as using a WHERE command
What is the cartesian product in relational algebra?
Cartesian Product (×) Cartesian product is just done by selection from two tables
What is natural join in relational algebra
Natural Join (⋈) Natural Join is just done by using the NATURAL JOIN command
