Constructs of SQL

Here is list of the key elements of SQL along with a brief description:
 Queries : Retrieves data against some criteria.
 Statements : Controls transactions, program flow, connections, sessions, or diagnostics.
 Clauses : Components of Queries and Statements.
 Expressions : Combination of symbols and operators and a key part of the SQL statements.
 Predicates : Specifies conditions.

SQL statements are grouped into following categories:

 Data manipulation : The Data Manipulation Language (DML) is the subset of SQL
which is used to add, update and delete data.
 Data definition : The Data Definition Language (DDL) is used to manage table and
index structure. CREATE, ALTER, RENAME, DROP and TRUNCATE statements
are to name a few data definition elements.
 Data control : The Data Control Language (DCL) is used to set permissions to users
and groups of users whether they can access and manipulate data.
 Transaction : A transaction contains a number of SQL statements. After the
transaction begins, all of the SQL statements are executed and at the end of the
transaction, permanent changes are made in the associated tables.
 Procedure : Using a stored procedure, a method is created which contains source
code for performing repetitive tasks.

Class Example

SQL data statements SELECT, INSERT, UPDATE,

DELETE

SQL connection statements CONNECT, DISCONNECT

SQL schema statements ALTER, CREATE, DROP

SQL control statements CALL, RETURN

SQL diagnostic statements GET DIAGNOSTICS

SQL session statements SET CONSTRAINT

SQL transaction statements COMMIT, ROLLBACK

Database & Table Manipulation

Command Description

CREATE DATABASE database_name Create a database

DROP DATABASE database_name Delete a database

CREATE TABLE "table_name" ("column_1" Create a table in a database.

"column_1_data_type", "column_2" "column_2_data_type", ... )

ALTER TABLE table_name ADD column_name Add columns in an existing

column_datatype table.

ALTER TABLE table_name DDROP column_name Delete columns in an

column_datatype existing table.

DROP TABLE table_name Delete a table.

Data Types

Data Type Description

CHARACTER(n) Character string, fixed length n.

CHARACTER VARYING(n) or Variable length character string, maximum length n.

VARCHAR(n)

BINARY(n) Fixed-length binary string, maximum length n.

BOOLEAN Stores truth values - either TRUE or FALSE.

BINARY VARYING(n) or Variable length binary string, maximum length n.

VARBINARY(n)

INTEGER(p) Integer numerical, precision p.

SMALLINT Integer numerical precision 5.

INTEGER Integer numerical, precision 10.

BIGINT Integer numerical, precision 19.

DECIMAL(p, s) Exact numerical, precision p, scale s.

NUMERIC(p, s) Exact numerical,

precision p, scale s.
(Same as DECIMAL ).

FLOAT(p) Approximate numerical, mantissa precision p.

REAL Approximate numerical

mantissa precision 7.

FLOAT Approximate numerical

mantissa precision 16.

DOUBLE PRECISION Approximate numerical

mantissa precision 16.

DATE Composed of a number of integer fields, representing an

TIME absolute point in time, depending on sub-type.
TIMESTAMP

INTERVAL Composed of a number of integer fields, representing a

period of time, depending on the type of interval.

COLLECTION (ARRAY, ARRAY(offered in SQL99) is a set-length and ordered the

MULTISET) collection of elements.
 XML Stores XML data. It can be used wherever a SQL data
type is allowed, such as a column of a table.

Insert, Update, Delete

Command Description

INSERT INTO table_name VALUES (value_1, value_2,....) Insert new rows into a table.
INSERT INTO table_name (column1, column2,...) VALUES
(value_1, value_2,....)

UPDATE table_name SET column_name_1 = new_value_1, Update one or several

column_name_2 = new_value_2 WHERE column_name = columns in rows.
some_value

DELETE FROM table_name WHERE column_name = Delete rows in a table.

some_value

Select

Command Description

SELECT column_name(s) FROM Select data from a table.

table_name

SELECT * FROM table_name Select all data from a table.

SELECT DISTINCT column_name(s) Select only distinct (different) data from a table.
FROM table_name

SELECT column_name(s) FROM Select only certain data from a table.

table_name WHERE column operator
value AND column operator value OR
column operator value AND (...
OR ...) ...
SELECT column_name(s) FROM The IN operator may be used if you know the exact
table_name WHERE column_name IN value you want to return for at least one of the
(value1, value2, ...) columns.

SELECT column_name(s) FROM Select data from a table with sort the rows.
table_name ORDER BY row_1, row_2
DESC, row_3 ASC, ...

SELECT column_1, ..., The GROUP BY clause is used with the SELECT
SUM(group_column_name) FROM statement to make a group of rows based on the
table_name GROUP BY values of a specific column or expression. The SQL
group_column_name AGGREGATE function can be used to get
summary information for every group and these are
applied to individual group.

SELECT column_name(s) INTO Select data from table(S) and insert it into another
new_table_name FROM table.
source_table_name WHERE query

SELECT column_name(s) IN Select data from table(S) and insert it in another

external_database_name FROM database.
source_table_name WHERE query

Index Manipulation

Command Description

CREATE INDEX index_name ON table_name Create a simple

(column_name_1, column_name_2, ...) index.

CREATE UNIQUE INDEX index_name ON table_name Create a unique

(column_name_1, column_name_2, ...) index.

DROP INDEX table_name.index_name Drop a index.

SQL Operators
Operators Description

SQL Arithmetic Arithmetic operators are addition(+), subtraction(-), multiplication(*) and

Operator division(/). The + and - operators can also be used in date arithmetic.

SQL Comparison A comparison (or relational) operator is a mathematical symbol which is

Operator used to compare two values.

SQL Assignment In SQL the assignment operator ( = ) assigns a value to a variable or of a

operator column or field of a table.

SQL Bitwise The bitwise operators are & ( Bitwise AND ), | ( Bitwise OR ) and ^
Operator ( Bitwise Exclusive OR or XOR ). The valid datatypes for bitwise
operators are BINARY, BIT, INT, SMALLINT, TINYINT, and
VARBINARY.

SQL Logical The Logical operators are those that are true or false. The logical
Operator operators are AND , OR, NOT, IN, BETWEEN, ANY, ALL, SOME,
EXISTS, and LIKE.

SQL Unary The SQL Unary operators perform such an operation which contain only
Operator one expression of any of the datatypes in the numeric datatype category.

Functions

SQL Description
functions

Aggregate This function can produce a single value for an entire group or table. Some
Function Aggregate functions are -
 SQL Count function
 SQL Sum function
 SQL Avg function
 SQL Max function
 SQL Min function

Arithmetic A mathematical function executes a mathematical operation usually based on

Function input values that are provided as arguments, and return a numeric value as the
result of the operation.
Some Arithmetic functions are -
 abs()
 ceil()
 floor()
 exp()
 ln()
 mod()
 power()
 sqrt()

Character A character or string function is a function which takes one or more characters
Function or numbers as parameters and returns a character value. Some Character
functions are -
 lower()
 upper()
 trim()
 translate()

Joins

Unions

Command Description

Statement_1 UNION Select all different values from SQL_Statement_1

SQL_Statement_2 and SQL_Statement_2

Statement_1 UNION ALL Select all values from SQL_Statement_1 and

Statement_2 SQL_Statement_2

View
 Command Description

CREATE VIEW view_name AS SELECT Create a virtual table based on the result-set
column_name(s) FROM table_name WHERE of a SELECT statement.
condition

5 Easy Ways to Handle Duplicates Using SQL INSERT INTO SELECT

The easiest way to handle duplicates is to remove unique constraints, right?

Wrong!

With unique constraints gone, it’s easy to make a mistake and insert the data twice or more.
We do not want that. And what if we have a user interface with a dropdown list for picking
the origin of the pasta dish? Will the duplicates make your users happy?

Therefore, removing the unique constraints is not one of the five ways to handle duplicates.
We have better options.

1. Using INSERT INTO SELECT DISTINCT - Use DISTINCT in your SELECT:

2. Using WHERE NOT IN

3. Using WHERE NOT EXISTS

4. Using IF NOT EXISTS

5. Using COUNT(*) = 0

SQL Server PIVOT

PIVOT operator to convert rows to columns.

SQL Server PIVOT operator rotates a table-valued expression. It turns the unique values in
one column into multiple columns in the output and performs aggregations on any remaining
column values.

You follow these steps to make a query a pivot table:

 First, select a base dataset for pivoting.
 Second, create a temporary result by using a derived table or common table
expression (CTE)
 Third, apply the PIVOT operator.

Example: Consider the following tables-

Aggregate functions in SQL
An aggregate function is a function where the values of multiple rows of a single column
are grouped together as input on certain criteria to form a single value of more significant
meaning. Aggregate functions allow us to easily produce summarized data from our
database.

The ISO standard defines five (5) aggregate functions namely;

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX
COUNT Function

The COUNT function returns the total number of values in the specified field. It
works on both numeric and non-numeric data types. All aggregate functions by default
exclude nulls values before working on the data.

COUNT (*) is a special implementation of the COUNT function that returns the count of all
the rows in a specified table. COUNT (*) also considers Nulls and duplicates.

Syntax: SELECT COUNT(*) FROM table_name WHERE condition;

When the * is used for COUNT(), all records ( rows ) are counted even if some
content NULL. But COUNT(column_name) does not COUNT a record if its field is NULL.
The COUNT statement counts those rows for the column which are not NULL. You can use
* or ALL or DISTINCT or some expression along with COUNT to count the number of rows
w.r.t. some condition or all of the rows, depending up on the arguments you are using along
with COUNT() function.

DISTINCT Keyword

. It is used with SELECT keyword to retrieve only distinct or unique data. Inside a
table, a column often contains many duplicate values and sometimes you only want to list the
different (distinct) values. In such scenarios, SQL SELECT DISTINCT statement is used.
This is achieved by grouping similar values together

SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are same.

Syntax: SELECT DISTINCT column_name FROM table_name

Syntax with WHERE condition: SELECT DISTINCT column_name FROM table_name

WHERE condition

MIN function

The MIN function returns the smallest value in the specified table field.

Syntax: select min(column_name) from table_name

MAX function

Just as the name suggests, the MAX function is the opposite of the MIN function. It returns
the largest value from the specified table field.

Syntax: select max(column_name) from table_name

SUM function

SUM function returns the sum of all the values in the specified column. SUM works on
numeric fields only. Null values are excluded from the result returned.

Syntax: select sum(column_name) from table_name

AVG function

AVG function returns the average of the values in a specified column. Just like the SUM
function, it works only on numeric data types.

Syntax: select avg(column_name) from table_name

What is the difference between WHERE clause and HAVING clause?

Both WHERE and HAVING clause are used to check the condition and have filters
on the resultant data. But both are used in different contest. The WHERE clause is used with
simple SELECT statement and to have subset of data from one or more tables. This clause
will add the conditions and filter the resultant data set. A simple SELECT statement without
WHERE clause will select all the data from the table/s. If we want to see only part or subset
of data from the table, we will include WHERE clause.

The HAVING clause, though used in the SELECT statement, it is actually used to compare
the aggregate values. i.e.; it can only compare the values of aggregate functions like SUM (),
MAX (), MIN (), AVG (), and COUNT (). It cannot compare any simple conditions like
WHERE clause does, while WHERE clause cannot compare aggregate values. Hence even
though both of them are used for comparison, they are comparing different kinds of values.

Why do we need to use the same columns that are selected in the SELECT list
in the GROUP BY Clause?

This is because, when we group the records based on some columns, and if we see the result
without those columns, then we will not understand what is the result. If we see the result
along with grouped columns, then we will get meaningful result and understand them too.

3. Why do we need to have columns that are not part of GROUP BY Clause as
part of aggregate function?

This is also same reason as above. If we have some columns in the SELECT list that are not
part of GROUP BY clause, then it will change the requirement of query itself. Still if we need
to include them in SELECT list but not in GROUP BY clause, then we have use some
aggregate function on them so that their value remains same but it will be displayed in the
SELECT list.
4. Will fixing the error by adding the columns to Group By clause, result in
correct output?

Not always. It always depends on the data and structure of the table.

5. In SQL, what’s the difference between the having clause and the group by
statement?

Group by statement is used to group each value of particular columns and then calculate
aggregated values. i.e.; when group by COLUMN_NAME is used, it first finds all the
DISTINCT values from the column of the table. Then it finds the aggregate values based on
this grouped column/s. If we need to compare the value of this aggregated value, we cannot
put it in WHERE clause. Instead we need to put HAVING clause to compare the aggregated
values.

When we perform GROUP BY in a query, it first selects all the columns that are being
selected from one or more tables by applying the conditions and filters in the WHERE clause.
Once it gets this result, grouping of records are done using GROUP BY statement. Now this
grouped record set maybe left without any aggregation or it may be aggregated to get sum,
average, count etc. Now if we need to compare these aggregated values we cannot use
WHERE clause. This is because WHERE clause has been already executed and after which
we have got these aggregated values. Hence we need some other clause to compare these
aggregated values. Hence we use HAVING clause to compare aggregated values like SUM,
AVG, COUNT, MIN and MAX.

Scalar functions
these functions are based on user input, this too returns single value.

1. LCASE() - Used to convert values of a string column to lowercase characters.

Syntax: select lcase(column_name) from table

In sql – select lower(column_name) from table

2. UCASE() - Used to convert values of a string column to uppercase characters

Syntax: in sql – select upper(column_name) from table

3. MID() - This function is used to extract substrings from columns having string data
type.

Syntax: select mid(column_name, start, length) from table

Insql – select substring(column_name,start,length) from table

4. LEN() - Used to retrieve the length of the input string.

 Syntax: in sql – select len(column_name) from table

5. ROUND() - This function is used to round off a numeric value to the nearest integer.

Syntax: select round(column_name,decimals) from table

6. NOW() - Used to return the current date and time. The date and time are returned in
the “YYYY-MM-DD HH-MM-SS” format.

In sql – getdate()

7. FORMAT() - This function formats the way a field must be displayed.

Syntax: FORMAT(InputValue, Format)

Ex: select format(123456,’##-##-##’) o/p = 12-34-56

What are scalar functions?

SQL Server scalar function takes one or more parameters and returns a single value.
The scalar functions help you simplify your code. Instead of including the formula in every
query, you can create a scalar function that encapsulates the formula and uses it in each
query.

Creating a scalar function

To create a scalar function, you use the CREATE FUNCTION statement as follows:

CREATE FUNCTION function_name (parameter_list)

RETURNS data_type AS
BEGIN
statements
RETURN value
END

Ex:

After creating the scalar function, you can find it under Programmability > Functions >
Scalar-valued Functions