SQL queries for Relations Students Teachers and Teaches

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:

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

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