In: Computer Science
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.
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);