Informatics Practices
Consider the following table:
Table: ITEM
| Itemno | Iname | Price | Quantity |
|---|---|---|---|
| 101 | Soap | 50 | 100 |
| 102 | Powder | 100 | 50 |
| 103 | Facecream | 150 | 25 |
| 104 | Pen | 50 | 200 |
| 105 | Soapbox | 20 | 100 |
Write queries based on the table ITEM:
(a) Display the information of all the items.
(b) Display item name and price value.
(c) Display soap information.
(d) Display the item information whose name starts with letter 's'.
(e) Display a report with item number, item name and total price (total price = price * quantity).
(f) SELECT DISTINCT PRICE FROM ITEM;
(g) SELECT COUNT(DISTINCT Price) FROM ITEM;
Answer
(a)
SELECT * FROM ITEM;
Output
+--------+-----------+-------+----------+
| ITEMNO | INAME | PRICE | QUANTITY |
+--------+-----------+-------+----------+
| 101 | SOAP | 50 | 100 |
| 102 | POWDER | 100 | 50 |
| 103 | FACECREAM | 150 | 25 |
| 104 | PEN | 50 | 200 |
| 105 | SOAPBOX | 20 | 100 |
+--------+-----------+-------+----------+
(b)
SELECT INAME, PRICE
FROM ITEM;
Output
+-----------+-------+
| INAME | PRICE |
+-----------+-------+
| SOAP | 50 |
| POWDER | 100 |
| FACECREAM | 150 |
| PEN | 50 |
| SOAPBOX | 20 |
+-----------+-------+
(c)
SELECT * FROM ITEM
WHERE INAME = 'SOAP';
Output
+--------+-------+-------+----------+
| ITEMNO | INAME | PRICE | QUANTITY |
+--------+-------+-------+----------+
| 101 | SOAP | 50 | 100 |
+--------+-------+-------+----------+
(d)
SELECT * FROM ITEM
WHERE INAME LIKE 'S%';
Output
+--------+---------+-------+----------+
| ITEMNO | INAME | PRICE | QUANTITY |
+--------+---------+-------+----------+
| 101 | SOAP | 50 | 100 |
| 105 | SOAPBOX | 20 | 100 |
+--------+---------+-------+----------+
(e)
SELECT ITEMNO, INAME, (PRICE * QUANTITY) AS Total_Price
FROM ITEM;
Output
+--------+-----------+-------------+
| ITEMNO | INAME | Total_Price |
+--------+-----------+-------------+
| 101 | SOAP | 5000 |
| 102 | POWDER | 5000 |
| 103 | FACECREAM | 3750 |
| 104 | PEN | 10000 |
| 105 | SOAPBOX | 2000 |
+--------+-----------+-------------+
(f)
Output
+-------+
| PRICE |
+-------+
| 50 |
| 100 |
| 150 |
| 20 |
+-------+
(g)
Output
+-----------------------+
| COUNT(DISTINCT Price) |
+-----------------------+
| 4 |
+-----------------------+
Related Questions
Consider the following structure of TEACHER and STUDENT table:
Table: TEACHER
TeacherID TName City Subject Qualification Designation Pay Table: STUDENT
StdID Name FName Stream TeacherID Write the SQL commands to get the following:
(a) Show the name of students enrolled in Science stream.
(b) Count the number of students in Commerce stream.
(c) Count the number of teachers in each designation.
(d) Display the maximum pay of teacher who is teaching English.
(e) Display the names of students who are taught by "Anand Mathur".
(f) Display the names and designations of teachers who are teaching a student named "Amit".
(g) Find out the name of the teacher who is getting the highest pay.
(h) Find out the cities of teachers who are teaching Maths.
(i) Find out the name of teacher who is getting the lowest salary among PGTs.
(j) Display the list of students who are taught by PGTs only.
Consider the following table and answer the questions that follow:
Table: TEACHER
ID Name Department Hiredate Category Gender Salary 1 Tanya Nanda SocialStudies 1994-03-17 TGT F 25000 2 Saurabh Sharma Art 1990-02-12 PRT M 20000 3 Nandita Arora English 1980-05-16 PGT F 30000 4 James Jacob English 1989-10-16 TGT M 25000 5 Jaspreet Kaur Hindi 1990-08-01 PRT F 22000 6 Disha Sehgal Math 1980-03-17 PRT F 21000 8 SonaliMukherje Math 1980-11-17 TGT F 24500 Write the command/output for the following:
(a) To display all information about the teacher of PGT category.
(b) To list the names of female teachers of Hindi department.
(c) To list names, departments and date of hiring of all the teachers in ascending order of date of joining.
(d) To count the number of teachers in English Department.
(e) Display the department and hire date of all the female teachers whose salary is more than 25000.
(f) Display the list of teachers whose name starts with J.
(g) SELECT COUNT(*) FROM TEACHER WHERE Category = 'PGT';
(h) SELECT AVG(Salary) FROM TEACHER GROUP BY Gender;
Write SQL commands and the output for the following queries:
Table: SPORTS
StudentNo Class Name Game1 Grade1 Game2 Grade2 10 7 Sameer Cricket B Swimming A 11 8 Sujit Tennis A Skating C 12 7 Kamal Swimming B Football B 13 7 Veena Tennis C Tennis A 14 9 Archana Basketball A Cricket A 15 10 Arpit Cricket A Athletics C (a) Display the names of the students who have grade 'A' in either Game1 or Game2 or both.
(b) Display the number of students having game 'Cricket'.
(c) Display the names of students who have the same game for both Game1 and Game2.
(d) Display the games taken by the students whose name starts with 'A'.
(e) Give the output of the following sql statements:
- SELECT COUNT(*) FROM SPORTS;
- SELECT DISTINCT CLASS FROM SPORTS;
- SELECT MAX(Class) FROM SPORTS;
- SELECT COUNT(*) FROM SPORTS GROUP BY Game1;
Write the output produced by the following SQL commands:
(a) SELECT POW(2,3);
(b) SELECT ROUND(123.2345, 2) ,ROUND(342.9234,-1);
(c) SELECT LENGTH("Informatics Practices");
(d) SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), MONTHNAME("1979/11/26"), DAY("1979/11/26");
(e) SELECT LEFT("INDIA", 3), RIGHT("Computer Science", 4);
(f) SELECT MID("Informatics", 3,4), SUBSTR("Practices", 3);
(g) SELECT CONCAT("You Scored", LENGTH("123") , "rank");
(h) SELECT ABS(-67.89);
(i) SELECT SQRT(625) + ROUND(1234.89, -3);
(j) SELECT MOD(56, 8);