Q: Question 8Write 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' ;

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 |
+---------+---------+------------+----------------+

Question 12

Question 14Consider the table Student1 of Q. 13. Give the output of following SQL statement :SELECT TRUNCATE(AvgMark) FROM Student1 WHERE AvgMark < 75 ;SELECT ROUND(AvgMark) FROM Student1 WHERE Grade = 'B' ;SELECT CONCAT(Name, Stream) FROM Student1 WHERE Class = '12A' ;SELECT RIGHT(Stream, 2) FROM Student1 ;

Accepted Answer

1. It will return error because no argument is passed as decimal places to truncate. Syntax of truncate function is TRUNCATE(number, decimals).2.Output+----------------+
| ROUND(AvgMark) |
+----------------+
|             78 |
|             73 |
|             75 |
+----------------+
3.Output+----------------------+
| CONCAT(Name, Stream) |
+----------------------+
| RubinaNonmedical     |
| VikasNonmedical      |
+----------------------+
4.Output+------------------+
| RIGHT(Stream, 2) |
+------------------+
| al               |
| ce               |
| ce               |
| es               |
| al               |
| al               |
| es               |
| al               |
| al               |
| ce               |
+------------------+

Question 13

Question 15Given the following table :Table : STUDENTNo.NameStipendStreamAvgMarkGradeClass1Karan400.00Medical78.5B12B2Divakar450.00Commerce89.2A11C3Divya300.00Commerce68.6C12C4Arun350.00Humanities73.1B12C5Sabina500.00Nonmedical90.6A11A6John400.00Medical75.4B12B7Robert250.00Humanities64.4C11A8Rubina450.00Nonmedical88.5A12A9Vikas500.00Nonmedical92.0A12A10Mohan300.00Commerce67.5C12CGive the output of following SQL statements :SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75 ;SELECT SUM(Stipend) FROM STUDENT WHERE Grade = 'B' ;SELECT AVG(Stipend) FROM STUDENT WHERE Class = '12A' ;SELECT COUNT(DISTINCT) FROM STUDENT ;

Accepted Answer

1.Output+--------------+
| MIN(AvgMark) |
+--------------+
| 64.4         |
+--------------+
2.Output+--------------+
| SUM(Stipend) |
+--------------+
|         1150 |
+--------------+
3.Output+--------------+
| AVG(Stipend) |
+--------------+
|          475 |
+--------------+
4. It will give an error because the COUNT function requires an argument specifying what to count. Additionally, the DISTINCT keyword is followed by a column name to count the distinct values of that column.

Question 14

Question 16Write SQL commands for the following on the basis of given table MOV :Table : MOVNoTitleTypeRatingStarsQtyPrice1Gone with the WindDramaGGable439.952Friday the 13thHorrorRJason269.953Top GunDramaPGCruise749.954SplashComedyPG13Hanks329.955Independence DayDramaRTurner319.956Risky BusinessComedyRCruise244.957CocoonScifiPGAmeche231.958Crocodile DundeeComedyPG13Harris269.959101 DalmatiansComedyG359.9510TootsieComedyPGHoffman129.95Display a list of all movies with Price over 20 and sorted by Price.Display all the movies sorted by QTY in decreasing order.Display a report listing a movie number, current value and replacement value for each movie in the above table. Calculate the replacement value for all movies as : QTY * Price * 1.15.

Accepted Answer

1.SELECT Title
FROM MOV
WHERE Price > 20
ORDER BY Price ;Output+--------------------+
| Title              |
+--------------------+
| Splash             |
| Tootsie            |
| Cocoon             |
| Gone with the Wind |
| Risky Business     |
| Top Gun            |
| 101 Dalmatians     |
| Friday the 13th    |
| Crocodile Dundee   |
+--------------------+
2.SELECT Title
FROM MOV
ORDER BY Qty DESC ;Output+--------------------+
| Title              |
+--------------------+
| Top Gun            |
| Gone with the Wind |
| Splash             |
| Independence Day   |
| 101 Dalmatians     |
| Friday the 13th    |
| Risky Business     |
| Cocoon             |
| Crocodile Dundee   |
| Tootsie            |
+--------------------+
3.SELECT No AS Movie_Number , Price AS Current_Value, (Qty * Price * 1.15) AS Replacement_Value
FROM MOV ;Output+--------------+---------------+--------------------+
| Movie_Number | Current_Value | Replacement_Value  |
+--------------+---------------+--------------------+
|            1 |         39.95 | 183.77000350952147 |
|            2 |         69.95 |   160.884992980957 |
|            3 |         49.95 | 402.09750614166256 |
|            4 |         29.95 |  103.3275026321411 |
|            5 |         19.95 |   68.8275026321411 |
|            6 |         44.95 | 103.38500175476074 |
|            7 |         31.95 |  73.48500175476073 |
|            8 |         69.95 |   160.884992980957 |
|            9 |         59.95 |  206.8275026321411 |
|           10 |         29.95 |  34.44250087738037 |
+--------------+---------------+--------------------+

Question 15

Question 17Write SQL commands for the following on the basis of given table Teacher :Table : TeacherNoNameAgeDepartmentSalarySexDateofjoin1Jugal34Computer12000M1997-01-102Sharmila31History20000F1998-03-243Sandeep32Maths30000M1996-12-124Sangeeta35History40000F1999-07-015Rakesh42Maths25000M1997-09-056Shyam50History30000M1998-06-277Shiv Om44Computer21000M1997-02-258Shalakha33Maths20000F1997-07-31To show all information about the teacher of history department.To list the names of female teachers who are in Hindi department.To list names of all teachers with their date of joining in ascending order.

Accepted Answer

1.SELECT *
FROM Teacher
WHERE Department = 'History' ;Output+----+----------+-----+------------+--------+-----+------------+
| No | Name     | Age | Department | Salary | Sex | Dateofjoin |
+----+----------+-----+------------+--------+-----+------------+
|  2 | Sharmila |  31 | History    |  20000 | F   | 1998-03-24 |
|  4 | Sangeeta |  35 | History    |  40000 | F   | 1999-07-01 |
|  6 | Shyam    |  50 | History    |  30000 | M   | 1998-06-27 |
+----+----------+-----+------------+--------+-----+------------+
2.SELECT Name
FROM Teacher
WHERE Sex = 'F' and Department = 'Hindi' ;ExplanationThere are no records in the Teacher table where the department is 'Hindi'. Hence, there will be no output.3.SELECT Name, Dateofjoin
FROM Teacher
ORDER BY Dateofjoin ;Output+----------+------------+
| Name     | Dateofjoin |
+----------+------------+
| Sandeep  | 1996-12-12 |
| Jugal    | 1997-01-10 |
| Shiv Om  | 1997-02-25 |
| Shalakha | 1997-07-31 |
| Rakesh   | 1997-09-05 |
| Sharmila | 1998-03-24 |
| Shyam    | 1998-06-27 |
| Sangeeta | 1999-07-01 |
+----------+------------+

Question 16

Question 4Compare Char and Varchar datatypes.

Accepted Answer

Char datatypeVarchar datatypeChar datatype specifies a fixed length string.Varchar datatype specifies a variable length string.Defining a length is not required, but the default is 1.Defining a length is required.CHAR(n) ensures that all values stored in that column are of length n bytes, padding shorter values with blanks while maintaining a fixed size of n bytes.VARCHAR(n) columns have a maximum size of n bytes, storing values exactly as specified without adding blanks for shorter lengths. Exceeding n bytes results in an error message.

Question 17

Question 18The pattern '_ _ _' matches any string of ............... three characters. '_ _ _%' matches any string of ............... three characters.Atleast, ExactlyExactly, AtleastAtleast, AllAll, Exactly

Accepted Answer

Exactly, AtleastReason — '_ _ _' matches any string of exactly 3 characters. Each dash represents one character, so there must be three characters in total. '_ _ _%' matches any string of at least 3 characters. The first three dashes represent exactly three characters, and the '%' symbol matches any substring. So, it matches any string with three or more characters.

Question 18

Question 19By default, ORDER BY clause lists the results in ............... order.DescendingAnySameAscending

Accepted Answer

AscendingReason — By default, ORDER BY clause lists the results in ascending order.

Source: This question is from Simple Queries in SQL, Computer Science &mdash; Class 12, CBSE Board.
Key Concepts CoveredThis question tests your understanding of the following concepts from the chapter Simple Queries in SQL: Question, Default, Order, Clause, Lists, Results. These are fundamental topics in Computer Science that students are expected to master as part of the CBSE Class 12 curriculum.A thorough understanding of these concepts will help you answer similar questions confidently in your CBSE examinations. These topics are frequently tested in both objective and subjective sections of Computer Science papers. We recommend revising the relevant section of your textbook alongside practising these solved examples to build a strong foundation.
How to Approach MCQsMultiple choice questions test your ability to quickly recall facts and apply concepts. Read all options carefully before selecting your answer. Use the process of elimination to rule out clearly incorrect options first. If two options seem similar, look for the subtle difference that makes one more accurate. For numerical MCQs, verify your answer by substituting it back into the problem.
Key Points to RememberWrite programs with proper indentation and comments.
Trace through your code with sample inputs to verify correctness.
Explain the logic behind each step of your solution.
Familiarise yourself with common library functions and methods.
Related Questions from Simple Queries in SQLQ1: Question 1 The condition in a WHERE clause in a SELECT query can refer to only one value.
Q10: Question 10 Write SQL commands for the following on the basis of given table STUDENT : Table :...
Q11: Question 11 Write SQL commands for the following on the basis of given table SPORTS : Table :...
Q12: Question 12 Write SQL commands for the following on the basis of given table CLUB : Table : CLUB...
Q13: Question 13 Write SQL commands for the following on the basis of given table STUDENT1 : Table :...
Practice more questions from Simple Queries in SQL &mdash; Computer Science, Class 12 CBSE

Question 19

Question 20Consider the following querySELECT * FROM employee ORDER BY salary ..............., name ...............;To display the salary from greater to smaller and name in alphabetical order which of the following options should be used ?Ascending, DescendingAsc, DescDesc, AscDescending, Ascending

Accepted Answer

Desc, AscDescending, AscendingReason — To display the salary from greater to smaller i.e., in descending order we use DESC or descending keyword and to sort name in alphabetical order i.e., in ascending order we use ASC or ascending keyword.

Question 20

Question 1The condition in a WHERE clause in a SELECT query can refer to only one value.

Accepted Answer

FalseReason — In SQL, the condition in a WHERE clause can refer to multiple values. We can use logical operators such as AND, OR, and NOT to combine multiple conditions. For example :SELECT * FROM pet WHERE (species = 'cat' OR species = 'dog') AND sex = 'm';

Simple Queries in SQL

Assignments

Checkpoint 131

Multiple Choice Questions

Truefalse Questions