Database Query using SQL Preeti Arora Class 12 Information Practices (IP) Solution

Database Query using SQL Preeti Arora Class 12 Information Practices (IP) Solution

Note :-  Please Click on Question to Answer of that Question !!!

Q1. Consider the table "Item" given below and give the outputs on the basis of it:

Table: ITEM

(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;


Q2. What are single row and multiple row functions?


Q3. What is the significance of GROUP BY clause in an SQL query?


Q4. What is the difference between WHERE and HAVING clause in SQL select command?


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


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


Q7. Define a function. Why are they useful?


Q8. Write commands to display the system date.


Q9. Write a command to display the name of the current month.


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


Q11. What is the difference between SYSDATE() and NOW() function?


Q12. Consider two fields-B_date, which stores the birth date, and J_date, which stores the joining date of an employee. Write commands to find out and display the approximate age of an employee as on joining date.


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


Q14. Consider the given table Faculty and answer the questions that follow:

Table: FACULTY

(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 id 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.



Q15. Consider the following EMP and DEPT tables:

Table: EMP
Table: DEPT

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



Q16. Write a MySQL command for creating a table PAYMENT whose structure is given below:
Table: PAYMENT



Q17. Consider the table Product shown below:

Table: PRODUCT

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



Q18. Consider the table RESULT given below. Write commands in MySQL for (a) to (d) and output for (e) to (g):

Table: RESULT

(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 stipend the 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 EXAM: 6, "Mohan", 500, "English", 73, "Second"
(e) select avg(Stipend) from EXAM where DIVISION = "THIRD";
(f) select count(distinct Subject) from EXAM;
(g) select min(Average) from EXAM where Subject = "English";



Q19. Write the SQL query commands based on the following table:

Table: SCHOOLBUS

(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 traveling.
(d) To show rtno, area covered and average cost per student for all routes where average cost per student is charges/no. of students.
(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:
(i) select sum(distance) from schoolbus where transporter = "Yadav Co.";
(ii) select min(noofstudents) from schoolbus;
(iii) select avg(charges) from schoolbus where transporter = "Anand Travels": (iv) select distinct transporter from schoolbus;



Q20. Answer the questions based on the table given below:

Table: STUDENT

(a) Write the SQL command to create the above table with constraints.
(b) Insert 2 records with relevant information in the table Student.
(c) Display all the records of the table Student.
(d) Delete the student whose Roll no is 100.
(e) Change the stream of student to 'Computer' whose Roll no. is 536.
(f) Add one column email of data type VARCHAR and size 30 to the table Student.
(g) View structure of the table created by you.
(h) Drop the table Student.



Q21. Consider the CUSTOMERS table having the following records:

Table: CUSTOMERS

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



Q22. Consider the following tables WORKER and PAYLEVEL and answer the questions:

Table: WORKER
Table: PAYLEVEL

(a) To display details of all workers in descending order of DOB.
(b) To display the PLEVEL and number of workers in that PLEVEL.
(c) To display the PLEVEL and number of workers in that PLEVEL whose pay is greater than 15000.
(d) to display NAME and DESIG of those workers, whose PLEVEL is either P001 or P002.
(e) Give the output of the following SQL queries:
(i) select count(PLEVEL), PLEVEL from WORKER group by PLEVEL;
(ii) select max(DOB), min(DOB) from WORKER;



Q23. Consider the tables given below and answer the questions that follow:

Table: EVENT
Table: CELEBRITY

(a) Name the Primary keys in both the tables and the Foreign key in 'Event' table. Can NumPerformers (Number of performers) be set as the Primary key? Give reason.
(b) How many rows will be present in the Cartesian join of the above-mentioned two tables?
(c) Write the commands in SQL:
(i) To display Eventld, Event name, Celebrityld for only those events that have more than 10 performers.
(ii) To display Celebrityld and names of celebrities who have "Khan" anywhere in their names.
(iii) To display names of celebrities and fee charged for those celebrities who charge more than 200000.



Q24. Consider the following structure of TEACHER and STUDENT table:

Table: TEACHER
Table: STUDENT

Write the SQL commands to get the following:
(a) Show the name of students enrolled in Science stream.
(b) Count the number of students in Commerce stream.
(c) Count the number of teachers in each designation.
(d) Display the maximum pay of teacher who is teaching English.
(e) Display the names of students who are taught by "Anand Mathur".
(f) Display the names and designations of teachers who are teaching a student named "Amit".
(g) Find out the name of the teacher who is getting the highest pay.
(h) Find out the cities of teachers who are teaching Maths.
(i) Find out the name of teacher who is getting the lowest salary among PGTS.
(j) Display the list of students who are taught by PGTs only.



Q25. Consider the following table and answer the questions that follow.

Table: TEACHER

Write the command/output for the following:
(a) To display all information about the teacher of PGT category.
(b) To list the names of female teachers of Hindi department.
(c) To list names, departments and date of hiring of all the teachers in ascending order of date of joining.
(d) To count the number of teachers in English Department.
(e) Display the department and hire date of all the female teachers whose salary is more than 25000.
(f) Display the list of teachers whose name starts with J.
(g) select count(*) from TEACHER where Category='PGT';
(h) select avg(salary) from TEACHER group by Gender;



Q26. Write SQL commands and the output for the following queries:

Table: SPORTS

(a) Display the names of the students who have grade 'A' in either Game1 or Game2 or both.
(b) Display the number of students having game 'Cricket'.
(c) Display the names of students who have the same game for both Game1 and Game2.
(d) Display the games taken by the students whose name starts with 'A'.
(e) Give the output of the following sql statements:
(i) select count(*) from SPORTS;
(ii) select distinct Class from SPORTS;
(iii) select max(Class) from STUDENT;
(iv) select count(*) from SPORTS group by Game1;



Q27. Consider the following table:

Table: ITEM

Write queries based on the table ITEM:
(a) Display the information of all the items.
(b) Display item name and price value.
(c) Display soap information.
(d) Display the item information whose name starts with letter 's'.
(e) Display a report with item number, item name and total price (total price = price * quantity).
(f) select distinct price from item;
(g) select count(distinct price) from item;