Class 12 Computer Science Sumita Arora Exercise Solution of Ch 12 Simple Queries in SQL

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.

Q1. Write a query to display 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

TABLE : STUDENT

(i) Display the names of the students who are getting a grade ‘C’ in either GAME or SUPW.

Ans: SELECT NAME FROM STUDENT WHERE GRADE1 =’C’ OR GRADE2 = ‘C’;

(ii) Display the different games offered in the school.

Ans: SELECT DISTINCT GAME FROM STUDENT;

(iil) Display the SUPW taken up by the students, whose name starts with ‘A’.

Ans: 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

TABLE : SPORTS

(i) Display the names of the students who have grade ‘C’ in either Game1 or Game2 or both.

Ans: SELECT NAME FROM SPORTS WHERE GRADE1 =’C’ OR GRADE2 = ‘C’;

(ii) Display the names of the students who have same game for both Game1 and Game2.

Ans: SELECT NAME FROM STUDENT WHERE Game1 = Game2 ;

(iii) Display the games taken up by the students, whose name starts with ‘A’.

Ans: SELECT Game1, Game2 FROM STUDENT WHERE Name LIKE ‘A%’;

Class 12 Computer Science Sumita Arora Exercise Solution of Ch 12 Simple Queries in SQL