Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

COMP207 > SQL > Flashcards

SQL Flashcards

1
Q

What is a relational database?

A
  • Data is stored in relations (or tables) depending on grouped attributes
  • each table has a schema
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is the typical format of a schema?

A

nameOfTable(Attribute1, Attribute2..)

  • Example from the diagram above: Measurements(Tag#, Height, Weight)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

In relational databases, columns are ___ and rows are ___

A

In relational databases, columns are attributes and rows are different entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What does SQL stand for?

A

Standard query language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is DDL and which SQL commands fall into it?

A

Data definition language
- Create/alter/delete databases, tables and their attributes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is DML and which SQL commands fall into it?

A

Data Manipulation Language
- Add/remove/update and query rows in tables Transact-SQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the query for creating a table in a database?

A

CREATE tableName(attributeName attribute Type, …);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is the query for creating a database in SQL?

A

CREATE DATABASE databaseName;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What are the data types in SQL?

A

INT, FLOAT, CHAR, VARCHAR, DATE, DATETIME, XML, BLOB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does the UNIQUE keyword mean in SQL?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a primary key?

A
  • an attribute that is unique in a table and is used to reference rows in another table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is the SQL for a primary key?

A

CREATE TABLE people (name VARCHAR(20), birthday DATE, CONSTRAINT PK_people PRIMARY KEY (name));

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a foreign key?

A

The primary key from another table used to reference things in this table.
- used to link two table together

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is the SQL for a foreign key?

A

CREATE TABLE staff (employee VARCHAR(20) , staffnum INT, CONSTRAINT FK_staff FOREIGN KEY (employee) REFERENCES people(name));

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the SQL to delete/drop a database?

A

DROP DATABASE databaseName;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the SQL to delete/drop a table?

A

DROP TABLE tableName;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is the SQL for adding an attribute to a table

A

ALTER TABLE people ADD email VARCHAR(30);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is the SQL for changing an attribute to a table?

A

ALTER TABLE people MODIFY email VARCHAR(100)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is the SQL for removing an attribute to a table?

A

ALTER TABLE people DROP COLUMN email;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the SQL for inserting into a table?

A

INSERT INTO people VALUES (‘phoebe’, 19, ‘student’)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

if the schema is people(name, number, course) how do you insert into the database leaving a field empty in SQL?

A

INSERT INTO people(name, course) VALUES (‘Danny’, ‘G402’);

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is an SQL injection?

A
  • A common attack where malicious users input into a form SQL commands to DROP TABLE … or INSERT INTO
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are 4 common methods for avoiding SQL injection attacks?

A
  1. Input validation ◦ Check the input is valid for the field entered
  2. Use prepared statements ◦ You write the query, except have some ? or similar which you can later replace with say a first name
  3. Stored procedures ◦ Like prepared statements, but stored in the database instead of in your programming language
  4. Escaping ◦ Make sure that you escape all characters users input – should be functions for this
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is the SQL command to delete from a table where the person is called John?

A

DELETE FROM people WHERE name = ‘John’;`

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What is the SQL command to delete from a table where the person is called John?

A

DELETE FROM people WHERE name = ‘John’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What are the conditions for WHERE clauses?

A
  • comparisons: <, >, <=, >=, ==, !=
  • operators: OR, AND, NOT, XOR, BETWEEN, LIKE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Give an example of an SQL statement that uses the LIKE with WHERE clause?

A

SELECT * FROM people WHERE name LIKE ‘O%r’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What does the command DELETE FROM people WHERE name IN (list of names) do?

A

It deletes all the rows that have a name that is in that list

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is the SQL command to update a row?

A

UPDATE people SET course = ‘G407’ WHERE name = ‘oliver’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What is the format for querying a database?

A

SELECT
FROM
WHERE
GROUP BY
HAVING (same as WHERE but inside GROUP BY)
ORDER BY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

what does * mean in SELECT * FROM people?

A
  • It means select ALL of the things that apply
32
Q

What is protection in SQL?

A

Projection allows you to select attributes you want to keep (the rest are discarded)

33
Q

How do you write protection in SQL if the schema is people(name, course, id, birthday) ?

A

SELECT (name, id) FROM people;

34
Q

What does the DISTINCT keyword do in SQL?

A
  • It removes duplicate rows
35
Q

Where does DISTINCT come in an SQL command?

A

SELECT DISTINCT (name) FROM people;

36
Q

What is renaming in SQL?

A
  • Renaming allows you to rename attributes when you query them?
37
Q

How do you write protection in SQL if you want to return name as firstName instead?

A

SELECT name AS firstname FROM people;

38
Q

How do you create new columns on returning a table of data in SQL?

A

SELECT name, price, number, price * number AS total_cost FROM items;

39
Q

How do you add all the items in a column?

A

use the SUM keyword
- SELECT SUM(prices) FROM items

40
Q

What are the aggregation keywords we can use in SQL?

A
  • COUNT, SUM, MIN, MAX, AVG
41
Q

What does the FROM keyword do?

A
  • FROM defines the input, FROM can contain many input tables
42
Q

What are the ways we can join table together?

A
  • Cross product
  • Natural Join (semi-join, equi-join)
43
Q

How do we carry out a cross product on two tables if they have schemas: employee(birthday, firstName, familyName) and items(name, price, number)?

A

SELECT firstName, name FROM employee, items;

44
Q

What does the cross product command SELECT firstName, name FROM employee, items; return?

A

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

45
Q

What is a natural join?

A

Merging two tables together that have an overlap of one or more attributes

46
Q

What is the command for doing a natural join between two tables employees and transactions?

A

SELECT * FROM employees NATURAL JOIN transactions;

47
Q

How is a natural join carried out logically?

A
  • 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
48
Q

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)

A

SELECT birthday, familyName, firstName Employees.e_id AS e_id, t_id, c_id FROM Employees, Transactions WHERE Employees.e_id = Transactions .e_id

49
Q

Why do we usually not use natural joins (in real life)?

A

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

50
Q

What is a sub query in SQL?

A
  • 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
51
Q

What is the exists keyword?

A
  • 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.
52
Q

Why would we use SELECT 1 in a query?

A
  • common to use the constant 1 when we just want to know if there’s an output or not
53
Q

What is a left semi-join?

A

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

54
Q

What is a right semi-join?

A

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

55
Q

Why do left semi joins make querying faster over natural joins?

A
  • 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
56
Q

what is the sql for a left semi join on tables employees to transactions?

A

SELECT * FROM Employees E WHERE EXISTS (SELECT 1 FROM Transactions E.e_id = T.e_id);

57
Q

What do we use GROUP BY for in SQL?

A
  • 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
58
Q

What is the issue with group by?

A

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.

59
Q

What is the solution to the group by complication?

A

put multiple attributes after the GROUP BY clause:
- instead of GROUP BY first_name
- do GROUP BY first_name, e_id

60
Q

What is the HAVING keyword in SQL?

A

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

61
Q

What is the ORDER BY keyword in SQL?
- what is the keyword for different orders

A

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

62
Q

What is the order of execution of SQL queries

A
  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY
63
Q

What are views?

A

-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

64
Q

Can Views be modified?

A
  • By default data in views can’t be modified
  • However we can modify the actual saved statement to change the saved query
65
Q

What is the SQL command to create a View?

A

CREATE VIEW employeeTransactionCount AS
SELECT first_name, e_id, COUNT(t_id)
FROM Employees NATURAL JOIN Transactions
GROUP BY first_name, e_id

66
Q

What is the SQL to ‘modify’ a view?

A

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

67
Q

What is UNION in SQL?

A
  • 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.
68
Q

What is the command for UNION in SQL?

A

SELECT firstName AS name, e_id
FROM Employees
UNION
SELECT familyName, e_id
FROM Employees

69
Q

What is relational algebra?

A
  • Relational Algebra is algebra that deals with tables ≈ SQL SELECT queries
70
Q

Why do we use relational algebra?

A

Relational algebra is crucial for optimisation

71
Q

What is protection in relational algebra

A

Projection (𝜋) Projection is just the same as using a DISTINCT command

72
Q

What is renaming in relational algebra

A

Renaming (𝜌) Renaming is just the same as using an AS command

73
Q

What is selection in relational algebra

A

Selection (𝜎) Selection is just the same as using a WHERE command

74
Q

What is the cartesian product in relational algebra?

A

Cartesian Product (×) Cartesian product is just done by selection from two tables

75
Q

What is natural join in relational algebra

A

Natural Join (⋈) Natural Join is just done by using the NATURAL JOIN command

COMP207 (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions