SQL help C. Assume the following sample of data :ТеamCityLos AngelesTeamIDTeamNameStateWinLoss1001WildcatsCA1031002HawksSan FranciscoCA1003RenegadesSan JoseCA41004GatorsSan FranciscoCA310PlayerPlayerIDCollegelDPlayerNameJulio PetersTeamIDAgePosition1011001108023WR10210031080Lance Randolph 29QBTimothy HaasJoey Daniel1031003200127TE1151002147832LB10610041479Hobert Golden25FB11010011478Hunter Padilla21QBCollegeCollegelDCollegeNameGator UniversityHogwart CollegeStark CollegeData UniversityCityState1478San FranciscoCA1479San FranciscoCA1080Los AngelesCA2001PortlandORUsing the previous tables, write the SQL queries that perform the following actions.Based on the data above, the output of each query is displayed below the question.List the name of all the teams with more wins than lossesYour output should be :TeamName• WildcatsНawks 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

Hey there, I am writing the required solution based on the above given question. Please do find the…

C. Assume the following sample of data : Тeam TeamID 1001 | 1002 1003 | 1004 City Win TeamName Wildcats Hawks Renegades Gators State Loss Los Angeles CA 10 San Francisco San Jose San Francisco CA CA 4 CA 3. 10 Player PlayerID 101 CollegelD PlayerName Julio Peters Lance Randolph 29 Timothy Haas Joey Daniel Hobert Golden Hunter Padilla TeamID Age Position 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 21 QB College CollegeName Gator University San Francisco Hogwart College San Francisco Stark College Data University CollegelD 1478 City State CA 1479 CA Los Angeles Portland 1080 CA 2001 OR Using the previous tables, write the SQL queries that perform the following actions. Based on the data above, the output of each query is displayed below the question. • List the name of all the teams with more wins than losses Your output should be : TeamName • Wildcats Hawks

C. Assume the following sample of data : Тeam TeamID 1001 | 1002 1003 | 1004 City Win TeamName Wildcats Hawks Renegades Gators State Loss Los Angeles CA 10 San Francisco San Jose San Francisco CA CA 4 CA 3. 10 Player PlayerID 101 CollegelD PlayerName Julio Peters Lance Randolph 29 Timothy Haas Joey Daniel Hobert Golden Hunter Padilla TeamID Age Position 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 21 QB College CollegeName Gator University San Francisco Hogwart College San Francisco Stark College Data University CollegelD 1478 City State CA 1479 CA Los Angeles Portland 1080 CA 2001 OR Using the previous tables, write the SQL queries that perform the following actions. Based on the data above, the output of each query is displayed below the question. • List the name of all the teams with more wins than losses Your output should be : TeamName • Wildcats Hawks

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Attributes, rows, and tables all refer to the same thing in relational models:
The relational schema shown below is part of a hospital database. The primary keys are highlighted in bold. Patient (patientNo, patName, patAddr, DOB)Ward (wardNo, wardName, wardType, noOfBeds)Contains (patientNo, wardNo, admissionDate)Drug (drugNo, drugName, costPerUnit)Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) for CONTAINS nad PRESCRIBED table , which one is primary key and how to create table ?
The relational schema shown below is part of a hospital database. The primary keys are highlighted in bold. Patient (patientNo, patName, patAddr, DOB)Ward (wardNo, wardName, wardType, noOfBeds)Contains (patientNo, wardNo, admissionDate)Drug (drugNo, drugName, costPerUnit)Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate) Formulate the following SQL statements: a) Find the names of all the patients being prescribed ‘Morphine’.b) What is the total cost of Morphine supplied to a patient called ‘John Smith’?c) For each ward that admitted more than 10 patients today, list the ward number, ward type and number of beds in each ward.
Material : Relational Algebra Course : Database Systems Create a Relational Algebra From Entity Relational Diagram (ERD) Below for:a) Displays books published in the last 5 years from 2020.b) Displays details of customers who have done shopping (using the Join operator)c) Displays email customers who have done shopping (using the Set operator)d) Displays the ISBN book that has never been purchased (using the Set operator)
Consider the following ER diagram: storcid address description value Displays Store Memorabilia The above diagram represents information pertaining to stores and the memorabilia that is displayed within the stores. a) Write the SQL statements to create the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s).
An insurance company needs to store their salespeople’s information who are selling their insurance policies. They already have a database with multiple tables, one of the tables (Salesperson) stores information about each salesperson along with the bonus percent they receive, based on the city where the insurance is sold. The table has the following fields: Salesperson(spID, spName, spBirthDate,spCitySelling, bonusPercent) spID: Unique identification number of the salesperson. spName: Full name of the salesperson. spBirthDate: Birthdate of the salesperson. spCitySelling: The city in which the salesperson is selling the insurance. bonusPercent: The bonus percent received by the salesperson based on the city in which he/she sells the insurance. Each salesperson can sell the insurance in just one city. However, for a city, there can be more than one salesperson appointed. Also, the bonus percent is fixed for each city. For example, all of the salespeople who sells insurance in…
SQL: Consider the following relational schema: Staff (staffNo, name, dept, skillCode) Skill (skillCode, description, chargeOutRate) Project (projectNo, startDate, endDate, budget, projectManagerStaffNo) Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn) where: Staff contains staff details and staffNo is the key. Skill contains descriptions of skill codes (e.g. Programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill; the key is skillCode. Project contains project details and projectNo is the key. Booking contains details of the date and the number of hours that a member of…
Write the SQL statements to create all of the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s). When playing a game, the home team name should be stored in attribute htname and the away team name should be stored in atname.
In relational model terminology, a table is considered as Select one: a. Tuple b. Range c. Relation d. Domain
For the following relational model: Student(sid, fname, lname, gpa)Course ( cid, code, name, credithours)Enrolled (sid, cid,grade) Write SQL queries for the following: Print all the students by the name order desc by the gpa. Print all the computer sciences courses, these courses with code start with ‘CS’sorted alphabetically. Print all the students with a gpa less than 3.0 Print all the students with a gpa of a least 3.0 Find all students that have enrolled in database course Find all students that have enrolled in database and network courses Find all the students that have (A or more) at least once
Consider the following table in a relational database.Last Name Rank Room ShiftSmith Manager 234 MorningJones Custodian 33 AfternoonSmith Custodian 33 EveningDoe Clerical 222 MorningAccording to the data shown in the table, which of the following could be candidate keys of the table?a) {Last Name}b) {Room}c) {Shift}d) {Rank, Room}e) {Room, Shift}
Course : Database System Construct the relational model of the following scenario. System Description: Branches Dream Home has branch offices in cities throughout the United Kingdom. Each branch office is allocated members of staff, including a Manager, who manages the operations of the office. The data describing a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manages the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly bonus payment based upon his or her performance in the property for rent market. Staff Members of staff with the role of Supervisor are responsible for the day-to-day activities of an allocated group of staff called Assistants (up to a maximum of 10, at any one time). Not all members of staff are assigned to a Supervisor. The…