Date: 1.8.
2024 XII COMPUTER SCIENCE (083)
UNIT TEST – 2 (DATABASE MANAGEMENT)
WORKSHEET – 2
1. Answer the following questions on the basis the given table.
Admno Name Subject Sex Avg
1001 Amit Math M 85.5
1002 Suman English F 90
Questions
a. How many attributes are there in above table?
b. How many tuples are there in above table?
c. What is the degree of above table?
d. What is the cardinality of above table?
e. Name the primary key in the table.
2. Write the output of the following: Select 76 + 75%4 from dual;
3. Write the queries of the following: Table : Student
a) Display all the records of table student.
b) Display Roll Number, Name and Class of table Student
c) Display records of students of class X.
d) Display details of Sumit. Q76. Display records of student paying fees less than
3000.
e) Display fee of Amit Q78. Display Class and percentage of Pushkar.
f) Delete record of Amit
g) Display the structure of table student
4. Identify the errors in the following query
a) Select * from Student where Name = NULL; Name is NULL;
b) Select max(salary) from employee group by designation where DOJ > “2020-
02-02”;
c) Select * from student where name is = “Amit”;
d) Select * from employee where salary between 1000, 2000;
e) Select * from employee where name = “A%”;
f) Select Name , Subject from student where average >20 and < 30;
g) Select * from Student where Name = “Amit” and Name =”Sumit”;
h) Select highest(Salary) from employee;
i) Select all from student;
j) j) Update table student set Name = “Sunita” where admno = 1234;
5. Write the output of the following on the basis of given Table : Product
a) Select max(price) from product;
b) Select avg(price) from product;
c) Select min(qty) from product;
d) Select count(*) from product;
e) Select count(Qty) from product;
f) Select distinct(price) from product;
g) Select count(distinct(price)) from product;
h) Select price * Qty from product;
i) Select sum(price) from product;
j) j) Select sum(price) where Qty > 30;
6. Write the queries of the following on the basis of given table : Faculty
a)Display details of Faculties who joins after 1990.
b)Display details of Faculties whose salary is more than 20000.
c)Show the name of faculties whose DOJ is 12-10-1980.
d)Display the detail of faculties whose name start from alphabet ‘A’.
e)Display details of faculties whose salary is greater than 25000 and name ends
with ‘n’.
f) Count number of faculties whose name starts with ‘S’ and salary more than
40000.
g) Display all records in increasing order of name.
h) Display details of faculties who earns maximum.
i) Display name of lowest earning faculty.
j) Display “Annual Salary” of all faculties. (Given salary is monthly)
7. Observe the given Table TEACHER and give the output of question (i)
and (ii)
TEACHER_COD TEACHER_NAM DOJ
E E
T001 ANAND 2001-01-30
T002 AMIT 2007-09-05
T003 ANKIT 2007-09-20
T004 BALBIR 2010-02-15
T005 JASBIR 2011-01-20
T006 KULBIR 2008-07-11
i)SELECT TEACHER_NAME,DOJ FROM TEACHER WHERE
TEACHER_NAME LIKE “%I%”
ii)SELECT * FROM TEACHER WHERE DOJ LIKE “%-09-%”;
8. Suppose a table BOOK contain columns (BNO, BNAME, AUTHOR,
PUBLISHER), Raj is assigned a task to see the list of publishers,
when he executed the query as:
SELECT PUBLISHER FROM BOOK;
He noticed that the same publisher name is repeated in query output.
What could be possible solution to get publisher name uniquely? Rewrite
the following query to fetch unique publisher names from table.
9. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii)
which are based on tables
TABLE: TRANSACT
TRNO ANO AMOUNT TYPE DOT
T001 101 2500 Withdraw 2017-12-21
T002 103 3000 Deposit 2017-06-01
T003 102 2000 Withdraw 2017-05-12
T004 103 1000 Deposit 2017-10-22
T005 102 12000 Deposit 2017-11-06
(i) To display details of all transactions of TYPE Withdraw from
TRANSACT table
(ii) To display ANO and AMOUNT of all Deposit and Withdrawals done
in month of “May” 2017 from table TRANSACT
(iii) To display first date of transaction (DOT) from table TRANSACT
for Account having ANO as 102
(iv) To display ANO, ANAME, AMOUNT and DOT of those persons
from ACCOUNT and TRANSACT table who have done
transaction less than or equal to 3000
(v) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS
NOT IN ('CHENNAI', 'BANGALORE');
(vi) SELECT DISTINCT ANO FROM TRANSACT
(vii) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT
GROUP BY ANO HAVING COUNT(*)> 1
SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT
WHERE DOT <= '2017-10-01'
10. Consider the following tables EMP and SALGRADE, write the query for
(i) to (vi) and output for (vii) to (x)
TABLE: EMPLOYEE
ECODE NAM DESIG SGRAD DOJ DOB
E E
101 Vikrant Executive S03 2003-03-23 1980-01-13
102 Ravi Head-IT S02 2010-02-12 1987-07-22
103 John Receptio S03 2009-06-24 1983-02-24
n
105 Azhar GM S02 2009-08-11 1984-03-03
108 Priyam CEO S01 2004-12-29 1982-01-19
TABLE: SALGRADE
SGRADE SALARY HRA
S01 56000 18000
S02 32000 12000
S03 24000 8000
(i) To display details of all employee in descending order of their DOJ
(ii) To display NAME AND DESIG of those employees whose sgrade
is either “S02” or “S03”
(iii) To display NAME, DESIG, SGRADE of those employee who joined
in the year 2009
(iv) To display all SGRADE, ANNUAL_SALARY from table
SALGRADE [where ANUAL_SALARY= SALARY*12]
(v) To display number of employee working in each SALGRADE from
table EMPLOYEE
(vi) To display NAME, DESIG, SALARY, HRA from tables
EMPLOYEE and SALGRADE where SALARY is less than 50000
(vii) Select MIN(DOJ), MAX(DOB) from employee;
(viii)Select SGrade,Salary+HRA from SalGrade where Sgrade=‟S02”
(ix) Select count(distinct sgrade) from employee
(x) Select sum(salary), avg(salary) from salgrade
