Data Science - SQL

variables

#mySQL SET @idmin = 5; SELECT * FROM table WHERE id > @idmin user defined variable that's longer lasting. they exist outside of a BEGIN and END scope #oracle DECLARE var1 NUMBER local variable, only exists within scope

Foreign Key

A primary key from one table that is used in another table.

What is the purpose of a view?

A view is considered a query of several tables combined, using joins and subsets.

What is the difference between a plain text editor and a word processor?

A word processor will interfere with computer language code, so the preferred editor is a plain text editor.

When listing in a certain order in SQL, what keyword lists data in ascending order?

ASC

Tom is creating a transaction to SELECT and INSERT several tables. What is the correct code format for executing a transaction?

BEGIN TRANSACTION SELECT COUNT(cars)FROM vehicle; INSERT INTO vehicles('jeep', 'blue', 'V-6'); SELECT COUNT(*)FROM vehicles; INSERT INTO vehicles('saturn'); END TRANSACTION

AND

Boolean operator returns only when both conditions are true ex: SELECT name, continent, population FROM country WHERE population < 1000000 AND continent = 'Oceania' ORDER BY population;

In terms of adding constraints to certain columns in a table, what is the correct code format for constraining a column called Cities that cannot have a NULL value?

CREATE TABLE country (towns TEXT, cities TEXT NOT NULL);

Jonathan is studying SQL development and is working on his lab for creating database tables. What is the correct code format for creating a table called TextData that has two TEXT fields and one INTEGER field?

CREATE TABLE textdata ( a TEXT, b TEXT, c INTEGER);

In terms of creating an id column, what is the correct code format for creating an id for table Titan in SQLite?

CREATE TABLE titan (id INTEGER PRMIARY KEY);

TIMESTAMP()

CREATE TRIGGER stampSale AFTER INSERT ON widgetSale BEGIN UPDATE widgetSale SET stamp = DATETIME('now') WHERE id = NEW.id; UPDATE widgetCustomer SET last_order_id = NEW.id, stamp = DATETIME('now') WHERE widgetCustomer.id = NEW.customer_id; INSERT INTO widgetLog (stamp, event, username, tablename, table_id) VALUES (DATETIME('now'), 'INSERT', 'TRIGGER', 'widgetSale', NEW.id); END ;

Brittany is learning how to join views together in her database schema. What is the correct code format for creating a join view?

CREATE VIEW joinedTrain AS SELECT r.rails AS rail, m.title AS model, t.title AS track, FROM track AS t JOIN model as m on m.id = t.model_id;

Sunita is eliminating old data that she doesn't need anymore. What is the correct code format for deleting data from the Customer table where id= 10?

DELETE FROM customer WHERE id =10;

Angus is assigned a task to remove all references that involve the name Bob. What is the correct code format for deleting several rows in table Winston where the Name column = Bob?

DELETE FROM winston WHERE name='Bob';

The Air Force team at Buckley Tech is deleting old database tables to save on storage space with their servers. What is the correct code format for deleting a table called AF_3?

DROP TABLE af_3;

OFFSET #

Example: say you have SELECT Name FROM Country LIMIT 5 OFFSET 5; LIMIT 5 grabs only the first 5 rows but OFFSET 5 now grabs the second set of 5 rows, or the 5 rows starting at the 5th row. Say OFFSET 10, then it grabs only 5 rows starting at the 10th row

functions

FUNCTION 'kitty_function_name'(kitty_id INT) RETURNS INT(11) READS SQL DATA *the above defines a function *the below uses the function passing it the id parameter 12 SELECT kitty_function_name(12)

Delilah has two new records that need to be inserted into the Customer table? What is the correct code format for inserting new records?

INSERT INTO customer(name, address) VALUES('John Doe', 7903 Redline Dr'); INSERT INTO customer(name, address) VALUES('Betty Doe', 7903 Redline Dr');

What is the correct code format for inserting, updating, and deleting data into a Students table?

INSERT INTO students VALUES(12, 'Aly', 4, '1996-10-12'); UPDATE students SET departmentId = 3 WHERE studentId = 6; DELETE FROM students WHERE studentId = 11 OR studentId = 12;

John needs to add empty records into a table called Vehicle. What is the correct code format for inserting default values into a row from the table Vehicle?

INSERT INTO vehicle DEFAULT VALUES;

Integer Types

INTEGER(precision) - whole with no fraction DECIMAL(precision, scale) - decimals are more accurate, good to represent quantities less than 1 MONEY(precision, scale) precision - accuracy. refers to how many digits are presented scale - represents the magnitude of the numbers presented. supports very large/small numbers

What keyword can access multiple tables?

JOIN

Why is SQL good in relational databases?

Modern relational databases use the SQL language to perform data tasks and operations.

Integer Division

SELECT 1/2 **returns 0 SELECT 1.0 / 2; ** 0.5 (as a real number) SELECT CAST(1 AS REAL) / 2; ** 0.5 SELECT 17 / 5 ** 3 SELECT 17 % 5 ** 2 (THIS IS MODULUS)

How to output fields with first letter capitalized and the rest lowercase?

SELECT CONCAT(LEFT(first_name, 1), LOWER(RIGHT(first_name, LENGTH(first_name) -1))) from actor OR SELECT CONCAT(SUBSTRING(first_name, 1,1), LOWER(SUBSTRING(first_name, 2))) FROM actor

CONCAT()

SELECT CONCAT(first_name, ' ',last_name) FROM actor **returns Jennifer Anniston [as one column rather than two]

Sommer has several reports that show customers' information. What is the correct code format for counting customers' information?

SELECT COUNT(*) FROM customer;

What is the correct code format for counting the column cars from a Vehicles table?

SELECT COUNT(cars)FROM vehicle;

What is the correct code format for adding 1 extra hour and 15 minutes to a DATETIME function?

SELECT DATETIME('now', '+1 hours', '+15 minutes');

Arun is tasked to find duplicates in his Ships table. What is the correct code format for removing duplicate records in a table called Ships?

SELECT DISTINCT titan from ships;

Abby needs to find out the lengths of certain strings in her application form. What is the correct code format for finding a length of a string?

SELECT LENGTH('important');

Rounding numbers

SELECT ROUND(2.55555) **3.0 SELECT ROUND(2.55555, 3); **2.556 (rounded to 3 decimal points) SELECT ROUND(2.55555, 0); ** 3.0 (rounded to 0 decimal places)

Atena needs to split up a string with an SQL function. What is the correct code format for splitting up a string that starts at the 7th character and has the length of 3?

SELECT SUBSTR('split this string up', 7, 3);

Robin has been assigned a task to create subselects in her SQL queries. What is the correct code format for creating subselects?

SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks > (SELECT total_marks FROM marks WHERE studentid = 'V002');

searching within a result set

SELECT a.title AS album, a.artist, t.track_number AS seq, t.title, t.duration AS secs FROM album AS a JOIN ( SELECT album_id, track_number, duration, title FROM track WHERE duration <= 90 ) AS t ON t.album_id = a.id ORDER BY a.title, t.track_number ;

left outer join

SELECT c.name AS Cust, c.zip, i.name AS Item, i.description, s.quantity AS Quan, s.price AS Price FROM customer AS c LEFT JOIN sale AS s ON s.customer_id = c.id LEFT JOIN item AS i ON s.item_id = i.id ORDER BY Cust, Item ; *returns even the customers who have null for values in other tables

Marie works for IBM updating database code. What is the correct code format for selecting the Car, Engine, and Color rows in the Vehicle table?

SELECT car, engine, color FROM vehicle;

Nikhil is a junior developer who needs to create variable names for the database columns. What is the correct code format for creating variables for Car, Engine, and Interior columns in the Vehicle table?

SELECT cr AS car, eng AS engine, clr AS color FROM vehicle;

Join 3 tables

SELECT i.name AS item, c.name AS cust, s.price AS price FROM sale AS s JOIN item AS i ON s.item_id = i.id JOIN customer AS c ON s.customer_id = c.id ORDER BY cust, item ; *returns 'item' from the table called 'item', customer name from table 'customer', and price from table 'sale'

Eduardo is combining two tables together using the join SQL query command. What is the correct code format for creating an inner join on the Order and Customer tables with the Customer_ID?

SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;

Hal works at Wells Fargo on the security database team. He needs to update financial data into record id 4. What is the correct code format for updating record 4 with a different address?

UPDATE customer SET address='123 Top Hat Dr.', WHERE id =4;

SEPARATOR

function used to insert a symbol or whatever to separate data in a chosen set. Aids in readability ex: SELECT district, GROUP_CONCAT(phone ORDER BY phone ASC SEPARATOR '--') FROM address GROUP BY district **returns phone numbers like 3423353255--23423553--2343255 instead of 234235346,4352364362,24352345

ROLLBACK

happens in transaction if one or more of the statements in a transaction fails. But we use the rollback statement if we need to rollback before all statements have been completed. This rolls back before we commit it BEGIN TRANSACTION; INSERT INTO widgetInventory(description, onhand) VALUES ('toy', 25); ROLLBACK;

Unique Key

identifies a table row. Often called 'Primary Key'. This ALWAYS exists

INNER JOIN / JOIN

keywords are the same. the result includes rows from both tables where the condition is met SELECT s.id AS sale, i.name, s.price FROM sale AS s JOIN item AS i ON s.item_id = i.id ; *returns sale name price 1 Box of 64 Pixels 2995 2 Sense of Humor 1995 3 Box of 64 Pixels 2995 4 Bar Code 999 5 Box of 64 Pixels 2995 SELECT * FROM customer AS c JOIN address AS a ON c.address_id = a.address_id

OUTER JOINS (FULL, LEFT AND RIGHT)

less common. same thing as 'full join', 'full outer join'. merge tables horizontally (rows?) 2 tables of 3 cols and 6 rows, after join we output 1 table of 6 rows and 6 columns Left outer join - includes rows where condition is met plus ALL rows from left table even where it's not met right outer join - includes rows where condition is met plus ALL rows from right table, even if condition isn't met. (sometimes not supported to do these) full outer join - includes all rows from both tables where conditions are met AND where they are not

UNION

merges tables vertically. they don't care about matching records with2 tables having 3 cols 6 rows, the union outputs 3 cols with 12 rows. SELECT 'actor' AS tbl, DATE(last_update) FROM actor UNION ALL SELECT 'address' AS tbl, DATE(last_update) FROM address **there will be two columns with all the address info listed after the actor data in the same columns

which of the following is not correct about Candidate key of a table?

must exist across multiple tables

Triggers

operation that is automatically performed when specified db event occurs. CREATE TRIGGER newWidgetSale AFTER INSERT ON widgetSale BEGIN UPDATE widgetCustomer SET last_order_id = NEW.id WHERE widgetCustomer.id = NEW.customer_id; END ; **AFTER INSERT is a type of trigger. to happen after a row is inserted on the table. after insert happens, the UPDATE statement will be executed. NEW is a meta row inserted into widgetSale **BEFORE UPDATE is another type of trigger good for keeping up business rules to update table whenever another table is updated. can also be used to prevent tables from being changed

IN / NOT IN

operator to refine filtered results more where a column's values are particular ex: SELECT name, continent, population FROM country IN ('Europe', 'Asia'); *returns all rows where the Continent column is either Europe or Asia ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ use to search within a result set SELECT * FROM album where ID IN(SELECT DISTINCT album_id FROM track WHERE duration <= 90); ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~` SELECT * FROM actor WHERE first_name NOT IN ('PENELOPE', 'NICK', 'JOHN');

TRIM(), LTRIM(), RTRIM()

removes spaces or other characters from beginning and or end from string. helpful when dealing with user input SELECT ' string '; SELECT TRIM(' string '); SELECT TRIM(' string string '); SELECT LTRIM(' string '); SELECT RTRIM(' string '); SELECT '...string...'; SELECT TRIM('...string...', '.'); *use view source to see it in the html template to show better ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LTRIM() trims only spaces from the left side RTRIM() only trims spaces from right side SELECT * FROM actor WHERE TRIM(first_name = ' Alayna '); **removes the whitespaces and only searches for alayna. without the trim(), there would be no match as it counts the spaces

NULL

represents a lack of value (is NOT a value itself). you CANNOT call a row like SELECT * FROM test WHERE a=NULL; (doesn't work) instead: SELECT * FROM test WHERE a IS NULL; SELECT * FROM test WHERE a IS NOT NULL; ++++++++++++++++++++++++++++++++++++++++++++++++ example: CREATE TABLE test( a INTEGER NOT NULL, b TEXT NOT NULL, c TEXT); when inserting into this table, values in a and b CANNOT lack a value but c can be null. INSERT INTO test(b, c ) VALUES ('one', 'two'); **throws an error as a cannot be null

WHERE

retrieve data from table that has certain criteria; Used before a GROUP BY clause because it operates on the data before an aggregation [for non-aggregate data] ex: SELECT name, continent, population FROM country WHERE population < 1000000;

transactions

a group of operations handled as one unit of work. if one operation fails-> they all fail. Can improve performance and reliability for complex operations. tells the system that all statements up until end of transaction must be treated as one unit. you can test it by inserting many lines of data into a table and see the time it takes to select all. Then, wrap around a transaction and compare the time. BEGIN TRANSACTION; INSERT INTO widgetSales ( inv_id, quan, price ) VALUES ( 1, 5, 500 ); UPDATE widgetInventory SET onhand = ( onhand - 5 ) WHERE id = 1; END TRANSACTION;

RAISE

a trigger that comes befunction that will raise an event in sql CREATE TRIGGER updateWidgetSale BEFORE UPDATE ON widgetSale BEGIN SELECT RAISE(ROLLBACK, 'cannot update table "widgetSale"') FROM widgetSale WHERE id = NEW.id AND reconciled = 1; END ; *code runs before the update can happen. Here, raises the rollback with message. From the widgetSale, we are checking if the particular row has been reconciled. If it was, then we raise the event

what are aggregates?

aggregate data is info derived from more than one row at a time. Aggregate functions are called last. COUNT(*) is an aggregate function. When you use a GROUP BY with it, it is called first. So, it allows you to apply aggregate function to groups of data instead of all data. SELECT Region, COUNT(*) AS count FROM country GROUP BY region ORDER BY count DESC, region; **returns regions in order with count in descending order a WHERE clause happens before an aggregation [for non-aggregated data] while HAVING clause operates on data after an aggregation [for aggregate data].

SUM()

aggregate function to return sum of numeric values SELECT region, SUM(population) FROM country GROUP BY region;

MIN() MAX()

aggregate function to return the minimum or maximum numeric value SELECT region, MIN(population), MAX(population) FROM country GROUP BY region;

LEFT() and RIGHT()

allows access to certain characters in a string starting from either the left or the right. ex: from name Alayna Camelbak and Caruba Ameri SELECT LEFT(first_name, 1) **returns 'A' and 'C' SELECT LEFT(first_name, 3) **returns 'Ala' and 'Car' SELECT RIGHT(first_name, 3) **returns 'yna' and 'uba'

ALTER TABLE

allows the ability to add new columns to the table ex: ALTER TABLE test ADD d TEXT; ALTER TABLE test ADD e TEXT DEFAULT 'panda';

SUBSTR(,)

allows you to select part of a string based on the position of the characters SELECT SUBSTR('this string', 6); SELECT released, SUBSTR(released, 1, 4) AS year, SUBSTR(released, 6, 2) AS month, SUBSTR(released, 9, 2) AS day FROM album ORDER BY released ;

SUBSTR()

allows you to subselect specific parts of data from values: 'NY0123', 'US4567' 'AZ9437', 'GB1234' 'CA1279', 'FR5678' SELECT SUBSTR(a, 1, 2) AS State, SUBSTR(a, 3) AS SCode, SUBSTR(b, 1, 2) AS Country, SUBSTR(b, 3) AS CCode FROM t; *returns State SCode Country CCode NY 0123 US 4567 AZ 9437 GB 1234 CA 1279 FR 5678 substring a and b grabs at position 1 and 2, then we take from position a, character 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ in a join: SELECT co.Name, ss.CCode FROM ( SELECT SUBSTR(a, 1, 2) AS State, SUBSTR(a, 3) AS SCode, SUBSTR(b, 1, 2) AS Country, SUBSTR(b, 3) AS CCode FROM t ) AS ss JOIN country AS co ON co.Code2 = ss.Country ; Name CCode United States 4567 United Kingdom 1234 France 5678

AVG()

an aggregate function that returns the average of all values for a given column SELECT region, AVG(population) FROM country GROUP BY region;

Robert needs to change all of the number type values. What function can change the number type of variables?

cast

id Column

column that holds unique value for each row in a table. usually automatically populated with sequential values using 'PRIMARY KEY' . The syntax is not standardized per database system. Every time you add a new row, it should automatically add the id column tied to it with a new id. ex: CREATE TABLE test ( id INTEGER PRIMARY KEY, a INTEGER, b TEXT ); INSERT INTO test(a,b) VALUES (10, 'a'); INSERT INTO test(a,b) VALUES (11, 'b'); INSERT INTO test(a,b) VALUES (12, 'c'); *result id a b --------------- 1 10 a 2 11 b 3 12 c

LOWER()

converts a string to all lowercase SELECT LOWER('StRiNg') = LOWER('string'); SELECT LOWER('StRiNg') = 'string'; **both return 1 , aka true SELECT LOWER('StRiNg'); **returns 'string'

UPPER()

converts string to all uppercase SELECT UPPER('StRiNg'); **returns 'STRING' SELECT UPPER('StRiNg') = UPPER('string'); SELECT UPPER('StRiNg') = 'STRING'; **returns 1, aka true SELECT UPPER(name) FROM city ORDER BY name; **returns all the cities in uppercase ~but~ accented letters remain lowercase as they are not ASCII

COUNT()

counts the number of rows in a desired column. EX: SELECT COUNT(*) FROM Country; returns 239 or SELECT COUNT(LifeExpectancy) FROM Country; returns 222 because it counts all that have a life expectancy and leaves out the nulls

UNIQUE

defines a column to only have unique values without duplicates. ex: CREATE TABLE test ( a TEXT UNIQUE, b TEXT, c TEXT DEFAULT 'panda' ); INSERT INTO test ( a, b ) VALUES ( 'one', 'two' ); INSERT INTO test ( a, b ) VALUES ( 'one', 'two' ); SELECT * FROM test; **throws error since a already has a value of 'one' ex: CREATE TABLE test ( a TEXT UNIQUE, b TEXT, c TEXT DEFAULT 'panda' ); INSERT INTO test ( a, b ) VALUES ( NULL, 'two' ); INSERT INTO test ( a, b ) VALUES ( NULL, 'two' ); SELECT * FROM test; **does NOT throw error because while there are more than one nulls, null is still a lack of value as opposed to being the same. could also add NOT NULL to a to not allow these either.

Field having autogrowth property

field grows automatically as needed ?

LENGTH()

finds length of string. SELECT LENGTH('hello'); SELECT name, LENGTH(name) AS len FROM city ORDER BY len desc, name;

ON

** ON clause gives us the expression that must be satisfied for the join to be true SELECT l.description AS left, r.description AS right FROM left AS l JOIN right AS r ON l.id = r.id ; **returns left right left 04 right 04 left 05 right 05

what does the given query mean broken down? SELECT f.first_name FROM (SELECT first_name FROM actor) as f

*read from inside out select first_name column from the actor table and refer to that result as f. Then, from the result set called f, return the first_name

When two string values match, what number is shown as the results?

1

true and False/ booleans

1, non-zero, non-empty string = true 0, empty string = false

how many datatypes are there in sql?

30? NUMERIC bit, tinyint, smallint, int, bigint, decimal, numeric, float, real DATE AND TIME: date, time, datetime, timestamp, year CHARACTER AND STRING char, varchar, varchar(max), text UNICODE CHAR AND STRING nchar, nvarchar, nvarchar(max), ntext BINARY binary, varbinary, varbinary(max), image MISCELLANEOUS clob, blob, xml, json

ORDER BY

A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order). ex: SELECT name, continent, population FROM country WHERE population < 1000000 ORDER BY population DESC; **does not retrieve rows where pop is null or SELECT name, continent, population FROM country WHERE population < 1000000 ORDER BY population; **auto outputs in ascending order ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ex: SELECT name, continent, region FROM country ORDER BY continent desc, region, name; **orders continents in descending order, then the region alphabetically within that continent group, then the names alphabetically within the region group

HAVING

A clause applied to the output of a GROUP BY operation to restrict selected rows. AKA it is used on aggregate data

BETWEEN and AND

Comparison operator that searches for values within a range. BETWEEN - is inclusive: SELECT actor_id, first_name FROM actor WHERE actor_id BETWEEN 3 AND 5; **returns actor_id first_name 3 Alayna 4 Jennifer 5 Allie AND - is exclusive SELECT actor_id, first_name FROM actor WHERE actor_id > 3 AND actor_id < 5; **returns actor_id first_name 4 Jennifer

Real Types (numerical)

REAL(precision) - sacrifice accuracy for scale. rep very large or very small numbers FLOAT(precision) - allow large numbers with few significant digits. sacrifice accuracy for precision precision - accuracy scale - size/supports very large/small numbers SELECT A,B,A = B FROM ( SELECT ( ( .1 + .2 ) * 10 ) as A, ( 1.0 + 2.0 ) as B ); **returns A B A = B 3.0 3.0 0 aka not equal

What keyword will remove spaces at the end?

RTRIM

TYPEOF()

Returns the type of a variable or expression ex: SELECT TYPEOF(1 + 1) **returns 'integer' SELECT TYPEOF(1 + 1) ** returns 'text' SELECT TYPEOF('hey' + 'there') **returns 'integer'

Robert is a database developer who needs to add a string value to the Music column using the SELECT and AS keywords. Which code format should he use?

SELECT 'Make the World Go Away' AS music;

Strings

SELECT 'a standard literal string'; SELECT "a non-standard literal string"; SELECT 'Here' 's a quoted string'; SELECT 'This' || ' is standard ' || ' concatenation'; SELECT CONCAT('MySQL', ' concats ', 'like this'); SELECT 'Microsoft' + ' way of ' + 'concat'; *string functions*use SELECT prior* SUBSTR(string, start, length); LENGTH(string); //finds length of string TRIM(string); UPPER(string); LOWER(string);

Stedman is interested in finding NULL values in the table called Review. What is the correct code format for finding a NULL value in an existing table called Review?

SELECT * FROM review WHERE a IS NULL;

How is the many-to-many relationship defined in SQL?

The many-to-many relationship is defined with multiple JOIN SQL queries.

What databases can you manage with SQL other than SQLite?

You can manage any relational database that is compatible with the SQL language.

DISTINCT

[filtering data] get only unique results (not duplicated) when retrieving data from table ex: SELECT DISTINCT continents FROM country *returns Continent ------------ Asia Europe North America Africa Oceania South America Antarctica **as opposed to getting multiple Asia's per country in Asia, etc. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ INSERT INTO test VALUES ( 5, 1 ); INSERT INTO test VALUES ( 1, 2 ); INSERT INTO test VALUES ( 1, 2 ); INSERT INTO test VALUES ( 1, 2 ); INSERT INTO test VALUES ( 1, 2 ); INSERT INTO test VALUES ( 1, 2 ); SELECT DISTINCT a,b FROM test; SELECT * FROM test; **returns all the distinct ~combinations~ between cols a and b SELECT DISTINCT a FROM test; **returns all distinct values from a ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Can be used with aggregate functions SELECT COUNT(DISTINCT headofstate) FROM country;

OR

a boolean operator returns if at least one condition is true ex: SELECT name, continent, population FROM country WHERE population < 1000000 OR population IS NULL ORDER BY population; **does retrieve rows where pop value is null

LIKE '% %'

returns all values that contains the matching word. The % on both ends pulls names that has the word anywhere in it or if it's only in the front, then grabs names that only end with it. vis a versa. ex: SELECT name, continent, population FROM country WHERE name LIKE '%island%' ORDER BY name; **returns all values that have 'island' in the name** Fiji Islands Heard Island and McDonald Islands Virgin Islands, British ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ex: SELECT name, continent, population FROM country WHERE name LIKE '%island' ORDER BY name; **returns all values that END with 'island' Bouvet Island Christmas Norfolk Island ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ex: SELECT name, continent, population FROM country WHERE name LIKE 'island%' ORDER BY name; **returns all values that BEGIN with 'island' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WHERE name LIKE '_a%' **returns all where 'a' is the second letter followed by whatever string of characters ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~` SELECT * FROM actor WHERE first_name LIKE('JOHN%'); **returns names like 'john', 'johnny', 'johnston', etc.

Dates and times

standard format: 2018-03-28 15:32:47 UTC: Coordinated Universal Time DATE TIME DATETIME YEAR INTERVAL SELECT DATETIME('now'); ** 2021-06-17 14:50:49 SELECT DATE('now'); **2021-06-17 SELECT TIME('now'); **14:50:49 SELECT DATETIME('now', '+1 day'); **2021-06-18 14:50:49 SELECT DATETIME('now', '+3 days'); **2021-06-20 14:50:49 SELECT DATETIME('now', '-1 month'); **2021-05-17 14:50:49 SELECT DATETIME('now', '+1 year'); **2022-06-17 14:50:49 SELECT DATETIME('now', '+3 hours', '+27 minutes', '-1 day', '+3 years'); **2024-06-16 18:17:49 SELECT * FROM address WHERE YEAR(last_update) = 2015 SELECT DATE_FORMAT(last_update, '%M %D, %Y') FROM address

CALL

statement to execute a function. BEGIN SELECT inventory_id FROM inventory WHERE film_id = p_film_id AND store_id = p_store_id AND NOT inventory_in_stock(inventory_id); SELECT FOUND_ROWS() INTO p_film_count; END CALL film_not_in_stock(39,2,@count) **for film 39 and store 2, we want the count of the films that are currently not in stock (aka being rented out now) sends back the inventory id of the film being lent out currently.

SELECT INTO

storing result set into variable SELECT col1 col2 INTO @x, @y FROM table1

CASE

there is a standard syntax but not all systems follow it. a use case when you have conditional expressions ex: SELECT CASE WHEN a THEN 'true' ELSE 'false' END as boolA, CASE WHEN b THEN 'true' ELSE 'false' END as boolB FROM booltest; SELECT CASE a WHEN 1 THEN 'true' ELSE 'false' END AS boolA, CASE b WHEN 1 THEN 'true' ELSE 'false' END AS boolB FROM booltest;

AS

this is the display name of a column when you display it. aka creates an alias ex: say the column is country_name but you want it to be Country Name, then you say SELECT country_name AS "Country Name" FROM Country ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT l.description AS left, r.description AS right FROM left AS l JOIN right AS r ON l.id = r.id ; ** creates an alias for the whole left table, where now it can be referred to as l. ON clause gives us the expression that must be satisfied for the join to be true

VIEW

turns a select statement into a shortened, reusable line. Essentially just a saved query. purpose: A view is considered a query of several tables combined, using joins and subsets. CREATE VIEW trackView AS SELECT id, album_id, title, track_number, duration / 60 AS min, duration % 60 AS sec FROM track; SELECT * FROM trackView; *the above prints the same thing but now we can just select trackView as a simplified version of the first line ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ joined query using our created view SELECT a.title AS album, a.artist, t.track_number AS seq, t.title, t.min, t.sec FROM album AS a JOIN trackView AS t ON t.album_id = a.id ORDER BY a.title, t.track_number ; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ joined query view CREATE VIEW joinedAlbum AS SELECT a.artist AS artist, a.title AS album, t.title AS track, t.track_number AS trackno, t.duration / 60 AS m, t.duration % 60 AS s FROM track AS t JOIN album AS a ON a.id = t.album_id ; SELECT * FROM joinedAlbum; SELECT * FROM joinedAlbum WHERE artist = 'Jimi Hendrix';

INSERT INTO

used to insert data into a table ex: CREATE TABLE test ( a INTEGER, b TEXT, c TEXT ); INSERT INTO test VALUES(1, 'this', 'right here'); SELECT * FROM test; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ or could add into only some columns INSERT INTO test (b,c) VALUES('this', 'right here'); then a returns NULL. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ or could make all NULL by INSERT INTO test DEFAULT VALUES; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ or add data from one table to another INSERT INTO test (a, b , c) SELECT id, name, description FROM item; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ INSERT INTO customer (name) VALUES('Jane Smith');

IF EXISTS

used when dropping (deleting tables) without it throwing errors if it doesn't exist ex: DROP TABLE IF EXISTS test;

Cristina is updating her SQL queries to include conditional expressions. What is the correct code format for creating a conditional expression that checks for NULL values?

vehicle(a, b) CASE WHEN a IS NOT NULL THEN a ELSE b END

When concatenating strings together in standard SQL, what symbol is used?

|| SELECT a.title AS album, a.artist, t.track_number AS seq, t.title, t.min || ':' || substr('00' || t.sec, -2, 2) AS dur FROM album AS a JOIN trackView AS t ON t.album_id = a.id ORDER BY a.title, t.track_number ; **takes minutes from one column and seconds from another column and concats them into one column from: min sec -> dur 2 7 -> 2:07 4 38 -> 4:38 1 50 -> 1:50