Modern Database Management (12th Edition)
12th Edition
ISBN: 9780133544619
Author: Jeffrey A. Hoffer, Ramesh Venkataraman, Heikki Topi
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 6, Problem 6.44PAE
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.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Using the EMPLOYEE, JOB, and PROJECT tables in the Ch07_ConstructCo database, write the SQL code that will join the JOB, EMPLOYEE, and PROJECT tables using common attributes. Display the attributes shown in the results presented in Figure P7.2, sorted by project value.
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 listed
Write SQL Queries for following set of tables:EMPLOYEE (EmpNo, Name, DoB, Address, Gender, Salary, DNumber)DEPARTMENT (DNumber, Dname, ManagerEmpNo, MnagerStartDate).i) Display the Age of ‘male’ employees.ii) Display all employees in Department named ‘Marketing’.iii) Display the name of highest salary paid ‘female’ employee.iv) Which employee is oldest manger in company?v) Display the name of department of the employee ‘SMITH’..
Chapter 6 Solutions
Modern Database Management (12th Edition)
Ch. 6 - Prob. 6.1RQCh. 6 - Prob. 6.2RQCh. 6 - Contrast the following terms: a. base table; view...Ch. 6 - Prob. 6.4RQCh. 6 - Prob. 6.5RQCh. 6 - Prob. 6.6RQCh. 6 - List six potential benefits of achieving an SQL...Ch. 6 - Prob. 6.8RQCh. 6 - Distinguish among data definition commands, data...Ch. 6 - Prob. 6.10RQ
Ch. 6 - Prob. 6.11RQCh. 6 - Prob. 6.12RQCh. 6 - Prob. 6.13RQCh. 6 - Drawing on material covered in prior chapters,...Ch. 6 - Explain and provide at least one example Of how to...Ch. 6 - Prob. 6.16RQCh. 6 - What is the difference between COUNT, COUNT...Ch. 6 - What is the evaluation order for the Boolean...Ch. 6 - If an SQL statement includes a GROUP BY clause,...Ch. 6 - Prob. 6.20RQCh. 6 - Prob. 6.21RQCh. 6 - Prob. 6.22RQCh. 6 - Explain why SQL is called a set-oriented language.Ch. 6 - When would the use of the LIKE key word with the...Ch. 6 - Prob. 6.25RQCh. 6 - Prob. 6.26RQCh. 6 - In what order are the clauses of an SQL statement...Ch. 6 - Within which clauses of an SQL statement can a...Ch. 6 - Prob. 6.29RQCh. 6 - Prob. 6.30RQCh. 6 - Prob. 6.31RQCh. 6 - Explain the difference between the WHERE and...Ch. 6 - Prob. 6.33RQCh. 6 - Prob. 6.34PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.36PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.40PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.42PAECh. 6 - Prob. 6.43PAECh. 6 - Are based on the class scheduling 3NF relations...Ch. 6 - Prob. 6.45PAECh. 6 - Prob. 6.46PAECh. 6 - Prob. 6.47PAECh. 6 - Prob. 6.48PAECh. 6 - Prob. 6.49PAECh. 6 - Prob. 6.50PAECh. 6 - Prob. 6.51PAECh. 6 - Prob. 6.52PAECh. 6 - Prob. 6.53PAECh. 6 - Modify the Product_T table by adding an attribute...Ch. 6 - Prob. 6.55PAECh. 6 - Add an order to the Order_T table and include a...Ch. 6 - Use the Pine Valley database to answer the...Ch. 6 - Prob. 6.58PAECh. 6 - Prob. 6.59PAECh. 6 - Prob. 6.60PAECh. 6 - Prob. 6.61PAECh. 6 - Prob. 6.62PAECh. 6 - Prob. 6.63PAECh. 6 - Prob. 6.64PAECh. 6 - Prob. 6.65PAECh. 6 - Prob. 6.66PAECh. 6 - Prob. 6.67PAECh. 6 - Prob. 6.68PAECh. 6 - Prob. 6.69PAECh. 6 - Prob. 6.70PAECh. 6 - Prob. 6.71PAECh. 6 - Prob. 6.72PAECh. 6 - Prob. 6.73PAECh. 6 - Prob. 6.74PAECh. 6 - Prob. 6.75PAECh. 6 - Prob. 6.76PAECh. 6 - Prob. 6.77PAECh. 6 - Prob. 6.78PAECh. 6 - Prob. 6.79PAECh. 6 - Prob. 6.80PAECh. 6 - Prob. 6.81PAECh. 6 - Prob. 6.82PAECh. 6 - Prob. 6.83PAECh. 6 - Prob. 6.84PAECh. 6 - Prob. 6.85PAE
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- Use %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_forwardCreate both the tables Branch and Supervisor with appropriate attribute names, primary key, foreign key and data type. Insert the above records into Branch and Supervisor tables. Display all the records from Branch and Supervisor (Separately) Display all the details of Name, City and Salary. Display the total salary for every city in the supervisor table. Write a PL/SQL program to display BName from the Branch table for D5. Write a PL/SQL program to display all the details of Supervisor who is getting more than 1250 salary using cursor. Update city as ‘Suhar’ for Supervisor named Jack. Write a Procedure to receive a Supervisor SId as Input and display all the related values of that Supervisor. (Also write the calling program.) Delete all the branch details of operations.arrow_forwardSubject : 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_forward
- Using 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_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_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
- Write PL/SQL coomands for the following question5. Table student( name,age,inter_mark,entrance_rank,address,contact_no,ID,branch)Admission( college_name,mode( pay/free/star/NRI), date,ID)a) Write a query to display the students who are all admitted on starmode branchwiseb) Display the college under which maximum NRI student admittedc) Display the college which got minimum free students and atmost one NRI studentd) Display the student who got second maximum rank admission detailse) Display the students category wise admission detailsf) Write a plsql routine to check a perfect square number is the given number.arrow_forwardin pl/sql ,use Cursor to get all the records from table job_history for employee_id 121arrow_forwardUsing 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_forward
- Given 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_forwardWrite the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 7–15, the EMP_2 table will contain the data shown in Figure P8.15.arrow_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
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
SQL Basics for Beginners | Learn SQL | SQL Tutorial for Beginners | Edureka; Author: edureka;https://www.youtube.com/watch?v=zbMHLJ0dY4w;License: Standard YouTube License, CC-BY