SQL Queries

SQL SELECT Syntax

SELECT column_name(s) FROM table_name and SELECT * FROM table_name

SQL SELECT DISTINCT Syntax

SELECT DISTINCT column_name(s) FROM table_name - The DISTINCT keyword can be used to return only distinct (different) values.

SQL WHERE Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator value

The AND & OR Operators

AND

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'

The AND operator displays a record if both the first condition and the second condition is true.

OR
SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='Ola'
The OR operator displays a record if either the first condition or the second condition is true.

SQL ORDER BY Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

SQL INSERT INTO Syntax

INSERT INTO table_name VALUES (value1, value2, value3,...) (or)

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

SQL UPDATE Syntax

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

SQL DELETE Syntax

DELETE FROM table_name WHERE some_column=some_value

The TOP Clause

SELECT TOP 50 PERCENT * FROM Persons- select half data from persons table.

SQL LIKE Syntax

SELECT * FROM Persons WHERE City LIKE 's%'

SQL Wildcards 

SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist]or [!charlist]	Any single character not in charlist

SQL IN Syntax

SELECT * FROM Persons WHERE LastName IN ('Hansen','Pettersen')

SQL BETWEEN Syntax -to find the names of the students between age 10 to 15 years, the query would be like,

SELECT first_name, last_name, age FROM student_details WHERE BETWEEN 10 AND 15;

Different SQL JOINS


Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

JOIN Syntax

SELECT col1, col2, col3...FROM table_name1, table_name2 WHERE table_name1.col2 = table_name2.col1;

The SQL SELECT INTO Statement

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.

SELECT LastName,FirstName INTO Persons_Backup FROM Persons

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

SQL CREATE TABLE Syntax

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... )

The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.
DROP TABLE table_name

The DROP DATABASE Statement

The DROP DATABASE statement is used to delete a table.
DROP  DATABASE  table_name

The TRUNCATE TABLE Statement

What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:  TRUNCATE TABLE table_name

SQL ALTER TABLE Syntax

ALTER TABLE table_name ADD column_name datatype- used to add column.

ALTER TABLE table_name DROP COLUMN column_name - used to delete column.

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:

Function	Description
NOW()	Returns the current date and time
CURDATE()	Returns the current date
CURTIME()	Returns the current time
DATE()	Extracts the date part of a date or date/time expression
EXTRACT()	Returns a single part of a date/time
DATE_ADD()	Adds a specified time interval to a date
DATE_SUB()	Subtracts a specified time interval from a date
DATEDIFF()	Returns the number of days between two dates
DATE_FORMAT()	Displays date/time data in different formats

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value in the database:

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
• YEAR - format YYYY or YY

SQL Functions

SQL AVG() Syntax : SELECT AVG(column_name) FROM table_name

SQL COUNT() Syntax : SELECT COUNT(column_name) FROM table_name

SQL FIRST() Syntax : SELECT FIRST(column_name) FROM table_name

SQL LAST() Syntax : SELECT LAST(column_name) FROM table_name

SQL MAX() Syntax : SELECT MAX(column_name) FROM table_name

SQL MIN() Syntax : SELECT MIN(column_name) FROM table_name

SQL SUM() Syntax : SELECT SUM(column_name) FROM table_name

SQL GROUP BY Syntax : SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer

SQL UPPERCASE() Syntax : SELECT UCASE(column_name) FROM table_name

SQL LOWERCASE() Syntax : SELECT LCASE(column_name) FROM table_name

SQL FORMAT() Syntax :SELECT FORMAT(column_name,format) FROM table_name

SQL LENGTH() Syntax : SELECT LEN(column_name) FROM table_name