3.28 Using the university schema, write an SQL query to find the names and IDs of thoseinstructors who teach every course taught in his or her department (i.e., every coursethat appears in the course relation with the instructor's department name). Order resultby name.<sectioncourse idsec_idsemesteryearbuildingroom_numbertime_slot_idclassroombuildingroom_numbercapacitytakesIDcourse idsec idsemesteryeargradetime_slottime slot iddaystart_timeend_timeteachesIDcourse idsec_idsemesteryearcoursecourse idtitledept_namecreditsprereqcourse idprereq_idstudentIDnamedept_nametot credidepartmentdept_namebuildingbudgetinstructorIDnamedept_namesalaryFigure 2.9 Schema diagram for the university database.advisors idi_id

To make it more clear I created tables and added data, given in the images. NOTE : I did not create…

3.28 Using the university schema, write an SQL query to find the names and IDs of those instructors who teach every course taught in his or her department (i.e., every course that appears in the course relation with the instructor's department name). Order result by name.< section course id sec_id semester year building takes ID course id sec_id semester year grade time_slot course course_id title dept_name credits student ID name dept_name tot cred department dept_name building budget advisor s_id i_id

3.28 Using the university schema, write an SQL query to find the names and IDs of those instructors who teach every course taught in his or her department (i.e., every course that appears in the course relation with the instructor's department name). Order result by name.< section course id sec_id semester year building takes ID course id sec_id semester year grade time_slot course course_id title dept_name credits student ID name dept_name tot cred department dept_name building budget advisor s_id i_id

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

Question 13 Given the following relation: Movie (MovielID, Title, Genre, Year) Write a PL'SQL block of code that performs the following tasks: i. Read a movie id provided by user at run time and store it in a variable. i. Fetch the Genre and title of the movie having the entered id and store them inside two variables i. Display the fetched movie Genre and title. T T T T Paragraph v 3 (12pt) Arial T T O f. O Mashups HTHL CSS
: Write SQL queries DDL Queries to implement the following Relational Model i.e. create the Nurse and Care_Center relations (including primary key and foreign key). Intelligently write data types. Show Output.
SQL: Create a SQL query that uses an uncorrelated subquery and no joins to display the descriptions for products provided by a vendor in area code 615. Remember, the main difference between a correlated subquery and uncorrelated subquery is that, in a correlated subquery, the inner query is dependent upon the outer query, while, with an uncorrelated query, the inner query does not depend upon the outer query. Instead, it runs just once. You should NOT use union, minus, or intersect in this query. The outer query will use the PRODUCT table and the inner query will use the VENDOR table [hint: you should have 9 records of output]. My code: select p_descript from product where (select v_areacode from vendor where v_areacode = '615' ; I am getting this error, problem is I can't see what is missing any assistance is appreciated. ORA-00936: missing expression
Let's wa(k through a review of basic SQL syntax, using as an example the database that was mentionedearlier. This database has the following simple structure(* indicates a primary key):Courses: CourseID*, CourseName, TeacherIDTeachers: TeacherID*, TeacherNameStudents; StudentID*, StudentNameStudentCourses: CourseID*, StudentID*Using the above table, implement the following queries.Query 1: Student Enrollment Query 2: Teacher Class Size
Write the SQL statements using the university schema to perform the followingoperations: Delete all takes tuples corresponding to any section of any course withthe word “advanced” as a part of the title; ignore case when matching theword with the title.
Part II (50 points, each problem is worth 5 points) Write SQL queries for the following database: id Customers name email id Categories name Ho Ho 8 Orders id customer_id B id Products category_id name price но HO Order_Product id order_id product_id quantity 1. Display all customers whose email is null or ends with 'fiu.edu' or 'gmail.com' 2. Display the number of orders placed by a customer whose name is ‘Jason Bourne' (assume there is only one customer with this name). 3. Insert a new product (category_id = 1, name = 'apple', price = 3.00) into the product table. Assume Products.id has a default value. 4. Create the Order_Product table as follows: id is the primary key and its data type is serial, quantity is an integer which doesn't allow null, order_id and product_id are foreign keys and will be set to null on delete. 5. Display the average price of all the products. For example, there are only three products in the database with a price of 7, 8 and 9. Then the average price would…
9. Display student no, student name and leader name for all students including those without leader id (i.e. null value). If exists null value, replace it with the value of "BigBoss'. Order the query in ascending order by the student id column. Give appropriate labels. SQL statements:
Question 13 Given the following relation: Book (BookID, Author, ISBN, Title) Write a PL/SQL block of code that performs the following tasks: i. Read a Book id provided by user at run time and store it in a variable. ii. Fetch the Author and title of the book having the entered id and store them inside two variables iii. Display the fetched book author and title.
SQL Use appropriate clauses and filters to run the query with minimum time complexity. 2. Try to avoid running the queries on unnecessary rows and columns. 3. Share the queries along with a brief description of the code in a python file, so that the results are reproducible. Q1: Write a query to find the students working in a company having more ratings than their seniors EMPLOYEES RATING TABLE student id student name rating senior id designation 1 george 9 3 student 2 micheal 8 4 student 3 mary 6 null senior 4 steve 9 null senior For the above table GEORGE has more rating than his sENIOR
(d) Write an SQL statement, using the inner join construct, that will, for the lecturer John Smith, list in alphabetical order the students taking any module which he teaches. (e) What information does the output from the following SQL query provide? SELECT Lecturer, COUNT(DISTINCT Student) FROM class INNER JOIN staff ON class.Module = staff.Module WHERE Grade < 40 GROUP BY Lecturer; (f) Write an SQL query which would obtain the following information: for each lecturer, what is the average grade obtained by students across all of the modules which they have taught?
*Develop a SQL Query Display all the resorts names whose star-rating ranges between 4 and 5. Sort the result set in the ascending order of star rating. RESORT MANAGEMENT SYSTEM Town Pointofinterest Manager townID Number PK managerID Number PK pointID Number PK townname Varchar(30) describe Varchar(30) name varchar(30) state varchar(30) opentime varchar(10) address varchar(30) %23 longitude varchar(30) varchar(20) varchar(15) closetime varchar(10) city varchar(30) Number latitude FK townID Number summertemp HAS phone wintertemp Number sealevel Number GIVES Resort Guest Review РK resortiD Number guestID Number PK FK,PK guestiD Number resortName varchar(30) name varchar(30) FK,PK resortID Number street varchar(30) MANADES address varchar(30) + varchar(20) dateofreview Date townname varchar(30) BOOKS country email varchar(30) FK townid Number varchar(150) comments phone varchar(15) starRating Number FK managerID Number Booking HAS PK bookingID FK guestid Number CabinCost HAS Number HAS…
Write SQL statements and relation algebra for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.