Informatics Practices
Consider the table Product shown below:
Table: PRODUCT
| P_ID | ProductName | Manufacturer | Price |
|---|---|---|---|
| P001 | Moisturizer | XYZ | 40 |
| P002 | Sanitizer | LAC | 35 |
| P003 | Bath Soap | COP | 25 |
| P004 | Shampoo | TAP | 95 |
| P005 | Lens Solution | COP | 350 |
Write the commands in SQL queries for the following:
(a) To display the details of product whose price is in the range of 40 and 120 (both values included).
(b) To increase the price of all the products by 20.
Answer
(a)
SELECT *
FROM PRODUCT
WHERE PRICE BETWEEN 40 AND 120;
Output
+------+-------------+--------------+-------+
| P_ID | ProductName | Manufacturer | Price |
+------+-------------+--------------+-------+
| P001 | MOISTURISER | XYZ | 40 |
| P004 | SHAMPOO | TAP | 95 |
+------+-------------+--------------+-------+
(b)
UPDATE PRODUCT
SET PRICE = PRICE + 20;
Related Questions
Consider the following EMP and DEPT tables:
Table: EMP
EmpNo EmpName City Designation DOJ Sal Comm DeptID 8369 SMITH Mumbai CLERK 1990-12-18 800.00 NULL 20 8499 ANYA Varanasi SALESMAN 1991-02-20 1600.00 300.00 30 8521 SETH Jaipur SALESMAN 1991-02-22 1250.00 500.00 30 8566 MAHADEVAN Delhi MANAGER 1991-04-02 2985.00 NULL 20 Table: DEPT
DeptID DeptName MgrID Location 10 SALES 8566 Mumbai 20 PERSONNEL 9698 Delhi 30 ACCOUNTS 4578 Delhi 40 RESEARCH 8839 Bengaluru Write the SQL command to get the following:
(a) Show the minimum, maximum and average salary of managers.
(b) Count the number of clerks in the organization.
(c) Display the designation-wise list of employees with name, salary and date of joining.
(d) Count the number of employees who are not getting commission.
(e) Show the average salary for all departments with more than 5 working people.
(f) List the count of employees grouped by DeptID.
(g) Display the maximum salary of employees in each department.
(h) Display the name of employees along with their designation and department name.
(i) Count the number of employees working in ACCOUNTS department.
Write a MySQL command for creating a table PAYMENT whose structure is given below:
Table: PAYMENT
Field Name Data type Size Constraint Loan_number Integer 4 Primary key Payment_number Varchar 3 Payment_date Date Payment_amount Integer 8 Not Null Consider the table RESULT given below. Write commands in MySQL for (a) to (d) and output for (e) to (g):
Table: RESULT
No Name Stipend Subject Average Division 1 Sharon 400 English 38 THIRD 2 Amal 680 Mathematics 72 FIRST 3 Vedant 500 Accounts 67 FIRST 4 Shakeer 200 Informatics 55 SECOND 5 Anandha 400 History 85 FIRST 6 Upasna 550 Geography 45 THIRD (a) To list the names of those students who have obtained Division as FIRST in the ascending order of NAME.
(b) To display a report listing Name, Subject and Annual Stipend received assuming that the Stipend column has monthly stipend.
(c) To count the number of students who have either Accounts or Informatics as subject.
(d) To insert a new row in the table RESULT: 7, "Mohan", 500, "English", 73, "Second"
(e) SELECT AVG(STIPEND) FROM RESULT WHERE DIVISION = "THIRD";
(f) SELECT COUNT(DISTINCT Subject) FROM RESULT;
(g) SELECT MIN(Average) FROM RESULT WHERE Subject = "English";
Write the SQL query commands based on the following table:
Table: SCHOOLBUS
Rtno Area_Covered Capacity NoOfStudents Distance Transporter Charges 1 Vasant Kunj 100 120 10 Shivam Travels 100000 2 Hauz Khas 80 80 10 Anand Travels 85000 3 Pitampura 60 55 30 Anand Travels 60000 4 Rohini 100 90 35 Anand Travels 100000 5 Yamuna Vihar 50 60 20 Bhalla Co. 55000 6 Krishna Nagar 70 80 30 Yadav Co. 80000 7 Vasundhra 100 110 20 Yadav Co. 100000 8 Paschim Vihar 40 40 20 Speed Travels 55000 9 Saket 120 120 10 Speed Travels 100000 10 Janak Puri 100 100 20 Kisan Tours 95000 (a) To show all information of students where capacity is more than the number of students in order of rtno.
(b) To show area_covered for buses covering more than 20 km, but charge is less than 80000.
(c) To show transporter-wise total no. of students travelling.
(d) To show Rtno, Area_covered and Average cost per student for all routes where average cost per student is — Charges/NoOfStudents
(e) Add a new record with the following data:
(11, "Moti Bagh", 35,32,10, "Kisan Tours", 35000)(f) Give the output considering the original relation as given below:
- SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";
- SELECT MIN(NoOfStudents) FROM SCHOOLBUS;
- SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";
- SELECT DISTINCT Transporter FROM SCHOOLBUS;