More on SQL || Type B || Sumita Arora || Class 12 || Computer science || Information practices || Solution

More on SQL || Type B || Sumita Arora || Class 12 || Computer science || Information practices || Solution

Q1= 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 ;




Q2 = 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 ;




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;



Q  4=  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;



Q 5 = Which 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;



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;




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;





Q8 =  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' ;




Q9 =  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




Q10  = Given the following table : 
Give the output of following SQL statements: (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’ ;







 

11. Given the following table :Give the output of following SQL statements:(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 ;
 



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’ .





Q 13 = Show the average salary for all departments with more than 3 people for a job.



Q 14 =  Display only the jobs with maximum salary greater than or equal to 3000.

Q 15 = Find out number of employees having "Manager" as Job.

 
Q 16 = List the count of employees grouped by deptno. (table EMPL)




Q 17 =  List the sum of employees' salaries grouped by department. (table EMPL)



Q 18 = List the maximum salary of employee grouped by their department number.

Q 19 = List the total of customers' orders grouped by customer (id).

Q 20 = List all customers (name) who have orders (use EXISTS).

Q 21 =  List the sum of the totals of orders grouped by customer and 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.

Q 23 = List the customers (name) and their orders' details.

Q 24 = List the customers (name) and the total amount of all their orders.




Q 25 = List the department names and the number of their employees.

Q26 = List the employee names and the name of their departments.