Class 12 Computer Science Sumita Arora Exercise Solution

Ch 12 Simple Queries in SQL

Consider table Empl given in solved problems and answer the following questions.

Table: Empl

empno	Ename	Job	mgr	Hiredate	sal	comm	deptno
8369	SMITH	CLERK	8902	1990-12-18	800.00	NULL	20
8499	ANYA	SALESMAN	8698	1991-02-20	1600.00	300.00	30
8521	SETH	SALESMAN	8698	1991-02-22	1250.00	500.00	30
8566	MAHADEVAN	MANAGER	8839	1991-04-02	2985.00	NULL	20
8654	MOMIN	SALESMAN	8698	1991-09-28	1250.00	1400.00	30
8698	BINA	MANAGER	8839	1991-05-01	2850.00	NULL	30
8882	SHIAVNSH	MANAGER	8839	1991-06-09	2450.00	NULL	10
8888	SCOTT	ANALYST	8566	1992-12-09	3000.00	NULL	20
8839	AMIR	PRESIDENT	NULL	1991-11-18	5000.00	NULL	10
8844	KULDEEP	SALESMAN	8698	1991-09-08	1500.00	0.00	30
8886	ANOOP	CLERK	8888	1993-01-12	1100.00	NULL	20
8900	JATIN	CLERK	8698	1991-12-03	950.00	NULL	30
8902	FAKIR	ANALYST	8566	1991-12-03	3000.00	NULL	20
8934	MITA	CLERK	8882	1992-01-23	1300.00	NULL	10

Q1. Write a query to display the EName and Sal of employees whose salary is greater than or equal to 2200 from table Empl.

Ans: SELECT EName, Sal FROM Empl WHERE sal >= 2200;

Q2. Write a query to display details of employees who are not getting commission from table Empl.

Ans: SELECT * FROM EMPL WHREE COMM IS NULL;

Q3. Write a query to display employee name and salary of those employee who don’t have there salary in the range of 2500 to 4000.

Ans: SELECT ENAME, SAL FROM EMPL WHERE SAL NOT BETWEEN 2500 AND 4000;

Q4. Write a query to display the name, job title and salary of employee who do not have manager.

Ans: SELECT ENAME, JOB, SAL FROM EMPL WHERE MGR IS NULL;

Q5. Write a query to display the name of employee whose name contains ‘A’ as third alphabet.

Ans: SELECT ENAME FROM EMPL WHERE ENAME LIKE ‘_ _ A%’;

Q6. Write a query to display the name of employee whose name contains ‘T’ as the last alphabet.

Ans: SELECT ENAME FROM EMPL WHERE ENAME LIKE ‘%T’;

Q7. Write a query to display the name of employee whose name contains ‘M’ as first alphabet ‘L’ as third alphabet.

Ans: SELECT ENAME FROM EMPL WHERE ENAME LIKE ‘M_L%’;

Q8. Write a query on the customers table whose output will exclude all customers with a rating <= 100, unless they are located in Shimla.

Ans: SELECT * FROM CUSTOMERS WHERE RATING <=100 OR CITY = ‘SHIMLA’;

Q9. Write a query that selects all orders (Order table) except those with zeros or NULLs in the amt field.

Ans: SELECT * FROM ORDER WHERE AMT <> 0 OR AMT IS NOT NULL;

Q10. Write SQL commands for the following on the basis of given table STUDENT:

Table : STUDENT

StudentNo	Class	Name	GAME	Grade1	SUPW	Grade2
10 11 12 13 14 15	7 8 7 7 9 10	Sameer Sujit Kamal Veena Archana Arpit	Cricket Tennis Swimming Tennis Basket Ball Cricket	B A B C A A	Photography Gardening Photography Cooking Literature Gardening	A C B A A C

• (i) Display the names of the students who are getting a grade ‘C’ in either GAME or SUPW.
• (ii) Display the different games offered in the school.
• (iil) Display the SUPW taken up by the students, whose name starts with ‘A’.

Ans:

• (i) SELECT NAME FROM STUDENT WHERE GRADE1 =’C’ OR GRADE2 = ‘C’;
• (ii) SELECT DISTINCT GAME FROM STUDENT;
• (iii) SELECT SUPW, NAME FROM STUDENT WHERE NAME LIKE ‘A%’;

Q11. Write SQL commands for the following on the basis of given table SPORTS :

Table : SPORTS

StudentNo	Class	Name	Game1	Grade1	Game2	Grade2
10 11 12 13 14 15	7 8 7 7 9 10	Sameer Sujit Kamal Veena Archana Arpit	Cricket Tennis Swimming Tennis Basket Ball Cricket	B A B C A A	Swimming Skating Football Tennis Cricket Atheletics	A C B A A C

• (i) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.
• (ii) Display the names of the students who have same game for both Game1 and Game2.
• (iii) Display the games taken up by the students, whose name starts with ‘A’.

Ans:

• (i) SELECT NAME FROM SPORTS WHERE GRADE1 =’C’ OR GRADE2 = ‘C’;
• (ii) SELECT NAME FROM STUDENT WHERE Game1 = Game2 ;
• (iii) SELECT Game1, Game2 FROM STUDENT WHERE Name LIKE ‘A%’;

Question 12.
Write SQL commands for the following on the basis of the given table CLUB :

Table: CLUB

COACH_ID	COACHNAME	AGE	SPORTS	DATOFAPP	PAY	SEX
1.	KUKREJA	35	KARATE	27/03/1996	1000	M
2.	RAVINA	34	KARATE	20/01/1998	1200	F
3.	KARAN	34	SQUASH	19/02/1998	2000	M
4.	TARUN	33	BASKETBALL	01/01/1998	1500	M
5.	ZUBIN	36	SWIMMING	12/01/1998	750	M
6.	KETAKI	36	SWIMMING	24/02/1998	800	F
7.	ANKITA	39	SQUASH	20/02/1998	2200	F
8.	ZAREEN	37	KARATE	22/02/1998	1100	F
9.	KUSH	41	SWIMMING	13/01/1998	900	M
10.	SHAILYA	37	BASKETBALL	19/02/1998	1700	M

• (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.

Ans:

• (a) SELECT * FROM CLUB WHERE SPORTS = ‘SWIMMING’ ;
• (b) SELECT COACHNAME, DATEOFAPP FROM CLUB ORDER BY DATEOFAPP DESC ;
• (c) SELECT COACHNAME, PAY, AGE, PAY *0.15 AS “BONUS” FROM CLUB ;

Question 13.
Write SQL commands for the following on the basis of the given table STUDENT1 :

Table: STUDENT1

No.	Name	Stipend	Stream	AvgMark	Grade	Class
1	Karan	400.00	Medical	78.5	B	12B
2	Divakar	450.00	Commerce	89.2	A	11C
3	Divya	300.00	Commerce	68.6	C	12C
4	Arun	350.00	Humanities	73.1	B	12C
5	Sabina	500.00	Nonmedical	90.6	A	11A
6	John	400.00	Medical	75.4	B	12B
7	Robert	250.00	Humanities	64.4	C	11A
8	Rubina	450.00	Nonmedical	88.5	A	12A
9	Vikas	500.00	Nonmedical	92.0	A	12A
10	Mohan	300.00	Commerce	67.5	C	12C

• (a) Select all the Nonmedical stream students from STUDENTI.
• (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.
• (d) Display a report, listing Name, Stipend, Stream and Amount of stipend received in a year assuming that stipend is paid every month.

Answer:

• (a) SELECT * FROM STUDENT1 WHERE STREAM = “NONMEDICAL” ;
• (b) SELECT NAME FROM STUDENT1 WHERE CLASS LIKE ‘12%’ ORDER BY STIPEND
• (c) SELECT * FROM STUDENT1 ORDER BY AVGMARK DESC;
• (d) SELECT NAME, STIPEND, STREAM, STIPEND * 12 AS “STIPEND AMOUNT IN YEAR” FROM STUDENT1;

More questions are on the way. coming soon.

---

• Class 12 Computer Science 083 Ch 2 Python Revision Tour II (Type – C) Sumita Arora Book Exercise Solution
• Class 12 Computer Science 083 Ch 2 Python Revision Tour II (Type – B) Sumita Arora Book Exercise Solution
• Class 12 Computer Science 083 Chapter 5 – File Handling in Python Sumita Arora Book Exercise Solution
• Chapter 3 Working with Functions | Sumita Arora Computer Science with Python CBSE Class 12 book
• Chapter 2 Python Revision Tour II | Sumita Arora Computer Science with Python CBSE Class 12 book
• Chapter 1 Python Revision Tour I | Sumita Arora Computer Science with Python CBSE Class 12 book
• Class 12 Computer Science Sumita Arora Exercise Solution of Ch 12 Simple Queries in SQL
• Class 12 Computer Science Solution Check Point 12.1 Simple Queries in SQL

---