Scribd
Upload
3 views2 pages

SQLServer Lab

The document outlines a series of SQL Server tasks to be performed on the ITI database, including creating various types of functions (scalar, multi-statement table-valued, and inline) to manipulate and retrieve data related to students and departments. It also includes queries for data retrieval and deletion, as well as a bonus section that covers hierarchyid data type and a batch insert of 3000 rows into the student table. Each task specifies the expected input and output, guiding the user in implementing SQL functions and queries.

Uploaded by

amr.yehia.zakaria
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views2 pages

SQLServer Lab

The document outlines a series of SQL Server tasks to be performed on the ITI database, including creating various types of functions (scalar, multi-statement table-valued, and inline) to manipulate and retrieve data related to students and departments. It also includes queries for data retrieval and deletion, as well as a bonus section that covers hierarchyid data type and a batch insert of 3000 rows into the student table. Each task specifies the expected input and output, guiding the user in implementing SQL functions and queries.

Uploaded by

amr.yehia.zakaria
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQLServer Lab

Note: Use ITI DB

1. Create a scalar function that takes date and returns Month name of that
date.

2. Create a multi-statements table-valued function that takes 2 integers


and returns the values between them.

3. Create inline function that takes Student No and returns Department


Name with Student full name.

4. Create a scalar function that takes Student ID and returns a message to


user
a. If first name and Last name are null then display 'First name &
last name are null'
b. If First name is null then display 'first name is null'
c. If Last name is null then display 'last name is null'
d. Else display 'First name & last name are not null'

5. Create inline function that takes integer which represents manager ID


and displays department name, Manager Name and hiring date

6. Create multi-statements table-valued function that takes a string


If string='first name' returns student first name
If string='last name' returns student last name
If string='full name' returns Full Name from student table
Note: Use “ISNULL” function
7. Write a query that returns the Student No and Student first name
without the last char
8. Write query to delete all grades for the students Located in SD
Department
Bonus:

1. Give an example for hierarchyid Data type


2. Create a batch that inserts 3000 rows in the student table(ITI database).
The values of the st_id column should be unique and between 3000 and
6000. All values of the columns st_fname, st_lname, should be set to 'Jane',
' Smith' respectively.

You might also like