Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
43 Cards in this Set
- Front
- Back
What does SQL stand for? |
Structured Query Language |
|
What does DDL stand for? |
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. |
|
Which DDL statements does SQLite support? |
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. |
|
What are the column datatypes? |
NULL INTEGER REAL TEXT BLOB |
|
What are some popular CREATE commands? |
IF NOT EXISTS AS TEMPORARY |
|
What is the DROP syntax? |
DROP TABLE table ; DROP TABLE IF EXISTS table ; |
|
In which ways can you alter tables? |
Possible:
Rename table Add a column Not possible: rename column remove column add/remove constraints |
|
Popular alter commands and syntax? |
CREATE TABLE table (Id INTEGER, Name TEXT); ALTER TABLE table RENAME TO new_table ; ALTER TABLE new_table ADD COLUMN email TEXT; |
|
SQL operators, oder of precedence |
unary + - ~ NOT || (concat) * / % + - << <> & | < <= > >= = == != <> IS IN LIKE GLOB BETWEEN AND OR |
|
Popular SQL operator syntax |
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 |
|
Difference between GLOB and LIKE? |
GLOB is case sensitive LIKE is case insensitive |
|
What is CRUD, and what are some SQL commands for it? |
Create Read Update Delete CREATE READ (?) UPDATE, ALTER TABLE, INSERT DELETE |
|
Describe the INSERT syntax |
INSERT INTO table(col1,col2) VALUES(val1,val2);
|
|
What are the alternative names for a row? |
Tuple Record Instance (Rubyland) |
|
What happens if you insert a new record but don't include an ID? |
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 |
|
What happens when you don't supply a value to a field which has no default value? |
NULL |
|
Command to show null values as null? (it does this by default) |
.nullvalue NULL |
|
Syntax to insert a row to update it while overriding a unique key issue? |
Either: UPDATE or: INSERT OR REPLACE INTO table VALUES(4,'Volvo',30000) |
|
Syntax for a multi-line INSERT |
INSERT INTO table(Val) VALUES (1),(3),(5) ID VAL 1 1 2 3 3 5 |
|
Syntax to SELECT and INSERT on one line |
INSERT INTO table2 SELECT * FROM table1 |
|
Syntax to delete a row from a table |
DELETE FROM table WHERE ID=1 |
|
Syntax to delete all data from a table |
DELETE FROM table; |
|
Delete the first five rows |
DELETE FROM table LIMIT 5 |
|
Syntax to update a row |
UPDATE table SET column = "Timmy" WHERE ID=3 |
|
COUNT(*) creates a column named count |
|
|
GROUP BY notes |
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 |
|
join one to many |
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 |
|
join many to many |
same syntax |
|
join (order by asc) |
SELECT name, title FROM artists INNER JOIN albums ON artists.id = albums.artist_id ORDER BY artists.name ASC |
|
Describe JOIN aka INNER JOIN |
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 |
|
Describe FULL OUTER JOIN |
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. |
|
Describe LEFT JOIN aka LEFT OUTER JOIN |
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 |
|
Describe RIGHT JOIN aka RIGHT OUTER JOIN |
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. |
|
Joins |
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 |
|
Join two rows on certain stuffs. Return the top one. |
SELECT * FROM customers INNER JOIN invoices ON customers.id = invoices.customer_id ORDER BY invoices.total DESC LIMIT 1 |
|
Join notes |
When in doubt, spell it out explicitly.
eg: artists.id instead of just id |
|
Having and Where seem to be interchangeable? |
a |
|
Join |
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; |
|
When in doubt, do ORDER BY last |
|
|
Compare Ruby and SQL |
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 |
|
Run through the steps from creating a SCHEMA to executing stuff on the new DB object. |
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 |
|
Use IRB to hard-code a new $db, create a new table, insert rows into the table, print the table showing the updates. |
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]] |
|
CREATE TABLE IF NOT EXISTS table |
|