Relational Database and SQL Preeti Arora Solution Class 12 Computer science

Relational Database and SQL Preeti Arora Solution || Relational Database and SQL || Preeti Arora || Class 12 || Unsolved Question || Computer science || Solution

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

Q1. What is an Alternate Key?


Q2. What are views? How are they useful?


Q3. Define the following terms:
(a) Relation
(b) Tuple
(c) Attribute
(d) Domain


Q4. What do you understand by the terms candidate key and cardinality of a relation in a relational database?
 

Q5. What is SQL? What are different categories of commands available in SQL?


Q6. What is a database system? What is its need?


Q7. Differentiate between DDL and DML commands.


Q8. What is a datatype? Name some datatypes available in MySQL.


Q9. Differentiate between char and varchar datatypes.


Q10. Which operator concatenates two strings in a query result?


Q11. How would you calculate 13*15 in SQL?


Q12. Which keywords eliminate redundant data from a query?


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



Q14 Define the following terms:

(i) Database
(ii) Data Inconsistency
(iii) Primary Key
(iv) Candidate Key
(v) Indexes



Q15. Consider the following tables STORE and SUPPLIERS. Write SQL commands for the statements (i) to (iv) and give  outputs for SQL queries (v) to (viii).

Table: Store

Table: SUPPLIERS

(i) To display details of all the items in the Store table in ascending order of LastBuy.
(ii) To display Itemno and item name of those from Store table whose rate is more than 15 rupees.
(iii) To display the details of those items whose supplier code is 22 or quantity in store is more than 110 from the table Store.
(iv) To display minimum rate of items for each Supplier individually as per Scode from the table Store.
(v) SELECT COUNT(DISTINCT Scode) FROM STORE;
(vi) SELECT Rate*Qty FROM STORE WHERE Itemno = 2004;
(vii) SELECT Item, Sname FROM STORE S, SUPPLIER P WHERE S.Scode = P.Scode AND ItemNo = 2006;
(viii) SELECT MAX(LastBuy)FROM STORE;





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

BOOKS

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 display the BOOK_NAME and price of the books, more than 3 copies of which have been issued.
(v) To show total cost of books of each type.
(vi) To show the details of the costliest book.





Q17. Write SQL commands for (i) to (vi) and write output for (vii) on the basis of PRODUCTS relation given below:

PRODUCTS TABLE

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



Q18. What are DDL and DML?


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


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


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



Q22. Consider the given table and answer the questions.

(i) To show all information of students where capacity is more than the no. of students in order of rtno.
(ii) To show area_covered for buses covering more than 20 km., but charges less than 80000.
(ii) To show transporter-wise total no. of students travelling.
(iv) To show rtno, area_covered and average cost per student for allroutes where average cost per student is --- charge / noofstudents.
(v) Add a new record with the following data:
(11, "Motibagh", 35, 32, 10, "kisan tours", 35000)
(vi) Give the output considering the original relation as given:
(a) select sum(distance) from school bus where transporter= "Yadav travels";
(b) select min(no of students) from school bus;
(c) select avg(charges) from school bus where transporter = "Anand travels";
(d) select distinct transporter from school bus;




Q23. Write SQL Commands for (i) to (v) and write the outputs for (vi) to (vii) on the basis of the following table:

Table: FURNITURE

(i) To list the details of furniture whose price is more than 10000.
(ii) To list the Item name and Price of furniture whose discount is between 10 and 20.
(iii) To delete the record of all items where discount is 30.
(iv) To display the price of 'Baby Cot'.
(v) To list item name, type and price of all items whose names start with 'D'.
(vi) Select Distinct Type from Furniture;
(vii) Select Max(Price) from Furniture where DateofStock >' 2002-02-15';
(viii) Select Count(*) from Furniture where Discount < 25;



Q24. Write the outputs of the following:

(i) Select POWER(2,5);
(ii) Select ROUND(10.195,2);
(iii) Select TRUNCATE(10.195,2);
(iv) Select SUBSTR('COMPUTER SCIENCE',3,4);
(v) Select INSTR('Technical Teaching','ch');



Q25. Write SQL Commands for the following on the basis of the given table GRADUATE:

(i) List the names of those students who have obtained rank 1 sorted by NAME
(ii) Display a list of all those names whose AVERAGE is greater than 65.
(iii) Display the names of those students who have opted COMPUTER as a subject with an AVERAGE of more than 60.
(iv) List the names of all the students in alphabetical order.
(v) SELECT * FROM GRADUATE WHERE NAME LIKE "% 1 %";
(vi) SELECT DISTINCT RANK FROM GRADUATE;




Q26.
(a) What is the difference between Candidate key and Alternate key?
(b) What is the degree and cardinality of a table having 10 rows and 5 columns?
(c) For the given table, do as directed:

Table: STUDENT

(i) Write SQL query to create the table.
(ii) Write SQL query to increase the size of SNAKE to hold 30 characters.
(iii) Write SQL query to remove the column HOBBY.
(iv) Write SQL query to insert a row in the table with any values of your choice that can be accommodated there.





Q27. Write SQL queries based on the following tables:

PRODUCT:

CLIENT:

(i) Write SQL Query to display Product Name and Price for all products whose Price is in the range 50 to 150.
(ii) Write SQL Query to display details of products whose manufacturer is either XYZ or ABC.
(iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are not giving any discount.
(iv) Write SQL query to display ProductName and price for all products whose ProductName ends with 'h'.
(v) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose city is Delhi.
(vi) Which column is used as Foreign Key and name the table where it has been used as Foreign key.




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

Table: HOSPITAL

(a) To list the names of all the patients admitted after 15/01/98.
(b) To list the names of female patients who are in ENT department.
(c) To list the names of all patients with their date of admission in ascending order.
(d) To display Patient's Name, Charges, Age for only female patients.
(e) Find out the output of the following SQL commands:
(i) Select COUNT(DISTINCT charges) from HOSPITAL;
(ii) Select MIN(Age) from HOSPITAL where Sex = "F";