Question

In: Computer Science

1. Write a SQL statement which joins the rider_student table with the rider_major table and lists...

1. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name and the name of the major (major_name) and the description of the major for which they are currently assigned. (You may use the SQL 'join' subclause, or simply express the join as part of the 'where' clause by indicating that you only want records where the primary key of the child table, rider_major, equals the corresponding foreign key of the joined table, rider_student.)

2. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name and the name of the major (major_name) and the description of the major for which they are currently assigned. The SQL statement should only select records for students who are majoring in Information Systems.

3. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name and the name of the major (major_name) and the description of the major for which they are currently assigned. The SQL statement should only select records for majors who are graduate students (graduate_only = 'TRUE’).

4. Write a SQL statement which joins the rider_student table with the rider_major table and lists the rider student name (select CONCAT(first_name,' ',last_name) AS NAME) and the name of the major (major_name) and the description of the major for which they are currently assigned. The SQL statement should only select records for majors who are graduate students (graduate_only = 'TRUE’)

5. Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query.

6. Write a SQL statement which joins the parts table with the suppliers table and lists the part_name, supplier_name. You should return all rows from the parts table whether or not there are corresponding rows in the supplier table. You should use an outer join for this query.

7. Write a SQL statement which joins the parts table with the supplier table and lists the part_no, part_name, supplier_name from parts and suppliers table. Only return the parts supplied by ‘Fred Smith ..'. You should use an inner join for this query.

8. The faculty table contains faculty information and has a faculty_id as the primary key. The faculty_department table has the department assignments for faculty members and has the faculty_id from the faculty table as an exported key or foreign key. The department table has information about departments and has a department_id column as its primary key. The faculty_department table also has the exported key or foreign key for the department table.

Write a SQL statement to display faculty_id, first name, last name, department_id, department name (need to joins the faculty table with the faculty_department table with the department table. )Use an inner join for this query.

Solutions

Expert Solution

You didn't provide me with all the columns of the tables. So I am writing these queries in a manner where you can substitute the column names with your given columns from your question if needed. Please provide the table details in proper way next time as the column names are vague in this question.

1. SELECT rider_student.first_name, rider_student.last_name, rider_major.major_name, rider_major.major_description

FROM rider_student, rider_major

WHERE rider_major.PK = rider_student.FK;

Note: Here I am guessing the description of major column name as major_description. You also didn't mention the names of primary key and foreign key. So I denoted them as PK and FK respectively. May be there should be a column like major_id which is the primary key for rider_major and it will be the foreign key for rider_student.

Please substitute these names with proper column names.

2.

SELECT rider_student.first_name, rider_student.last_name, rider_major.major_name, rider_major.major_description

FROM rider_student, rider_major

WHERE rider_major.PK = rider_student.FK

AND rider_major.major_name = 'Information Systems';

3.

SELECT rider_student.first_name, rider_student.last_name, rider_major.major_name, rider_major.major_description

FROM rider_student

JOIN rider_major ON rider_major.PK = rider_student.FK

WHERE rider_student.graduate_only = 'TRUE';

Note: I believe that graduate_only column belongs to rider_student.

4.

SELECT CONCAT(rider_student.first_name,' ',rider_student.last_name) AS NAME, rider_major.major_name, rider_major.major_description

FROM rider_student, rider_major

WHERE rider_major.PK = rider_student.FK

AND rider_student.graduate_only = 'TRUE';

5. SELECT supplier.supplier_name, parts.part_name

FROM supplier

INNER JOIN parts

ON supplier.supplier_id = parts.supplier_id;

Note: check the table names properly. Change them if it is not the correct name according to your question.

6.

SELECT supplier.supplier_name, parts.part_name

FROM parts

LEFT JOIN supplier

ON supplier.supplier_id = parts.supplier_id;

Note: Left join returns all rows from left table i.e. parts even if there are no matches in the right table i.e. supplier.

7.

SELECT parts.part_no, parts.part_name, supplier.supplier_name

FROM supplier

INNER JOIN parts

ON supplier.supplier_id = parts.supplier_id

WHERE supplier.supplier_name = 'Fred Smith';

note: please correct the string (supplier_name) if it is not written properly.

8.

SELECT faculty.faculty_id, faculty.first_name, faculty.last_name, department.department_id, department.department.name

FROM ((faculty

INNER JOIN faculty_department

ON faculty.faculty_id = faculty_department.faculty_id)

INNER JOIN department

ON faculty_department.department_id = department.department_id);


Related Solutions

Write a SQL statement which joins the parts table with the supplier table and lists the...
Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...
Write a SQL statement which joins the parts table with the supplier table and lists the...
Write a SQL statement which joins the parts table with the supplier table and lists the part_name, supplier_name for all parts in the part table. The supplier_id column in the suppliers table is the primary key in the suppliers table, and this key has been exported to the parts table where it is a foreign key. You should use an inner join for this query. Write a SQL statement which joins the parts table with the suppliers table and lists...
1. What is one of the most powerful features of SQL? Operators Functions Table Joins 2....
1. What is one of the most powerful features of SQL? Operators Functions Table Joins 2. A JOIN is a mechanism used to associate tables within a SELECT statement? True False 3. A JOIN can modify existing database tables True False None of the above 4. The WHERE clause acts as a filter to only include rows that match the filter condition. True False 5. The more tables you JOIN the less resources the system uses. True False 6. The...
1. Can you please describe the different kinds of joins used in SQL? 2. What SQL...
1. Can you please describe the different kinds of joins used in SQL? 2. What SQL syntax would you use to ensure you are not returning duplicates in your data sets? 3. What SQL syntax would you use to change data in a table if you had write access to the table? Having write access means the data manipulation would occur in the table being pulled from. 4. What SQL syntax would you use to change data in a table...
Use a single SQL statement to create a relational table and to load into the table...
Use a single SQL statement to create a relational table and to load into the table department name, subject code, year of running and session of running that offered by the departments. Note that a running subject offered by a department means a lecturer of the department has been assigned to teach the subject. Next, enforce the appropriate consistency constraints on the new table.    When ready use SELECT statement to list the contents of the relational table created and...
write a sql statement that retrieves product ID (ProductID) and name (Name) from Production.product table for...
write a sql statement that retrieves product ID (ProductID) and name (Name) from Production.product table for all product whose name includes both the words "silver" and "frame"
Can you please describe the different kinds of joins used in SQL?
Can you please describe the different kinds of joins used in SQL?
Write an SQL statement to show which customers boughtwhich items, and include any items that...
Write an SQL statement to show which customers bought which items, and include any items that have not been sold. Include LastName, FirstName, InvoiceNumber, InvoiceDate,ItemNumber, ItemDescription, ArtistLastName, and ArtistFirstName. Use a join using JOIN ON syntax, and sort the results by ArtistLastName and ArtistFirstName in ascending order (Hint: you have to use a RIGHT JOIN on the last step).
In SQL we are working with functions 1. Write a SELECT statement that returns these columns...
In SQL we are working with functions 1. Write a SELECT statement that returns these columns from the Instructors table: a. The AnnualSalary column b. A column named MonthlySalary that is the result of dividing the AnnualSalary column by 12 c. A column named MonthlySalaryRounded that calculates the monthly salary and then uses the ROUND function to round the result to 2 decimal places 2. Write a SELECT statement that returns these columns from the Students table: a. The EnrollmentDate...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table as...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table as if it were being added to MySQL (please give explanations for each line of SQL code and a copy of the code as it would be entered into the query by itself: Customer PK Customer ID Text Phone Number int name text address ID int email text FK vendor ID int Vendor is the name of the table the FK comes from.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT