XII - D INFORMATICS PRACTICE (MySQL PRACTICALS: PART-A) 2014-15

XII - D  INFORMATICS PRACTICE (MySQL PRACTICALS:  PART-A) 2014-15 
                                                               (JULY)

Q.1    Create the following tables in MySQL as per the given constraints.  

    Table: Book

Attribute	Datatype	Constraint
ID	Integer	Primary Key
Title	Varchar	Not Null
AuthorId	Integer	Foreign Key
Price	Integer	Default 100

    Table: Author

Attribute	Datatype	Constraint
Aid	Integer	Primary Key
Author	Varchar	Not Null
Language	Varchar	Not Null

Sample Data:

Book                                                

ID	TITLE	AUTHORID
1	DS	10	300
2	Pascal	20	375
3	Moodle	15	450
4	C++	20	475
5	Cobol	25	550
6	MySQL	40	350
7	Fortran	30	450
8	Java	15	500
9	C	20	475
10	Python	30	300

Author     

Aid	Author	Language
10	Tom	English
15	Babu	Hindi
20	Zen	Japanese
25	Jim	English
30	Tanu	Hindi
40	Jimmy	English
50	Chery	Japanese

Insert Sample Data in both the tables as given above.

Write the following queries in SQL :

i)             Display the Title, Price for books in the price range of 400 to 500.

ii)           Display the Title and Author name for the books which are written in English.

iii)          Display the average price for the books for each Language.

iv)          Display Title of all books written in Japanese.

v)           Display total of cost of books written in Hindi.

vi)          Display Title and Price of all books written by Zen.

vii)        Display no. of books written by each Author separately/individually.

Q.2    Create the following tables in MySQL                                                

Table: Student

Attribute	Datatype	Constraint
rollno	Integer	Primary Key
Name	Varchar	Not Null
hobbyid	Integer	Foreign Key
age	Integer	Default 5

Table: Hobby

Attribute	Datatype	Constraint
hid	Integer	Primary Key
Hobby	Varchar	Not Null
Area	Varchar	Not Null

Sample Data:

Student                                                     

Rollno	Name	Hobbyid	Age
1001	Ankur	10	15
1002	Ravi	12	14
1003	Saumya	14	17
1004	Kanchan	10	15
1005	Zubin	16	12
1006	Saif	12	16

             Hobby

Hid	Hobby	Area
10	Football	Sports
12	Guitar	Music
14	Swimming	Sports
16	Cricket	Sports

Insert Sample Data in both the tables as given above.

Write the following queries:

i)                    Display the Name and age for students whose name starts with ‘S’.

ii)           Display the Name and Hobby for the students which are who are interested in Sports area.

iii)          Display the average age for the students for each hobby.

iv)          Display roll no. of students who are in Sports area.

v)           Display minimum age of students in sports area.

vi)          Display name of students whose age is less than 15 years and belongs to music field.

Q.3    Create the following tables in MySQL                                                 

Table: Order

Attribute	Datatype	Constraint
Oid	Integer	Primary Key
Customer	Varchar	Not Null
Pid	Integer	Foreign Key
Qty	Integer	Default 15

Table: Product

Attribute	Datatype	Constraint
pid	Integer	Primary Key
Pname	Varchar	Not Null
cost	Integer	Default 0

Sample Data:

Order                                                                             

OID	CUSTOMER	PID	QTY
1001	Raja	101	150
1002	Suman	102	160
1003	Ranjeet	101	170
1004	Prabhu	103	150
1005	Geeta	101	120
1006	Raghu	104	180

Product

Hid	Product	cost
101	Toffee	10
102	Biscuit	20
103	Tea	50
104	Coffee	70

Insert Sample Data in both the tables as given above.

Write the following queries:

       i)           Display the customer and quantity for orders for PID 101 and 103

ii)            Display the Customer, product and qty for the orders whose cost is less than 50.

iii)          Display the count of orders for each product.

iv)         Display the name of customers who placed order for Biscuit.

Q.4    Create the following tables in MySQL                                                 

Table: Emp

Attribute	Datatype	Constraint
empno	Int	Primary Key
ename	Varchar	Not Null
deptno	Int	Foreign Key
salary	Int	>= 5000

Table: Dept

Attribute	Datatype	Constraint
deptno	Int	Primary Key
dname	Varchar	Not Null
loc	Varchar	Defalult –Vizag

Sample Data:

Emp                                                                           

Empno	Ename	Deptno
1001	Ankur	10	1500
1002	Ravi	12	1600
1003	Saumya	14	1700
1004	Kanchan	10	1500
1005	Zubin	16	1200
1006	Saif	12	1600

Dept

Hid	Hobby	Loc
10	ACC	DEL
12	HR	CHE
14	PRJ	DEL
16	FIN	KOL

Insert Sample Data in both the tables as given above.

Write the following queries:

i)            Display the ename and salary for employees who are getting salary below 1700.

ii)           Display the ename and deptno for the employees who are located at DEL.

iii)              Display the minimum salary for the employees for each department.

                                     (AUGUST)

Q5. Consider the table Student . Write MySQL commands for the following statement

StudentNo.	Class	Name	Game	Grade1		Supw	Grade2
10	7	Sameer	Cricket	B	Photography		A
11	8	Sujit	Tennis	A	Gardening		C
12	7	Kamal	Swimming	B	Photography		B
13	7	Veena	Tennis	C	Cooking		A
14	9	Archana	Basket ball	A	Literature		A
15	10	Arpit	Cricket	A	Gardening		C

a.     Display the names of the students who are getting a grade C in their game or supw

b.     Display the number of students getting grade A in cricket

c.      Display the different games offered in the school

d.     Display the supw taken up by the students whoes name starts with A

e.     Add a new column named ‘Marks’

6.Consider the following table Teacher . Write MySQL Command for the following statement

S.No.	Name	Age	Department	DOJ	Salary	Gender
1	Rahul	34	Computer	10/01/97	12000	M
2	Sharmila	31	History	24/03/98	20000	F
3	Sai	32	Maths	12/12/96	30000	M
4	Sangeeta	35	History	1/07/99	40000	F
5	Raju	42	Maths	05/09/97	25000	M
6	Shyam	50	History	27/06/98	30000	M
7	Shiva	44	Computer	25/02/97	21000	M
8	Shalakha	33	Maths	31/7/97	20000	F

a.     To show all the information about the teacher of history department

b.     To list the name of female teacher of Hindi  department

c.      To list names of all teacher with their date of joining  in ascending order

d.     To display teacher’s Name, Salary, Age for male teacher

e.     To count the number of teacher with age<23

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

SOLUTION

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

Q1.

create table author(aid int(2) primary key , author varchar(15)  not null,language varchar(15) NOT NULL);

create table book(id int(2) primary key , title varchar(15)  not null , authorid int(2), price int(5) default 100, FOREIGN KEY (AUTHORID) REFERENCES AUTHOR(AID));

INSERT INTO  AUTHOR VALUE(10, 'Tom' , 'English');

INSERT INTO  AUTHOR VALUE(15, 'Babu',  'Hindi');

INSERT INTO  AUTHOR VALUE(20, 'Zen' ,  'Japanese');

INSERT INTO  AUTHOR VALUE(25, 'Jim' ,  'English'); 

INSERT INTO  AUTHOR VALUE(30, 'Tanu',  'Hindi');

INSERT INTO  AUTHOR VALUE(40, 'Jimmy', 'English');

INSERT INTO  AUTHOR VALUE(50, 'Chery', 'Japanese');

INSERT INTO  BOOK VALUES(1, 'DS'     ,      10 , 300);

INSERT INTO  BOOK VALUES(2, 'Pascal' , 20 , 375);

INSERT INTO  BOOK VALUES(3, 'Moodle' , 15 , 450);

INSERT INTO  BOOK VALUES(4, 'C++'    , 20 , 475);

INSERT INTO  BOOK VALUES(5, 'Cobol'  , 25 , 550);

INSERT INTO  BOOK VALUES(6, 'MySQL'  , 40 , 350);

INSERT INTO  BOOK VALUES(7,      'Fortran', 30 , 450);

INSERT INTO  BOOK VALUES(8, 'Java'   , 15 , 500);

INSERT INTO  BOOK VALUES(9, 'C'      , 20 , 475);

INSERT INTO  BOOK VALUES(10, 'Python' , 30 , 300);

SQL QUERY ANSWER : 

  I.   SELECT Title, Price FROM  BOOK WHERE PRICE BETWEEN 400 AND 500;

  II.  SELECT BOOK.TITLE ,AUTHOR.AUTHOR FROM BOOK,AUTHOR  WHERE BOOK.AUTHORID=AUTHOR.AID AND AUTHOR.LANGUAGE='ENGLISH';

  III. SELECT AVG(BOOK.PRICE) , AUTHOR.LANGUAGE  FROM BOOK , AUTHOR  WHERE BOOK.AUTHORID=AUTHOR.AID   GROUP BY AUTHOR.LANGUAGE ;

                                   OR

  SELECT AVG(PRICE) , LANGUAGE  FROM BOOK , AUTHOR  WHERE        AUTHORID=AID   GROUP BY LANGUAGE ;

  IV.  SELECT BOOK.TITLE  FROM BOOK,AUTHOR  WHERE BOOK.AUTHORID=AUTHOR.AID AND AUTHOR.LANGUAGE='Japanese';

  V.   SELECT SUM(BOOK.PRICE)  FROM BOOK,AUTHOR  WHERE BOOK.AUTHORID=AUTHOR.AID AND AUTHOR.LANGUAGE='HINDI';

  VI.  SELECT TITLE , PRICE  FROM BOOK,AUTHOR  WHERE BOOK.AUTHORID=AUTHOR.AID AND AUTHOR.AUTHOR='ZEN';

  VII. SELECT COUNT(*) , AUTHOR  FROM BOOK,AUTHOR  WHERE BOOK.AUTHORID=AUTHOR.AID GROUP BY AUTHOR.

                                                                                             OR 
SELECT COUNT(TITLE) , AUTHOR  FROM BOOK,AUTHOR  WHERE AUTHORID=AID GROUP BY AUTHOR.

Q2.

CREATE TABLE HOBBY(hid Integer(2) Primary Key,Hobby Varchar(15) Not Null,Area Varchar(15) Not Null);

CREATE TABLE Student(rollno  Integer(4) Primary Key ,Name Varchar(15) Not Null,hobbyid Integer(2), age InT(2) Default 5,
FOREIGN KEY(HOBBYID) REFERENCES HOBBY(HID)); 

INSERT INTO HOBBY VALUES(10, 'Football', 'Sports');

INSERT INTO HOBBY VALUES(12, 'Guitar', 'Music');

INSERT INTO HOBBY VALUES(14, 'Swimming', 'Sports');

INSERT INTO HOBBY VALUES(16, 'Cricket', 'Sports');

   

INSERT INTO STUDENT VALUES(1001, 'Ankur' ,       10, 15);

INSERT INTO STUDENT VALUES( 1002, 'Ravi' ,      12, 14);

INSERT INTO STUDENT VALUES(1003, 'Saumya' , 14, 17);

INSERT INTO STUDENT VALUES(1004, 'Kanchan', 10, 15);

INSERT INTO STUDENT VALUES(1005, 'Zubin' , 16, 12);

INSERT INTO STUDENT VALUES(1006, 'Saif' , 12, 16);

SQL QUERY ANSWER : 

I. SELECT NAME , AGE FROM STUDENT  WHERE NAME  LIKE 'S%';

                              OR

  SELECT NAME , AGE FROM STUDENT  WHERE INSTR(NAME,"S")=1;

II. SELECT NAME , HOBBY FROM STUDENT,HOBBY WHERE HOBBYID=HID   AND AREA='SPORTS';

III. SELECT AVG(AGE) ,HOBBY FROM STUDENT,HOBBY WHERE HOBBYID=HID   GROUP BY HOBBY;

IV.  SELECT ROLLNO FROM STUDENT,HOBBY WHERE HOBBYID=HID  AND AREA='SPORTS';

V.  SELECT MIN(AGE) FROM STUDENT,HOBBY WHERE HOBBYID=HID  AND AREA='SPORTS';

VI. SELECT NAME FROM STUDENT,HOBBY WHERE HOBBYID=HID  AND AREA='MUSIC' AND AGE <15;

Q3.

CREATE TABLE PRODUCT (Pid Integer(3) Primary Key,Pname Varchar(15) Not Null,cost Integer(5) Default 0);

CREATE TABLE ORDERS(Oid Integer(5) Primary Key, Customer Varchar(15) Not Null,Pid Integer(3), Qty Integer(5) Default 15,

Foreign Key(PID) REFERENCES  PRODUCT(PID));

INSERT INTO PRODUCT VALUE(101, 'Toffee' , 10);

INSERT INTO PRODUCT VALUE(102, 'Biscuit', 20);

INSERT INTO PRODUCT VALUE(103, 'Tea', 50);

INSERT INTO PRODUCT VALUE(104, 'Coffee', 70);

INSERT INTO ORDERS VALUES(1001, 'Raja' , 101 , 150);

INSERT INTO ORDERS VALUES(1002, 'Suman', 102, 160);

INSERT INTO ORDERS VALUES(1003, 'Ranjeet', 101, 170);

INSERT INTO ORDERS VALUES(1004, 'Prabhu', 103, 150);

INSERT INTO ORDERS VALUES(1005, 'Geeta', 101, 120);

INSERT INTO ORDERS VALUES(1006, 'Raghu' ,       104, 180);

SQL QUERY ANSWER : 

I.    SELECT CUSTOMER , QTY FROM ORDERS WHERE PID=101 OR PID=103;

II.   SELECT ORDERS.Customer, PRODUCTS.PNAME , ORDERS.qty FROM ORDERS,PRODUCT WHERE ORDERS.PID=PRODUCT.PID  AND PRODUCT.COST<50; 

III.  SELECT ORDERS.OID, PRODUCTS.PNAME  FROM ORDERS,PRODUCT WHERE ORDERS.PID=PRODUCT.PID  GROUP BY PRODUCT.PNAME;

IV.   SELECT ORDERS.CUSTOMER FORM ORDERS , PRODUCT WHERE ORDERS.PID=PRODUCT.PID AND PRODUCT.PNAME='BISCUIT';

Q4.

CREATE TABLE DEPT(deptno Int(2) Primary Key,dname Varchar(15) Not Null,loc Varchar(15) Default 'Vizag');

 CREATE TABLE EMP(empno Int(4) Primary Key ,ename Varchar(15) Not Null,deptno Int(2), salary Int(5) CHECK(SALARY>= 5000) ,

Foreign Key(DEPTNO) REFERENCES DEPT(DEPTNO));

INSERT INTO DEPT VALUE(10,  'ACC' ,     'DEL');

INSERT INTO DEPT VALUE(12,  'HR' , 'CHE');

INSERT INTO DEPT VALUE(14,  'PRJ', 'DEL');

INSERT INTO DEPT VALUE(16, 'FIN' , 'KOL');

INSERT INTO EMP VALUE(1001, 'Ankur',10,1500);

 INSERT INTO EMP VALUE(1002,'Ravi',12,1600);

INSERT INTO EMP VALUE(1003,'Saumya',14,1700);

INSERT INTO EMP VALUE(1004,'Kanchan',10,1500);

INSERT INTO EMP VALUE(1005,'Zubin',16,1200);

INSERT INTO EMP VALUE(1006,'Saif',12,1600);

SQL QUERY ANSWER : 

I. SELECT ENAME ,SALARY FROM EMP WHERE SALARY < 1700;

II. SELECT EMP.ENAME , EMP.DEPTNO FROM EMP , DEPT WHERE EMP.DEPTNO = DEPT.HID AND DEPT.LOC='DEL';

III. SELECT MIN(SALARY), DEPTNO FROM EMP GROUP BY DEPTNO;

Q5.

 SQL QUERY ANSWER : 

I. SELECT NAME FROM STUDENT WHERE GRADE1='C' OR GRADE2='C';

II. SELECT COUNT(NAME) FROM STUDENT WHERE GAME='CRICKET' AND GRADE1='A';

III. SELECT DISTINCT GAME FROM STUDENT;

IV.  SELECT SUPW , NAME FROM STUDENT WHERE NAME LIKE 'A%';

V.   ALTER TABLE STUDENT ADD  MARKS INT(2);

 Q6.  SQL QUERY ANSWER : 

I.SELECT * FROM TEACHER WHERE DEPARTMENT = 'HISTORY';

II. SELECT NAME FROM TEACHER WHERE DEPARTMENT='HINDI' AND GENDER='F';

III.  SELECT NAME FROM TEACHER ORDER BY DOJ;

IV.   SELECT NAME,SALARY,AGE FROM TEACHER WHERE GENDER='M';

V.     SELECT COUNT(*) FROM TEACHER WHERE AGE <23;

*********************************   THE END  ******************************************