CLASS XI SQL TEST

Answer the following questions:
(a)	Compare DDL and DML statements of SQL.
(b)	Swati needs to remove all the rows from SALES table to release the storage space. But she does not want to remove the table structure. Which statement should she use?
(c)	Meena uses a EMP table with following columns: NAME ,SAL,ID,DNAME She needs to display names of employees who have not been assigned any department or have been assigned “pathology” department. Pathology course’s names end with “Pathology”. She wrote the following query: SELECT NAME,SAL FROM EMP,COURSE WHERE DNAME = NULL OR DNAME = “%pathology”; But the query is not producing result. Identify the problem.
(d)	What is the importance of primary key in a table? Explain with example.
A table ACCOUNT in a database has 6 columns and 60 rows. The DBA has added 3 more columns and 50 more rows to the table. But the table has about 15 records where balance is null. What is the degree and cardinality of this table now ?

·         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 


Write MySQL commands for following Statements
i.To show details of all the PC with stock more than 110.       
ii.To list the company which gives warranty for more than 2 years


iii.To show the PRODUCT name which are within warranty as on date.
iv) Show the products whose warranty is over

Q1)Consider the table Hospital given below.

No	Name	Age	DEPARTMENT	DateOfAdm	Charges	Sex
1	Sandeep	64	Surgery	23/02/98	300	M
2	Ravina	24	Orthopedic	20/01/98	200	F
3	Karan	45	Orthopedic	10/02/98	200	M
4	Tarun	12	Surgery	01/01/98	300	F
5	Zubin	36	ENT	12/01/98	250	M
6	Ketaki	16	ENT	12/02/98	300	F
7	Ankita	29	Cardiology	20/02/98	800	F
8	Zareen	45	Gynecology	22/02/98	Null	F
9	Ankit	19	Cardiology	13/01/98	800	M
10	Shailya	31	Medicine	19/02/97	400	F

—  To show all information about the patients of cardiology department.

—  To list the names of female patients who are in orthopaedic department.

—  To display Patient’s name, charges, Age for  male and female patients.

—  Increase the charges of male patient in ENT department by 3%.

—  Add another column email_id with suitable data type.

—   Delete the records of all female patients in Surgery department.

—  Display a report listing name, age, charges and amount of charges including VAT as 2%  on charges name the column as total charges and keep the data in ascending order of name.

—    Display department name where charges are null.

Insert new row with following values dept="ENT"  patient name="mohit"

—   Display the details of all the patients who are hospitalised in 1998.

—   Display the charges of various departments .A charge amount should appear only once.  

QGive output:

SELECT ASCII('Q')
FROM DUAL;

SELECT CONCAT(name, ' is a good boy') "Result"
FROM hospital
WHERE dept=’ENT’;

SELECT SUBSTR('SPIDERMAN',7,3);

SELECT instr('oralce apps','app');

SELECT round(123.67,-1) FROM DUAL;

SELECT trunc(123.67) FROM DUAL;

Q) Extract ‘my’ from ‘Mysql’ string.

q) Extract month from date(12-03-2012’);

q) select lower(substr(“MYSQLCLASSXI”,3,4));

Q)Select length(lower(substr(“CLASS XI”,-2,2)));