Question 1

Question 1Assertion. There is a difference between a field being empty or storing NULL value in a field.Reason. The NULL value is a legal way of signifying that no value exists in the field.

Accepted Answer

(a)Both Assertion and Reason are true and Reason is the correct explanation of Assertion.ExplanationThere is a difference between a field being empty or storing NULL value in a field. An empty field means that the field contains a value, but that value is an empty string or a space, depending on the data type. In contrast, if a column in a row has no value, then column is said to contain a NULL. Hence, the NULL value is a legal way of signifying that no value exists in the field.

Question 2

Question 2Assertion. The ALL and DISTINCT clauses of a SELECT query are related.Reason. The ALL clause is the opposite of the DISTINCT clause of a SELECT Query.

Accepted Answer

(a)Both Assertion and Reason are true and Reason is the correct explanation of Assertion.ExplanationThe ALL and DISTINCT clauses in a SELECT query are related in that they both affect how duplicate rows are handled in the result. The DISTINCT keyword in SQL is used to eliminate duplicate rows from the results of a query. The ALL keyword in SQL is used to retain the duplicate rows from the results of a query. Therefore, the ALL clause is the opposite of the DISTINCT clause of a SELECT Query.

Question 3

Question 3Assertion. The WHERE and HAVING clauses are used for the same thing in a SELECT query.Reason. Both WHERE and HAVING clauses are used to specify conditions at different levels.

Accepted Answer

(d)Assertion is false but Reason is true.ExplanationThe WHERE and HAVING clauses are not used for the same thing in a SELECT query. While both WHERE and HAVING clauses are used to specify conditions in a SELECT query, they operate at different levels. The WHERE clause filters rows based on conditions applied to individual rows before grouping, while the HAVING clause filters groups based on conditions applied to the result of aggregation functions after grouping.

Question 4

Question 4Assertion. Both WHERE and HAVING clauses are used to specify conditions.Reason. The WHERE and HAVING clauses are interchangeable.

Accepted Answer

(b)Both Assertion and Reason are true but Reason is not the correct explanation of Assertion.ExplanationBoth WHERE and HAVING clauses are used to specify conditions in a SELECT query, they operate at different levels. The WHERE clause filters rows based on conditions applied to individual rows before grouping, while the HAVING clause filters groups based on conditions applied to the result of aggregation functions after grouping. Hence, they are not interchangeable.

Question 5

Question 5Assertion. DDL and DML are not the same.Reason. DDL and DML are two subcategories of SQL where DDL creates the objects and DML manipulates the data.

Accepted Answer

(a)Both Assertion and Reason are true and Reason is the correct explanation of Assertion.ExplanationDDL and DML commands are two different commands of SQL. Data definition language (DDL) commands in SQL are used to define a database, including creating, altering, and dropping tables whereas Data manipulation language (DML) commands are used to retrieve, insert, update, and delete data in a database.

Question 6

Question 6Assertion. DDL and DML both are part of SQL.Reason. Both DDL and DML are interchangeable.

Accepted Answer

(c)Assertion is true but Reason is false.ExplanationDDL and DML are two subcategories of SQL. Data definition language (DDL) commands in SQL are used to define a database, including creating, altering, and dropping tables whereas Data manipulation language (DML) statements are used to retrieve, insert, update, and delete data in a database. Hence, DDL and DML are two different commands and are not interchangeable.

Question 7

Question 7Assertion. Both BETWEEN and IN operators can choose from a list of values.Reason. The value ranges and a list of values are interpreted in the same way in SQL.

Accepted Answer

(c)Assertion is true but Reason is false.ExplanationBoth BETWEEN and IN operators can select values from a list. The BETWEEN operator defines a range of values into which column values must fall to make the condition true. This range includes both lower and upper values. In contrast, the IN operator is used to specify a list of values. It selects values that match any value in the given list of values. Therefore, while both operators involve selecting values from a list, they operate differently in SQL, distinguishing between value ranges and specific value lists.

Question 8

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

Accepted Answer

SELECT ENAME, SAL  
FROM empl   
WHERE SAL >= 2200;Output+-----------+------+
| ENAME     | SAL  |
+-----------+------+
| MAHADEVAN | 2985 |
| BINA      | 2850 |
| AMIR      | 5000 |
| SHIAVNSH  | 2450 |
| SCOTT     | 3000 |
| FAKIR     | 3000 |
+-----------+------+

Question 9

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

Accepted Answer

SELECT *
FROM empl
WHERE COMM IS NULL OR COMM = 0;Output+-------+-----------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME     | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+-----------+-----------+------+------------+------+------+--------+
|  8369 | SMITH     | CLERK     | 8902 | 1990-12-18 |  800 | NULL |     20 |
|  8566 | MAHADEVAN | MANAGER   | 8839 | 1991-04-02 | 2985 | NULL |     20 |
|  8698 | BINA      | MANAGER   | 8839 | 1991-05-01 | 2850 | NULL |     30 |
|  8839 | AMIR      | PRESIDENT | NULL | 1991-11-18 | 5000 | NULL |     10 |
|  8844 | KULDEEP   | SALESMAN  | 8698 | 1991-09-08 | 1500 |    0 |     30 |
|  8882 | SHIAVNSH  | MANAGER   | 8839 | 1991-06-09 | 2450 | NULL |     10 |
|  8886 | ANOOP     | CLERK     | 8888 | 1993-01-12 | 1100 | NULL |     20 |
|  8888 | SCOTT     | ANALYST   | 8566 | 1992-12-09 | 3000 | NULL |     20 |
|  8900 | JATIN     | CLERK     | 8698 | 1991-12-03 |  950 | NULL |     30 |
|  8902 | FAKIR     | ANALYST   | 8566 | 1991-12-03 | 3000 | NULL |     20 |
|  8934 | MITA      | CLERK     | 8882 | 1992-01-23 | 1300 | NULL |     10 |
+-------+-----------+-----------+------+------------+------+------+--------+

Question 10

Question 3Write a query to display employee name and salary of those employee who don't have their salary in the range of 2500 to 4000.

Accepted Answer

SELECT ENAME, SAL
FROM empl
WHERE SAL NOT BETWEEN 2500 AND 4000;Output+----------+------+
| ENAME    | SAL  |
+----------+------+
| SMITH    |  800 |
| ANYA     | 1600 |
| SETH     | 1250 |
| MOMIN    | 1250 |
| AMIR     | 5000 |
| KULDEEP  | 1500 |
| SHIAVNSH | 2450 |
| ANOOP    | 1100 |
| JATIN    |  950 |
| MITA     | 1300 |
+----------+------+

Question 11

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

Accepted Answer

SELECT ENAME, JOB, SAL
FROM empl
WHERE MGR IS NULL ;Output+-------+-----------+------+
| ENAME | JOB       | SAL  |
+-------+-----------+------+
| AMIR  | PRESIDENT | 5000 |
+-------+-----------+------+

Question 12

Question 5Write a query to display the name of employee whose name contains 'A' as third alphabet.

Accepted Answer

SELECT ENAME
FROM empl
WHERE ENAME LIKE '__A%' ;ExplanationThere are no employees whose name contains 'A' as the third alphabet in the empl table. Therefore, the output will be empty.

Question 13

Question 6Write a query to display the name of employee whose name contains 'T' as the last alphabet.

Accepted Answer

SELECT ENAME
FROM empl
WHERE ENAME LIKE '%T' ;Output+-------+
| ENAME |
+-------+
| SCOTT |
+-------+

Question 14

Question 7Write a query to display the name of employee whose name contains 'M' as first alphabet 'L' as third alphabet.

Accepted Answer

SELECT ENAME
FROM empl
WHERE ENAME LIKE 'M_L%' ;ExplanationThere are no employees whose name contains 'M' as first alphabet and 'L' as third alphabet in the empl table. Therefore, the output will be empty.

Question 15

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

Accepted Answer

SELECT *
FROM customers
WHERE rating > 100 OR city = 'Shimla' ;

Question 16

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

Accepted Answer

SELECT *
FROM order
WHERE amt IS NOT NULL AND amt <> 0 ;

Question 17

Question 10Write SQL commands for the following on the basis of given table STUDENT :Table : STUDENTStudentNo.ClassNameGAMEGrade1SUPWGrade2107SameerCricketBPhotographyA118SujitTennisAGardeningC127KamalSwimmingBPhotographyB137VeenaTennisCCookingA149ArchanaBasket BallALiteratureA1510ArpitCricketAGardeningCDisplay the names of the students who are getting a grade 'C' in either GAME or SUPW.Display the different games offered in the school.Display the SUPW taken up by the students, whose name starts with 'A'.

Accepted Answer

Question 18

Question 11Write SQL commands for the following on the basis of given table SPORTS :Table : SPORTSStudentNo.ClassNameGame1Grade1Game2Grade2107SameerCricketBSwimmingA118SujitTennisASkatingC127KamalSwimmingBFootballB137VennaTennisCTennisA149ArchanaBasketballACricketA1510ArpitCricketAAthleticsCDisplay the names of the students who have grade 'C' in either Game1 or Game2 or both.Display the names of the students who have same game for both Game1 and Game2.Display the games taken up by the students, whose name starts with 'A'.

Accepted Answer

Question 19

Question 12Write SQL commands for the following on the basis of given table CLUB :Table : CLUBCOACH_IDCOACHNAMEAGESPORTSPAYSEXDATOFAPP1KUKREJA35KARATE1000M1996-03-272RAVINA34KARATE1200F1998-01-203KARAN34SQUASH2000M1998-02-194TARUN33BASKETBALL1500M1998-01-015ZUBIN36SWIMMING750M1998-01-126KETAKI36SWIMMING800F1998-02-247ANKITA39SQUASH2200F1998-02-208ZAREEN37KARATE1100F1998-02-229KUSH41SWIMMING900M1998-01-1310SHAILYA37BASKETBALL1700M1998-02-19To show all information about the swimming coaches in the club.To list names of all coaches with their date of appointment (DATOFAPP) in descending order.To display a report, showing coachname, pay, age and bonus (15% of pay) for all the coaches.

Accepted Answer

1.SELECT *
FROM CLUB 
WHERE SPORTS = 'SWIMMING' ;Output+----------+-----------+-----+----------+-----+-----+------------+
| COACH_ID | COACHNAME | AGE | SPORTS   | PAY | SEX | DATOFAPP   |
+----------+-----------+-----+----------+-----+-----+------------+
|        5 | ZUBIN     |  36 | SWIMMING | 750 | M   | 1998-01-12 |
|        6 | KETAKI    |  36 | SWIMMING | 800 | F   | 1998-02-24 |
|        9 | KUSH      |  41 | SWIMMING | 900 | M   | 1998-01-13 |
+----------+-----------+-----+----------+-----+-----+------------+
2.SELECT COACHNAME, DATOFAPP
FROM CLUB
ORDER BY DATOFAPP DESC ;Output+-----------+------------+
| COACHNAME | DATOFAPP   |
+-----------+------------+
| KETAKI    | 1998-02-24 |
| ZAREEN    | 1998-02-22 |
| ANKITA    | 1998-02-20 |
| KARAN     | 1998-02-19 |
| SHAILYA   | 1998-02-19 |
| RAVINA    | 1998-01-20 |
| KUSH      | 1998-01-13 |
| ZUBIN     | 1998-01-12 |
| TARUN     | 1998-01-01 |
| KUKREJA   | 1996-03-27 |
+-----------+------------+
3.SELECT COACHNAME, PAY, AGE, (PAY * 0.15) AS BONUS  
FROM CLUB ;Output+-----------+------+-----+--------+
| COACHNAME | PAY  | AGE | BONUS  |
+-----------+------+-----+--------+
| KUKREJA   | 1000 |  35 | 150.00 |
| RAVINA    | 1200 |  34 | 180.00 |
| KARAN     | 2000 |  34 | 300.00 |
| TARUN     | 1500 |  33 | 225.00 |
| ZUBIN     |  750 |  36 | 112.50 |
| KETAKI    |  800 |  36 | 120.00 |
| ANKITA    | 2200 |  39 | 330.00 |
| ZAREEN    | 1100 |  37 | 165.00 |
| KUSH      |  900 |  41 | 135.00 |
| SHAILYA   | 1700 |  37 | 255.00 |
+-----------+------+-----+--------+

Question 20

Question 13Write SQL commands for the following on the basis of given table STUDENT1 :Table : STUDENT1No.NameStipendStreamAvgMarkGradeClass1Karan400.00Medical78.5B12B2Divakar450.00Commerce89.2A11C3Divya300.00Commerce68.6C12C4Arun350.00Humanities73.1B12C5Sabina500.00Nonmedical90.6A11A6John400.00Medical75.4B12B7Robert250.00Humanities64.4C11A8Rubina450.00Nonmedical88.5A12A9Vikas500.00Nonmedical92.0A12A10Mohan300.00Commerce67.5C12CSelect all the Nonmedical stream students from STUDENT1.List the names of those students who are in class 12 sorted by Stipend.List all students sorted by AvgMark in descending order.Display a report, listing Name, Stipend, Stream and amount of stipend received in a year assuming that the Stipend is paid every month.

Accepted Answer

1.SELECT *
FROM STUDENT1
WHERE Stream = 'Nonmedical' ;Output+-----+--------+---------+------------+---------+-------+-------+
| No. | Name   | Stipend | Stream     | AvgMark | Grade | Class |
+-----+--------+---------+------------+---------+-------+-------+
|   5 | Sabina |     500 | Nonmedical | 90.6    | A     | 11A   |
|   8 | Rubina |     450 | Nonmedical | 88.5    | A     | 12A   |
|   9 | Vikas  |     500 | Nonmedical | 92.0    | A     | 12A   |
+-----+--------+---------+------------+---------+-------+-------+
2.SELECT Name 
FROM STUDENT1
WHERE Class LIKE '12%'
ORDER BY Stipend ;Output+--------+
| Name   |
+--------+
| Divya  |
| Mohan  |
| Arun   |
| Karan  |
| John   |
| Rubina |
| Vikas  |
+--------+
3.SELECT *
FROM STUDENT1
ORDER BY AvgMark DESC ;Output+-----+---------+---------+------------+---------+-------+-------+
| No. | Name    | Stipend | Stream     | AvgMark | Grade | Class |
+-----+---------+---------+------------+---------+-------+-------+
|   9 | Vikas   |     500 | Nonmedical | 92.0    | A     | 12A   |
|   5 | Sabina  |     500 | Nonmedical | 90.6    | A     | 11A   |
|   2 | Divakar |     450 | Commerce   | 89.2    | A     | 11C   |
|   8 | Rubina  |     450 | Nonmedical | 88.5    | A     | 12A   |
|   1 | Karan   |     400 | Medical    | 78.5    | B     | 12B   |
|   6 | John    |     400 | Medical    | 75.4    | B     | 12B   |
|   4 | Arun    |     350 | Humanities | 73.1    | B     | 12C   |
|   3 | Divya   |     300 | Commerce   | 68.6    | C     | 12C   |
|  10 | Mohan   |     300 | Commerce   | 67.5    | C     | 12C   |
|   7 | Robert  |     250 | Humanities | 64.4    | C     | 11A   |
+-----+---------+---------+------------+---------+-------+-------+
4.SELECT Name, Stipend, Stream, (Stipend * 12) AS Yearly_Stipend 
FROM STUDENT1 ;Output+---------+---------+------------+----------------+
| Name    | Stipend | Stream     | Yearly_Stipend |
+---------+---------+------------+----------------+
| Karan   |     400 | Medical    |           4800 |
| Divakar |     450 | Commerce   |           5400 |
| Divya   |     300 | Commerce   |           3600 |
| Arun    |     350 | Humanities |           4200 |
| Sabina  |     500 | Nonmedical |           6000 |
| John    |     400 | Medical    |           4800 |
| Robert  |     250 | Humanities |           3000 |
| Rubina  |     450 | Nonmedical |           5400 |
| Vikas   |     500 | Nonmedical |           6000 |
| Mohan   |     300 | Commerce   |           3600 |
+---------+---------+------------+----------------+

Simple Queries in SQL

Assertions And Reasons

Assignments

Checkpoint 131

Fill In The Blanks

Multiple Choice Questions

Truefalse Questions