Page |1
ASSIGNMENT 1
1. Consider following relation schema:
Author(id#, name, country)
Book(book_id#, book_title)
Book_Authors(id#, book_id#)
• Where # denotes primary key.
Answer the following queries. Create tables with primary key and foreign key,
suitable data types. Also Insert meaningful data (atleast 5 rows)
a. List all the authors staying in the same country.
b. List all the books where book title starts with string data'
c. Find total number of books written by author whose name is 'Asha'
d. Add not null constraint to book_title attribute in Book table
e. Add number_of pages attribute to Book table
f. Display distinct authors in ascending order of name attribute
Answer:
• Create and connect the database:
• Create and insert values in the table Author:
• Create and insert values in the table Book:
Anwesha Jana (33) CE32
Page |2
• Create and insert values in the table Book_Authors:
• List all the authors staying in the same country:
• List all the books where book title starts with string ‘data’:
• Find total number of books written by author whose name is 'Asha':
• Add not null constraint to book_title attribute in Book table:
• Add number_of pages attribute to Book table:
Anwesha Jana (33) CE32
Page |3
• Display distinct authors in ascending order of name attribute:
Anwesha Jana (33) CE32
Page |4
2. Consider following relation schema:
grocery_list(g_name, g_category, date)
grocery_visit(date, Time_spent, amount_spent)
Answer the following queries. Create tables with primary key and foreign key, suitable data
types. Also Insert meaningful data (at least 5 rows)
a. Display distinct g_category
b. Rename g_name attribute as Item_name
c. Display g_name, g_category where g_category begins with 'fruit'
d. Find number of visits where time_spent is greater than 15 min
e. Find the total number of g_names group by g_category
f. Find the shortest and longest visits using aggregate functions
Answer:
• Create and connect a database:
• Create and insert values in table grocery_list:
• Create and insert values in table grocery_visit:
• Display distinct g_category:
Anwesha Jana (33) CE32
Page |5
• Rename g_name attributes as Item_name:
• Display g_name, g_category where g_category begins with ‘fruit’:
• Find number of visits where time_spent is greater than 15 min:
• Find the total number of g_names group by g_category:
• Find the shortest and longest visits using aggregate functions:
Anwesha Jana (33) CE32
Page |6
3. Consider following relation schema:
Station(id#, city, state)
Statistics(id, month, temperature, rain_fall)
Answer the following queries.
Also Insert meaningful data (at least 5 rows)
a. Find out total number of stations present in Maharashtra state
b. Alter table to add constraint as month attribute should take data as (1,2,3,4,5,6) only
c. Find the maximum rain fall in second month
d. Display statistics arranged by month attribute
e. Show MAX and MIN temperatures as well as average rainfall for each station
f. Update city as "Mumbai" where it was "Bombay"
g. Find the cities where temperature is between 30 to 40 degrees
Answer:
• Create and connect database:
• Create and insert values into table station:
• Create and insert values into table statistics:
• Find out total number of stations present in Maharashtra state:
Anwesha Jana (33) CE32
Page |7
• Alter table to add constraint as month attribute should take data as (1,2,3,4,5,6) only
• Find the maximum rain fall in second month
• Display statistics arranged by month attribute
• Show MAX and MIN temperatures as well as average rainfall for each station
• Update city as "Mumbai" where it was "Bombay"
• Find the cities where temperature is between 30 to 40 degrees
Anwesha Jana (33) CE32
Page |8
4. Consider following relation schema:
Artist(Art_id#, Art_name, Art gender)
Producer(P_id#, P_name, P_phone)
Films(F_id#, F_title, F_year, P_id)
Casting(Art_id, F_id, part)
Review(Film_id#, stars)
Primary Key Art_id, F_id
Where # denotes primary key.
Answer the following queries.
Create tables with primary key and foreign key, suitable data types.
Also Insert meaningful data (atleast 5 rows)
a. Find the name of all the Films whose producer is "Gauri".
b. Display the names of films with the 3 stars
c. Update the stars of all films whose producer is 'Prachi' to 5.
d. Alter table Artist to accept gender as (male, female,other) only
e. Find the total number of films in year 2021 who are having 4 stars
f. Display films in descending order of year
Answer:
• Create and connect to the database
• Create and insert values into table artist
• Create and insert values into table producer
Anwesha Jana (33) CE32
Page |9
• Create and insert values into table films
• Create and insert values into table casting
• Create and insert values into table review
• Find the name of all the Films whose producer is "Gauri".
Anwesha Jana (33) CE32
P a g e | 10
• Display the names of films with the 3 stars
• Update the stars of all films whose producer is 'Prachi' to 5
• Alter table Artist to accept gender as (male, female, other) only
• Find the total number of films in year 2021 who are having 4 stars
• Display films in descending order of year
Anwesha Jana (33) CE32
P a g e | 11
5. Consider following relation schema:
Customer(cust_id#, cust_name, cust_city)
Order(O_id#, O_date, purchase_amount, cust_id, salesman_id)
Where # denotes primary key.
Answer the following queries. Create tables with primary key and foreign key , suitable data
types. Also Insert meaningful data (atleast 5 rows)
a. Find the total number of orders made on date "15/12/2021"
b. Calculate the average purchase amount made on date "15/12/2021"
c. Find out number of unique salesman
d. Find out the total number of customers from each city.
Answer:
• Create and connect to a database
• Create and insert values into table customer
• Create and insert values into table order
• Find the total number of orders made on date "15/12/2021"
Anwesha Jana (33) CE32
P a g e | 12
• Calculate the average purchase amount made on date "15/12/2021”
• Find out number of unique salesman
• Find out the total number of customers from each city
Anwesha Jana (33) CE32