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]
(c)
Give the output of following MySQL statement. [4]
(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
(c) Remove
the errors from query and rewriting it.
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?
1
|
|
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
|
|||||||||||||||||||||
f) GarCode, GarName, Price and FabrCode of table “GARMENT” are given below-
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
Table : Salary
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
(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:
mysql> SELECT * FROM ITEM;
mysql> SET AUTOCOMMIT = 0;
mysql> INSERT INTO ITEM VALUES(103,'COFFEE TABLE',340);
mysql> SELECT * FROM ITEM;
mysql> ROLLBACK;
mysql> SELECT * FROM ITEM;
mysql> START TRANSACTION;
mysql> UPDATE ITEM SET IPRICE = IPRICE +200;
mysql> SAVEPOINT S1;
mysql> UPDATE ITEM SET IPRICE = IPRICE +400;
mysql> SELECT * FROM ITEM;
mysql> ROLLBACK TO S1;
mysql> SELECT * FROM ITEM;
Now verify the output by creating the table using MySQL and
executing the above
statements.
Q12) Write the purpose of inserting
Savepoints in a transaction.
No comments:
Post a Comment