Consider the Following Tables.
Q#1:Database Schema for a Video Library scenario.
Customer(cust_no:number,cust_name:varchar2,cass_no:number)
Membership(mem_no:number,cust_no:number)
Cassette(cass_no:number,cass_name:varchar2,Language:varchar2)
Iss_rec(iss_no:number,iss_date:date,mem_no:number,cass_no:numbser)
Write down the SQL statements for Above Scenarios.
For the above schema, perform the following
a) Create the tables with the appropriate integrity constraints at table level.
b) Rename any table name.
c) Insert the below records in each of the tables.
d) Change the name of Cust_no 40 to Fahad.
e) Write a query to display the Cust_no,Cust_name, whose Cust_no is greater than
Hijab.Exclude Hijab.
f) Display the Cust_name of all Customers where the second letter of the Cust_name is a.
g) Write a query that display the Customer records with the first letter Capitalized and all
other letters lowercase and the length of the Cust_name, Give each column an
appropriate label. Sort the result by the Customer names.
h) Create a query to display the cust_name concatenated with the cust_no, separated by
comma and space. Name the column heading CUSTOMER_DETAILS respectively.
(Customers table).
i) Display the Cass_name of all Cassettess where the language of the Cass_name is
English.
, Cust_no Cust_name Cass_no
39 ALI 201
34 HAMZA 202
18 HINA 203
8 HIJAB 204
24 ASMA 205
10 FAIZA 206
20 HASSAN 201
40 ADEEL 202
50 ADEEBA 203 1
60 HADIA 210
Cust_no Mem_no
39 101
34 111
18 112
8 113 2
24 114
10 115
20 116
40 117
50 118
60 119
Cass_no Cass_name Language
201 The lion of king English
202 Lord of rings English
203 Walk to remember Persian
204 The Legend Persian 3
205 The Prince German
206 The Town German
210 The Home Chineese
Cass_no Mem_no Iss_no Iss_Date
201 101 301 07-Jan-13
202 111 302 07-Jan-13
203 112 303 10-Jan-13
204 113 304 10-Jan-13 4
205 114 305 10-Jan-13
206 115 306 07-Jan-13
201 116 307 01-Jan-13
202 117 308 01-Jan-13
203 118 309 07-Jun-13
210 119 310 07-Jun-13
Q#1:Database Schema for a Video Library scenario.
Customer(cust_no:number,cust_name:varchar2,cass_no:number)
Membership(mem_no:number,cust_no:number)
Cassette(cass_no:number,cass_name:varchar2,Language:varchar2)
Iss_rec(iss_no:number,iss_date:date,mem_no:number,cass_no:numbser)
Write down the SQL statements for Above Scenarios.
For the above schema, perform the following
a) Create the tables with the appropriate integrity constraints at table level.
b) Rename any table name.
c) Insert the below records in each of the tables.
d) Change the name of Cust_no 40 to Fahad.
e) Write a query to display the Cust_no,Cust_name, whose Cust_no is greater than
Hijab.Exclude Hijab.
f) Display the Cust_name of all Customers where the second letter of the Cust_name is a.
g) Write a query that display the Customer records with the first letter Capitalized and all
other letters lowercase and the length of the Cust_name, Give each column an
appropriate label. Sort the result by the Customer names.
h) Create a query to display the cust_name concatenated with the cust_no, separated by
comma and space. Name the column heading CUSTOMER_DETAILS respectively.
(Customers table).
i) Display the Cass_name of all Cassettess where the language of the Cass_name is
English.
, Cust_no Cust_name Cass_no
39 ALI 201
34 HAMZA 202
18 HINA 203
8 HIJAB 204
24 ASMA 205
10 FAIZA 206
20 HASSAN 201
40 ADEEL 202
50 ADEEBA 203 1
60 HADIA 210
Cust_no Mem_no
39 101
34 111
18 112
8 113 2
24 114
10 115
20 116
40 117
50 118
60 119
Cass_no Cass_name Language
201 The lion of king English
202 Lord of rings English
203 Walk to remember Persian
204 The Legend Persian 3
205 The Prince German
206 The Town German
210 The Home Chineese
Cass_no Mem_no Iss_no Iss_Date
201 101 301 07-Jan-13
202 111 302 07-Jan-13
203 112 303 10-Jan-13
204 113 304 10-Jan-13 4
205 114 305 10-Jan-13
206 115 306 07-Jan-13
201 116 307 01-Jan-13
202 117 308 01-Jan-13
203 118 309 07-Jun-13
210 119 310 07-Jun-13
