Q: Question 8What SQL statement do we use to display the record of all students whose last name contains 5 letters ending with "A"?SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _';SELECT * FROM STUDENTS WHERE LNAME LIKE '????A';SELECT * FROM STUDENTS WHERE LNAME LIKE '*A';

SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';Reason — The SQL statement to display the records of all students whose last name contains 5 letters ending with "A" is SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';. This statement uses the LIKE operator with the pattern '_ _ _ _A', where each underscore represents a single character and the letter "A" specifies the ending condition. This pattern ensures that the last name has exactly 5 letters and ends with "A".

Q: Question 11Consider a table named Employees with columns — EName, Age, Salary and Designation. Which SQL statement do we use to find out the total salary given to employees?Select SUM(Salary) From Employees;Select COUNT (*) From Employees;Select FIND (*) From Employees;Select SUM () From Employees;

Select SUM(Salary) From Employees;Reason — The SQL statement used to find out the total salary given to employees is SELECT SUM(Salary) FROM Employees;. This statement utilizes the SUM() function to calculate the total value of the 'Salary' column from the 'Employees' table.

Q: Question 4Write a query to find out the sum, average, lowest and highest marks in Student table.

The Student table is as follows:ROLLNONAMEGENDERMARKSDOBMOBILE_NOSTREAM1RAJ KUMARM932000-11-179586774748SCIENCE2DEEP SINGHM981996-08-228988886577COMMERCE3ANKIT SHARMAM762000-02-02NULLSCIENCE4RADHIKA GUPTAF781999-12-039818675444HUMANITIES5PAYAL GOELF821998-04-219845639990VOCATIONAL6DIKSHA SHARMAF801999-12-179897666650HUMANITIES7GURPREET KAURFNULL2000-01-047560875609SCIENCE8AKSHAY DUREJAM901997-05-059560567890COMMERCE9SHREYA ANANDF701999-10-08NULLVOCATIONAL10PRATEEK MITTALM752000-12-259999967543SCIENCESELECT SUM(MARKS) AS Total_Sum, AVG(MARKS) AS Average_Marks, MIN(MARKS) AS Lowest_Marks, MAX(MARKS) AS Highest_Marks FROM Student;Output+-----------+---------------+--------------+---------------+ | Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks | +-----------+---------------+--------------+---------------+ | 742 | 82.4444 | 70 | 98 | +-----------+---------------+--------------+---------------+

Q: Question 5Write a query to find out the sum, average, lowest and highest marks of the students in Student table grouped by STREAM.

The Student table is as follows:ROLLNONAMEGENDERMARKSDOBMOBILE_NOSTREAM1RAJ KUMARM932000-11-179586774748SCIENCE2DEEP SINGHM981996-08-228988886577COMMERCE3ANKIT SHARMAM762000-02-02NULLSCIENCE4RADHIKA GUPTAF781999-12-039818675444HUMANITIES5PAYAL GOELF821998-04-219845639990VOCATIONAL6DIKSHA SHARMAF801999-12-179897666650HUMANITIES7GURPREET KAURFNULL2000-01-047560875609SCIENCE8AKSHAY DUREJAM901997-05-059560567890COMMERCE9SHREYA ANANDF701999-10-08NULLVOCATIONAL10PRATEEK MITTALM752000-12-259999967543SCIENCESELECT STREAM, SUM(MARKS) AS Total_Sum, AVG(MARKS) AS Average_Marks, MIN(MARKS) AS Lowest_Marks, MAX(MARKS) AS Highest_Marks FROM Student GROUP BY STREAM;Output+------------+-----------+---------------+--------------+---------------+ | STREAM | Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks | +------------+-----------+---------------+--------------+---------------+ | SCIENCE | 244 | 81.3333 | 75 | 93 | | COMMERCE | 188 | 94.0000 | 90 | 98 | | HUMANITIES | 158 | 79.0000 | 78 | 80 | | VOCATIONAL | 152 | 76.0000 | 70 | 82 | +------------+-----------+---------------+--------------+---------------+

Q: Question 6Consider the table "Item" given below and give the outputs on the basis of it:Table: ITEMItemnoInamePrice (₹)Quantity101Soap50100102Powder10050103Facecream15025104Pen50200105Soapbox20100(a) SELECT SUM(Price) FROM ITEM;(b) SELECT AVG(Price) FROM ITEM;(c) SELECT MIN(Price) FROM ITEM;(d) SELECT MAX(Price) FROM ITEM;(e) SELECT COUNT(Price) FROM ITEM;(f) SELECT DISTINCT Price FROM ITEM;(g) SELECT COUNT(DISTINCT Price) FROM ITEM;(h) SELECT Iname, Price*Quantity FROM ITEM;

(a) SELECT SUM(Price) FROM ITEM;Output+------------+ | SUM(Price) | +------------+ | 370 | +------------+ (b) SELECT AVG(Price) FROM ITEM;Output+------------+ | AVG(Price) | +------------+ | 74.0000 | +------------+ (c) SELECT MIN(Price) FROM ITEM;Output+------------+ | MIN(Price) | +------------+ | 20 | +------------+ (d) SELECT MAX(Price) FROM ITEM;Output+------------+ | MAX(Price) | +------------+ | 150 | +------------+ (e) SELECT COUNT(Price) FROM ITEM;Output+--------------+ | COUNT(Price) | +--------------+ | 5 | +--------------+ (f) SELECT DISTINCT Price FROM ITEM;Output+-------+ | Price | +-------+ | 50 | | 100 | | 150 | | 20 | +-------+ (g) SELECT COUNT(DISTINCT Price) FROM ITEM;Output+-----------------------+ | COUNT(DISTINCT Price) | +-----------------------+ | 4 | +-----------------------+ (h) SELECT Iname, Price*Quantity FROM ITEM;Output+-----------+----------------+ | Iname | Price*Quantity | +-----------+----------------+ | SOAP | 5000 | | POWDER | 5000 | | FACECREAM | 3750 | | PEN | 10000 | | SOAPBOX | 2000 | +-----------+----------------+

Q: Question 9Write a command to display the name of the current month.

SELECT MONTHNAME(CURDATE());Output+----------------------+ | MONTHNAME(CURDATE()) | +----------------------+ | June | +----------------------+

Question 1

Question 2Assertion (A): The HAVING clause is used with GROUP BY clause and aggregate functions.Reasoning (R): WHERE clause specifies the condition on individual rows or records.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true but R is not the correct explanation of A.ExplanationThe HAVING clause is used in combination with the GROUP BY clause. It allows aggregate functions to be used along with the specified condition. This is necessary because aggregate functions cannot be used with the WHERE clause, which is evaluated on a single row, whereas aggregate functions are evaluated on a group of rows specified by the GROUP BY clause.

Question 2

Question 3Assertion (A): SQL queries are performed on a table created under a database.Reasoning (R): The database is a folder created by the user in MySQL workbench. Thereafter, the tables are created in a database where various queries can be performed using SQL commands.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationThe database is a folder created by the user in MySQL workbench. Within this database, tables are created, and SQL commands are used to perform various queries on these tables, such as altering tables, inserting data, and retrieving data.

Question 3

Question 7Assertion (A): GROUP BY clause is always preceded by ORDER BY clause in a SELECT statement.Reasoning (R): ORDER BY clause always follows other clauses.Both A and R are true and R is the correct explanation of A.Both A and R are true but R is not the correct explanation of A.A is true but R is false.A is false but R is true.

Accepted Answer

Both A and R are true and R is the correct explanation of A.ExplanationIn SQL syntax, the GROUP BY clause comes before the ORDER BY clause. The standard order of clauses is SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. On the other hand, it is true that the ORDER BY clause always follows other clauses, as it comes after the other clauses in the statement.

Question 4

Question 1ABC Associates has over 2000 employees on its roll and deals with customer support services. Help the company to perform associated operations for calculating the salaries (stored in Sal column) of their employees and to perform relevant analysis on the fetched data from the SQL database/table.How can we retrieve records where salary is between 1000 and 2000?

Accepted Answer

SELECT * FROM emp 
WHERE Sal BETWEEN 1000 AND 2000;ORSELECT * FROM emp 
WHERE Sal >= 1000 AND Sal <= 2000;

Question 5

Question 8What SQL statement do we use to display the record of all students whose last name contains 5 letters ending with "A"?SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _';SELECT * FROM STUDENTS WHERE LNAME LIKE '????A';SELECT * FROM STUDENTS WHERE LNAME LIKE '*A';

Accepted Answer

SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';Reason — The SQL statement to display the records of all students whose last name contains 5 letters ending with "A" is SELECT * FROM STUDENTS WHERE LNAME LIKE '_ _ _ _A';. This statement uses the LIKE operator with the pattern '_ _ _ _A', where each underscore represents a single character and the letter "A" specifies the ending condition. This pattern ensures that the last name has exactly 5 letters and ends with "A".

Question 6

Question 11Consider a table named Employees with columns — EName, Age, Salary and Designation. Which SQL statement do we use to find out the total salary given to employees?Select SUM(Salary) From Employees;Select COUNT (*) From Employees;Select FIND (*) From Employees;Select SUM () From Employees;

Accepted Answer

Select SUM(Salary) From Employees;Reason — The SQL statement used to find out the total salary given to employees is SELECT SUM(Salary) FROM Employees;. This statement utilizes the SUM() function to calculate the total value of the 'Salary' column from the 'Employees' table.

Question 7

Question 12Which clause in SQL is used to apply a condition on a group?WhereHavingAsOn

Accepted Answer

HavingReason — The HAVING clause is used in SQL queries to place conditions on groups when using the GROUP BY clause.

Source: This question is from Database Query using SQL, Informatics Practices &mdash; Class 12, CBSE Board.
Key Concepts CoveredThis question tests your understanding of the following concepts from the chapter Database Query using SQL: Question, Clause, Sql, Apply, Condition, Group. These are fundamental topics in Informatics Practices 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 Informatics Practices 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 RememberRead the question carefully and identify all parts before answering.
Use the terminology specific to this subject and chapter.
Review the textbook content for this chapter before attempting questions.
Practice writing concise, well-structured answers within time limits.
Related Questions from Database Query using SQLQ1: Question 1 ABC Associates has over 2000 employees on its roll and deals with customer support...
Q10: Question 10 Write a command to print the day of the week of your birthday in the year 1999.
Q11: Question 11 Consider a table named Employees with columns — EName, Age, Salary and Designation....
Q13: Question 13 Write a query to find out the number of students in each Stream in STUDENT table.
Q14: Question 14 Consider the given table Faculty and answer the questions that follow: Table: FACULTY...
Practice more questions from Database Query using SQL &mdash; Informatics Practices, Class 12 CBSE

Question 8

Question 4Write a query to find out the sum, average, lowest and highest marks in Student table.

Accepted Answer

The Student table is as follows:ROLLNONAMEGENDERMARKSDOBMOBILE_NOSTREAM1RAJ KUMARM932000-11-179586774748SCIENCE2DEEP SINGHM981996-08-228988886577COMMERCE3ANKIT SHARMAM762000-02-02NULLSCIENCE4RADHIKA GUPTAF781999-12-039818675444HUMANITIES5PAYAL GOELF821998-04-219845639990VOCATIONAL6DIKSHA SHARMAF801999-12-179897666650HUMANITIES7GURPREET KAURFNULL2000-01-047560875609SCIENCE8AKSHAY DUREJAM901997-05-059560567890COMMERCE9SHREYA ANANDF701999-10-08NULLVOCATIONAL10PRATEEK MITTALM752000-12-259999967543SCIENCESELECT SUM(MARKS) AS Total_Sum, AVG(MARKS) AS Average_Marks, 
MIN(MARKS) AS Lowest_Marks, MAX(MARKS) AS Highest_Marks
FROM Student;Output+-----------+---------------+--------------+---------------+
| Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+-----------+---------------+--------------+---------------+
|       742 |       82.4444 |           70 |            98 |
+-----------+---------------+--------------+---------------+

Question 9

Question 5Write a query to find out the sum, average, lowest and highest marks of the students in Student table grouped by STREAM.

Accepted Answer

The Student table is as follows:ROLLNONAMEGENDERMARKSDOBMOBILE_NOSTREAM1RAJ KUMARM932000-11-179586774748SCIENCE2DEEP SINGHM981996-08-228988886577COMMERCE3ANKIT SHARMAM762000-02-02NULLSCIENCE4RADHIKA GUPTAF781999-12-039818675444HUMANITIES5PAYAL GOELF821998-04-219845639990VOCATIONAL6DIKSHA SHARMAF801999-12-179897666650HUMANITIES7GURPREET KAURFNULL2000-01-047560875609SCIENCE8AKSHAY DUREJAM901997-05-059560567890COMMERCE9SHREYA ANANDF701999-10-08NULLVOCATIONAL10PRATEEK MITTALM752000-12-259999967543SCIENCESELECT STREAM, SUM(MARKS) AS Total_Sum,
AVG(MARKS) AS Average_Marks, MIN(MARKS) AS Lowest_Marks,
MAX(MARKS) AS Highest_Marks
FROM Student
GROUP BY STREAM;Output+------------+-----------+---------------+--------------+---------------+
| STREAM     | Total_Sum | Average_Marks | Lowest_Marks | Highest_Marks |
+------------+-----------+---------------+--------------+---------------+
| SCIENCE    |       244 |       81.3333 |           75 |            93 |
| COMMERCE   |       188 |       94.0000 |           90 |            98 |
| HUMANITIES |       158 |       79.0000 |           78 |            80 |
| VOCATIONAL |       152 |       76.0000 |           70 |            82 |
+------------+-----------+---------------+--------------+---------------+

Question 10

Question 6Consider the table "Item" given below and give the outputs on the basis of it:Table: ITEMItemnoInamePrice (₹)Quantity101Soap50100102Powder10050103Facecream15025104Pen50200105Soapbox20100(a) SELECT SUM(Price) FROM ITEM;(b) SELECT AVG(Price) FROM ITEM;(c) SELECT MIN(Price) FROM ITEM;(d) SELECT MAX(Price) FROM ITEM;(e) SELECT COUNT(Price) FROM ITEM;(f) SELECT DISTINCT Price FROM ITEM;(g) SELECT COUNT(DISTINCT Price) FROM ITEM;(h) SELECT Iname, Price*Quantity FROM ITEM;

Accepted Answer

(a) SELECT SUM(Price) FROM ITEM;Output+------------+
| SUM(Price) |
+------------+
|        370 |
+------------+
(b) SELECT AVG(Price) FROM ITEM;Output+------------+
| AVG(Price) |
+------------+
|    74.0000 |
+------------+
(c) SELECT MIN(Price) FROM ITEM;Output+------------+
| MIN(Price) |
+------------+
|         20 |
+------------+
(d) SELECT MAX(Price) FROM ITEM;Output+------------+
| MAX(Price) |
+------------+
|        150 |
+------------+
(e) SELECT COUNT(Price) FROM ITEM;Output+--------------+
| COUNT(Price) |
+--------------+
|            5 |
+--------------+
(f) SELECT DISTINCT Price FROM ITEM;Output+-------+
| Price |
+-------+
|    50 |
|   100 |
|   150 |
|    20 |
+-------+
(g) SELECT COUNT(DISTINCT Price) FROM ITEM;Output+-----------------------+
| COUNT(DISTINCT Price) |
+-----------------------+
|                     4 |
+-----------------------+
(h) SELECT Iname, Price*Quantity FROM ITEM;Output+-----------+----------------+
| Iname     | Price*Quantity |
+-----------+----------------+
| SOAP      |           5000 |
| POWDER    |           5000 |
| FACECREAM |           3750 |
| PEN       |          10000 |
| SOAPBOX   |           2000 |
+-----------+----------------+

Question 11

Question 9Write a command to display the name of the current month.

Accepted Answer

SELECT MONTHNAME(CURDATE());Output+----------------------+
| MONTHNAME(CURDATE()) |
+----------------------+
| June                 |
+----------------------+

Question 12

Question 10Write a command to print the day of the week of your birthday in the year 1999.

Accepted Answer

SELECT DAYOFWEEK('1999-07-21');Output+-------------------------+
| DAYOFWEEK('1999-07-21') |
+-------------------------+
|                       4 |
+-------------------------+

Question 13

Question 13Write a query to find out the number of students in each Stream in STUDENT table.

Accepted Answer

The Student table is as follows:ROLLNONAMEGENDERMARKSDOBMOBILE_NOSTREAM1RAJ KUMARM932000-11-179586774748SCIENCE2DEEP SINGHM981996-08-228988886577COMMERCE3ANKIT SHARMAM762000-02-02NULLSCIENCE4RADHIKA GUPTAF781999-12-039818675444HUMANITIES5PAYAL GOELF821998-04-219845639990VOCATIONAL6DIKSHA SHARMAF801999-12-179897666650HUMANITIES7GURPREET KAURFNULL2000-01-047560875609SCIENCE8AKSHAY DUREJAM901997-05-059560567890COMMERCE9SHREYA ANANDF701999-10-08NULLVOCATIONAL10PRATEEK MITTALM752000-12-259999967543SCIENCESELECT STREAM, COUNT(*) AS NUMBER_OF_STUDENTS
FROM STUDENT
GROUP BY STREAM;Output+------------+--------------------+
| STREAM     | NUMBER_OF_STUDENTS |
+------------+--------------------+
| SCIENCE    |                  4 |
| COMMERCE   |                  2 |
| HUMANITIES |                  2 |
| VOCATIONAL |                  2 |
+------------+--------------------+

Question 14

Question 14Consider the given table Faculty and answer the questions that follow:Table: FACULTYF_IDF_NameL_NameHire_dateSalary102AmitMishra1998-10-1210000103NitinVyas1994-12-248000104RakshitSoni2001-05-1814000105RashmiMalhotra2004-09-1111000106SulekhaSrivastava2006-06-0510000(a) To display the details of those Faculty members whose salary is higher than 12000.(b) To display the details of Faculty members whose salary is in the range of 8000 to 12000 (both values included).(c) Count the number of different ids from faculty.(d) Count the number of faculty members getting salary as 10000.(e) Display details of those faculty members whose names start with S.(f) Display all records in descending order of Hire date.(g) Find the maximum and the minimum salary.(h) Select CONCAT(F_Name, L_Name) from FACULTY;(i) Select Month(Hire_date) from FACULTY;

Accepted Answer

(a)SELECT * FROM FACULTY WHERE SALARY > 12000;Output+------+---------+--------+------------+--------+
| F_ID | F_NAME  | L_NAME | HIRE_DATE  | SALARY |
+------+---------+--------+------------+--------+
|  104 | RAKSHIT | SONI   | 2001-05-18 |  14000 |
+------+---------+--------+------------+--------+
(b)SELECT * FROM FACULTY WHERE SALARY BETWEEN 8000 AND 12000;Output+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+
(c)SELECT COUNT(DISTINCT F_ID) FROM FACULTY;Output+----------------------+
| COUNT(DISTINCT F_ID) |
+----------------------+
|                    5 |
+----------------------+
(d)SELECT COUNT(*) FROM FACULTY WHERE SALARY = 10000;Output+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
(e) SELECT * FROM FACULTY WHERE F_NAME LIKE 'S%';Output+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
+------+---------+------------+------------+--------+
(f)SELECT * FROM FACULTY ORDER BY HIRE_DATE DESC;Output+------+---------+------------+------------+--------+
| F_ID | F_NAME  | L_NAME     | HIRE_DATE  | SALARY |
+------+---------+------------+------------+--------+
|  106 | SULEKHA | SRIVASTAVA | 2006-06-05 |  10000 |
|  105 | RASHMI  | MALHOTRA   | 2004-09-11 |  11000 |
|  104 | RAKSHIT | SONI       | 2001-05-18 |  14000 |
|  102 | AMIT    | MISHRA     | 1998-10-12 |  10000 |
|  103 | NITIN   | VYAS       | 1994-12-24 |   8000 |
+------+---------+------------+------------+--------+
(g)SELECT MAX(SALARY), MIN(SALARY) FROM FACULTY;Output+-------------+-------------+
| MAX(SALARY) | MIN(SALARY) |
+-------------+-------------+
|       14000 |        8000 |
+-------------+-------------+
(h)Output+------------------------+
| CONCAT(F_Name, L_Name) |
+------------------------+
| AMITMISHRA             |
| NITINVYAS              |
| RAKSHITSONI            |
| RASHMIMALHOTRA         |
| SULEKHASRIVASTAVA      |
+------------------------+
(i)Output+------------------+
| Month(Hire_date) |
+------------------+
|               10 |
|               12 |
|                5 |
|                9 |
|                6 |
+------------------+

Question 15

Question 15Consider the following EMP and DEPT tables:Table: EMPEmpNoEmpNameCityDesignationDOJSalCommDeptID8369SMITHMumbaiCLERK1990-12-18800.00NULL208499ANYAVaranasiSALESMAN1991-02-201600.00300.00308521SETHJaipurSALESMAN1991-02-221250.00500.00308566MAHADEVANDelhiMANAGER1991-04-022985.00NULL20Table: DEPTDeptIDDeptNameMgrIDLocation10SALES8566Mumbai20PERSONNEL9698Delhi30ACCOUNTS4578Delhi40RESEARCH8839BengaluruWrite the SQL command to get the following:(a) Show the minimum, maximum and average salary of managers.(b) Count the number of clerks in the organization.(c) Display the designation-wise list of employees with name, salary and date of joining.(d) Count the number of employees who are not getting commission.(e) Show the average salary for all departments with more than 5 working people.(f) List the count of employees grouped by DeptID.(g) Display the maximum salary of employees in each department.(h) Display the name of employees along with their designation and department name.(i) Count the number of employees working in ACCOUNTS department.

Accepted Answer

(a)SELECT MIN(Sal) AS MinSalary, MAX(Sal) AS MaxSalary, AVG(Sal) AS AvgSalary
FROM EMP
WHERE Designation = 'MANAGER';Output+-----------+-----------+-----------+
| MinSalary | MaxSalary | AvgSalary |
+-----------+-----------+-----------+
|      2985 |      2985 |      2985 |
+-----------+-----------+-----------+
(b)SELECT COUNT(*) AS ClerkCount
FROM EMP
WHERE Designation = 'CLERK';Output+------------+
| ClerkCount |
+------------+
|          1 |
+------------+
(c)SELECT Designation, EmpName, Sal, DOJ
FROM EMP
ORDER BY Designation;Output+-------------+-----------+------+------------+
| Designation | EmpName   | Sal  | DOJ        |
+-------------+-----------+------+------------+
| CLERK       | SMITH     |  800 | 1990-12-18 |
| MANAGER     | MAHADEVAN | 2985 | 1991-04-02 |
| SALESMAN    | ANYA      | 1600 | 1991-02-20 |
| SALESMAN    | SETH      | 1250 | 1991-02-22 |
+-------------+-----------+------+------------+
(d)SELECT COUNT(*) AS No_comm
FROM EMP
WHERE comm is NULL;Output+---------+
| No_comm |
+---------+
|       2 |
+---------+
(e)SELECT d.DeptID, d.DeptName, AVG(e.Sal) AS Avg_Salary
FROM DEPT d, EMP e
WHERE e.DeptID = d.DeptID AND d.DeptID IN (
  SELECT DeptID
  FROM EMP
  GROUP BY DeptID
  HAVING COUNT(*) > 5
)
GROUP BY d.DeptID, d.DeptName;(f)SELECT DeptID, COUNT(*) AS EmpCount
FROM EMP
GROUP BY DeptID;Output+--------+----------+
| DeptID | EmpCount |
+--------+----------+
|     20 |        2 |
|     30 |        2 |
+--------+----------+
(g)SELECT D.DeptName, MAX(E.Sal) AS MaxSalary
FROM EMP E, DEPT D
WHERE E.DeptID = D.DeptID
GROUP BY D.DeptName;Output+-----------+-----------+
| DeptName  | MaxSalary |
+-----------+-----------+
| PERSONNEL |      2985 |
| ACCOUNTS  |      1600 |
+-----------+-----------+
(h)SELECT EMP.EMPNAME, EMP.DESIGNATION, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID;Output+-----------+-------------+-----------+
| EMPNAME   | DESIGNATION | DEPTNAME  |
+-----------+-------------+-----------+
| SMITH     | CLERK       | PERSONNEL |
| ANYA      | SALESMAN    | ACCOUNTS  |
| SETH      | SALESMAN    | ACCOUNTS  |
| MAHADEVAN | MANAGER     | PERSONNEL |
+-----------+-------------+-----------+
(i)SELECT COUNT(*) AS NUM_EMP
FROM EMP, DEPT
WHERE EMP.DEPTID = DEPT.DEPTID
AND DEPTNAME = 'ACCOUNTS';Output+---------+
| NUM_EMP |
+---------+
|       2 |
+---------+

Question 16

Question 16Write a MySQL command for creating a table PAYMENT whose structure is given below:Table: PAYMENTField NameData typeSizeConstraintLoan_numberInteger4Primary keyPayment_numberVarchar3Payment_dateDatePayment_amountInteger8Not Null

Accepted Answer

CREATE TABLE PAYMENT (
    Loan_number INT(4) PRIMARY KEY,
    Payment_number VARCHAR(3),
    Payment_date DATE,
    Payment_amount INT(8) NOT NULL
);

Question 17

Question 17Consider the table Product shown below:Table: PRODUCTP_IDProductNameManufacturerPriceP001MoisturizerXYZ40P002SanitizerLAC35P003Bath SoapCOP25P004ShampooTAP95P005Lens SolutionCOP350Write the commands in SQL queries for the following:(a) To display the details of product whose price is in the range of 40 and 120 (both values included).(b) To increase the price of all the products by 20.

Accepted Answer

(a)SELECT * 
FROM PRODUCT 
WHERE PRICE BETWEEN 40 AND 120;Output+------+-------------+--------------+-------+
| P_ID | ProductName | Manufacturer | Price |
+------+-------------+--------------+-------+
| P001 | MOISTURISER | XYZ          |    40 |
| P004 | SHAMPOO     | TAP          |    95 |
+------+-------------+--------------+-------+
(b)UPDATE PRODUCT 
SET PRICE = PRICE + 20;

Question 18

Question 18Consider the table RESULT given below. Write commands in MySQL for (a) to (d) and output for (e) to (g):Table: RESULTNoNameStipendSubjectAverageDivision1Sharon400English38THIRD2Amal680Mathematics72FIRST3Vedant500Accounts67FIRST4Shakeer200Informatics55SECOND5Anandha400History85FIRST6Upasna550Geography45THIRD(a) To list the names of those students who have obtained Division as FIRST in the ascending order of NAME.(b) To display a report listing Name, Subject and Annual Stipend received assuming that the Stipend column has monthly stipend.(c) To count the number of students who have either Accounts or Informatics as subject.(d) To insert a new row in the table RESULT: 7, "Mohan", 500, "English", 73, "Second"(e) SELECT AVG(STIPEND) FROM RESULT WHERE DIVISION = "THIRD";(f) SELECT COUNT(DISTINCT Subject) FROM RESULT;(g) SELECT MIN(Average) FROM RESULT WHERE Subject = "English";

Accepted Answer

Question 19

Question 19Write the SQL query commands based on the following table:Table: SCHOOLBUSRtnoArea_CoveredCapacityNoOfStudentsDistanceTransporterCharges1Vasant Kunj10012010Shivam Travels1000002Hauz Khas808010Anand Travels850003Pitampura605530Anand Travels600004Rohini1009035Anand Travels1000005Yamuna Vihar506020Bhalla Co.550006Krishna Nagar708030Yadav Co.800007Vasundhra10011020Yadav Co.1000008Paschim Vihar404020Speed Travels550009Saket12012010Speed Travels10000010Janak Puri10010020Kisan Tours95000(a) To show all information of students where capacity is more than the number of students in order of rtno.(b) To show area_covered for buses covering more than 20 km, but charge is less than 80000.(c) To show transporter-wise total no. of students travelling.(d) To show Rtno, Area_covered and Average cost per student for all routes where average cost per student is — Charges/NoOfStudents(e) Add a new record with the following data:(11, "Moti Bagh", 35,32,10, "Kisan Tours", 35000)(f) Give the output considering the original relation as given below:SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";SELECT MIN(NoOfStudents) FROM SCHOOLBUS;SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";SELECT DISTINCT Transporter FROM SCHOOLBUS;

Accepted Answer

(a)SELECT * 
FROM SCHOOLBUS 
WHERE Capacity > NoOfStudents 
ORDER BY Rtno;Output+------+--------------+----------+--------------+----------+---------------+---------+
| RTNO | AREA_COVERED | CAPACITY | NOOFSTUDENTS | DISTANCE | TRANSPORTER   | CHARGES |
+------+--------------+----------+--------------+----------+---------------+---------+
|    3 | PITAMPURA    |       60 |           55 |       30 | ANAND TRAVELS |   60000 |
|    4 | ROHINI       |      100 |           90 |       35 | ANAND TRAVELS |  100000 |
+------+--------------+----------+--------------+----------+---------------+---------+
(b)SELECT Area_Covered 
FROM SCHOOLBUS 
WHERE Distance > 20 AND Charges < 80000;Output+--------------+
| Area_Covered |
+--------------+
| PITAMPURA    |
+--------------+
(c)SELECT Transporter, SUM(NoOfStudents) AS Total_Students 
FROM SCHOOLBUS 
GROUP BY Transporter;Output+----------------+----------------+
| Transporter    | Total_Students |
+----------------+----------------+
| SHIVAM TRAVELS |            120 |
| ANAND TRAVELS  |            225 |
| BHALLA CO.     |             60 |
| YADAV CO.      |            190 |
| SPEED TRAVELS  |            160 |
| KISAN TOURS    |            100 |
+----------------+----------------+
(d)SELECT Rtno, Area_Covered, (Charges / NoOfStudents) AS Average_Cost_Per_Student 
FROM SCHOOLBUS;Output+------+---------------+--------------------------+
| Rtno | Area_Covered  | Average_Cost_Per_Student |
+------+---------------+--------------------------+
|    1 | VASANT KUNJ   |                 833.3333 |
|    2 | HAUZ KHAS     |                1062.5000 |
|    3 | PITAMPURA     |                1090.9091 |
|    4 | ROHINI        |                1111.1111 |
|    5 | YAMUNA VIHAR  |                 916.6667 |
|    6 | KRISHNA NAGAR |                1000.0000 |
|    7 | VASUNDHARA    |                 909.0909 |
|    8 | PASCHIM VIHAR |                1375.0000 |
|    9 | SAKET         |                 833.3333 |
|   10 | JANAKPURI     |                 950.0000 |
+------+---------------+--------------------------+
(e)INSERT INTO SCHOOLBUS 
VALUES (11, 'Moti Bagh', 35, 32, 10, 'Kisan Tours', 35000);(f)1. SELECT SUM(Distance) FROM SCHOOLBUS WHERE Transporter= "Yadav Co.";Output+---------------+
| SUM(Distance) |
+---------------+
|            50 |
+---------------+
2. SELECT MIN(NoOfStudents) FROM SCHOOLBUS;Output+-------------------+
| MIN(NoOfStudents) |
+-------------------+
|                40 |
+-------------------+
3. SELECT AVG(Charges) FROM SCHOOLBUS WHERE Transporter = "Anand Travels";Output+--------------+
| AVG(Charges) |
+--------------+
|   81666.6667 |
+--------------+
4. SELECT DISTINCT Transporter FROM SCHOOLBUS;Output+----------------+
| Transporter    |
+----------------+
| SHIVAM TRAVELS |
| ANAND TRAVELS  |
| BHALLA CO.     |
| YADAV CO.      |
| SPEED TRAVELS  |
| KISAN TOURS    |
+----------------+

Question 20

Question 20Consider the CUSTOMERS table having the following records:Table: CUSTOMERSIDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.002Khilan25Delhi1500.003Kaushik23Kota2000.004Chaitali25Mumbai6500.005Hardik27Bhopal8500.006Komal22Bengaluru4500.007Muffy24Indore10000.00(a) Write an SQL query to display all records in ascending order of name.(b) Write an SQL query to display all records in descending order of name.(c) Write an SQL query to display all records in ascending order of name and descending order of age.(d) Write an SQL query to display maximum salary.(e) Write an SQL query to display minimum salary.(f) Write an SQL query to display total number of records.(g) Write an SQL query to display average salary.(h) Write an SQL query to display total salary of all the persons.(i) Write an SQL query to display names of those persons whose salary is greater than the average salary.(j) Write an SQL query to display details of those persons whose age is less than the average age.

Accepted Answer

(a)SELECT * FROM CUSTOMERS 
ORDER BY NAME;Output+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
|  5 | HARDIK   |  27 | BHOPAL    |   8500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
|  1 | RAMESH   |  32 | AHMEDABAD |   2000 |
+----+----------+-----+-----------+--------+
(b)SELECT * FROM CUSTOMERS 
ORDER BY NAME DESC;Output+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  1 | RAMESH   |  32 | AHMEDABAD |   2000 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  5 | HARDIK   |  27 | BHOPAL    |   8500 |
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
+----+----------+-----+-----------+--------+
(c)SELECT * 
FROM CUSTOMERS 
ORDER BY NAME ASC, AGE DESC;Output+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
|  5 | HARDIK   |  27 | BHOPAL    |   8500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
|  1 | RAMESH   |  32 | AHMEDABAD |   2000 |
+----+----------+-----+-----------+--------+
(d)SELECT MAX(SALARY) FROM CUSTOMERS;Output+-------------+
| MAX(SALARY) |
+-------------+
|       10000 |
+-------------+
(e)SELECT MIN(SALARY) FROM CUSTOMERS;Output+-------------+
| MIN(SALARY) |
+-------------+
|        1500 |
+-------------+
(f)SELECT COUNT(*) FROM CUSTOMERS;Output+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
(g)SELECT AVG(SALARY) FROM CUSTOMERS;Output+-------------+
| AVG(SALARY) |
+-------------+
|        5000 |
+-------------+
(h)SELECT SUM(SALARY) FROM CUSTOMERS;Output+-------------+
| SUM(SALARY) |
+-------------+
|       35000 |
+-------------+
(i)SELECT NAME 
FROM CUSTOMERS 
WHERE SALARY > (SELECT AVG(SALARY) FROM CUSTOMERS);Output+----------+
| NAME     |
+----------+
| CHAITALI |
| HARDIK   |
| MUFFY    |
+----------+
(j)SELECT * FROM CUSTOMERS 
WHERE AGE < (SELECT AVG(AGE) FROM CUSTOMERS);Output+----+----------+-----+-----------+--------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+--------+
|  2 | KHILAN   |  25 | DELHI     |   1500 |
|  3 | KAUSHIK  |  23 | KOTA      |   2000 |
|  4 | CHAITALI |  25 | MUMBAI    |   6500 |
|  6 | KOMAL    |  22 | BENGALURU |   4500 |
|  7 | MUFFY    |  24 | INDORE    |  10000 |
+----+----------+-----+-----------+--------+

Database Query using SQL

Assertions And Reasons

Casesource Based Questions

Multiple Choice Questions

Solutions To Unsolved Questions