JSS PUBLIC SCHOOL
HSR LAYOUT, BENGALURU – 102.
MY SQL PRACTICE QUESTIONS - SOLUTIONS
CLASS: XII
SUBJECT: COMPUTER SCIENCE (083)
1. Consider the following table named "GYM" with details about fitness items
being sold in the store. Write command of SQL for (i) to (iv).
TABLE: GYM
(i) To display the names of all the items whose name starts with "A".
(ii) To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore.
(iii) To change the Brandname to "Fit Trend India" of the item, whose ICODE as "G101".
(iv) Add a new row for new item in GYM with the details :
"G107", "Vibro exerciser", 21000, "GTCFitness"
Answer
(i)
SELECT INAME
FROM GYM
WHERE INAME LIKE 'A%';
Output
+-------------------+
| INAME |
+-------------------+
| Aquafit Hand Grip |
+-------------------+
(ii)
SELECT ICODE, INAME
FROM GYM
WHERE BRANDNAME = "Reliable" OR BRANDNAME = "Coscore";
Output
+-------+----------------------+
| ICODE | INAME |
+-------+----------------------+
| G102 | Aquafit Hand Grip |
| G104 | Protoner Extreme Gym |
+-------+----------------------+
(iii)
UPDATE GYM
SET BRANDNAME = 'Fit Trend India'
WHERE ICODE = 'G101';
(iv)
INSERT INTO GYM
VALUES("G107", "Vibro exerciser", 21000, "GTCFitness");
2. Consider the following table FITNESS with details about fitness products
being sold in the store. Write command of SQL for (i) to (iv).
Table : FITNESS
(i) To display the names of all the products with price more than 20000.
(ii) To display the names of all products by the manufacturer "Aone".
(iii) To change the price data of all the products by applying 25% discount reduction.
(iv) To add a new row for product with the details :
"P7", "Vibro Exerciser", 28000, "Aone".
Answer
(i)
SELECT PNAME FROM FITNESS WHERE PRICE > 20000;
Output
+-----------+
| PNAME |
+-----------+
| Treadmill |
| Multi Gym |
+-----------+
(ii)
SELECT PNAME
FROM FITNESS
WHERE MANUFACTURER = "Aone";
Output
+-------+
| PNAME |
+-------+
| Bike |
+-------+
(iii)
UPDATE FITNESS
SET PRICE = PRICE * 0.75;
(iv)
INSERT INTO FITNESS
VALUES("P7", "Vibro Exerciser", 28000, "Aone");
3. Write SQL commands for the following on the basis of given table CLUB.
Table : CLUB
(a) To show all information about the swimming coaches in the club.
(b) To list names of all coaches with their date of appointment (DATOFAPP) in
descending order.
(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all the
coaches.
Answer
(a) SELECT * FROM CLUB WHERE SPORTS = 'SWIMMING' ;
Output
(b) SELECT COACHNAME, DATOFAPP FROM CLUB ORDER BY DATOFAPP DESC ;
Output
(c) SELECT COACHNAME, PAY, AGE, (PAY * 0.15) AS BONUS FROM CLUB ;
Output
4. Write SQL commands for the following on the basis of given table
STUDENT1.
Table : STUDENT1
(a) Select all the Nonmedical stream students from STUDENT1.
(b) List the names of those students who are in class 12 sorted by Stipend.
(c) List all students sorted by AvgMark in descending order.
Answer
(a) SELECT * FROM STUDENT1 WHERE Stream = 'Nonmedical' ;
Output:
(b) SELECT Name FROM STUDENT1 WHERE Class LIKE '12%' ORDER BY Stipend;
Output:
(c) SELECT * FROM STUDENT1 ORDER BY AvgMark DESC ;
Output
5. Kabir has created the following table named exam :
Help him in writing SQL queries to perform the following tasks :
(i) Insert a new record in the table having following values : [6, 'Khushi', 'CS',
85]
(ii) To change the value "IP" to "Informatics Practices" in Subject column.
(iii) To remove the records of those students whose marks are less than 30.
(iv) To add a new column Grade of suitable datatype.
(v) To display records of "Informatics Practices" subject.
Answer:
(i) INSERT INTO exam(RegNo, Name, Subject, Marks) VALUES(6, 'Khushi', 'CS', 85);
(ii) UPDATE exam SET Subject = 'Informatics Practices' WHERE Subject = 'IP';
(iii) DELETE FROM EXAM WHERE MARKS < 30 ;
(iv) ALTER TABLE EXAM ADD COLUMN (Grade VARCHAR(1));
(v) SELECT * FROM EXAM WHERE Subject = "Informatics Practices";
6. Increase salary of employee records by 10% (table employee).
Answer: Table employee
UPDATE employee SET Salary = (Salary * 0.1) + Salary ;
Output: To view all the details (all columns and rows) of the "employee" table the below
query is executed : SELECT * FROM employee ;
7. The Doc_name Column of a table Hospital is given below :
Based on the information, find the output of the following queries :
(i) SELECT doc_name FROM HOSPITAL WHERE Doc_name like "%v";
(ii) SELECT doc_name FROM HOSPITAL WHERE doc_name like "%e%";
Answer: (i) SELECT doc_name FROM HOSPITAL WHERE Doc_name like "%v";
Output
Explanation: The query SELECT doc_name FROM HOSPITAL WHERE Doc_name like
"%v"; selects the doc_name from the HOSPITAL table where the Doc_name column ends
with the letter "v" using the LIKE operator with the "%" wildcard. This pattern matches
any string where "v" is the last character, and any characters can precede it.
(ii) SELECT doc_name FROM HOSPITAL WHERE doc_name like "%e%";
Output:
Explanation: The query SELECT doc_name FROM HOSPITAL WHERE doc_name like "%e
%"; selects the doc_name from the HOSPITAL table where the doc_name column
contains the letter "e". This is achieved using the LIKE operator with the "%" wildcard
before and after the letter "e", which matches any sequence of characters that have "e"
in them.
8. Consider the following tables.
TABLE: TEACHER
TABLE: ADMIN
Write SQL statement
(i) To display the name of the Teacher and their Designation?
Answer: SELECT TNAME, DESIGNATION FROM TEACHER T, ADMIN A WHERE [Link] = [Link];
(ii) To display the Name and Subjects of all Female Teachers?
Answer: SELECT TNAME, SUBJECT FROM TEACHER T, ADMIN A WHERE [Link] = [Link] AND
GENDER = 'FEMALE' ;
(iii) To display the all details of all teachers including Gender and Designation.
Answer: SELECT [Link], TNAME, SUBJECT, DOJ, PERIOD, EXPERIENCE, [Link],
[Link] FROM TEACHER T, ADMIN A WHERE [Link] = [Link] ;
(iv) To display details of all HOD.
Answer: SELECT * FROM TEACHER NATURAL JOIN ADMIN WHERE DESIGNATION = 'HOD' ;
Il Method:
SELECT T .* , [Link], [Link] FROM TEACHER T, ADMIN A WHERE [Link] = [Link]
AND DESIGNATION = 'HOD';