1- Consider the following SQL query and related relations
SELECT P.Name
FROM Players P, Games G
WHERE P.Id = G.PlayerId
AND P.Game = 'Football' AND G.Season ='2019'
where:
Players (Id, Name, Game)
Games (PlayerId, GameCode, Season)
A- Translate the above SQL query to a relational algebra expression.
B- Draw the Query tree for above relational algebra expression in part (a).
Note: Please no screenshot
SELECT P.Name
FROM Players P, Games G
WHERE P.Id = G.PlayerId
AND P.Game = 'Football' AND G.Season ='2019'
Let us see first symbol which will be used in translating the query in relational algebra:
πP.name
σ Id(Player)= PlayerId(Games)
So the relational algebra expression for the following part of Sql Query :
SELECT P.Name
FROM Players P, Games G
WHERE P.Id = G.PlayerId
is
πP.name (σ Id(Player)= PlayerId(Games))
π Player.name (σ Id(Player)= PlayerId(Games) and σGame=’FootBall’(Player) and σseason=2019(Games))( Player X Games)
Where Player X Games shows Cartesian product to merge two relations Player and Games.
Query Tree
1- Consider the following SQL query and related relations SELECT P.Name FROM Players P, Games G...
1. Given the table above, write an SQL Query that will select only those individuals that scored 800 or higher, sorted by last name in ascending order. 2. Given the table above, write an SQL Query that will add a new entry for John Smith with a score of 834. The field id is auto incrementing. 3. Given the data below, write an SQL Query that will delete any values where the score is below 740. id Fname Lname Score...
**************PLEASE COMPLETE PART F) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...
**************PLEASE COMPLETE PART E) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...
Question 9 (1 point) Which choice below describes the function of the following SQL statement? SELECT ID, Last Name, First Name, Address FROM Members WHERE Last Name Last Name a simple query a parameterized query a compound query a wildcard query Question 10 (1 point) The correct SQL syntax to sort a table by Last Name in order of A-Z is SORT BY Last Name ASCENDING ORDER BY Last Name Down ORDER BY Last Name ASC ORDER BY Last Name...
Consider the following relations for an airline company: Flights (flno: integer, from: string, to: string, distance: integer, departs: time, arrives: time) Aircraft (aid: integer, aname: string, cruising_range: integer) Certifed (pid: integer, aid: integer) Pilots (pid: integer, pname: string, salary: integer) (word in bold are primary keys) Note that every pilot is certified for some aircraft. Write the following queries in relational algebra: 1- Find the flight numbers of flights departing from ‘Amsterdam’ or ‘London’ between 8:00am and 11:00am, and arriving...
PLEASE INCLUDE SAW-PROMPTS FOR 2 PLAYERS NAMES(VALIDATE NAMES). SHOW MENU (PLAYER MUST SELECT FROM MENU B4 THE GAME STARTS 1=PLAY GAME, 2=SHOW GAME RULES, 3=SHOW PLAYER STATISTICS, AND 4=EXIT GAME WITH A GOODBYE MESSAGE.) PLAYERS NEED OPTION TO SHOW STATS(IN A DIFFERNT WINDOW-FOR OPTION 3)-GAME SHOULD BE rock, paper, scissor and SAW!! PLEASE USE A JAVA GRAPHICAL USER INTERFACE. MUST HAVE ROCK, PAPER, SCISSORS, AND SAW PLEASE This project requires students to create a design for a “Rock, Paper, Scissors,...
Question 1.Write a SQL statement for each of the following questions based on the above tables (50 Points). 1) Create “Enrollment” table. 2) Change the credits of “Database” course from 2 to 3. 3) List the course titles (not Course_No) and grades John Doe had taken. 4) Calculate average of Café Balance, and name new field as Average Balance. 5) Create a roster for “Database” course (list student ID, last name, first name, major, and status for each student enrolled...
ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus) NeedByEvent(Event) CRPrevState(CRID, CRState, StartDate, EndDate) CRAssigned(CRID, EmpID, StartDate, EndDate) Employees(EmpID, FirstName, LastName, JobTitle) ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus) The CRID is the primary key, it is unique, and it is an positive integer The CRType may be one of two values: "Deficiency" or "Enhancement" CRTitle is a variable length string that may be up to 2048 characters CROriginDate is a date CRPriority is an integer that may assume a value of...
1 L, as a dynamical system (Notes from Assignment #2) We take our definition of dynamical system to be an "object" along with a specific set of modifications that can be performed (dynamically) upon this object. In this case, the object is a bi-infinite straight road with a lamp post at every street corner and a marked lamp (the position of the lamplighter). There are two possible types of modifications: the lamplighter can walk any distance in either direction from...