Chapter Five

Introduction
to
SQL(Standard Query Language)

1 Database Management Systems

 SQL(Standard Query Language)
 SQL (Structured Query Language) is a database computer language designed for managing data
in relational database management systems (RDBMS)
 SQL is an ANSI (American National Standards Institute) standard

 SQL is a standard language for accessing and manipulating databases.

 SQL is a nonprocedural language , it only require a user to specify what data to retrieve or modify
without specifying how to get those data
 SQL is a widely used database language, providing means of data manipulation (store, retrieve,
update, delete) and database creation
 T-SQL stands for Transact Structure Query Language which is a Microsoft product and is an
extension of SQL Language used in SQL Server .

2 Database Management Systems

  SQL lets database users
• Create new databases
• Create new tables in a database
• Execute queries against a database
• Insert records into a database
• Retrieve data from a database
• Update records in a database
• Delete records from a database
• Create stored procedures in a database
• Create views in a database
• Can set permissions on tables, procedures, and views

3 Database Management Systems

  SQL key words
 The SQL keywords are words that are reserved words that are not used as a user defined data. The
most commonly used SQL key words according to ANSI/ISO SQL keywords are as follows:
ADA DEC GRANT NUMERIC
ALL DECIMAL GROUP OF
AND DECLARE HAVING ON
ANY DEFUALT IN OPEN
AS DELETE INDICATOR OPTION
ASC DESC INSERT OPEN
AUTHORIZATION DISTNICT INT OPTION
AVG DOUBLE INTEGER OR
BEGIN END INTO ORDER
BETWEEN ESCAPE IS PRIMARY
BY EXEC KEY REAL
C EXISTS LANGAUGE SELECT
CHAR FETCH LIKE SET
CHARACTER FLOAT MAX SOME
CHECK FOR MIN SUM
CLOSE FOREIGN MODULE TABLE
COBOL FORTRAN NOT TO UNION
COMMIT FOUND NULL UPDATE
CONTINUE FROM USER
COUNT GO VIEW
CREATE GOTO WHERE
CURRENT WITH
CURSOR WORK

4 Database Management Systems

 Introduction to SQL Statement/ commands
 The SQL languages consist of many statements

 Each statement requests a specific action from the DBMS, such as creating a new table,
retrieving data, or inserting new data into the database.
 Categories of SQL

• DDL (Data Definition Language): defines the structure of the database

• DCL (Data Control Language): which defines the privileges granted to database users.

• DML (Data Manipulation Language): which retrieves or modifies data

 DDL and DCL statements are commonly used by a database designer and database
administrator for establishing the database structures.

5 Database Management Systems

 DDL (Data Definition Language):
 defines the structure of the database
 manages table and index structure.
• DDL permits database tables to be created , modified or deleted.
• It also defines indexes (keys), specify links between tables, and impose constraints between
tables.
 The most basic items of DDL are the CREATE, ALTER, RENAME and DROP statements:
Most important DDL statements are composed of the following commands
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE – creates/add a new table to the database
Creates a table with the column names the user provides. The user also needs to specify the data
type for each column.
 ALTER TABLE - modifies the structure of an existing table
 Adds or removes a column from a table.
 Used in connection with ADD, MODIFY and DROP.

6 Database Management Systems

  DROP TABLE - deletes a table irretrievably
 Deletes all rows & removes the table from the database.

 CREATE INDEX - creates an index (search key)

 DROP INDEX - deletes an index

 CREATE VIEW Adds a new view to the database

 DROP VIEW Removes view from the database

 CREATE INDEX- Builds an index for a column

 DROP INDEX- Remove an index for a column

 CREATE SYNOMY- Define an alias for a table name

 DROP SYNOMY - Remove an alias for a table name

 COMMENT Defines a remarks for a table or column

 LABEL Defines a label for a table or column

7 Database Management Systems

  In short, using DDL statements, database user can:

• Define and create a new table, using CREATE

• Remove a table that’s no longer needed, using DROP

• Changes the definition of an existing table, using ALTER

• Define a virtual table(or view) of a data

• Build an index to make table access faster

• Control the physical storage of data by the DBMS

8 Database Management Systems

 DCL (Data Control Language):

 defines the privileges granted to database users.

Most important DCL statements are composed of the following commands

 GRANT: to allow specified users to perform specified tasks/Grant user access privileges

 REVOKE: to cancel previously granted or denied permissions/Removes user access privileges

 DENAY: restricts users not to perform tasks

 Data base administrators Establish security controls for a database

9 Database Management Systems

 DML (Data Manipulation Language):
 which retrieves or modifies data

 Most important DML statements are composed of the following commands

 SELECT - extracts data from a database

 - retrieves rows from a table.
 Specifies which columns to include in the result set.

 UPDATE - updates data in a database

 Modifies existing rows in a table.

 DELETE - deletes data from a database

 Removes a specified row or a set of rows from a table.

 INSERT INTO/ Insert - inserts new data into a database

 Adds rows to a table.

Database Management Systems

10
  The acronym CRUD refers to all of the major functions that need to be implemented in a
relational database application to consider it complete.
 Each letter in the acronym can be mapped to a standard SQL statement: database

11 Database Management Systems

 Working with SQL Statements
SQL statement structure
 Every SQL statement begins with a keyword(a verb) that describes what the
statement does.
Example CREATE, INSERT, and DELETE
 The statement continues with one or more clauses.

• A clause may specify the data to be acted upon by the statement, or provide
more detail about what the statement is supposed to do.
• Every clause also begins with a keyword, such as WHERE, FROM, INTO,
and HAVING. Some clauses are optional; others are required.

12 Database Management Systems

  Create Database Statement

Syntax
CREATE DATABASE database_name
 CREATE TABLE Statement

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

13 Database Management Systems

  Constraints( PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, DEFAULT ,

IDENTITY) can be specified when a table is created with the CREATE TABLE statement or
after the table is created with the ALTER TABLE statement.
Example
CREATE TABLE CUSTOMER
(
CustomerId int PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE CHECK(CustomerNumber>0) ,
CustName varchar(25) NOT NULL,
CustFName varchar(25) NOT NULL,
CustGFName varchar(25) NOT NULL,
Country varchar(20) DEFAULT ‘Ethiopia‘,

14 Database Management Systems

 CREATE TABLE CUSTORDER
(
OrderId int IDENTITY (1,1) PRIMARY KEY,
OrderDescription varchar(500),
CustomerId int NOTNULL FOREIGN KEY REFERENCES CUSTOMER
(CustomerId),
)

15 Database Management Systems

  ALTER TABLE
• ALTER TABLE - modifies a table
• The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
• The ALTER TABLE statement is also used to add and drop various constraints on an existing
table
• This command is used in connection with ADD, MODIFY and DROP.
 ALTER TABLE - ADD Column
syntax:
ALTER TABLE table_name
ADD column_name datatype;
Example
ALTER TABLE Customer
ADD Email varchar(50);

16 Database Management Systems

  ALTER TABLE - DROP COLUMN

Syntax
ALTER TABLE table_name
DROP COLUMN column_name;
Example
ALTER TABLE Customer
DROP COLUMN Email;
 ALTER TABLE - ALTER/MODIFY COLUMN

To change the data type of a column in a table

Syntax
ALTER TABLE table_name
ALTER COLUMN column_name datatype;

17 Database Management Systems

  DROP

• A DROP statement in SQL removes a component from a relational database management

system (RDBMS).
• DROP is used to delete a an existing database, table, index, or view.
Syntax:
DROP object object_name
 DROP TABLE -Deletes all rows and removes the table definition from the database.
Syntax
DROP TABLE table_name;
table_name: Name of the table to be deleted.
 Example

 DROP TABLE CUSTOMER;

18 Database Management Systems

  Create an Index
Syntax
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column_name [ASC | DESC], …..)
 Unique: if unique, no 2 rows can have the same value for the index column(s)
 Clustered/non-clustered: clustering or not
 ASC/DESC: ascending order or descending order of search keys in the index. For a clustered
index, this will affect the order of the rows in the data file.
 Drop an index
Syntax
DROP INDEX table.index_name

19 Database Management Systems

  To create a Clustered Index other than the primary key

Example For a table name sample1( sid , sname), When the table is created with sid a s a primary Key ,
primary key constraint and Clustered index are created with name PK_sample1.
To create a Clustered Index other than the primary key follow the following steps
1. Drop the primary key constraint and the index
IF (EXISTS(select * from sys.key_constraints where name = 'PK_sample1'))
ALTER table sample1 DROP PK_sample1
IF (EXISTS(select * from sys.indexes where name = 'PK_sample1'))
DROP INDEX PK_sample1 on sample1
2. Create the clustered index on the column that you want.
CREATE CLUSTERED INDEX IX_sname ON dbo.sample1 (sname);
3. Re-create the primary key
ALTER TABLE sample1
ADD CONSTRAINT PK_sample1 PRIMARY KEY (sid)

20 Database Management Systems

 INSERT INTO/ Insert
• The INSERT INTO statement is used to insert new records in a table.
Syntax
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
• If values for all the columns of the table are added , no need to specify the column names in
the SQL query. the order of the values should be in the same order as the columns in the
table
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
• Inserting Multiple records
Syntax
INSERT INTO table_name (column_list)
VALUES
(value_list_1), (value_list_2), ... (value_list_n);
Note: Text values should be entered inside single quotes like ‘ Text value’
21 Database Management Systems
 SELECT Statement
• The SELECT statement is used to select data from a database.
• The data returned is stored in a result table, called the result-set
Syntax
SELECT column1, column2, ...
FROM table_name;
• to select all the fields available in the table,
Syntax
SELECT * FROM table_name;
SQL SELECT DISTINCT Statement
• The SELECT DISTINCT statement is used to return only distinct (different) values.
• Inside a table, a column often contains many duplicate values; and sometimes you only want to list
the different (distinct) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;

22 Database Management Systems

 WHERE Clause
 The WHERE clause is used to filter records.
 It is used to extract only those records that fulfill a specified condition.

WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Note: The WHERE clause is not only used in SELECT statements, it is also used in
INSERT, UPDATE and DELETE

23 Database Management Systems

 Using more conditions in Where clause
• The WHERE clause can be combined with AND, OR, and NOT operators
• The AND and OR operators are used to filter records based on more than one condition:
The AND operator displays a record if all the conditions separated by AND are TRUE
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
• The NOT operator displays a record if the condition(s) is NOT TRUE.
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

24 Database Management Systems

 Comparison Operators
Operator Description

Checks if the values of two operands are equal or not, if yes then condition becomes true.
=

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
!=

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
<>

Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes
>
true.

Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
<

Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition
>=
becomes true.

Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition
<=
becomes true.

Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes
!<
true.

Checks if the value of left operand is not greater than the value of right operand, if yes then condition
!>
becomes true.
25 Database Management Systems
 Arithmetic Operators
Operator Description

+ (Addition) Adds values on either side of the operator.

Subtracts right hand operand from left hand operand.

- (Subtraction)

Multiplies values on either side of the operator.

* (Multiplication)

/ (Division) Divides left hand operand by right hand operand.

Divides left hand operand by right hand operand and returns

% (Modulus) remainder.

26 Database Management Systems

 Logical Operators
Sr.No. Operator & Description
ALL
1 The ALL operator is used to compare a value to all values in another value set.

AND
2 The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

ANY
3 The ANY operator is used to compare a value to any applicable value in the list as per the condition.
BETWEEN
4 The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS
5 The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.

IN
6 The IN operator is used to compare a value to a list of literal values that have been specified.

LIKE
7 The LIKE operator is used to compare a value to similar values using wildcard operators.

NOT
8 The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc.
This is a negate operator.
OR
9 The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL
10 The NULL operator is used to compare a value with a NULL value.
UNIQUE
11 The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

27 Database Management Systems

 SQL BETWEEN Operator
• The BETWEEN operator selects values within a given range. The values can be numbers,
text, or dates.
• The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

28 Database Management Systems

 SQL IN Operator
 The IN operator allows you to specify multiple values in a WHERE clause.
 The IN operator is a shorthand for multiple OR conditions.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
 or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

29 Database Management Systems

 SQL LIKE Operator
• The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
• There are wildcards often used in conjunction with the LIKE operator:
o The percent sign (%) represents zero, one, or multiple characters
o The underscore sign (_) represents one, single character
o [] (Square Brackets): Represents any single character within brackets.
o – (Hyphen): Specify a range of characters inside brackets.

Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
 Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?)
instead of the underscore (_).
 The percent sign and the underscore can also be used in combinations!

30 Database Management Systems

 LIKE Operator Description

WHERE CustomerName LIKE 'a%' Finds any values that start with "a"

WHERE CustomerName LIKE '%a' Finds any values that end with "a"

WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position

WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position

WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in
length

WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in
length

WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"

31 Database Management Systems

 IS NULL and IS NOT NULL operators
 It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

IS NULL and IS NOT NULL operators are used instead.

IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
 IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

32 Database Management Systems

 SQL Subqueries
 A subquery is a SQL query nested inside a larger query.
 A subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or
inside another subquery.
 A subquery may occur in SELECT clause, FROM clause or WHERE clause, but commonly
added within the WHERE Clause of another SQL SELECT statement.
 Comparison operators (such as >, <, or =) and multiple-row operator, (such as IN, ANY, or
ALL) can be used.
 The subquery (inner query) executes before its parent query (outer/ main query) so that the
main query use the subquery result
 Example
SELECT EmpId,EmpName,EmpFName
FROM Employee
WHERE EmpSalary=
(SELECT MAX(EmpSalary) FROM Employee);

33 Database Management Systems

 Note
 Enclose a subquery in parentheses.

 Place a subquery on the right side of the comparison operator.

 Use an ORDER BY clause in the main SELECT statement (outer query) not in subqueries

 Use single-row operators with single-row subqueries.

 If a subquery (inner query) returns a null value to the outer query, the outer query will not return
any rows when using certain comparison operators in a WHERE clause.
 A sub query can returns zero or one row(Single row subquery ), returns one or more
rows(Multiple row subquery), returns one or more columns (Multiple column subqueries), be
placed within another subquery (Nested subqueries), can reference one or more columns in the
outer SQL statement(Correlated subqueries)

34 Database Management Systems

 Common uses of SQL subqueries:
 Filtering data: Use subqueries in the WHERE clause to filter data based on specific conditions,
making your queries more dynamic.
 Nested aggregations: Employ subqueries to perform aggregations within aggregations, allowing
for more complex calculations.
 Checking existence: Determine whether a specific value exists in another table using subqueries
with the EXISTS or IN
 Subquery in FROM clause: Use a subquery in the FROM clause to create a temporary table,
allowing for more complex joins.
 Subquery in SELECT clause: Include a subquery in the SELECT clause to retrieve a single
value or set of values that can be used in the main query.

35 Database Management Systems

 SQL ANY and ALL Operators
 The ANY and ALL operators used to perform a comparison between a single column value and a
range of other values.
SQL ANY Operator
The ANY operator
• returns a boolean value as a result
• returns TRUE if ANY of the subquery 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 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 <=).

36 Database Management Systems

 SQL ALL Operator
• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition
• 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.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);

37 Database Management Systems

 SQL EXISTS Operator
• The EXISTS operator is used to test for the existence of any record in a subquery.
• The EXISTS operator returns TRUE if the subquery returns one or more records.
Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

38 Database Management Systems

 SQL SELECT from Multiple Tables
 using join query to get data from multiple tables
 A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
 The ‘Join’ syntax is capable of retrieving the matching columns from two or more
databases, or database tables.
 The databases are always visualized as tables, and they are the actual individual units
that hold the data in the form of columns and rows.
 Each table is constructed using an identifier key that remains unique for every row.
 All our manipulations are based on those keys – you can learn how to use them by
reading further. Before we move on to the differences, let us take a look at the general
syntax of the ‘join’.
SELECT Column_name
FROM Table1
JOIN Table2
ON Table1.Column_name = Table2.Column_name.
 The above syntax retrieves all the matching rows for the specified column names from
the tables – Table1 and Table2. You can note that the Column_name is the identifier key
in both tables, and the above query compares those to find the matching rows.
39 Database Management Systems
 Different Types of SQL JOIN
 (INNER) JOIN: Returns records that have matching values in both tables
 LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the
right table
 RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from
the left table
 FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

40 Database Management Systems

 SQL INNER JOIN Keyword
 The INNER JOIN keyword selects records that have matching values in both tables.
 INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
SQL LEFT JOIN Keyword
LEFT JOIN/ LEFT OUTER JOIN
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 Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

41 Database Management Systems

  SQL RIGHT JOIN / RIGHT OUTER JOIN
 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;
 SQL FULL OUTER JOIN/ FULL JOIN
 The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
 FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

42 Database Management Systems

 Ordered by
 The SQL ORDER BY Keyword

 The ORDER BY keyword is used to sort the result-set in ascending or descending order.

 The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.

 ORDER BY Syntax

SELECT column1, column2, ...

FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

43 Database Management Systems

 The SQL UPDATE Statement
 The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
The WHERE clause specifies which record(s) that should be updated.
 Note: If the WHERE clause is omitted, all records in the table will be updated!

44 Database Management Systems

 The SQL DELETE Statement
 The DELETE statement is used to delete existing records in a table.

 DELETE Syntax

DELETE FROM table_name WHERE condition;

The WHERE clause specifies which record(s) should be deleted
 Note: If the WHERE clause omitted , all records in the table will be deleted!

45 Database Management Systems

 The SQL GROUP BY
 The GROUP BY clause combines similar rows, producing a single summary result row for each group of rows that have the same
values, for each column listed in the Projection clause.
 The HAVING clause sets conditions on those groups after you form them.

 The GROUP BY statement is often used with aggregate functions

(COUNT(), MAX(), MIN(), SUM(), AVG())
to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING Condition
ORDER BY column_name(s);

46 Database Management Systems

  Example
For CustomerOrder database with the following tables
REGION (RegionId ,RegionName )
ZONE(ZoneId ,ZoneName ,ZRegionID )
Where ZRegionID references RegionId from REGION table
WOREDA(WoredaId ,WoredaName,WZoneId)
Where WZoneId references ZoneId from ZONE table
CUSTOMER (CustomerId, CustName,,CustFName ,CustWoredaId, Other attributes )
Where CustWoredaId references WoredaId from WOREDA table

To get the number of customers in each region ,zone and woreda we can use the
following SQL statements with group by clause

47 Database Management Systems

 Example 1
SELECT dbo.REGION.RegionName,count(CustomerId)
FROM dbo.CUSTOMER INNER JOIN dbo.WOREDA
ON dbo.CUSTOMER.CustWoredaId = dbo.WOREDA.WoredaId INNER JOIN
dbo.ZONE ON dbo.WOREDA.WZoneId = dbo.ZONE.ZoneId INNER JOIN
dbo.REGION ON dbo.REGION.RegionId = dbo.ZONE.ZRegionID
Group by REGION.RegionName

Example 2
SELECT dbo.REGION.RegionName,dbo.ZONE.ZoneName ,count(CustomerId)
FROM dbo.CUSTOMER INNER JOIN dbo.WOREDA
ON dbo.CUSTOMER.CustWoredaId = dbo.WOREDA.WoredaId INNER JOIN
dbo.ZONE ON dbo.WOREDA.WZoneId = dbo.ZONE.ZoneId INNER JOIN
dbo.REGION ON dbo.REGION.RegionId = dbo.ZONE.ZRegionID
GROUP BY REGION.RegionName,zone.ZoneName
ORDER BY REGION.RegionName, Zone.ZoneName ASC

48 Database Management Systems

 The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to
include extra rows that represent the subtotals, which are commonly referred to as super-aggregate
rows, along with the grand total row
SELECT COALESCE( REGION.RegionName, ' All Regions Total') As Region
,COALESCE(ZONE.ZoneName ,region.regionname) As Zone
,count(CUSTOMER.CustName)as 'Number of Customers'
FROM CUSTOMER INNER JOIN WOREDA ON
CUSTOMER.CustWoredaId=WOREDA.WoredaId
INNER JOIN zone on WOREDA.WZoneId=ZONE.ZoneId
INNER JOIN Region on ZONE.ZRegionID=REGION.RegionId
GROUP BY rollup( REGION.RegionName,ZONE.ZoneName)
HAVING count(CUSTOMER.CustName)>=4
ORDER BY REGION.RegionName,ZONE.ZoneName

49 Database Management Systems

 SQL CASE
The SQL CASE statement
The CASE statement is SQL's way of handling if/then logic.
The CASE statement is followed by at least one pair of WHEN and THEN statements—SQL's equivalent
of IF/THEN in Excel.
Every CASE statement must end with the END statement. The ELSE statement is optional, and provides a
way to capture values not specified in the WHEN/THEN statements.
Example:
SELECT *,
CASE
WHEN EmpSalary < 600
THEN 0
WHEN EmpSalary >600 and EmpSalary < 1650
THEN 0.1*EmpSalary -60
ELSE 0.15*EmpSalary -142.5
END AS Tax
FROM Employee

50 Database Management Systems

  The CASE statement checks each row to see if the conditional statement EmpSalary < 600 is

true.
•For any given row, if that conditional statement is true, the value 0 will be assigned to Tax

•In any row for which the conditional statement is false, move on to the next WHEN/THEN ,the condition

checked EmpSalary > 600 and EmpSalary < 1650 will be checked if that condition is true 0.1* EmpSalary
– 60 will be assigned toTax
•And if it is not true .15 * EmpSalary - 142.5 will be assigned to Tax
•At the same time all this is happening, SQL is retrieving and displaying all the values in Employee table

Note:
CASE must include the following components: WHEN, THEN, and END. ELSE is an optional
component.

51 Database Management Systems

 View
 A view is a virtual table whose contents are defined by a query (SQL Statement).

 The query that defines the view can be from one or more tables or from other views in the current or
other databases.
Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

52 Database Management Systems

 Example for the database with the following relations we can create a view VStudentEnroll containing
columns from three of the relations and also a calculated field TotalResult
 tblSTUDENT(StudentID,StuName,StuFname,StuGFName,StuGender,StuDoB,StuTelNo,StuNationality)

 tblCOURSE(CourseId,CourseTitle,CourseCode,CrdtHrs)

 tblENROLLMENT(EnrollId,CourseId,StudentID,DateofEnrollment,MidExResult,ProjectResult,FinalExResult)
CREATE VIEW VStudentEnroll AS
SELECT tS.StudentID,tS.StuName,tS.StuFname,tS.StuGFName,tS.StuGender,
tC.CourseTitle, tC. CrdtHrs,tE.MidExResult, tE.ProjectResult, tE.FinalExResult,
tE.MidExResult + tE.ProjectResult + tE.FinalExResult as TotalResult
FROM tblENROLLMENT tE
INNER JOIN tblSTUDENT tS ON tE.StudentID = tS.StudentID
INNER JOIN tblCOURSE tC ON tE.CourseId = tC.CourseId

53 Database Management Systems

 SQL Stored Procedures

 A stored procedure is a saved SQL code, and called to execute it repeatedly.

 It is also possible to pass parameters to a stored procedure, so that the stored procedure
can act based on the passed parameter value(s).
Stored Procedure Syntax
CREATE PROCEDURE procedure_name
AS
sql_statement
GO
 Execute a Stored Procedure Syntax
EXEC procedure_name;
Example
The following SQL statement creates a stored procedure named "SelectAllCustomers"
that selects all records from the "Customers" table:
CREATE PROCEDURE SelectAllEmployee
AS
SELECT * FROM EMPLOYEE
GO

 Execute the stored procedure above as follows: EXEC SelectAllEmployee

54 Database Management Systems
 Stored Procedure with One Parameter
 The following SQL statement creates a stored procedure that selects Customers from a particular
City from the "Customers" table:
Example
CREATE PROCEDURE selectempgender @gender varchar(1)
As
SELECT * FROM EMPLOYEE
WHERE EmpGender= @gender
Go
Execute the stored procedure
EXEC selectempgender @gender='M'

55 Database Management Systems

 Stored Procedure with Multiple Parameters
 Setting up multiple parameters is very easy. Just list each parameter and the data type separated
by a comma as shown below.
Example
CREATE PROCEDURE selectempgendersal @gender varchar(1), @salary money
As
SELECT * FROM EMPLOYEE
WHERE EmpGender= @gender and EmpSalary >=@salary
Go
Execute the stored procedure
EXEC selectempgendersal @gender='M' , @salary=14000

56 Database Management Systems

 Transact-SQL User defined Functions(UDF)

 User defined functions are objects that are stored in the database and avoid writing the same code over
and over again.
 Once functions are created and stored in the database, they can be called any number of times

 allow faster execution.

 UDF allow to control what is the input and define the structure/type of output and also allow to decide
permissions to DB users.
 SQL Server UDF are routines that accept parameters, perform an action, such as a complex calculation,
and return the result of that action as a value.

57 Database Management Systems

 Syntax
CREATE FUNCTION [database_name.]function_name (parameters)
RETURNS data_type AS
BEGIN
SQL statements
RETURN value
END;
•CREATE FUNCTION keywords. The schema name is optional (dbo is default).
•takes parameters as input surrounded by parentheses after the function name.
•specify the data type of the return value in the RETURNS statement.
•It contains a sequence of SQL statements which will use values provided as parameters , combine them with
other values (local variables) or database objects
•Returns result of the calculation (RETURN value) with the previously defined type
(RETURNS data_type)
•RETURN statement to return a value inside the body of the function.
Note The return value can either be a single scalar value or a result set(Table).

58 Database Management Systems

 Example function that returns a table as a result. For a STUDENT table with
Stuname,StuFname,age columns the following function will accept age and displays students with
the given age

CREATE FUNCTION udfstuAge (

@stuage INT
)
RETURNS TABLE
AS
RETURN
SELECT
Stuname,StuFname,age
FROM
STUDENT
WHERE
age = @stuage;

select * from udfstuAge(19)

59 Database Management Systems

 Scalar valued functions
 A Scalar Valued Function is a function that returns a single value back to the caller.

 SQL Server scalar function takes one or more parameters and returns a single value.

Example Using custumerorder database and SUPITEM and CUSTORDER tables

SUPITEM (ItemId ,ItemName, ItemUnit ,ItemUnitPrice , Other attributes )
CUSTORDER (OrderId ,CustomerIdO ,ItemIdO ,Quantity, OrderDescription )
Where ItemIdO references SUPITEM table column ItemId and
CustomerIdO references CUSTOMER table column CustomerId
to get the total amount of order of an item (sum of quantities order for a specific item (providing
its id ItemId )) can be obtained by the following scalar user defined function

60 Database Management Systems

 CREATE FUNCTION getitemquantSum(@itemID as INT)
RETURNS int
AS
BEGIN
DECLARE @itemquantitysum int
SELECT @itemquantitysum= SUM(co.Quantity)
FROM CUSTORDER as co
INNER JOIN SUPITEM as si
ON co.ItemIdO=si.ItemId
WHERE co.ItemIdO = @itemID
GROUP BY co.ItemIdO,si.ItemName
RETURN @itemquantitysum
END
GO

Select dbo.getitemquantSum(1)

61 Database Management Systems