JAVA NET BEANS -call for classes: 9717570033: January 2013

1) Answer the following questions:

a) What is the purpose of ALTER TABLE Command? [2]

b) Why do we use ROLLBACK statement? Explain in brief with the help of an example [2]

c) After creating the “school” database you want to use it. Write the command that you. should

give. [1]

d) Explain the two wildcard character – and % used with the LIKE clause [2]

e) What are joins? Why are they used? [2]

f) What is the importance of group function in MySQL? [2]

2)Answer the following questions:

(a) Write the difference between COMMIT and ROLLBACK [2]

Consider the following tables PRODUCT and answer (b) and (c) part of this question

Relation :PRODUCT

PCODE PNAME COMPANY PRICE STOCK MANUFACTURE WARRANTY

P001 TV BPL 10000 200 12-JAN-2008 3

P002 TV SONY 12000 150 23-MAR-2007 4

P003 PC LENOVO 39000 100 09-APR-2008 2

P004 PC COMPAQ 38000 120 20-JUN-2009 2

P005 HANDYCAM SONY 18000 250 23-MAR-2007 3

(b) Write MySQL commands for following Statements

i. To show details of all the PC with stock more than 110. [1]

ii. To list the company which gives warranty for more than 2 years. [1]

iii. To find stock value of the BPL company where stock value is sum of the

products of price and stock. [1]

iv. To show number of products from each company. [1]

v. To count the number of PRODUCTS which are manufactured in 2009. [1]

vi. To show the PRODUCT name which are within warranty as on date. [1]

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

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

(iii) select AVG (price) from PRODUCT where Company=”SONY”;

(iv) Select MIN (price) from PRODUCT where stock<200;

3)Answer the following questions:

a) Study the following table STAFF and Salary and write MySQL command for the questions (i)

to (iv)and give output for the (v) to(vi)

TABLE: STAFF

ID Name DEPT SEX EXPERIENCE

101 Siddharat Sales M 12

104 Raghav Finance M 6

107 Naman Research M 10

114 Nupur Sales F 3

109 Janvi Finance F 9

105 Rama Research M 10

117 James Sales F 3

111 Binoy finance F 12

130 Samuel Sales M 15

TABLE: SALARY

ID Basic Allowance Commission

101 15000 1500 800

104 17000 1200 500

107 16000 1000 200

114 20000 2200 550

109 18000 1000 250

105 15000 1200 150

117 18000 1700 100

111 20000 1500 300

130 18000 1200 500

(i) Display NAME of all the staff who is in SALES having more than 10 year experience from the

table staff. [1]

(ii) Display the average Basic Salary of all staff working in ”Finance” department using the table

staff and salary. [1]

(iii)Display the minimum ALLOWANCE of female staff. [1]

(iv) Display the highest commission among all male staff [1]

(v) Select count(*) from STAFF where sex=’F’; [1]

(vi) SELECT NAME,DEPT,BASIC FROM STAFF,SALARY WHERE DEPT=’SALES’ AND

STAFF.ID=SALARY.ID; [1]

(b) Answer the question based on the table VOTER given below:

Table : VOTER

Column Name Data type Size Constraints Description

V_id BIGINT 8 Primary key Voter identification

Vname VARCHAR 25 Not null Name of the voter

Age INT 3 Check>17 Age should not less than equal

to 17

Address VARCHAR2 30 Address of voter

Phone VARCHAR 10 Phone number of the voter

(i) Write the command to delete all the rows of particular voter from the table voter where

voter ID between 10 and 20. [1]

(ii) Delete the table physically. [1]

(iii)Write SQL query to add a new column called Phno

q) You need to remove all the rows from the Sales_HIST table. You want to release the storage

space but do not want to remove the table structure. What is the solution to the problem? [2]

b. Differentiate between CHAR and VARCHAR datatypes? [3]

c. Predict the output: [4]

i. Select round(29.21,1), round(32.76,1);

ii. Select power(2,5);

iii. Select concat(‘catch a ’ ,concat( ‘falling’, ‘star’));

iv. Select length(trim( ‘……..to be continued…’));

v SELECT DAYOFMONTH(‘2009-08-24’);

vi Select concat(‘catch a ’ ,concat( ‘falling’, ‘star’));

Vii. SELECT MID(‘STUDENTS’,2,3);

d Define Equi join and non Equi join

4). (a)If database “Emplyoee” exists, which Mysql command helps you to starts working in that database?

(b) Mohit created a table in Mysql .later on he found that there should have been another column in the table. Which commands should he use to add another column to the table? 1

Select* from book where Price=NULL;

d) Which command is used in MySql to make the changes in database permanents? 1

(e)The itemno and cost column of a table “ITEM” are given below.

itemno	Cost
101	5000
102	NULL
103	4000
104	6000
105	NULL

f) Find the output of the following queries:

i)SELECT AVG(cost) FROM ITEMS;

ii) SELECT cost+100 FROM ITEM WHERE itemno>102;

(g) A table “Animals“ in a database has 3 columns and 10 records. What is the degree and cardinality of this table? What will happen if we add 2 more rows 2

(h) Differentiate DDL and DML commands with examples of each. 2

(i) What are multirow functions?

Q 5 The Pincode column of table ‘Post’ is given below-

Pincode

110001

120012

300048

281001

i. SELECT Pincode from Post where Pincode like = “ %1” ;

ii. SELECT Pincode from Post where Pincode Like = “ 0 %” ;

Q 6.

a) If a database “Library” exists. Write the command to start working in this database.

b) While creating a table “MobDet”, Kavita forgot to set primary key for the table. Write the statement to set the column MobileNo as the primary key of the table.

c) Avani has created a table named “Doctor”, she wants to increase the OPDCharge by 25% of “Nephro” and “Cardio” department. She wrote a query-

UPDATE Nephro,Cardio SET OPDCharge=25% WHERE department IN (‘Nephro’,’Cardio’);

Help Avani to run the query by removing the errors from the query and write corrected query.

d) Abhi wants to undo the changes made during the transaction execution. What command should Abhi use for this purpose?

e) Identify the candidate key(s) in the following table.

Relation: Data

EmpNo

Name

Designation

MobileNo

PANCardNo

Salary

BankAccountNo

f) GarCode, GarName, Price and FabrCode of table “GARMENT” are given below-

GarCode	GarName	Price	FabrCode
10015	Informal Pant	1899	F02
10089	Formal Pant	1295	F01
10075	Shirt	690	F01
10036	Frock	690	F04

Based on this information, find output of the following queries.

a) SELECT COUNT(Distinct FabrCode)) FROM GARMENT;

b) SELECT GarName FROM GARMENT WHERE FabrCode NOT LIKE ‘__1’;

g) What is the role of UNIQUE constraint? How is PRIMARY KEY constraint different from UNIQUE constraint?

7) Predict output

ii) SELECT TRUNCATE(170,-2);

iii) SELECT INSTR(‘Coordination ‘,’o’);

iv) SELECT CONCAT(‘India’,NULL,’Australia’);

8) Consider the tables given below-

Table : Staff

StaffId	Name	Dept	Gender	Experience
1125	Noopur	Sales	F	12
1263	Kartik	Finance	M	6
1452	Palak	Research	F	3
236	Nayan	Sales	M	8
366	Anvashan	Finance	M	10
321	Sawan	Sales	M	7

Table : Salary

StaffId	Basic	Allowance	CommPer
1125	14000	1500	9
1263	25000	2800	6
236	13500	1400	5
321	12000	1500	5
366	26100	3100	12

With reference to above tables, write commands in SQL for (i) and (ii) and output for (iii)-

(i) To display name of all the staff that are in Sales having more than 9 years experience and commission percentage is more than 8.

(ii) To display average salary of staff working in Finance department. (Salary=Basic+allowance)

(iii) SELECT name, Basic from Staff, Salary WHERE Dept=’Sales’ and Staff.StaffId=Salary.StaffId;

(iv) How many rows and how many columns will be there in the Cartesian product of these two tables?

9)Consider the table EXAM given below. Write commands in MySql for (i) to (iv) and output for (v) to (viii).

Table: EXAM

No.	Name	Stipend	Subject	Average	Division
1	Karan	400	English	15	FIRST
2	Aman	680	Maths	24	FIRST
3	Javed	500	Accounts	NULL	FIRST
4	Bishakh	200	IP	20	SECOND
5	Sugandha	400	History	10	THIRD
6	Suparna	NULL	Geo	5	THIRD
7	Ankit	400	NULL	10	THIRD

(i) To list the names of those students, who have obtained Division as FIRST in the ascending order of NAME.

(ii) To display a report listing NAME, SUBJECT and Annual stipend received assuming that the stipend column has monthly stipend.

(iii) To count the number of students, who have either Accounts or Informatics as Subject.

(iv) To insert a new row in the table EXAM: for columns(no,name,division)

6, “Mohan”,“SECOND”;

(v) SELECT AVG(Stipend) FROM EXAM

(vi) SELECT COUNT (DISTINCT Subject) FROM EXAM;

(vii) SELECT MIN (Average) FROM EXAM WHERE Subject=“English”;
(viii) SELECT name from exam where stipend between 200 and 500;

(ix) To count the number of students according to their Division wise.

(x) Explain the significance of NULL

(xi) Remove column average from the table

(xii) Remove primary key from the table. (12)

10)a) What do you mean by referential integrity? How is it enforced in databases?

b)What is the foreign key?

c)Explain ddl,dml and tcl with example

d)Differentiate between drop and delete command.

Ans: Delete is a dml command whereas drop is a ddl command.

Delete will be used to remove the contents from the database /table whereas drop command is issued to delete the entire table with its structure . Drop command can also be used in conjuction with alter command to remove any column or constraint from the already defined table.

Example: delete from student where rollno=10;

Drop table student;

Alter table student drop rollno;

Alter table student drop primary key.

e)Find error in the following statement:

1) Delete * from student;

2) Delete rollno,name from student

11) Q7)A table named ITEM has the following contents:

+-------+--------------+----------+

| icode | iname | iprice |

+-------+--------------+----------+

| 101 | CHAIR | 1500.00 |

| 102 | DINING TABLE | 24000.00 |

+-------+--------------+----------+

Write the output that will be displayed by each SELECT statement as the SQL

statements given below are executed: