3.
SQL DML Commands
CREATE DATABASE campusx
CREATE TABLE users(
user_id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
)
SHOW TABLES;
Tables_in_campusx
users
1. INSERT
INSERT INTO campusx.users (user_id, name, email, password)
VALUES (NULL, 'nitish', 'nitish@gmail.com', '1234')
SELECT * FROM users;
user_id name email password
NULL nitish nitish@gmail.com 1234
INSERT INTO campusx.users
VALUES (NULL, 'ankit', 'ankit@gmail.com', '1234')
SELECT * FROM users;
user_id name email password
NULL nitish nitish@gmail.com 1234
NULL ankit ankit@gmail.com 1234
INSERT INTO campusx.users (name, email)
VALUES ('amit', 'amit@gmail.com')
SELECT * FROM users;
user_id name email password
NULL nitish nitish@gmail.com 1234
NULL ankit nitish@gmail.com 1234
NULL amit amit@gmail.com NULL
INSERT INTO campusx.users (password, name)
VALUES ('123456','rupesh')
SELECT * FROM users;
user_id name email password
NULL nitish nitish@gmail.com 1234
NULL ankit nitish@gmail.com 1234
NULL amit amit@gmail.com NULL
NULL rupesh NULL 123456
INSERT INTO campusx.users VALUES
(NULL, 'rishabh','rishabh@gmail.com', '12345'),
(NULL, 'rohan','rohan@gmail.com', '12345'),
(NULL,'rahul','rahul@gmail.com', '12345')
SELECT * FROM users;
user_id name email password
NULL nitish nitish@gmail.com 1234
NULL ankit nitish@gmail.com 1234
NULL amit amit@gmail.com NULL
NULL rupesh NULL 123456
NULL rishabh rishabh@gmail.com 12345
NULL rohan rohan@gmail.com 12345
NULL rahul rahul@gmail.com 12345
2. SELECT
Importing Data in MySQL Worlbench
Schemas --> Select Database --> Right Click on Tables --> Table Data Import Wizard --> Select File Path --> Encoding UTF-8 -->
Change Data Type if needed --> Finished Import.
SELECT * FROM campusx.smartphones_cleaned_v6 WHERE 1;
★ means we are fetching all columns and WHERE 1 means we are not applying any conditions on rows or we are fetching all rows.
SELECT model,price,rating FROM campusx.smartphones_cleaned_v6;
Renaming Column Names (Aliasing)
SELECT os AS "Operating System",model,battery_capacity AS "MAh" FROM
campusx.smartphones_cleaned_v6;
Creating Expressions Using Columns
Calculating PPI of Smartphones
SELECT model,
SQRT(resolution_width*resolution_width +
resolution_height*resolution_height)/screen_size AS "PPI"
FROM campusx.smartphones_cleaned_v6;
SELECT model, "smartphone" AS "Type"
FROM campusx.smartphones_cleaned_v6;
Distinct
SELECT DISTINCT(brand_name) AS 'All brands'
FROM campusx.smartphones_cleaned_v6;
SELECT DISTINCT brand_name, processor_brand
FROM campusx.smartphones_cleaned_v6;
Filtering Rows WHERE Clause
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE brand_name = "samsung";
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE price>50000
Operators
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE price>10000 AND price<20000
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE price BETWEEN 10000 AND 20000
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE price < 15000 AND rating < 80
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE brand_name = 'samsung' AND ram_capacity > 8
Query Execution Order
A SELECT statement can have many clauses so it is important to understand the order in which these are executed to provide the result.
However, for ease of understanding we can refer to the execution order by FJWGHSDO.
A quick way to remember this is to use the mnemonic "Frank John's Wicked Grave Haunts Several Dull Owls". In this section we will
focus on FROM, WHERE, SELECT and DISTINCT keywords. The first step is always the FROM clause as we need to identify the tables
from which data has to be fetched SELECT must be always be executed after the WHERE clause, e.g. we can have a query
SELECT EName FROM Employee WHERE Id = 1
Here the filtering needs to happen on an Id column which is not included in the SELECT clause. Unless SELECT executes after WHERE
this functionality cannot be supported DISTINCT removes duplicates based on all columns of the SELECT clause. These columns could
be a subset of all columns of the table OR may even contain derived columns through the use of an expression. Thus DISTINCT is
dependent on SELECT clause and it's execution must happen after SELECT clause.
SELECT DISTINCT(brand_name) FROM campusx.smartphones_cleaned_v6
WHERE price > 100000
IN and NOT IN
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE processor_brand = "snapdragon" OR processor_brand = "bionic" OR processor_brand
= "exynos"
Same code with IN operator
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE processor_brand IN ("snapdragon", "bionic", "exynos")
3. UPDATE
All phones with dimensity Processor
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE processor_brand = "dimensity"
Changing Processor name from dimensity to mediatek
UPDATE campusx.smartphones_cleaned_v6
SET processor_brand = "mediatek"
WHERE processor_brand = "dimensity"
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable
safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
Updating Multiple Columns
UPDATE campusx.users
SET email = 'nitish@yahoo.com',password='123456'
WHERE name = 'nitish'
4. DELETE
delete all phones price > 200000
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE price > 200000
DELETE FROM campusx.smartphones_cleaned_v6
WHERE price > 200000
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE primary_camera_rear > 150
DELETE FROM campusx.smartphones_cleaned_v6
WHERE primary_camera_rear > 150 AND brand_name = "samsung"
Note : Update and Delete are Permanent Operations, so be careful while performing it.
Functions in SQL
Aggregate Functions or Group Functions
MAX and MIN
SELECT MAX(price) FROM campusx.smartphones_cleaned_v6
SELECT MIN(price) FROM campusx.smartphones_cleaned_v6
SELECT MAX(ram_capacity) FROM campusx.smartphones_cleaned_v6
Find the price of the costliest samsung phone
SELECT MAX(price) FROM campusx.smartphones_cleaned_v6
WHERE brand_name = 'samsung'
Finding Costliest phone
SELECT * FROM campusx.smartphones_cleaned_v6
WHERE brand_name = 'samsung' AND price = "110999"
AVG
SELECT AVG(rating) FROM campusx.smartphones_cleaned_v6
WHERE brand_name = 'apple'
SUM
SELECT SUM(price) FROM campusx.smartphones_cleaned_v6
WHERE brand_name = 'apple'
COUNT
Find the number of oneplus phones
SELECT COUNT(*) FROM campusx.smartphones_cleaned_v6
WHERE brand_name = 'oneplus'
COUNT(DISTINCT)
Find the number of brands available
SELECT DISTINCT (brand_name) FROM campusx.smartphones_cleaned_v6
SELECT COUNT(DISTINCT (brand_name)) FROM campusx.smartphones_cleaned_v6
STD (Standard Deviation)
find std of screen sizes
SELECT STD(screen_size) FROM campusx.smartphones_cleaned_v6
VARIANCE
SELECT VARIANCE(screen_size) FROM campusx.smartphones_cleaned_v6
Scalar Functions
ABS
SELECT ABS(100000 - price) AS "temp" FROM campusx.smartphones_cleaned_v6
ROUND
round the ppi to 1 decimal place
SELECT model,
ROUND(SQRT(resolution_width*resolution_width +
resolution_height*resolution_height)/screen_size,1) AS "PPI"
FROM campusx.smartphones_cleaned_v6;
CEIL
4.1 -> 5
ceil the screen size
SELECT CEIL(screen_size) FROM campusx.smartphones_cleaned_v6
FLOOR
4.1 -> 4
floor the screen size
SELECT FLOOR(screen_size) FROM campusx.smartphones_cleaned_v6
