Question 1 Refer to the sample tables and data below. Tables: people (id(pk), firstname, lastname, zip(fk)) -- foreign key references zip_code(zip) zip_code (zip(pk), city, state) mysql> select from people; | id | firstname | lastname | zip ++ | 1 | Marty | 2 | Jennifer 3 | Lorraine | 4 | Biff is | George | McFly 5 rows in set (0.01 sec) mysql> select from zip_code; | zip | city | state | | 45001 | New York | NY | 0700s | New Jersey | NJ | 33647 | Tampa FL | 33620 | Tampa | FL | 33765 | Clearwater | FL 5 rows in set (0.01 sec) Fill in the blank with the word(s) that complete the command below, so that it returns the following result set: | zip | COUNT (firstname) | 45001 | 07005 | | 33647 | | 33620 | 33765 | McFly | Parker | McFly Tannen SELECT zip_code.zip, COUNT(firstname) FROM zip code people ON people.zip GROUP BY zip_code.zip; JOIN 45001 | 33647 | | 33647 | | 33647 | | 07005 | O UNION O LEFT JOIN zip_code.zip

Question 1 Refer to the sample tables and data below. Tables: people (id(pk), firstname, lastname, zip(fk)) -- foreign key references zip_code(zip) zip_code (zip(pk), city, state) mysql> select from people; | id | firstname | lastname | zip ++ | 1 | Marty | 2 | Jennifer 3 | Lorraine | 4 | Biff is | George | McFly 5 rows in set (0.01 sec) mysql> select from zip_code; | zip | city | state | | 45001 | New York | NY | 0700s | New Jersey | NJ | 33647 | Tampa FL | 33620 | Tampa | FL | 33765 | Clearwater | FL 5 rows in set (0.01 sec) Fill in the blank with the word(s) that complete the command below, so that it returns the following result set: | zip | COUNT (firstname) | 45001 | 07005 | | 33647 | | 33620 | 33765 | McFly | Parker | McFly Tannen SELECT zip_code.zip, COUNT(firstname) FROM zip code people ON people.zip GROUP BY zip_code.zip; JOIN 45001 | 33647 | | 33647 | | 33647 | | 07005 | O UNION O LEFT JOIN zip_code.zip

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

The database should contain the following six tables: Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime) Write a single SQL query for each request below. Find the ids of those users who have listened to at least one track but have not followed any artists. Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'. Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.
Refer to the sample tables and data below. Tables: people (id(pk), firstname, lastname, zip(fk)) zip_code (zip(pk), city, state) mysql> select * from people; +-- |id| firstname | lastname | zip 1 Marty | 2 | Jennifer 3 Lorraine | 4 Biff | 5 | George 5 rows in set (0.01 sec) McFly | Parker McFly 45001 New York | NY 45010 | New Jersey | NJ 33647 Tampa FL 33765 | Clearwater | FL 4 rows in set (0.01 sec) O rows mysql> select * from zip_code; zip | city | state | 3 rows Tannen McFly 6 rows 9 rows 45001 | 33647 33647 How many rows will be returned by the following command? SELECT firstname, city, state FROM people LEFT JOIN zip_code ON people.zip = zip_code.state; | 33647 | 45010 | foreign key references zip_code(zip)
Refer to the sample tables and data below. Tables: people (id(pk), firstname, lastname, zip(fk)) zip_code (zip(pk), city, state) mysql> select * from people; | id | firstname | lastname | zip +-- | McFly Jennifer | Parker Lorraine | McFly Tannen | 1 | Marty | 2 | 45001 33647 33647 33647 | 07005 | 3 | | 4 | Biff | 5 | George | McFly 5 rows in set (0.01 sec) mysql> select from zip_code; | zip | city | state | 45001 New York | NY 07005 New Jersey | NJ FL 33647 Tampa | 33765 | Clearwater | FL 4 rows in set (0.01 sec) How many rows and columns will the result set from the following command have? SELECT zip FROM people UNION ALL SELECT zip FROM zip_code; (a) Number of rows: 4 rows foreign key references zip_code(zip) (b) Number of columns: 1 column How many rows and columns will the result set from the following command have? SELECT zip FROM people UNION SELECT state FROM zip_code; (c) Number of rows: 5 rows (d) Number of columns: 9 columns
Refer to the sample tables and data below. Tables: employees (id(pk), name, job_code(fk)) jobs (code (pk), title, salary) mysql> SELECT * FROM employees; | id | name +-- | 1 | Captain Hook 2 Tiger Lily 3 | Pocahontas 4 | Meeko 4 rows in set (0.00 sec) mysql> select * from jobs; | code title | salary | +- I author | 100.50 200.00 | editor 3 manager 150.00 | | 1 2 I job_code 3 rows in set (0.00 sec) 2 1 | foreign key references jobs (code) How many rows will the following commands return? (a) SELECT name, salary FROM employees LEFT JOIN jobs ON job_code = code; (b) SELECT name, salary FROM employees RIGHT JOIN jobs ON job_code = code; [Select] [Select]
Question 2 Refer to the sample tables below. Tables: adult (id(pk), name, spouse_id(fk)) foreign key (spouse id) references adult(id) -- child (id(pk), name) Fill in the blank: The foreign key in the table adult is called a foreign key. • Previous
Create the following tables in your database with the following schema: Table: Employee | Column Name | Type | empId I name | supervisor | salary | int | varchar | | int | int empId is the primary key column for this table. Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager. Table: Bonus | Column Name | Type | | empId | bonus | int | int empId is the primary key column for this table. empId is a foreign key to empId from the Employee table. Each row of this table contains the id of an employee and their respective bonus. Add the following data to your tables: Input: Employee table: | empId | name | supervisor | salary | | 3 | Brad | null | 4000 | 1000 | 2000 | 4000 | John | 3 | 2 | 4 | Dan | 3 | Thomas | 3
name of table 1 Staff_info: Staffid (pk) Fname Lname Dob Genderid Course_code (fk) Gender_info: Genderid (pk) gender Course_info: Course_code (pk) Course_name Departid (fk) Depart_info: Departid (pk) Depart_name using the information above develop a database using mysql workbench and connect the tables to each other using the propper fk (foreign key) and pk (primary key) database name should be connectstaff
34 Given the portion of data from the table Patient in the database. Patient ID Patient_FirstName 211347 Eliza 211348 Michael 211349 Ivan 211350 Suhail Patient_LastName Tan Lyod Tan Tan Patient_DOB Patient Address 29-Aug-2017 123, Jalan Bukit Katil, Minden, Penang 24-Sept-2016 87, Tmn Sentapan, Jelutong, Penang 23-Dec-2016 9-2-1, Apartment Jati, Muar, Johor 17-Jul-2015 11, Lebuh Lid, Goergetown, Penang 34a) Write the SQL query to select all columns from the Patient table for rows where the Patient's Last Name is "Tan" for its value. 34b) Based only on the given Patient table and based on the same criteria as above (34a), Write the SQL query to return only the Patient's ID and First Name columns, and who are also from the state of Penang.
SQL Queries: Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward. Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary) Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber) Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations) Donation_Record(Donor_Id, Donation_Date, Donation_Amount) Employee(MS_Id, Bank_Id) Storage(Donor_Id, Donation_Date, Bank_Id) Write SQL queries to answer the following questions: a.Find the average donation amount for Sue Thompson (Donor Id = 3329) for the year 2018. b.Sum all donations by Bank for each State (i.e. the State the blood bank is located in, not the Donor). c.Find the name of the youngest donor and all the donations made in by that donor in 2020.
Refer to the dimension tables and EmployeeFact table below. Job Date Location EmployeeFact •JobID •DatelD •LocationID • ( OEmployeelD Title FullDate StreetAddress oJobID JobCode DayNumber City PostalCode OLocationID LevelCode MonthNumber oHireDatelD OBirthDatelD ) SalaryAmount BonusAmount MonthName State Year CountryCode EducationYears PerformanceRating The following query reports the employee with the highest salary amount per postal code for employees with level code 8. Complete the missing values. SELECT CountryCode, EmployeeID, MAX(_ _(A)_ FROM (B). Location, Job WHERE EmployeeFact.LocationID = Location.LocationID AND EmployeeFact.JobID = (C). AND = 8 GROUP (A) Ex: Identifier (B) (C)
SQL Queries: Given the following schema. Primary keys are underlined and highlighted in yellow and foreign keys are bold. The meaning of the relations is straightforward. Medical_Staff(MS_Id, MS_First, MS_Last, MS_PhoneNumber, MS_Occupation, Salary) Blood_Bank(Bank_Id, Bank_Name, Bank_Street, Bank_City, Bank_St, Bank_PhoneNumber) Donor(Donor_Id, Donor_First, Donor_Last, Donor_BloodType, Donor_City, Donor_St, DOB, TotalNumberOfDonations) Donation_Record(Donor_Id, Donation_Date, Donation_Amount) Employee(MS_Id, Bank_Id) Storage(Donor_Id, Donation_Date, Bank_Id) Write SQL queries to answer the following questions: a.Create a view to list of all employees for any Blood Bank that is in a city named St. Paul. (i.e., view output includes MS_Last, MS_First, and Bank_Id). Sort by Bank_Id, MS_Last, MS_First. b.Find full names of all donors who donated in 2021. (Hint: use IN) c.Create a stored procedure that takes two inputs: a Medical Staff identifier (MS_Id) and new salary. The…
Help with SQL database 2. In database lab01, create 4 tables: course, advisor, student, and department (Note: The order of tables below is not proper for creating tables. Please think about which table should be created first) course (course_id varchar(8), title varchar(50), dept_name varchar(20), credits numeric(2,0), primary key (course_id), foreign key (dept_name) references department (dept_name)) advisor (s_ID varchar(5), i_ID varchar(5), primary key (s_ID), foreign key (s_ID) references student (ID)) student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID), foreign key (dept_name) references department (dept_name)) department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key (dept_name)) . Populate tables (Note: The order of populating table below is not proper. Please think about which table should be populated first) Populate the table "student" with the data below: ('23121', 'Chavez',…