Chapter 1: Data and Tables
Create database:
CREATE DATABASE gregs_list;
Telling the RDBMS i want to use specific database:
USE gregs_list;
Data types:
CHAR - set length of data, example: CHAR(10)
VARCHAR - hold text data of up to 255 characters in length, example: VARCHAR(120)
INT - whole numbers, example: INT
DEC - decimal numbers, example: DEC(6,2)
DATETIME - date and time, example: DATETIME
TIMESTAMP - is usually used to capture the current time, example: TIMESTAMP
DATE - date, example: DATE
BLOB - large text, example: BLOB
Creating table:
CREATE TABLE my_contacts
(
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);
To see structure of the table you created:
DESC my_contacts;
Deleting table:
DROP TABLE my_contacts;
Removing the data but keep the table structure table:
DELETE FROM my_contacts;
Adding data into table:
INSERT INTO my_contacts
(
last_name,
first_name,
email,
gender,
birthday,
profession,
location,
status,
interests,
seeking
)
VALUES
(
'Kacperska',
'Sophie',
'sophiekacperska@gmail.com',
'F',
'2022-08-12',
'Doctor',
'Sheffield',
'Holy',
'pray',
'God'
);
Showing data from your table:
SELECT * FROM my_contacts;
Set up your table to not accept NULL values for column:
CREATE TABLE my_contacts
(
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(20) NOT NULL,
email VARCHAR(50),
gender CHAR(1) NOT NULL,
birthday DATE NOT NULL,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);
DEFAULT value in CREATE TABLE:
CREATE TABLE my_contacts
(
last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(20) NOT NULL,
email VARCHAR(50),
gender CHAR(1) NOT NULL,
birthday DATE NOT NULL,
profession VARCHAR(50),
location VARCHAR(50) NOT NULL DEFAULT 'UK',
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
);
Chapter 2: The SELECT Statement
To find something specific in your table use WHERE:
USE gregs_list;
SELECT * FROM my_contacts
WHERE first_name = 'Sophie';
Showing only column we want to see:
SELECT last_name, email, location
FROM my_contacts;
Combining your queries by word AND:
SELECT location
FROM doughnut_rating
WHERE type = 'plain glazed'
AND
rating = 10;
Comparison Operators:
=
>
<
<> This confusing sign is not equal
>=
<=
Combine two queries by using word OR:
SELECT * FROM my_contacts;
WHERE email = example1@gmail.com
OR
email = example2@gmail.com
Looks for part of a text string and returns any matches with word LIKE:
(Place a percent sign inside the single quotes. This tells your software you're looking for all values in the location column that end with CA)SELECT * FROM my_contacts
WHERE location LIKE '%CA';
BETWEEN can help us with certain range of numbers of alphabatical character:
SELECT * FROM drink_info;
WHERE calories BETWEEK 30 AND 60;
AND
name_drink BETWEEK 'a' AND 'c'
Use IN word with a set of values in parentheses to find words or numbers you are looking for:
By addinng the keyword NOT to our IN statement NOT gives you the opposite results, anything that doesn't match the set.SELECT * FROM my_contacts;
WHERE rating IN ('happy','sad','pretty good')
AND
cost IN (5.5, 26);
Chapter 3: DELETE and UPDATE
If value contain apostrofe put \ backslash before it:
INSERT INTO clown_info
VALUES
('MR. Kop\'ye');
Getting rid of a row with DELETE clause:
All records that match the WHERE condition will be deleted from our table.DELETE FROM clown_info
WHERE activities = 'dancing';
Change your data with UPDATE clause:
UPDATE doughnut_rating
SET type = 'glazed'
WHERE type = 'plain glazed';
Here you updating more then one row in one querie:
UPDATE drink_info
SET cost = cost + 1
WHERE
drink_name='Blue Moon'
OR
drink_name='Oh My Gosh'
OR
drink_name= 'Lime Fizz';
Chapter 4: Smart Table Design
Atomic data / value :
Rule 1: A column with atomic data can't have several values of the same type of data in that column.
Rule 2: A table with atomic data can't have multiple columns with the same type of data.
Primary Key:
A primary key is a column in your table that makes each record unique.
1NF First Normal Form:
Each row of data must contain atomic values, and each row of data must have a unique identifier.
Rule 1: Columns contain only atomic values
Rule 2: No repeating groups of data
The statement SHOW CREATE TABLE will return a CREATE TABLE statement that can exactly recreate our table, minus any data in it.:
SHOW CREATE TABLE my_contacts;
To display all the columns in your table and their data type along with any other column-specific details:
SHOW COLUMNS FROM my_contacts;
The statement SHOW CREATE DATABASE you'll get the command that would exactly recreate your database:
SHOW CREATE DATABASE databasename;
To display any columns that are indexed and what type of index they have:
SHOW INDEX FROM tablename;
Creating / putting Primary Key in your table:
CREATE TABLE my_contacts
(
contact_id INT NOT NULL (Remember, the primary key column has to be NOT NULL!)
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
PRIMARY KEY (contact_id) (Here's where we specifying the primary key.)
);
Auto increment Primary Key value:
CREATE TABLE my_contacts
(
contact_id INT NOT NULL AUTO_INCREMENT
last_name VARCHAR(30),
first_name VARCHAR(20),
email VARCHAR(50),
gender CHAR(1),
birthday DATE,
profession VARCHAR(50),
location VARCHAR(50),
status VARCHAR(20),
interests VARCHAR(100),
seeking VARCHAR(100)
PRIMARY KEY (contact_id)
);
Adding a PRIMARY KEY to an existing table with word ALTER:
ALTER TABLE my_contacts
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (contact_id);
Chapter 5: ALTER
Adding colum to an existing table to certain place in table with word AFTER or BEFORE:
ALTER TABLE my_contacts
ADD COLUMN phone INT NOT NULL AFTER column_before,
Renaming the column:
ALTER TABLE projekts
CHANGE COLUMN old_column_name new_column_name new_data_type [other_modifiers];
Use CHANGE when you want to change both the name and the data type of a column..
Change the data type of a column with MODIFY keyword:
ALTER TABLE projekts
MODIFY COLUMN proj_desc VARCHAR(120);
You can use the MODIFY keyword. It changes only the data type of a column and leaves the name alone.
DROP your column:
ALTER TABLE projekts
DROP COLUMN start_date;
Renaming the table:
RENAME TABLE old_table_name TO new_table_name;
To SELECT the last two character:
SELECT RIGHT(location, 2) FROM my_contacts;
To SELECT everything in front of the specific character or string:
SELECT SUBSTRING_INDEX(location, ',', 1) FROM my_contacts;
SUBSTRING_INDEX: "This grabs part of the column, or substring. It looks for the string in single quotes (in this case, it’s a comma) and grabs everything in front of it."
location: "Again, the column name."
',': "Here’s the comma the command is looking for."
1 : "This is the tricky part. It’s “1” because it’s looking for the first comma. If it were “2” it would keep going until it found a second comma and grab everything in front of that.."
Use a current column to fill a new column:
UPDATE my_contacts
SET new_still_empty_colum = old_colum_with_value
Each row in our table goint to set, one at a time, to this value
Chapter 6: advanced SELECT
AND & OR:
Using AND: Both conditions must be true
Using OR: Either conditions must be true
If statement in SQL:
UPDATE movie_table
SET temp =
CASE
WHEN category = 'gore' OR category = 'horror'
THEN 1
ELSE 0
END;
Sort your data with ORDER BY:
SELECT title, category
FROM movie_table
WHERE title LIKE 'A%'
ORDER BY title
Function SUM() for add them for us:
SELECT SUM(colum_name)
FROM table_name
WHERE column_name = 'whatever you looking for'
SUM all of them at once with GROUP BY:
SELECT first_name, SUM(colum_name)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(colum_name)
Since GROUP BY automatically eliminates duplicates within each group, it achieves the same result as DISTINCT
keyword DISTINCT to get rid of duplications:
SELECT DISTINCT sale_date
FROM cookie_sales
Since GROUP BY automatically eliminates duplicates within each group, it achieves the same result as DISTINCT
keyword LIMIT:
SELECT first_name, SUM(colum_name)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(colum_name)
LIMIT 2
COUNT Function and SUM function:
SELECT COUNT(sales) AS total_numbers_of_sales, SUM(sales) AS total
FROM cookie_sales
Chapter 7: Multi-table Database Design:
The FOREIGN KEY is a column in a table that references the PRIMARY KEY of another table.
How to create table with CONSTRAINT FOREIGN KEY REFERENCES to Parent Table (so child knows where to looking for):
CREATE TABLE interest
(
int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
interest VARCHAR(50) NOT NULL,
contact_id INT NOT NULL,
CONSTRAINT my_contacts_contact_id_fk FOREIGN KEY (contact_it) REFERENCES my_contacts (contact_id)
);
Patterns of data: one-to-one.
Patterns of data: one-to-many.
Patterns of data: getting to many-to-many.
Many-to-Many: a junction table holds a key from each table.
A COMPOSITE KEY is a PRIMARY KEY composed of multiple columns, creating a unique key.
2NF Second Normal Form:
Rule 1: Be in 1NF
Rule 2: Have no partial functional dependencies.
Any table with an artificial primary key (or know as a surrogate keys - include auto-incrementing integer columns (like ID or CustomerID) or globally unique identifiers (GUIDs)) and no composite primary key is always 2NF.
3NF Third Normal Form:
Rule 1: Be in 2NF
Rule 2: Have no transitive dependencies.
A transitive functional dependency means that any non-key column is related to any of the other non-key columns. If changing any of the non-key columns might cause any of the other columns to change, you have a transitive dependency.
Chapter 8: Joins And Multi-table Operations
SUBSTR function.
INSERT INTO with SELECT:
CREATE TABLE with SELECT and AS:
Column aliases:
There’s one small difference between the two queries. All queries return the results in the form of tables. The alias changes the name of the column in the results but it doesn’t change the original column name in any way. An alias is temporary.
Table aliases:
Cartesian join or Cartesian product or Cross Product or Cross Join or “no join.” or Comma Join:
The Cartesian join takes each value in from the first table and pairs it up with each value from the second table.
COMMA JOIN The same thing as a CROSS JOIN, except a comma is used instead of the keywords CROSS JOIN
Inner Join:
An INNER JOIN combines the records from two tables using comparison operators in a condition (see line 7 with 'ON' keyword). Columns are returned only where the joined rows match the condition.
Inner Join - non-equijoin:
The non-equijoin returns any rows that are not equal..
Inner Join - the natural join:
There’s only one kind of inner join left, and it’s called a natural join. Natural joins only work if the column you’re joining by has the same name in both tables. Consider these two tables again.
He died but he is alive !
Left Join, Right Join, Inner Join, Outer (Full) Join - Types of joins SQL:
Chapter 9: Subqueries - Queries Within Queries
IN Keyword:
The IN keyword in SQL is used to specify a set of values within a WHERE clause to filter your results. It acts as a shorthand for writing multiple OR conditions.
Page 394:
INSERT INTO clown_info
Chapter 10:
If value contain apostrofe put \ backslash before it:
INSERT INTO clown_info
VALUES
('MR. Kop\'ye');
Chapter 11:
If value contain apostrofe put \ backslash before it:
INSERT INTO clown_info
VALUES
('MR. Kop\'ye');
Chapter 12:
If value contain apostrofe put \ backslash before it:
INSERT INTO clown_info
VALUES
('MR. Kop\'ye');
Romans 8:28 - And we know that in all things God works for the good of those who love him, who have been called according to his purpose.