DBMS ASSIGNMENTS

PART A

1
 INDEX

PAGE
SER DBMS EXERCISES (PART A)
NO

ACTIVITY – 1: DATABASE : STUDENT (DDL, DML

1 03 -06
STATEMENTS)

2 ACTIVITY 2: (SELECT CLAUSE, ARITHMETIC OPERATORS) 07-14

3 ACTIVITY 3: (LOGICAL, RELATIONAL OPERATORS) 15-21

4 ACTIVITY 4: (DATE FUNCTIONS) 22-27

5 ACTIVITY 5: ( NUMERIC, CHARACTER FUNCTIONS) 28-33

6 ACTIVITY : 6 (SET OPERATORS) 34-39

ACTIVITY 7: (VIEWS) DATABASE: RAILWAY

7 40-47
RESERVATION SYSTEM

2
 DBMS ASSIGNMENTS

PART A

1. CREATE TABLES USING CREATE STATEMENT

2. INSERT ROWS TO INDIVIDUAL TABLES USING INSERT STATEMENT
3. ALTER TABLE SECTION ADD NEW FIELD SECTION AND UPDATE THE
RECORDS
4. DELETE BROWN’S GRADE REPORT
5. DROP THE TABLE SECTION

3
1. CREATE TABLES USING CREATE STATEMENT

4
2. INSERT ROWS TO INDIVIDUAL TABLES USING INSERT STATEMENT

5
3. ALTER TABLE SECTION ADD NEW FIELD SECTION AND UPDATE THE
RECORDS

4. DELETE BROWN’S GRADE REPORT

5. DROP THE TABLE SECTION

6
7
CREATING EMPLOYEE AND EMP SAL TABLES

INSERTING RECORDS IN EMPLOYEE TABLE

8
9
1. TO DISPLAY FIRSTNAME, LASTNAME, ADDRESS AND CITY OF ALL
EMPLOYEES LIVING IN PARIS.

2. TO DISPLAY THE CONTENT OF EMPLOYEE TABLE IN DESCENDING ORDER

OF FIRSTNAME.

10
 3. SELECT FIRSTNAME AND SALARY OF SALESMAN

4. TO DISPLAY THE FIRSTNAME,LASTNAME, AND TOTAL SALARY OF ALL

EMPLOYEES FROM THE TABLE EMPLOYEE AND EMPSALARY. WHERE
TOTAL SALARY IS CALCULATED AS SALARY+BENEFITS.

5. LIST THE NAMES OF EMPLOYEES, WHO ARE MORE THAN 1 YEAR OLD IN
THE ORGANIZATION

11
6. COUNT NUMBER OF DISTINCT DESINGATION FROM EMPSALARY

7. LIST THE EMPLOYEE WHOSE NAME HAS EXACTLY 6 CHARACTERS.

12
8. ADD NEW COLUMN PHONE_NO TO EMPLOYEE AND UPDATE THE
RECORDS.

UPDATING RECORDS:

9. LIST EMPLOYEE NAMES, WHO HAVE JOINED BEFORE 15-JU-08 AND AFTER
16-JUN-07.

13
10. GENERATE SALARY SLIP WITH NAME, SALARY, BENEFITS, HRA-50%,
DA-30%, PF-12%, CALCULATE GROSS, ORDER THE RESULT IN DESCENDING
ORDER OF GROSS.

14
15
 CREATING TABLE BOOKS

INSERTING RECORDS IN BOOKS TABLE

16
 CREATING TABLE: ISSUED.

INSERTING RECORDS IN ISSUE TABLE

17
 1. TO SHOW BOOK NAME, AUTHOR NAME AND PRICE OF BOOKS OF
FIRST PUBL. PUBLISHER

2. DISPLAY BOOK ID, BOOK NAME AND PUBLISHER OF BOOKS HAVING

QUANTITY MORE THAN 8 AND PRICE LESS THAN 500

18
3. SELECT BOOK ID, BOOK NAME, AUTHOR NAME OF BOOKS WHICH IS
PUBLISHED BY OTHER THAN ERP PUBLISHERS AND PRICE BETWEEN 300
TO 700.

4. GENERATE A BILL WITH BOOK_ID, BOOK_NAME, PUBLISHER, PRICE,

QUANTITY, 4% OF VAT ―TOTAL‖

19
5. DISPLAY BOOK DETAILS WITH BOOK ID‘S C0001, F0001, T0002, F0002
(HINT: USE IN OPERATOR)

6. DISPLAY BOOK LIST OTHER THAN, TYPE NOVEL AND FICTION

7. DISPLAY BOOK DETAILS WITH AUTHOR NAME STARTS WITH LETTER ‗A‘

20
8. DISPLAY BOOK DETAILS WITH AUTHOR NAME STARTS WITH LETTER ‗T‘
AND ENDS WITH ‗S‘

9. SELECT BOOKID, BOOKNAME, AUTHOR NAME , QUANTITY ISSUED

WHERE BOOKS.BOOKSID = ISSUED.BOOKID

10. LIST THE BOOK_NAME, AUTHOR_NAME, PRICE. IN ASCENDING ORDER

OF BOOK_NAME AND THEN ON DESCENDING ORDER OF PRICE

21
22
 TABLE CREATION – EQUIPMENT DETAILS

INSERTING RECORDS IN EQUIPMENT DETAILS

23
 AFTER INSERTING ALL RECORDS IN EQUIPMENT DETAILS TABLE

RECORDS IN TABLE ARE:

1. TO SELECT THE ITEMNAME PURCHASE AFTER 31/10/07

2. EXTEND THE WARRANTY OF EACH ITEM BY 6 MONTHS

24
3. DISPLAY ITEMNAME , DATEOF PURCHASE AND NUMBER OF MONTHS
BETWEEN PURCHASE DATE AND PRESENT DATE

4. TO LIST THE ITEMNAME IN ASCENDING ORDER OF THE DATE OF

PURCHASE WHERE QUANTITY IS MORE THAN 3.

5. TO COUNT THE NUMBER, AVERAGE OF COSTPERITEM OF ITEMS

PURCHASED BEFORE 1/1/08

6. TO DISPLAY THE MINIMUM WARRANTY, MAXIMUM WARRANTY

PERIOD

25
7. TO DISPLAY THE DAY OF THE DATE , MONTH , YEAR OF PURCHASE IN
CHARACTERS.

8. TO ROUND OF THE WARRANTY PERIOD TO MONTH AND YEAR

FORMAT.

9. TO DISPLAY THE NEXT SUNDAY FROM THE DATE ‘07-JUN-96‘

26
10. TO LIST THE ITEMNAME, WHICH ARE WITHIN THE WARRANTY PERIOD
TILL PRESENT DATE

27
28
1. FIND THE MOD OF 165,16

2. FIND SQUARE ROOT OF 5000

3. TRUNCATE THE VALUE 128.3285 TO 2 AND -1 DECIMAL PLACES

4. ROUND THE VALUE 92.7683 TO 2 AND -1 DECIMAL PLACES

29
5. CONVERT THE STRING ‗DEPARTMENT‘ TO UPPERCASE AND
LOWERCASE

6. DISPLAY YOUR ADDRESS CONVERT THE FIRST CHARACTER OF EACH

WORD TO UPPERCASE AND REST ARE IN LOWERCASE

7. COMBINE YOUR FIRST NAME AND LAST NAME UNDER THE TITLE FULL

NAME

30
8. A) TAKE A STRING LENGTH MAXIMUM OF 15 DISPLAY YOUR NAME TO
THE LEFT. THE REMAINING SPACE SHOULD BE FILLED WITH ‗*‘

9. TAKE A STRING LENGTH MAXIMUM OF 20 DISPLAY YOUR NAME TO THE

RIGHT. THE REMAINING SPACE SHOULD BE FILLED WITH ‗#‘

31
10. FIND THE LENGTH OF THE STRING ‗JSS COLLEGE, MYSORE‘

11. DISPLAY SUBSTRING ‗BASE‘ FROM ‗DATABASE‘

12. DISPLAY THE POSITION OF THE FIRST OCCURRENCE OF CHARACTER

‗O‘ IN POSITION AND LENGTH

13. REPLACE STRING DATABASE WITH DATATYPE

32
14. DISPLAY THE ASCII VALUE OF ‗ ‗ (SPACE)

15. DISPLAY THE CHARACTER EQUIVALENT OF 42

33
34
35
 TABLE CREATION: PHYSICS TABLE

TABLE CREATION: COMPUTERSCIENCE TABLE

INSERTING RECORDS IN PHYSICS TABLE

ALL RECORDS OF PHYSICS TABLE AFTER INSERTION

36
 INSERTING RECORDS IN COMPUTER SCIENCE TABLE

ALL RECORDS OF COMPUTERSCIENCE TABLE AFTER INSERTION

1. SELECT ALL STUDENTS FROM PHYSICS AND COMPUTER SCIENCE

37
2. SELECT STUDENT COMMON IN PHYSICS AND COMPUTER SCIENCE

3. DISPLAY ALL STUDENT DETAILS THOSE ARE STUDYING IN SECOND

YEAR

4. DISPLAY STUDENT THOSE WHO ARE STUDYING BOTH PHYSICS AND

COMPUTER SCIENCE IN SECOND YEAR

5. DISPLAY THE STUDENTS STUDYING ONLY PHYSICS

38
6. DISPLAY THE STUDENTS STUDYING ONLY COMPUTER SCIENCE

7. SELECT ALL STUDENT HAVING PMCS COMBINATION

8. SELECT ALL STUDENT HAVING BCA COMBINATION

39
9. SELECT ALL STUDENT STUDYING IN THIRD YEAR

10. RENAME TABLE COMPUTER SCIENCE TO CS

40
41
 TABLE CREATION: TRAINDETAILS

TABLE CREATION: AVAILABILITY

RECORD INSERTION : TRAINDETAILS TABLE

AFTER INSERTING ALL RECORDS IN TRAINDETAILS TABLE

INSERTING RECORDS IN AVAILABILITY TABLE

42
 AFTER INSERTING ALL RECORDS IN AVAILABILITY TABLE

1. CREATE VIEW SLEEPER TO DISPLAY TRAIN NO, START PLACE,

DESTINATION WHICH HAVE SLEEPER CLASS AND PERFORM THE
FOLLOWING

A. INSERT NEW RECORD

B. UPDATE DESTINATION=‘MANGLORE‘ WHERE TRAIN NO=‘RJD16‘

C. DELETE A RECORD WHICH HAVE TRAIN NO=‘KKE55‘

CREATING SLEEPER VIEW

CONTENT OF SLEEPER VIEW

43
A. INSERT NEW RECORD

B. UPDATE DESTINATION=‘MANGLORE‘ WHERE TRAIN NO=‘RJD16‘

C. DELETE A RECORD WHICH HAVE TRAIN NO=‘KKE55‘

44
2. CREATE VIEW DETAILS TO DISPLAY TRAIN NO, TRAIN NAME, CLASS

3. CREATE VIEW TOTAL_SEATS TO DISPLAY TRAIN NUMBER, START

PLACE, USE COUNT FUNCTION TO NO OF SEATS , GROUP BY START PLACE
AND PERFORM THE FOLLOWING

A. INSERT NEW RECORD

B. UPDATE START PLACE=‘HUBLI‘ WHERE TRAIN NO=‘JNS8‘

C. DELETE LAST ROW OF THE VIEW

A. INSERT NEW RECORD

45
 B. UPDATE START PLACE=‘HUBLI‘ WHERE TRAIN NO=‘JNS8‘

C. DELETE LAST ROW OF THE VIEW

4. RENAME VIEW SLEEPER TO CLASS

5. DELETE VIEW DETAILS

46
DELETING SLEEPER TABLE

NOTE: SINCE TABLE NAME HAS BEEN CHANGED TO CLASS SO TO DELETE

SLEEPER TABLE WE HAVE TO DELETE CLASS TABLE.

47