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]%

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.........)

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#;

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;

SELECT column_name 
FROM table_name as alias_name

Alias for address

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

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;