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	12,000
103	Nitin	Vyas	24-12-1994	8,000
104	Rakshit	Soni	18-5-2001	14,000
105	Rashmi	Malhotra	11-9-2004	11,000
106	Sulekha	Srivastava	5-6-2006	10,000

Table: COURSES

C_ID	F_ID	CName	Fees
C21	102	Grid Computing	40,000
C22	106	System Design	16,000
C23	104	Computer Security	8,000
C24	106	Human Biology	15,000
C25	102	Computer Network	20,000
C26	105	Visual Basic	6,000

1. To display complete details (from both the tables) of those Faculties whose salary is less than 12000.
2. To display the details of courses whose fees is in the range of 20000 to 50000 (both values included).
3. To increase the fees of all courses by 500 which have "Computer" in their Course names.
4. (A) To display names (FName and LName) of faculty taking System Design.
                                                OR
   (B) To display the Cartesian Product of these two tables. 

Answer 1

1. Select * from FACULTY natural join COURSES where Salary<12000;
   Or
   Select * from FACULTY, COURSES where Salary<12000 and facuty.f_id=courses.f_id;
2. Select * from courses where fees between 20000 and 50000;
3. Update courses set fees=fees+500 where CName like '%Computer%';
4. (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;
                                       Or
   (B) Select * from FACULTY, COURSES;