java video tutorials

DBMS II Practical Workbook Answers

Worksheet  3

DDLs in T_SQL

1. Bank schema

create table Bank(

code varchar(50),

name varchar(50) not null ,

descrip varchar(50),

constraint pk_Bank primary key(code)

)

create table Branch (

bank_code varchar(50),

branch_no varchar(50),

name varchar(50),

addr varchar(50),

constraint pk_Branch primary key(bank_code,branch_no),

constraint fk_Branch foreign key(bank_code) references Bank (code)

)

create table Account (

acc_no varchar(50),

balance float,

bank_code varchar(50),

branch_no varchar(50),

type_code varchar(50),

constraint pk_Account primary key(acc_no),

constraint fk1_Account foreign key(bank_code,branch_no)

references Branch (bank_code,branch_no),

constraint fk2_Account foreign key(type_code) references

AccountType (code),

constraint chk_Account check (balance >= 0)

)

create table AccountType (

code varchar(50),

name varchar(50),

decrip varchar(50),

constraint pk_AccountType primary key(code)

)

create table Saving (

code varchar(50),

interest_rate float,

constraint pk_Saving primary key(code),

constraint fk_Saving foreign key(code) references AccountType (code),

constraint chk_Saving check (interest_rate >= 0)

)

create table CurrentAcc (

code varchar(50),

max_cheque float,

constraint pk_CurrentAcc primary key(code),

constraint fk_CurrentAcc foreign key(code) references AccountType (code)

)

create table Balongs_to (

acc_no varchar(50),

cus_no varchar(50),

constraint pk_Balongs_to primary key(acc_no,cus_no),

constraint fk_Belongs_to foreign key(acc_no) references Account (acc_no),

constraint fk2_Belongs_to foreign key(cus_no) references Customer (cus_no),

)

create table Customer (

cus_no varchar(50),

NIC varchar(15),

name varchar(50)not null,

addr varchar(50),

phone varchar(12),

PIN char(4),

constraint pk_Customer primary key(cus_no),

constraint chk_Customer check (PIN like '[0-9][0-9][0-9][0-9]'),

constraint chk2_Customer check ( NIC like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][V,X]')

)

create table Has (

acc_no varchar(50),

trans_id varchar(50),

Trans_type varchar(20),

constraint pk_Has primary key(acc_no),

constraint fk_Has foreign key(acc_no) references Account (acc_no),

constraint fk2_Has foreign key(trans_id) references Transactions (trans_id),

constraint chk_Has check(Trans_type in('Credit','debit'))

)

create table Transactions (

trans_id varchar(50),

amount float,

descrip varchar(50),

executed_by varchar(50),

dateAndtime datetime default getdate(),

constraint pk_Transactions primary key(trans_id),

constraint chk_Transactions check(executed_by in('ATM','Teller','Bank','Standing order','Cheque','On-Line','Other'))

)

2. Library schema

create table Book (

item_no varchar(50),

title varchar(50) not null,

decrip varchar(50),

constraint pk_Books primary key(item_no),

constraint chk_Books check(item_no like 'B%')

)

create table Written_by (

item_no varchar(50),

author_id int,

constraint pk_Written_by primary key(item_no,author_id),

constraint fk_Witten_by foreign key(item_no) references Books (item_no),

constraint fk2_Witten_by foreign key(author_id) references Author (author_id)

)

create table Author (

author_id int IDENTITY(1,1),

fname varchar(50),

lname varchar(50),

other varchar(50),

constraint pk_Author primary key(author_id)

) 

create table TextBooks (

item_no varchar(50),

semester varchar(20),

year int,

section varchar(50),

constraint pk_TextBooks primary key(item_no),

constraint fk_TextBooks foreign key(item_no) references Books (item_no),

constraint chk_TextBooks check(section in('Lecture','Lab','Tutorial')),

constraint chk2_TextBooks check(year between '1999' and '3000')

)

create table CopyText (

access_no varchar(50),

item_no varchar(50),

status varchar(15),

constraint pk_CopyText primary key(access_no),

constraint fk_CopyText foreign key(item_no) references TextBooks (item_no),

constraint chk_CopyText check(status in('Borrowed','Stolen','Available'))

)

create table PublishedBooks (

item_no varchar(50),

edition varchar(15),

constraint pk_PublishedBooks primary key(item_no),

constraint fk_PublishedBooks foreign key(item_no) references Books (item_no)

)

create table PublishedBooksType (

ISBN varchar(50),

item_no varchar(50),

cover varchar(15),

published_yr int,

publisher varchar(50),

constraint pk_PublishedBooksType primary key(ISBN),

constraint fk_PublishedBooksType foreign key(item_no) references PublishedBooks (item_no),

constraint chk_PublishedBooksType check(cover in('Soft Cover','Hard Cover')),

constraint chk2_PublishedBooksType check(published_yr between 1000 and 3000)

)

create table CopyPublishedBooksType(

access_no varchar(50),

ISBN varchar(50),

status varchar(15),

constraint pk_CopyPublishedBooksType primary key(access_no),

constraint fk_CopyPublishedBooksType foreign key(ISBN) references PublishedBooksType (ISBN),

constraint chk_CopyPublishedBooksType check(status in('Borrowed','Stolen','Available'))

);

create table Periodicals (

item_no varchar(50),

title varchar(50) not null,

decrip varchar(50),

frequency varchar(50),

publisher varchar(50),

type varchar(10),

constraint pk_Periodicals primary key(item_no),

constraint chk_Periodicals check(item_no like 'P%'),

constraint chk2_Periodicals check(type in('Magazine','Newspaper'))

)

create table PeriodicalType (

item_no varchar(50),

date datetime,

constraint pk_PeriodicalType primary key(item_no,date),

constraint fk_PeriodicalType foreign key(item_no) references Periodicals(item_no)

)

create table CopyPeriodicalType (

access_no varchar(50),

status varchar(15),

item_no varchar(50),

date datetime,

constraint pk_CopyPeriodicalType primary key(access_no),

constraint fk_CopyPeriodicalType foreign key(item_no,date) references PeriodicalType(item_no,date),

constraint chk_CopyPeriodicalType check(status in('Borrowed','Stolen','Available'))

) 

create table SoftwareAndManuals (

item_no varchar(50),

title varchar(50) not null,

decrip varchar(50),

manufacture varchar(50),

version varchar(50),

constraint pk_SoftwareAndManuals primary key(item_no),

constraint chk_SoftwareAndManuals check(item_no like 'S%')

)

create table CopySoftwareAndManuals (

access_no varchar(50),

status varchar(15),

item_no varchar(50),

constraint pk_CopySoftwareAndManuals primary key(access_no),

constraint fk_CopySoftwareAndManuals foreign key(item_no) references SoftwareAndManuals (item_no),

constraint chk_CopySoftwareAndManuals check(status in('Borrowed','Stolen','Available'))

)

3. SLIIT schema

create table Campus (

code varchar(50),

name varchar(50) not null,

addr varchar(50),

constraint pk_Campus primary key(code)

)

create table Batch (

id varchar(50),

groupNo varchar(50) not null,

maximum int,

section varchar(50),

code varchar(50),

prg_code varchar(50),

constraint pk_Batch primary key(id),

constraint fk_Batch foreign key(code) references Campus (code),

constraint fk2_Branch foreign key(prg_code) references Program (prg_code)

)

create table Program (

prg_code varchar(50),

name varchar(50),

special varchar(50),

descrip varchar(50),

constraint pk_Program primary key(prg_code),

)

create table Batch_Semester (

batch_id varchar(50),

acadamic_sem_no int,

constraint pk_Batch_Semester primary key(batch_id,acadamic_sem_no),

constraint fk_Batch_Semester foreign key(batch_id) references Batch(id),

constraint fk2_Batch_Semester foreign key(acadamic_sem_no) references Acadamic_Semester (acadamic_sem_no),

)

create table Semester (

prgm_code varchar(50),

sem_no int,

semester varchar(50) not null,

year int not null,

constraint pk_Semester primary key(prgm_code,sem_no),

constraint fk_Semester foreign key(prgm_code) references Program (prg_code)

)

create table Acadamic_Semester (

acadamic_sem_no int,

year int,

prgm_code varchar(50),

sem_no int,

constraint pk_Acadamic_Semester primary key(acadamic_sem_no),

constraint fk_Acadamic_Semester foreign key(prgm_code,sem_no) references Semester (prgm_code,sem_no),

)

create table Student (

reg_no varchar(50),

name varchar(50),

address varchar(50),

phone varchar(10),

constraint pk_Student primary key(reg_no)

)

create table Register (

reg_no varchar(50),

batch_id varchar(50),

acadamic_sem_no int,

date datetime DEFAULT GETDATE(),

constraint pk_Register primary key(reg_no,batch_id,acadamic_sem_no),

constraint fk_Register foreign key(reg_no) references Student (reg_no),

constraint fk2_Register foreign key(batch_id,acadamic_sem_no) references Batch_Semester (batch_id,acadamic_sem_no)

)



Worksheet  4

Objective: Moving Data


Export the data given in the excel sheets to the Library and Bank databases which is developed by you. You may use Data Transformation Services (Import/Export Wizard) and T-SQL scripts to do this. If you write any SQL or T-SQL scripts to load data to the tables, you should attach them to your solution.

1. Enter the data into an Excel spreadsheet

• First, enter the data into an Excel spreadsheet.
• In this example, the default spreadsheet name, Sheet1, has been retained, but if you rename the sheet (to the name you want for the table in the database, for example), that name will be automatically used in the import process (at Step 6).

Small demonstration Excel spreadsheet ready for transfer to a SQL database

2. Start the SQL Import and Export Wizard

• Next, in Windows, start the Import and Export Wizard at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.
• The Welcome page appears. Click Next.

SQL Import and Export Wizard in the Windows Start menu

OR  Right click on the you needed database in the Object Explorer, select Tasks, then Import Data from the context menu to launch the Import Wizard. 

3. Chose your Excel spreadsheet as the Data Source

• In the Data Source dropdown of the Choose a Data Source page, select Microsoft Excel.
• In the Excel file path box, specify the file path to the Excel spreadsheet.
• Select the the version in the Excel version dropdown.
• Make sure that First row has column names is checked.
• Click Next.

4. Chose your SQL database as the destination

• In the Destination dropdown list, accept the default setting of SQL Server Native Client 10.0.
• In the Server name dropdown list, enter the name of the server. The example is a remote server, so the IP address and port of the server were specified.
• Chose the Authentication type. The example is a remote server, so SQL Server authentication, with a user name and password, is required.
• In the Database dropdown list, select or type the name of the database.
• Click Next.

5. Specify how to copy the data

• The default option, Copy data from one or more tables or views, works for this example.
• If you want to try the second option, Write a query to specify the data to transfer, the followinglolcode snippet may be instructive:

CAN HAS SQL?
DBASE IZ GETDB('db/demo.db')
FUNNAHS IZ DBUCKET(&DBASE&,"CAN I PLZ GET * ALL UP IN lollit")
IM IN UR FUNNAHS ITZA TITLE
    VOTEZ IZ &TITLE#ups& - &TITLE#downs&

• Click Next.

 

6. Select the source tables and views

• The default settings as shown work in this example.
• In the Destination column, you can specify a different name for the table in the SQL database if you choose.
• Click Preview to see how your data will appear in the destination table.
• You can click Edit Mappings to change how your data is assigned at the destination table, but it shouldn't be necessary in this example since you entered the data into the Excel spreadsheet yourself.
• Click Next.

7. Run the "Package"

The following message appears at the bottom of the Run Package page: In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation. A "package" is all of the settings that your have configured so far. In the commercial versions of SQL Server, you can save the package for reuse so you don't have to enter all of the settings the next time you run the wizard. In the Express (free) version of Microsoft SQL Server 2008 Management Studio, you must re-enter all of the settings every time you run the SQL Server Import and Export wizard.

• Click Next.

8. Verify that the package executed successfuly

• Click Report to view useful information about the data transfer process.
• Click Close.

9. View the new table in SQL Server Management Studio

• View your new table by opening Microsoft SQL Server 2008 Management Studio at Start / All Programs / Microsoft SQL Server 2008/ Import and Export Data.

The new table, dbo.Sheet1$, in Microsoft SQL Server Management Studio

You can also use the Import and Export Wizard to export data from a table in SQL Server to an Excel spreadsheet.

Worksheet  5

--Exercise 1.

Create a view in T-SQL (called Cust_Inf containing the following information of customers:

 Customer’s number, NIC, name,

 His/her account information: account number and account type’s name

Using the view above, query Mr. Sampath Wijesinghe’s account information.

create table Customer(

customer_number int primary key,

name varchar(100),

nic varchar(10),

);

create table Account(

account_number varchar(100) primary key,

account_type varchar(100),

customer_number int,

amount float,

constraint fk1 foreign key (customer_number) references Customer(customer_number),  

);

create view Cust_Inf(customer_number,name,nic,account_number,account_type)

as

select c.customer_number,c.name,c.nic,a.account_number,a.account_type

from Customer c,Account a

where c.customer_number = a.customer_number

select *

from Cust_Inf

-- Exercise 2

A policy of the bank is that a single customer cannot have more than 5 accounts. Write a DML trigger to ensure that this policy is enforced. Note that the customer can have more than 5 accounts in total from different banks.

Test your trigger with sample data.

create trigger trigger1

on Account

for insert,update

as

begin

            declare @customer_number int

            declare @count_ int

            select @customer_number = i.customer_number

            from Inserted i

            select @count_ = COUNT(*)

            from Account

            where customer_number = @customer_number

            if(@count_>5)

            begin

                        rollback transaction

            end

end

-- Exercise 3

Write a stored procedure, which takes the account number and returns the number of cheques processed for that account during the current month.

Test your stored procedure with sample data.

create table cheque

(

chequeNo int primary key ,

dates datetime,

account_number varchar(100),

constraint fk2 foreign key (account_number) references Account(account_number), 

);

select *

from cheque

create procedure count_cheques(@number varchar,@noOfCheque int output )

as

begin

            declare @months int

            select @months=month(getdate())

            select @noOfCheque = COUNT(*)

            from Account a,cheque c

            where  a.account_number=@number and a.account_number=c.account_number and c.dates = @months

end;

drop procedure count_cheques

-- Exercise 4:

Write a stored procedure that transfers funds between accounts. The input parameters include account number of source account, account number of destination account and amount to be transferred. Ensure that the transaction occurs within a single transaction. Also, ensure that appropriate information is updated in the Transaction table.

Test your stored procedure with sample data.

create procedure transactions (@sourseAccount int ,@destinationAccount int,@amount float)

as

begin

            declare @sourseAmount float

            declare @destinationAmount float

            select @sourseAmount=amount

            from Account a

            where a.account_number=@sourseAccount

            select @destinationAmount=amount

            from Account b

            where b.account_number=@destinationAccount

            if(@sourseAmount>@amount)

            begin

             

                        SET @destinationAmount=@destinationAmount+@amount

                        SET @sourseAmount = @sourseAmount-@amount

                        update Account

                        set amount=@sourseAmount

                        where account_number =@sourseAccount

                        update Account

                        set amount=@destinationAmount

                        where account_number =@destinationAccount

                        return;

            end

            else

            begin

                        rollback transaction

            end

end;

SQL Create Table

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name


The CREATE TABLE Statement

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

SQL CREATE TABLE Syntax

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


SQL Constraints

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).

We will focus on the following constraints:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT


SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)


SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.


SQL UNIQUE Constraint on CREATE TABLE

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)


SQL UNIQUE Constraint on ALTER TABLE

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)


To DROP a UNIQUE Constraint

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID


SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.


SQL PRIMARY KEY Constraint on CREATE TABLE

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)


SQL PRIMARY KEY Constraint on ALTER TABLE

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID


SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.


SQL FOREIGN KEY Constraint on CREATE TABLE

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)


SQL FOREIGN KEY Constraint on ALTER TABLE

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)


To DROP a FOREIGN KEY Constraint

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders


SQL CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.


SQL CHECK Constraint on CREATE TABLE

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)


SQL CHECK Constraint on ALTER TABLE

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

ALTER TABLE Persons
DROP CONSTRAINT chk_Person


SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.


SQL DEFAULT Constraint on CREATE TABLE

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)


SQL DEFAULT Constraint on ALTER TABLE

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'


To DROP a DEFAULT Constraint

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT



The ALTER TABLE Statement

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


SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype


To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name
DROP COLUMN column_name


To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype


SQL NULL Values

If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

NULL values are treated differently from other values.

NULL is used as a placeholder for unknown or inapplicable values.

Note: It is not possible to compare NULL and 0; they are not equivalent.


SQL IS NULL

How do we select only the records with NULL values in the "Address" column?

We will have to use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL


SQL IS NOT NULL

How do we select only the records with no NULL values in the "Address" column?

We will have to use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL