Relational Database and SQL Preeti Arora Class 11 Information Practices Solution

Relational Database and Structured Query Language Preeti Arora Class 11 Information Practices Solution

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

Q1. What is the difference between data and information?


Q2. What is database and database system? What are the elements of database system?


Q3. Why do we need a database?


Q4. What is database management system? Why do we need a DBMS?


Q5. Explain the difference between:
(a) Database and file
(b) Data and file


Q6. Describe the components of the database system.


Q7. What are the advantages and disadvantages of DBMS?


Q8. What is the main function of DBA?


Q9. What is data redundancy? How can it be controlled?


Q10. Write SQL queries to perform the following based on the table PRODUCT having fields as (prod_id, prod_name, quantity, unit_rate, price, city)
(i) Display those records from table PRODUCT where prod_id is more than 100.
(ii) List records from table PRODUCT where prod_name is ‘Almirah’
(iii) List all those records whose price is between 200 and 500.
(iv) Display the product names whose price is less than the average of price.
(v) Show the total number of records in the table PRODUCT.


Q11. Suppose that all the customers of a particular business live in states for which city name is unique. Given the following description for customer data:

CUST-ID, CUST-NAME, STREET, CITY, STATE, PHONE)

(a) List the most likely key for the primary key.
(b) List all the candidate keys and alternate keys.


Q12. Define the following terms:

(a) Relation
(b) Domain
(c) Tuple
(d) Attribute
(e) Degree
(f) Cardinality


Q13. Define the following:
(a) Primary key
(b) Candidate key
(c) Alternate key
(d) Foreign key


Q14. Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (a) to (d) and give outputs for SQL queries (e) to (g).

Table: STORE

Table: SUPPLIERS

(a) To display details of all the items in the Store table in ascending order of LastBuy.
(b) To display Itemno and item name of those items from store table whose rate is more than 15 rupees.
(c) To display the details of those items whose supplier code is 22 or Quantity in store is more than 110 from the table Store.
(d) To display minimum rate of items for each Supplier individually as per Scode from the table Store.
(e) SELECT COUNT (DISTINCT Scode) FROM STORE;
(f) SELECT Rate * Qty FROM STORE WHERE Itemno = 2004;
(g) SELECT MAX(LastBuy) FROM STORE;


Q15. Write SQL commands for (i) to (v) on the basis of relations given below:

Table:- BOOKS

Table:- ISSUED

(i) To show the books of FIRST PUBL. Publishers written by P. Purohit.
(ii) To display cost of all the books published for FIRST PUBL.
(iii) Depreciate the price of all books of EPB publishers by 5%.
(iv) To show total cost of books of each type.
(v) To show the details of the costliest book.


Q16. Write SQL commands for (a) to (f) and write output for (g) on the basis of PRODUCTS relation given below:

Table:- PRODUCTS TABLE

(a) To show details of all PCs with stock more than 110.
(b) To list the company which gives warranty of more than 2 years.
(c) To find stock value of the BPL Company where stock value is the sum of the products of price and stock.
(d) To show number of products from each company.
(e) To count the number of PRODUCTS which shall be out of warranty on 20-NOV-2020.
(f) To show the PRODUCT name of the products which are within warranty as on date.
(g) Give the output of the following statements:
(i) Select COUNT(distinct company) from PRODUCT.
(ii) Select MAX(price)from PRODUCT where WARRANTY<=3


Q17. What are DDL and DML?


Q18. Differentiate between primary key and candidate key in a relation.


Q19. What do you understand by the terms Cardinality and Degree of a relation in relational database?


Q20. Differentiate between DDL and DML. Mention the two commands for each category.


Q21. Consider the given table and answer the questions.

Table: SchoolBus

(a) To show all information of students where capacity is more than the no. of students in order of rtno.
(b) To show Area_covered for buses covering more than 20 km., but charges 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, "Motibagh",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 travels";
(ii) select min(noofstudents) from schoolbus;
(iii) select avg(charges) from schoolbus where transporter = "Anand travels";
(g) Select distinct transporter from schoolbus;