Informatics Practices
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 |
Related Questions
Consider the given table Faculty and answer the questions that follow:
Table: FACULTY
F_ID F_Name L_Name Hire_date Salary 102 Amit Mishra 1998-10-12 10000 103 Nitin Vyas 1994-12-24 8000 104 Rakshit Soni 2001-05-18 14000 105 Rashmi Malhotra 2004-09-11 11000 106 Sulekha Srivastava 2006-06-05 10000 (a) To display the details of those Faculty members whose salary is higher than 12000.
(b) To display the details of Faculty members whose salary is in the range of 8000 to 12000 (both values included).
(c) Count the number of different ids from faculty.
(d) Count the number of faculty members getting salary as 10000.
(e) Display details of those faculty members whose names start with S.
(f) Display all records in descending order of Hire date.
(g) Find the maximum and the minimum salary.
(h) Select CONCAT(F_Name, L_Name) from FACULTY;
(i) Select Month(Hire_date) from FACULTY;
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.
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.
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";