Concept explainers
Are based on the class scheduling 3NF relations along with some sample data shown in Figure 6-11. Not shown in this figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections.
Note that values of the SectionNo column do not repeat across semesters. Figure 6-11 Class scheduling relations (missing ASSIGNMENT)
Write SQL queries to answer the following questions.
a. List the numbers of all sections of course ISM 3113 that are offered during the semester "1-2015."
b. List the course IDs and names of all courses that start with the letters 'Data.'
c. List the IDs of all faculty members who are qualified to teach both ISM 3112 and ISM 3113.
d. Modify the query above in part c so that both qualifications must have been earned after the year 2005.
e. List the ID of the faculty member who has been assigned to teach ISM 4212 during the semester 11-2015.
Want to see the full answer?
Check out a sample textbook solutionChapter 6 Solutions
Modern Database Management (12th Edition)
- Given the employee information in Question 11, list the query output for the INTERSECT query.arrow_forwardToys Galore currently has a credit limit of 7,500. Because Toys Galore has an excellent credit rating, TAL Distributors is increasing the companys credit limit to 10,000. If yourun the SQL query in Exercise 1 after the credit limit has been increased, would ToysGalore still be included in the view? Why or why not?arrow_forwardGiven the following relations (the primary keys are bold): MATCH (CodM, Home Team VisitingTeam, Date,League) PLAYER (CodP, Name, Surname, Role, Nation, Team) GOAL (CodP, CodM, MinutesIntoTheMatch) YELIOW-CARDS (CodP, CodM, MinutesIntoTheMatch) Please note that MinutesIntoTheMatch indicates the time at which either the goal was scored or the yellow card was cautioned (e.g.. between 1st and 90th). Write SQL Query for each AMerican player (nation='America'), find the player's name and surname and the date on which he scored the former goal in his career.arrow_forward
- Using SQL data definition language, write the SQL statements to create each of the following relations. i. student(student id, name, dept id, total credits)ii. department(dept id, building, budget)iii. lecture_hall(hall id, campus, capacity)iv. course(course id, title, dept id, credits) v. takes(student id, course id, semester, year, grade)arrow_forwardUsing SQL data definition language, write the SQL statements to create each of the following relations. i. customer(cust id, lname, fname, initial, area code, phone, balance)ii. product(prod id, vendor id, description, expiry date, quantity, reorder level, price, discount)iii. vendor(vendor id, name, contact, area code, phone, state)iv. invoice(inv number, cust id, date, subtotal, tax, grand total)Note: Use primary key, foreign key and not null constraints where necessary.arrow_forwardConsider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. d) Find the name s of students who took courses offered by CS department and noother department. e) Find the names of all classes that have 10 or more students enrolled.arrow_forward
- Consider the following relation schemas: Department (dnumber, dname, employee_count), PK: dnumber. Employee (eid, fname, lname, salary, dno), PK: eid, FK: dno refs Department. Write triggers in PL/SQL that satisfy the following requirements: The employee_count column in the Department table records the current number of employees in each department. You should guarantee that, whenever an employee record is inserted, deleted or updated in the Employee table, the employee_count attribute always has the correct value for the affected department(s) in the Department table. Also, no department in the organization should have an average salary lower than 10,000. When the insertion, deletion or update of an employee record causes the average salary of a department to be lower than 10,000, the corresponding operation should be cancelled. You may create additional tables or views if needed. Note that your triggers will be graded based on both correctness and efficiency.arrow_forwardBased on the following ER Model, write SQL statements to create the tables. Include all the necessary integrity constraints: entity integrity, key constraint and referential integrity constraint. RoomType should be one of the three values: Classroom, ConferenceRoom, or Office. For each column, use the data type and the length as you think appropriate.arrow_forwardAnswer the given question with a proper explanation and step-by-step solution. Using SQL Server and the AdventureWorks2012 Database to create Multi table queries. A) Write a query to find out how many products had a scrapped quantity greater than 20 and an order quantity greater than 2000. List the Product ID, Product Name, Product Number, Work Order ID, Order Quantity, Scrapped Quantity, and Scrap Reason ID. Sort by Scrapped Quantity, and then by Product ID. Use the Production.Product and the Production.Workorder tables. B) Write a query to find out how many Vista credit cards expire in 2008 or more recent (2009, 2010, etc.) from customer 11151, 11223, and 11750. List the Credit Card ID, Card Type, Card Number, Expiration Month, Expiration Year, Sales Order ID, and Customer ID. Order by Customer ID. Use the Sales.Creditcard and Sales.SalesOrderHeader tablesarrow_forward
- Based on the following ER Model, write SQL statements to create the tables. USING SQL NOT MYSQL Include all the necessary integrity constraints: entity integrity, key constraint and referential integrity constraint. RoomType should be one of the three values: Classroom, ConferenceRoom, or Office. For each column, use the data type and the length as you think appropriate.arrow_forward6. use sql and the folloing data as shown below to Display Warehouse Id, warehouse name, product category Id, product category name, and the lowest product standard cost for this combination. In your result, include the rows that the lowest standard cost is less then $200. Also, include the rows that the lowest cost is more than $500. Sort the output according to Warehouse Id, warehouse name and then product category Id, and product category name.arrow_forwardConsider the following relational data model. Employee (empid, ename, address, title, headid) Works (Empid,Deptid,joindate) Department (Deptid, dname, block) Payment (title, salary) A) Write relational algebra for Display the employee name and department name of each employee. Display the employee who works as senior lecturer and belongs to BEI or BCT department. Display the employee name with corresponding department head. B) Write sql query for Creating the above relations; including appropriate versions of all primary and foreign key constraints Find the employee name and address whose address name ends with substring ‘pur’. Update the salary of senior lecturer by 25%.(Solve A - 1,2,3 only)arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning