SQL Syntaxes

Select data from a table

To select all(*) the data from a table 
SELECT *
FROM TABLE_NAME;

To select specific columns of a table
SELECT COLUMN_1, COLUMN_2,..., COLUMN_n
FROM TABLE_NAME;;
DISTINCT - To select distinct(different) values
SELECT DISTINCT COLUMN_1, COLUMN_2,..., COLUMN_n
FROM TABLE_NAME;
To list the number of distinct rows
SELECT COUNT(DISTINCT COLUMN_NAME)
FROM TABLE_NAME;
or
SELECT COUNT(*) AS COLUMN_NAME_IDENTIFIER
FROM (SELECT DISTINCT COLUMN_NAME FROM TABLE_NAME);
To give a condition to the select statement
SELECT COLUMN_1, COLUMN_2,...,COLUMN_n
FROM TABLE_NAME 

WHERE condition

When using the WHERE condition,
1. if it's an integer
COLUMN_NAME = INT_VALUE
2. if it's a string (single quotation is required)
COLUMN_NAME = 'STRING_VALUE'
3. if it's a date (single quotation is required and the format must be YYYY-MM-DD)
COLUMN_NAME = 'YEAR-MONTH-DATE'

Using AND syntax with WHERE condition

SELECT COLUMN_1, COLUMN_2, COLUMN_n
FROM TABLE_NAME
WHERE CONDITION_1 AND CONDITION_2 AND ... OR CONDITION_n;

Using OR syntax with WHERE condition

SELECT COLUMN_1, COLUMN_2, COLUMN_n
FROM TABLE_NAME
WHERE CONDITION_1 OR CONDITION_2 OR ... OR CONDITION_n;

Using NOT syntax with WHERE condition

SELECT COLUMN_1, COLUMN_2, COLUMN_n
FROM TABLE_NAME
WHERE NOT CONDITION;
You can use AND, OR, NOT syntaxes together to form complex expressions
Ex:
SELECT * 
FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Munchen');
This selects all the records from the customers table having Germany as the country and Berlin or Munich as the city
Ex:
SELECT *
FROM Customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA';
This selects all the records from the customers table not having Germany and USA as the country

ORDER BY syntax is used to order the selected records

SELECT COLUMN_1, COLUMN_2,...,COLUMN_n
FROM TABLE_NAME
ORDER BY COLUMN_1, COLUMN_2,...COLUMN_n ASC;
or 
ORDER BY COLUMN_1, COLUMN_2,...COLUMN_n DESC;
ASC | DESC
ORDER by ASC - Ascending order
ORDER by DESC - Descending order
If you just type ORDER BY COLUMN_NAME
SELECT * 
FROM TABLE_NAME
ORDER BY COLUMN_NAME;
It will sort the selected rows by that column

Order by several columns

SELECT *
FROM TABLE_NAME
ORDER BY COLUMN_1 ASC, COLUMN_2 DESC;
In this way the table is ordered first in the ascending order of the COLUMN_1 and then in the decending order of the COLUMN_2

INSERT INTO

To insert data to a table, use the INSERT INTO syntax
To add data to selected columns you have to use the below method
INSERT INTO TABLE_NAME (COLUMN_1, COLUMN_2)
VALUES ('value1', 'value2');
Ex: 
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
If you insert all the data to the table then you don't need to give the column names
INSERT INTO TABLE_NAME
VALUES ('value1', 'value2',...);
Ex :
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
You can check if a column has null values by using the following syntax
SELECT COLUMN_NAMES
FROM TABLE_NAME
WHERE COLUMN_NAME IS NULL;
or
SELECT COLUMN_NAMES
FROM TABLE_NAME
WHERE COLUMN_NAME IS NOT NULL; 

UPDATE

To update a record use the UPDATE statement
UPDATE TABLE_NAME
SET COLUMN_1 = value1, COLUMN_2 = value2...
WHERE CONDITION;
Ex :
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
If you omit the WHERE clause then all the records will be updated, Therefore remember to use the WHERE clause

DELETE

To delete a record from a table, use the DELETE syntax
DELETE FROM TABLE_NAME
WHERE CONDITION;
Ex :
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';

Delete all records

To delete all the records from the table then you can use both of the following syntaxes
DELETE FROM TABLE_NAME;
or

DELETE * FROM TABLE_NAME

MIN & MAX

To find the minimum value or the maximum value of a column then use the MAX and MIN syntaxes
SELECT MAX(COLUMN_NAME)
FROM TABLE_NAME
WHERE CONDITION;
or
SELECT MIN(COLUMN_NAME)
FROM TABLE_NAME
WHERE CONDITION;
Ex :
SELECT MIN(Price) AS SmallestPrice
FROM Products;
Here AS syntax is used to display the min price as the smallest price

COUNT

To get the count of the number of rows with a certain condition you can use the COUNT syntax
SELECT COUNT(COLUMN_NAME)
FROM TABLE_NAME
WHERE CONDITION;

AVERAGE 

To have the average of a certain coloumn use the AVG syntax
SELECT AVG(COLUMN_NAME)
FROM TABLE_NAME
WHERE CONDITION;

SUM

To have the sum of the values of a certain coloumn use tbe SUM syntax
SELECT SUM(COLUMN_NAME)
FROM TABLE_NAME
WHERE CONDITION;

SQL LIKE syntax

SELECT COLUMN_1, COLUMN_2
FROM TABLE_NAME
WHERE COLUMN_1 LIKE pattern;
pattern can be as follows,
  • 'a%'         Finds any values that start with "a"
  • '%a'         Finds any values that end with "a"
  • '%or%' Finds any values that have "or" in any position
  • '_r%'         Finds any values that have "r" in the second position
  • 'a_%_%' Finds any values that start with "a" and are at least 3 characters in length
  • 'a%o' Finds any values that start with "a" and ends with "o"
Ex :
SELECT * 
FROM Customers
WHERE CustomerName LIKE 'a%';
Here this selects the records starting from 'a'

NOT LIKE 

NOT LIKE syntax is used to select records which doesn't match with the given pattern
SELECT COLUMN_1, COLUMN_2
FROM TABLE_NAME
WHERE COLUMN_1 NOT LIKE pattern;
Ex :
SELECT * 
FROM Customers
WHERE CustomerName NOT LIKE 'a%';
This selects the records that do not start from 'a'

Wildcards

% The percent sign represents zero, one, or multiple characters
_ The underscore represents a single character
'a%'         Finds any values that starts with "a"
'%a'         Finds any values that ends with "a"
'%or%' Finds any values that have "or" in any position
'_r%'         Finds any values that have "r" in the second position
'a_%_%' Finds any values that starts with "a" and are at least 3 characters in length
'a%o' Finds any values that starts with "a" and ends with "o"
Ex :
SELECT * 
FROM Customers
WHERE City LIKE '_erlin';
This selects all the rows that contain citys that starts with any character followed by erlin

[!charlist]

To select the records which doesn't match with the given pattern use the [!charlist] wildcard
SELECT *
FROM TABLE_NAME 
WHERE COLUMN_NAME LIKE '[!bsp]%';
Ex : 
SELECT * 
FROM Customers
WHERE City LIKE '[!bsp]%';
This selects the records that do not start with b, s or p
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
This is also the same

IN

IN operator is a shorthand for multiple OR syntaxes
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN_NAME IN (value1, value2, ...);
This is the same as
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN_NAME = value1 OR COLUMN_NAME = value2 OR COLUMN_NAME = ..... ;

NOT IN

You can use NOT IN syntax to select the records which do not match with the given condition
SELECT COLUMN_NAME
FROM TABLE_NAME
WHERE COLUMN_NAME NOT IN (value1, value2,...);
Ex : 
SELECT * 
FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
IN can be also used to have a SELECT statement inside another SELECT statement
Ex :
SELECT * 
FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

src : https://www.w3schools.com/sql/

Comments

Post a Comment

Popular posts from this blog

Getting started with MySQL using wamp server

After installing the wamp server on your computer follow the below steps. Step 1 : Run the command prompt.  Step 2 : Copy the path of the bin folder of the wamp server. usually it is, C:\wamp64\bin\mysql\mysql5.7.19\bin Step 3 : Go back to the command prompt and change the directory to the path you copied C:\Users\admin>cd C:\wamp64\bin\mysql\mysql5.7.19\bin Step 4 : Then type the command "mysql -u root -p", then press enter. C:\wamp64\bin\mysql\mysql5.7.19\bin>mysql -u root -p Step 5 : Then it will request the password, just enter because there isn't a password. Enter password: Step 6 : If you have successfully connected following message will be prompted. Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.19 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and...
Read more

Supporting code snippets for OOP take home assignment

Open a file and read data line by line. You're given a text(.txt) file as the input and not allowed to do manual user inputs. So you have to read the text file using your program. It can be done in the following way. Here the line is taken into a String called 'line'. To read lines you have to use the library 'fstream' #include <fstream> string line; ifstream myfile ("yourfilename.txt"); if (myfile.is_open()){ while(getline(myfile,line)){ // Enter your code here } myfile.close(); } src :  http://www.cplusplus.com/doc/tutorial/files/ Spliting string into two with blank space Method 1 Here 'line' is the String which we read from the file. To store the two sub strings we use a vector called tokens of type String. If you use this method after splitting the string into two parts you have to cast it into int. #include <vector> #include <string> #include <sstream> #include <it...
Read more

Java ADF surfing through pages using buttons

Image
Open 'Oracle JDeveloper Studio' by double clicking on the icon. Select Studio Developer (All Features) and press 'Ok'.  Press 'Finish' We need to create a folder before creating page fragments. Select from gallery Add a folder name and press 'Ok'.  Then create page fragments. Here we are studying the connection between two pages therefore we need to create two page fragments. Change the file name and press 'Ok'. At the components panel search 'panel box' then drag and drop a panel box onto the page fragment.  Then drag and drop a button from the panel 'General Controls'. You can change the names of the button and the panel box at the 'Common' tab. Then go to page flow in web content (in Applications tab) and create a ADF Task Flow. ...
Read more