Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

One to Many

memorize.ai (lvl 286)

(0)

18 cards

()

Section 1

Preview this deck

Implicit(by syntax) Inner Join

Front

1 / 18

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (18)

Section 1

(18 cards)

Implicit(by syntax) Inner Join

Front

SELECT * FROM customers, orders WHERE customers.id = orders.customer_id;

Back

IFNULL can be used to replace NULL with some other value.

Front

SELECT first_name, last_name, order_date, IFNULL(SUM(amount), 0) AS total FROM customers LEFT JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id;

Back

Relationship types?

Front

One to one relationship. One to many relationship. Many to many relationship.

Back

CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) ); CREATE TABLE papers ( title VARCHAR(100), grade INT, student_id INT, FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE );

Front

SELECT first_name, Ifnull(Avg(grade), 0) AS average, CASE WHEN Avg(grade) IS NULL THEN 'FAILING' WHEN Avg(grade) >= 75 THEN 'PASSING' ELSE 'FAILING' end AS passing_status FROM students LEFT JOIN papers ON students.id = papers.student_id GROUP BY students.id ORDER BY average DESC;

Back

Foreign key will stop us to delete data from tables if we don't us ON DELETE CASCADE while creating a table.

Front

CREATE TABLE customers( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100) ); CREATE TABLE orders( id INT AUTO_INCREMENT PRIMARY KEY, order_date DATE, amount DECIMAL(8,2), customer_id INT, FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE );

Back

Case statements are good if there is multiple options, but if descision is binary then we can use IF(condition,' ', ' ') SELECT first_name, last_name, Count(rating) AS COUNT, Ifnull(Min(rating), 0) AS MIN, Ifnull(Max(rating), 0) AS MAX, Round(Ifnull(Avg(rating), 0), 2) AS AVG, CASE WHEN Count(rating) >= 10 THEN 'POWER USER' WHEN Count(rating) > 0 THEN 'ACTIVE' ELSE 'INACTIVE' end AS STATUS FROM reviewers LEFT JOIN reviews ON reviewers.id = reviews.reviewer_id GROUP BY reviewers.id;

Front

SELECT first_name, last_name, Count(rating) AS COUNT, Ifnull(Min(rating), 0) AS MIN, Ifnull(Max(rating), 0) AS MAX, Round(Ifnull(Avg(rating), 0), 2) AS AVG, IF(COUNT(rating) > 0, 'ACTIVE','INACTIVE') AS STATUS FROM reviewers LEFT JOIN reviews ON reviewers.id = reviews.reviewer_id GROUP BY reviewers.id;

Back

Find all orders containing last name 'George'.

Front

SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE last_name = 'george');

Back

Explict(by syntax) Inner Join

Front

SELECT * FROM customers JOIN orders ON customers.id = orders.customer_id;

Back

We can join 3 tables.

Front

SELECT title, rating, CONCAT(first_name,' ', last_name) AS reviewer FROM reviewers INNER JOIN reviews ON reviewers.id = reviews.reviewer_id INNER JOIN series ON series.id = reviews.series_id ORDER BY title;

Back

A cross join mix every customer row with all order rows. So it's customer rows x order row.

Front

SELECT * FROM customers, orders;

Back

Foreign keys

Front

They are refrences to another table withing a given table.It must be explicit said.

Back

YEAR() datatype

Front

CREATE TABLE series( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100) );

Back

Changing the order wouldn't change anything if we do NOT use SELECT * FROM.

Front

Like this: SELECT first_name, last_name, order_date, amount FROM customers JOIN orders ON customers.id = orders.id;

Back

ROUND FUNCTION ROUND(what to round, on how many decimals)

Front

SELECT genre, ROUND(AVG(rating), 2) AS g_rating FROM series JOIN reviews ON series.id = reviews.series_id GROUP BY genre;

Back

This will delte data from both interconnected tables.

Front

DELETE FROM customers WHERE email ='gm@gmail.com';

Back

You can't use NULL = ... in WHERE. Insread of = you need to use IS.

Front

SELECT title AS unrated FROM series LEFT JOIN reviews ON series.id = reviews.series_id WHERE rating IS NULL; SELECT title AS unrated FROM series LEFT JOIN reviews ON series.id = reviews.series_id WHERE rating IS NOT NULL;<== NOT 100% SURE FOR THIS.

Back

Conncectiong to tables with foreign key. CREATE TABLE customers( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100) );

Front

CREATE TABLE orders( id INT AUTO_INCREMENT PRIMARY KEY, order_date DATE, amount DECIMAL(8,2), customer_id INT, FOREIGN KEY(customer_id) REFERENCES customers(id) );

Back

LEFT JOIN allow us to to match all rows from left table with right table and if values are not existent in the right table then it will show us NULL.

Front

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

Back