Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

PART III: SQL The Database of the VIDEO STORE has 4 tables (see attached code) CREATE TABLE Team idTeam varchar2(5) primary key, Name Team varchar2(30)

image text in transcribed
image text in transcribed
image text in transcribed
PART III: SQL The Database of the VIDEO STORE has 4 tables (see attached code) CREATE TABLE Team idTeam varchar2(5) primary key, Name Team varchar2(30) unique); CREATE TABLE Member ( id Varchar2(5) primary key, FnameM varchar2(30). LnameM varchar2(30), CityM varchar2(30), YearMembership int(4), CONSTRAINT IKO FOREIGN KEY(idTeam) REFERENCES Team (idTeam)); CREATE TABLE Competition idComp varchar2(5) primary key, TitleComp varchar2(30) unique, CompFees number(5)); CREATE TABLE Participation idM varchar2(5), idComp varchar2(5), Score number(3), CONSTRAINT pkl PRIMARY KEY(idM, idComp). CONSTRAINT fki FOREIGN KEY(idM) REFERENCES Member (idM), CONSTRAINT fk2 FOREIGN KEY(idComp) REFERENCES Competition (idComp)) 1) Draw the CDM corresponding to the above SQL code 2) Answer the following questions in SQL: a Give all information about all members. b/ Give idM of Members who became members in 2018. c/ Give number of members (how many members) stored in the database. d Give the idComp of the competitions in which member Ml participated. e/ Give the idComp of the competitions in which member 'Ahmed Salim' participated. f/ Give for each idM the number of Competitions in which he participated. g/Give for each idm the total score he got from all competitions in which he participated. Show only those who scored more than 1000. W Give for each team, the number of members who live in "Muscat'. i/ Give the idComp of the Competition which has the highest fee. j/ Delete from the database all members who live in 'Sur". k/ As the government has imposed new taxes, we want to increase by 5% all CompFees. V Give the list of teams which have no members m/ Give all id and their team names, including those who have no team. n/ We frequently query the database for the details of a certain member by executing "select * from members where lidM=5* for example. Suppose we have a huge number of Members in the database and thus we would like to improve the performance of this sql query. Write a SQL statement for that

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Visual Basic6 Database Programming

Authors: John W. Fronckowiak, David J. Helda

1st Edition

0764532545, 978-0764532542

More Books

Students also viewed these Databases questions

Question

Question What are the requirements for a safe harbor 401(k) plan?30

Answered: 1 week ago