SQL Help A college football association has a database to record various data about their teams, playersand affiliated university.The following relational model presents the relationship between the tables:TeamTeamIDTeamNameCityStateWinLosPlayerPlayerlDPlayerNameAgePositionTeamIDCollegelDCollegeCollegelDCollegeNameCityStateWe have the following information about the attributes:Team:- TeamID is an integer and is the Primary Key of the Team table- TeamName is a chain of no more than 30 characters- City is a chain of no more than 25 characters- State is a chain of 2 characters- Win is an integer- Loss is an integerPlayer:- PlayerlD is an integer and is the Primary Key of the Player table- PlayerName is a chain of no more than 50 characters- Age is an integer- Position is a chain of no more than 15 characters- TeamID is an integer and is a foreign key of the Player table- CollegelD is an integer and is a foreign key of the Player tableCollege:- CollegelD is an integer and is the Primary Key of the College table- CollegeName is a chain of no more than 30 characters- City is a chain of no more than 25 characters- State is a chain of 2 characters C. Assume the following sample of data :ТеamCityLos AngelesTeamIDTeamNameStateWinLoss1001WildcatsCA101002HawksSan FranciscoCA851003RenegadesSan JoseCA41004GatorsSan FranciscoCA10PlayerPlayerIDTeamIDCollegelDPlayerNameAgePosition10110011080Julio Peters23WR10210031080Lance Randolph29QBTimothy HaasJoey Daniel1031003200127ТЕ1151002147832LB10610041479Hobert Golden25FB11010011478Hunter Padilla21QBCollegeCollegelDCollegeNameExampleUniversityHogwart CollegeStark CollegeData UniversityCityState1478San FranciscoCA1479San FranciscoCA1080Los AngelesCA2001PortlandORUsing Subqueries, list the player name, age and position for all the player coming fromExample University (5 pts)Your output should be :PlayerNameHunter PadilaAgePosition21QBJoey Daniel32LB

The SQL query is given below: SELECT P.PlayerName,P.Age,P.PositionFROM Player P, College CWHERE…

c. Assume the following sample of data : Тeam City Los Angeles San Francisco San Jose San Francisco TeamID TeamName State Win Loss Wildcats Hawks Renegades 1001 CA 10 3 1002 CA 8 1003 CA 9. 1004 Gators CA 3 10 Player PlayerlD CollegelD PlayerName Julio Peters Lance Randolph 29 Timothy Haas Joey Daniel Hobert Golden TeamID Age Position 101 1001 1080 23 WR 102 1003 1080 QB 103 1003 2001 27 ТЕ 115 1002 1478 32 LB 106 1004 1479 25 FB 110 1001 1478 Hunter Padilla 21 QB College CollegelD CollegeName Example University Hogwart College San Francisco Stark College Data University City San Francisco State 1478 CA 1479 CA 1080 Los Angeles CA 2001 Portland OR Using Subqueries, list the player name, age and position for all the player coming from Example University (5 pts) Your output should be: PlayerName Age Position Hunter Padila Joey Daniel 21 QB 32 LB

c. Assume the following sample of data : Тeam City Los Angeles San Francisco San Jose San Francisco TeamID TeamName State Win Loss Wildcats Hawks Renegades 1001 CA 10 3 1002 CA 8 1003 CA 9. 1004 Gators CA 3 10 Player PlayerlD CollegelD PlayerName Julio Peters Lance Randolph 29 Timothy Haas Joey Daniel Hobert Golden TeamID Age Position 101 1001 1080 23 WR 102 1003 1080 QB 103 1003 2001 27 ТЕ 115 1002 1478 32 LB 106 1004 1479 25 FB 110 1001 1478 Hunter Padilla 21 QB College CollegelD CollegeName Example University Hogwart College San Francisco Stark College Data University City San Francisco State 1478 CA 1479 CA 1080 Los Angeles CA 2001 Portland OR Using Subqueries, list the player name, age and position for all the player coming from Example University (5 pts) Your output should be: PlayerName Age Position Hunter Padila Joey Daniel 21 QB 32 LB

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

Consider a database that consists of the following relations.SUPpLIER(Sno. Sname)PART(Pno, Pname)PROJECT(Jno, Jname)SUPPLY(Sno, Pno, Jno)The database records information about suppliers, parts, and projectsandincludes a ternary relationship between suppliers, parts, and projects.Thisrelationship is a many-many-many relationships. Specify and execute thefollowingqueries using the relational algebraRetrieve the part numbers that are supplied to exactly two projects.b. Retrieve the names of suppliers who supply more than two parts toproject J11c. Retrieve the part numbers that are supplied by every supplier.d. Retrieve the project names that are supplied by supplier S1' only.e. Retrieve the names of suppliers who supply at least two different partseach to at least two different projects.
Consider the following shorthand representation of a relational database used by a book publisher to answer the following questions.BRANCH (BRANCH_NUM, COUNTRY, NUM_EMPLOYEES) WRITER (WRITER_NUM, LAST_NAME, FIRST_NAME, BOOK_NUM) BOOK (BOOK_NUM, TITLE, TYPE, PRICE, WRITER_NUM) INVENTORY (BOOK_NUM, BRANCH_NUM, ON_HAND)2.3 List the number of employees for each branch in South Africa. 2.4 List the book code and title of each book of type “Romance”. 2.5 For each type of book list the type and the average price. 2.6 Find the title for each book written by “Bob Builder”. 2.7 List the name of all books in South Africa written by “Hans Anderson“ 2.8 Count the number of books (AS NumOfBooks) written by each author
Passenger (pid, pbithday, pcity)Station (sid, sname, sxcoord, sycoord)CheckIn (pid, departure_sid, arrive_sid, departure_date, arrive_date)departure_sid reference sid in Stationarrive_sid reference sid in Stationpid references pid in Passenger there are 400 million Passenger, 500 stations, and 20 billion checkIn stored in the schema. For simplicity, assume that every attribute is of size 8 bytes, and the size of each tuple is thus 8*x bytes for a table with x attributes, and that the size of a table is simply the size of a tuple multiplied by the number of tuples (i.e., 100% occupancy and no gaps or space for block metadata). Now consider the following queries: SELECT P.pidFROM Passenger p, CheckIn C, Station SWHERE P.pid = C.pidand C.departure_sid = S.sidand S.sname = "LA"and year(P.pbithday) = 2000and date(C.departure_date) = "2015-12-12" question:1. first join P with C, and then join result with S2. first join S with C, and then join result with PWhich way would be faster for this…
Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNumber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The column facID of relation Classes that references the table Faculty, The column StuID of relation Enrolls that references table Students, and The column classNunber of relation Enrolls that references table Classes. The following SQL statement is intended to add a record to the table Students. Assume that the entered value for each column is appropriate. In other words, there is no domain constraint violation. INSERT INTO Students(stuID, lastName, firstName, major) VALUES('950555111', 'Anna' 'Smith', 'BSIT'); However, this statement does not work correctly. Briefly explain why and list the correct SQL statement.
Consider a relational database for an online hotel booking company. The database consisting ofthe following tables (where the primary keys are underlined): Hotel (hId, hName, hAddress, hCity) Guest(gId, gName, gAddress, gCity) Room(hid, roomNo, type, price) Booking(gId, hId, roomNo, fromDate, year, noOfDays)Where hId and gId are identifiers for the hotels and the guests,and the Booking relation indicated that a guest booked a hotel room for a specified number of days(noOfDays) starting from fromDate of a given year. For instance, a tuple < g12345, h5555, 220,Jan05, 2016, 15> in Booking indicates that the guest g12345 booked room 220 of the h5555 hotelfor 8 days starting on Jan 5, 2016. The attribute "gAddress" records the home address of theguests.Write the following queries in relational algebra (1) Return thename of guests who have booked a hotel room in NYC in January 2016 (i.e.,fromDate between 01/01 and 01/31 and year=2016).(2) Return thename of guests who have booked a…
TRUE OR FALSE In the Relational Data Model, Referential Integrity Constraint is a special kind/case of Foreign Key Constraint. Multiple Inheritance happens when a class has more than one sub-class. This problem can be observed in Programming Languages as well. Given two relations named A and B, if we q-JOIN A and B, the maximum number of attributes in the result relation could be (the number of attributes of A) times(*) the number of attributes of B). * in an SQL SELECT statement means “all the tuples”. In relational algebra, Cartesian Product (X) can be implemented using the q-JOIN operator. Cartesian Product ISA q-JOIN where q-condition always evaluates to true. SQL is based on Predicate Calculus. A query in SQL always specifies what should be included in the query result.
This question is related to the Database:- Suppose you are creating a database for a library management system. Explain how you would make a table for the "books" entity and insert some sample data, and then alter the table to add a new column for the "authorID" attribute and update "authorID" with some data. Finally, make a SQL query to retrieve all the books that were published after the year 2000. Note that this book schema has several columns, including "book_id" as the primary key, "title", "authorN", "publisher", "publication_year", "isbn", "language", and "num_pages". It also includes "available" column that is set to "true" by default and can be used to track the availability of the book. Please don't use handwriting...
The following is a relational schema for a database to document sharksightings for a research project. Primary keys are underlined. ForeignKeys are identified by FK, and their name indicates the attribute that theyreference.________________________________________________________________Sharks(shark_id:Integer, name:String, species_name:String,tagged_by_mission_id:Integer (FK))Sightings(shark_id:Integer (FK), sighting_num:Integer,sight_time:Timestamp, lat:Real, lng:Real, ocean:String, mission_id:Integer(FK))Missions(mission_id:Integer, start_date:Date, end_date:Date,vessel_id:Integer (FK))ResearchVessels(vessel_id:Integer, name:String)________________________________________________________________Sharks identified by a unique id, have a name which is not necessarilyunique, and a scientific species name.All Sharks are tagged and are sighted by Research Vessels, which areresearch ships that go on research Missions throughout the year. EachMission has a start and end date. Shark…
Consider the relational database below, where the primary keys are underlined.employee (person-name, street, city)works (person-name, company-name, salary)company (company-name, city)manages (person-name, manager-name)Give a query for each of the following:a Give all managers in database a 10 percent raise.b Give managers in the database a 10 percent raise, unless the salary would be greaterthan $100,000. In such cases, give only 3 percent raise.c Delete all tuples in the works relation for employees of Small Bank Corporation
25. In the relational model, relationships between relations or tables are created by using: a. composite keys b. determinants c. candidate keys d. foreign keys
Let us consider the following relational database. Students(stuID, lastName, firstName, major, gpa) Faculty(facID, name, deptName, rank) Classes(classNhmber, facID, schedule, room) Enrolls(stuID, classNumber, grade) The primary keys are underlined. The referential integrity constraints are as follows: The column facID of relation Classes that references table Faculty, The column of stuID of relation Enrolls that references table Students, and The column classNumber of relation Enrolls that references table Classes. The following SQL statement is intended to define the table Students. However, this statement does not work correctly. CREAT TABLE Students( lastName VARCHAR(20), firstName VARCHAR(20), major VARCHAR(40) gpa DECIMAL(3,2), PRIMARY KEY(stuID)); However, this statement does not work correctly. Briefly explain why and list the corrected SQL statement.
Question#2: Given the following Theatre Database form: No.of seats:500 Owner: J.Martin Year Founded: 1890 Owner’s Phone: 2347756 Play Name Play Type No. of performances Play Author name Play Author nationality MacBeth Tragedy 35 W.shakesper English Ulysses Drama 12 J.Joyce Irish Represent the above as an unnormalized relational schema then apply the rules of normalization to normalize it to 3NF.

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781305971776

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781305971776

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS