Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This document is about advanced database lab manual
Typology: Schemes and Mind Maps
1 / 82
This page cannot be seen from the preview
Don't miss anything!
Prepared by TsegawKelela, Lecturer at the school of computing, Bahir Dar Institute of Technology
iv
List of Acronyms
DDL Data Definition Language
DML Data Manipulation Language
DBMS Database Management System
DB Database
SSMS SQL Server Management Studio
SQL Structured Query Language
SSIS SQL Server Integration Service
SSRS SQL Server Reporting Service
T-SQL Transact SQL
The past few decades have seen a proliferation of organizations that rely heavily on information technology. These organizations store their data in databases. There are several database management systems (DBMS) available in the software world with primary differences in performance, platform support, license, security and functionalities they provide.
In this manual, Microsoft SQL server 2012 and oracle database 12c (where deemed necessary) are used for demonstrating some database concepts. The lessons covered in this manual cover advanced concepts from both database development and administration point of view.
This manual is primarily prepared for Bsc degree students who already have taken the course “Fundamentals of Database Systems” or equivalent. The manual covers advanced database topics such as functions, stored procedures, triggers, security, transaction, import/export, backup, replication, mirroring and log shipping. After explanation of each topic, examples are given. In addition, each session includes hands-on exercises to test the knowledge of students and challenge them how to apply that knowledge to solve real world practical business problems.
The database tables used in this material are taken from the hypothetical Logical data model of XYZ registrar and ABC Publishing databases which are shown in Annex 1and 2 of this manual.
To evaluate student individual practical skills and performance on this course, there will be laboratory tests seriously monitored on regular basis.
The following DBMS softwares are needed to practice the concepts covered in this material:
Microsoft SQL Server 2012 or Later Oracle 12c or Later
At the end of this lesson, students will be able to: Declare variables Understand the usage of If…Else statements
Understand the usage of CASE…WHEN expressions Use control structures to solve problems
In SQL Server, if a variable is typically known as a local variable, it is only available in the batch, stored procedure or code block in which it is defined. A local variable is defined using the Transact-SQL (T-SQL) DECLARE statement. The name of the local variable needs to start with the @ symbol as the first character of its name. A local variable can be declared as any system or user defined data type. Here is a typical declaration for an integer variable named @Count. DECLARE @Count INT More than one variable can be defined with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you separate each variable definition with a comma, like this: DECLARE @Count INT, @X INT, @Y INT, @Z CHAR (10) In the above declaration, 4 local variables with a single DECLARE statement. A local variable is initially assigned a NULL value. A value can be assigned to a local variable by using the SET or SELECT statement. On the SET command you specify the local variable and the value you wish to assign to the local variable. Here is an example of where I have defined my @Count variable and then initialize the variable to 1. DECLARE @Count INT SET @Count = 1 Here is an example of how to use the SELECT statement to initialize the value of a local variable with the value returned from a select that fetch the total number of rows in the employee table. DECLARE @ROWCNT INT SELECT @ROWCNT= (SELECT COUNT (*) FROM employee) One of the uses of a variable is to programmatically control the records returned from a SELECT statement. You do this by using a variable in the WHERE clause.
if (select count(*) from Sales.dbo.Customer where fname like '[A-D]%') > 0 Print 'A-D Customers are Found'
The above examples only showed how to execute a single T-SQL statement if the condition is true. T-SQL allows you to execute a block of code as well. A code block is created by using a "BEGIN" statement before the first line of code in the code block, and an "END" statement after that last line of code in the code block. Here is any example that executes a code block when the IF statement condition evaluates to true. if db_name() = 'master' begin Print 'We are in the Master Database' Print '' Print 'So whatever code you execute must be done carefully' End else if db_name() = 'Sales' begin Print 'We are in the test Sales’ database Print 'So Enjoy' End Sometimes you want to not only execute some code when you have a true condition, but also want to execute a different set of T-SQL statements when you have a false condition. If this is your requirement then you will need to use the IF...ELSE construct, that I called format two above. Example: Suppose we want to determine whether to update or add a record to the employee table in the HR database. The decision is based on whether the employee already exists in the HR.dbo.Employee table. Here is the T-SQL code to perform this existence test for two different EmployeeId's. if exists(select * from HR.dbo.Employee where CustomerId = 'Abebe123') Print 'Need to update Record 'Abebe123' else Print 'Need to add Employee Record 'Abebe123'
sent to first statement of while loop.
Example 1:
IF (SELECT MAX(ListPrice) FROM Product) > 500
Example 2: usage of simple while loop DECLARE @counter INT SET @counter = 1 WHILE @counter <=5) BEGIN PRINT @counter SET @counter = @counter + 1 END Example 3: Usage of WHILE Loop with BREAK keyword DECLARE @counter INT SET @counter = 1 WHILE (@counter <=5) BEGIN PRINT @counter SET @counter =@counter + 1 IF @counter = 4 BREAK; END
Evaluates a list of conditions and returns one of multiple possible result expressions.
The simple CASE expression : It compares an expression to a set of simple expressions to determine the result.
ELSE 'Technical Assistant' END FROM employee ORDER BY sex; GO
At the end of this lab session, students will be able to: Create and alter user defined functions Use functions to solve problems User defined functions are functions which are developed by the users of the database system to supplement and extend the built-in functions.A user-defined function takes zero, or more, input parameters and returns either a scalar value or a table. Input parameters can be any data type except timestamp,cursor, or table. User defined functions may take the following forms: a. Scalar user defined functions b. Inline table valued functions c. Multi statements table valued functions
Syntax: CREATE FUNCTION [schema_name.]function_name ([@parameter_name parameter_data_type],[@parameter_name2 parameter_data_type], [@parameter_name parameter_data_type],…[parameter_nameN parameter_data_type] ) RETURNS return_data_type [ WITH<function_option>] AS BEGIN function_body RETURN scalar_expression END
Where Schema_name = the name of the function owner function_name = any valid sysname for the function @ parameter_name1 ………… @ parameter_nameN = the name of the parameter for the function return_data_type = any scalar data type data type like int, Varchar,Nvarcahr, date, char,Ncahr, etc.
If the function is being schema-bound, you must have REERENCE permission on tables, views, and functions referenced by the function. REFERENCE permissions can be granted through the GRANT statement to views and user defined functions as well as tables. If a CREATE TABLE or ALTER TABLE S statement references a user-defined function in a CHECK constraint, DEFAULT clause, or computed column, the table owner must also own the function.
These type of functions are fucntions that return list of records/tables based on a logic defined using a single SQL statement. Syntax: CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name ] parameter_data_type [ ,...n ] ] ) RETURNS TABLE [ WITH<function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ] [ ; ]
Example : create an inline table valued function to list the name of all female Instructors create function fn_FemaleInstructors() returns table as return select FirstName,MidleNamefrom Instructor where sex=‘F‘ To call the function, run the function in following manner: select * from dbo. fn_FemaleInstructors()
These type of functions are fucntions that return list of records/tables based on a logic defined using many SQL statements that perform complex operations. In the same way that you use a view, you can usea table-valued function in the FROM clause of a Transact-SQL statement. When using a multi-statement table-valued function, consider the following facts:
The BEGIN and END delimit the body of the function The RETURNS clause specifies table as the data type returned. The RETUENS clause defines a name for the table and defines the format of the table. The scope of the return variable name is local to the function.
Syntax:
CREATE FUNCTION [schema_name.] function_name ( [@parameter_name parameter_data_type, [ ,...n ] ] ) RETURNS @return_variable TABLE <table_type_definition> [ WITH<function_option> [ ,...n ] ] AS BEGIN function_body RETURN END Example : create a multi-statement table-valued function that returns the name orFull names of employees, depending on the parameter provided.
Solution:
CREATE FUNCTION fn_Employees (@length varchar(60)) RETURNS @fn_Employee TABLE (EmployeeIDint Primary Key Not Null, EmployeeName varchar(16) Not Null) AS BEGIN IF (@length =‘ShortName‘) INSERT @fn_Employees SELECT EmployeeID, fName FROM Employees ELSE IF(@length =‘FullName‘) INSERT @fn_Employees SELECT EmployeeID,fName + ‗ ‘+MidName + ‗ ‘ + GfatherName FROM Employees RETURN END
You modify a user-defined function by using the ALTER FUNCTION statement Syntax: ALTER FUNCTION function_name
Synax:
create [or replace] function [schema .] function [( argumentdatatype [, argument datatype]... )] returndatatype { is | as } { pl/sql_function_body }}; If a function already exists, you may replace it via create or replace function command. Ifyou use the or replace clause, any EXECUTE grants previously made on the function will remainin place. Example:
create or replace functionNetPay(sal float) return float is Np float; begin if (sal<=150) then Np:=sal; elsif (sal<=650) then NP:=sal-(sal-150).1; Elsif(sal>=651) then NP:=sal-(50+(sal-650).15) else NP:=sal-(112.50+(sal-1400)*.15); end if; returnNp; end;
To call the above oracle function, use the following code Select NetPay(NetPay) from dual; N.B. Dual is a dummy table with one element in it. It is useful because Oracle doesn't allow statements without specifying the From clause.
Show oracle equivalent SQL codes for the functions we created Using SQL server codes.
Salary deduction in a given range
Salary Tax(%) 0 - 150 0 151 - 650 10 651 - 1400 15
= 1401 20
