Question

Saman has been entrusted with the management of Law University Database. He needs to access some information from FACULTY and COURSES tables for a survey analysis. Help him extract the following information by writing the desired SQL queries as mentioned below.

                     +Table: FACULTY

F_ID	FName	LName	Hire_Date	Salary
102	Amit	Mishra	12-10-1998	12000
103	Nitin	Vyas	24-12-1994	8000
104	Rakshit	Soni	18-5-2001	14000
105	Rashmi	Malhotra	11-9-2004	11000
106	Sulekha	Srivastava	5-6-2006	10000

              Table: COURSES

C_ID	F_ID	CName	Fees
C21	102	Grid Computing	40000
C22	106	System Design	16000
C23	104	Computer Security	8000
C24	106	Human Biology	15000
C25	102	Computer Network	20000
C26	105	Visual Basic	6000

(I) To display complete details (from both the tables) of those Faculties whose salary is less than 12000.

(II) To display the details of courses whose fees is in the range of 20000 to 50000 (both values included).

(III) To increase the fees of all courses by 500 which have "Computer" in their Course names.

(IV) (A) To display names (FName and LName) of faculty taking System Design.

(B) To display the Cartesian Product of these two tables

Answer 1

(I) Select * from FACULTY natural join COURSES where Salary<12000;

Or

Select * from FACULTY, COURSES where Salary<12000 and

facuty.f_id=courses.f_id;

(II) Select * from courses where fees between 20000 and 50000;

(III) Update courses set fees=fees+500 where CName like

'%Computer%';

(IV)

(A) Select FName, LName from faculty natural join courses where

Came="System Design";

Or

Select FName, LName from faculty, courses where Came="System

Design" and facuty.f_id=courses.f_id;

(B) Select * from FACULTY, COURSES;