Sql

Front 1

What does SQL stand for?

Back 1

Structured Query Language

Front 2

What does DDL stand for?

Back 2

Data Definition Language

The DDL consists of SQL statements that define the database schema. The schema is the database structure described in a formal language. In relational databases, the schema defines the tables, views, indexes, relationships, or triggers.

Front 3

Which DDL statements does SQLite support?

Back 3

CREATE

ALTER TABLE

DROP

In SQLite, the CREATE statement is used to create tables, indexes, views, and triggers. The ALTER TABLE statement changes the structure of a table. The DROP statement removes tables, indexes, views, or triggers.

Front 4

What are the column datatypes?

Back 4

NULL

INTEGER

REAL

TEXT

BLOB

Front 5

What are some popular CREATE commands?

Back 5

IF NOT EXISTS

AS

TEMPORARY

Front 6

What is the DROP syntax?

Back 6

DROP TABLE table ;

DROP TABLE IF EXISTS table ;

Front 7

In which ways can you alter tables?

Back 7

Possible:

Rename table

Add a column

Not possible:

rename column

remove column

add/remove constraints

Front 8

Popular alter commands and syntax?

Back 8

CREATE TABLE table (Id INTEGER, Name TEXT);

ALTER TABLE table RENAME TO new_table ;

ALTER TABLE new_table ADD COLUMN email TEXT;

Front 9

SQL operators, oder of precedence

Back 9

unary + - ~ NOT

|| (concat)

* / %

+ -

<< <> & |

< <= > >=

= == != <> IS IN LIKE GLOB BETWEEN

AND

OR

Front 10

Popular SQL operator syntax

Back 10

SELECT 3+4

=>7

SLECT 1 AND 2

=> 1|2

SELECT "wolf" || "hound" (concatenation)

=> wolfhound

SELECT 'Tom" IN ("Tom", "Frank")

=> 1 (with 1 being the boolean true)

SELECT * FROM table

SELECT * FROM table WHERE name IN ('x','y')

SELECT * FROM cars WHERE name LIKE 'Vol%'

=> Volvo, Volkswagen

SELECT * FROM table WHERE name LIKE '___'

=>any name with three characters. It's a pattern match. Each underscore stands for one letter.

SELECT * FROM table WHERE name GLOB '*en'

=>Citroen, Volkswagen

SELECT * FROM table WHERE name GLOB '????'

=> another way of getting items with four chars

SELECT * FROM table WHERE price BETWEEN 1000 AND 1500

SELECT * FROM table WHERE name LIKE '%smith%' (this is for *contains* but not particularly at the start or end)

SELECT * FROM table WHERE field IS NULL

SELECT billing_address FROM invoices WHERE billing_city = 'Cupertino' AND billing_state = 'CA' ORDER BY total DESC LIMIT 3

SELET COUNT(*) FROM invoices WHERE billing_city = 'Santiago'

SELECT country, COUNT(*) FROM customers GROUP BY country (needs the group by)

SELECT name, COUNT(*)

FROM artists

JOIN albums

ON artists.id = albums.artist_id

GROUP BY name

ORER BY name ASC

Front 11

Difference between GLOB and LIKE?

Back 11

GLOB is case sensitive

LIKE is case insensitive

Front 12

What is CRUD, and what are some SQL commands for it?

Back 12

Create

Read

Update

Delete

CREATE

READ (?)

UPDATE, ALTER TABLE, INSERT

DELETE

Front 13

Describe the INSERT syntax

Back 13

INSERT INTO table(col1,col2) VALUES(val1,val2);

Front 14

What are the alternative names for a row?

Back 14

Tuple

Record

Instance (Rubyland)

Front 15

What happens if you insert a new record but don't include an ID?

Back 15

The table should have been set up with the id column as INTEGER PRIMARY KEY. So when inserting a new record, you don't need to declare the ID

Front 16

What happens when you don't supply a value to a field which has no default value?

Back 16

NULL

Front 17

Command to show null values as null? (it does this by default)

Back 17

.nullvalue NULL

Front 18

Syntax to insert a row to update it while overriding a unique key issue?

Back 18

Either:

UPDATE

or:

INSERT OR REPLACE INTO table VALUES(4,'Volvo',30000)

Front 19

Syntax for a multi-line INSERT

Back 19

INSERT INTO table(Val) VALUES (1),(3),(5)

ID VAL

1 1

2 3

3 5

Front 20

Syntax to SELECT and INSERT on one line

Back 20

INSERT INTO table2 SELECT * FROM table1

Front 21

Syntax to delete a row from a table

Back 21

DELETE FROM table WHERE ID=1

Front 22

Syntax to delete all data from a table

Back 22

DELETE FROM table;

Front 23

Delete the first five rows

Back 23

DELETE FROM table LIMIT 5

Front 24

Syntax to update a row

Back 24

UPDATE table SET column = "Timmy" WHERE ID=3

Front 25

COUNT(*) creates a column named count

Front 26

GROUP BY notes

Back 26

Put GROUP BY before ORDER BY

Columns referenced in the SELECT statement list must be included in the GROUP BY clause.

Put it after WHERE or before HAVING

Front 27

join one to many

Back 27

Artist table

ID: 1

Name: name

Album table

ID: 1

Title: title

Artist_ID: art_id

SELECT name,title FROM artists

INNER JOIN albums

ON artists.id = albums.artist_id

Experiment with swapping the "name,title" part of select. This decides the row of the columns

Front 28

join many to many

Back 28

same syntax

Front 29

join (order by asc)

Back 29

SELECT name, title FROM artists

INNER JOIN albums

ON artists.id = albums.artist_id

ORDER BY artists.name ASC

Front 30

Describe JOIN

aka INNER JOIN

Back 30

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Produces only a set of records that match from both tables

Front 31

Describe FULL OUTER JOIN

Back 31

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Front 32

Describe LEFT JOIN

aka LEFT OUTER JOIN

Back 32

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

LEFT OUTER JOIN produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null

Front 33

Describe RIGHT JOIN

aka RIGHT OUTER JOIN

Back 33

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Front 34

Joins

Back 34

INNER JOIN: Returns all rows when there is at least one match in BOTH tables

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

FULL JOIN: Return all rows when there is a match in ONE of the tables

Front 35

Join two rows on certain stuffs. Return the top one.

Back 35

SELECT * FROM customers

INNER JOIN invoices ON customers.id = invoices.customer_id

ORDER BY invoices.total DESC LIMIT 1

Front 36

Join notes

Back 36

When in doubt, spell it out explicitly.

eg: artists.id instead of just id

Front 37

Having and Where seem to be interchangeable?

Back 37

a

Front 38

Join

Back 38

SELECT location, voter_id, COUNT(*)

FROM congress_members

INNER JOIN votes

ON congress_members.id = votes.politician_id

GROUP BY location

ORDER BY COUNT(*) DESC;

Front 39

When in doubt, do ORDER BY last

Front 40

Compare Ruby and SQL

Back 40

Remember the mapping between Ruby-land and SQL-land: Ruby classes to database tables, instances of the class to table rows, and instance variables to table columns.

In SQL World we talk in terms of tables, rows, and relations. In Ruby World we talk in terms of classes, objects, and associations

Front 41

Run through the steps from creating a SCHEMA to executing stuff on the new DB object.

Back 41

create schema -> save as .sql -> create a new db in terminal and either copy in the .sql or run it using "sqlite3 my_db.db < my_sql.sql" -> create a rb file -> create a new ruby object to represent the db by doing "$db = SQLite3::Database.new "my_db.db" -> execute stuff on the new object using ruby's execute method and passing in SQL commands

Front 42

Use IRB to hard-code a new $db, create a new table, insert rows into the table, print the table showing the updates.

Back 42

require "sqlite3"

$db = SQLite3::Database.new("animal_shelter.db",)

$db.execute <

CREATE TABLE dogs (

id INTEGER AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(64),

age INTEGER,

weight INTEGER);

SQL

irb(main):054:0> $db.execute ("INSERT INTO dogs (id,name,age,weight) VALUES(1,'fig',7,9);")=> []

irb(main):055:0> $db.execute("SELECT * FROM dogs;")

=> [[1, "fig", 7, 9]]

irb(main):057:0> $db.execute ("insert into dogs (id,name,age,weight) values(2,'indy',8,10);")

=> []

irb(main):058:0> $db.execute('select * from dogs;')

=> [[1, "fig", 7, 9], [2, "indy", 8, 10]]

Front 43

CREATE TABLE IF NOT EXISTS table