Xii-
informatics practices (1st monthly
test)
Attempt any 10 questions : Max
Marks : 5 x 10 =50
1. Differentiate between :
a. Char and Varchar b. Primary Key Candidate Key and Foreign Key
2. What is a
NULLvalue?
3. Define SQL. How many types of statements
are there in SQL give at least one example in each category?
4. What do % and _ mean inside SELECT with
LIKE statement? Explain with the help of one examples of each.
5. After creating the "employee" database,
you want to use it. Write the command that you should give.
6. What will be the output of the
following :
i. SELECT ROUND(124.44) + POW(4,3) ;
ii. SELECT
LOWER(SUBSTR(LTRIM(‘ THE JAIN INTERNATIONAL SCHOOL’),4,10));
iii. SELECT
LEFT(TRIM(‘IPL2010-12MAR’),5);
iv. SELECT
MONTHNAME(19-03-2010);
v. SELECT CONCAT (LOWER
(‘class’),UPPER(‘XII’));
7. Write
SQL commands for the queries given from a to h shown below (Attempt any
5) 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 book 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.
8.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;
9.Write the syntax of the
following MYSQL function:
i.
SUBSTR() MID()
INSTR() CHAR() NOW()
|
10.
|
Answer the following
questions ::Table: Employee (Attempt any 5)
|
||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||
|
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%’;
|
|||||||||||||||||||||||||||||||||
11. 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.
