More on Class 12 Databases and SQL Solutions of Computer Science (Python). Questions and answers and solved assignments deals on structure query language for MySQL according to CBSE latest syllabus and NCERT Sumita Arora Book. Questions and Solutions are very useful for upcoming CBSE Board Exams.
Conceptual Questions / Short Answer : Databases and SQL Class 12
Q.1. What is the use of ORDER BY clause?
Answer: ORDER BY clause is used to order the resulting table in the customized form according to variables. Example like ascending, desending,byname and other values.
Q.2.What is the default sort order of ORDER BY clause?
Answer: To sort the result set in ascending order as default sort order.
Q.3.Which function do you use in ORDER BY clauses to specify custom sort order?
Answer: A order() in a table can be equal, grater than , sorter than and other. Then this order can be given by ASC or DESC keyword.
Q.4. Write an example query that sorts on three columns.
Answer: SELECT * FROM MARKS ORDER BY PROJECT, SCHOOL ,ROLLNO ;
Q.5.Write a query that sorts the data of table student on the basis of Project-Group (in ascending order), section (in descending order), Marks (in descending order).
Answer : SELECT * FROM STUDENT ORDER BY PROJECT-GROUP ASC, SECTION DESC , MARKS DESC;
Q.6.What is the difference between HAVING and WHERE clause?
Answer: WHERE clause is applicable on individual rows whereas HAVING clause is applicable on a group of table.
Q.7.What is the use of GROUP BY clause?
Answer: GROUP BY clause make group from the table that have been given that by taking one column of connected data and accoring group them by using GROUP BY clause.
Q.8.What are aggregate functions? What is their use? Give some examples.
Answer:The function that works on the whole records of one column at a time is called aggregate functions.It is a multiple row function.
Uses:-
An aggregate Function retrieve a single value after performing calculation on set of values.
Example :-
SUM (),MIN(),MAX(),COUNT(),AVG() etc.
Q.9.What type of functions can you use with GROUP BY and HAVING clauses?
Answer :Aggregate Functions can be use with GROUP BY and HAVING clauses(row plus column order group). It is called row plus column group.
Application Based Questions:
Q.1. Which SQL statement allows you to find the highest price from the table BOOK_INFORMATION ?
(a)= SELECT B0OK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION ;
(b) = SELECT MAX(PRICE) FROM BOOK_INFORMATION ;
(c)= SELECT MAXIMUM( PRICE) FROM BOOK_INFORMATION ;
(d) SELECT PRICE FROM BOOK_ INFORMATION ORDER BY PRICE DESC ;
Answer: (b) = SELECT MAX(PRICE) FROM BOOK_INFORMATION ;
Q.2. Which SQL statement lets you find the sales amount for each store ?
(a) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
(b) = SELECT STORE_ID, SUM (SALES_AMOUNT ) FROM SALES ORDER BY STORE_ID;
(c) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;
(d) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID ;
Answer:(c) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;
Q.3.Which SQL statement lets you list all stores whose total sales amount is over 5000 ?
(a) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;
(b) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000 ;
(c) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID
(d) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID;
Consider this table —
Answer:(a) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;
Q4. Which SQL statement lets you find the total number of stores in the SALES table?
(a) = SELECT COUNT (STORE_ID) FROM SALES;
(b) = SELECT COUNT (DISTINCT STORE_ID) FROM SALES;
(c) = SELECT DISTINCT STORE_ID FROM SALES;
(d) = SELECT COUNT (STORE_ID) FROM SALES GROUP BY STORE_ID;
Answer:(d) = SELECT COUNT (STORE_ID) FROM SALES GROUP BY STORE_ID;
Q.5Which SQL statement allows you to find the total sales amount for Store ID 25 and the total sales amount for Store ID 45?
(a) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25,45) GROUP BY STORE_ID;
(b) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING STORE_ID IN (25,45);
(c) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25,45);
(d) = SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID;
TABLE:- SALES
| Column Name |
| STORE_ID |
| SALES_DATE |
| SALES_AMOUNT |
Answer:= SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING STORE_ID IN (25,45);
Q.6.What SQL statement do we use to find the average exam score for EXAM ID= 1 ?
(a) = SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS;
(b) = SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1;
(c) = SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;
(d) = SELECT COUNT (EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1;
Consider this table —
Answer: (c) = SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;
Q.7.Which SQL statement do we use to find out how many students took each exam?
a) = SELECT COUNT (DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
(b) = SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
(c) = SELECT EXAM_ID, COUNT (DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
(d) = SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
Consider this table —
Answer:(c) = SELECT EXAM_ID, COUNT (DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
Q.8.What SQL statement do we use to print out the record of all students whose last name starts with L?
(a) = SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE ‘L%’ ;
(b) = SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE ‘L’ ;
(c) = SELECT * FROM EXAM_RESULTS WHERE LNAME = ‘L’ ;
(d) = SELECT * FROM EXAM_RESULTS WHERE LNAME <> ‘L’ ;
Consider this table —
Answer: (a) = SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE ‘L%’ ;
Q.9. What is the result of the following SQL statement?
SELECT MAX(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM ID = 1 ;
(a) 90
(b) 85
(c) 100
(d) 95
Consider this table —
Answer: (d) 95
Q.10. Given the following table :
(i) = SELECT COUNT (DISTINCT SPORTS) FROM Club;
(ii) = SELECT MIN(Age) FROM CLUB WHERE Sex = ‘F’ ;
(iii) = SELECT AVG(Pay) FROM CLUB WHERE Sports = ‘KARATE’ ;
(iv) = SELECT SUM(Pay) FROM CLUB WHERE Datofapp > ’31/01/98’ ;
Answer :
(i)
| COUNT (DISTINCT SPORTS) |
| 4 |
(ii)
| MIN(Age) |
| 34 |
(iii)
| AVG(Pay) |
| 1100.0 |
(iv)
| SUM(Pay) |
| 7800 |
Q.11. Given the following table :
(i) = SELECT MIN(AvgMark) FROM STUDENT WHERE AvgMark < 75;
(ii) = SELECT SUM(Stipend) FROM Student WHERE Grade = ‘B’;
(iii) = SELECT AVG(Stipend) FROM Student WHERE Class = ’12A’;
(iv) = SELECT COUNT(DISTINCT) FROM Student ;
Answer =
(i)
| MIN(AvgMark) |
| 64.4 |
(ii)
| SUM(Stipend) |
| 1150 |
(iii)
| AVG(Stipend) |
| 475.0 |
(iv)ERROR
Q.12. In a Database, there are two tables given below :
Write SQL Queries for the following :
(i) = To display employee ids , names of employees, job ids with corresponding job titles.
(ii) = To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.
(iii) = To display names and corresponding job titles of those employees who have ‘SINGH’ (anywhere) in their names.
(iv) = Identify foreign key in the table EMPLOYEE.
(v) = Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table ‘EMPLOYEE’ .
Answer:
(i) SELECT EMPLOYEEID , NAME , EMPLOYEE.JOBID , JOBTITLE FROM EMPLOYEE NATURAL JOIN ON JOB ;
(ii) SELECT NAME , SALES , JOBTITLE FROM EMPLOYEE , JOB WHERE EMPLOYEE.JOBID = JOB.JOBID AND SALES > 1300000 ;
(iii) SELECT NAME , JOBTITLE FROM EMPLOYEE , JOB WHERE EMPLOYEE.JOBID = JOB.JOBID AND NAME LIKE “%SINGH%” ;
(iv) JOBID
(v) UPDATE EMPLOYEE SET JOBID = 104 WHERE EMPLOYEEID = “E4” ;
Q.13.Show the average salary for all departments with more than 3 people for a job.
Answer: SELECT AVG(SALARY) FROM DEPARTMENTS GROUP BY JOB HAVING COUNT(JOB) > 3 ;
Q.14. Display only the jobs with maximum salary greater than or equal to 3000.
Answer: SELECT JOB FROM DEPARTMENTS GROUP BY JOB HAVING MAX(SALARY) >= 3000 ;
Q.15.Find out number of employees having “Manager” as Job.
Answer: SELECT COUNT(*) FROM JOB WHERE JOBTITLE LIKE “%Manger%” ;
Q.16.List the count of employees grouped by deptno. (table EMPL)
Consider this table —
Answer:
SELECT Deptno , COUNT(*) FROM EMPL GROUP BY Deptno ;
| Deptno | COUNT(*) |
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
Q.17.List the sum of employees’ salaries grouped by department. (table EMPL)
Consider this table —
Answer:
SELECT Deptno , SUM(Sal) FROM EMPL GROUP BY Deptno ;
| deptno | Sum(sal) |
| 10 | 8750 |
| 20 | 10885 |
| 30 | 9400 |
Q.18.List the maximum salary of employee grouped by their department number.
Consider this table —
SELECT Deptno , MAX(sal) FROM EMPL GROUP BY Deptno ;
| Deptno | Max(sal) |
| 10 | 5000 |
| 20 | 3000 |
| 30 | 2850 |
For 19-24 Question
Q19. List the total of customers’ orders grouped by customer (id).
Answer:
SELECT COUNT(order) from CUSTOMERS
GROUP BY id;
Q.20.List all customers (name) who have orders (use EXISTS).
Answer:
SELECT NAME FROM CUSTOMERS WHERE EXISTS ( SELECT * FROM ORDERS WHERE CUSTOMERS.NAME = ORDERS.NAME ) ;
Q21. List the sum of the totals of orders grouped by customer and state.
Answer:
SELECT ORDERS.NAME , SUM (QUANTITY * PRICE) FROM ORDERS , PARTS , CUSTOMERS WHERE ORDERS.PARTNUM = PARTS.PARTNUM GROUP BY ORDERS.NAME , STATE ;
Q.22.List the sum of the totals of orders where this sum is greater than $1000 grouped by customer (id) and state and ordered by state.
Answer:
SELECT SUM(orders) from CUSTOMERS
where sum(orders)>1000
GROUP by id
ORDER by Slate;
Q.23.List the customers (name) and their orders’ details.
Answer: SELECT NAME , DESCRIPTION , PRICE FROM ORDERS , PARTS WHERE ORDERS.PARTNUM = PARTS.PARTNUM ;
Q.24.List the customers (name) and the total amount of all their orders.
Answer:
SELECT NAME , SUM (QUANTITY * PRICE) FROM ORDERS , PARTS WHERE ORDERS.PARTNUM = PARTS.PARTNUM GROUP BY NAME ;
Q.25.List the department names and the number of their employees.
Consider this table —
SALARYGRADE ( LOWSAL , HIGHSAL , GRADE )DEPT(DEPTNO. , DEPTNAME , LOCATION)
Answer:
SELECT DEPTNAME ,COUNT( EMPL.DEPTNO ) FROM EMPL , DEPT WHERE EMPL.DEPTNO = DEPT.DEPTNO ;
Q.26.List the employee names and the name of their departments.
Consider this table —
SALARYGRADE ( LOWSAL , HIGHSAL , GRADE )DEPT(DEPTNO. , DEPTNAME , LOCATION)
Answer:
SELECT ENAME , DEPTNO FROM EMPL , DEPTWHERE EMPL.DEPTNO = DEPT.DEPTNO ;
Thanks for reading More on databases and sql class 12 solutions.