**Consider the relational database:**

**Students**(Roll, SName, SAddress, SContact, SFee)**Teachers**(TID, TName, TSalary, TAddress)**Teaches**(Roll, TID)

Write the **SQL statement** for the following:

- Find the record of the student who pay fee in the range of Rs. 1000 and Rs. 10000.
- Find the name of teacher consisting of at most 6 characters.
- List the name of all teachers in ascending order.
- Find the name of the highest paying teacher.
- Insert a new tuple in the relation teacher.

## A: SQL queries for Relations Students Teachers and Teaches: 65

## i. Find the record of the student who pays a fee in the range of Rs. 1000 and Rs. 10000.

**Option** **#1:** Using >= and <=.

```
SELECT *
FROM Students
WHERE SFee >= 1000
AND SFee <= 10000;
```

**Option** **#2:** Using BETWEEN.

```
SELECT *
FROM Students
WHERE SFee BETWEEN 1000 AND 10000;
```

## ii. Find the name of the teacher consisting of at most 6 characters.

**Option #1:** Using LEN OR DATALENGTH. For Microsoft SQL Server (MSSQL).

```
SELECT TName
FROM Teachers
WHERE LEN(TName) <= 6;
---------- OR ------------
SELECT TName
FROM Teachers
WHERE DATALENGTH(TName) <= 6;
```

**Option #2:** Using LENGTH OR CHAR_LENGTH. For MySQL.

```
SELECT TName
FROM Teachers
WHERE LENGTH(TName) <= 6;
---------- OR ------------
SELECT TName
FROM Teachers
WHERE CHAR_LENGTH(TName) <= 6;
```

## iii. List the name of all teachers in ascending order.

```
SELECT TName
FROM Teachers
ORDER BY TName ASC;
```

## iv. Find the name of the highest-paying teacher.

**Method #1:** Using **Nested Queries**.

```
SELECT TName
FROM Teachers
WHERE TSalary = (SELECT MAX(TSalary)
FROM Teachers);
```

**Method #2:** Using **ORDER BY** and **LIMIT**

```
SELECT TName FROM Teachers
ORDER BY TSalary DESC
LIMIT 1;
```

## v. Insert a new tuple in the relation teacher.

**#1** **Assuming** TID is not an AUTO INCREMENT field.

```
INSERT INTO teachers
VALUES ( 'T112',
'Ram',
80000,
'Dhangadhi' );
```

**#2** **Assuming** TID is an AUTO INCREMENT field.

```
INSERT INTO Teachers
(TName,
TSalary,
TAddress)
VALUES ( 'Ram',
80000,
'Dhangadhi' );
```

Asked in Year

2018

Course

BBM

University

TU