CBSE Class 12 Computer Science HOTs Database and SQL

Unit 3 : Database and SQL

General Guidelines to solve questions based on Database Concepts:

1. To answer the questions on Database Concepts, your answer should be to the point.

2. In case of few students who know the concept but are not able to write the answer, the practice in writing will help them prepare those questions.

3. Your answer may be supported with examples, if possible and required.

4. Sometimes 2 marks questions are asked clubbing two terms/definitions or concepts.

General Guidelines to solve questions based on SQL:

1. Always terminate SQL statement by ; in your answer.

2. Sometimes character or date values are enclosed with in “ “. It is normally not accepted in SQL. You can always write your answer using ‘ ‘ for character & date values.

3. In case of date values { } are sometimes used. However ‘ ‘ should be used for dates also, as mentioned in above point.

4. Only data is case sensitive, columns and table names may be written in any case.

1 mark questions

Q1. Define the terms:

i. Database Abstraction

Ans: Database system provides the users only that much information that is required by them, and hides certain details like, how the data is stored and maintained in database at hardware level. This concept/process is Database abstraction.

ii. Data inconsistency

Ans: When two or more entries about the same data do not agree i.e. when one of them stores the updated information and the other does not, it results in data inconsistency in the database.

iii. Conceptual level of database implementation/abstraction.

Ans: It describes what data are actually stored in the database. It also describes the relationships existing among data. At this level the database is described logically in terms of simple data-structures.

iv. Primary Key

Ans : It is a key/attribute or a set of attributes that can uniquely identify tuples within the relation.

v. Candidate Key

Ans : All attributes combinations inside a relation that can serve as primary key are candidate key as they are candidates for being as a primary key or a part of it.

vi. Relational Algebra.

Ans : It is the collections of rules and operations on relations(tables). The various operations are selection, projection, Cartesian product, union, set difference and intersection, and joining of relations.

vii. Domain

Ans : it is the pool or collection of data from which the actual values appearing in a given column are drawn.

viii. Projection

Ans : It is the operation yielding a vertical subset of a given relation , i.e. data under specified columns , in contrast to the horizontal subset(rows) returned by a select operation.

Q2 Write the purpose of following relational algebra statements:

i. price>50 (PRODUCTS).

ii.  city=‘Chennai’ (PRODUCTS)

iii.  price>20 ^ price <45(SALES)

iv. price>50 (PRODUCTS).

Ans: To select/show those rows from the table PRODUCTS whose price is more than 50.

. city=‘Chennai’ (PRODUCTS)

Ans: To select/show those rows from the table PRODUCTS where city is Chennai.

. price>20 ^ price <45(SALES)

Ans: To select/show those rows from the table SALES where city is Chennai.

Q3 Write the expression in relational algebra to :

i. Show the tuples from PRODUCT table where cost of the product is more than 5000.

ii. Show the tuples from PRODUCT table where product_name is ‘TV’.

iii. Show the tuples pertaining to prices between 55 and 100 from the table Items.

iv. Show the tuples whose price is more than 55 or qty<10 from the table Items.

v. Show the supplier_name, city where price is more than 1000 from the table Items.

Ans1.: cost>5000(PRODUCTS)

(ii): product_name=‘TV’(PRODUCTS)

(iii) price>55^price<100(Items) . Assuming 55 and 100 not included.

      price>=55^price<=100(Items). Assuming 55 and 100 included.

(iv): price>=55 v qty<10(Items)

(v): supplier_name,city ( price>1000(Items))

1 MARK QUESTIONS FOR PRACTICE

(UNSOLVED)

Q1. Define constraints in database.

Q2. Define unique constraint.

Q3. Explain the basic difference between simple view and complex view.

Q4. What is default constraint?

Q5. What is the difference between where and having clauses?

Q6. What is the advantage of outer-join?

Q7. What is schema?

Q8. What is scalar expression in SQL?

Q9. Up to which level can you nest subqueries in SQL?

Q10. Write on working example of check constraint.

6/8 Marks Questions SQL

(Solved)

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

1.a) To show details of all the PC with stock more than 110.

Ans: select * from products where pname=’TV’ and stock>110;

b) To list the company which gives warranty for more than 2 years.

Ans: select company from products where warranty>2;

c) To find stock value of the BPL company where stock value is sum of the products of price and stock.

Ans: select sum(price*stock) from PRODUCTS where company=’BPL’;

d) To show number of products from each company.

Ans: select company,COUNT(*) from products group by company;

e) To count the number of PRODUCTS which shall be out of warranty on 20-NOV-2010.

Ans: select count(*) from products where (20-NOV-2010- manufacture)/365>warranty;

f) To show the PRODUCT name which are within warranty as on date.

Ans: select pname from products where (sysdate- manufacture)/365<warranty;

g). Give the output of following statement.

(i) Select COUNT(distinct company) from PRODUCT.

Ans: 4

(ii) Select MAX(price)from PRODUCT where WARRANTY<=3

Ans: 39000

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

i. To show the books of FIRST PUBL Publishers written by P.Purohit.

Ans: select * from books where publishers=’FIRST PUBL’

ii. To display cost of all the books written for FIRST PUBL.

Ans: select sum(price*qty) from books where publishers=’ FIRST PUBL’;

iii.Depreciate the price of all books of EPB publishers by 5%.

Ans: update books set price=price-0.5*price where publishers=’EPB’;

iv.To display the BOOK_NAME,price of the books whose more than 3 copies have been issued.

Ans: select BOOK_NAME,price from books, issued where books.book_id=issued.book_id and quantity_issued>3;

v.To show total cost of books of each type.

Ans: select sum(price*qty) from books group by type;

vi.To show the detail of the most costly book.

Ans: select * from books where book_id=(select book_id from books where price=select max(price) from books));

SQL Questions for practice (Unsolved)

Q1. Consider the following tables Employee and salary. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to viii

(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 name from employee where eid=(select eid from salary where basic= (select max(basic) from salary));

(vi) select max(basic) from salary where bonus >40;

(vii) Select count(*) from employee group by sex;

(viii) select Distinct deptid from Employee;

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

1. Show empno, name and salary of those who have Sports as hobby.

2. Show name of the eldest employee.

3. Show number of employee area wise.

4. Show youngest employees from each Native place.

5. Show Sno, name, hobby and salary in descending order of salary.

6. Show the hobbies of those whose name pronounces as ‘Abhay’.

7. Show the appointment date and native place of those whose name starts with ‘A’ or ends in ‘d’.

8. Show the salary expense with suitable column heading of those who shall retire after 20-jan-2006.

9. Show additional burden on the company in case salary of employees having hobby as sports, is increased by 10%.

10. Show the hobby of which there are 2 or more employees.

11. Show how many employee shall retire today if maximum length of service is 20 years.

12. Show those employee name and date of birth who have served more than 17 years as on date.

13. Show names of those who earn more than all of the employees of Sales dept.

14. Show names of those who earn more than at least one of the employees of Marketing dept.

15. Increase salary of the employees by 5 % of their present salary with hobby as Music or they have completed atleast 3 years of service.

Write the output of:

1. Select distinct hobby from personal;

2. Select avg(salary) from personal,job where personal.empno=job.sno and area in(‘Agra’,’Delhi’);

3. Select count(distinct Native_place) from personal.

4. Select name,max(salary) from personal,job where personal.empno=job.sno;

Now,

1. Add a new tuple in the table essentially with hobby as Music.

2. insert a new column email in job table

3. Create a table with values of columns empno,name, and hobby.

4. Create a view of personal and job details of those who have served less than 15 years.

5. Erase the records of employee from job table whose hobby is not Sports.

6. Remove the table personal.

Q3. With reference to the table below, answer the question that follow:( 8 Marks)

i. To show all information about the patients of cardiology department.

ii. To list the names of female patients who are in orthopedic department.

iii. To list names of all patients with their date of admission in ascending order.

iv. To display patient’s Name, Charges, AGE for only male patients only.

v. To count the number of patients with Age greater than 30.

vi. To insert a new row in the Hospital table with the following data:

11, ‘ Nipan ‘, 26 , ‘ENT’, ‘25/02/98’, 50, ‘ M ‘

vii. Give the output of following SQL statements:

a). Select COUNT(distinct Department) from Hospital;

b). Select MAX(Age) from Hospital where Sex = ‘M’;

c). Select AVG(Charges) from Hospital where Sex = ‘ F ‘;

d). Select SUM(Charges) from Hospital where Dateofadm < ‘2/08/98’;

Q4. Given the following LAB table, write SQL command for the questions (i) to

(iii) and give the output of (iv).

(i) To select the ItemName,which are within the Warranty period till present date.

(ii) To display all the itemName whose name starts with ‘C’.

(iii)To list the ItemName in ascending order of the date of purchase where quantity is more than 3.

(iv) Give the output of the following SQL commands:

(a) select min(DISTINCT Quantity) from LAB;

(b) select max(Warranty) from LAB;

(c) select sum(CostPerItem) from Lab.

Write the SQL commands for the following using above tables:

(i) To show firstname,lastname,address and city of all employees living in paris

(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 Employee and empsalary , 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’;