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)
- The query in the figure below. PROGRAM 1 Dept ID PROGRAM ACADEMIC ADVISOR STUDENT OFFICE PHONE 1 SID Name GPA Gender Dept ID Field: SID Table: STUDENT Sort Show Criteria: Name STUDENT PROGRAM PROGRAM GPA STUDENT Accounting" or Name and PROGRAM for the students who registered in the A program "Accounting" will be listed SID, Name, PROGRAM, and GPA for the students who registered in the B program "Accounting" will be listed SID and GPA for the students who registered in the program "Accounting" will be listed Name and GPA for the students who registered in the program "Accounting will be listedarrow_forwardUse %ROWTYPE for your cursor and use 3 tables in this activity - EMPLOYEES, JOBS, and DEPARTMENTS Write a PL/SQL block to read and display the last name, job title, and department name from departments 50 and 80 only. Display also the number of records returned by your PL/SQL block.arrow_forwardPart B Create the entities, subtypes, and relationships according to the business rule below. Then map the entities identified into tables. Do these in SQL developer. BUSINESS RULE: A Seller can be categorized as Retailer or Wholesaler. All sellers have a seller id, first name, last name, contact no and maximum credit. A retailer has a RetailPrice, while a Wholesaler has a WholeSalePrice. Each seller should be categorized only to either a retailer or a wholesaler and cannot be a retailer and a wholesaler at the same time. A seller can have one or more shops and shops must be owned by a seller. A shop can contain many products and many products must be found in at least one shop or more shops. Products Shops price type manufacturer Kaddress name Copy and Paste your answers here. (Answers should come from SQL developer)arrow_forward
- Subject : Data base management system( MY SQL LIVE) NOTE: write queries for following. 1. Make a table bonus (bonus Id, bonus amount, department no), insert information of multiple departments with department numbers 2, 4, 10, 15, 20 and 25. 2. Delete row when department no is equal to 20, 3. Increment bonus amount when department no is equal to 10 , 4. Delete row where department is between 10 and 20. 5. Delete rows where department number is greater than 20arrow_forward6. Write SQL queries to answer the following questions:What are the courses included in the Section table? List each course only once.List all students in alphabetical order by StudentNameList the students who are enrolled in each course in Semester l, 2015. Group the students bythe sections in which they are enrolledList the courses available. Group them by course prefix. (ISM is the only prefix shown, butthere are many others throughout the university.)arrow_forwardUsing the relational database given in lab work no. 2, write the following queries in SQL: 1) How many different courses are there in each department? Sort the list. 2) How many students do have an advisor in the computer engineering faculty? 3) For each department, find out the highest CGPA of a student. Sort the list. (Add a new column to the student table!) 4) How many instructors are advising more than three students? 5) Which department has the highest number of students? 6) Which courses have multiple prerequisites? Sort the list. 7) How many different instructors taught the “Computer Programming” course at least two times? 8) Which instructor taught the “Object-Oriented Programming” course the most time? You should also present the output of each query in the lab report. _________________________________________________________________ The relational database schemas are as follows: department(dept-name, building, budget) instructor(ID, name, dept-name, salary) student(ID, name,…arrow_forward
- Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number. The results of running that query are shown in Figure P7.6. EMP_NUM 101 103 104 105 108 113 115 117 EMP_LNAME News Arbough Ramoras Johnson Washington Joenbrood Bawangl Williamson SumOfASSIGN HOURS 3.1 19.7 11.9 12.5 8.3 3.8 12.5 18.8 SumOfASSIGN_CHARGE 387.50 1664.65 1218.70 1382.50 840.15 192.85 1276.75 649.54arrow_forwardin pl/sql ,use Cursor to get all the records from table job_history for employee_id 121arrow_forwardGiven the following relations, write an SQLite query with JOIN keyword to retrieve the full name of employees with customers and the total number of customers they have, as shown in Figure 1. FullName is based on the concatenation of fName and IName, separated by a blank space. TotalCustomer is the total number of customers for a particular employee. Employee(employeeld, fName, IName) Customer(customerNo, fName, IName, email, employeeld) FullName TotalCustomer Figure 1: Sample output Enter your answer herearrow_forward
- Using the Schema - data/statistics Write down the following SQL queries. Assume appropriate data types for each attribute by yourself. 1) Create Bill table. 2)Assume you forgot to set SID as foreign key during creation of Bill table, by default SID is declared as null.Now you have to declare SID as foreign key. 3)Add constraint on names of customer that ensures that names must start with alphabet. 4)Delete constraint that was added in statement 3. 5)Add a new column in any one of the tables. 6)Delete the added column in statement 5 7)Show name of all those customers who have placed order after 23 June 2014 using subquery. 8)Show the data of those customers to who items were sold with SID 1 or 3 or 6 9)Show names of customers and items names of items sold to him 10)Make a vertical partition on customer table in such a way that changes in partitioned portion must be reflected on original table. 11) Update the city of customer as ‘Lahore’ where customer id is 3 12)Delete all those records…arrow_forwardUsing the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number. The results of running that query are shown in Figure P7.6.FIGURE P7.6 TOTAL HOURS AND CHARGES BY EMPLOYEEarrow_forward1. Write an SQL Query to list the department number and the count of projects in each of these departments and only display those values that has more than 3 count. Rename the count column as ‘Project Count’. Use GROUP BY and HAVING. 2. Write an SQL Query to count unique ‘Female’ employees who have been designated as ‘Staff’ and also have worked for more than 20 hours on a single project? Rename the count value as ‘Employee Count’ (Hint: Use Table Joining) Just please help me write the querey based on the picturearrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education