DBMS LAB 4 SOLUTION

-
SQL>create table customer(cust_no number(5) primary key,cust_name varchar2(20));

SQL>desc customer;

Name                                      Null?              Type
……………………………………………………………………………………………………………..
CUST_NO                           NOT NULL          NUMBER(5)
CUST_NAME                                                 VARCHAR2(20)

Valid Test Data:

SQL>insert into customer values(&cust_no,’&cust_name’);
SQL>select * from customer;

CUST_NO           CUST_NAME
……………………………………………………………….
50                                          scott
51                                          pandey
52                                          varshney
53                                          naidu
54                                          bhimbra

SQL>create table membership(mem_no number(5) primary key,cust_no number(5) references customer(cust_no));

SQL>dsec membership;
Name                                      Null?                      Type
………………………………………………………………………………………………………...
MEM_NO                                    NOT NULL          NUMBER(5)
CUST_NO                                                           NUMBER(5)

SQL>insert into memship values(&mem_no,&cust_no);
SQL>select * from memship;

MEM_NO            CUST_NO
…………………………………………………
920                           50
981                           51
897                           52
820                           53    
928                           54
                                                                                                SQL>create table cassette(cass_no number(5) primary key,
Cass_name varchar2(15),language varchar2(15));

SQL>desc cassette;

Name                                      Null?              Type
………………………………………………………………………………………..
CASS_NO                                NOT NULL      NUMBER(5)
CASS_NAME                                                  VARCHAR2(15)
LANGUAGE                                                VARCHAR2(15)

SQL>insert into cassette values(&cass_no,’&cass_name’,’&language’);

SQL>select * from cassette;

CASS_NO      CASS_NAME                     LANGUAGE
………………………………………………………………………………………
1                      tagore                       telugu
2                      the lion king               English
3                      anniyan                     tamil
4                      indra                         telugu
5                      lord of rings                English

SQL>create table issu_rec(iss_no number(5) primary key,iss_date date,mem_no number(5)references memship(mem_no),cass_no number(5) references cassette(cass_no));

SQL>desc issu_rec;
Name                                           Null?                          Type
………………………………………………………………………………………………………...
ISS_NO                                     NOT NULL          NUMBER(5)
ISS_DATE                                                          DATE
MEM_NO                                                            NUMBER(5)
CASS_NO                                                           NUMBER(5)

SQL>select * from issu_rec;

ISS_NO      ISS_DATE       MEM_NO     CASS_NO
……………………………………………………………………………………
22             07-JAN-06          920           1
23             10-JAN-00          981           2
26             10-JAN-06          897           5
3              01-JAN-06          820           4
34             31-DEC-05          928           3

c)   List all the customer names with their membership numbers
SQL>select c.custname,m.memno from customer1 c,membership1 m where c.custno=m.custno;

CUSTNAME    MEMNO
……………….. ………………..
NIKHIL              51
VIVEK                52
SHRAVAN           58
VAMSI               57
SHIVA               56


d)   List all the issues for the current date with the customer names and cassette names

SQL>select i.issno,c.custname,cc.cassettename from customer1 c,membership1 m,cassette  cc,issrec1 I where i.issdate=to_char(sysdate) and c.custno=m.custno and i.cassno=cc.cassno and i.memno=m.memno;

OutPut:

no rows selected.

e)   List the details of the customer who has borrowed the cassette whose title is “ The Legend”
f)    Give a count of how many cassettes have been borrowed by each customer
g)   Give a list of book which has been taken by the student with mem_no as 5
h)   List the cassettes issues for today
i)    Create a view which lists outs the iss_no, iss_date, cust_name, cass_name
j)    Create a view which lists issues-date wise for the last one week


Comments

Post a Comment

Popular posts from this blog

PROCEDURE TO CREATE AN ANIMATION TO REPRESENT THE GROWING MOON.

-
Image
1.       Open flash 8 software -> click on flash document ->go to windows->properties ->select the properties tool-> choose the Background to black. 2.       Go to fill color under tool bar-> select the white color. 3.       Select the oval tool in order to draw the moon. u will get a white circle. 4.       Select the oval tool in order to draw the moon. u will get a white circle. 5.       Select the white circle on the worksheet using the selection tool ->right click- >convert to symbol ->select movie clip ->give suitable name e g: moon->click ok. 6.       Go to filter ->click on the + symbol->select glow to apply glowing effect-> select the color to white under glow and adjust the blur x/blur y values. 7.       Click on the...
Read more

PROCEDURE TO CREATE AN ANIMATION TO INDICATE A BALL BOUNCING ON STEPS.

-
Image
1.     Go to start- macromedia- click on flash document 2.     Select the line tool and draw the steps. color it using the paint bucket tool 3.     Select the circle from the tool bar and create a circle on the work area. 4.     Now fill the color to the circle using the paint bucket tool from the tool bar. 5.     Go to frames right click on the first frame and choose insert key frame. Slightly move the ball. Repeat the same procedure by adding new key frames to show the ball change the shape of the ball slightly when it touches the surface. 6.     In order to change the shape use the free transform tool. 7.     Go to control and click on test movies .you will observe the ball bouncing on steps.
Read more

PROCEDURE TO SIMULATE MOVEMENT OF A CLOUD.

-
Image
1.       go to start- macromedia- click on flash document 2.       create a blue background in layer 1 3.       Now insert a layer 2 and draw the clouds in this layer. 4.       in order to create the clouds, go to tool bar and select pencil option, draw the cloud in layer2 5.       fill the color to the cloud, right click on it- choose convert to symbol option- give the name as cloud 6.       select the movie clip option and click ok. 7.       Go to filter ->click on the + symbol->select glow to apply glowing effect-> select the colour to white 8.       under glow and adjust the blur x/blur y values. 9.       give the appropriate blur effect to the cloud. 10.   go to frames, insert key frame on both ...
Read more