Department of Computer Science & Information Technology

Khwaja Fareed University of Engineering & Information Technology

Course: Database Systems

Lab Instructor:
Ms. Hira Kanwal

Student Name

Student Roll #

Department

Batch/Year/Section

For Lab. Instructor

Marks Signature

KFUEIT Department of CS/IT

xl
Lab Manual # 5 Creating & Manipulating Databases

5.1. Objective
1. AND & OR Operator
2. Auto increment field in SQL Server
3. ORDER BY Clause
a. Single Column
b. Multiple Column
4. SQL Aggregate Functions
a. COUNT
b. SUM
c. AVG
d. Max
e. Min
5. String Concatenation

5.2. AND / OR Operators

Complex comparisons can be made by using logical operators AND, OR and NOT. Rules for
evaluating a conditional expression are:

i. Expressions are always evaluated left to right.

ii. Subexpressions in brackets are evaluated first.
iii. NOT’s are evaluated before AND’s and OR’s
iv. AND’s are evaluated before OR’s.

Example: Write a query to get all students enrolled in BSCS or BSIT.

select name from detail where class = 'BSCS' OR class = 'BSIT';

The purpose of these SQL Server operators is to allow filtering based on multiple conditions.
Using these operators, you can combine the result of two (or more) conditions to produce a
single result based on them.

 AND requires all conditions to be true.

 OR requires either condition to be true.

5.3. Auto increment Field in SQL Server

MS SQL Server uses IDENTITY keyword to perform auto increment feature. The
IDENTITY feature is found in column properties of the table. Identity value is specified as:

IDENTITY (SEED, INCREMENT) where SEED is the starting value of column.

E.g., IDENTITY (1, 1) means starts a column with 1, and increments each row with 1.

IDENTITY (1, 2) means starts a column with 1, and increment each row with 2.

KFUEIT Department of CS/IT

41
Lab Manual # 5 Creating & Manipulating Databases

Syntax for defining a column Identity:

The following SQL statement defines the "ID" column to be an auto-increment primary key
field in the "Persons" table:

CREATE TABLE Persons

(ID int IDENTITY(1,1) PRIMARY KEY,

LastName varchar(25) NOT NULL,
FirstName varchar(25),
Age int
);

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for
each new record.

Tip: To specify that the "ID" column should start at value 10 and increment by 5, change it to
IDENTITY(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the
"ID" column (a unique value will be added automatically).

Notes:

1. There can only be one IDENTITY column per table.

2. Once an identity value is initiated, it cannot be reset. A DBCC (Data Base Console
Command) is used to reset the identity value.

Command Syntax: DBCC CHECKIDENT(‘TableName’ , Reseed, 0);

5.4. ORDER BY Clause

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

i. The ORDER BY Clause is the last clause of Select statement.

ii. There are two types of orderings, Single column ordering and Multiple column
ordering.

5.4.1. Single Column Ordering

Show the list of all employees with their salaries in descending order.

SELECT empname, empdesignation, empsalary

FROM employee
ORDER BY empsalary DESC;

KFUEIT Department of CS/IT

42
Lab Manual # 5 Creating & Manipulating Databases

5.4.2. Multiple Column Ordering

Show the list of all employees with their salaries in descending order.

SELECT empname, empdesignation, empsalary

FROM employee
ORDER BY empdesignation ,empsalary DESC;

It is possible to include more than one element in the ORDER BY Clause. The first column is
known as Major Sort key, while the second column name is known as Minor Sort key.

Major Sort Key determines the overall order of the result table. If the values of the Major Sort
key are unique, there is no need of any additional key. In the other case, another key called
Minor sort key is also included.

The result of the above query will be now ordered first by employee designation (in
Ascending order), and within employee designation, in descending order of employee salary.

5.5. SQL Aggregate Functions

SQL Aggregate functions operate on single column of a table and return a single value.

i. COUNT

select count(*) from tablename;

ii. SUM

Write a Query to find the total number of employees with designation manager and their total
salary.

select COUNT(EmpDesig), SUM(empSalary) from Employee where EmpDesig = 'Manager';

iii. AVG, MIN and MAX

Write a query to get the Average, Minimum and Maximum salary of all the employees.

select MIN(empsalary), MAX(empsalary), AVG(empsalary) from Employee;

Notes:

1. COUNT, MAX and MIN apply to both numeric and non-numeric fields. While SUM
and AVG works only on numeric datatypes.
2. Apart from COUNT, all functions first eliminate null values, and then operate on
remaining values. While COUNT function operates on all null and not null values.
3. The keyword DISTINCT has no effect with MIN and MAX functions. However, it
may have an effect on the result of SUM and AVG.
4. Aggregate Functions can only be used in the SELECT list.

KFUEIT Department of CS/IT

43
Lab Manual # 5 Creating & Manipulating Databases

5.6. String Concatenation

SQL Server String concatenation allows you to append one string to the end of another string.
To display the contents of two columns or more under the name of a single column, you can
use the concatenation operator (+).

Note: Columns we are concatenating must be of type String.

5.7. LAB TASK

For the AC Database created in Lab 4, write queries to the following questions:

5.7.1. Display all the data from Customers table for all customers who have the letters: i, j
or h in their last name. Order the query in descending order by monthly discount
(Customerstable).

5.7.2. Display the first name, join date, monthly discount and package number for all
customers who don’t have the letter ‘a’ in their last name. Order the query in
ascending order by package number (Customers table).

KFUEIT Department of CS/IT

44
Lab Manual # 5 Creating & Manipulating Databases

5.7.3. Display all data from Customers table for all customers without a package (package
number is null)

5.7.4. Display the first name concatenated with the last name (separated by space), and
monthly discount for all customers whose monthly discount is not in the range
between 20 and 30. Order the query in ascending order by the full name (Customers
table).

5.7.5. Display the first name concatenated with the last name (separated by space), the main
phone number concatenated with secondary phone number (separated by comma),
and monthly discount for all customers whose monthly discount is in the range
between 11 and 27. Order the table by Joining Date in descending order. Name the
column headings FULL_NAME, CONTACTS, and DC respectively (Customers
table).

KFUEIT Department of CS/IT

45
Lab Manual # 5 Creating & Manipulating Databases

5.7.6. Display all data from Customers table for:

5.7.6.1.All customers who live in Lahore and whose monthly discount is in the range between
30 and 40 or
5.7.6.2.All customers whose package number is not 8,19, or 30 and whose join date is before
January 1st, 2007
Order the result by package id and monthly discount

KFUEIT Department of CS/IT

46
Lab Manual # 5 Creating & Manipulating Databases

Aggregate Functions

5.7.7. Display the lowest last name alphabetically (Customers table).

5.7.8. Display the average monthly payment (Packages table).

5.7.9. Display the highest last name alphabetically (Customers table).

5.7.10. Display the number of internet packages (Packages table).

5.7.11. Display the number of records in Customers table.

5.7.12. Display the number of distinct states (Customers table).

KFUEIT Department of CS/IT

47
Lab Manual # 5 Creating & Manipulating Databases

5.7.13. Display the number of distinct internet speeds (Packages table).

5.7.14. Display the number of values (exclude Nulls) in Fax column (Customers table).

5.7.15. Display the number of Null values in Fax column (Customers table).

5.7.16. Display the highest, lowest and average monthly discount (Customers table).

KFUEIT Department of CS/IT

48