Computer Science
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.
OR
(B) To display the Cartesian Product of these two tables.
Answer
(I)
SELECT * FROM FACULTY, COURSES
WHERE Salary < 12000 AND FACULTY.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, COURSES
WHERE CName = 'System Design' AND
FACULTY.F_ID = COURSES.F_ID;
OR
(B)
SELECT *
FROM FACULTY, COURSES;
Related Questions
Consider the table ORDERS as given below
O_Id C_Name Product Quantity Price 1001 Jitendra Laptop 1 12000 1002 Mustafa Smartphone 2 10000 1003 Dhwani Headphone 1 1500 Note: The table contains many more records than shown here.
A) Write the following queries:
(I) To display the total Quantity for each Product, excluding Products with total Quantity less than 5.
(II) To display the orders table sorted by total price in descending order.
(III) To display the distinct customer names from the Orders table.
(IV) Display the sum of Price of all the orders for which the quantity is null.
OR
B) Write the output
(I)
Select c_name, sum(quantity) as total_quantity from orders group by c_name;(II)
Select * from orders where product like '%phone%';(III)
Select o_id, c_name, product, quantity, price from orders where price between 1500 and 12000;(IV)
Select max(price) from orders;A csv file "Happiness.csv" contains the data of a survey. Each record of the file contains the following data:
- Name of a country
- Population of the country
- Sample Size (Number of persons who participated in the survey in that country)
- Happy (Number of persons who accepted that they were Happy)
For example, a sample record of the file may be:
[‘Signiland’, 5673000, 5000, 3426]
Write the following Python functions to perform the specified operations on this file:
(I) Read all the data from the file in the form of a list and display all those records for which the population is more than 5000000.
(II) Count the number of records in the file.
A table, named STATIONERY, in ITEMDB database, has the following structure:
Field Type itemNo int(11) itemName varchar(15) price float qty int(11) Write the following Python function to perform the specified operation:
AddAndDisplay(): To input details of an item and store it in the table STATIONERY. The function should then retrieve and display all records from the STATIONERY table where the Price is greater than 120.
Assume the following for Python-Database connectivity:
Host: localhost, User: root, Password: Pencil
Surya is a manager working in a recruitment agency. He needs to manage the records of various candidates. For this, he wants the following information of each candidate to be stored:
- Candidate_ID – integer
- Candidate_Name – string
- Designation – string
- Experience – float
You, as a programmer of the company, have been assigned to do this job for Surya.
(I) Write a function to input the data of a candidate and append it in a binary file.
(II) Write a function to update the data of candidates whose experience is more than 10 years and change their designation to "Senior Manager".
(III) Write a function to read the data from the binary file and display the data of all those candidates who are not "Senior Manager".