⚡Created Random data and experimented with different JOINS.
👉Different Types of SQL JOINs to fetch data:
INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN
SELF JOIN
🔹Used MYSQL Commands and Data types:
CREATE TABLE- creates a new table.
SELECT — is used to select data from a database.
INSERT INTO — inserts new data into a database.
LEFT OUTER JOIN (or) LEFT JOIN— this type of Join would fetch all the rows (including NULL values) from the table which is on the left side of the JOIN query.
RIGHT OUTER JOIN (or) RIGHT JOIN— in this type of Join all the records that do not match from the right table are returned with NULL values on the columns for the left side table.
UPDATE — the statement is used to modify the existing records in a table.
ALTER TABLE— the statement is used to add, delete, or modify columns in an existing table.
CROSS JOIN — It produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.
SELF JOIN — It is a join in which a table is joined with itself especially when the table has a FOREIGN KEY which references its PRIMARY KEY.
SET command — is used with UPDATE to specify which columns and values should be updated in a table.
WHERE Clause — is used to filter records.
INT — Integer numerical (no decimal).
VARCHAR — data type stores variable-length character strings.
NOT NULL — If you do not want a column to have a NULL value, then you need to define such a constraint on the column specifying that NULL is now not allowed for a specific column.
A PRIMARY KEY is a field in a table that uniquely identifies each row/record in a database table.
AUTO INCREMENT — allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
INNER JOIN — selects records that have matching values in both tables.
👉OUTER JOIN is used to retrieve all records from tables, even for those records with no matching value in the other table based on the JOIN condition. In such cases, it returns NULL as the value for the missing columns.
🔷There are two Outer Joins:
LEFT OUTER JOIN
RIGHT OUTER JOIN
✅Steps to follow:
🟣Creating office locations table with fields ID, and address.
Create table
Output:
Output — Office locations
🟣Adding a column by using Alter command.
Alter table syntax
Output:
Output — Alter Table
🟣Now inserting values (random data).
Insert Into command
Output:
Output — Insert Into command
🟣Updating the office id column for the Employee table. ID % 2 is checking what the remainder is if you divide ID by 2. If you divide an even number by 2 it will always have a remainder of 0. Any other number (odd) will result in a non-zero value. Which is what is checking for.
Update syntax
Output:
Output — update command
🟣Need location data for all the Employees — i.e. office address of all the Employees. Creating Office locations Table.
🟣LEFT JOIN Syntax:
Left join syntax
👉Now can see the whole table after running the left join syntax.
Left join command — Table
🟣RIGHT JOIN syntax:
Right join syntax
👉Now can see the whole table after running the Right join syntax.
Right join command — Table
🟣CROSS JOIN Syntax:
Cross join syntax
🔺After running the cross join syntax you will see 400 rows in a table. In simple words — the number of rows in the first table is multiplied by the number of rows in the second table.
Output:
🟣SELF JOIN is just like any other INNER or OUTER Join, with the only exception that both the tables i.e. the Joining and Joined table are the same. we have joined the Employee table to itself to find out Employee names that have the same office location and Department
MYSQL Basics to know….PART -1 MYSQL -It is a Cross-Platform, Open-Source, Relational Database management system and free Application. The data in a row is called “ Record ” The data in a column is called “ Field ” CREATE DATABASE -Creates a database in the server. USE command -Switch to the database with specified name. CREATE TABLE -Creates a entire database objects table. Syntax: CREATE TABLE shirts1(shirts_id INT NOT NULL AUTO_INCREMENT,Article VARCHAR(100),color VARCHAR(100), shirt_size VARCHAR(100),last_worn INT, PRIMARY KEY (shirts_id)); DESC(DESCRIBE) command-To see Database Field formats. SELECT command -Extracts Data from a Database. Over here Asterisk “ * “ it selects all the columns from the table. INSERT INTO command-Inserts data into the tables. WHERE command -Is used for Filtering the records(rows). UPDATE command- If you want to change any new Value/data to be added up in the table can use the UPDATE command. Updates ...
MYSQL Basics to know ….PART -2 LoL…I used my YouTube channel name for syntax. YOUTUBE CHANNEL REVERSE() : This command reverses a string. REVERSE( ) syntax SUBSTRING( ) : It works with parts of strings. SUBSTRING( ) syntax UPPER() : Converts a string into upper-case. UPPER( ) syntax LOWER( ) : Converts a string into lower-case. LOWER( ) syntax CONCAT () : This command adds two or more strings together. CONCAT( ) syntax CONCAT_WS () : Concat two or more strings into one string with a separator. CONCAT_WS ( ) syntax REPLACE( ) : Replaces parts(characters)of a string with another set of characters. REPLACE( )syntax DISTINCT( ) : It collects unique values and it won’t show any duplicate values in the table. DISTINCT( ) syntax ORDER BY( ) : This command is used to sort the values in Ascending or Descending order. ASC -Ascending order, DESC-Descending order . In the below syntax the output shows in ascending order. Ascending by default. ORDER BY( ) syntax in Ascending...
Comments
Post a Comment