3 rd year elite SQL
–3&4
Mr. Sai Sushanth Durvasula
SQL Query as a way to solve a problem!
Transformation(s)
Input Output
transformations are functions in DSA
world
and transformations are sql queries
in DB world!
Types of SQL Commands
DDL (Data Definition Language) :
create, alter, rename, truncate & drop
DML (Data Manipulation Language) :
insert, update & delete
DCL (Data Control Language) :
grant & revoke permission to users
DQL (Data Query Language) :
select
TCL (Transaction Control Language) :
start transaction, commit, rollback etc
SUMMARY OF KEYWORDS SEEN SO FAR
Goal/Task Keyword/Queries Example
Create a new database CREATE DATABASE <db_name>; create database elite_db;
to hold objects
Delete the database DROP DATABASE <db_name>; drop database elite_db;
created
Create a table to hold CREATE TABLE <table_name> ( create table tbl1 (
data column_name1 datatype constraint, name varchar(30),
column_name2 datatype constraint, phone_num varchar(15),
….); email varchar(20),
created_at date
);
Delete the table DROP TABLE <table_name> drop table tbl1;
created
Project selective data SELECT col1 select name
from table , col3 , email
FROM table_name; from tbl1
Note: * For selecting all columns
SUMMARY OF KEYWORDS SEEN SO FAR
Goal/Task Keyword/Queries Example
Filtering records SELECT .. select name
retrieved FROM.. , email
WHERE <where_condition> from tbl1
(AND,OR,NOT,BETWEEN,LIKE) where name = 'Bob';
Give alternate ALIAS SELECT ID AS CustomerID
, CustomerName AS “Customer Name”
name to table or FROM Customers;
column
Add records to INSERT INTO table_name insert into tbl1
table (colname1, colname2); (name,email,phone_num)
VALUES values
(col1_v1, col2_v1), ('Jayneesh','jayneesh@psit.ac.in',987654
(col1_v2, col2_v2); 322),
('Harshit','harshit@psit.ac.in',87856465
);
SQL Datatypes
They define the type of values that can be stored in a column
Modify records - UPDATE
UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Be careful when updating records in a table! Notice
the WHERE clause in the UPDATE statement.
The WHERE clause specifies which record(s) that should be updated.
If you omit the WHERE clause, all records in the table will be
updated!
Modify records - UPDATE Example
UPDATE Customers
SET PostalCode = 05344
WHERE Country = ’UK';
Remove records from table - DELETE
DELETE statement is used to delete the existing records in a table.
DELETE
FROM table_name
WHERE condition;
Be careful when deleting records in a table! Notice
the WHERE clause in the DELETE statement.
The WHERE clause specifies which record(s) that should be deleted.
If you omit the DELETE clause, all records in the table will be
deleted!
Remove records from table - DELETE
Example
DELETE FROM Customers
WHERE CustomerName=‘Aravind';
MySQL vs Mongo DB
String Pattern Matching - LIKE
The LIKE operator is used in a WHERE clause to search for a
specified pattern in a column.
There are two wildcards often used in conjunction with the
LIKE operator
• The percent sign % represents zero, one or multiple
characters
• The underscore sign _ represents one, singe character
LIKE Example
SELECT *
FROM Customers
WHERE CustomerName LIKE 'a%';
Can you guess the output?
The _ Wildcard
The _ wildcard represents a single character.
It can be any character or number, but each _ represents one,
and only one, character
Return all customers from a city that starts with ‘I' followed
by one wildcard character, then ‘d' and then two wildcard
characters:
SELECT *
FROM Customers
WHERE country LIKE ’I_d__';
The % Wildcard
The % wildcard represents any number of characters, even
zero characters.
Return all customers from a city that contains the letter 'L':
SELECT *
FROM Customers
WHERE country LIKE ’%L%';
SELECT *
FROM Customers
WHERE CustomerName LIKE ’Al%';
The % Wildcard
You can also combine any number of conditions using
AND or OR operators
SELECT *
FROM Customers
WHERE CustomerName LIKE 'a%’ OR
CustomerName LIKE 'b%';
Return all customers that starts with "b" and ends
with "s":
SELECT *
FROM Customers
WHERE CustomerName LIKE ’b%s’;
Return all customers that contains the phrase ‘or’
SELECT *
FROM Customers
WHERE CustomerName LIKE ’%or%’;
Return all customers that starts with “a" and are
atleast 3 characters in length:
SELECT * FROM Customers
WHERE CustomerName LIKE ’a___%’;
Return all customers that contain “r” in the second
position
SELECT * FROM Customers
WHERE CustomerName LIKE ’_r%’;
Return all customers from India:
SELECT *
FROM Customers
WHERE Country LIKE ‘India’;
LIMIT Clause
The LIMIT clause is used to specify the no.of records to return
There are two wildcards often used in conjunction with the
LIKE operator
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
The following SQL statement selects the first three
records from the "Customers" table:
SELECT *
FROM Customers
LIMIT 3;
What if we want to select records 4 - 6 (inclusive)?
MySQL provides a way to handle this: by using
OFFSET.
The SQL query below says "return only 3 records,
start on record 4 (OFFSET 3)":
SELECT *
FROM Customers
LIMIT 3 OFFSET 3;
ORDER BY Keyword
This keyword is used to sort the result-set in
ascending or descending order.
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
The following SQL statement selects all customers
from the "Customers" table, sorted DESCENDING by
the "Country" column:
SELECT * FROM Customers
ORDER BY country DESC;
SELECT * FROM Customers
ORDER BY country ASC,
CustomerName DESC;
