Module Title: Advanced Structured Query Language

Unit One: Advanced SQL statements

1.1. DBMS fundamentals
A database-management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The primary
goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient.

Understanding the fundamentals of DBMS is crucial for anyone working with databases, whether as a database administrator, data analyst, or
software developer.

1. Data Organization: One of the primary functions of a DBMS is to organize data in a structured manner. It provides a logical
framework, known as a schema, which defines the structure and relationships of the data. The schema includes tables, which
consist of rows (records) and columns (attributes).
2. Data Retrieval: DBMS allows users to retrieve data from databases using queries. Queries are written in a language called
Structured Query Language (SQL), which is a standard language for interacting with relational databases.
3. Data Manipulation: DBMS enables users to manipulate data by performing various operations such as inserting, updating, and
deleting records. These operations ensure data consistency and integrity.
4. Data Security: DBMS provides mechanisms to ensure data security and access control. It allows administrators to define user roles
and privileges, restricting unauthorized access to sensitive data.
5. Data Integrity and Consistency: DBMS enforces data integrity rules to maintain the accuracy and consistency of data. It supports
constraints such as primary keys, foreign keys, and unique constraints to ensure data integrity.
6. Data Backup and Recovery: DBMS offers features for data backup and recovery to protect against data loss.
1.2. Database tools and equipment
The choice of tools and equipment should align with the specific information requirements of your organization or project. This
process involves considering various factors such as data volume, complexity, security needs, performance requirements, and
scalability. Security is another critical aspect to consider when selecting database tools and equipment.

1.3. Functions of where clause

 Retrieving data from a table
The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc.
It is used to extract only those records that fulfill a specified condition. We can retrieve the data from a single table or multiple tables
(after join operation) using the WHERE clause.

You can use SQL to retrieve the columns of a database table with the SELECT statement. You can retrieve all columns, a single
column, or several specific columns. It is then up to your programming language to display that data.

You can write a query to retrieve all the elements in a database table by using the SELECT statement and wildcard (*) indicator.

Select * from table_name;

Note: It is standard practice to add ";" at the end of your SQL query.

The most common query from a database is to collect or retrieve all the elements in a specific column of the database table.

Select column_name from table_name;

You can also retrieve data from several columns by separating the column names with a comma.

2
Select column_1, column_2, column_7 from table_name;

 WHERE clause with SELECT statement

If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved
based on a specific condition (or expression). Example: Assume we have created a table
named CUSTOMERS in SQL database using CREATE TABLE statement and inserted some
values. The table created is as shown below.

ID Name Age Address Salary

1 Tilahun 32 Gulele 2000
2 Kebede 25 Arada 1500
3 Chemdesa 23 Lemikura 2000
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000
In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the
SALARY is greater than 2000
 Select ID, Name, Salary from customers where salary > 2000;

ID Name Salary
4 Fulea 6500
5 Kemal 8500
6 Momona 4500
7 Tibletse 10000

3
 WHERE clause with UPDATE statement

The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE
statement, we can update particular records. If the WHERE clause is not used, the UPDATE statement would affect all the records of
a table.

Example: by using the previous customers table, we are incrementing the salary of the customer named Ramesh by 10000 by using
the WHERE clause along with the UPDATE statement

 Update customer set salary= salary + 1000 where Name= ‘Tilahun’;

ID Name Age Address Salary
1 Tilahun 32 Gulele 3000
 Comparison operators in the WHERE clause
SQL Comparison Operators test whether two given expressions are the same or not. These operators are used in SQL conditional
statements while comparing one expression with another and they return a Boolean value which can be either TRUE or FALSE. The
result of an SQL comparison operation can be UNKNOWN when one or another operand has its value as NULL.

Here is a list of all the comparison operators available in SQL.

Operator Description
= Equal to
!= Not equal
<> Not equal
> Greater than

4
 < Less than
>= Greater than or equal to
<= Less than or equal to
!< Not less than
!> Not greater than
Example: by using the previous customers table, we can write a query with a comparison operator
 Select * from customers where age != 25;

ID Name Age Address Salary

1 Tilahun 32 Gulele 2000
3 Chemdesa 23 Lemikura 2000
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000

 Select * from customers where age >= 25;

ID Name Age Address Salary
1 Tilahun 32 Gulele 2000
2 Kebede 25 Arada 1500
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
 CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables. They are used to retrieve data from the database more quickly than
otherwise. 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, only
create indexes on columns that will be frequently searched against.

5
  CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:

 CREATE INDEX index_name ON table_name (column1, column2, ...);

 CREATE INDEX index_name ON table_name (column1, column2, ...) ASC or DES;

1.4. Functions of order by clause

The SQL ORDER BY clause is used to sort the data in either ascending or descending order, based on one or more columns. ORDER
BY is used with the SQL SELECT statement and is usually specified after the WHERE, HAVING, and GROUP BY clauses. To sort
the data in ascending order, we use the keyword ASC and descending order, we use the keyword DESC.

In addition to sorting records in ascending order or descending order, the ORDER BY clause can also sort the data in a database table
in a preferred order. Example: by using the previous product table and sort the products from highest to lowest price

 SELECT * FROM Products ORDER BY Price DESC;

Sort the products from lowest to highest price

 SELECT * FROM Products ORDER BY Price ASC;

1.5. Boolean operators

Boolean operators in SQL are logical operators used to combine or manipulate conditions in a query.

Operator Description
ALL TRUE if all of the sub query values meet the condition
AND TRUE if all the condition separated by AND is true
ANY TRUE if any of the sub query values meet the condition

6
BETWEEN TRUE if the operand is within the range of comparisons
EXISTS TRUE if the sub query returns one or more records
IN TRUE if the operand is equal to one of a list of expressions
LIKE TRUE if the operand matches a pattern
NOT Displays a record if the condition(s) is NOT TRUE
OR TRUE if any of the conditions separated by OR is true
Example: Assume we have created a table named CUSTOMERS in SQL database using CREATE TABLE statement and inserted
some values. The table created is as shown below.

ID Name Age Address Salary

1 Tilahun 32 Gulele 2000
2 Kebede 25 Arada 1500
3 Chemdesa 23 Lemikura 2000
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000
 SQL ALL Operator

The ALL operator:

 Returns a Boolean value as a result
 Returns TRUE if ALL of the sub query values meet the condition

 It is used with SELECT, WHERE and HAVING statements

ALL means that the condition will be true only if the operation is true for all values in the range.

ALL operator syntax : SELECT ALL column_name(s) FROM table_name WHERE condition;
7
 WHERE clause with AND, OR operators
The AND operator will make sure only those rows are filtered that satisfy all the conditions and the OR operator will filter records
that satisfy any one of the specified conditions.

Following is the syntax for using the AND and OR operators in a WHERE clause

 WHERE (condition1 OR condition2) AND condition3;

Example: by using the previous customers table, we are retrieving all rows from the CUSTOMERS table based on some conditions.
The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator.

 Select * from customers where (age = 25 OR salary < 4500) AND (name = 'Chemdesa' OR name = 'Momona');
ID Name Age Address Salary

3 Chemdesa 23 Lemikura 2000

 SQL ANY Operator

The ANY operator:
 Returns a Boolean value as a result
 Returns TRUE if ANY of the sub query values meet the condition

ANY means that the condition will be true if the operation is true for any of the values in the range.

ANY operator syntax

 SELECT column_name(s) FROM table_name WHERE column_name operator ANY

(SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

8
Example: in the following example if we want to retrieve name of the customers whose salary is 2000, we can write this query
 SELECT Name FROM customers WHERE ID = ANY (SELECT ID
FROM customers WHERE salary=2000;
Name
Tilahun
Chemdesa
 SQL BETWEEN Operator
The BETWEEN operator is a logical operator in SQL, that is used to retrieve the data within a specified range. The retrieved values
can be integers, characters, or dates. You can use the BETWEEN operator to replace a combination of "greater than equal AND less
than equal" conditions.

 SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Example: by using the previous customers table, we can write a query that will display all customers whose age is between 20 and 30.

 Select * from customers where age between 20 and 30;

ID Name Age Address Salary

2 Kebede 25 Arada 1500
3 Chemdesa 23 Lemikura 2000
4 Fulea 25 Kality 6500
5 Kemal 27 Yeka 8500
6 Momona 22 Lafto 4500
7 Tibletse 24 Bole 10000
Example: by using the previous customers table, we can write a query that will display all customers whose name starts between the a
and g.
 Select * from customers where name between ‘a’ and ‘g’;
ID Name Age Address Salary
3 Chemdesa 23 Lemikura 2000
4 Fulea 25 Kality 6500
9
  NOT BETWEEN Operator
The NOT BETWEEN operator in SQL works in exactly opposite to the BETWEEN operator. This is used to retrieve the data which
is not present in the specified range.

Example: Select * from customers where age not between 20 and 30;

ID Name Age Address Salary

1 Tilahun 32 Gulele 2000
 SQL EXISTS Operator
 The EXISTS operator is used to test for the existence of any record in a sub query. It returns TRUE if the sub query returns
one or more records.
EXISTS operator Syntax

 SELECT column_name(s) FROM table_name WHERE EXISTS

(SELECT column_name FROM table_name WHERE condition);
 WHERE clause with IN operator

Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN with the
SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax
for it. Example: by using the previous customers table, suppose you want to display records with NAME
values Khilan, Hardik and Muffy from the CUSTOMERS table, you can use the following query

 Select * from customers where name in (‘Tilahun', 'Chemdesa', 'momona');

ID Name Age Address Salary
1 Tilahun 32 Gulele 3000
3 Chemdesa 23 Lemikura 2000
6 Momona 22 Lafto 4500
10
  WHERE clause with NOT IN operator

The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator.
 If you use WHERE with the IN operator, the DML statement will act on the list of values (of a column) specified
 Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are
not there in the specified list.

Example: by using the previous customers table, we are displaying the records from CUSTOMERS table, where AGE is NOT equal
to 25, 23 and 22.
 Select * from customers where age NOT IN (25, 23, 22);
ID Name Age Address Salary
1 Tilahun 32 Gulele 2000
5 Kemal 27 Yeka 8500
7 Tibletse 24 Bole 10000
 SQL LIKE Operator
 The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
 There are two wildcards often used in conjunction with the LIKE operator:
 The percent sign % represents zero, one, or multiple characters
 The underscore sign _ represents one, single character
LIKE operator syntax

 SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;
Example: by using the previous customers table, we can write a query which would display all the records where the name starts with
K and is at least 3 characters in length

 Select * from customers where name LIKE 'K__%';

11
ID Name Age Address Salary
2 Kebede 25 Arada 1500
5 Kemal 27 Yeka 8500
 SQL NOT Operator
 The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
NOT operator syntax

 SELECT column1, column2, ...FROM table_name WHERE NOT condition;

1.6. Elimination of duplicated and null values

 Duplicated values
Duplicates can be a big problem in SQL databases as they can slow down query performance and waste valuable storage space. You
can use the DISTINCT identifier to eliminate duplicate records from the result set.

Consider the following facts when using DISTINCT identifier in a SELECT statement:
 In a SELECT statement, include DISTINCT keyword after the SELECT clause.
 Multiple columns can be specified after DISTINCT keyword. In this case, the result set contains distinct combination of data
from these columns.
DISTINCT keyword syntax

SELECT DISTINCT column1, column2, ... FROM table_name;

 Null Value
A null value indicates no value. It means that the column value is absent in a row. A null value is not the same as a blank space or a
zero value. A zero value is an integer and a blank space is a character while a null value is the one that has been left blank.

To exclude the null values from a table we need to use IS NOT NULL operator with the WHERE clause. IS NOT NULL Operator is
used to test for non-empty values.
12
IS NOT NULL operator syntax:

 SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

1.7. Functions of join operator

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

The INNER JOIN keyword selects records that have matching values in both tables. It is the most common type of join. Inner
joins combine records from two tables whenever there are matching values in a field common to both tables. This join is based on a
logical relationship (or a common field) between the tables and is used to retrieve data that appears in both tables.

INNER JOIN operator syntax

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

 SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The
result is 0 records from the right side, if there is no match.

LEFT JOIN keyword syntax

 SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

Figure 1.1. Left Join

13
 SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1).
The result is 0 records from the left side, if there is no match.

RIGHT JOIN Syntax

 SELECT column_name(s) FROM table1 RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Figure 1.2. Right Join

 SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
Tip: FULL OUTER JOIN and FULL JOIN are the same.

FULL OUTER JOIN Syntax

 SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name
WHERE condition;

Figure 1.3. Full outer Join

14
1.8. Functions of union operator
The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement
within UNION must have the same number of columns. The columns must also have similar data types. The columns in
every SELECT statement must also be in the same order

UNION operator syntax

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION operator with where clause syntax
SELECT column_name(s) FROM table1 WHERE condition1
UNION SELECT column_name(s) FROM table2 WHERE condition2

1.9. Data control language

DCL stands for Data Control Language in Structured Query Language (SQL). The privileges (Right to access the data) are required
for performing all the database operations like creating tables, views, or sequences. DCL command is a statement that is used to
perform the work related to the rights, permissions, and other control of the database system.
Need Of DCL commands
 Unauthorized access to the data should be prevented in order to achieve security in our database
GRANT
This command is used to grant permission to the user to perform a particular operation on a particular object. If you are a database
administrator and you want to restrict user accessibility such as one who only views the data or may only update the data. You can
give the privilege permission to the users according to your wish.
Syntax: RANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

15
REVOKE
This command is used to take permission/access back from the user. If you want to return permission from the database that you have
granted to the users at that time you need to run REVOKE command.

Syntax: REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

1.10. Transaction control language

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific job and ends when all the tasks in
the group successfully completed. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two
results: success or failure. BEGIN:

Incomplete steps result in the failure of the transaction. A database transaction, by definition, must be atomic, consistent, isolated, and
durable. These are popularly known as ACID properties. These properties can ensure the concurrent execution of multiple
transactions without conflict.

Properties of Transaction
 Atomicity: The outcome of a transaction can either be completely successful or completely unsuccessful. The whole
transaction must be rolled back if one part of it fails.
 Consistency: Transactions maintain integrity restrictions by moving the database from one valid state to another.
 Isolation: Concurrent transactions are isolated from one another, assuring the accuracy of the data.
 Durability: Once a transaction is committed, its modifications remain in effect even in the event of a system failure.
1. COMMIT: This command is used to save the data permanently. Whenever we perform any of the DML command like -
INSERT, DELETE or UPDATE, these can be rollback if the data is not stored permanently. So, in order to be at the safer side
COMMIT command is used.
 Syntax: commit;

16
2. ROLLBACK: This command is used to get the data or restore the data to the last savepoint or last committed state. If due to some
reasons the data inserted, deleted or updated is not correct, you can roll back the data to a particular savepoint or if savepoint is not
done, then to the last committed state.
 Syntax: rollback;
3. SAVEPOINT
This command is used to save the data at a particular point temporarily, so that whenever needed can be rollback to that
particular point.
 Syntax: Savepoint A;
Example: Consider the following student table

 UPDATE STUDENT SET NAME = ‘Sherlock’ WHERE NAME = ‘Jolly’;

COMMIT;
ROLLBACK;
By using the above command, you can update the record and save it permanently by using COMMIT command. Now after
COMMIT:

17
If commit was not performed then the changes made by the update command can be rollback.
Now if no COMMIT is performed.

 UPDATE STUDENT SET NAME = ‘Sherlock’ WHERE STUDENT_NAME = ‘Jolly’;

After update command the table will be:

Now if ROLLBACK is performed on the above table:

rollback;

After Rollback:

If on the above table savepoint is performed:

18
  INSERT into STUDENT VALUES ('Jack', 95);
Commit;
 UPDATE NAME SET NAME= ‘Rossie’ WHERE marks= 70;
SAVEPOINT A;
 INSERT INTO STUDENT VALUES (‘Zack’, 76);
SAVEPOINT B;
 INSERT INTO STUDENT VALUES (‘Bruno’, 85);
SAVEPOINT C;
 SELECT * FROM STUDENT;

Now if we Rollback to SAVEPOINT B:

Rollback to B;
The resulting Table will be-

19
Now if we Rollback to SAVEPOINT A:

Rollback to A;

The resulting Table will be-

Unit Two: SQL statements with functions

2.1. Arithmetic operations

Arithmetic operators can perform arithmetical operations on numeric operands involved. Arithmetic operators are addition (+),
subtraction (-), multiplication (*) and division (/).

Operator Meaning
+(Add) Addition
-(Subtract) Subtraction
*(Multiply) Multiplication
/(Divide) Division
%(Modulo) Returns the integer reminder of a division
Arithmetic operations syntax:

 SELECT column_name arithmetic operator FROM [table_name] WHERE [expression];

20
 2.2. String functions and operators
The following table shows some string functions. All string functions works with the select statement.

Function Description
ASCII Returns the ASCII for the specified character
CHAR Returns the character based on ASCII
CHARINDEX Returns the position of a substring in a string
DATALENGTH Returns the number of bytes used to represent an expression
DIFFERENCE Compare two SOUNDEX values and returns integer value
LEFT Extract a number of characters by starting from the left
UPPER Convert a string in to upper case
LTRIM Remove leading spaces from a string
SUBSTRING Extract some characters from a string
REPLICATE Replicates a string a specified number of times
 The ASCII () function returns the ASCII value for the specific character.
ASCII syntax:
 ASCII (character)
Character parameter returns the ASCII value. If more than one character is entered, it will only return the value for the first character

 The CHAR () function returns the character based on the ASCII code.
CHAR syntax

 CHAR (code)
Code parameter returns the ASCII number code for the character

21
 The CHARINDEX () function searches for a substring in a string, and returns the position.
If the substring is not found, this function returns 0.

Note: This function performs a case-insensitive search.

CHARINDEX syntax

 CHARINDEX (substring, string, start)

Substring parameter is the one to search for
String parameter is the one to be searched
Start parameter is optional where the search will start
 The DATALENGTH () function returns the number of bytes used to represent an expression.
Note: The DATALENGTH () function counts both leading and trailing spaces when calculating the length of the expression.

DATALENGTH syntax
 DATALENGTH (expression)
Expression parameter is the one that return data type length

 The DIFFERENCE () function compares two SOUNDEX values, and returns an integer. The integer value indicates the match
for the two SOUNDEX values, from 0 to 4.
0 indicates weak or no similarity between the SOUNDEX values. 4 indicate strong similarity or identically SOUNDEX values.

DIFFERENCE syntax
 DIFFERENCE (expression, expression)
The two expressions are to be compared. Can be contrast, variable or column

 The LEFT () function extracts a number of characters from a string (starting from left).
22
LEFT syntax

 LEFT (string, number_of_chars)

String operator is the one to extract from

Number_of_chars operator, it is the number of characters to extract If the number exceeds the number of characters in string, it
returns string

 The UPPER () function converts a string to upper-case.

UPPER syntax

 UPPER (text)
Text parameter is the string to be converted

 The LTRIM () function removes leading spaces from a string.

Note: Also look at the RTRIM () function.

LTRIM syntax

 LTRIM (string)
String parameter is the one to remove leading spaces from.

 The SUBSTRING () function extracts some characters from a string.

SUBSTRING syntax

 SUBSTRING (string, start, length)

String parameter is the one the string to be extracted from

Start parameter is the first position in string is 1

23
Length parameter is number of characters to extract. Must be a positive number

 The REPLICATE () function repeats a string a specified number of times.

REPLICATE syntax

 REPLICATE (string, integer)

String parameter is the string to repeated

Integer parameter is the number of times to repeat the string

2.3. Mathematical functions

The following table shows some mathematical functions. All string functions works with the SELECT statement.

Function Description
COUNT Returns the number of records returned by a select query
AVG Returns the average value of an expression
DEGREES Converts a value in radians to degrees
MAX Returns the maximum value in a set of values
SQUARE Returns the square of a number
SUM Calculates the sum of a set of values
POWER Calculates the sum of a set of values
TAN Returns the tangent of a number
COS Returns cosine of a number
CEILING Returns the smallest integer value that is >= a number
 The COUNT () function returns the number of records returned by a select query.
Note: NULL values are not counted.
24
COUNT () Syntax

 COUNT (expression)
Expression parameter is a field or a string value

 The AVG () function returns the average value of an expression.

Note: NULL values are ignored.

AVG () syntax

 AVG (expression), the Expression parameter is a numeric value

 The DEGREES () function converts a value in radians to degrees.
DEGREES () syntax

 DEGREES (number)
Number parameter is a numeric value

 The MAX () function returns the maximum value in a set of values.

MAX () syntax

 MAX (expression)
Expression parameter is a numeric value

 The SQUARE () function returns the square of a number.

SQUARE () syntax

25
  SQUARE (number)
Number parameter is a positive number to calculate the square

 The SUM () function calculates the sum of a set of values.

Note: NULL values are ignored.

SUM () syntax

 SUM (expression)
Expression parameter is a field or a formula

 The POWER () function returns the value of a number raised to the power of another number.
POWER () syntax

 POWER (a, b)
A parameter is a number (the base) and B parameter is a number (the exponent)

 The TAN () function returns the tangent of a number.

TAN () syntax

 TAN (number)
Number parameter is a numeric value

 The COS () function returns the cosine of a number.

COS () syntax

 COS (number)
Number parameter is a numeric value

26
 The CEILING () function returns the smallest integer value that is larger than or equal to a number.
CEILING () syntax

 CEILING (number)
Number parameter is a numeric value

2.4. Date functions

The following table shows some date functions

Function Description

CURRENT_TIMESTAMP Returns the current date and time

DATEADD Adds a time/date interval to a date and then returns the date

DATEDIFF Returns the difference between two dates

DATEPART Returns a single part of a date/time

CONVERT Displays date/time data in different formats

GETDATE Returns the current database system date and time

 The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format.
CURRENT_TIMESTAMP syntax
 CURRENT_TIMESTAMP
 The DATEADD () function adds or subtracts a time/date interval to a date and then returns the date.
DATEADD () syntax
 DATEADD (datepart, number, date)
Interval parameter is to add time/date interval. Can be one of the following values:
 yyyy, yy = Year

27
  qq, q = Quarter
 mm, m = month
 dy, y = Day of the year
 day, dd, d = Day
 ww, wk = Week
 dw, w = Weekday
 hh = hour
 mi, n = Minute
 ss, s = Second
 ms = Millisecond
 Number parameter is the number of intervals to add to date. Can be positive (to get dates in the future) or negative (to get
dates in the past)
 Date parameter is the date that will be modified.
 The DATEDIFF () function returns the difference between two dates.
DATEDIFF () syntax
 DATEDIFF (date_part, start_date, end_date)
Interval parameter is the part to be returned
 yyyy, yy = Year
 qq, q = Quarter
 mm, m = month
 dy, y = Day of the year
 dd, d = Day
 ww, wk = Week
 dw, w = Weekday
28
  hh = hour
 mi, n = Minute
 ss, s = Second
 ms = Millisecond
 Start date and end date parameters are the two dates to calculate the difference between.
 The DATEPART () function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.
DATEPART () syntax
 DATEPART (datepart,date)
Where date is a valid date expression and datepart can be one of the following:
Interval parameter is the part to be returned
 yyyy, yy = Year
 qq, q = Quarter
 mm, m = month
 dy, y = Day of the year
 dd, d = Day
 ww, wk = Week
 dw, w = Weekday
 hh = hour
 mi, n = Minute
 ss, s = Second
 ms = Millisecond
 The CONVERT () function is a general function that converts an expression of one data type to another.
The CONVERT () function can be used to display date/time data in different formats.

29
CONVERT () syntax

 CONVERT (data_type(length),expression,style)
 data_type(length) Specifies the target data type (with an optional length)
 expression Specifies the value to be converted
 style Specifies the output format for the date/time
 The GETDATE () function returns the current date and time from the SQL Server.
GETDATE () syntax

 GETDATE ()

Unit Three: SQL statements with aggregation and filtering 3.1. Function of group
by statement
The GROUP BY statement groups rows that have the same values into summary rows. It is often used with aggregate functions
(COUNT (), MAX (), MIN (), SUM (), AVG ()) to group the result-set by one or more columns.

The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e., if a particular
column has the same values in different rows, then it will arrange these rows in a group.

Features
 GROUP BY clause is used with the SELECT statement.
 In the query, the GROUP BY clause is placed after the WHERE clause.
 In the query, the GROUP BY clause is placed before the ORDER BY clause if used.
 In the query, the Group BY clause is placed before the Having clause.
 Place condition in the having clause.
GROUP BY Syntax
30
  SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Example: let’s say we have a customers table with the following data
OrderID CustomerID EmployeeID OrderDate Country
10248 90 5 1996-07-04 Adama
10249 81 6 1996-07-05 Welkite
10250 34 4 1996-07-08 Adama
If we want the number of customers in each country, sorted high to low: we can write the following query

 SELECT COUNT (CustomerID), Country FROM Customers GROUP BY Country

ORDER BY COUNT (CustomerID) DESC;
I. GROUP BY With JOIN
Below there is a customers table with the following data

OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90 5 1996-07-04 3
10249 81 6 1996-07-05 1
10250 34 4 1996-07-08 2
And here there is a shippers table with the following data

ShipperID ShipperName
1 Speedy Express
2 United Package

31
3 Federal Shipping
If we want to retrieve the number of orders sent by each shipper, we can write the following SQL statement
 SELECT Shippers.ShipperName, COUNT (Orders.OrderID) AS NumberOfOrders from Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;Sorting aggregated data
II. GROUP BY With ORDER BY CLAUSE
You can sort aggregate data using the ORDER BY clause in combination with the aggregate functions. Sorting aggregate data in SQL
Server involves using aggregate functions to calculate summary values and then sorting the result set based on those calculations

3.2. Function of having clause

The HAVING clause was introduced in SQL to allow the filtering of query results based on aggregate functions and groupings, which
cannot be achieved using the WHERE clause that is used to filter individual rows.

In simpler terms MYSQL, the HAVING clause is used to apply a filter on the result of GROUP BY based on the specified condition.
The conditions are Boolean type i.e. use of logical operators (AND, OR). This clause was included in SQL as the WHERE keyword
failed when we use it with aggregate expressions. Having is a very generally used clause in SQL. Similar to WHERE it helps to apply
conditions, but HAVING works with groups. If you wish to filter a group, the HAVING clause comes into action.
Some important points:
 Having clause is used to filter data according to the conditions provided.
 Having a clause is generally used in reports of large data.
 Having clause is only used with the SELECT clause.
 The expression in the syntax can only have constants.
 In the query, ORDER BY is to be placed after the HAVING clause, if any.
 HAVING Clause is implemented in column operation.
 Having clause is generally used after GROUPBY.
The main difference between where and having clause
32
 Where Clause in SQL Having Clause in SQL

Applied before GROUP BY clause Used after GROUP BY clause

Used with single row operations such as Applicable with multiple row functions such
Upper, Lower and so on as Sum, count and so on

HAVING clause syntax

SELECT col_1, function_name(col_2) FROM tablename WHERE condition

GROUP BY column1, column2 HAVING Condition ORDER BY column1, column2

Example: Below there is a customer table

CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Ana Trujillo Avda. de la México D.F. 05021 Mexico
Emparedados y Constitución
helados 2222
3 Antonio Moreno Antonio Mataderos 2312 México D.F. 05023 Mexico
Taquería Moreno
4 Around the Horn Thomas Hardy 120 Hanover London WA1 1DP UK
Sq.
5 Berglunds Christina Berguvsvägen 8 Luleå S-958 22 Sweden
snabbköp Berglund
If we want to lists the number of customers in each country. And only include countries with more than 5 customers: the SQL
statement will be

 SELECT COUNT (CustomerID), Country FROM Customers GROUP BY Country

HAVING COUNT (CustomerID) > 5;
33
If we want to lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):
the SQL statement will be

 SELECT COUNT (CustomerID), Country FROM Customers GROUP BY Country

HAVING COUNT (CustomerID) > 5 ORDER BY COUNT (CustomerID) DESC;

3.4. Backup database

The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database.

Syntax: BACKUP DATABASE databasename TO DISK = 'filepath';

 Backup with differential

A differential backup reduces the backup time (since only the changes are backed up). The following SQL statement creates a
differential back up of the database "testDB":

Example: BACKUP DATABASE testDB TO DISK = 'D:\backups\testDB.bak' WITH DIFFERENTIAL;

34