ANSWERS:-

(i) SELECT TNAME, CITY, SALARY FROM TRAINER ORDER BY HIREDATE;

(ii) SELECT TNAME, CITY FROM TRAINER WHERE HIREDATE BETWEEN ‘2001-12-01’ AND ‘2001-12-31’;

(iii) SELECT TNAME, HIREDATE, CNAME, STARTDATE FROM TRAINER, COURSE WHERE TRAINER.TID=COURSE.TID AND
FEES<=10000;

(iv) SELECT CITY, COUNT(*) FROM TRAINER GROUP BY CITY;

(v) SELECT TID, TNAME, FROM TRAINER WHERE CITY NOT IN(‘DELHI’, ‘MUMBAI’);
i) To display details of all the items in the Store table in descending order of LastBuy.

ii) To display Itemno and item name of those items from store table whose rate is more than 15 rupees.

iii) To display the details of those items whose supplier code is 22 or Quantity in store is more than 110 from the table
Store.

iv) To display minimum rate of items for each Supplier individually as per Scode from the table Store.

v) To display ItemNo, Item Name and Sname from the tables with their corresponding matching Scode.

(i) Select * from Store order by Lastbuy;

(ii) Select Itemno, Item from store where rate > 15;

(iii) Select * from store where scode = 22 or qty > 110;

(iv) Select scode, min(rate) from store group by scode;

(v) Select Itemno, Item, Store.scode, Sname from Store, Suppliers where Store.scode = Suppliers.scode;
OUTPUT:- (i) 43000

(ii) Max (DOB) Min(DOB)

08-10-1995 05-071993

(iii) Gender Count(*)

F 3

M 3
a) Create table WORKER(WORKER_ID varchar(3), FIRST_NAME varchar(10), LAST_NAME varchar(10), SALARY
integer, JOINING_DATE Date, DEPARTMENT varchar(10));

b) WORKER_ID

c) alter table worker modify FIRST_NAME varchar(20);

d) DELETE FROM WORKER;

e) Desc WORKER / Describe WORKER;

Observe the following table and answer the question (a) to (e)

(a) Write the name of most appropriate columns which can be considered as Candidate keys?
(b) Out of selected candidate keys, which one will be the best to choose as Primary Key?
(c) What is the degree and cardinality of the table?
(d) Insert the following data into the attributes VisitorID, VisitorName and ContactNumber respectively in the given
table VISITOR.
VisitorID = “V004”, VisitorName= “VISHESH” and ContactNumber= 9907607474
(e) Remove the table VISITOR from the database HOTEL.
Answer:
(a) VisitorID and ContactNumber
(b) VisitorID
(c) Degree= 3 Cardinality=4
(d) insert into VISITOR values (“V004”, “VISHESH”,9907607474)
(e) DROP TABLE VISITOR;
i) To decrease period by 10% of the teachers of English subject.

ii) To display TEACHERNAME, CODE and DESIGNATION from tables SCHOOL and ADMIN whose gender is male.

iii) To Display number of teachers in each subject.

iv) To display details of all teachers who have joined the school after 01/01/1999 in descending order of
experience.

v) Delete all the entries of those teachers whose experience is less than 10 years in SCHOOL table.

Answer:

i) update SCHOOL set PERIODS=0.9*PERIODS;

ii) select SCHOOL.TEACHERNAME, SCHOOL.CODE, ADMIN.DESIGNATION from SCHOOL, ADMIN where

iii) select SUBJECT, count(*) from SCHOOL group by SUBJECT;

iv) select * from SCHOOL where DOJ>’ 01/01/1999’ order by EXPERIENCE desc;

v) delete from SCHOOL where EXPERIENCE<10;

(ii) List the details of mobile whose name starts with “S” or ends with “a”.

(iii) Display the Mobile supplier & quantity of all mobiles except “MB003”.

(iv) List showing the name of mobile company having price between 3000 & 5000.

(v) Display M_Id and sum of Moble quantity in each M_Id.

Answer:

(i) SELECT M_Company, M_Name, M_Price FROM MobileMasterORDER BY M_Mf_Date DESC;

(ii) SELECT * FROM MobileMaster WHERE M_Name LIKE “S%” or M_Name LIKE “%a”;

(iii) SELECT M_Supplier, M_Qty FROM MobileStock WHERE M_Id <>“MB003”;

(iv) SELECT M_Company FROM MobileMaster WHERE M_Price BETWEEN 3000 AND 5000;

(v) SELECT M_Id, SUM(M_Qty) FROM MobileStock GROUP BY M_Id;

As a database administrator, answer any 4 of the following questions:

Name of the table : SOFTDRINK

The attributes are as follows:

Drinkcode, Calories - Integer

Price - Decimal

Dname - Varchar of size 20

a) Identify the attributes that can be called Candidate keys.

b) What is the cardinality and degree of the table SOFTDRINK.

d) Give the command to remove all the records from the table.

e) Write a query to create the above table with Drinkcode as the Primary Key.

) Drinkcode and Dname

b) Cardinality = 6, Degree = 4

c) Insert into softdrink(drinkcode,dname,calories) values (107,”Milkshake”,125);

d) Delete from softdrink;

e) Create table softdrink(drinkcode integer(5) Primary Key, dname

varchar(20), Price decimal(6,2), calories integer(5));

Write the outputs of the SQL queries i) to iii) based on the tables given below:

i) Select Item_Name, max(Price), count(*) from Item group by Item_Name ;

ii) Select CName, Manufacturer from Item, Customer where Item.ID = Customer.ID;

iii) Select Item_Name, Price * 100 from Item where Manufacturer = “ABC”;

A CD/DVD Shop named “NEW DIGITAL SHOP” stores various CDs & DVDs of songs/albums/movies and use SQL
to maintain its records. As a Database Administrator, you have decided the following:
Name of Database - CDSHOP
Name of Relation - LIBRARY
Attributes are:-
(a) CDNO - Numeric values
(b) NAME - Character values of size (25)
(c) QTY - Numeric values
(d) PRICE - Decimal values

Answer the following questions based on the above table LIBRARY:-

(a) Write the Degree & Cardinality of the relation LIBRARY.

4&6

(b) Identify the best attribute which may be declared as Primary key. 1

CDNO

(c) Insert the following record in the above relation: (10009, ”Motivational Songs”, 15, 70)

INSERT INTO LIBRARY VALUES (10009, ”Motivational Songs”, 15, 70);

(d) Write an SQL query to display the minimum quantity.

SELECT MIN(QTY) FROM LIBRARY;

(e) Database administrator wants to count the no. of CDs which does not have any Price value. Write the query for
the same.

SELECT COUNT(*) FROM LIBRARY WHERE PRICE IS NULL;

Consider the table TEACHER given below. Write commands in SQL for (i) to (iii)

i. To display all information about teachers of Female PGT Teachers.

ii. To list names, departments and date of hiring of all the teachers in descending order of date of joining.

iii. To count the number of teachers and sum of their salary department wise.

Ans i) SELECT * FROM TEACHER WHERE CATEGORY= ‘PGT’ AND GENDER= ‘F’;

ii) SELECT NAME, DEPARTMENT, HIREDATE FROM TEACHER ORDER BY HIREDATE DESC;

iii) SELECT DEPARTMENT, COUNT(NAME), SUM(SALARY) FROM TEACHER GROUP BY DEPARTMENT;

a. To display the RecIC, Sendername, SenderAddress, RecName, RecAddress for every Recipient

b. To display Recipient details in ascending order of RecName

c. To display number of Recipients from each city

d. To display the details of senders whose sender city is ‘mumbai’

e. To change the name of recipient whose recid is ’Ko05’ to’ S Rathore’.

a. Select R.RecIC, S.Sendername, S.SenderAddress, R.RecName, R.RecAddress from Sender S, Recepient R where
S.SenderID=R.SenderID ;

b. SELECT * from Recipent ORDER By RecName;

c. SELECT COUNT( *) from Recipient Group By RecCity;

d.Select * from sender where Sendercity=’mumbai’;

e. update recipient set RecName=’S Rathore’ where RecID=’ KO05’

Write SQL commands for (i) to (v) on the basis of relations given below:
(i) To show the books of FIRST PUBL. Publishers written by P. Purohit.

(ii) To display cost of all the books published for EPB.

(iii) Depreciate the price of all books of EPB publishers by 5%.

(iv) To display the BOOK_NAME and price of the books, more than 5 copies of which have been issued.

(v) To show total cost of books of each type.

i) SELECT * FROM BOOKS WHERE PUBLISHER LIKE „FIRST PUBL.‟ AND AUTHOR_NAME LIKE „P. Purohit‟;

ii) Select Price from Books where PUBLISHER LIKE „EPB‟;

iii) UPDATE BOOKS SET PRICE = PRICE * 0.90 WHERE PUBLISHER LIKE “EPB‟;

iv) SELECT BOOK_NAME, PRICE FROM BOOKS B, ISSUED I WHERE B.BOOK_ID = I.BOOK_ID AND QTY_ISSUED > 5;

v) SELECT SUM(PRICE) FROM BOOKS GROUP BY TYPE;