Computer Science
Consider the tables PRODUCT and BRAND given below:
Table: PRODUCT
| PCode | PName | UPrice | Rating | BID |
|---|---|---|---|---|
| P01 | Shampoo | 120 | 6 | M03 |
| P02 | Toothpaste | 54 | 8 | M02 |
| P03 | Soap | 25 | 7 | M03 |
| P04 | Toothpaste | 65 | 4 | M04 |
| P05 | Soap | 38 | 5 | M05 |
| P06 | Shampoo | 245 | 6 | M05 |
Table: BRAND
| BID | BName |
|---|---|
| M02 | Dant Kanti |
| M03 | Medimix |
| M04 | Pepsodent |
| M05 | Dove |
Write SQL queries for the following:
(i) Display product name and brand name from the tables PRODUCT and BRAND.
(ii) Display the structure of the table PRODUCT.
(iii) Display the average rating of Medimix and Dove brands.
(iv) Display the name, price, and rating of products in descending order of rating.
Answer
(i)
SELECT PName, BName FROM PRODUCT P,
BRAND B WHERE P.BID = B.BID;
(ii)
DESC PRODUCT;
(iii)
SELECT BName, AVG(Rating) FROM PRODUCT
P, BRAND B
WHERE P.BID = B.BID
GROUP BY BName
HAVING BName = 'Medimix' OR
BName = 'Dove';
(iv)
SELECT PName, UPrice, Rating
FROM PRODUCT
ORDER BY Rating DESC;
Related Questions
Consider the table Personal given below:
Table: Personal
P_ID Name Desig Salary Allowance P01 Rohit Manager 89000 4800 P02 Kashish Clerk NULL 1600 P03 Mahesh Superviser 48000 NULL P04 Salil Clerk 31000 1900 P05 Ravina Superviser NULL 2100 Based on the given table, write SQL queries for the following:
(i) Increase the salary by 5% of personals whose allowance is known.
(ii) Display Name and Total Salary (sum of Salary and Allowance) of all personals. The column heading 'Total Salary' should also be displayed.
(iii) Delete the record of personals who have salary greater than 25000.
A list, NList contains following record as list elements:
[City, Country, distance from Delhi]
Each of these records are nested together to form a nested list. Write the following user defined functions in Python to perform the specified operations on the stack named travel.
(i) Push_element(NList): It takes the nested list as an argument and pushes a list object containing name of the city and country, which are not in India and distance is less than 3500 km from Delhi.
(ii) Pop_element(): It pops the objects from the stack and displays them. Also, the function should display “Stack Empty” when there are no elements in the stack.
For example: If the nested list contains the following data:
NList=[["New York", "U.S.A.", 11734], ["Naypyidaw", "Myanmar", 3219], ["Dubai", "UAE", 2194], ["London", "England", 6693], ["Gangtok", "India", 1580], ["Columbo", "Sri Lanka", 3405]]
The stack should contain:
['Naypyidaw', 'Myanmar'], ['Dubai', 'UAE'], ['Columbo', 'Sri Lanka']
The output should be:
['Columbo', 'Sri Lanka'] ['Dubai', 'UAE'] ['Naypyidaw', 'Myanmar'] Stack EmptyVedansh is a Python programmer working in a school. For the Annual Sports Event, he has created a csv file named Result.csv, to store the results of students in different sports events. The structure of Result.csv is :
[St_Id, St_Name, Game_Name, Result]
Where
St_Id is Student ID (integer)
ST_name is Student Name (string)
Game_Name is name of game in which student is participating(string)
Result is result of the game whose value can be either 'Won', 'Lost' or 'Tie'.For efficiently maintaining data of the event, Vedansh wants to write the following user defined functions:
Accept() – to accept a record from the user and add it to the file Result.csv. The column headings should also be added on top of the csv file.
wonCount() – to count the number of students who have won any event.
As a Python expert, help him complete the task.
Meticulous EduServe is an educational organization. It is planning to setup its India campus at Chennai with its head office at Delhi. The Chennai campus has 4 main buildings – ADMIN, ENGINEERING, BUSINESS and MEDIA.
Block to Block distances (in Mtrs.)
From To Distance ADMIN ENGINEERING 55 m ADMIN BUSINESS 90 m ADMIN MEDIA 50 m ENGINEERING BUSINESS 55 m ENGINEERING MEDIA 50 m BUSINESS MEDIA 45 m DELHI HEAD OFFICE CHENNAI CAMPUS 2175 km Number of computers in each of the blocks/Center is as follows:
block/center Number of computers ADMIN 110 ENGINEERING 75 BUSINESS 40 MEDIA 12 DELHI HEAD 20 (a) Suggest and draw the cable layout to efficiently connect various blocks of buildings within the CHENNAI campus for connecting the digital devices.
(b) Which network device will be used to connect computers in each block to form a local area network?
(c) Which block, in Chennai Campus should be made the server? Justify your answer.
(d) Which fast and very effective wireless transmission medium should preferably be used to connect the head office at DELHI with the campus in CHENNAI?
(e) Is there a requirement of a repeater in the given cable layout? Why/ Why not?