[Assignments] Simple Queries in SQL — Chapter 12 Assignments (Q/A)
Table of Contents
Sumita Arora Assignments for Class 12 Computer Science Chapter 12
These are MySQL Grade 12 practical questions. Though, Simple Queries in SQL doesn’t contain any type based questions, it contains an assignment which mostly contains practical questions only. Follow along for the most popular question in SQL for class 12 i.e.,
Write sql commands for the following on the basis of given table: student, student1 (Q10, Q14, Q15) (Page 2, Page 6, Page 7)
Authored by Sumita Arora, this chapter introduces learners to the basics of database management, focusing on simple SQL queries. These assignments offer a gentle initiation, gradually building students’ confidence in crafting and executing SQL queries.
Consider table Empl given in solved problems and answer the following questions.
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 8369 | SMITH | CLERK | 8902 | 18.12.90 | 800.00 | NULL | 20 |
| 8499 | ANYA | SALESMAN | 8698 | 20.2.91 | 1600.00 | 300.00 | 30 |
| 8521 | SETH | SALESMAN | 8698 | 22.2.91 | 1250.00 | 500.00 | 30 |
| 8566 | MAHADEVAN | MANAGER | 8839 | 2.4.91 | 2985.00 | NULL | 20 |
| 8654 | MOMIN | SALESMAN | 8698 | 28.9.91 | 1250.00 | 1400.00 | 30 |
| 8698 | BINA | MANAGER | 8839 | 1.5.91 | 2850.00 | NULL | 30 |
| 8882 | SHIVANSH | MANAGER | 8839 | 9.6.91 | 2450.00 | NULL | 10 |
| 8888 | SCOTT | ANALYST | 8566 | 9.12.92 | 3000.00 | NULL | 20 |
| 8839 | AMIR | PRESIDENT | NULL | 18.11.91 | 5000.00 | NULL | 10 |
| 8844 | KULDEEP | SALESMAN | 8698 | 8.9.91 | 1500.00 | 0.00 | 30 |
| 8886 | ANOOP | CLERK | 8888 | 12.1.93 | 1100.00 | NULL | 20 |
| 8900 | JATIN | CLERK | 8698 | 3.12.91 | 950.00 | NULL | 30 |
| 8902 | FAKIR | ANALYST | 8566 | 3.12.91 | 3000.00 | NULL | 20 |
| 8934 | MITA | CLERK | 8882 | 23.1.92 | 1300.00 | NULL | 10 |
1. Write a query to display EName and Sal of employees whose salary is greater than or equal to 2200 from table Empl.
SELECT ename, sal
FROM Empl
WHERE sal >= 2200;2. Write a query to display details of employees who are not getting commission from table Empl.
SELECT *
FROM Empl
WHERE comm IS NULL;3. Write a query to display employee name and salary of those employee who don’t have their salary in the range of 2500 to 4000.
SELECT ename, sal
FROM Empl
WHERE sal NOT BETWEEN 2500 AND 4000;4. Write a query to display the name, job title and salary of employee who do not have manager.
SELECT ename, job, sal
FROM Empl
WHERE mgr IS NULL;5. Write a query to display the name of employee whose name contains ‘A’ as third alphabet.
SELECT ename
FROM Empl
WHERE ename LIKE '__A%';6. Write a query to display the name of employee whose name contains ‘T’ as the last alphabet.
SELECT ename
FROM Empl
WHERE ename LIKE '%T';7. Write a query to display the name of employee whose name contains ‘M’ as first alphabet & ‘L’ as third alphabet.
SELECT ename
FROM Empl
WHERE ename LIKE 'M_L%';8. Write a query on the customers table whose output will exclude all customers with a rating <=100, unless they are located in Shimla.
SELECT *
FROM customers
WHERE rating > 100 OR city = 'Shimla';9. Write a query that selects all orders (Order table) except those with zeros or NULLs in the amt field.
SELECT *
FROM Order
WHERE amt IS NOT NULL AND amt != 0;