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' );