Grouping Records, Joins in SQL Type B Sumita Arora Class 12 Computer science Solution

Grouping Records, Joins in SQL  || 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’ .

Q13. Consider the following tables Employee and Salary. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (i) to (vii)

Table: Employee

Table: Salary

(i) To display the frequency of employees department wise.
(ii) To list the names of those employees only whose name starts with 'H'
(iii) To add a new column in salary table. The column name is Total_Sal.

(iv) To store the corresponding values in the Total_Sal column.
(v) Select max(Basic) from Salary where Bonus > 40 ;
(vi) Select count(*) from Employee group by Sex;
(vii) Select Distinct Depid from Employee;





Q14. With reference to following relations PERSONAL and JOB answer the questions that follow:

Create following tables such that Empno and Sno are not null and unique, date of birth is after '12-Jan-1960', name is never blank, Area and Native place is valid, hobby, dept is not empty, salary is between 4000 and 10000.

Table: Personal

Table: job

(a) Show empno, name and salary of those who have Sports as hobby.
(b) Show name of the eldest employee.
(c) Show number of employee area wise.
(d) Show youngest employees from each Native place.
(e) Show Sno, Name, Hobby and Salary in descending order of Salary.
(f) Show the hobbies of those whose name pronounces as 'Abhay'.
(g) Show the appointment date and native place of those whose name starts with 'A' or ends in
'd'.
(h) Show the salary expense with suitable column heading of those who shall retire after 20-jan-
2006.
(i) Show additional burden on the company in case salary of employees having hobby as sports,
is increased by 10%.
(j) Show the hobby of which there are 2 or more employees.
(k) Show how many employee shall retire today if maximum length of service is 20 years.
(l) Show those employee name and date of birth who have served more than 17 years as on date.
(m) Show names of those who earn more than all of the employees of Sales dept.
(n) Increase salary of the employees by 5% of their present salary with hobby as Music or they
have completed at least 3 years of service.
(o) Write the output of:
(p) Add a new tuple in the table Personal essentially with hobby as Music.
(q) Insert a new column email in Job table.
(r) Create a table with values of columns empno, name, and hobby.
(s) Create a view of Personal and Job details of those who have served less than 15 years.
(t) Erase the records of employee from Job table whose hobby is not Sports.
(u) Remove the table Personal.

Q15. With reference to the table below, answer the questions that follow:

Table: Employees

Table : EmpSalary

Write the SQL commands for the following using above tables:

(i) To show firstname, lastname, address and city of all employees living in Pairs.
(ii) To display the content of Employees table in descending order of Firstname.
(iii) To display the firstname, lastname and total salary of all managers from the tables Employes and Emp Salary, where total salary is calculated as Salary + Benefits.
(iv) To display the maximum salary among managers and clerks from the table EmpSalary.

Give the Output of following SQL commands:

(i) Select firstname, Salary from Employees, Empsalary where Designation = 'Salesman' and Employees. Empid = Empsalary.Empid;
(ii) Select count(distinct designation) from EmpSalary:
(iii) Select designation, sum(salary) from EmpSalary group by designation having count(*) > 2;
(iv) Select sum(Benefits) from EmpSalary where Designation = 'Clerk';

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


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

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

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


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


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

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

Q 23 =  List the sum of the totals of orders grouped by customer and state.

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.