XII- IP HOME WORK FOR "ONE WEEK HOLIDAY"
HOME
ASSIGNMENT
(For XII
INFORMATICS PRACTICES 2012-13)
Q1. Write SQL commands for the queries given
below
Table: LIBRARY
|
No.
|
Title
|
Author
|
Subject
|
Publisher
|
Quantity
|
Price
|
|
1
|
Data Structure
|
Lipschute
|
DS
|
McGraw
|
4
|
217.00
|
|
2
|
DOS Guide
|
NORTRON
|
OS
|
PHI
|
3
|
175.00
|
|
3
|
Turbo C++
|
Robort Lafore
|
Prog
|
Galgotia
|
5
|
270.00
|
|
4
|
Dbase Dummies
|
Palmer
|
DBMS
|
PustakM
|
7
|
130.00
|
|
5
|
Mastering Windows
|
Cowart
|
OS
|
BPB
|
1
|
225.00
|
|
6
|
Computer Studies
|
French
|
FND
|
Galgotia
|
2
|
75.00
|
|
7
|
COBOL
|
Stern
|
Prog
|
John W
|
4
|
1000.00
|
|
8
|
Guide Network
|
Freed
|
NET
|
Zpress
|
3
|
200.00
|
|
9
|
Basic for Beginners
|
Norton
|
Prog
|
BPB
|
3
|
40.00
|
|
10
|
Advanced Pascal
|
Schildt
|
Prog
|
McGraw
|
4
|
350.00
|
a) To
display the title of all books with Price between 100 and 300.
b) To
display Title and Author of all the books having type Prog and published by
BPB.
c) To
display the list of all the books with price more than 130 in ascending order
of Qty.
d) To
display the list of all books whose quantity is less than 4.
e) To
display the publishers and the number of books of each Publisher in the table.
f) To
insert a new book in the table LIBRARY.
g) To
increase the price of the book title ‘TURBO C++’ by Rs.30.
h) To display the Title
and Total Price of all Computer
books.The Total Price is calculated as Price
* Qty.
Q2.Write the output of the following:
i. Select
MIN(Price) from Library;
ii. Select
Sum(Price * Qty) from Library where Qty > 3;
iii. Select
Avg(Price) from Library where Qty < 4;
iv. Select
Count(Distinct Publisher) from Library;
v. Select
CONCAT(Title, “**”,Author) from Library;
Q3.Write
the syntax of the following MYSQL function:
i. SUBSTR() MID() INSTR() CHAR() NOW()
|
10.
|
Q4. Answer the following questions ::Table: Employee
|
||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||
|
a) .
|
Give
a statement as follows:
CREATE
TABLE EMP( EMPNO INT(10) PRIMARY KEY, ENAME VARCHAR(30) NOT NULL, GENDER
CHAR(1) DEFAULT ‘M’, DEPTNO INT(6) REFERENCES DEPT(DEPTNO), SALARY INT
CHECK(SALARY>2000));
Identify the number and types of
constraints in the table EMPLOYEE.
|
||||||||||||||||||||||||||||||||
|
b).
|
Write
the MySQL command to ADD new column JOB VARCHAR (20) NOT NULL.
|
||||||||||||||||||||||||||||||||
|
c).
|
Write
the MySQL command to remove a GENDER column from Employee table
|
||||||||||||||||||||||||||||||||
|
Find
the output of the following commands:-
(d)SELECT
SUBSTR(ENAME,1,5), COMM FROM EMPLOYEE;
(e)SELECT
INSTR(ENAME, ‘I’), GENDER FROM EMPLOYEE;
(g)SELECT
TRUNCATE(SALARY,2) , ROUND(SALARY,-1) FROM EMPLOYEE;
(f)SELECT
EMPNO, ENAME, SALARY, IFNULL(COMM , ‘0.00’ ) FROM EMPLOYEE WHERE ENAME LIKE
‘---J%’;
|
|||||||||||||||||||||||||||||||||
Q5 . Write a SQL commands for table FURNITURE
Table: FURNITURE
|
ITEMNO
|
ITEMNAME
|
TYPE
|
DATEOFSTOCK
|
PRICE
|
DISCOUNT
|
|
INT
|
VARCHAR
|
VARCHAR
|
DATE
|
INT
|
INT
|
|
5
|
20
|
20
|
-
|
6
|
2
|
|
Primary Key
|
Not
Null
|
Not
Null
|
Default ’10-03-10’
|
Price
< 40,000
|
-
|
Write a MYSQL Command to create a
furniture table including all constraints.
For any queries/doubts feel free to write me @ Email ID : HSYADAV.KVS@GMAIL.COM
************************* THE END*************************
