SQL

Question 1

What is a database?

Answer 1

Collection of data, a method for accessing and manipulating that data.

Question 2

DBMS
RDBMS
SQL

Answer 2

Database Management System
Relational DBMS
Structured Query Language

Question 3

Types of DBs

Answer 3

1. Relational – works well with SQL
2. Document – MongoDB, CouchBase, FireBase
3. KeyValue – Redis, DynamoDB
4. Graph – Neo4j, Nephew - for social media looking at relations.
5. Wide Columnar – BigTable, Apache Cassandra

Question 4

What is Query?

Answer 4

Instructions for the RDBMS that will return us output.

SQL is a declarative language.
– What will happen?

Imperative:
– How will happen?

Question 5

Database Models

Answer 5

Hierarchial - IBM 60s 70s - One-to-many -- XML
Networking - Many-to-many
Entity-Relationship
Relational
Object Oriented
Flat
Semi-Structured

Question 6

CRUD Operation

Answer 6

Create
Read
Update
Delete

Question 7

DBMS Functions

Answer 7

• Management of Data
• Encryption
• Transaction Management

Question 8

Relational Model

Answer 8

Relation Schema
Attribute - data in a cell
Degree - collection of all columns
Cardinality - collection of all rows
Tuple/Row - a single set of data
Column - may or may not store a specific type of data
Relation Key
– Primary key - uniquely identifies a row
– Foreign key - reference primary key of another table
Domain/Constraint - what kind of data can be stored in a column
Table - collection of tables and rows
Relation Instance

Question 9

OLTP

OLAP

Answer 9

Online Transaction Processing - Day to day business

Online Analytical Processing - Predict/Analyse

Question 10

\du

Answer 10

List role names and attributes

Question 11

DCL
DDL
DQL
DML

Answer 11

Data Control Language
-- Grant
-- Revoke
Data Definition Language
-- Create
-- Alter
-- Drop
-- Rename
-- Truncate
-- Comment
Data Query Language
-- Select
Data Modification Language
-- Insert
-- Update
-- Delete
-- Merge
-- Call
-- Explain Plan
-- Lock Table

Question 12

Change name of a column

Answer 12

select title as “JOB TITLE” from titles where emp_no = 10006

Question 13

Concat two columns and print in a custom column

Answer 13

select CONCAT(first_name, ‘ ‘, last_name) AS “Employee Name” from employees

Question 14

Function in SQL

Answer 14

e.g. CONCAT

Receives an input and generates an output.

• • Aggregate - receives multiple values and generates 1 output.
• —AVG
• —COUNT
• —MIN
• —MAX
• —SUM
• • Scalar - runs against each row and generates multiple output.

Question 15

Youngest employee in the table

Answer 15

select * from employees order by birth_date ASC limit 1

Question 16

Common mistakes with SQL

Answer 16

1. Spelling error
2. Misplacing ; and ,
3. Use double commas
4. Using “ instead of ‘
5. Invalid column name.

” for Tables
‘ for Strings

Question 17

Order of operations

Answer 17

FROM
  |
WHERE
  |
SELECT

AND is chained with previous query
OR starts a new search
NOT

Comparison operators:
>
<
>=
<=
!=
=
'abc' > 'ace'   -- true

Logical Operators:

Question 18

Operator Precedence

Answer 18

()
*/
-+
NOT
AND
OR