SQL/Databases Flashcards Preview

General > SQL/Databases > Flashcards

Flashcards in SQL/Databases Deck (25):
1

creating tables

  • Use CREATE TABLE statement
  • First column is usually the primary key

Syntax

CREATE TABLE table_name (

id INTEGER PRIMARY KEY,
col1 TYPE,
col2 TYPE
)

 

 

2

primary key

  • Unique identifier for a table record
  • Added to column during creation of table

Syntax [2]:

  • id INTEGER PRIMARY KEY
  • id INTEGER PRIMARY KEY AUTOINCREMENT
    • This syntax will leave id creation to the database

3

INSERT INTO

  • Adds data to database

Syntax [2]:

INSERT INTO table_name
VALUES (datum1, datum2, datum3);

INSERT INTO table_name (col2, col3) 
VALUES (datum2, datum3);
  • With this syntax, can exclude certain values when inserting data, in particular the id
  • Must have set PRIMARY KEY to AUTOINCREMENT during table creation

4

SELECT

  • Used to get data from table
  • add DISTINCT to return only unique results

Syntax:

SELECT col1, col2, col3 FROM table_name

5

clauses

  • basically sql commands

4 clauses:

  1. DISTINCT
    • Add to SELECT to remove duplicates
      • SELECT DISTINCT city FROM country
    • Add to COUNT clause to only count values once
      • SELECT COUNT (DISTICT city) FROM country
  2. ORDER BY col_name
    • sorts results
    • sort in reverse order by adding negative
  3. WHERE col_name ><= value
    • filters out results
  4. HAVING agg_col ><= value
    • just like where, but used on alias of column created by aggregate function (agg_col)
    • ex: SELECT SUM(sales) as total_sales FROM store HAVING total_sales>1000

 

6

aggregate functions

  • functions that combine values from multiple rows
  • Functions
    • SUM
    • MAX
    • MIN
    • COUNT
    • AVG
  •  

SELECT FUNC(col_name) FROM table_name

 

 

7

GROUP BY

  • used in conjuctino with aggregate functions
  • basically, creates groups on which aggregate functions are performed
  • added to the end of a SELECT statement

ex: SELECT SUM(quantity) FROM groceries GROUP BY aisle;

  • instead of providing the sum over entire column, will generate sum for each aisle

8

operators

  • Use with WHERE to add additional conditions to filter by
  • Can include parantheses to control how logic is evaluated

5 operations

  1. AND
    • Returns rows if all conditions are met
    • WHERE col1>5 AND col2<5
  2. OR
    • Returns rows if any of conditions are met
    • WHERE col1>5 OR col2=10
  3. IN
    • Shorthand for multiple or operators
    • Returns rows if the column matches any of the values in the list 
    • WHERE genre IN ("disco", "jazz", "funk")
    • Values inside parentheses can also be a subquery
  4. NOT IN
    • Like IN, but returns row if none of conditions are met
    • WHERE genre NOT IN ("electronic", "new age")
  5. LIKE
    • Used for inexact matching
    • SELECT * FROM genre WHERE genre LIKE "%hip%"
    • Often used with percentage signs (which act as wildcards)

9

subqueries

  • using the result of on query inside another query
  • the queries can be from different tables
  • often used with IN operator or FROM
  • ex: SELECT * FROM music WHERE artist IN (SELECT stars FROM billboards);

10

AS

  • Assigns an alias to a column, usually an aggregate function column
  • Used to rename columns on the fly
  • SELECT col1 AS new_name

11

CASE

  • Adds a label to a record based on some condition
  • SQL's way of handling if/then logic
  • creates a new column, like an aggregate function

Syntax:

CASE
WHEN col1>'val1' THEN 'resultA'
ELSE 'resultB' 
END AS col_output
  • 4 parts: WHEN, THEN, ELSE, END AS
  • WHEN: evaluate the row
  • THEN: assigns a value if condition is met
  • ELSE: assign as value if condition isn't met
  • END AS: assigns an alias to the new column

12

cross join

  • naively matches every row with every other row
  • put both table names after FROM

SELECT * FROM table_1, table_2

13

order of execution

  • Order in which sql actions are performed
  1. FROM
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY

14

inner join

  • finds records that have matching values in both tables
  • and combines them in one row in output
  • query will only return records from either table when match is found

2 approaches:

1. Implicit inner join

SELECT *
FROM table_1, table_2
WHERE table_1.col = table_2.col

2. Explicit inner join

SELECT *
FROM table1
INNER JOIN table2 
ON table_1.col = table2.col
  • Explicit inner join is best practice

 

 

 

 

15

outer join

  • find records that have matching values in both tables
  • and combines them in one row in output
  • but keeps all rows from one table
  • and populates missing field in the other table with null

2 flavors:

  1. left outer join
    • returns left table, and matches in right table
  2. right outer join
    • returns right table, and matches in left table

Syntax [very similar to inner join]:

SELECT *
FROM table1
LEFT OUTER JOIN table2 
ON table_1.col = table2.col
 
 

16

SQL query lifecycle

3 steps

  1. Parse
    • makes sure that query is syntactically correct and that tables exist
    • turns query into algebraic expression
  2. Optimize
    • optimizes command (ex, turns 5*10 into 50)
    • estimates cost (CPU and time) for each potential approach to fulfilling query
    • chooses optimal approach
  3. Execute
    • takes plan and turns it into operations on database

17

query tuning

  • process of optimizing queries

Steps

  1. Use an SQL profiler to identity which queries are taking the longest amount of time
  2. Get execution plan for a specific command
    • ​syntax varies based on environment
    • ex: EXPLAIN QUERY PLAN select * from movies
  3. Optimize query manually

18

changing records

  • Use UPDATE
  • changes data in database
  • tell databse what content to change (with SET) and which row to update (with WHERE)

Syntax

UPDATE table_name
SET co1_1 = val_1, col_2 = val_2,
WHERE condition;

19

deleting records

  • Use DELETE
  • remove records from database
  • if WHERE is omitted, will remove all records

Syntax:

DELETE FROM table_name
WHERE condition;

20

changing schema

  • ALTER TABLE
  • changes the composition of the table after creation
  • adds a new column

Syntax:

ALTER TABLE table_name
ADD col_name TYPE;

21

deleteing table

  • Use DROP TABLE 

Syntax

DROP TABLE table_name

22

get string length

  • Use LENGTH function (LEN in some variations)

SELECT LENGTH(col_name) FROM table

23

NoSQL

  • a variety of technologies that don't use sql or tables
  • one type is a document database
  • ex:
    • MongoDB 

24

Mongoose

  • NPM package for connecting to a MongoDB database
  • It's a node thing (whereas MondoDB is just a database in general)

25

ACID properties

  • Atomicity
    • either all operatins in transactions occur, or none
    • no half transactions
  • Consistency
    • correctness
    • any transaction will bring the db to a valid state
  • Isolation
    • if a database is queried concurrently, you get the same result as if you'd quereid sequentially
  • Durability
    • persistence
    • changes are permanent
    • once a record is created/updated, it will remain, even if there power loss, crash, etc.