All SQL Query
Updated: May 25, 2022
Select all Customers ==== SELECT * FROM CUSTOMERS ( CUSTOMERS --- TABLE Name)
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
Selecting two columns from 1 table =====
SELECT column1, column2 FROM table_name
SELECT different columns from a table (It will only take values which are different then others no same value will be taken)
SELECT DISTINCT Column_Name FROM Table_Name
Count the Values
SELECT COUNT(DISTINCT Column_Name) FROM Table_Name
WHERE CLASS in SQL
'WHERE' is used to filter Records
SELECT Coulumn_1, Column_2
FROM Table_Name
WHERE condition
Example:
SELECT * FROM Customers
WHERE Country = 'Mexico' |||||||||||||||| For String File
WHERE Country Id = 1 ||||||||||||||||||||||| For Numeric Value
Operations in WHERE Clause
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range
LIKE 1Search for a pattern
IN To specify multiple possible values for a column
Sql AND , OR and NOT Operations
WHERE can be combined with these 3 operations all together
AND operations displays that all the conditions in them are true
OR operations display that any of these conditions can be true
NOT operations displays that the condition is not true
AND
SELECT Column_1, Column_2 , .......
FROM table_name
WHERE condition1 AND condition2 AND condtion3
OR
SELECT Column_1, Column_2 ......
FROM table_name
WHERE condition1 OR condition2 OR condtion3
NOT
SELECT Column_1, Column_2 ......
FROM table_name
WHERE NOT condition
Combining All these operations all together
SELECT * FROM customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Munchen');
AND & NOT Operations
SELECT * FROM customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA'
ORDER BY ======= keyword is used to sort result-set in ascending or descending order
Default ---- ascending
for descending use DESC keyword
example:
SELECT column_1, column_2
FROM table_name
ORDER BY column_1, column_2 ASC|DESC
Work on two different things at the same time
SELECT * FROM customers
ORDER BY Country ASC, CustomerName DESC
SQL INSERT into Statement
INSERT INTO ==== there are two ways in which we can insert data into database
Specify both column name and values which is going to be inserted
example:
INSERT INTO table_name (column_1, column_2, ....... )
VALUES (value_1, value_2, .............);
2. Adding Values for all columns then
INSERT INTO table_name
VALUES (value_1, value_2, ..........);
NULL VALUES
How to test for Null Values?
We cannot test null values with the operations =,>,<,! ,<>
IS NULL, IS NOT NULL
Example
NULL ====== values which are null
SELECT column_name
FROM table_name
WHERE column_name IS NULL;
NOT NULL ===== Values which are not NULL
SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;
SQL UPDATE Statement
UPDATE
UPDATE tabel_name
SET column_1 = value_1, column_2 = value_2,....
WHERE condition;
(((((If we Omit WHERE Then it will update all the values of the column))))
UPDATE Customers
SET ContactName = 'Alfred' , City = 'Berlin'
WHERE Customer ID = 1;
How to update multiple records?
UPDATE Customers
SET customersName = 'Juan'
WHERE Country = 'Mexico';
What Happens when we ommit WHERE
UPDATE Customers
SET customersName = 'Juan'
====== It will convert every name as Juan
SQL DELETE Statement
DELETE FROM
DELETE is used to delete existing data from the database
DELETE FROM table_name WHERE condition
Delete Complete Table
DELETE FROM table_name
Delete Everything on that table
DELETE FROM Customers;
SQL TOP, LIMIT, FETCH FIRST or ROWNUM Clause
SELECT TOP used to specify the number of records to return
SELECT TOP is useful on large tables , but it impacts the performance of the database
SELECT TOP number/percent column_name
FROM table_name
WHERE condition;
In MySQL
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
Example
SELECT TOP 3 * FROM Customers
WHERE Country = 'Germany';
If we want to find according to the percent then
SELECT TOP 50 PERCENT * FROM Customers
SQL Min(), Max() Functions
MIN() returns the smallest value of the selected column
MAX() return the largest value of the selected column
MIN() Syntax
SELECT MIN(column_name)
FROM table_name WHERE condition
MAX() Syntax
SELECT MAX(column_name)
FROM table_name WHERE condition
SQL COUNT(), AVG() and SUM() Functions
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG() Syntax
SELECT AVG (column_name)
FROM table_name
WHERE condition;
SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE Condition;
SQL LIKE
LIKE is used in WHERE clause to search for a specified pattern in a column
SQL LIKE Syntax
There are two wildcards used in conjunctions with the LIKE operator
% sign represents Zero, one or multiple characters
_ represents one, single character
MS Access use * instead of % and ? instead of _
SELECT column_1, column_2
FROM table_name
WHERE column LIKE pattern
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
Examples
Search all customersName whose second letter is r
SELECT * FROM Customers
WHERE CustomerName LIKE'_r%';
Search all customerName whose name starts with a and are atleast 3 charactes in length
SELECT * FROM Customers
WHERE CustomersName LIKE 'a__%'
Search all customersName which does not starts with a
SELECT * FROM Customers
WHERE CustomersName NOT LIKE 'a%
Search all customersName which starts with a and ends with o
SELECT * FROM Customers
WHERE CustomersName LIKE 'a%o';
Search CustomersName which has "or" anywhere in the CustomersName Column
SELECT * FROM Customers
WHERE CustomersName LIKE '%or%'
SQL WIldCards
% Represents zero or more characters === bl%--- black, blue
_ Represents a single Character === h_t finds hit , hot and hat
[] Represents any single character within the character === h[oa] finds hot, hat but not hit
^ Represents any character not in brackets === h[^oa] finds hit but not hot and hat
- Represents any single character within the specified range === c[a-b] finds cat and cbt
_ SQL Wildcard
WildCard in MS Access
! Represents any character not in the brackets === h[!oa]t finds hit, but not hot and hat
? Represents a single character h?t finds hot, hat, and hit
# Represents any single numeric character ==== 2#5 finds 205, 215, 225, 235, 245, 255.....
SELECT * FROM Customers
WHERE City LIKE'_ondon';
SELECT * FROM Customers
WHERE City LIKE'L_n_on';
[] Syntax
SELECT * FROM Customers
WHERE City LIKE'[bsp]%';
Selecting cities which starts with a, b, c and d
SELECT * FROM Customers
WHERE City LIKE '[a-d]%;
Select cities which does not starts with a,b,c
SELECT * FROM Customers
WHERE City LIKE '[!abc]%
OR
SELECT * FROM Customers
WHERE City NOT LIKE '[abc]%';
The SQL IN Operator
IN operator allows us to specify multiple values in WHERE Clause
IN is also a shorthand for multiple OR conditions
SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2.........)
OR
SELECT column_name(s)
FROMtable_name
WHEREcolumn_nameIN (SELECT STATEMENT);
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
Search Customers data where the countries are only from Suppliers.
SELECT * FROM Customers
WHERE Country IN ( SELECT Country FROM Suppliers );
SQL Between Operator
Between selects values within a given range
Between operator is inclusive begin and end values are included
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
NOT Between Query
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Between with IN example
SELECT * FROM Products
WHERE Price Between 10 AND 20
AND Category ID NOT IN (1,2,3);
selects all products with a ProductName not between Carnarvon Tigers and Mozzarella di Giovanni:
SELECT * FROM Products
WHERE Product Name NOT BETWEEN 'Carnarvon Tigers'AND'Mozzarella di Giovanni'
ORDER BY Product Name;
selects all orders with an OrderDate between '01-July-1996' and '31-July-1996':
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;
OR
SELECT * FROM Orders
WHERE OrderDate BETWEEN'1996-07-01'AND'1996-07-31';
SQL Aliases
used to give temporary name
'Aliases created with AS
Syntax
SELECT column_name AS alias_name
FROM table_name;
OR
SELECT column_name
FROM table_name as alias_name
Alias for address
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
OR
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
Aliases for tables
SELECT o.orderId, c.customerName, o.orderDate
FROM Customers AS c, Oders AS o
WHERE CustomerName= 'Around the world' AND c.customerID = o.customerID;
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn'AND Customers.CustomerID=Orders.CustomerID;