SQL queries

SQL

Structured Query Language. The standardized relational database language for defining database objects and manipulating data.

SQL delete

DELETE FROM table_name WHERE condition;

Named SQL query

SQLExceptionTranslator

SQLExceptionTranslator, is an interface to be implemented by classes that can translate between SQLExceptions and Spring’s own data-access-strategy-agnostic org.springframework.dao.DataAccessException.

SQLProvider

1. Has one method – getSql()
2. Typically implemented byPreparedStatementCreator implementers.
3. Useful for debugging

SQL Aggregate Functions

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list.

Common aggregate functions include:

1. Average()
2. Count()
3. Maximum()
4. Median()
5. Minimum()
6. Mode()
7. Sum()

SQL aggregate functions return a single value, calculated from values in a column

SQL TOP

1. The TOP clause is used to specify the number of records to return.
2. The TOP clause can be very useful on large tables with thousands of records.
3. Returning a large number of records can impact on performance.

   SELECT TOP number|percent column_name(s)FROM table_name;
   SELECT TOP 2 * FROM Persons;
   SELECT TOP 50 PERCENT * FROM Persons;
   

SQL case insensitive search

SELECT * FROM tutorials t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', ?1,'%'))

Double check that the CONCAT function is working. If not, if you are passing the parameters from a java application, make sure that the parameter is in lower case when you pass it to the query.

SQL LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

SELECT column_name(s)FROM table_name WHERE column_name LIKE pattern;
SELECT * FROM Persons WHERE City LIKE 's%';

SQL Wildcards

1. SQL wildcards can substitute for one or more characters when searching for data in a database.
2. SQL wildcards must be used with the SQL LIKE operator.
3. With SQL, the following wildcards can be used: %, _, [charlist], [^charlist] or [!charlist]

   SELECT * FROM Persons WHERE City LIKE '%nes%';
   SELECT * FROM Persons WHERE FirstName LIKE '_la';
   SELECT * FROM Persons WHERE LastName LIKE 'S_end_on';
   

SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

SELECT column_name(s) FROM table_name WHERE
column_name IN (value1,value2,...);

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

SQL BETWEEN Operator

1. The BETWEEN operator is used in a WHERE clause to select a range of data between two values.
2. The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

   SELECT column_name(s) FROM table_name WHERE
   column_name BETWEEN value1 AND value2;
   
   SELECT * FROM Persons WHERE
   LastName BETWEEN 'Hansen' AND 'Pettersen';
   

SQL Alias

With SQL, an alias name can be given to a table or to a column. You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names. SQL Alias Syntax for Tables:

SELECT column_name(s) FROM table_name AS alias_name;

SQL Alias Syntax for Columns:

SELECT column_name AS alias_name FROM table_name;

SELECT po.OrderID, p.LastName, p.FirstName
FROM
Persons AS p, Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola';

SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

SQL JOIN

1. The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
2. Tables in a database are often related to each other with keys.
3. A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

Different SQL JOINs

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

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

SQL INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

SELECT column_name(s) FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name.

INNER JOIN is the same as JOIN.

SELECT
Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName;

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in “Persons” that do not have matches in “Orders”, those rows will NOT be listed.

SQL LEFT JOIN / LEFT OUTER JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName;

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

SQL RIGHT JOIN / RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName;

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

SQL FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName;

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in “Persons” that do not have matches in “Orders”, or if there are rows in “Orders” that do not have matches in “Persons”, those rows will be listed as well.

CARTESIAN or CROSS JOINS

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables.

• Cartesian product

  In mathematics, specifically set theory, the Cartesian product of two sets A and B, denoted A × B, is the set of all ordered pairs (a, b) where a is in A and b is in B.

  

  e.g. If we have two tables - CUSTOMERS and ORDERS

   +----+----------+-----+-----------+----------+
  | ID | NAME     | AGE | ADDRESS   | SALARY   |
  +----+----------+-----+-----------+----------+
  |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
  |  2 | Khilan   |  25 | Delhi     |  1500.00 |
  |  3 | kaushik  |  23 | Kota      |  2000.00 |
  |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
  |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
  |  6 | Komal    |  22 | MP        |  4500.00 |
  |  7 | Muffy    |  24 | Indore    | 10000.00 |
  +----+----------+-----+-----------+----------+
  
  +-----+---------------------+-------------+--------+
  |OID  | DATE                | CUSTOMER_ID | AMOUNT |
  +-----+---------------------+-------------+--------+
  | 102 | 2009-10-08 00:00:00 |           3 |   3000 |
  | 100 | 2009-10-08 00:00:00 |           3 |   1500 |
  | 101 | 2009-11-20 00:00:00 |           2 |   1560 |
  | 103 | 2008-05-20 00:00:00 |           4 |   2060 |
  +-----+---------------------+-------------+--------+
  

  Now, let us join these two tables using CARTESIAN JOIN as follows −

  SQL> SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS, ORDERS;
  

  This would produce the following result −

  +----+----------+--------+---------------------+
  | ID | NAME     | AMOUNT | DATE                |
  +----+----------+--------+---------------------+
  |  1 | Ramesh   |   3000 | 2009-10-08 00:00:00 |
  |  1 | Ramesh   |   1500 | 2009-10-08 00:00:00 |
  |  1 | Ramesh   |   1560 | 2009-11-20 00:00:00 |
  |  1 | Ramesh   |   2060 | 2008-05-20 00:00:00 |
  |  2 | Khilan   |   3000 | 2009-10-08 00:00:00 |
  |  2 | Khilan   |   1500 | 2009-10-08 00:00:00 |
  |  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
  |  2 | Khilan   |   2060 | 2008-05-20 00:00:00 |
  |  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
  |  3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
  |  3 | kaushik  |   1560 | 2009-11-20 00:00:00 |
  |  3 | kaushik  |   2060 | 2008-05-20 00:00:00 |
  |  4 | Chaitali |   3000 | 2009-10-08 00:00:00 |
  |  4 | Chaitali |   1500 | 2009-10-08 00:00:00 |
  |  4 | Chaitali |   1560 | 2009-11-20 00:00:00 |
  |  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
  |  5 | Hardik   |   3000 | 2009-10-08 00:00:00 |
  |  5 | Hardik   |   1500 | 2009-10-08 00:00:00 |
  |  5 | Hardik   |   1560 | 2009-11-20 00:00:00 |
  |  5 | Hardik   |   2060 | 2008-05-20 00:00:00 |
  |  6 | Komal    |   3000 | 2009-10-08 00:00:00 |
  |  6 | Komal    |   1500 | 2009-10-08 00:00:00 |
  |  6 | Komal    |   1560 | 2009-11-20 00:00:00 |
  |  6 | Komal    |   2060 | 2008-05-20 00:00:00 |
  |  7 | Muffy    |   3000 | 2009-10-08 00:00:00 |
  |  7 | Muffy    |   1500 | 2009-10-08 00:00:00 |
  |  7 | Muffy    |   1560 | 2009-11-20 00:00:00 |
  |  7 | Muffy    |   2060 | 2008-05-20 00:00:00 |
  +----+----------+--------+---------------------+
  

SQL UNION and UNIONALL Operators

1. The SQL UNION operator combines/concatenates two or more SELECT statements.
2. The UNION operator is used to combine/concatenate the result-set of two or more SELECT statements.
3. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2;

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2;

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA;

Difference between UNION and UNIONALL

UNION: only keeps unique records UNION ALL: keeps all records, including duplicates

SQL SELECT INTO Statement

1. The SQL SELECT INTO statement can be used to create backup copies of tables.
2. The SELECT INTO statement selects data from one table and inserts it into a different table.
3. The SELECT INTO statement is most often used to create backup copies of tables.
4. We can select all columns into the new table:

   SELECT *
   INTO new_table_name [IN externaldatabase]
   FROM old_tablename;
   

5. Or we can select only the columns we want into the new table:

   SELECT column_name(s)
   INTO new_table_name [IN externaldatabase]
   FROM old_tablename;
   SELECT * INTO Persons_Backup FROM Persons;
   

SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database. CREATE DATABASE database_name

SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

SQL CREATE INDEX Statement

1. The CREATE INDEX statement is used to create indexes in tables.
2. Indexes allow the database application to find data fast; without reading the whole table.

Indexes

An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name)

SQL DROP INDEX, DROP TABLE, and DROP DATABASE

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

The DROP Statement

The DROP INDEX statement is used to delete an index in a table.

DROP TABLE table_name

TRUNCATE TABLE Statement

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

TRUNCATE TABLE table_name

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name ALTER COLUMN column_name datatype;

SQL AUTO INCREMENT Field

1. Auto-increment allows a unique number to be generated when a new record is inserted into a table.
2. Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

SQL Date Functions

SQL Dates

1. The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.
2. As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.
3. Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.

MySQL Date Functions

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

SQL Server Date Functions

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

SQL NULL Values

1. IS NULL and IS NOT NULL
2. NULL values represent missing unknown data.
3. By default, a table column can hold NULL values.

SQL NULL Values

1. If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.
2. NULL values are treated differently from other values.
3. NULL is used as a placeholder for unknown or inapplicable values.

SQL NULL Functions

SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions

SQL Server / MS Access

SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

Oracle: Oracle does not have an ISNULL() function. However, we can use the NVL() function to achieve the same result:

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products